INSERT requires 2 reads?
Hi,
I am INSERTing rows into an InnoDB table (doing nothing else), and I see that write throughput is limited in major part by the reads it's doing from disk. Anybody understands what InnoDB is doing, why it requires about 2 reads for every INSERT it is doing? It is a plain INSERT (without SELECT).
I can get about 40 INSERTs per second (I have 2 mirrored disks and definitely am I/O bound!) in steady state, or 65 when filling up dirty pages, it can't get higher because of the reads. I have seen indications of a possible 250 writes/s, and would very much like to sustain this kind of rate! I am when the DB is small enough to be mosly cached, but now that it's big (relatively speaking!) the reads hit the disk too often.
The database contains about 14 million rows (about 1k each), with a 2GB cache, and innodb_flush_log_at_trx_commit is set to 0.
Partial output from the status file shows numbers similar to these:
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2293705370; in additional pool allocated 2028928
Buffer pool size 131072
Free buffers 0
Database pages 131001
Modified db pages 107118
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages read 3669010, created 878929, written 2741015
62.00 reads/s, 14.73 creates/s, 73.53 writes/s
Buffer pool hit rate 925 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread id 8, state: sleeping
Number of rows inserted 14974853, updated 0, deleted 0, read 87094741
40.87 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
iostat shows something like:
100 reads/s (6300 kb read/s)
55 writes/s (2100 kb written/s)
Thanks!
-Nicolas