Piwik and LOAD DATA INFILE

If you use the free web analytics software Piwik and run a well attended web site you may know the problem of high server load due to the frequent archiving which is neccessary to create reports.

Piwik uses MySQL for data storage and archives the data for the reports in the web interface automatically if required – for example if the data for the reports to be displayed is older than 10 minutes.

As long as the monthly access numbers are just a few thousand the archiving does not cause big delays – but for larger amounts of data the documentation of Piwik recommends to disable the automatic archiving and to do this in a cron job which runs once an hour.

At least the processing in a cron job avoids delays in the web interface caused by archiving. But with increasing access numbers even this process will cause a significant frequent load of the server for a couple of minutes and can also increase the loading time of websites noticeable.

Piwik also provides a solution for this problem: The use of LOAD DATA INFILE in MySQL. This reduces the load considerable since MySQL does not have to execute thousands of INSERT statements any longer but can import the records directly from a file which Piwik creates. You can see in the system check of Piwik if this is possible.

LOAD DATA INFILE can be a security risk! Please check carefully that the following changes do not lead to a security problem on your machine! There are distributions where MySQL does not provide LOAD DATA INFILE at all – in this case you would first have to build and install a customized version of MySQL, which I do not recommend if you are not familiar with this.

For security reasons it is usually not possible to use LOAD DATA INFILE. To enable MySQL to be able accessing the files which Piwik writes for the import you have to change a number of things – here for example in Ubuntu Linux 12.04:

The MySQL user which is used for Piwik requires the global FILE permission, which can be done with the following SQL statement (modify user name and host as required):

GRANT FILE ON *.* TO 'piwik_user'@'localhost'

In the MySQL configuration the setting local-infile has to be activated. To do this you have to add a line in the in sections [mysqld] and [mysql] of the file /etc/mysql/my.cnf:

[mysqld]
local-infile = 1

[mysql]
local-infile = 1

After this change MySQL has to be restarted.

Additionally the user which is used to run MySQL has to be added to the group which is the owner of the folder for the Piwik website. If the user is mysql and the group www-data then you can achieve this with the following shell command:

usermod -a -G www-data mysql

A note about security: www-data is usually the group which is used to run every website on a server. With this change the MySQL user will have access to the data of other websites too, not only Piwik. To improve security I recommend running PHP in its own process using fcgi and using a group which is only used for the Piwik web site and nothing else.

After these changes you should see a green check mark at the respective entry in the system check of Piwik for “LOAD DATA INFILE”.

The server load should also be reduced considerably. In my case it was frequently above 1.5 – afterwards below 0.5. In this context I also recommend tools like Munin to view the development of longer periods.

On the following Munin chart you can see quite clearly the point at which the server load had been reduced by activating LOAD DATA INFILE:

Server load caused by Piwik archive

Additional options

If the steps above don’t work, there may be additional changes needed:

MySQL setting secure_file_priv

In the serverver configuration of MySQL you may need to disable the variable secure_file_priv (or set it to the correct directory where Piwik puts the import files).

[mysqld]
local-infile = 1
secure_file_priv = ""

[mysql]
local-infile = 1

Piwik database adapter

Piwik can use different database adapters. Usually it uses PDO_MYSQL. This adapter sometimes causes problems when Piwik builds the SQL statement depending on the PHP configuration. It can help to change it to MYSQLI, what you can do in the file config/config.ini as following:

[database]
adapter = "MYSQLI"

One thought on “Piwik and LOAD DATA INFILE”

  1. Just a quick note for others, if you are running each website on it’s own PHP process, then use the command mentioned above, but change www-data to the php process user:

    usermod -a -G user mysql

    Where “user” would obviously be replaced with the correct user account.

    This was a great help, thanks!

Leave a Comment

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