Piwik - Open Analytics Platform

A few weeks ago, I enabled import of webserver logs into Piwik to get better information about who's visiting me, and especially static contents. This worked fine BUT had serious downsides : it is insanely CPU and HDD greedy (which I don't have a lot of since I'm running on a low-cost virtual machine). So this is why I had to disable it all, and remove entries from the database.

The procedure below also shows the preliminary steps while searching for details about the database itself and the data I'm trying to delete. These steps are still there "for future reference" but also because I'm not sure so far this procedure is final and erased all the unnecessary information. I'll amend it if need be.

MariaDB [(none)]> use piwik;
	Database changed

MariaDB [piwik]> show tables;
	+--------------------------------+
	| Tables_in_piwik                |
	+--------------------------------+
	| piwik_access                   |
	| piwik_archive_blob_YYYY_MM    |
	
	| piwik_archive_numeric_YYYY_MM |
	
	| piwik_goal                     |
	| piwik_log_action               |
	| piwik_log_conversion           |
	| piwik_log_conversion_item      |
	| piwik_log_link_visit_action    |
	| piwik_log_profiling            |
	| piwik_log_visit                |
	| piwik_logger_message           |
	| piwik_option                   |
	| piwik_report                   |
	| piwik_segment                  |
	| piwik_sequence                 |
	| piwik_session                  |
	| piwik_site                     |
	| piwik_site_setting             |
	| piwik_site_url                 |
	| piwik_user                     |
	| piwik_user_dashboard           |
	| piwik_user_language            |
	+--------------------------------+
	68 rows in set (0.00 sec)

MariaDB [piwik]> DESCRIBE piwik_site;
	+--------------------------------+------------------+------+-----+---------+----------------+
	| Field                          | Type             | Null | Key | Default | Extra          |
	+--------------------------------+------------------+------+-----+---------+----------------+
	| idsite                         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
	| name                           | varchar(90)      | NO   |     | NULL    |                |
	

MariaDB [piwik]> SELECT idsite, name FROM piwik_site;
	+--------+-----------+
	| idsite | name      |
	+--------+-----------+
	|      1 | mySite_1 |
	|      2 | mySite_2 |
	|      5 | mySite_5 |
	|      7 | mySite_7 |
	+--------+-----------+
	4 rows in set (0.00 sec)

The sites with ids 1, 2, 5 and 7 are the sites that are registered and still appear in Piwik's web UI.
Sites with ids 4 and 6 (see below) have been deleted : they are not displayed anymore in the web UI, but they still appear in the database.

MariaDB [piwik]> SELECT idsite, count(idvisit) FROM piwik_log_visit WHERE idsite in (4, 6) GROUP BY idsite;
	+--------+----------------+
	| idsite | count(idvisit) |
	+--------+----------------+
	|      4 |           9022 |
	|      6 |          72890 |
	+--------+----------------+
	3 rows in set (0.19 sec)

MariaDB [piwik]> DESCRIBE piwik_log_link_visit_action;
	+------------------------------+------------------+------+-----+---------+----------------+
	| Field                        | Type             | Null | Key | Default | Extra          |
	+------------------------------+------------------+------+-----+---------+----------------+
	| idlink_va                    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
	| idsite                       | int(10) unsigned | NO   | MUL | NULL    |                |
	
	+------------------------------+------------------+------+-----+---------+----------------+
	27 rows in set (0.51 sec)

MariaDB [piwik]> SELECT idsite, count(idlink_va) FROM piwik_log_link_visit_action WHERE idsite in (4, 6) GROUP BY idsite;
	+--------+------------------+
	| idsite | count(idlink_va) |
	+--------+------------------+
	|      4 |           281508 |
	|      6 |          4231128 |
	+--------+------------------+
	3 rows in set (1 min 42.07 sec)

At that point, you may be interested in the size of the tables :

dbName='piwik'; ls -Shl /var/lib/mysql/$dbName | head -20
	-rw-rw---- 1 mysql mysql 956M Dec 16 17:50 piwik_log_link_visit_action.ibd
	-rw-rw---- 1 mysql mysql 120M Dec 16 17:28 piwik_archive_blob_2015_01.ibd
	-rw-rw---- 1 mysql mysql 120M Dec 16 17:28 piwik_archive_blob_2015_12.ibd
	

MariaDB [piwik]> SELECT
	table_name,
	table_rows,
	ROUND(( data_length + index_length ) / 1024 / 1024, 0) AS table_size_MB
FROM
	information_schema.tables
WHERE
	table_schema="piwik"
ORDER BY
	table_size_MB DESC,
	table_rows DESC;

	+--------------------------------+------------+---------------+
	| table_name                     | table_rows | table_size_MB |
	+--------------------------------+------------+---------------+
	| piwik_log_link_visit_action    |     436605 |           365 |
	| piwik_archive_blob_YYYY_MM    |      35384 |            41 |
	| piwik_archive_blob_YYYY_MM    |      14581 |            22 |
	| piwik_log_visit                |      35806 |            15 |
	| piwik_archive_numeric_YYYY_MM |      14217 |            12 |
	| piwik_archive_blob_YYYY_MM    |      12322 |            12 |
	| piwik_archive_numeric_YYYY_MM |       6270 |             4 |
	| piwik_log_action               |       5947 |             4 |
	
	+--------------------------------+------------+---------------+
	68 rows in set (0.72 sec)
	This is the "after" version.

