MySQL Forums
Forum List  »  MyISAM

Re: Help understanding the relationship between threads and opened tables for MyISAM
Posted by: Rick James
Date: October 10, 2012 08:02PM

> 5 copies of that table will be opened
5 handles, not copies of the table.

> 5 copies of the table can be seen by looking at file descriptors
Reading can happen simultaneously. But if a write is in the mix, each has to finish before the next can start.

SELECT COUNT(*) FROM tbl;
(no WHERE clause) It will return instantly. This is because the COUNT(*) of the whole table id dead-reckoned in MyISAM. You need a more complex example to actually get 5 threads contending for the table.

PARTITIONed tables have a 'flaw'. All partitions are opened in many situations when you would think that only one or a few partitions need to be opened. Hence, this is folly:
> tables with 150 partitions

> We allow up to 300 simultaneous connections
Connections != threads != threads running.

What is your value for table_open_cache?
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
Perhaps of most interest is (Opened_tables / Uptime). If that is more than, say, 1/sec, then increase table_open_cache.

What queries stand to benefit from PARTITIONing? (You may find that you are not getting any benefit.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help understanding the relationship between threads and opened tables for MyISAM
1316
October 10, 2012 08:02PM


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.