Hi!
We’ve been running piwik for a month now and tried to optimize it as much as possible by following the guides you’ve provided and managed to get good speed, ~1200 recs/s.
Thanks to the nice speed we’ve been able to import a years worth of logs for a few sites, each site generating ~500MB of logs per day.
Anyway, last wednesday we noticed that we were a bit behind version wise so we decided to upgrade and now we’ve noticed a BIG drop in performance, see below:
Purging Piwik archives for dates: 2014-01-25 2014-01-26
375474 requests imported successfully
Requests imported per second: 1306.18 requests per second
Purging Piwik archives for dates: 2014-01-27 2014-01-26
502795 requests imported successfully
Requests imported per second: 1166.89 requests per second
Purging Piwik archives for dates: 2014-01-27 2014-01-28
470100 requests imported successfully
Requests imported per second: 1247.19 requests per second
Purging Piwik archives for dates: 2014-01-28 2014-01-29
466384 requests imported successfully
Requests imported per second: 187.04 requests per second
Purging Piwik archives for dates: 2014-01-30 2014-01-29
502208 requests imported successfully
Requests imported per second: 185.03 requests per second
Purging Piwik archives for dates: 2014-01-30 2014-01-31
456583 requests imported successfully
Requests imported per second: 180.38 requests per second
Purging Piwik archives for dates: 2014-01-31 2014-02-01
292815 requests imported successfully
Requests imported per second: 208.74 requests per second
We’re almost exclusively importing IIS-logs via import_logs.py (this is where we see the performance issue).
The setup:
Quad core Intel® Xeon® CPU E5-2609 v2 @ 2.50GHz
16GB ram
High speed san-disk for mysql
MySQL 5.1.69 with following settings:
symbolic-links=0
innodb_file_per_table
innodb_log_buffer_size=64M
innodb_log_file_size=512M
read_buffer_size=2M
sort_buffer_size=4M
innodb_flush_log_at_trx_commit=0
innodb_lock_wait_timeout=50
innodb_buffer_pool_size = 8589934592
default-storage-engine=innodb
key_buffer_size=67108864
query_cache_type=1
query_cache_size=67108864
query_cache_limit=2097152
thread_cache_size=128
table_open_cache=256
Server Version: Apache/2.2.15 (Unix) DAV/2 PHP/5.3.3
APC Version 3.1.9
And the only thing that changed from 28:e januari -> 29:e was that we upgraded to 2.0.3 from 1.12.
I’ve been digging around but can’t seem to figure out what’s wrong, there seem to be something that’s taking unnecessary time during the import because the load is much lower during imports now, we’ve gone from cpu @ ~70-80% to 20-30%…
Do you have any idea what might have changed that could cause this?
Best regards,
Johan