Hi,
Have anyone optimized Piwik MySQL database? Have you reach better performance in piwik queries by doing some optimization or is it waste of time? What are best practices to start piwik optimization?
Our Piwik has been run now 9 months and tracked currently 11M page views. Scheduled task is performing piwik archiving automatically on every hour. In my opinion piwik portal is smooth but product stakeholders raised this optimization question and I should investigate this.
I just run mysqltuner just want to see what it suggest to do. I don’t aggree on most suggestions but some might be ok. Square brackets will contain my thoughts.
[ul]
[li] Berkeley DB engine not installed [Ignore]
[/li][li] Federated Engine not installed [Ignore]
[/li][li] ISAM engine not installed [Ignore]
[/li][li] ndbcluster engine not installed [Ignore]
[/li][li] total fragmented tables [Usage of mysqlcheck?]
[/li][li] query cache prunes per day [Usage of mysqlcheck?]
[/li][li] table cache hit rate 0 [Usage of mysqlcheck?]
[/li][li] innodb data size / buffer pool
[/li][li] Run OPTIMIZE TABLE to degframent tables for better performance [Usage of mysqlcheck?]
[/li][li] Enable the slow query log to troubleshoot… [Ignore]
[/li][li] Increase table_cache gradually to avoid file descriptor limits [my.cnf file and mysqld-section. How to determine proper value?]
[/li][li] query_cache_size > 8M [my.cnf file and mysqld-section. How to determine proper value?]
[/li][li] table_cache > 256 [my.cnf file and mysqld-section. How to determine proper value?]
[/li][li] innodb_buffer_pool_size (>=8G) [my.cnf file and mysqld-section. How to determine proper value?]
[/li][/ul]