{"action":"created","issue":{"url":"https://api.github.com/repos/piwik/piwik/issues/7373","labels_url":"https://api.github.com/repos/piwik/piwik/issues/7373/labels{/name}","comments_url":"https://api.github.com/repos/piwik/piwik/issues/7373/comments","events_url":"https://api.github.com/repos/piwik/piwik/issues/7373/events","html_url":"https://github.com/piwik/piwik/issues/7373","id":60019550,"number":7373,"title":"Store blob archives more efficient","user":{"login":"tsteur","id":273120,"avatar_url":"https://avatars.githubusercontent.com/u/273120?v=3","gravatar_id":"","url":"https://api.github.com/users/tsteur","html_url":"https://github.com/tsteur","followers_url":"https://api.github.com/users/tsteur/followers","following_url":"https://api.github.com/users/tsteur/following{/other_user}","gists_url":"https://api.github.com/users/tsteur/gists{/gist_id}","starred_url":"https://api.github.com/users/tsteur/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tsteur/subscriptions","organizations_url":"https://api.github.com/users/tsteur/orgs","repos_url":"https://api.github.com/users/tsteur/repos","events_url":"https://api.github.com/users/tsteur/events{/privacy}","received_events_url":"https://api.github.com/users/tsteur/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/piwik/piwik/labels/c:%20Performance","name":"c: Performance","color":"ededed"},{"url":"https://api.github.com/repos/piwik/piwik/labels/Enhancement","name":"Enhancement","color":"bfd4f2"}],"state":"open","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/piwik/piwik/milestones/73","html_url":"https://github.com/piwik/piwik/milestones/Piwik%202.12.0","labels_url":"https://api.github.com/repos/piwik/piwik/milestones/73/labels","id":852680,"number":73,"title":"Piwik 2.12.0","description":"This release ideally should not include DB schema upgrades.\r\n","creator":{"login":"mattab","id":466765,"avatar_url":"https://avatars.githubusercontent.com/u/466765?v=3","gravatar_id":"","url":"https://api.github.com/users/mattab","html_url":"https://github.com/mattab","followers_url":"https://api.github.com/users/mattab/followers","following_url":"https://api.github.com/users/mattab/following{/other_user}","gists_url":"https://api.github.com/users/mattab/gists{/gist_id}","starred_url":"https://api.github.com/users/mattab/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mattab/subscriptions","organizations_url":"https://api.github.com/users/mattab/orgs","repos_url":"https://api.github.com/users/mattab/repos","events_url":"https://api.github.com/users/mattab/events{/privacy}","received_events_url":"https://api.github.com/users/mattab/received_events","type":"User","site_admin":false},"open_issues":43,"closed_issues":3,"state":"open","created_at":"2014-11-04T11:19:45Z","updated_at":"2015-03-06T22:59:25Z","due_on":"2015-03-18T11:00:00Z","closed_at":null},"comments":2,"created_at":"2015-03-05T21:32:54Z","updated_at":"2015-03-07T01:39:51Z","closed_at":null,"body":"Current solution is to store each datatable in one archive_blob, and each subtable in a separate archive_blob. If a datatable has 1000 subtables (which is not unusual), we create 1001 rows in the archive table for this single report.\r\n\r\nNew solution would be to store max 2 blobs per archive: One for the first level datatable, and another one combined for all subtables. We do not store them combined in one blob entry to have still max performance for first level datatables while loading a subtable will be slightly slower since we will have to unserialize all rows. Storing all subtables in one row will not only result in less entries in the blob table and therefore a lower index size, it will also result in a lower data size (less storage needed) as GZIP can compress much better. For example currently when storing only one row per subtable, we do store `Piwik\\DataTable\\TableViewRow` 1000 times whereas afterwards it will be only once since gzip does not deal with 1000 different compressed entries but one.\r\n\r\nStoring the blob in one main blob and one for subtables will also allow us at some point for example to allow users to delete all archives for subtables but not for the first level tables.\r\n\r\nHere are some results of a comparsion for one site. I did track about 100.000 pageviews on one day and archive it afterwards with old behavior and with new, suggested behavior:\r\n\r\n### SINGLE SUBTABLE ENTRIES (OLD BEHAVIOUR)\r\n0 visits in last last2 days, 0 visits today, Time elapsed: 0.708s\r\n14210 visits in last last2 weeks, 0 visits this week, Time elapsed: 57.050s\r\n14210 visits in last last2 months, 0 visits this month, Time elapsed: 7.707s\r\n14210 visits in last last2 years, 14210 visits this year, Time elapsed: 0.694s\r\nTime elapsed: 66.168s [1/1 done]\r\n\r\n#### Archive 2015_02 \r\n| Rows in blob table  | Index size | Data size | \r\n| ------------- |  -------------  | ------------- |\r\n| 4.731  |  400K | 5.5MB |\r\n\r\n\r\n### WITH SUBTABLES GROUPED (NEW BEHAVIOUR)\r\n\r\n0 visits in last last2 days, 0 visits today, Time elapsed: 0.831s\r\n14210 visits in last last2 weeks, 0 visits this week, Time elapsed: 61.312s\r\n14210 visits in last last2 months, 0 visits this month, Time elapsed: 9.312s\r\n14210 visits in last last2 years, 14210 visits this year, Time elapsed: 6.646s\r\nTime elapsed: 78.122s [1/1 done]\r\n\r\n#### Archive 2015_02 \r\n| Rows in blob table  | Index size | Data size | \r\n| ------------- | -------------  | ------------- |\r\n| 165  | 16K | 3.5MB |\r\n\r\n\r\n### Archives comparison\r\n* 30 times less rows!\r\n* Index size is 30 times smaller! (400K vs 16KB)\r\n* Data size is halved (5.5MB vs 3.5MB)\r\n* Archiving is a bit slower with less data but probably 5% faster the more archives there are in there (could be also by accident, don't think there will be huge improvement. While we have to do less index work and row writing we do have a bit more serialize work todo)\r\n\r\nThe advantage will be even more clear when also having more other reports with subtables. For example there were no events tracked, no content tracking. So the factor can grow from 30 times to 50 times. On the other side if one has only a few subtables the advantage will be less clear.\r\n\r\n### Reading comparison\r\n* Building expanded datatable (25k rows, 800 subtables)\r\n * Speed pretty much the same (about 500ms with xhprof on my local instance)\r\n * Same memory usage\r\n* Building 1st level datatable: \r\n * Speed is the same (about 10ms with xhprof)\r\n * Same memory usage\r\n* Building a subtable datatable: \r\n * Speed is slower (12ms vs 40ms) as there is more data to uncompress (0.175ms vs 26.970ms) and to unserialize (0 vs 3.561ms). Background: we have to unserialize the blob that contains all subtables first before we can access the serialized subtable that we are interested in.\r\n * Peak memory usage 13MB vs 28MB (new method needs more memory since we have to unserialize all blobs into one array which contains the `[idSubtable] => [serialized subtable rows]`)\r\n * Incl. memory usage is always 13.2MB\r\n\r\nExpanded and first level reports are same in time needed to load archive and create datatable. Subtable reports are slightly slower and have a higher peak memory. Once there are more and more archives in a blob table the new method should become a bit faster as the index will be smaller in Mysql, there will be less data to transfer from mysql to php, there will be less archives to parse in PHP, ...\r\n\r\nEg when having on a test server 2GB of blob entries, 1.6M rows and 300MB index size could go down to 1GB storage, 50K rows and 10MB index size.\r\n\r\n\r\n\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/piwik/piwik/issues/comments/77666139","html_url":"https://github.com/piwik/piwik/issues/7373#issuecomment-77666139","issue_url":"https://api.github.com/repos/piwik/piwik/issues/7373","id":77666139,"user":{"login":"tsteur","id":273120,"avatar_url":"https://avatars.githubusercontent.com/u/273120?v=3","gravatar_id":"","url":"https://api.github.com/users/tsteur","html_url":"https://github.com/tsteur","followers_url":"https://api.github.com/users/tsteur/followers","following_url":"https://api.github.com/users/tsteur/following{/other_user}","gists_url":"https://api.github.com/users/tsteur/gists{/gist_id}","starred_url":"https://api.github.com/users/tsteur/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tsteur/subscriptions","organizations_url":"https://api.github.com/users/tsteur/orgs","repos_url":"https://api.github.com/users/tsteur/repos","events_url":"https://api.github.com/users/tsteur/events{/privacy}","received_events_url":"https://api.github.com/users/tsteur/received_events","type":"User","site_admin":false},"created_at":"2015-03-07T01:39:51Z","updated_at":"2015-03-07T01:39:51Z","body":"> Maybe there could be a special value used to look for in the serialized string (ie, something that won't normally occur).\r\n\r\nThought about this already but people could use any string on purpose in tracking requests, eg pageviews and break any archive?\r\n\r\n> To speed up single subtable reading, maybe you could grep through the serialized string for the [idSubtable] indexes? \r\n\r\nThat could be possible but probably could have same issue as above?\r\n\r\nBackground: To serialize subtables into one blob I currently do the following\r\n\r\n```php\r\n$combinedSubtableBlob = serialize(array(1 => 'serialized subtable', 2 => 'serialized subtable3', $subtableId => $serializedSubtable....));\r\n```\r\n\r\nThen when reading the subtable archive:\r\n```php\r\n$subtables = unserialize(combinedSubtableBlob); // array that holds all serialized subtables\r\n```\r\n\r\nI'm not really concerned about those 20ms on top as it will be faster on a fast CPU, and it will be faster when having less subtables (this test had 800 subtables which is often less) but it could be as well more when having eg 1600 subtables. Would be nice to make it faster though! Hope to test it on a fast server next week"}}