Calculate how much Popups events EL databases can host
Closed, ResolvedPublic

Description

The reading team asked if it would be possible to increment the rate of events per second of the Popups schema over 10/sec without having to blacklist the schema for mysql insertion.

They would do it during 2 weeks starting in around 2 weeks from now [PENDING CONFIRMATION] and after this time passed, the schema would return to its normal <10evt/sec.
Their initial wish is to have 100 evt/sec, which would result in a total of 120M events for the 2 weeks, and given the rather big size of the Popups schema, this would occupy around 240GB on disk.

We can assess if that is too much and negotiate on a lower sampling rate, or other potential solutions.

mforns created this task.Aug 2 2017, 7:57 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 2 2017, 7:57 PM

In terms of dates, we are planning on running the test from 8/14 to 8/28

mforns added a comment.Aug 2 2017, 8:17 PM

My initial impression is that 100 evt/sec (240GB) would cancel a big part of the efforts to free up disk space in EL databases.
Those efforts have been undertaken last weeks to improve the performance of the DBs, especially analytics-slave.
That performance improvement is key, because without it we can not execute the purging script that makes EL comply with WMF's data retention guidelines.
What do you think @elukey ?

mforns updated the task description. (Show Details)Aug 2 2017, 8:18 PM
Nuria added a comment.EditedAug 2 2017, 10:13 PM

Analytics slave: https://grafana.wikimedia.org/dashboard/file/server-board.json?refresh=1m&orgId=1&var-server=dbstore1002&var-network=bond0&panelId=17&fullscreen&from=1499387968077&to=1501261618785
has only 800G free, (5.5TB occupied) we cannot operate with a disk full and we actually consume (not counting this new stream of events) *I think* about 40G every day per machine. All things are replicated three ways, 1 master, two slaves, but there is no equal amount of free space in every storage machine.

Thus, it is my impression that unless we free space dramatically (at a faster rate than EL script can) the experiment will not fit as the slave will go kaput disk-wise (@elukey to confirm) This is not to say experiment cannot be done, It CAN, it would just need to use hadoop as an storage. Also, if we have gotten to this ticket: https://phabricator.wikimedia.org/T170720 before 8/14 there might be a lot more available space.

elukey added a comment.Aug 3 2017, 8:35 AM

As Nuria pointed out the analytics-store is now under disk consumption pressure and this experiment might be problematic. There are a couple of things that we could do to allow it:

  1. Delete a ton of data thanks to T170720 (Analytics court)
  2. Delete as much data as possibile (like super old data not used anymore) from tables belonging to the Reading team. These are the top table sizes ordered from the biggest to the smallest:
Table	Size in GB
================================
MobileWebUIClickTracking_10742159_15423246	519.31
PageContentSaveComplete_5588433_15423246	457.22
MediaViewer_10867062_15423246	317.52
MobileWikiAppToCInteraction_10375484_15423246	140.57
Edit_11448630	137.21
Edit_13457736_15423246	132.31
MobileWebSectionUsage_15038458	86.83
MobileWikiAppSearch_10641988_15423246	83.53
MobileWebEditing_8599025	80.55
MobileWikiAppToCInteraction_8461467	77.65
MobileWikiAppArticleSuggestions_11448426_15423246	74.15
NavigationTiming_15485142_15423246	65.15
QuickSurveyInitiation_15278946	62.54
PageContentSaveComplete_5588433	60.44
Popups_15906495	58.06
MobileWikiAppMediaGallery_10923135_15423246	49.56
MediaViewer_10867062	48.34
MobileWikiAppPageScroll_14591606_15423246	46.75
RelatedArticles_16352530	45.22
MobileWebSectionUsage_14321266	44.87
PageCreation_7481635_15423246	39.59
MobileWikiAppArticleSuggestions_12443791_15423246	33.76
NavigationTiming_10785754	32.37
MobileWikiAppLinkPreview_15730939_15423246	30.79
Edit_13457736	29.80
MobileWikiAppShareAFact_12588711_15423246	29.52
RelatedArticles_14496900_15423246	27.60
MobileWikiAppArticleSuggestions_10590869_15423246	27.23
MultimediaViewerDimensions_10014238_15423246	26.31
NavigationTiming_16305090	26.27
MobileWebLanguageSwitcher_15302503_15423246	23.63
UniversalLanguageSelector_7327441_15423246	23.62
SaveTiming_15396492_15423246	22.23
TestSearchSatisfaction2_16270835	22.22
MobileWikiAppSessions_15522505_15423246	20.45
NavigationTiming_15033442	20.14
SaveTiming_12236257	19.32
MobileWikiAppLinkPreview_15730939	19.19
PrefUpdate_5563398_15423246	19.06
MobileWikiAppSessions_15522505	18.20

