Re: large tables, large keycaches

[Mike Connell]

Hi,

>I fear that a FLUSH TABLE will stop all writing threads until the flush is complete.

Yes, we also have some innodb tables doing a totally seperate function and it
stops these too.

EXPERIMENT 1
Was able to obtain the ability to modify the keycache parameters on the box in
question and setup a nearly identical scenario to the one reported on originally.

I grabbed another 2Gig from the system and made a cache called "biginsertcache"
for the 70 million row table. So the default cache was 2Gig and the "biginsertcache" cache was 2Gig.

Rebooted and assigned biginsertcache to the big table. Started the PHP loader
process going yesterday about 6pm pacific time. Let the process run for 12 hours
so that the cache could stabillize (I didn't preload it).

The disk seems slightly busier now as the iostat shows more peaks up to 100%.

The mix of i/os has shifted fairly dramatically.

Here are those results:

R 8192 => 3262/10291 = 32%
R 4096 => 4853/10291 = 47%
W 8192 => 0544/10291 = 5.3%
W 4096 => 1342/10291 = 13%
miscellaneous = 2.7%

whereas when I first reported, they were:

R 8192 => 1417/6263 = 22%
R 4096 => 1458/6263 = 23%
W 8192 => 830/6263 = 13%
W 4096 => 1962/6263 = 31%
miscellaneous = 11%

much less writing and more reading in the mix now.
note the sample times are slightly different.

o:v40-4> show status like 'key%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_blocks_not_flushed | 1230447 |
| Key_blocks_unused | 1854159 |
| Key_blocks_used | 1600660 |
| Key_read_requests | 514187608 |
| Key_reads | 2909980 |
| Key_write_requests | 73690947 |
| Key_writes | 1258716 |
+------------------------+-----------+

So you can see that there are a lot of unused blocks
but one thing that you can't see is which cache are they
unused in. How do I do this?

At this point I'd say the result is a little
better, but because of the reduction in writes, maybe overall
system throughput is up slightly.