How to find Archived status (SQL Query?)

Barry · May 12, 2016, 7:45pm

Hi, we’ve got a Piwik system tracking a WordPress Multi-Site (62k sites) system for the last 7 years… as far as I can tell, the archiving cron jobs were never setup…

There’s 2M rows in piwik_log_action, and 17M in piwik_log_link_visit_action.

I’ve now stopped statistics recording and have been running the archive script on the command-line, but it’s been running for about 20 hours and I have no idea when it may finish…

So, I’m wondering is there a SQL query I can run to see how many sites are finished archiving, and how many are left?

Thanks,
Barry

matthieu · July 1, 2016, 3:14am

Are you using latest Piwik version? the core:archive command is very explicit and shows the progress of archiving.

Barry · July 1, 2016, 5:11pm

Hi, when I run
./console core:archive --concurrent-requests-per-website=6

I only see status for each site as it runs. I see no way to tell how many sites are completed and how many are remaining.

With 62,000 sites, this is hard to manage.

Thanks

matthieu · July 5, 2016, 10:54pm

Can you post the output of the command, the first 50 lines or so? it should include the progression number [ 1 / 62000 ]

Barry · July 8, 2016, 9:31pm

Hi,
I don’t see a progress indication…
I’m running 2.16.1
Note I have already deleted MANY of the 62,000 sites already, but I still need to delete the excess users as the user admin page doesn’t load (times out).

This is what it shows from last night when I run

cd /var/www/html/piwik
su barry -c 'php ./console core:archive --concurrent-requests-per-website=6' > /tmp/piwik-archive.log


