Hi, today there were some guesses about how much data we are actually talking for the “queryable public interface for pageview data”. As no one followed-up on that, I did some quick “back of the envelope” computations. The pagecounts files now come with ~7-9M data points/hour. As we talked about computing "per day" data, some readings snuggle up and collapse, and for the two days that I computed I arrived at ~70M datapoints/day. So each day we can expect ~70M new data points = ~70M new rows. Now to the per row cost: On Mon, Sep 30, 2013 at 01:57:58PM -0700, Diederik van Liere wrote: Let's ignore that table for now. Let's ignore "fact_id" as it's more than the bare pagecount files provide. As we're having 70M different rows/day, MEDIUMINT is too small. Having an INT foreign key, we may also run out of numbers soon. But let's also ignore that for now, and settle with INT = 4 bytes. DATE is 3 bytes Zipping through some random pagecount files, I saw values up to ~17M/hour. 17M is to small for MEDIUMINT, so we have to take at least INT = 4 bytes. That also suffices for the per day sums. Zipping through some random pagecount files, I saw values up to ~54G/hour. That's too big for INT. So let's settle for BIGINT = 8 bytes. That also suffices for the per day sums. Summing up, we have 4+3+4+8 = 19 bytes per data point. 19 bytes * 70M * 365 days = 485 GB in 1 year. And here the 19 bytes are really minimal. It will suffer overrun ids. And id does not contain a row id. 70M is very, very defensive. And the total size is without the page table, without indices, without all the other stuff that comes in. Have fun, Christian P.S.: There was some discussion whether or not we should drop "bytes served", as "no one uses it". I am glad we did not second-guess the requirements and took the field in, as that bought us a nice argument for free, if we decide to drop/discuss it. "bytes served" is worth ~200GB in 1 year, when modelling it directly. While such a decrease would help, it would not solve the problem, as ~285 GB data per year remain. P.P.S.: Hope is not lost :-) There are of course thousands of ways to get the size down. -- ---- quelltextlich e.U. ---- \\ ---- Christian Aistleitner ---- Companies' registry: 360296y in Linz Christian Aistleitner Gruendbergstrasze 65a Email: christian(a)quelltextlich.at 4040 Linz, Austria Phone: +43 732 / 26 95 63 Fax: +43 732 / 26 95 63 Homepage: http://quelltextlich.at/ --------------------------------------------------------------- _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

300GB per year should be trivial. It might increase slightly if we decide to use a relational database for everything; [...]

[ exploiting structure in data ]

[...] at the cost of more complex low-level queries.

* Do we need to have all of the data in MySQL in a trivial schema at all costs, or * is it better to have the data at least in MySQL but with a not totally trivial way to query, or * would it be even better to have a nice clean interface to the data that you maybe cannot query by SQL but allows to formulate queries in a straight forward way? What would you prefer?

Magnus Manske, 02/10/2013 10:12: I'd second this. We have partners (but also, say, internal WikiProjects) working on a long tail of tens or hundreds thousand pages with their own project: cutting this long tail, including redlinks, would be a higher loss than a decrease in resolution.

Here is shameless plug for existing data files in a highly condensed format, which could serve as input for whatever database we choose. **** **

One goody of these files: they detect missing input files and correct monthly counts to make up for this.

Hm, I don't think we will have much trouble with the size of the input.

