Re: Help understanding the relationship between threads and opened tables for MyISAM

[Rick James]

> 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.)