$ head -100 piwik-archive.log
INFO [2016-07-08 08:06:08] ---------------------------
INFO [2016-07-08 08:06:08] INIT
INFO [2016-07-08 08:06:08] Running Piwik 2.16.1 as Super User
INFO [2016-07-08 08:06:08] ---------------------------
INFO [2016-07-08 08:06:08] NOTES
INFO [2016-07-08 08:06:08] - Reports for today will be processed at most every 150 seconds. You can change this value in Piwik UI > Settings > General Settings.
INFO [2016-07-08 08:06:08] - Reports for the current week/month/year will be refreshed at most every 3600 seconds.
INFO [2016-07-08 08:06:08] - Archiving was last executed without error 23 hours 10 min ago
INFO [2016-07-08 08:06:08] Will invalidate archived reports for 2016-07-07 for following websites ids: 62489
INFO [2016-07-08 08:06:08] - Will process 395 other websites because the last time they were archived was on a different day (in the website's timezone) , IDs: 1, 3, 4, 5, 6, 10, 11, 18, 23, 62, 71, 97, 157, 164, 169, 273, 279, 283, 285, 322, 323, 342, 413, 418, 421, 465, 519, 565, 596, 630, 654, 675, 765, 803, 913, 1010, 1042, 1058, 1190, 1282, 1311, 1387, 1392, 1393, 1427, 1526, 1791, 1797, 1841, 1856, 1875, 1991, 2123, 2225, 2255, 2363, 2431, 2549, 2612, 2680, 2737, 2971, 3060, 3355, 3620, 3717, 3734, 3774, 3831, 3852, 3874, 4277, 4295, 4381, 4382, 4408, 4454, 4842, 4888, 5568, 5788, 6210, 6423, 6456, 6564, 6572, 6599, 6659, 6669, 6699, 6842, 6878, 7070, 7140, 7198, 7204, 7277, 7343, 7476, 7479, 7542, 7577, 7611, 7632, 8144, 8322, 8388, 8575, 8666, 8774, 8794, 8923, 9004, 9031, 9066, 9294, 9429, 9560, 9586, 9637, 9646, 10300, 10546, 10939, 10976, 11043, 11325, 11351, 11397, 11692, 11925, 11939, 11950, 11981, 12110, 12290, 12346, 12414, 12426, 12519, 12702, 12744, 12874, 13163, 13174, 13271, 13379, 13493, 13555, 13612, 13724, 13742, 13751, 13755, 13767, 13774, 13792, 13796, 13823, 13976, 14202, 14256, 14292, 14311, 14693, 14801, 14837, 14891, 15158, 15336, 15442, 15625, 15677, 15921, 15955, 16176, 16377, 16579, 16793, 17130, 17443, 17530, 18179, 18461, 18492, 18536, 18561, 18775, 18799, 18820, 18833, 18878, 18881, 19108, 19150, 19326, 19345, 19519, 19884, 19934, 20078, 20090, 20093, 20141, 20453, 20614, 20934, 21093, 21116, 21222, 21772, 21815, 21859, 22543, 22917, 23187, 23191, 23273, 23354, 23384, 23385, 23431, 23642, 23980, 24172, 24386, 24492, 24625, 24626, 24665, 24668, 24800, 25195, 25262, 25592, 25902, 25919, 26270, 27474, 27478, 28637, 28653, 28663, 29495, 29996, 31081, 32636, 33171, 33334, 33583, 33603, 33605, 33925, 34037, 34371, 36281, 37561, 38034, 38689, 39269, 40142, 40304, 40355, 41056, 41790, 42031, 42115, 42291, 44003, 44522, 44600, 45162, 45407, 45517, 46041, 46961, 47099, 47113, 47141, 47466, 47625, 48743, 49327, 49750, 49965, 50009, 50458, 50824, 50902, 51051, 51109, 51269, 51959, 52129, 52173, 52174, 52356, 52408, 52812, 52941, 53077, 53082, 53228, 53229, 54910, 55290, 55804, 55853, 55924, 55941, 55998, 56001, 56037, 56047, 56398, 56422, 56444, 56539, 57993, 58098, 58357, 59011, 59146, 60520, 60654, 60717, 60865, 61100, 61277, 61736, 61737, 61738, 61745, 61747, 61824, 61878, 62047, 62451, 62455, 62458, 62459, 62460, 62462, 62473, 62475, 62476, 62477, 62481, 62482, 62483, 62484, 62486, 62487, 62488, 62489, 62490, 62491, 62492, 62493, 62494, 62495, 62496, 62497, 62498, 62499, 62500, 62501, 62502, 62503, 62504, 62505, 62506, 62507, 62508, 62509, 62510, 62511, 62512, 62513, 62514, 62515, 62516, 62517, 62518, 62519, 62520, 62521, 62522, 62523, 62524, 62525, 62526, 62527, 62528, 62529
INFO [2016-07-08 08:06:08] - Will process 1 other websites because some old data reports have been invalidated (eg. using the Log Import script) , IDs: 62489
INFO [2016-07-08 08:06:08] ---------------------------
INFO [2016-07-08 08:06:08] START
INFO [2016-07-08 08:06:08] Starting Piwik reports archiving...
INFO [2016-07-08 08:06:08] Day has finished for website id 1 since last run
INFO [2016-07-08 08:06:08] Will pre-process for website id = 1, period = day, date = last3
INFO [2016-07-08 08:06:08] - pre-processing all visits
INFO [2016-07-08 08:06:09] Archived website id = 1, period = day, 0 segments, 0 visits in last 3 days, 0 visits today, Time elapsed: 0.607s
INFO [2016-07-08 08:06:09] Will pre-process for website id = 1, period = week, date = last2
INFO [2016-07-08 08:06:09] - pre-processing all visits
INFO [2016-07-08 08:06:09] Archived website id = 1, period = week, 0 segments, 0 visits in last 2 weeks, 0 visits this week, Time elapsed: 0.466s
INFO [2016-07-08 08:06:09] Will pre-process for website id = 1, period = month, date = last2
INFO [2016-07-08 08:06:09] - pre-processing all visits
INFO [2016-07-08 08:06:10] Archived website id = 1, period = month, 0 segments, 0 visits in last 2 months, 0 visits this month, Time elapsed: 0.477s
INFO [2016-07-08 08:06:10] Will pre-process for website id = 1, period = year, date = last3
INFO [2016-07-08 08:06:10] - pre-processing all visits
INFO [2016-07-08 08:06:11] Archived website id = 1, period = year, 0 segments, 0 visits in last 3 years, 0 visits this year, Time elapsed: 0.588s
INFO [2016-07-08 08:06:11] Archived website id = 1, 4 API requests, Time elapsed: 2.148s [1/395 done]
INFO [2016-07-08 08:06:11] Day has finished for website id 3 since last run
INFO [2016-07-08 08:06:11] Will pre-process for website id = 3, period = day, date = last3
INFO [2016-07-08 08:06:11] - pre-processing all visits
INFO [2016-07-08 08:06:11] Archived website id = 3, period = day, 0 segments, 0 visits in last 3 days, 0 visits today, Time elapsed: 0.464s
INFO [2016-07-08 08:06:11] Will pre-process for website id = 3, period = week, date = last2
INFO [2016-07-08 08:06:11] - pre-processing all visits
INFO [2016-07-08 08:06:12] Archived website id = 3, period = week, 0 segments, 0 visits in last 2 weeks, 0 visits this week, Time elapsed: 0.468s
INFO [2016-07-08 08:06:12] Will pre-process for website id = 3, period = month, date = last3
INFO [2016-07-08 08:06:12] - pre-processing all visits
INFO [2016-07-08 08:06:12] Archived website id = 3, period = month, 0 segments, 0 visits in last 3 months, 0 visits this month, Time elapsed: 0.467s
INFO [2016-07-08 08:06:12] Will pre-process for website id = 3, period = year, date = last3
INFO [2016-07-08 08:06:12] - pre-processing all visits
INFO [2016-07-08 08:06:13] Archived website id = 3, period = year, 0 segments, 0 visits in last 3 years, 0 visits this year, Time elapsed: 0.587s
INFO [2016-07-08 08:06:13] Archived website id = 3, 4 API requests, Time elapsed: 1.989s [2/395 done]
INFO [2016-07-08 08:06:13] Day has finished for website id 4 since last run
INFO [2016-07-08 08:06:13] Will pre-process for website id = 4, period = day, date = last3
INFO [2016-07-08 08:06:13] - pre-processing all visits
INFO [2016-07-08 08:06:13] Archived website id = 4, period = day, 0 segments, 0 visits in last 3 days, 0 visits today, Time elapsed: 0.469s
INFO [2016-07-08 08:06:13] Will pre-process for website id = 4, period = week, date = last2
INFO [2016-07-08 08:06:13] - pre-processing all visits
INFO [2016-07-08 08:06:14] Archived website id = 4, period = week, 0 segments, 0 visits in last 2 weeks, 0 visits this week, Time elapsed: 0.467s
INFO [2016-07-08 08:06:14] Will pre-process for website id = 4, period = month, date = last3
INFO [2016-07-08 08:06:14] - pre-processing all visits
INFO [2016-07-08 08:06:14] Archived website id = 4, period = month, 0 segments, 0 visits in last 3 months, 0 visits this month, Time elapsed: 0.468s
INFO [2016-07-08 08:06:14] Will pre-process for website id = 4, period = year, date = last3
INFO [2016-07-08 08:06:14] - pre-processing all visits
INFO [2016-07-08 08:06:15] Archived website id = 4, period = year, 0 segments, 0 visits in last 3 years, 0 visits this year, Time elapsed: 0.585s
INFO [2016-07-08 08:06:15] Archived website id = 4, 4 API requests, Time elapsed: 1.992s [3/395 done]
INFO [2016-07-08 08:06:15] Day has finished for website id 5 since last run
INFO [2016-07-08 08:06:15] Will pre-process for website id = 5, period = day, date = last3
INFO [2016-07-08 08:06:15] - pre-processing all visits
INFO [2016-07-08 08:06:15] Archived website id = 5, period = day, 0 segments, 0 visits in last 3 days, 0 visits today, Time elapsed: 0.466s
INFO [2016-07-08 08:06:15] Will pre-process for website id = 5, period = week, date = last2
INFO [2016-07-08 08:06:15] - pre-processing all visits
INFO [2016-07-08 08:06:16] Archived website id = 5, period = week, 0 segments, 0 visits in last 2 weeks, 0 visits this week, Time elapsed: 0.466s
INFO [2016-07-08 08:06:16] Will pre-process for website id = 5, period = month, date = last3
INFO [2016-07-08 08:06:16] - pre-processing all visits
INFO [2016-07-08 08:06:16] Archived website id = 5, period = month, 0 segments, 0 visits in last 3 months, 0 visits this month, Time elapsed: 0.469s
INFO [2016-07-08 08:06:16] Will pre-process for website id = 5, period = year, date = last3
INFO [2016-07-08 08:06:16] - pre-processing all visits
INFO [2016-07-08 08:06:17] Archived website id = 5, period = year, 0 segments, 0 visits in last 3 years, 0 visits this year, Time elapsed: 0.593s
INFO [2016-07-08 08:06:17] Archived website id = 5, 4 API requests, Time elapsed: 1.998s [4/395 done]
INFO [2016-07-08 08:06:17] Day has finished for website id 6 since last run
INFO [2016-07-08 08:06:17] Will pre-process for website id = 6, period = day, date = last3
INFO [2016-07-08 08:06:17] - pre-processing all visits
INFO [2016-07-08 08:06:17] Archived website id = 6, period = day, 0 segments, 0 visits in last 3 days, 0 visits today, Time elapsed: 0.465s
INFO [2016-07-08 08:06:17] Will pre-process for website id = 6, period = week, date = last2
INFO [2016-07-08 08:06:17] - pre-processing all visits
INFO [2016-07-08 08:06:18] Archived website id = 6, period = week, 0 segments, 0 visits in last 2 weeks, 0 visits this week, Time elapsed: 0.464s
INFO [2016-07-08 08:06:18] Will pre-process for website id = 6, period = month, date = last3
INFO [2016-07-08 08:06:18] - pre-processing all visits
INFO [2016-07-08 08:06:18] Archived website id = 6, period = month, 0 segments, 0 visits in last 3 months, 0 visits this month, Time elapsed: 0.473s
INFO [2016-07-08 08:06:18] Will pre-process for website id = 6, period = year, date = last3
INFO [2016-07-08 08:06:18] - pre-processing all visits
INFO [2016-07-08 08:06:19] Archived website id = 6, period = year, 0 segments, 0 visits in last 3 years, 0 visits this year, Time elapsed: 0.591s
INFO [2016-07-08 08:06:19] Archived website id = 6, 4 API requests, Time elapsed: 1.997s [5/395 done]
INFO [2016-07-08 08:06:19] Day has finished for website id 10 since last run
INFO [2016-07-08 08:06:19] Will pre-process for website id = 10, period = day, date = last3
INFO [2016-07-08 08:06:19] - pre-processing all visits
INFO [2016-07-08 08:06:19] Archived website id = 10, period = day, 0 segments, 0 visits in last 3 days, 0 visits today, Time elapsed: 0.466s
INFO [2016-07-08 08:06:19] Will pre-process for website id = 10, period = week, date = last2
INFO [2016-07-08 08:06:19] - pre-processing all visits
INFO [2016-07-08 08:06:20] Archived website id = 10, period = week, 0 segments, 0 visits in last 2 weeks, 0 visits this week, Time elapsed: 0.466s
INFO [2016-07-08 08:06:20] Will pre-process for website id = 10, period = month, date = last3
INFO [2016-07-08 08:06:20] - pre-processing all visits
INFO [2016-07-08 08:06:20] Archived website id = 10, period = month, 0 segments, 0 visits in last 3 months, 0 visits this month, Time elapsed: 0.474s
INFO [2016-07-08 08:06:20] Will pre-process for website id = 10, period = year, date = last3
INFO [2016-07-08 08:06:20] - pre-processing all visits
INFO [2016-07-08 08:06:21] Archived website id = 10, period = year, 0 segments, 0 visits in last 3 years, 0 visits this year, Time elapsed: 0.589s
INFO [2016-07-08 08:06:21] Archived website id = 10, 4 API requests, Time elapsed: 1.999s [6/395 done]
INFO [2016-07-08 08:06:21] Day has finished for website id 11 since last run
INFO [2016-07-08 08:06:21] Will pre-process for website id = 11, period = day, date = last3

Thanks

matthieu · July 12, 2016, 5:41am

Hi

this si the progress indicator. It’s maybe a bit hidden… but it lets you see you have processed N websites out of 395 total. Does it help?