** ** Turns out all files were generated, but rsync failed recently.**** So all is up to date now. Monthly aggregation for Sep is running. **** ** ** Erik**** ** ** *From:* Erik Zachte [mailto:ezachte@wikimedia.org] *Sent:* Wednesday, October 02, 2013 6:04 PM *To:* 'A mailing list for the Analytics Team at WMF and everybody who has an interest in Wikipedia and analytics.' *Subject:* RE: [Analytics] Back of the envelope data size for "Queryable public interface for pageview data" [was: Re: Queryable public interface for pageview data]**** ** ** Here is shameless plug for existing data files in a highly condensed format, which could serve as input for whatever database we choose. **** ** ** Daily and monthly aggregated files are available.**** http://dumps.wikimedia.org/other/pagecounts-ez/merged/**** ** ** Daily files have all cruft included. Only monthly files are topped off (and that could be changed of course).**** http://dumps.wikimedia.org/other/pagecounts-ez/merged/2013/2013-08/**** ** ** Monthly files come in two variations, **** 1 article title and monthly total only**** 2 same plus hourly data for one month in one long string in a highly compressed manner, comma separated **** **** Hour: from 0 to 23, written as 0 = A, 1 = B ... 22 = W, 23 = X**** Day: from 1 to 31, written as 1 = A, 2 = B ... 25 = Y, 26 = Z, 27 = [, 28 = \, 29 = ], 30 = ^, 31 = _**** ** ** so 33 views on day 2 hour 4 and 55 on same day hour 7 becomes BD33,BG55,**** this obviously need machine unpacking when used**** ** ** One goody of these files: they detect missing input files and correct monthly counts to make up for this.**** ** ** PS**** Ah the dreaded demo effect ;-) I will see why files for last 40 days have not yet been generated.**** ** ** Erik**** ** ** ** ** *From:* analytics-bounces(a)lists.wikimedia.org [ mailto:analytics-bounces@lists.wikimedia.org<analytics-bounces@lists.wikimedia.org>] *On Behalf Of *Dan Andreescu *Sent:* Wednesday, October 02, 2013 5:16 PM *To:* A mailing list for the Analytics Team at WMF and everybody who has an interest in Wikipedia and analytics. *Subject:* Re: [Analytics] Back of the envelope data size for "Queryable public interface for pageview data" [was: Re: Queryable public interface for pageview data]**** ** ** On Wed, Oct 2, 2013 at 5:16 AM, Federico Leva (Nemo) &lt;nemowiki(a)gmail.com&gt; wrote:**** Magnus Manske, 02/10/2013 10:12:**** Depending on the absolute value of "all costs", I'd prefer #1, or a combination of #2&#3. For GLAM (which is what I am mostly involved in), monthly page views would suffice, and those should be easily done in MySQL.**** Daily views would be nice-to-have, but do not reed to be in MySQL. [...]** ** I'd second this. We have partners (but also, say, internal WikiProjects) working on a long tail of tens or hundreds thousand pages with their own project: cutting this long tail, including redlinks, would be a higher loss than a decrease in resolution.**** ** ** ** ** Thank you both for the response, this is very useful to know. If I'm hearing people correctly so far:**** ** ** * reduced resolution is OK, handle requests for higher resolution data further down the line.**** * hacking the data to reduce size is OK if needed, but preferably the hacks should not be lossy.**** * a database is not absolutely 100% necessary but is preferred.**** ** ** If that's right, I have an additional question: would a non-relational database be acceptable? I'm not saying we're planning this, just wondering what people think. If, for example, the data would be available in a public Cassandra cluster. Would people be willing to understand how CQL [1] works?**** ** ** ** ** [1] - http://cassandra.apache.org/doc/cql/CQL.html**** _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

I think before we settle on a specific data store, we should determine what are the top queries people are interested in running, whether they expect to have scripted access to this data or primarily design a tool for human access and whether applying a threshold and cutting the long tail of low-traffic articles is a good approach for most consumers of this data. The GLAM case described by Magnus is pretty well-defined, but I'd like to point out that: • a large number of Wikipedias point to stats.grok.se from the history page of every single article • most researchers I've been talking to are interested in daily or hourly pv data per article • tools with a large user base like https://en.wikipedia.org/wiki/User:West.andrew.g/Popular_pages refresh pv data on a weekly basis Should we list the requirements for different use cases on a wiki page where a larger number of people than the participants in this thread can voice their needs? Dario On Oct 2, 2013, at 8:16 AM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: On Wed, Oct 2, 2013 at 5:16 AM, Federico Leva (Nemo) &lt;nemowiki(a)gmail.com&gt;wrote;wrote: Thank you both for the response, this is very useful to know. If I'm hearing people correctly so far: * reduced resolution is OK, handle requests for higher resolution data further down the line. * hacking the data to reduce size is OK if needed, but preferably the hacks should not be lossy. * a database is not absolutely 100% necessary but is preferred. If that's right, I have an additional question: would a non-relational database be acceptable? I'm not saying we're planning this, just wondering what people think. If, for example, the data would be available in a public Cassandra cluster. Would people be willing to understand how CQL [1] works? [1] - http://cassandra.apache.org/doc/cql/CQL.html _______________________________________________ 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

