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?