Hi,
I currently have 2 piwik installations : 1 for my small websites (which is working perfectly) and 1 dedicated for my biggest website. This particular website has a tons of URLs since it’s mainly legal laws and judgments that everyone can access online. My installation currently has a hard time to generate the stats for this website. It always times out with the database. Right now, I’ve been able to finally processed all stats from november, but it took almost 1 day to generate them. There’s approximately 800k hits on this site per month, which is not that much considering I’ve seen other posts with way more than that.
Is there anything I can configure to help piwik process faster the stats? Right now, my cron runs like this :
0 2-21 * * * apache /usr/bin/php /home/piwik/current/console core:archive --url=http://piwik.canlii.org/ --force-periods=day >> /home/piwik/current/piwik-archive-daily.log
15 22 * * * apache /usr/bin/php /home/piwik/current/console core:archive --url=http://piwik.canlii.org/ --force-periods=week >> /home/piwik/current/piwik-archive-week.log
30 23 * * * apache /usr/bin/php /home/piwik/current/console core:archive --url=http://piwik.canlii.org/ --force-periods=month >> /home/piwik/current/piwik-archive-month.log
30 9 * * sat apache /usr/bin/php /home/piwik/current/console core:archive --url=http://piwik.canlii.org/ --force-periods=year >> /home/piwik/current/piwik-archive-year.log
The whole database is running on a PCI-e SSD drive, and the CPU is way than enough for it’s purpose (2x8 CPU’s Intel E5-2667V2).
I’ll paste in too my my.cnf as I’m not a mysql expert
[mysqld]
Remove leading # and set to the amount of RAM for the most important data
cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
Remove leading # to turn on a very important data integrity option: logging
changes to the binary log between backups.
log_bin
Remove leading # to set options mainly useful for reporting servers.
The server defaults are faster for transactions and fast SELECTs.
Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir = /fastdrive/mysql
socket = /fastdrive/mysql/mysql.sock
log-bin = /fastdrive/mysql/mysql-bin
max_allowed_packet = 512M
wait_timeout=40800
interactive_timeout = 28800
#interactive_timeout=30
log_warnings = 2
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
datadir=/fastdrive/mysql
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket = /fastdrive/mysql/mysql.sock
Thanks
KP