Re: How to partition that table?

[Rick James]

> UNIQUE KEY `protocol` (`protocol`,`path`)
> SELECT id FROM path WHERE path='$path' AND protocol='$protocol'
Since the index is UNIQUE, LIMIT 1 will not help any -- the engine is smart enough to stop after one row.

> key_buffer = 6G
> index size is 9.5GB
> There are two MyISAM indexes.
Probably the index in question is 7GB and the index on id is only 2GB.

Here's what happens:
1. The optimizer sees "WHERE path='$path' AND protocol='$protocol'" and decides to use "UNIQUE KEY `protocol` (`protocol`,`path`)"
2. It drills down the BTree index (about 5 levels deep) to find the leaf node
3. The leaf node contains a byte offset into the data -- use it to fetch the record from the .MYD file.
4. Deliver `id` (SELECT id)

"Count the disk hits"...
In a cold system (after restarting, before anything gets cached), Step 2 needs 5 random reads; step 3 needs 1. That should not take more than about 60ms.
After running a bunch of such queries, the number of un-cached disk hits will drop to typically 1 hit.

What do you have for long_query_time?
Is there a non-zero "lock" time in the slowlog for these queries?

How fast are you inserting rows? With 100M rows, I suspect the inserts are pretty frequent? The table is MyISAM, so each INSERT locks the table to do the insert; this lock may be slowing down the SELECTs.

Consider InnoDB...
Size: You now have 7G (data) + 10G (index); with InnoDB this might change to 12G (data+PK) + 12G (secondary index).
Speed: Step 3 vanishes since `id` (the PK) is implicitly in the secondary index.
Tuning: key_buffer_size = 50M; innodb_buffer_pool_size = 16G.
Locking: InnoDB employs "row locking" instead of "table locking", so INSERTs rarely block SELECTs.
Disk space: While doing ALTER TABLE `path` ENGINE=InnoDB;, you will need 25GB of spare disk space.
File-per-table: Recommend turning on innodb_file_per_table _before_ doing the ALTER.
JOINs to other tables: There is no problem mixing engines. It may or may not be advantageous to convert all tables to InnoDB.

PARTITIONing -- I see no benefit. The number of disk hits would be essentially the same, and the likelihood of any block being cached is similar.