Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
You could tell Google to slow down it's requests using Google Webmaster Tools. You could add application layer logic to only update your session table if the last visit was more than x seconds ago. –  Max Vernon Oct 1 '13 at 13:47

1 Answer 1

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.