Echo schema eventlogging - Analytics

Jonathan Morgan

15 Dec 15 Dec

7:22 p.m.

Hi Nuria, FWIW: Although I'm not using this right now, but I could see it being useful for understanding the impact of new notification updates that are coming down the pike.[1][2] What are the costs involved in keeping this schema up? Best, J 1. https://meta.wikimedia.org/wiki/Research:Cross-wiki_notifications_user_rese… 2. https://phabricator.wikimedia.org/T116741 On Tue, Dec 15, 2015 at 8:22 AM, Nuria Ruiz <nuria(a)wikimedia.org> wrote:

...

Roan: The data for Echo schema(https://meta.wikimedia.org/wiki/Schema:Echo) is quite large and we are not sure is even used. Can you confirm either way? If it is no longer used we will stop collecting it. Thanks, Nuria _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- Jonathan T. Morgan Senior Design Researcher Wikimedia Foundation User:Jmorgan (WMF) <https://meta.wikimedia.org/wiki/User:Jmorgan_(WMF)>

Reply

Andrew Otto

7:35 p.m.

We could blacklist this schema from the mysql database, and still keep producing it. It would be available in Hadoop either way.

...

On Dec 15, 2015, at 12:22, Jonathan Morgan <jmorgan(a)wikimedia.org> wrote: Hi Nuria, FWIW: Although I'm not using this right now, but I could see it being useful for understanding the impact of new notification updates that are coming down the pike.[1][2] What are the costs involved in keeping this schema up? Best, J 1. https://meta.wikimedia.org/wiki/Research:Cross-wiki_notifications_user_rese… <https://meta.wikimedia.org/wiki/Research:Cross-wiki_notifications_user_research> 2. https://phabricator.wikimedia.org/T116741 <https://phabricator.wikimedia.org/T116741> On Tue, Dec 15, 2015 at 8:22 AM, Nuria Ruiz <nuria(a)wikimedia.org <mailto:nuria@wikimedia.org>> wrote: Roan: The data for Echo schema(https://meta.wikimedia.org/wiki/Schema:Echo <https://meta.wikimedia.org/wiki/Schema:Echo>) is quite large and we are not sure is even used. Can you confirm either way? If it is no longer used we will stop collecting it. Thanks, Nuria _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org <mailto:Analytics@lists.wikimedia.org> https://lists.wikimedia.org/mailman/listinfo/analytics <https://lists.wikimedia.org/mailman/listinfo/analytics> -- Jonathan T. Morgan Senior Design Researcher Wikimedia Foundation User:Jmorgan (WMF) <https://meta.wikimedia.org/wiki/User:Jmorgan_(WMF)> _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

Reply

Jonathan Morgan

9:16 p.m.

Hi Nuria! Speaking for *my own particular scenario*, that solution sounds like it will be fine, since I don't plan on immediately performing research with these data. But it's obviously still the Collab team's call here--they likely have needs I know nothing about. Cc'ing Joe Matazzoni in case he's not following this already... J On Tue, Dec 15, 2015 at 9:50 AM, Nuria Ruiz <nuria(a)wikimedia.org> wrote:

...

We could blacklist this schema from the mysql database, and still keep

producing it. It would be available in Hadoop either way. Right but I would also like to drop the table if it is not being used, if data is not going to be looked at soonish there is no point in storing as it will likely be deleted before it gets looked at. Thanks, Nuria On Tue, Dec 15, 2015 at 9:35 AM, Andrew Otto <aotto(a)wikimedia.org> wrote:

We could blacklist this schema from the mysql database, and still keep producing it. It would be available in Hadoop either way. On Dec 15, 2015, at 12:22, Jonathan Morgan <jmorgan(a)wikimedia.org> wrote: Hi Nuria, FWIW: Although I'm not using this right now, but I could see it being useful for understanding the impact of new notification updates that are coming down the pike.[1][2] What are the costs involved in keeping this schema up? Best, J 1. https://meta.wikimedia.org/wiki/Research:Cross-wiki_notifications_user_rese… 2. https://phabricator.wikimedia.org/T116741 On Tue, Dec 15, 2015 at 8:22 AM, Nuria Ruiz <nuria(a)wikimedia.org> wrote:

Roan: The data for Echo schema(https://meta.wikimedia.org/wiki/Schema:Echo) is quite large and we are not sure is even used. Can you confirm either way? If it is no longer used we will stop collecting it. Thanks, Nuria _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- Jonathan T. Morgan Senior Design Researcher Wikimedia Foundation User:Jmorgan (WMF) <https://meta.wikimedia.org/wiki/User:Jmorgan_(WMF)> _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

_______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- Jonathan T. Morgan Senior Design Researcher Wikimedia Foundation User:Jmorgan (WMF) <https://meta.wikimedia.org/wiki/User:Jmorgan_(WMF)>

Reply

Nuria Ruiz

9:34 p.m.

...

maybe we could nuke data that's more than a year old (or 6 months old or

something) from mysql? With eventlogging data we "normally" drop data that is older than 90 days, will this work? Thanks for the prompt response. On Tue, Dec 15, 2015 at 11:27 AM, Roan Kattouw <rkattouw(a)wikimedia.org> wrote:

...

If the data is going to be retained but would just become harder to query (i.e. still in Hadoop but not in mysql), maybe we could nuke data that's more than a year old (or 6 months old or something) from mysql? On Tue, Dec 15, 2015 at 9:35 AM, Andrew Otto <aotto(a)wikimedia.org> wrote:

We could blacklist this schema from the mysql database, and still keep producing it. It would be available in Hadoop either way. On Dec 15, 2015, at 12:22, Jonathan Morgan <jmorgan(a)wikimedia.org> wrote: Hi Nuria, FWIW: Although I'm not using this right now, but I could see it being useful for understanding the impact of new notification updates that are coming down the pike.[1][2] What are the costs involved in keeping this schema up? Best, J 1. https://meta.wikimedia.org/wiki/Research:Cross-wiki_notifications_user_rese… 2. https://phabricator.wikimedia.org/T116741 On Tue, Dec 15, 2015 at 8:22 AM, Nuria Ruiz <nuria(a)wikimedia.org> wrote:

Roan: The data for Echo schema(https://meta.wikimedia.org/wiki/Schema:Echo) is quite large and we are not sure is even used. Can you confirm either way? If it is no longer used we will stop collecting it. Thanks, Nuria _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- Jonathan T. Morgan Senior Design Researcher Wikimedia Foundation User:Jmorgan (WMF) <https://meta.wikimedia.org/wiki/User:Jmorgan_(WMF)> _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

_______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

Reply

Aaron Halfaker

16 Dec 16 Dec

3:14 a.m.

No! Please do not nuke old data. +1 to J-Mo. This will probably be useful for long-term studies of notifications. If I had the time, I'd pick it up right now based on this reminder! I'm happy with having historical data preserved (please makes sure that it is) and the MySQL table dropped until a recent point. It will be important that we can come back to this later and either restore the data or query it in it's entirety from hadoop. -Aaron On Tue, Dec 15, 2015 at 1:34 PM, Madhumitha Viswanathan < mviswanathan(a)wikimedia.org> wrote:

...

I want to mention that data in Hadoop is only available from Aug 27th 2015. Older data is only available in mysql. On Tue, Dec 15, 2015 at 11:27 AM, Roan Kattouw <rkattouw(a)wikimedia.org> wrote:

If the data is going to be retained but would just become harder to query (i.e. still in Hadoop but not in mysql), maybe we could nuke data that's more than a year old (or 6 months old or something) from mysql? On Tue, Dec 15, 2015 at 9:35 AM, Andrew Otto <aotto(a)wikimedia.org> wrote:

We could blacklist this schema from the mysql database, and still keep producing it. It would be available in Hadoop either way. On Dec 15, 2015, at 12:22, Jonathan Morgan <jmorgan(a)wikimedia.org> wrote: Hi Nuria, FWIW: Although I'm not using this right now, but I could see it being useful for understanding the impact of new notification updates that are coming down the pike.[1][2] What are the costs involved in keeping this schema up? Best, J 1. https://meta.wikimedia.org/wiki/Research:Cross-wiki_notifications_user_rese… 2. https://phabricator.wikimedia.org/T116741 On Tue, Dec 15, 2015 at 8:22 AM, Nuria Ruiz <nuria(a)wikimedia.org> wrote:

Roan: The data for Echo schema(https://meta.wikimedia.org/wiki/Schema:Echo) is quite large and we are not sure is even used. Can you confirm either way? If it is no longer used we will stop collecting it. Thanks, Nuria _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- Jonathan T. Morgan Senior Design Researcher Wikimedia Foundation User:Jmorgan (WMF) <https://meta.wikimedia.org/wiki/User:Jmorgan_(WMF)> _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

_______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- --Madhu :) _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

Reply

Dan Andreescu

4:07 p.m.

...

Just spoke with Jaime Crespo and he confirmed that: - m4-master (master EL database) only holds events for the last 45 days to avoid space problems. That's for all tables including Echo. - analytics-storage is the replica that keeps the historical data and is meant to apply the specific purging strategy agreed in the schema's talk page. This database does not have space problems (yet). Sure, it doesn't have space problems, but the problem remains that with a

table this large, it's impossible to query and get results in our lifetime. So we need to come up with some better solutions where we have these huge volumes of valuable data. I think in this case moving all of the data to Hadoop and blacklisting it from the mysql inserter seems like the right thing to do. The only reason for data to exist in mysql should be if we're querying data on a frequent period basis and taking actions based on the results of those queries. Otherwise it's a waste of resources and we should allocate that disk space to something else.

Reply

Nuria Ruiz

6:55 p.m.

...

I think in this case moving all of the data to Hadoop and blacklisting it

from the mysql inserter seems like the right thing to do.

...

I agree. We should implement partial auto-purging in Hadoop though. In the

Echo schema some fields should still be purged. Right, being able to move all this data to hadoop is contingent on having a purging strategy. Filed ticket to do this: https://phabricator.wikimedia.org/T121657 On Wed, Dec 16, 2015 at 6:55 AM, Marcel Ruiz Forns <mforns(a)wikimedia.org> wrote:

...

Sure, it doesn't have space problems, but the problem remains that with a

table this large, it's impossible to query and get results in our lifetime.

I see, makes sense. I think in this case moving all of the data to Hadoop and blacklisting it

from the mysql inserter seems like the right thing to do.

I agree. We should implement partial auto-purging in Hadoop though. In the Echo schema some fields should still be purged. On Wed, Dec 16, 2015 at 3:07 PM, Dan Andreescu <dandreescu(a)wikimedia.org> wrote:

Just spoke with Jaime Crespo and he confirmed that:

- m4-master (master EL database) only holds events for the last 45 days to avoid space problems. That's for all tables including Echo. - analytics-storage is the replica that keeps the historical data and is meant to apply the specific purging strategy agreed in the schema's talk page. This database does not have space problems (yet). Sure, it doesn't have space problems, but the problem remains that with

a table this large, it's impossible to query and get results in our lifetime. So we need to come up with some better solutions where we have these huge volumes of valuable data. I think in this case moving all of the data to Hadoop and blacklisting it from the mysql inserter seems like the right thing to do. The only reason for data to exist in mysql should be if we're querying data on a frequent period basis and taking actions based on the results of those queries. Otherwise it's a waste of resources and we should allocate that disk space to something else. _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- *Marcel Ruiz Forns* Analytics Developer Wikimedia Foundation _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

Reply

Neil P. Quinn

7:34 p.m.

...

*Schema:Edit contains no useful information that isn't already in the database apart from which button people use to thank each other,*

I assume you mean Schema:Echo? :) On Tue, Mar 1, 2016 at 11:58 PM, Roan Kattouw <rkattouw(a)wikimedia.org> wrote:

...

[Reviving old thread] I was looking at our EventLogging data today, and discovered that Schema:Edit contains no useful information that isn't already in the database apart from which button people use to thank each other, and if we really care about that we can measure it separately without producing nine gigs of unused data. Feel free to delete the data associated with Schema:Echo (but not Schema:EchoInteraction! We do use that one) with extreme prejudice. I've also written a config patch to stop us from producing these events ( https://gerrit.wikimedia.org/r/#/c/274345/ ) which I will deploy in the SWAT on Thursday. I also found that a long-standing issue with duplicate events in Schema:EchoInteraction wasn't fixed yet, so I wrote a patch for that too: https://gerrit.wikimedia.org/r/274342 On Tue, Dec 15, 2015 at 11:16 AM, Jonathan Morgan <jmorgan(a)wikimedia.org> wrote:

Hi Nuria! Speaking for *my own particular scenario*, that solution sounds like it will be fine, since I don't plan on immediately performing research with these data. But it's obviously still the Collab team's call here--they likely have needs I know nothing about. Cc'ing Joe Matazzoni in case he's not following this already... J On Tue, Dec 15, 2015 at 9:50 AM, Nuria Ruiz <nuria(a)wikimedia.org> wrote:

We could blacklist this schema from the mysql database, and still keep

producing it. It would be available in Hadoop either way. Right but I would also like to drop the table if it is not being used, if data is not going to be looked at soonish there is no point in storing as it will likely be deleted before it gets looked at. Thanks, Nuria On Tue, Dec 15, 2015 at 9:35 AM, Andrew Otto <aotto(a)wikimedia.org> wrote:

We could blacklist this schema from the mysql database, and still keep producing it. It would be available in Hadoop either way. On Dec 15, 2015, at 12:22, Jonathan Morgan <jmorgan(a)wikimedia.org> wrote: Hi Nuria, FWIW: Although I'm not using this right now, but I could see it being useful for understanding the impact of new notification updates that are coming down the pike.[1][2] What are the costs involved in keeping this schema up? Best, J 1. https://meta.wikimedia.org/wiki/Research:Cross-wiki_notifications_user_rese… 2. https://phabricator.wikimedia.org/T116741 On Tue, Dec 15, 2015 at 8:22 AM, Nuria Ruiz <nuria(a)wikimedia.org> wrote: > Roan: > > The data for Echo schema(https://meta.wikimedia.org/wiki/Schema:Echo) > is quite large and we are not sure is even used. > > Can you confirm either way? If it is no longer used we will stop > collecting it. > > > Thanks, > > Nuria > > _______________________________________________ > Analytics mailing list > Analytics(a)lists.wikimedia.org > https://lists.wikimedia.org/mailman/listinfo/analytics > > -- Jonathan T. Morgan Senior Design Researcher Wikimedia Foundation User:Jmorgan (WMF) <https://meta.wikimedia.org/wiki/User:Jmorgan_(WMF)> _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

_______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- Jonathan T. Morgan Senior Design Researcher Wikimedia Foundation User:Jmorgan (WMF) <https://meta.wikimedia.org/wiki/User:Jmorgan_(WMF)>

_______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

-- Neil P. Quinn <https://meta.wikimedia.org/wiki/User:Neil_P._Quinn-WMF>, product analyst Wikimedia Foundation

Reply

Dan Andreescu

8:11 p.m.

Reply