I can definitely help removing data but I'd need some guidance from the Reading team. The other (better) alternative is to use HDFS as storage, but not sure how feasible it would be for data analysts to query it rather than Mysql (pre-canned query, work already done, cross-table references, etc..).

Let me know :)

elukey triaged this task as Normal priority.Aug 3 2017, 8:35 AM
elukey added a project: User-Elukey.
phuedx added a subscriber: phuedx.Aug 3 2017, 2:59 PM
elukey moved this task from Next Up to In Progress on the Analytics-Kanban board.Aug 3 2017, 3:38 PM
elukey moved this task from Backlog to Stalled on the User-Elukey board.Aug 4 2017, 12:02 PM

@elukey: After a brief conversation with @ovasileva, I'd say that we can safely archive the following:

That's 519.31 + 86.83 + 44.87 = 651.01 GB!


Readers Web should strongly consider removing all MobileWebUIClickTracking instrumentation IMO. I'll create a task…

@phuedx awesome! Do you mean HDFS with "archive"? (Just to be sure)

phuedx added a comment.EditedAug 4 2017, 12:45 PM

@phuedx awesome! Do you mean HDFS with "archive"? (Just to be sure)

Yes. Sorry! This should definitely be done for the MobileWebSectionUsage_* tables as it's important that the results from the research are repeatable.

If it's feasible, then we should for MobileWebUIClickTracking as well. However, the MobileWebUIClickTracking instrumentation was removed from MFE in February 2016 (see 3c12edbb) and it hasn't been missed since so if there's an issue, then we can discuss deleting it.

It should be feasible since we are doing a similar thing in https://phabricator.wikimedia.org/T170720. Moving MobileWebSectionUsage_ and MobileWebUIClickTracking to HDFS would mean deleting more than a Terabyte of data from our mysql instances, that will give us a ton of space back (and would certainly allow your experiment).

elukey moved this task from Stalled to Keep an eye on it on the User-Elukey board.Aug 4 2017, 3:10 PM
Tbayer added a subscriber: JKatzWMF.Aug 4 2017, 8:58 PM

@elukey: After a brief conversation with @ovasileva, I'd say that we can safely archive the following:

...and for various other purposes. Please do not remove these tables from MySQL yet.

No objections from me in case of MobileWebUIClickTracking, although there too you may want to check with other people first (CC @JKatzWMF ).

@Tbayer Thanks for the heads up. If @ovasileva doesn't need it I think we're good, but I just started a query to get the primary historical data--it's so much juicy historical data, it would be a shame to lose the high level numbers over time.

I haven't queried in a long time, so it might be off:

SELECT count(*), event_name, editCountBucket, wiki, LEFT(timestamp,6) as months 
FROM log.MobileWebUIClickTracking_10742159_15423246 
WHERE event_mobileMode like "stable" 
GROUP BY event_name, wiki, months, editCountBucket;
elukey added a comment.Aug 8 2017, 1:48 PM

...and for various other purposes. Please do not remove these tables from MySQL yet.

Not planning to take any drop action without extensive discussion and confirmation :)

@Tbayer Thanks for the heads up. If @ovasileva doesn't need it I think we're good, but I just started a query to get the primary historical data--it's so much juicy historical data, it would be a shame to lose the high level numbers over time.

@JKatzWMF one of the options that we outlined above is to move the table to HDFS and then drop it from Mysql, so the data will be available for Hive queries but it will not take space on the analytics slaves (and m4 master). HDFS is one of those resources that are robust and easily expandable (from the ops point of view), but I understand that Hive SQL is not the same as regular SQL. I guess that it is enough to query historical data but let me know otherwise, curious to know all the use cases.

