It doesn't really answer your question, but have you tried "INSERT
DELAYED" as a work around?
Also the "updated" status is strange, because that generally indicates
that its looking for the record to be updated, but since the record is
new, there is no record to be updated. Could it be checking for
duplicates? Not that it should be this slow, but you might try ALTER
TABLE xxx DISABLE KEYS and see how that effect performance. At least it
will tell you whether the problem is in updating the keys, or something
else.
Chris Kantarjiev wrote:
That throws out my first theory about table locks.
That's what I thought, too.
What do vmstat and top say? Is it CPU bound? I/O bound?
Certainly not CPU bound. Maybe I/O bound, not conclusive. My current
theory is that there is some thrashing on key buffer blocks.
Also you might want to do a "show status" before and after.
Before and after the short query?
| Handler_commit | 0 |
| Handler_delete | 20075144 |
| Handler_read_first | 18 |
| Handler_read_key | 432092430 |
| Handler_read_next | 510230999 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 148798998 |
| Handler_read_rnd_next | 1676270985 |
| Handler_rollback | 0 |
| Handler_update | 122968312 |
| Handler_write | 286299142 |
...
| Key_blocks_used | 2887383 |
| Key_read_requests | 2929797981 |
| Key_reads | 8856159 |
| Key_write_requests | 305442125 |
| Key_writes | 94187694 |
| Uptime | 268069 |
| Handler_commit | 0 |
| Handler_delete | 20075144 |
| Handler_read_first | 18 |
| Handler_read_key | 432092430 |
| Handler_read_next | 510230999 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 148798998 |
| Handler_read_rnd_next | 1676280460 |
| Handler_rollback | 0 |
| Handler_update | 122968312 |
| Handler_write | 286308623 |
...
| Key_blocks_used | 2887383 |
| Key_read_requests | 2929979657 |
| Key_reads | 8859748 |
| Key_write_requests | 305476828 |
| Key_writes | 94187704 |
| Uptime | 268102 |
OK, so in 33 seconds, it did 181676 read requests and 3589 reads, and 34703
write requests and 10 actual writes. 108 reads/sec, less than 1 write/sec.
systat vmstat tells me that the overall data rate off the index disk
is relatively low, but the disk is pretty busy, so there's a lot of seeking.
The data disk is barely being touched.
You might want to try putting the result of both "show status" calls
side by side to see if some unexpected resource is being used.
Nothing jumps out at me. Handler_read_rnd_next is increasing at 287/sec,
which isn't small...
The key_buffer is 3GB or 4GB on this system. I'm contemplating an upgrade
to 4.1 so I can have multiples.
Thanks for looking at this...