I agree with Magnus; we decided to do a 'quick-and-dirty' approach that we can deliver in a single sprint (== 2 weeks). I think we defined the MVP as follows: 1) Import data at daily granularity -- yes we are fully aware of requests for more fine-grained data 2) Import data only for 2013 --- yes we are fully aware that people are likely to want to query the history 3) Import the data into a MySQL instance in Labs -- yes this might not scale to many dimensions and/or has sufficient write performance 4) Import the data using a very simple schema as specified in https://mingle.corp.wikimedia.org/projects/analytics/cards/1195 (one fact table and whe can extend it with other dimensions easily) 5) Community members can request a readonly mysql account to query the data This is something i believe we can deliver in one sprint -- it just exposes the data as-is. There are many more requests: 1) Data granularity 2) Cleaning the current data 3) Historic data 4) API 5) etc.. etc.. but let's deal with those issues as they are raised by real-users. By all standards we would be almost ashamed of releasing this and I think that's the exact place we should aim for. D On Wed, Oct 2, 2013 at 1:16 PM, Magnus Manske &lt;magnusmanske(a)googlemail.com&gt; wrote: I know I'm not completely unbiased here, but how long would a monthly-only SQL database take to create, compared to the "careful planning" approach? If it takes a few hours to write a per-month import script that will happily tick away in the background, I'd say go for it, and add more sophisticated things later. If it will take a programmer's week to do, I'd say wait for the survey. On Wed, Oct 2, 2013 at 6:10 PM, Dario Taraborelli &lt;dtaraborelli(a)wikimedia.org&gt; wrote: I think before we settle on a specific data store, we should determine what are the top queries people are interested in running, whether they expect to have scripted access to this data or primarily design a tool for human access and whether applying a threshold and cutting the long tail of low-traffic articles is a good approach for most consumers of this data. The GLAM case described by Magnus is pretty well-defined, but I'd like to point out that: • a large number of Wikipedias point to stats.grok.se from the history page of every single article • most researchers I've been talking to are interested in daily or hourly pv data per article • tools with a large user base like https://en.wikipedia.org/wiki/User:West.andrew.g/Popular_pages refresh pv data on a weekly basis Should we list the requirements for different use cases on a wiki page where a larger number of people than the participants in this thread can voice their needs? Dario On Oct 2, 2013, at 8:16 AM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics -- undefined _______________________________________________ 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