Another thing to consider is that we are currently sanitizing huge tables on Mysql with expensive UPDATE statements, thing that would be way quicker on HDFS with Hive. So even for sanitization purposes, moving MobileWebUIClickTracking_10742159_15423246 would be a massive win for Analytics.

Thanks for all the info!

Thoughts?

Nuria added a comment.Aug 8 2017, 3:25 PM

@JKatzWMF Let us know if there is any data that can be deleted and does not need to be archived

@elukey @Nuria my query is either failing or returning no results due to an error in the logic. My querying skills may have atrophied or the table may have gotten too big to query. All I know is that the query stopped running and the file it was supposed to populate is empty. Until this (see below) query is completed I think we should keep the table (hive is not ideal but okay).

SELECT count(*) as num_events, event_name, editCountBucket, wiki, LEFT(TIMESTAMP,6) as Months
FROM log.MobileWebUIClickTracking_10742159_15423246
WHERE event_mobileMode like  'stable' 
GROUP BY event_name, wiki, months, editCountBucket;

pinging @Halfak, @Neil_P._Quinn_WMF @Jdforrester-WMF: Can we delete this table: PageContentSaveComplete_5588433_1542324? It is 457.22 gb and as impossible as it sounds, we are running out of space to store event logging.

elukey added a comment.Aug 8 2017, 4:53 PM

pinging @Halfak, @Neil_P._Quinn_WMF @Jdforrester-WMF: Can we delete this table: PageContentSaveComplete_5588433_1542324? It is 457.22 gb and as impossible as it sounds, we are running out of space to store event logging.

This one will be moved to HDFS, we have a task for it: https://phabricator.wikimedia.org/T170720

JKatzWMF added a comment.EditedAug 8 2017, 4:54 PM

^ oh great! the next one on the list is mediaviewer and this needs to stay for the time being. A new multimedia PM should be coming on board soon and I think one of their early actions will need to be to understand current and historical mediaviewer behavior

@elukey @Nuria my query is either failing or returning no results due to an error in the logic. My querying skills may have atrophied or the table may have gotten too big to query. All I know is that the query stopped running and the file it was supposed to populate is empty. Until this (see below) query is completed I think we should keep the table (hive is not ideal but okay).

SELECT count(*) as num_events, event_name, editCountBucket, wiki, LEFT(TIMESTAMP,6) as Months
FROM log.MobileWebUIClickTracking_10742159_15423246
WHERE event_mobileMode like  'stable' 
GROUP BY event_name, wiki, months, editCountBucket;

So because this seems like a rather simple but performance-intensive query, perhaps we could use it as a test case for how querying EventLogging data might work in case such a table is moved to HDFS. @Ottomata, would you be able to demonstrate in detail how to rewrite this query in Hive (and possibly update these instructions in the process)?

And a related question: https://wikitech.wikimedia.org/wiki/Analytics/Systems/EventLogging#Hadoop currently says:

" all EventLogging data in Hadoop is automatically purged after 90 days; the whitelist of fields to retain is not used, but this feature could be added in the future if there is sufficient demand"

What (if any) steps would need to be taken to ensure that the EL table we plan to archive here stays retained?

And a related question: https://wikitech.wikimedia.org/wiki/Analytics/Systems/EventLogging#Hadoop currently says:

" all EventLogging data in Hadoop is automatically purged after 90 days; the whitelist of fields to retain is not used, but this feature could be added in the future if there is sufficient demand"

What (if any) steps would need to be taken to ensure that the EL table we plan to archive here stays retained?

We still need to come up with a script/procedure/other like the eventlogging_cleaner.py for HDFS so we'll take into account the archived tables as special use case. As far as I got from the last chat with the team we are going to copy over to HDFS tables like PageContentSaveComplete_5588433_1542324 with sqoop, skipping attributes that would be NULLed by the sanitization process (in the PageContentSaveComplete only the sensitive part of the EL capsule).

