Piwik archive via Cron


#1

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


#2

My my.cnf is there (2 M pageviews a month) if it can help :

http://forum.piwik.org/read.php?6,117933,119198#msg-119198


#3

Thanks for your reply! I will take a look at it as soon as I can.

KP