Time to flush out some data, now :

MariaDB [piwik]> DELETE FROM piwik_log_link_visit_action WHERE idsite in (4, 6);
	Query OK, 4512645 rows affected (1 hour 9 min 35.56 sec)

MariaDB [piwik]> DELETE FROM piwik_log_visit WHERE idsite in (4, 6);
	Query OK, 81914 rows affected (3 min 53.47 sec)

Now, let's reclaim some HDD space to MySQL.

MariaDB [piwik]> OPTIMIZE TABLE piwik_log_link_visit_action;
	+-----------------------------------+----------+----------+-------------------------------------------------------------------+
	| Table                             | Op       | Msg_type | Msg_text                                                          |
	+-----------------------------------+----------+----------+-------------------------------------------------------------------+
	| piwik.piwik_log_link_visit_action | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
	| piwik.piwik_log_link_visit_action | optimize | status   | OK                                                                |
	+-----------------------------------+----------+----------+-------------------------------------------------------------------+
	2 rows in set (31 min 45.15 sec)

MariaDB [piwik]> OPTIMIZE TABLE piwik_log_visit;
	+-----------------------+----------+----------+-------------------------------------------------------------------+
	| Table                 | Op       | Msg_type | Msg_text                                                          |
	+-----------------------+----------+----------+-------------------------------------------------------------------+
	| piwik.piwik_log_visit | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
	| piwik.piwik_log_visit | optimize | status   | OK                                                                |
	+-----------------------+----------+----------+-------------------------------------------------------------------+
	2 rows in set (3 min 46.07 sec)

1. intercept requests to http://www.mydomain.tld/static/document.pdf as they arrive with webserver rewrites.
2. transfer them internally to /deliverStatic.php?target=document.pdf
3. in deliverStatic.php, send a custom-built HTTP request to the Piwik Tracking HTTP API (example), then deliver document.pdf

You should create a dedicated "site" in the Piwik console to use the log analytics method to avoid counting twice the hits on dynamic content. Indeed, server-generated pages already carry the Javascript snippet that will hit our Piwik instance once displayed in a web browser, but sending those pages already created a hit on our webserver, which is already written in access logs. (source)

If you already have a running Piwik instance, this will be rather straightforward :

1. Find the misc/log-analytics/import_logs.py script on your Piwik installation directory.
   This may default to something like : /var/www/piwik/misc/log-analytics/import_logs.py
2. Retrieve some parameters :
   • the URL of your Piwik instance : http://piwik.mydomain.tld
   • the Piwik ID of the site you'll be monitoring with webserver logs : idSite (this is possibly a site you've just created)
   • the number of available CPU cores (threads, actually)
   • additional settings about whether or not to import hits related to (source) :
     • static content
     • HTTP redirects (HTTP 3xx status codes)
     • HTTP errors (HTTP 4xx and HTTP 5xx status codes)
     • hits made by bots
   • the location of your webserver access logs : /var/log/myWebser/www.mydomain.tld.access.log
3. then open a shell on your server to launch the log import command (source) : python /var/www/piwik/misc/log-analytics/import_logs.py --url=http://piwik.mydomain.tld --idsite=n --recorders=n --enable-http-errors --enable-http-redirects --enable-static --enable-bots /var/log/myWebser/www.mydomain.tld.access.log
4. Should be worth doing this on a regular basis via crontab.

1. Make /etc/varnish/default.vcl look like :

   sub vcl_recv {
   	if (req.restarts == 0) {
   		if (req.http.x-forwarded-for) {
   			set req.http.X-Forwarded-For = req.http.X-Forwarded-For + ", " + client.ip;
   			}
   		else {
   			set req.http.X-Forwarded-For = client.ip;
   			}
   		}
   	}

2. Then restart Varnish

1. Enable the mod_extforward module in /etc/lighttpd/lighttpd.conf :

   server.modules = (
   	,
   	"mod_extforward",
   	
   	)

2. Tell it to search for the client IP address in the X-Forwarded-For HTTP header sent by the trusted proxy 127.0.0.1 :

   extforward.headers	= ("X-Forwarded-For")
   extforward.forwarder	= ("127.0.0.1" => "trust")

3. Then restart Lighttpd

looks like there's nothing special to do. Forget about stuff below.

To do so, all we have to do is to instruct Piwik to get the client IP from the X-Forwarded-For HTTP header. This can be done in /pathTo/piwik/documentRoot/config/config.ini.php :

proxy_client_headers[] = HTTP_X_FORWARDED_FOR

When making tests, keep in mind that Piwik can not detect Wget hits since no Javascript is executed then.