OK, so for the avoidance of confusion, I assume that this archiving process with Scoop will be separate from the ongoing hourly import via Camus as described at https://wikitech.wikimedia.org/wiki/Analytics/Systems/EventLogging#Hadoop (whose results are, as mentioned, always deleted completely after 90 days). Correct?

I am not a big expert in this part of the analytics infrastructure so this answer is not authoritative, but IIUC from https://phabricator.wikimedia.org/T162610 the Eventlogging data that you mentioned is merely what's on Kafka without any attempt of refinement/grouping-by-schema-version/etc, meanwhile the data on Mysql is the result of the EventLogging processing.

The plan for the moment is to archive mysql tables as they are (without sensitive fields as said before) in a path like /wmf/data/archive/eventlogging/Table_name. This will be only a measure taken for specific tables to free space on the mysql dbs without dropping data.

So my answer to the Correct? question would be yes but let's see if others agree :)

Forgot to mention that we are going to document everything once we archive the table, so it will not be a guessing game to find it on HDFS. We'll also make clear what is the difference between the raw data imported and the few archived tables.

@Ottomata, would you be able to demonstrate in detail how to rewrite this query in Hive (and possibly update these instructions in the process)?

Yes, but there is not yet a timeline for this. :/ I am SO close to having this work. I'm writing tests, etc. but we have a higher prioritized quarterly goal of getting a new Kafka cluster up and running so we can start migrating clients and encrypting traffic, so I've had to put this work aside for now. When it is actually productionized and ready, this will def happen.

Nuria added a comment.EditedAug 11 2017, 8:22 PM

OK, so for the avoidance of confusion, I assume that this archiving process with Scoop will be separate from the ongoing hourly import via Camus as described at

Yes, this is a one time process to archive these tables and move them out of the way, we will just be doing this for couple huge tables. To archive the tables sensitive data needs to be removed..

Nuria added a comment.EditedAug 15 2017, 3:45 PM

So because this seems like a rather simple but performance-intensive query, perhaps we could use it as a test case for how querying EventLogging data might work in case such a table is moved to HDF

Table is now on hdfs (minus sensitive fields) please @JKatzWMF and @Tbayer take a look .

This query:

use wmf;
select count(*) as num_events,event_name, 
wiki,SUBSTR(timestamp,0,6), editCountBucket
from mobilewebuiclicktracking_10742159_15423246
where event_mobileMode='stable'
GROUP BY event_name, wiki, SUBSTR(timestamp,0,6), 
editCountBucket;

Took 2 minutes to run. I think that is the hive equivalent to the one
@JKatzWMF had running on mysql.

Some docs:

https://wikitech.wikimedia.org/wiki/Analytics/Systems/EventLogging#Hadoop._Archived_Data

Ping us when you have time to look at table so we can delete it from mysql (cc @elukey)

Nuria added a comment.Aug 15 2017, 3:54 PM

Once is deleted (this and the other table mentioned here: https://phabricator.wikimedia.org/T170720) there is probably space to run the pop up experiment.

We dropped the PageContentSaveComplete table and re-gained only ~100GB , that is not what we expected. I checked some numbers on the databases and reported them in https://phabricator.wikimedia.org/T170720#3533452; I think that TokuDB's compression makes the files on the file system way smaller than what reported by information_schema.TABLES.

The current state of dbstore1002 should allow this experiment to proceed, but I have two suggestions to make:

  1. keep dbstore1002's space consumption checked via this link
  2. alert @Marostegui when you start, together with @Ottomata
  3. give us the green light to drop MobileWebUIClickTracking_10742159_15423246 as Nuria suggested (after checking Hive)

Hm, @Nuria, I noticed that you imported this data into /wmf/data/archive/eventlogging, but created the table in the wmf database. Perhaps it would be better for us to have an archive database? The directory -> database name mapping is not and never will be totally consistent, but in this case, I think these tables probably shouldn't be in our main wmf database.

@Ottomata

Nuria put the tables there, knowing that you would have an opinion,
so she already mentioned that you could change them to wherever you saw fit.

archive database sounds good to me :]

