Hi everyone, I hope you can give me some pointers to speed up the inserts for a simple InnoDB table. We are running MySQL 4.0.16-Max on a machine with 4 Intel Xeon 2.8 GHz CPU's, 2 GB RAM, Red Hat 9 Linux kernel 2.4.20. The innodb_buffer_pool_size is set to 512 MB. This is the create statement for the table in question: CREATE TABLE `sensortest_rawdata` ( `db_test_id` int(10) unsigned NOT NULL default '0', `measurement_no` int(10) unsigned NOT NULL default '0', `point_no` smallint(6) unsigned NOT NULL default '0', `sweep_no` tinyint(3) unsigned default '0', `source_voltage` float default NULL, `gate_voltage` float default '0', `source_current` float default '0', `gate_current` float default NULL, PRIMARY KEY (`measurement_no`,`point_no`,`db_test_id`), KEY `db_test_id` (`db_test_id`,`measurement_no`) ) TYPE=InnoDB COMMENT='raw measurements from FTB'; According to SHOW TABLE STATUS, it has approximately 200 million records and takes 25 GB. One block of measurements typically consists of 256 records with the same values in db_test_id and measurement_no. It takes 200 - 300 ms to insert. At first, I was inserting the records one by one, then switched to this syntax: INSERT INTO table (field1, field2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value 2_2), ... This did not seem to affect the speed. I could store the measurements in BLOB columns and thus have a single record per block of measurements, as I did in another table in this database, but right now that would make viewing the results a lot more complicated. The program that performs the inserts is written in Java and uses the MySQL Connector/J 3.0.9 driver. The inserts happen through a PerparedStatement object, so, theoretically, the query should be compiled only once, when the object is instantiated. I'm not sure whether the driver and the database take advantage of that... Originally it was running on another machine, and all the upload traffic went through Ethernet. I thought that might be the bottleneck and tried to run it on the same machine that hosts the database, with no noticeable improvement in speed. As far as I understand, it still goes through TCP/IP. In any case, by my calculations, the network overhead should be orders of magnitude less than the 200 - 300 ms it takes to insert one block of measurements. I noticed that the CPU usage by mysqld-max during these inserts is about 10%. I thought one possible reason was updating the index on the db_test_id and measurement_no columns, but I cannot remove it, as the other queries would becme very slow... In fact, I'd like to add one more index, just on db_test_id column. Can I increase some buffer size or do anything else to speed up these inserts? Ideally, I'd like them to be faster by an order of magnitude... Below is the output of show innodb status command issued during the upload. Sorry about the length e-mail... Thanks in advance for your help, Sergei ===================================== 040804 16:17:46 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 10 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 89061, signal count 88892 --Thread 622604 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: X-lock on RW-latch at 6142b5b4 created in file buf0buf.c line 444 a writer (thread id 622604) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 3555 Last time write locked in file buf0buf.c line 1404 Mutex spin waits 1024927, rounds 5574408, OS waits 23954 RW-shared spins 74298, OS waits 35231; RW-excl spins 21136, OS waits 20743 ------------ TRANSACTIONS ------------ Trx id counter 0 2055621 Purge done for trx's n:o < 0 2055606 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 19835, OS thread id 573455 MySQL thread id 26, query id 85617 dba2.nano.covalentmaterials.co 192.168.1.231 dba SHOW INNODB STATUS ---TRANSACTION 0 0, not started, process no 19833, OS thread id 540686 MySQL thread id 24, query id 84974 dba2.nano.covalentmaterials.co 192.168.1.231 dba ---TRANSACTION 0 2055613, not started, process no 19750, OS thread id 294925 MySQL thread id 9, query id 59947 localhost.localdomain 127.0.0.1 dba ---TRANSACTION 0 2055620, ACTIVE 29 sec, process no 19857, OS thread id 622604 inserting, thread declared inside InnoDB 445 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320, undo log entries 34370 MySQL thread id 29, query id 85614 faster.nano.com 192.168.1.21 dba update insert into sensortest_rawdata (db_test_id, measurement_no, point_no, sweep_no, source_voltage, gate_voltage, source_current) values (12773, 267, 1, 1 -------- 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: doing file i/o (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 51, 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 507002 OS file reads, 135631 OS file writes, 36047 OS fsyncs 1 pending preads, 0 pending pwrites 183.98 reads/s, 64779 avg bytes/read, 2.20 writes/s, 1.30 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 5, seg size 7, 16161 inserts, 15965 merged recs, 1080 merges Hash table size 2212699, used cells 1777, node heap has 3 buffer(s) 1210.38 hash searches/s, 1231.08 non-hash searches/s --- LOG --- Log sequence number 62 2554411140 Log flushed up to 62 2554391478 Last checkpoint at 62 2551712730 0 pending log writes, 0 pending chkp writes 7761 log i/o's done, 1.10 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 593613432; in additional pool allocated 877952 Buffer pool size 32768 Free buffers 1 Database pages 32764 Modified db pages 16992 Pending reads 51 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1841409, created 28831, written 1284712 726.33 reads/s, 3.10 creates/s, 1.00 writes/s Buffer pool hit rate 927 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue Main thread process no. 19233, id 114696, state: sleeping Number of rows inserted 2939050, updated 446, deleted 0, read 232552 1205.18 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
