Mysql – Possibilities to speed up InnoDB INSERTs and UPDATEs

innodbMySQLmysql-5.5performance

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower.

I'm monitoring when, and it turns out it's usually when a bot is hitting my site (googlebot for instance always gets the same session assigned, so each hit will require an UDPATE on the session table).

I presume google hits me so hard that mysql can't keep up with updating the same record. Usually an update takes a few mills, but I've seen it reach a full second. My table btw has never reached more than 7000 records.

Knowing that I have only 1 disk at my disposal, but still some free memory to spare, I was wondering what would be the best possibilities to enhance the UPDATE performance on this particular table (I'm not experiencing any issues on other tables as it's much less write intensive).

Best Answer

InnoDB Architecture

InnoDB Architecture

You may find this intriguing but you can double or triple the speed of InnoDB writes. How ?

Disable the double write buffer (using innodb-doublewrite)

First, add this to my.cnf (my.ini for Windows)

[mysqld]
innodb-doublewrite=0

Then, restart mysql

  • Linux: service mysql restart
  • Windows
    • net stop mysql
    • net start mysql

WARNING

In the event of a crash, the crash recovery phase of the mysql startup may produce inconsistent data or lost data.

If you are using Linux, you can counteract that threat by setting up Percona XtraDB Cluster (PXC) with two or more nodes. If your main node crashes, then you can manually failover to one of the other nodes. Make sure all nodes in PXC has the double write buffer disabled.

GIVE IT A TRY !!!

Related Question