We dropped the PageContentSaveComplete table and re-gained only ~100GB , that is not what we expected. I checked some numbers on the databases and reported them in https://phabricator.wikimedia.org/T170720#3533452; I think that TokuDB's compression makes the files on the file system way smaller than what reported by information_schema.TABLES.

The current state of dbstore1002 should allow this experiment to proceed, but I have two suggestions to make:

  1. keep dbstore1002's space consumption checked via this link
  2. alert @Marostegui when you start, together with @Ottomata

Thanks @elukey! I have made a note for the web team to do this as part of the experiment rollout (currently envisaged for early next week).

So because this seems like a rather simple but performance-intensive query, perhaps we could use it as a test case for how querying EventLogging data might work in case such a table is moved to HDF

Table is now on hdfs (minus sensitive fields) please @JKatzWMF and @Tbayer take a look .

This query:

use wmf;
select count(*) as num_events,event_name, 
wiki,SUBSTR(timestamp,0,6), editCountBucket
from mobilewebuiclicktracking_10742159_15423246
where event_mobileMode='stable'
GROUP BY event_name, wiki, SUBSTR(timestamp,0,6), 
editCountBucket;

Took 2 minutes to run. I think that is the hive equivalent to the one
@JKatzWMF had running on mysql.

Some docs:

https://wikitech.wikimedia.org/wiki/Analytics/Systems/EventLogging#Hadoop._Archived_Data

Ping us when you have time to look at table so we can delete it from mysql (cc @elukey)

Thanks @Nuria, this looks pretty great! I'm back now from Wikimania and vacation, and did a spot check comparing the results between MariaDB[1] and Hive[2] just to be safe, and the numbers do seem to match up. In this particular case (restricted to one month) the speed increase was already noticeable too, 2 min on Hive vs. 9 min on MariaDB, but I assume it is even larger when querying the full table.