I think we're on the same page – I'm very much in favor of a quick and dirty release, just saying that we cannot answer the SQL vs NoSQL or what dimensions to expose without a better understanding of what data people need: pushing data out and let consumers play with it is by far the best approach to identify requirements. On Oct 2, 2013, at 10:26 AM, Diederik van Liere &lt;dvanliere(a)wikimedia.org&gt; wrote: I agree with Magnus; we decided to do a 'quick-and-dirty' approach that we can deliver in a single sprint (== 2 weeks). I think we defined the MVP as follows: 1) Import data at daily granularity -- yes we are fully aware of requests for more fine-grained data 2) Import data only for 2013 --- yes we are fully aware that people are likely to want to query the history 3) Import the data into a MySQL instance in Labs -- yes this might not scale to many dimensions and/or has sufficient write performance 4) Import the data using a very simple schema as specified in https://mingle.corp.wikimedia.org/projects/analytics/cards/1195 (one fact table and whe can extend it with other dimensions easily) 5) Community members can request a readonly mysql account to query the data This is something i believe we can deliver in one sprint -- it just exposes the data as-is. There are many more requests: 1) Data granularity 2) Cleaning the current data 3) Historic data 4) API 5) etc.. etc.. but let's deal with those issues as they are raised by real-users. By all standards we would be almost ashamed of releasing this and I think that's the exact place we should aim for. D On Wed, Oct 2, 2013 at 1:16 PM, Magnus Manske &lt;magnusmanske(a)googlemail.com > I know I'm not completely unbiased here, but how long would a > monthly-only SQL database take to create, compared to the "careful > planning" approach? > > If it takes a few hours to write a per-month import script that will > happily tick away in the background, I'd say go for it, and add more > sophisticated things later. > > If it will take a programmer's week to do, I'd say wait for the survey. > > > On Wed, Oct 2, 2013 at 6:10 PM, Dario Taraborelli < > dtaraborelli(a)wikimedia.org&gt; wrote: > >> I think before we settle on a specific data store, we should determine >> what are the top queries people are interested in running, whether they >> expect to have scripted access to this data or primarily design a tool for >> human access and whether applying a threshold and cutting the long tail of >> low-traffic articles is a good approach for most consumers of this data. >> >> The GLAM case described by Magnus is pretty well-defined, but I'd like >> to point out that: >> • a large number of Wikipedias point to stats.grok.se from the history >> page of every single article >> • most researchers I've been talking to are interested in daily or >> hourly pv data per article >> • tools with a large user base like >> https://en.wikipedia.org/wiki/User:West.andrew.g/Popular_pages refresh >> pv data on a weekly basis >> >> Should we list the requirements for different use cases on a wiki page >> where a larger number of people than the participants in this thread can >> voice their needs? >> >> Dario >> >> On Oct 2, 2013, at 8:16 AM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; >> wrote: >> >> On Wed, Oct 2, 2013 at 5:16 AM, Federico Leva (Nemo) &lt;nemowiki(a)gmail.com >>>> >>> Magnus Manske, 02/10/2013 10:12: >>> >>>> Depending on the absolute value of "all costs", I'd prefer #1, or a >>>> combination of #2&#3. >>>> >>>> For GLAM (which is what I am mostly involved in), monthly page views >>>> would suffice, and those should be easily done in MySQL. >>>> >>>> Daily views would be nice-to-have, but do not reed to be in MySQL. >>>> [...] >>>> >>> >>> I'd second this. We have partners (but also, say, internal >>> WikiProjects) working on a long tail of tens or hundreds thousand pages >>> with their own project: cutting this long tail, including redlinks, would >>> be a higher loss than a decrease in resolution. >> >> >> >> Thank you both for the response, this is very useful to know. If I'm >> hearing people correctly so far: >> >> * reduced resolution is OK, handle requests for higher resolution data >> further down the line. >> * hacking the data to reduce size is OK if needed, but preferably the >> hacks should not be lossy. >> * a database is not absolutely 100% necessary but is preferred. >> >> If that's right, I have an additional question: would a non-relational >> database be acceptable? I'm not saying we're planning this, just wondering >> what people think. If, for example, the data would be available in a >> public Cassandra cluster. Would people be willing to understand how CQL >> [1] works? >> >> >> [1] - http://cassandra.apache.org/doc/cql/CQL.html >> _______________________________________________ >> 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 >> >> > > > -- > undefined > > _______________________________________________ > 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

Here is shameless plug for existing data files in a highly condensed format, which could serve as input for whatever database we choose. Daily and monthly aggregated files are available. http://dumps.wikimedia.org/other/pagecounts-ez/merged/

