Server Hangs or Crashes with large Piwik Installation

 
2 Kudos
Don't
move!

Update 01.06.2015: Issue still remains. The suggested fix in this post didn’t work for me.

I have a dedicated Linux server (Debian) that runs Piwik. It’s a fairly standard installation of the LAMP stack. My Piwik server tracks visitors on 5–6 websites and one of them is fairly large with thousands of visitors per day. In recent weeks, my Piwik server would sometimes just hang. Responses weren’t sent and thus, I have gaps in my visitors graph, which is pretty annoying.

If this happened to you, you might want to check your syslog:

cat /var/log/syslog

This showed some problems with InnoDB:

Mar 26 09:59:21 hostname mysqld: InnoDB: Warning: a long semaphore wait:
Mar 26 09:59:21 hostname mysqld: --Thread 140057268492032 has waited at row0ins.c line 2017 for 911.00 seconds the semaphore:
Mar 26 09:59:21 hostname mysqld: X-lock on RW-latch at 0x7f61c55f32c0 created in file buf0buf.c line 938
Mar 26 09:59:21 hostname mysqld: a writer (thread id 140057750890240) has reserved it in mode  wait exclusive
Mar 26 09:59:21 hostname mysqld: number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Mar 26 09:59:21 hostname mysqld: Last time read locked in file buf0flu.c line 1336
Mar 26 09:59:21 hostname mysqld: Last time write locked in file /build/mysql-5.5-rbPmJD/mysql-5.5-5.5.41/storage/innobase/row/row0ins.c line 2017

The solution is to disable innodb_adaptive_hash_index in MySQL (see docs here)

It is enabled by default and you can check it like so:

mysql -u root -p

Then in MySQL:

mysql> SHOW VARIABLES;

To disable it, run this:

mysql> SET GLOBAL innodb_adaptive_hash_index=0;

Check again with SHOW VARIABLES;:

| innodb_adaptive_hash_index                        | OFF          

To make the changes persistent after a server restart, edit my.cnf:

sudo nano /etc/my.cnf

Go to section [mysqld] and add this line:

innodb_adaptive_hash_index=0

This should hopefully fix InnoDB issues with Piwik.

Leave a Reply

Your email address will not be published. Required fields are marked *