INSERT/UPDATE to InnoDB tables really slow?

[Oldiesmann]

For some time now, I've been having issues with one site on my server - www.seniorsandfriends.org - being extremely slow, and haven't really been able to figure out what's going on.

The other day I decided to enable the db debugging feature in SMF to see which queries were taking so long.

It seems that the UPDATE queries for tables that I had converted to InnoDB are taking a ridiculously long time:

Code Select Expand

         UPDATE smf_topics
         SET num_views = num_views + 1
         WHERE id_topic = 886
   in .../Sources/Display.php line 185, which took 1.63933706 seconds at 0.03537107 into request

Code Select Expand

      INSERT IGNORE INTO smf_log_topics(`id_member`, `id_topic`, `id_msg`)
      VALUES
         (2, 2337, 426317)
   in .../Sources/Display.php line 865, which took 4.90868187 seconds at 0.17046118 into request.

Converting the tables back to MyISAM alleviates the slowness, but I can't figure out why.

InnoDB settings are as follows:

Code Select Expand

innodb adaptive hash index ON
innodb additional mem pool size 1,048,576
innodb autoextend increment 8
innodb autoinc lock mode 1
innodb buffer pool size 8,388,608
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb doublewrite ON
innodb fast shutdown 1
innodb file io threads 4
innodb file per table OFF
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log buffer size 1,048,576
innodb log file size 5,242,880
innodb log files in group 2
innodb log group home dir ./
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 300
innodb rollback on timeout OFF
innodb stats method nulls_equal
innodb stats on metadata ON
innodb support xa ON
innodb sync spin loops 20
innodb table locks ON
innodb thread concurrency 8
innodb thread sleep delay 10,000
innodb use legacy cardinality algorithm ON

Any suggestions on this? I converted the tables to InnoDB in the first place because it's supposed to improve performance, but it seems to have done just the opposite in this case.

phpInfo: http://www.oldiesmann.us/test.php
status.php: http://smf.oldiesmann.us/status.php and http://smf.oldiesmann.us/status2.php

[butch2k]

Set innodb flush log at trx commit   to 2 or 0 to fix your issue.

[Oldiesmann]

Thanks! Just changed it to 2 and will get busy changing the tables back to InnoDB here to see if it helps.

[Joshua Dickerson]

You should keep the slow query log enabled all the time as per MySQL's recommendation.

[butch2k]

The insert slowness as nothing to do with the queries being really slow. Indeed Innodb sync the change to disk for every row changed whenever the setting is set to 1, meaning that there is absolutly no write caching going on, seriously limiting the speed of inserts. On a 7200rpm disk you get an average of about 100 iops, meaning about a hundred writes a second at best. At setting 2 Innodb flush every seconds, meaning that it buffers data and insert en-masse, which is much faster client side as it is asynchronous front it's point of view. Yet you might en up with some missing data if server crash. Another cause for slowness is the binary log, but this is another subject.

[stylusss]

Thanks! Just changed it to 2 and will get busy changing the tables back to InnoDB here to see if it helps.

Did this work?