I agree with Magnus; we decided to do a 'quick-and-dirty' approach that we can deliver in a single sprint (== 2 weeks). I think we defined the MVP as follows: 1) Import data at daily granularity -- yes we are fully aware of requests for more fine-grained data 2) Import data only for 2013 --- yes we are fully aware that people are likely to want to query the history 3) Import the data into a MySQL instance in Labs -- yes this might not scale to many dimensions and/or has sufficient write performance 4) Import the data using a very simple schema as specified in https://mingle.corp.wikimedia.org/projects/analytics/cards/1195 (one fact table and whe can extend it with other dimensions easily) 5) Community members can request a readonly mysql account to query the data This is something i believe we can deliver in one sprint -- it just exposes the data as-is. There are many more requests: 1) Data granularity 2) Cleaning the current data 3) Historic data 4) API 5) etc.. etc.. but let's deal with those issues as they are raised by real-users. By all standards we would be almost ashamed of releasing this and I think that's the exact place we should aim for. D On Wed, Oct 2, 2013 at 1:16 PM, Magnus Manske &lt;magnusmanske(a)googlemail.com > I know I'm not completely unbiased here, but how long would a > monthly-only SQL database take to create, compared to the "careful > planning" approach? > > If it takes a few hours to write a per-month import script that will > happily tick away in the background, I'd say go for it, and add more > sophisticated things later. > > If it will take a programmer's week to do, I'd say wait for the survey. > > > On Wed, Oct 2, 2013 at 6:10 PM, Dario Taraborelli < > dtaraborelli(a)wikimedia.org&gt; wrote: > >> I think before we settle on a specific data store, we should determine >> what are the top queries people are interested in running, whether they >> expect to have scripted access to this data or primarily design a tool for >> human access and whether applying a threshold and cutting the long tail of >> low-traffic articles is a good approach for most consumers of this data. >> >> The GLAM case described by Magnus is pretty well-defined, but I'd like >> to point out that: >> • a large number of Wikipedias point to stats.grok.se from the history >> page of every single article >> • most researchers I've been talking to are interested in daily or >> hourly pv data per article >> • tools with a large user base like >> https://en.wikipedia.org/wiki/User:West.andrew.g/Popular_pages refresh >> pv data on a weekly basis >> >> Should we list the requirements for different use cases on a wiki page >> where a larger number of people than the participants in this thread can >> voice their needs? >> >> Dario >> >> On Oct 2, 2013, at 8:16 AM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; >> wrote: >> >> On Wed, Oct 2, 2013 at 5:16 AM, Federico Leva (Nemo) &lt;nemowiki(a)gmail.com >>>> >>> Magnus Manske, 02/10/2013 10:12: >>> >>>> Depending on the absolute value of "all costs", I'd prefer #1, or a >>>> combination of #2&#3. >>>> >>>> For GLAM (which is what I am mostly involved in), monthly page views >>>> would suffice, and those should be easily done in MySQL. >>>> >>>> Daily views would be nice-to-have, but do not reed to be in MySQL. >>>> [...] >>>> >>> >>> I'd second this. We have partners (but also, say, internal >>> WikiProjects) working on a long tail of tens or hundreds thousand pages >>> with their own project: cutting this long tail, including redlinks, would >>> be a higher loss than a decrease in resolution. >> >> >> >> Thank you both for the response, this is very useful to know. If I'm >> hearing people correctly so far: >> >> * reduced resolution is OK, handle requests for higher resolution data >> further down the line. >> * hacking the data to reduce size is OK if needed, but preferably the >> hacks should not be lossy. >> * a database is not absolutely 100% necessary but is preferred. >> >> If that's right, I have an additional question: would a non-relational >> database be acceptable? I'm not saying we're planning this, just wondering >> what people think. If, for example, the data would be available in a >> public Cassandra cluster. Would people be willing to understand how CQL >> [1] works? >> >> >> [1] - http://cassandra.apache.org/doc/cql/CQL.html >> _______________________________________________ >> 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 >> >> > > > -- > undefined > > _______________________________________________ > 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