High traffic Piwik servers - database usage?
Posted by greenone83
| March 16, 2011 11:46PM |
That's a good question and we'd like to show such numbers on the piwik website upfront.
On demo.piwik.org here are the numbers:
Average bytes per page view: 320 bytes
So for example if after 2 years, you have tracked 10,000 pages per day on average, you will have a DB size of approximately
10000 pages * 365 days * 2 years * 320 bytes / 1000000000 = 2.33 Gb
If you track 1000 pages per day, after 2 years and keeping all data, DB size will be approx 250 MB.
Edited 3 time(s). Last edit at 04/26/2011 09:00AM by matt.
On demo.piwik.org here are the numbers:
- First important to run the archive.sh script to ensure all reports are processed and the stats below are accurate
- Total pages ~ 10 millions (number easy to find via "Database usage" page, look at piwik_log_link_visit_action table "rows count"
- Total visits ~ 3.2 millions (piwik_log_visit "rows count" in the same page)
- Websites tracked = 5
- Total DB size (logs + pre-processed reports) after 4 years: 3.2 GB
(number found at bottom of the DB Usage page)
Average bytes per page view: 320 bytes
So for example if after 2 years, you have tracked 10,000 pages per day on average, you will have a DB size of approximately
10000 pages * 365 days * 2 years * 320 bytes / 1000000000 = 2.33 Gb
If you track 1000 pages per day, after 2 years and keeping all data, DB size will be approx 250 MB.
Edited 3 time(s). Last edit at 04/26/2011 09:00AM by matt.
| March 18, 2011 10:57AM |
Summary of the problems of Piwik at large scale
Piwik works well for up to 100k-500k daily pageviews. If your website is around these values, you start might experiencing some performance issues, listed below.
EDIT Nov 2011: 1) and 2) below are now dealt with, as of Piwik 1.7, by using the new misc/cron/archive.php script instead of archive.sh
1) archive.sh memory usage reaching php error: it will probably first throw the infamous "Archiving memory exhausted error". See discussion and possible solutions in [dev.piwik.org]
2) archive.sh execution time. It is possible that we hit Mysql limits (or some other limitations/ bugs) in the system which result in very long archive.sh execution time. For example, Mysql could behave badly when the log table becomes very large and INDEXes bloated and we are trying to do a group by on a 15M row set... well it will take more than 1s
See ticket
3) High server load when Piwik is tracking data. Apparently Mysql Innodb (as you point out) seems relatively good about this, but you could hit some limit there since I've never seen Piwik with this much traffic.
Solution is already planned for next few months: write a queue and then Bulk import requests from the queue in mysql
4) Reaching Mysql performance threshold for large scale analytics
At some point, if we improve all the rest, there will still be the "Mysql" factor that will prevent good performance for largest piwik servers (millions of pages per day). We will investigate alternatives such as: InfiniDB, MongoDB, HBase, etc
Edited 4 time(s). Last edit at 12/01/2011 05:26AM by matt.
Piwik works well for up to 100k-500k daily pageviews. If your website is around these values, you start might experiencing some performance issues, listed below.
EDIT Nov 2011: 1) and 2) below are now dealt with, as of Piwik 1.7, by using the new misc/cron/archive.php script instead of archive.sh
1) archive.sh memory usage reaching php error: it will probably first throw the infamous "Archiving memory exhausted error". See discussion and possible solutions in [dev.piwik.org]
2) archive.sh execution time. It is possible that we hit Mysql limits (or some other limitations/ bugs) in the system which result in very long archive.sh execution time. For example, Mysql could behave badly when the log table becomes very large and INDEXes bloated and we are trying to do a group by on a 15M row set... well it will take more than 1s
See ticket
3) High server load when Piwik is tracking data. Apparently Mysql Innodb (as you point out) seems relatively good about this, but you could hit some limit there since I've never seen Piwik with this much traffic.
Solution is already planned for next few months: write a queue and then Bulk import requests from the queue in mysql
4) Reaching Mysql performance threshold for large scale analytics
At some point, if we improve all the rest, there will still be the "Mysql" factor that will prevent good performance for largest piwik servers (millions of pages per day). We will investigate alternatives such as: InfiniDB, MongoDB, HBase, etc
Edited 4 time(s). Last edit at 12/01/2011 05:26AM by matt.
| July 06, 2011 02:31PM |
We want to use Piwik for a Governament Agency, but reading this post seems that there are several problems with large scale monitoring.
These are actual data managed by the actual business solution:
Monitored websites: 100
Daily page views: 145.000
Daily visits: 22.000
Daily visitors: 11.000
Also they use IIS log files to read data from.
We want to know if we can replace the business solution with Piwik and, if yes, wich configuration we need to use (webservers, storages, etc.)
Thanks
Edited 1 time(s). Last edit at 12/01/2011 04:36AM by matt.
These are actual data managed by the actual business solution:
Monitored websites: 100
Daily page views: 145.000
Daily visits: 22.000
Daily visitors: 11.000
Also they use IIS log files to read data from.
We want to know if we can replace the business solution with Piwik and, if yes, wich configuration we need to use (webservers, storages, etc.)
Thanks
Edited 1 time(s). Last edit at 12/01/2011 04:36AM by matt.
| July 15, 2011 05:42PM |
leoaxet, I think that Piwik should work on your server since it is already top end. Please make sure you follow recommendations in: [piwik.org]
If you have any problem, please email me at matt att piwik.org and I can assist
If you have any problem, please email me at matt att piwik.org and I can assist
| November 27, 2011 09:00PM |
First of all for a high-traffic Piwik-system (with enough RAM) it`s absolute neccessary to use an PHP-Cache like Eaccerlator or APC. Also use the cronjob to clean your Database, don`t use the feature at the webfrontend...
I`ve also make some good experiences with InnoDB instead of the standard MyISAM-table-type of MySQL, cause with InnoDB you don`t have any locking as at MyISAM.
BUT: If your server runs with SSDs it makes no change what type you use. My server had 8 SSDs @ RAID10 at the begining, there the performance was perfect. This setup was faulty, so it was changed to SAS-HDDs, after this the performance went down.
Now some words to my setup:
I can`t exactly say since when piwik is running stable cause the server crashed many times as said before...
DB Size is 10GB. But this isn`t a problem if the table-cleanup-cron is setup correctly.
Out complete server-Net which only delivers Webpages is creating 30TB traffic per month.
And this is our server (only piwik with his DB is running on this):
Traffic:
Can`t say it exactly, cause the backup is also included in this stat:
If someone want`s to see the System-Load over one Year, just ask...
@PR0FESSI0NAL:
If you want we can get in touch via e-mail, as i`ve said i`ve got some experience in this nice kind of application-hosting... I`m not associated with any Hardware-OEM or some sort of this. Just a fat piwik-site (in my opinion) and some other fat solutions running.
Edited 4 time(s). Last edit at 11/27/2011 11:16PM by tompal.
I`ve also make some good experiences with InnoDB instead of the standard MyISAM-table-type of MySQL, cause with InnoDB you don`t have any locking as at MyISAM.
BUT: If your server runs with SSDs it makes no change what type you use. My server had 8 SSDs @ RAID10 at the begining, there the performance was perfect. This setup was faulty, so it was changed to SAS-HDDs, after this the performance went down.
Now some words to my setup:
I can`t exactly say since when piwik is running stable cause the server crashed many times as said before...
DB Size is 10GB. But this isn`t a problem if the table-cleanup-cron is setup correctly.
Out complete server-Net which only delivers Webpages is creating 30TB traffic per month.
mysql> SELECT COUNT(*) FROM piwik_log_link_visit_action; +-----------+ | COUNT(*) | +-----------+ | 215886125 | +-----------+
mysql> SELECT COUNT(*) FROM piwik_log_visit; +----------+ | COUNT(*) | +----------+ | 26695846 | +----------+ 1 row in set (0.09 sec)
And this is our server (only piwik with his DB is running on this):
analytics:~ # free -m
total used free shared buffers cached
Mem: 64432 49550 14881 0 584 24893
-/+ buffers/cache: 24073 40359
Swap: 10244 270 9973
analytics:~ # cat /proc/cpuinfo
......
processor : 7
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5472 @ 3.00GHz
stepping : 6
cpu MHz : 2999.852
....
8 x SEAGATE ST973452SS (RAID10) @ LSI MegaSAS 9260
Traffic:
Can`t say it exactly, cause the backup is also included in this stat:
RX bytes:837060327167 (798282.9 Mb) TX bytes:1686983246572 (1608832.5 Mb)
8:56pm up 95 days 11:29, 1 user, load average: 26.21, 21.64, 32.00
Cool, at the moment my sites have many visits
If someone want`s to see the System-Load over one Year, just ask...
@PR0FESSI0NAL:
If you want we can get in touch via e-mail, as i`ve said i`ve got some experience in this nice kind of application-hosting... I`m not associated with any Hardware-OEM or some sort of this. Just a fat piwik-site (in my opinion) and some other fat solutions running.
Edited 4 time(s). Last edit at 11/27/2011 11:16PM by tompal.
| November 28, 2011 02:07AM |
Excellent to see you here tompal, will you break the 1 Billion pages tracked with Piwik??
If you can achieve such amount of tracking with normal hard disks, normal Mysql and a Piwik without hacks, I'm impressed, and this is great news!
1) Could you post a screenshot of all tables row count/sizes ? I would love to see the data reparition on such a high traffic setup. Maybe this is private, you know my email.
2) What does this query return: mysql> SELECT COUNT(*) FROM piwik_log_action;
3) What is your exact server setup to produce the screenshot above? Not sure if "SAS-HDDs" are normal hdd? I looked up the hard disk which don't seem SSD? [www.seagate.com]
4) how long does the archiving job takes to run every day or every hour?"
> If someone want`s to see the System-Load over one Year, just ask...
Over 1 month / 1 week would be great
Thanks!!
If you can achieve such amount of tracking with normal hard disks, normal Mysql and a Piwik without hacks, I'm impressed, and this is great news!
1) Could you post a screenshot of all tables row count/sizes ? I would love to see the data reparition on such a high traffic setup. Maybe this is private, you know my email.
2) What does this query return: mysql> SELECT COUNT(*) FROM piwik_log_action;
3) What is your exact server setup to produce the screenshot above? Not sure if "SAS-HDDs" are normal hdd? I looked up the hard disk which don't seem SSD? [www.seagate.com]
4) how long does the archiving job takes to run every day or every hour?"
> If someone want`s to see the System-Load over one Year, just ask...
Over 1 month / 1 week would be great
Thanks!!
| November 28, 2011 12:49PM |
Hey Matt,
of course i will break them.
I`ve done no hacks to the Piwik-source...
1)
2)
3) The above posted hardware. ST973452SS are SAS-(SATA with Enterprise-features)-HDDs with 15k RPM. First the server was equipped with 8 SSDs at RAID10, but after this caused some problems the SSDs were replaced by the Seagates.
4) Puuh, good question... I will have a look at this this week
of course i will break them.
I`ve done no hacks to the Piwik-source...
1)
mysql> SHOW TABLE STATUS FROM analytics; +-------------------------------+--------+---------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+ | Name | Engine | Version | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | +-------------------------------+--------+---------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+ | piwik_access | MyISAM | 10 | 59 | 24 | 1424 | 281474976710655 | 3072 | 0 | NULL | | piwik_archive_blob_2010_01 | MyISAM | 10 | 150 | 72 | 10900 | 281474976710655 | 9216 | 0 | NULL | | piwik_archive_blob_2010_04 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2010_05 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2010_06 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2010_07 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2010_08 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2010_09 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2010_10 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2010_11 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2010_12 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2011_01 | MyISAM | 10 | 56759 | 323 | 18348716 | 281474976710655 | 1624064 | 0 | NULL | | piwik_archive_blob_2011_02 | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_archive_blob_2011_03 | MyISAM | 10 | 17136 | 512 | 8780052 | 281474976710655 | 543744 | 0 | NULL | | piwik_archive_blob_2011_04 | MyISAM | 10 | 115728 | 446 | 51645960 | 281474976710655 | 3517440 | 0 | NULL | | piwik_archive_blob_2011_05 | MyISAM | 10 | 257690 | 369 | 95334972 | 281474976710655 | 7501824 | 0 | NULL | | piwik_archive_blob_2011_06 | MyISAM | 10 | 399993 | 318 | 127356088 | 281474976710655 | 11375616 | 0 | NULL | | piwik_archive_blob_2011_07 | MyISAM | 10 | 424127 | 306 | 130037956 | 281474976710655 | 11993088 | 0 | NULL | | piwik_archive_blob_2011_08 | MyISAM | 10 | 462804 | 328 | 152242184 | 281474976710655 | 16097280 | 0 | NULL | | piwik_archive_blob_2011_09 | MyISAM | 10 | 35915 | 342 | 12313328 | 281474976710655 | 1330176 | 0 | NULL | | piwik_archive_blob_2011_10 | MyISAM | 10 | 211355 | 368 | 77829352 | 281474976710655 | 6288384 | 0 | NULL | | piwik_archive_blob_2011_11 | MyISAM | 10 | 228943 | 371 | 84967216 | 281474976710655 | 6769664 | 0 | NULL | | piwik_archive_numeric_2010_01 | MyISAM | 10 | 314 | 46 | 14624 | 281474976710655 | 43008 | 0 | NULL | | piwik_archive_numeric_2010_04 | MyISAM | 10 | 347 | 40 | 13880 | 281474976710655 | 32768 | 0 | NULL | | piwik_archive_numeric_2010_05 | MyISAM | 10 | 711 | 40 | 28440 | 281474976710655 | 53248 | 0 | NULL | | piwik_archive_numeric_2010_06 | MyISAM | 10 | 700 | 40 | 28000 | 281474976710655 | 53248 | 0 | NULL | | piwik_archive_numeric_2010_07 | MyISAM | 10 | 720 | 40 | 28800 | 281474976710655 | 54272 | 0 | NULL | | piwik_archive_numeric_2010_08 | MyISAM | 10 | 740 | 40 | 29600 | 281474976710655 | 55296 | 0 | NULL | | piwik_archive_numeric_2010_09 | MyISAM | 10 | 713 | 40 | 28520 | 281474976710655 | 53248 | 0 | NULL | | piwik_archive_numeric_2010_10 | MyISAM | 10 | 756 | 40 | 30240 | 281474976710655 | 56320 | 0 | NULL | | piwik_archive_numeric_2010_11 | MyISAM | 10 | 757 | 40 | 30280 | 281474976710655 | 56320 | 0 | NULL | | piwik_archive_numeric_2010_12 | MyISAM | 10 | 789 | 40 | 31560 | 281474976710655 | 58368 | 0 | NULL | | piwik_archive_numeric_2011_01 | MyISAM | 10 | 1265 | 44 | 56152 | 281474976710655 | 95232 | 0 | NULL | | piwik_archive_numeric_2011_02 | MyISAM | 10 | 817 | 40 | 32680 | 281474976710655 | 59392 | 0 | NULL | | piwik_archive_numeric_2011_03 | MyISAM | 10 | 3265 | 50 | 164688 | 281474976710655 | 246784 | 0 | NULL | | piwik_archive_numeric_2011_04 | MyISAM | 10 | 13378 | 53 | 717732 | 281474976710655 | 1025024 | 0 | NULL | | piwik_archive_numeric_2011_05 | MyISAM | 10 | 15305 | 53 | 824628 | 281474976710655 | 1172480 | 0 | NULL | | piwik_archive_numeric_2011_06 | MyISAM | 10 | 15150 | 53 | 816788 | 281474976710655 | 1163264 | 0 | NULL | | piwik_archive_numeric_2011_07 | MyISAM | 10 | 15143 | 53 | 814720 | 281474976710655 | 1160192 | 0 | NULL | | piwik_archive_numeric_2011_08 | MyISAM | 10 | 16115 | 53 | 865968 | 281474976710655 | 1579008 | 0 | NULL | | piwik_archive_numeric_2011_09 | MyISAM | 10 | 2601 | 49 | 129156 | 281474976710655 | 244736 | 0 | NULL | | piwik_archive_numeric_2011_10 | MyISAM | 10 | 10971 | 53 | 585224 | 281474976710655 | 873472 | 0 | NULL | | piwik_archive_numeric_2011_11 | MyISAM | 10 | 13733 | 53 | 739000 | 281474976710655 | 1053696 | 0 | NULL | | piwik_goal | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_log_action | MyISAM | 10 | 5317721 | 99 | 529694916 | 281474976710655 | 147832832 | 0 | 5317722 | | piwik_log_conversion | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_log_conversion_item | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_log_link_visit_action | MyISAM | 10 | 219105115 | 66 | 14570043584 | 281474976710655 | 8588289024 | 0 | 219605116 | | piwik_log_profiling | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | | piwik_log_visit | MyISAM | 10 | 26654859 | 186 | 4980708308 | 281474976710655 | 1538563072 | 0 | 27154860 | | piwik_logger_api_call | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | | piwik_logger_error | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | | piwik_logger_exception | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | | piwik_logger_message | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | | piwik_option | MyISAM | 10 | 67 | 46 | 3156 | 281474976710655 | 6144 | 44 | NULL | | piwik_pdf | MyISAM | 10 | 2 | 272 | 544 | 281474976710655 | 2048 | 0 | 3 | | piwik_session | MyISAM | 10 | 121 | 291 | 381020 | 281474976710655 | 81920 | 345796 | NULL | | piwik_site | MyISAM | 10 | 26 | 68 | 1780 | 281474976710655 | 2048 | 0 | 28 | | piwik_site_url | MyISAM | 10 | 15 | 34 | 512 | 281474976710655 | 5120 | 0 | NULL | | piwik_user | MyISAM | 10 | 9 | 112 | 1012 | 281474976710655 | 4096 | 0 | NULL | | piwik_user_dashboard | MyISAM | 10 | 5 | 2020 | 10104 | 281474976710655 | 3072 | 0 | NULL | | piwik_user_language | MyISAM | 10 | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | +-------------------------------+--------+---------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+ 62 rows in set (0.00 sec)
2)
mysql> SELECT COUNT(*) FROM piwik_log_action; +----------+ | COUNT(*) | +----------+ | 5318820 | +----------+
3) The above posted hardware. ST973452SS are SAS-(SATA with Enterprise-features)-HDDs with 15k RPM. First the server was equipped with 8 SSDs at RAID10, but after this caused some problems the SSDs were replaced by the Seagates.
4) Puuh, good question... I will have a look at this this week
| February 16, 2012 11:17PM |
Gigius, 20M pages per month, should be possible with Piwik. It is hard to say what server config to use etc. but check out this guide which contain all tips necessary: [piwik.org]
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
| April 27, 2012 07:17AM |
Hello everyone, My installation:
Total Actions: 0.8 million
Total Visits: 510.000
Total Visits including bots:470,000
Websites tracked: 2
Total DB Size: 3.6GB
Thanks
Total Actions: 0.8 million
Total Visits: 510.000
Total Visits including bots:470,000
Websites tracked: 2
Total DB Size: 3.6GB
Thanks
| May 18, 2012 05:10AM |
With so much data it's better to archive only once or twice a day, which will also decrease the DB size.
There is also a bug in Piwik causing that some temporary data is not correctly deleted, so when we fix this soon you will notice DB size improvements: [dev.piwik.org]
In the next release there will be a feature to delete old reports as welll so for example you could delete daily reports after a few weeks.
How many visits / pages per day do you track on these 22 websites?
Maybe try to remove the biggest websites until the situation is stable and the bug fixed?
Finally int he next release 1.7.2 there will be much improved database usage screen which will tell us exactly what reports are using more data so we can help you debug more.
hopefully piwik will work for you eventually!
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
There is also a bug in Piwik causing that some temporary data is not correctly deleted, so when we fix this soon you will notice DB size improvements: [dev.piwik.org]
In the next release there will be a feature to delete old reports as welll so for example you could delete daily reports after a few weeks.
How many visits / pages per day do you track on these 22 websites?
Maybe try to remove the biggest websites until the situation is stable and the bug fixed?
Finally int he next release 1.7.2 there will be much improved database usage screen which will tell us exactly what reports are using more data so we can help you debug more.
hopefully piwik will work for you eventually!
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
| May 18, 2012 05:50AM |
It's about 4,5M visit and more than 28M pageview a day. Our goal is make this number double 
- We already have an simple python script for bulk import request from scribe. This script is customized from pycurl (http://pycurl.cvs.sourceforge.net/pycurl/pycurl/examples/retriever-multi.py?view=markup). Everything is fine until the archive process.
- It's not about the archive table, it's about the raw log table.
- The problem come from 200 million record from log_link_visit_action table. Is it necessary to keep this table after daily archive?
- Another question can we is deleting log_visit weekly but still get correct unique vistor?
I think we should create a raw data buffer table for realtime report , and re-calculate it with normal archive script once a day.
We hope to share our knowledge to make a better world with floss.
http://forum.ubuntu-vn.org - ubuntu linux for human beings
- We already have an simple python script for bulk import request from scribe. This script is customized from pycurl (http://pycurl.cvs.sourceforge.net/pycurl/pycurl/examples/retriever-multi.py?view=markup). Everything is fine until the archive process.
- It's not about the archive table, it's about the raw log table.
- The problem come from 200 million record from log_link_visit_action table. Is it necessary to keep this table after daily archive?
- Another question can we is deleting log_visit weekly but still get correct unique vistor?
I think we should create a raw data buffer table for realtime report , and re-calculate it with normal archive script once a day.
We hope to share our knowledge to make a better world with floss.
http://forum.ubuntu-vn.org - ubuntu linux for human beings
| May 21, 2012 09:03PM |
afterlastangel,
> We already have an simple python script for bulk import request from scribe.
If possible it would be good to see the script maybe oither users would find it useful
> Is it necessary to keep this table after daily archive?
You can delete the log_link_visit_action table after daily archive
> can we is deleting log_visit weekly but still get correct unique vistor?
If you delete logs older than 7 days you will still get weekly unique visitors, but not monthly or yearly.
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
> We already have an simple python script for bulk import request from scribe.
If possible it would be good to see the script maybe oither users would find it useful
> Is it necessary to keep this table after daily archive?
You can delete the log_link_visit_action table after daily archive
> can we is deleting log_visit weekly but still get correct unique vistor?
If you delete logs older than 7 days you will still get weekly unique visitors, but not monthly or yearly.
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
| June 03, 2012 05:23AM |
In the new Piwik release there is a new tool very useful for large traffic Piwik.
Enable DBStats plugin, click on "Database usage" to access the full database usage report!
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
Enable DBStats plugin, click on "Database usage" to access the full database usage report!
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
| June 06, 2012 10:27AM |
Quote
afterlastangel
It's about 4,5M visit and more than 28M pageview a day. Our goal is make this number double
If piwik can handle up to 500k pageviews per day, how did you manage to handle 28M per day? I have to manage about 20M pageviews per day and for each one of them I need to track 5 custom variables. As far as I understood from this forum, this is quite difficult.
Edited 1 time(s). Last edit at 06/06/2012 11:12AM by useless.
| June 12, 2012 06:15PM |
Thanks Matt for great improvement in new version 1.8.2. Our website now tracking 39M pageviews/22 websites everyday with 4 servers (1 gateway, 2 importer/reporter, 1 SQL D
.
@useless: Let me share with you some ideas, maybe you may find it helpful.
- In Web gateway, replace web gateway with our built-in web service + scribed to received logs
- In importer/reported, run job queue to incremental import logs to Piwik
- Run archive and rotate table piwik_log_link_visit every hour for minimize row counts.
That's it.
.
@useless: Let me share with you some ideas, maybe you may find it helpful.
- In Web gateway, replace web gateway with our built-in web service + scribed to received logs
- In importer/reported, run job queue to incremental import logs to Piwik
- Run archive and rotate table piwik_log_link_visit every hour for minimize row counts.
That's it.
| June 17, 2012 09:00PM |
@baysao fantastic to hear.
- "scribed to received logs " do you import the web server logs, or do you import log of requests to piwik.php?
- if you rotate table piwik_log_link_visit then you will lose data, are you sure it's OK to rotate these?
do you run the full archiving on the 39M pages per day?
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
- "scribed to received logs " do you import the web server logs, or do you import log of requests to piwik.php?
- if you rotate table piwik_log_link_visit then you will lose data, are you sure it's OK to rotate these?
do you run the full archiving on the 39M pages per day?
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
| June 19, 2012 03:42PM |
- "scribed to received logs " i mean write all requests sent by js script to disk through [github.com] and have job queue to import log data on disk to piwik.php
This sounds VERY interesting. Would you please consider releasing this How to by email to us or publicly on the piwik forums? I'm sure many users will love to do the same!
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
This sounds VERY interesting. Would you please consider releasing this How to by email to us or publicly on the piwik forums? I'm sure many users will love to do the same!
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
| June 21, 2012 11:55AM |
Thanks for the diagram!
Wuold you be interested or willing to share the scripts used for Scribed/inotififywait and the python importer?
Ideally we would really like to put such scripts in the official piwik package. Please contact me at matt@piwik.org if youre keen to work together
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
Wuold you be interested or willing to share the scripts used for Scribed/inotififywait and the python importer?
Ideally we would really like to put such scripts in the official piwik package. Please contact me at matt@piwik.org if youre keen to work together
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
| July 09, 2012 01:25PM |
My installation:
Total Actions: 0.9 million
Total Visits: 217.000
Total Visits including bots:480,000
Websites tracked: 2
Total DB Size: 2.9GB
Newbie Piwiker http://www.tektasim.com.tr
Edited 2 time(s). Last edit at 07/09/2012 01:31PM by Pırlanta.
Total Actions: 0.9 million
Total Visits: 217.000
Total Visits including bots:480,000
Websites tracked: 2
Total DB Size: 2.9GB
Newbie Piwiker
http://www.tektasim.com.trEdited 2 time(s). Last edit at 07/09/2012 01:31PM by Pırlanta.
| July 24, 2012 10:05PM |
We are running Piwik 1.6 since 6 month now, THANK YOU to the dev team for the excelent job!
We run the web server on CENTOS 6.2 using nginx and php-cgi 5.3.3 (10 childs) on a VM configured with 8GB ram and 4cores, the database is on a windows 2008 server, mysql 5.5 64bit, 16GB RAM, 2 CPU x4 core 3GHz.
We track around 5 million visit per month; average of 25 million page views over 5 main sites and currently our Database size is 29GB.
Database CPU usage is not noticable high mysqld process shows around 2.7 to 3GB RAM usage, VM load is around 2, CPU peaks at 60%.
The main problem we are facing is that archive.sh is crashing regulary , at least every second time it runs, but only from cron, running it on the command line so far always worked We have set the php memory limit to 1GB and we use apc but still the archiver ... 
Second worry is the backup of the database, mysqldump takes 30min and makes the server very slow and sluggish. Anyone with any good ideas for a better backup solution then mysqldump?
Regards Mike
We run the web server on CENTOS 6.2 using nginx and php-cgi 5.3.3 (10 childs) on a VM configured with 8GB ram and 4cores, the database is on a windows 2008 server, mysql 5.5 64bit, 16GB RAM, 2 CPU x4 core 3GHz.
We track around 5 million visit per month; average of 25 million page views over 5 main sites and currently our Database size is 29GB.
Database CPU usage is not noticable high mysqld process shows around 2.7 to 3GB RAM usage, VM load is around 2, CPU peaks at 60%.
The main problem we are facing is that archive.sh is crashing regulary , at least every second time it runs, but only from cron, running it on the command line so far always worked
We have set the php memory limit to 1GB and we use apc but still the archiver ...
Second worry is the backup of the database, mysqldump takes 30min and makes the server very slow and sluggish. Anyone with any good ideas for a better backup solution then mysqldump?
Regards Mike
| July 25, 2012 12:22AM |
Mike, thanks for your comment here - very appreciated!
My first recommendation would be to update to 1.8.2 and use the new optimized archive.php script (the .sh still work but is slower since archive.php only runs the required process and does not call unncessarily the API) - could you try the update and then run archive.php?
Also if you have 8G of ram, you can safely increase php limit to 2G or 3G (even though we all agree this is ridiculous- we have some plans to make it better)
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
My first recommendation would be to update to 1.8.2 and use the new optimized archive.php script (the .sh still work but is slower since archive.php only runs the required process and does not call unncessarily the API) - could you try the update and then run archive.php?
Also if you have 8G of ram, you can safely increase php limit to 2G or 3G (even though we all agree this is ridiculous- we have some plans to make it better)
Please READ before posting a new topic
Search Piwik Forum & Docs - Piwik FAQ - Piwik Help - Stay tuned on the Piwik Blog. I'm on twitter & on github
New! Help : fund an AWESOME new feature for Piwik (Custom Segmentations Editor + Apply in Real Time!), we need your help!
New: Sponsor a feature or work new plugin. Contact us now with your budget, timeline, and which new feature you would like to fund we will let you know the quote.
| September 17, 2012 12:11PM |
Hello Matt,
just got around to some test to upgrade piwik to 1.8.4. The new setup is running Centos 6.3, php 5.3 using php-fpm for php processing and mysql 5.5 (innodb), the system has a iCore 7 - 2600, 16GB RAM and a 256GB SSD.
piwik_log_link_visit_action = 180 million record
piwik_log_visit = 30 million record
innodb size on disk ~ 35GB
Regularly delete old visitor logs from the database is enabled and set to 180 days.
Here is my feedback; currently only tested the db reload and update, not yet in live production. Biggest issue before upgrading the stock Centos Mysql 5.1 package was recovery time... The piwik_log_link_visit_action table took 3 hours to restore and piwik_log_visit estimated 10 hours!! After upgrade to 5.5, piwik_log_visit restored in 2,5 hours and piwik_log_link_visit_action in 2 hours.
Piwik DB update script run for 1,5 hours to updated the databases. The archive script completes in under 5 minutes, but has shown a error, saying PIWIK API not available doing "Starting Scheduled tasks" ... error was to fast to catch ;( will try it again this evening.
i hope to have it in production this week then i can give you some feedback on performance. But i strongly advice everyone to upgrade mysql to 5.5 it makes a huge difference in performance as far as restores go.
Regards Mike
just got around to some test to upgrade piwik to 1.8.4. The new setup is running Centos 6.3, php 5.3 using php-fpm for php processing and mysql 5.5 (innodb), the system has a iCore 7 - 2600, 16GB RAM and a 256GB SSD.
piwik_log_link_visit_action = 180 million record
piwik_log_visit = 30 million record
innodb size on disk ~ 35GB
Regularly delete old visitor logs from the database is enabled and set to 180 days.
Here is my feedback; currently only tested the db reload and update, not yet in live production. Biggest issue before upgrading the stock Centos Mysql 5.1 package was recovery time... The piwik_log_link_visit_action table took 3 hours to restore and piwik_log_visit estimated 10 hours!! After upgrade to 5.5, piwik_log_visit restored in 2,5 hours and piwik_log_link_visit_action in 2 hours.
Piwik DB update script run for 1,5 hours to updated the databases. The archive script completes in under 5 minutes, but has shown a error, saying PIWIK API not available doing "Starting Scheduled tasks" ... error was to fast to catch ;( will try it again this evening.
i hope to have it in production this week then i can give you some feedback on performance. But i strongly advice everyone to upgrade mysql to 5.5
it makes a huge difference in performance as far as restores go.
Regards Mike
Sorry, only registered users may post in this forum.
Free Forum support is provided by the Piwik Community.
If you require any urgent or professional help, contact Piwik Professional Services team!
