mysql insert performance

Question

Our DB has very bad insert and update performance. Most of the big tables take 2-3 seconds per insert. If we rebuild the table, time reduces to 0.02-0.03 which means about 40 inserts per second. Even that is pretty low, and we cannot afford to rebuild tables every now and then. It is a time-consuming process and locks the table for the duration. If we group inserts into transaction the performance increases. But in our application we have a requirement of commiting the insert immediately. Can anybody suggest what can be done to improve the insert performance.

Here is the mysqlreport from server if it indicates anything: One thing noteworthy is that Com_rollback is very high on our server. What can that possibly mean?

MySQL 5.0.51a-3ubuntu5. uptime 2 22:23:11 Thu Mar 11 09:13:33 2010

__ Key _____________________________________

Buffer used 70.03M of 3.00G %Used: 2.28
Current 552.19M %Usage: 17.97
Write hit 78.74%
Read hit 99.63%

__ Questions _________________________________

Total 803.30M 3.2k/s
Com_ 606.44M 2.4k/s %Total: 75.49
QC Hits 130.15M 513.7/s 16.20
DMS 47.99M 189.4/s 5.97
+Unknown 18.69M 73.7/s 2.33
COM_QUIT 32.23k 0.1/s 0.00
Slow (10) 3.24k 0.0/s 0.00 %DMS: 0.01 Log: ON DMS 47.99M 189.4/s 5.97
SELECT 40.94M 161.6/s 5.10 85.30
INSERT 3.92M 15.5/s 0.49 8.16
UPDATE 2.85M 11.2/s 0.35 5.93
DELETE 291.57k 1.2/s 0.04 0.61
REPLACE 0 0/s 0.00 0.00
Com_ 606.44M 2.4k/s 75.49
set_option 467.34M 1.8k/s 58.18
rollback 126.23M 498.2/s 15.71
commit 11.44M 45.2/s 1.42

__ SELECT and Sort _____________________________

Scan 85.20k 0.3/s %SELECT: 0.21
Range 1.91M 7.5/s 4.67
Full join 126 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 914 0.0/s
Sort range 191.09k 0.8/s
Sort mrg pass 0 0/s

__ Query Cache _______________________________

Memory usage 190.04M of 1.00G %Used: 18.56
Block Fragmnt 24.36%
Hits 130.15M 513.7/s
Inserts 12.10M 47.8/s
Insrt:Prune 12.10M:1 47.8/s
Hit:Insert 10.75:1

__ Table Locks _______________________________

Waited 52.96k 0.2/s %Total: 0.08
Immediate 63.16M 249.3/s

__ Tables __________________________________

Open 64 of 64 %Cache: 100.00
Opened 1.07M 4.2/s

__ Connections _______________________________

Max used 396 of 500 %Max: 79.20
Total 49.06k 0.2/s

__ Created Temp ________________________________

Disk table 5.27k 0.0/s
Table 47.47k 0.2/s Size: 200.0M
File 27 0.0/s

__ Threads ___________________________________

Running 18 of 245
Cached 0 of 4 %Hit: 39.82
Created 29.53k 0.1/s
Slow 0 0/s

__ Aborted ___________________________________

Clients 33.67k 0.1/s
Connects 13 0.0/s

__ Bytes ___________________________________ Sent 227.04G 896.0k/s
Received 106.15G 418.9k/s

__ InnoDB Buffer Pool ____________________________

Usage 4.00G of 4.00G %Used: 100.00
Read hit 99.96%
Pages
Free 0 %Total: 0.00
Data 251.34k 95.88 %Drty: 0.86
Misc 10807 4.12
Latched 2 0.00
Reads 13.04G 51.4k/s
From file 5.02M 19.8/s 0.04 Ahead Rnd 133920 0.5/s Ahead Sql 246973 1.0/s Writes 968.37M 3.8k/s Flushes 19.42M 76.6/s Wait Free 0 0/s

__ InnoDB Lock _______________________________

Waits 1540 0.0/s Current 0 Time acquiring Total 1239283 ms Average 804 ms Max 51521 ms

__ InnoDB Data, Pages, Rows ________________________

Data Reads 6.86M 27.1/s Writes 21.30M 84.1/s fsync 13.42M 53.0/s Pending Reads 1 Writes 0 fsync 1

Pages Created 2.34M 9.2/s Read 23.39M 92.3/s Written 19.42M 76.6/s

Rows Deleted 291.57k 1.2/s Inserted 138.07M 544.9/s Read 7.21G 28.5k/s Updated 2.78M 11.0/s

Answer 1

Check DISC IO parameters. Possibly you simlpy overload your disc subsystem - which would mean getting more discs, a RAID controller etc.

I have seen on Windows / SQL Server a custome rcomlpain about bad insert perforamnce, and the measurement showed average disc response time was 1.2 SECONDS - so overloaded was the disc. This should be in the 2 digit milliseecond area - like 15ms or.

Answer 2

Com_rollback means 'commit rollbacks' I assume. This would mean that you have lots of transaction commits fail.

Generally I suspect that you're doing something out of the ordinary with your keys, especially your primary key (you have one, right?). How are your keys defined? Are you changing the columns on which the keys frequently? If your INSERT and UPDATE commands are much faster after you rebuild the table then it sounds like your primary key, upon which MySQL orders and stores your table's rows, are changing.

Putting your table definition up here should help (DESCRIBE your_table_name or SHOW CREATE TABLE your_table_name will do the trick – I'm partial to the latter format).