Re: Hash and Linear Hash partition table not performing well

[Rick James]

Let me first walk through how I think it is working.

You have about 400MB of data and 100MB of index per partition. There are 4 indexes, each taking 20-30MB.

Each partition has about 2.5M rows.

SELECT COUNT(*) FROM test_data WHERE id IN(.........); -- I assume "id" one of the 3 indexed ids in the table.

You have 4GB of key_buffer. If you don't have at least 6GB of RAM, you will be thrashing I/O.

The SELECT will...
1. hash each id to decide which partitions to use. Use EXPLAIN PARTITION ... on some of the shorter selects to see if I am right.
2. Foreach id, reach into the appropriate index to COUNT the number of entries in the index (no data need be touched). Or perhaps it will do: Foreach needed partition...
3. Total up the COUNTs.

When it is doing step 2 for a single id in a single partition, it will be drilling down a BTree, which will be about 4 levels deep.

If the key_buffer is "cold", as in right after restarting, it will be perhaps 1-2 disk hits per id. So, you might be able to count 100 ids per second. Is that the speed you got?

If you then run the same query again, all the index blocks will be in the key_buffer, so there should be no I/O, just CPU. This will probably count faster than 1000/sec.

If you hit it with different ids, it will be slow, again. Eventually, you will get all the index blocks for _one_ index for all the 128 partitions into RAM. (Looks like < 3GB in 4GB.) Then all the queries will be CPU speed.

Now, let's consider a simple MyISAM table without partitioning. Guess what. The analysis above is almost correct. Remove any mention of partitioning, add 1 level to the BTree. The rest stands; including the timings.

But, you say Partitioning is slower? Well, I would guess the bookkeeping takes a toll.

Ok, one thing should be a tiny bit faster -- the hash to pick one of 128 partitions versus a binary search to drill down the top layer of the index (of about 100 items).