I have a website with about 50k visits/day and I’m using Piwik, because I need the realtime feature. Rendering the dashboard takes 45-60 seconds.
I have seen that Piwik has improved a lot regarding performance. I was trying to find a way to even improve it a little bit more. So I did a test with MySQL partitioning I want to share. ( MySQL :: MySQL 5.7 Reference Manual :: 22 Partitioning )
This is what I tried:
ALTER TABLE log_link_visit_action DROP PRIMARY KEY , ADD INDEX `index_id_link` ( `idlink_va` ) ;
ALTER TABLE log_link_visit_action PARTITION BY hash (TO_DAYS(server_time)) PARTITIONS 31 ;
ALTER TABLE log_visit DROP PRIMARY KEY ,ADD INDEX `id_log_visit` ( `idvisit` ) ;
ALTER TABLE log_visit PARTITION BY hash(TO_DAYS( visit_last_action_time )) PARTITIONS 31 ;
The result was some improvement, but maybe it can be even pushed further. Is MySQL partitioning something for Piwik?
Applying the changes lasted about an hour (size of table log_link_visit_action: 5.1 GB, log_visit: 3.3 Gcool smiley.
The time to render the dashboard with changes applied was about 40s
To be sure, I reverted the changes and to my surprise, it still lasted 40s. I’m not sure if caching mechanisms (memory, Mysql, Piwik) have something to do with that.
After a couple of days, I checked again and it lasted 60s
My theory was, that the two tables have “all” data from the beginning of my Piwik tracking (2009). It’s not necessary to load them all in memory (“maybe” done so by MySQL, no expert talking). So the approach was to experiment and limit it somehow by using MySQL partitions which only takes into account one day of all month. This was done without deep understanding of the Piwik DB structure, code and much knowledge of the MySQL partitioning feature. Just a test to see if it improves something and later to discuss the MySQL feature here
