Re: Very slow insert into's

[Tom Nordgren]

This is w/O locking

SHOW INNODB STATUS ---TRANSACTION 0 198403, ACTIVE 10 sec, process no 5787, OS thread id 1078856016, thread declared inside InnoDB 450 mysql tables in use 1, locked 1 4927 lock struct(s), heap size 702448 MySQL thread id 63277, query id 1766712 Sending data
insert into dside_staging.contact (fk_scenario_execution, person_id, timestamp, infected_person_id, location_id, sub_location_id, contact_type)
select 188 as fk_scenario_execution, person_id, timestamp, contact, location_id, sub_location_id, contact_type
from dside_staging.contact2 where timestamp between 8121600 and 12009600 order by timestamp asc
--------
FILE I/O --------
I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
54349159 OS file reads, 37015559 OS file writes, 1888233 OS fsyncs
186.76 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 392, seg size 394,
83585383 inserts, 83585383 merged recs, 19416254 merges
Hash table size 17393, used cells 17220, node heap has 122 buffer(s)
33348.73 hash searches/s, 4499.70 non-hash searches/s
---
LOG ---
Log sequence number 37 2695525272

Log flushed up to 37 2695525272
Last checkpoint at 37 2695525272
0 pending log writes, 0 pending chkp writes
849279 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20992498; in additional pool allocated 1036800
Buffer pool size 512
Free buffers 0
Database pages 348
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 108915213, created 1987550, written 51342393
186.76 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000
-------------- ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 5787, id 1152661840, state: waiting for server activity
Number of rows inserted 392053146, updated 27720123, deleted 8983679, read 13562199309
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 37848.43 reads/s
---------------------------- END OF INNODB MONITOR OUTPUT ============================



Edited 1 time(s). Last edit at 04/03/2009 12:55PM by Tom Nordgren.