MySQL Forums
Forum List  »  Partitioning

Re: How to partition that table?
Posted by: Rick James
Date: July 03, 2011 11:42AM

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

Options: ReplyQuote

Written By
June 30, 2011 07:49AM
July 01, 2011 11:25PM
Re: How to partition that table?
July 03, 2011 11:42AM

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.