MySQL Forums
Forum List  »  MyISAM

Help understanding the relationship between threads and opened tables for MyISAM
Posted by: Michael Finch
Date: October 09, 2012 06:36PM

Hey,

We've been having trouble understanding the relationship between threads and opened tables for MyISAM for a while now, despite reading all of the documentation. Quoting the mysql docs, "To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session". Following this logic, it would seem that if you create 5 different connections and they all perform a sql_no_cache count(*) on a table, 5 copies of that table will be opened. However, we are only seeing this behavior when the queries are made SIMULTANEOUSLY (5 copies of the table can be seen by looking at file descriptors, and we see 5 tables when we run >show global status like "Opened_tables";). When the query is made sequentially on each connection, though, all connections appear to share the file descriptors and >show global status like "Opened_tables"; only equals 1.

The specific problem that we are having is that many of our tables are heavily partitioned, and we are running into issues around too many file descriptors being opened. For example, we often see tables with 150 partitions opened 20 times (20x150 copies of MYD file descriptors opened, 1x150 copy of MYIs). We allow up to 300 simultaneous connections, but there is no point in time where those tables are getting queried 20 times simultaneously. Anyway we were thinking that, per what the docs say, maybe 20 different threads were each holding onto a copy of the tables, but our basic testing has shown that the threads will in fact share the MyISAM resources if they are not being used at the same time.

Any help would be greatly appreciated. Thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
Help understanding the relationship between threads and opened tables for MyISAM
2540
October 09, 2012 06:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.