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
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
| innodb_adaptive_hash_index | OFF
To make the changes persistent after a server restart, edit
sudo nano /etc/my.cnf
Go to section
[mysqld] and add this line:
This should hopefully fix InnoDB issues with Piwik.