large tables, large keycaches
Need help with very large keycache performance tuning...
70 million row table in MyISAM.
Delay key write =1 so that inserts are very fast.
2 Gig key buffer.
As long as the key buffer has key blocks unused, things go fast. Our php program can
run 300-400 rows a second loading this table.
When the key buffer reaches the point where key blocks unused = 0 and key blocks not flushed
is nearly equal to key blocks used, things really slow down. The php program slows down to
100 rows/second.
It looks like at the point where there are no clean blocks in the cache for MySQL
to use, it has to start using its LRU algorithm to kick out dirty pages. This seems reasonable.
The question is, how can I speed this up???
We have about 10 large tables. Out of the 10, the one table is 70 million rows, the next biggest
is about 13 million. Usually 5 of the tables are active and in the key cache. The 70 million is
mostly inserts and some few selects.
A keycache of 2Gig worked fine for the smaller tables (13 million rows and under).
This was used after it was found that a 500Meg keycache didn't work at all.
But at this point I'm wondering if the new mid point insertion strategy would be the way to go, or if
I can otherwise improve the performance. It is especially noticeable how slow things get
on a flush tables.
The machine is a 4way with 8gig of RAM. The disk is RAID 5. The number of MySQL processes is
always under 20. I'm running with indexes enabled during the load :( sometimes the php
needs to access some data from the table).
Oh, and for backup, I have a parallel database that I can recover from if need be.
Thanks,
Mike