As discussed earlier, there would still be major blockers if we were to aim moving all EL data analysis to Hive (most queries aren't as easy to translate as this one), but this example is certainly motivating for the work Andrew outlined in T172322#3515527 .

[1]
(using MariaDB, accessing analytics-store.eqiad.wmnet, query took about 9 minutes wall clock time)
SELECT event_name,
editCountBucket,
wiki, LEFT(TIMESTAMP,6) as yearmonth,
COUNT(*) as num_events
FROM log.MobileWebUIClickTracking_10742159_15423246
WHERE event_mobileMode='stable'
AND timestamp LIKE '201601%'
AND wiki LIKE 'de%'
AND editCountBUcket IS NULL
GROUP BY event_name, editCountBucket, wiki, yearmonth
--------------

event_name      editCountBucket wiki    yearmonth       num_events
hamburger       NULL    dewiki  201601  357919
hamburger       NULL    dewikibooks     201601  836
hamburger       NULL    dewikinews      201601  96
hamburger       NULL    dewikiquote     201601  427
hamburger       NULL    dewikisource    201601  114
hamburger       NULL    dewikiversity   201601  42
hamburger       NULL    dewikivoyage    201601  357
hamburger       NULL    dewiktionary    201601  3168
languages       NULL    dewiki  201601  451489
languages       NULL    dewikibooks     201601  84
languages       NULL    dewikinews      201601  4
languages       NULL    dewikiquote     201601  333
languages       NULL    dewikisource    201601  123
languages       NULL    dewikiversity   201601  4
languages       NULL    dewikivoyage    201601  144
languages       NULL    dewiktionary    201601  2573
lastmodified-history    NULL    dewiki  201601  64
lastmodified-history    NULL    dewikibooks     201601  1
lastmodified-history    NULL    dewiktionary    201601  1
lastmodified-profile    NULL    dewiki  201601  70379
lastmodified-profile    NULL    dewikibooks     201601  413
lastmodified-profile    NULL    dewikinews      201601  7
lastmodified-profile    NULL    dewikiquote     201601  53
lastmodified-profile    NULL    dewikisource    201601  61
lastmodified-profile    NULL    dewikiversity   201601  20
lastmodified-profile    NULL    dewikivoyage    201601  59
lastmodified-profile    NULL    dewiktionary    201601  284
page-toc-link   NULL    dewiki  201601  1670740
page-toc-link   NULL    dewikibooks     201601  7168
page-toc-link   NULL    dewikinews      201601  4
page-toc-link   NULL    dewikiquote     201601  1099
page-toc-link   NULL    dewikisource    201601  273
page-toc-link   NULL    dewikiversity   201601  14
page-toc-link   NULL    dewikivoyage    201601  4956
page-toc-link   NULL    dewiktionary    201601  2069
page-toc-toggle NULL    dewiki  201601  38958
page-toc-toggle NULL    dewikibooks     201601  231
page-toc-toggle NULL    dewikiquote     201601  87
page-toc-toggle NULL    dewikisource    201601  27
page-toc-toggle NULL    dewikiversity   201601  4
page-toc-toggle NULL    dewikivoyage    201601  59
page-toc-toggle NULL    dewiktionary    201601  309
reference       NULL    dewiki  201601  223843
reference       NULL    dewikibooks     201601  77
reference       NULL    dewikiquote     201601  2
reference       NULL    dewikisource    201601  34
reference       NULL    dewikiversity   201601  1
reference       NULL    dewikivoyage    201601  3
reference       NULL    dewiktionary    201601  1004
search  NULL    dewiki  201601  1656386
search  NULL    dewikibooks     201601  1355
search  NULL    dewikinews      201601  52
search  NULL    dewikiquote     201601  778
search  NULL    dewikisource    201601  227
search  NULL    dewikiversity   201601  26
search  NULL    dewikivoyage    201601  794
search  NULL    dewiktionary    201601  35069
[2]
SELECT event_name,
editCountBucket, 
wiki, SUBSTR(timestamp,0,6) AS yearmonth,
COUNT(*) as num_events 
FROM wmf.mobilewebuiclicktracking_10742159_15423246
WHERE event_mobileMode='stable'
AND SUBSTR(timestamp,0,6) = '201601'
AND wiki LIKE 'de%'
AND editCountBucket IS NULL
GROUP BY event_name, editCountBucket, 
wiki, SUBSTR(timestamp,0,6)
ORDER BY event_name, editCountBucket, 
wiki, yearmonth LIMIT 10000;

event_name	editcountbucket	wiki	yearmonth	num_events
hamburger	NULL	dewiki	201601	357919
hamburger	NULL	dewikibooks	201601	836
hamburger	NULL	dewikinews	201601	96
hamburger	NULL	dewikiquote	201601	427
hamburger	NULL	dewikisource	201601	114
hamburger	NULL	dewikiversity	201601	42
hamburger	NULL	dewikivoyage	201601	357
hamburger	NULL	dewiktionary	201601	3168
languages	NULL	dewiki	201601	451489
languages	NULL	dewikibooks	201601	84
languages	NULL	dewikinews	201601	4
languages	NULL	dewikiquote	201601	333
languages	NULL	dewikisource	201601	123
languages	NULL	dewikiversity	201601	4
languages	NULL	dewikivoyage	201601	144
languages	NULL	dewiktionary	201601	2573
lastmodified-history	NULL	dewiki	201601	64
lastmodified-history	NULL	dewikibooks	201601	1
lastmodified-history	NULL	dewiktionary	201601	1
lastmodified-profile	NULL	dewiki	201601	70379
lastmodified-profile	NULL	dewikibooks	201601	413
lastmodified-profile	NULL	dewikinews	201601	7
lastmodified-profile	NULL	dewikiquote	201601	53
lastmodified-profile	NULL	dewikisource	201601	61
lastmodified-profile	NULL	dewikiversity	201601	20
lastmodified-profile	NULL	dewikivoyage	201601	59
lastmodified-profile	NULL	dewiktionary	201601	284
page-toc-link	NULL	dewiki	201601	1670740
page-toc-link	NULL	dewikibooks	201601	7168
page-toc-link	NULL	dewikinews	201601	4
page-toc-link	NULL	dewikiquote	201601	1099
page-toc-link	NULL	dewikisource	201601	273
page-toc-link	NULL	dewikiversity	201601	14
page-toc-link	NULL	dewikivoyage	201601	4956
page-toc-link	NULL	dewiktionary	201601	2069
page-toc-toggle	NULL	dewiki	201601	38958
page-toc-toggle	NULL	dewikibooks	201601	231
page-toc-toggle	NULL	dewikiquote	201601	87
page-toc-toggle	NULL	dewikisource	201601	27
page-toc-toggle	NULL	dewikiversity	201601	4
page-toc-toggle	NULL	dewikivoyage	201601	59
page-toc-toggle	NULL	dewiktionary	201601	309
reference	NULL	dewiki	201601	223843
reference	NULL	dewikibooks	201601	77
reference	NULL	dewikiquote	201601	2
reference	NULL	dewikisource	201601	34
reference	NULL	dewikiversity	201601	1
reference	NULL	dewikivoyage	201601	3
reference	NULL	dewiktionary	201601	1004
search	NULL	dewiki	201601	1656386
search	NULL	dewikibooks	201601	1355
search	NULL	dewikinews	201601	52
search	NULL	dewikiquote	201601	778
search	NULL	dewikisource	201601	227
search	NULL	dewikiversity	201601	26
search	NULL	dewikivoyage	201601	794
search	NULL	dewiktionary	201601	35069
57 rows selected (134.179 seconds)

PS (after discussing with @JKatzWMF ): That means that it is now fine from everyone's perspective to drop log.MobileWebUIClickTracking_10742159_15423246, assuming that we retain the wmf.mobilewebuiclicktracking_10742159_15423246 version on Hive. (And regarding T172322#3537746: yes, moving it to a separate archive database instead of wmf sounds like a good idea.)

Mentioned in SAL (#wikimedia-operations) [2017-08-29T08:06:37Z] <elukey> drop log.MobileWebUIClickTracking_10742159_15423246 from dbstore1002 to free space (table archived on HDFS) - T172322 T168303

Hi all!

I'm seeing insertions into mariaDB for Popups events. About 3000 every 4 minutes (12 evt per second approx).

2017-08-29 13:54:59,108 [22699] (MainThread) Inserted 189 Popups_16364296 events in 0.312208 seconds
2017-08-29 13:55:00,864 [22699] (MainThread) Inserted 2208 Popups_16364296 events in 1.740874 seconds
2017-08-29 13:55:01,241 [22699] (MainThread) Inserted 603 Popups_16364296 events in 0.371966 seconds

So, should we move this task to done?

I'd prefer to wait a bit before closing, space consumption went a bit up afaics:

It was of course expected but since dbstore1002 is still not out of the 90% space occupied I'd like to keep an eye on it a bit more (the drop registered was me dropping MobileWebUIClickTracking_10742159_15423246).

Mentioned in SAL (#wikimedia-operations) [2017-08-29T14:51:18Z] <elukey> drop log.MobileWebUIClickTracking_10742159_15423246 from db1047 (archived on HDFS) - T172322

Hi all!
While following up on EL performance regarding Popups experiment, I looked at the mysql consumer logs and I think EL is receiving around 200 events per second from the Popups schema. Is that expected? We discussed around 100 events per second in this task's description, no? This might make our calculations for space consumption fail. Here are the logs I found: I just summed the inserted events (33000) and divided by the difference of the first and last timestamps (159 seconds).

2017-08-30 18:51:27,518 [22699] (MainThread) Inserted 192 Popups_16364296 events in 0.113053 seconds
2017-08-30 18:51:28,908 [22699] (MainThread) Inserted 2215 Popups_16364296 events in 1.369190 seconds
2017-08-30 18:51:29,189 [22699] (MainThread) Inserted 593 Popups_16364296 events in 0.276136 seconds
2017-08-30 18:51:42,410 [22699] (MainThread) Inserted 199 Popups_16364296 events in 0.118885 seconds
2017-08-30 18:51:43,802 [22699] (MainThread) Inserted 2295 Popups_16364296 events in 1.375483 seconds
2017-08-30 18:51:44,045 [22699] (MainThread) Inserted 506 Popups_16364296 events in 0.238042 seconds
2017-08-30 18:52:02,979 [22699] (MainThread) Inserted 187 Popups_16364296 events in 0.116786 seconds
2017-08-30 18:52:04,551 [22699] (MainThread) Inserted 2295 Popups_16364296 events in 1.552113 seconds
2017-08-30 18:52:04,802 [22699] (MainThread) Inserted 518 Popups_16364296 events in 0.245957 seconds
2017-08-30 18:52:13,393 [22699] (MainThread) Inserted 191 Popups_16364296 events in 0.337739 seconds
2017-08-30 18:52:14,891 [22699] (MainThread) Inserted 2304 Popups_16364296 events in 1.479943 seconds
2017-08-30 18:52:15,131 [22699] (MainThread) Inserted 505 Popups_16364296 events in 0.237636 seconds
2017-08-30 18:52:28,376 [22699] (MainThread) Inserted 158 Popups_16364296 events in 0.093370 seconds
2017-08-30 18:52:28,381 [22699] (MainThread) Inserted 1 Popups_16364296 events in 0.004940 seconds
2017-08-30 18:52:29,854 [22699] (MainThread) Inserted 2349 Popups_16364296 events in 1.453029 seconds
2017-08-30 18:52:30,087 [22699] (MainThread) Inserted 492 Popups_16364296 events in 0.229931 seconds
2017-08-30 18:52:44,575 [22699] (MainThread) Inserted 169 Popups_16364296 events in 0.107121 seconds
2017-08-30 18:52:45,976 [22699] (MainThread) Inserted 2287 Popups_16364296 events in 1.384102 seconds
2017-08-30 18:52:46,242 [22699] (MainThread) Inserted 544 Popups_16364296 events in 0.260800 seconds
2017-08-30 18:53:00,650 [22699] (MainThread) Inserted 174 Popups_16364296 events in 0.105673 seconds
2017-08-30 18:53:02,240 [22699] (MainThread) Inserted 2291 Popups_16364296 events in 1.572348 seconds
2017-08-30 18:53:02,501 [22699] (MainThread) Inserted 535 Popups_16364296 events in 0.257486 seconds
2017-08-30 18:53:16,104 [22699] (MainThread) Inserted 194 Popups_16364296 events in 0.143966 seconds
2017-08-30 18:53:17,742 [22699] (MainThread) Inserted 2295 Popups_16364296 events in 1.624070 seconds
2017-08-30 18:53:18,167 [22699] (MainThread) Inserted 511 Popups_16364296 events in 0.419751 seconds
2017-08-30 18:53:33,360 [22699] (MainThread) Inserted 231 Popups_16364296 events in 0.139718 seconds
2017-08-30 18:53:34,726 [22699] (MainThread) Inserted 2228 Popups_16364296 events in 1.348730 seconds
2017-08-30 18:53:34,991 [22699] (MainThread) Inserted 541 Popups_16364296 events in 0.260142 seconds
2017-08-30 18:53:48,461 [22699] (MainThread) Inserted 179 Popups_16364296 events in 0.106170 seconds
2017-08-30 18:53:49,867 [22699] (MainThread) Inserted 2291 Popups_16364296 events in 1.384415 seconds
2017-08-30 18:53:50,121 [22699] (MainThread) Inserted 530 Popups_16364296 events in 0.251223 seconds
2017-08-30 18:54:04,423 [22699] (MainThread) Inserted 195 Popups_16364296 events in 0.119049 seconds
2017-08-30 18:54:06,002 [22699] (MainThread) Inserted 2268 Popups_16364296 events in 1.559001 seconds
2017-08-30 18:54:06,255 [22699] (MainThread) Inserted 537 Popups_16364296 events in 0.250670 seconds

@mforns The estimate was for around 100 events/second on average, and the new sampling rate was chosen based on the event rate from the previous instrumentation, where the peak hourly rate on weekdays was usually achieved between 13-16h UTC (and was about 4-6 times higher than the daily low). BTW most of the conversation relevant to this launch is now happening at T172291 instead.

@Tbayer
I see, makes total sense.
Thanks for the explanation!

JAllemandou moved this task from In Progress to Done on the Analytics-Kanban board.Tue, Sep 5, 3:26 PM
elukey moved this task from In Progress to Done on the User-Elukey board.
Nuria closed this task as Resolved.Tue, Sep 12, 8:41 PM