Chris Kantarjiev wrote:
Can you post the output of SHOW FULL PROCESSLIST during the time when
both sets of queries are running?
That throws out my first theory about table locks.
What do vmstat and top say? Is it CPU bound? I/O bound?
Also you might want to do a "show status" before and after. See whether
the ratio of Key_reads/Key_read_requests or
Key_writes/Key_write_requests is high. This could be an indication that
your key_buffer_size is to small.
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.
mysql> show full processlist;
+-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State
| Info
|
+-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
| 201 | len | dick.landsonar.com:15405 | landsonar | Query | 4033 | Sending
data | insert ignore into trimble.old_crumb select * from trimble.crumba_rolled
|
| 209 | len | dick.landsonar.com:34684 | landsonar | Query | 2 | update
| INSERT IGNORE INTO link_area
(link_ID, dir_Travel, area_ID)
VALUES
(20202282, 'T', 21014142) |
| 216 | len | localhost | NULL | Query | 0 | NULL
| show full processlist
|
+-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
Also what storage engine are you using for your tables?
MyISAM. We found InnoDb to be considerably slower for selects for our data,
though it's been a year since I did that experiment.
