(+ Eric) On Mon, Jun 8, 2015 at 5:42 PM, Toby Negrin <tnegrin(a)wikimedia.org> wrote: -- Gabriel Wicke Principal Engineer, Wikimedia Foundation

WMF uses PostGRES for some things, no? Or is that is just in labs? > On Jun 8, 2015, at 17:42, Toby Negrin &lt;tnegrin(a)wikimedia.org&gt; wrote: > > > -Toby > > On Mon, Jun 8, 2015 at 4:46 PM, Marcel Ruiz Forns &lt;mforns(a)wikimedia.org <mailto:mforns@wikimedia.org>> wrote: > This discussion is intended to be a branch of the thread: "[Analytics] Pageview API Status update". > > Hi all, > > We Analytics are trying to choose a storage technology to keep the pageview data for analysis. > > We don't want to get to a final system that covers all our needs yet (there are still things to discuss), but have something that implements the current stats.grok.se <http://stats.grok.se/> functionalities as a first step. This way we can have a better grasp of which will be our difficulties and limitations regarding performance and privacy. > > The objective of this thread is to choose 3 storage technologies. We will later setup an fill each of them with 1 day of test data, evaluate them and decide which one of them we will go for. > > There are 2 blocks of data to be stored: > Cube that represents the number of pageviews broken down by the following dimensions: > day/hour (size: 24) > project (size: 800) > agent type (size: 2) > To test with an initial level of anonymity, all cube cells whose value is less than k=100 have an undefined value. However, to be able to retrieve aggregated values without loosing that undefined counts, all combinations of slices and dices are precomputed before anonymization and belong to the cube, too. Like this: > > dim1, dim2, dim3, ..., dimN, val > a, null, null, ..., null, 15 // pv for dim1=a > a, x, null, ..., null, 34 // pv for dim1=a & dim2=x > a, x, 1, ..., null, 27 // pv for dim1=a & dim2=x & dim3=1 > a, x, 1, ..., true, undef // pv for dim1=a & dim2=x & ... & dimN=true > > So the size of this dataset would be something between 100M and 200M records per year, I think. > > Timeseries dataset that stores the number of pageviews per article in time with: > maximum resolution: hourly > diminishing resolution over time is accepted if there are performance problems > article (dialect.project/article), day/hour, value > en.wikipedia/Main_page, 2015-01-01 17, 123456 > en.wiktionary/Bazinga, 2015-01-02 13, 23456 > > It's difficult to calculate the size of that. How many articles do we have? 34M? > But not all of them will have pageviews every hour... > > > Note: I guess we should consider that the storage system will presumably have high volume batch inserts every hour or so, and queries that will be a lot more frequent but also a lot lighter in data size. > > And that is that. > So please, feel free to suggest storage technologies, comment, etc! > And if there is any assumption I made in which you do not agree, please comment also! > > I will start the thread with 2 suggestions: > 1) PostgreSQL: Seems to be able to handle the volume of the data and knows how to implement diminishing resolution for timeseries. > 2) Project Voldemort: As we are denormalizing the cube entirely for anonymity, the db doesn't need to compute aggregations, so it may well be a key-value store. > > Cheers! > > Marcel > > > _______________________________________________ > 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> > > > _______________________________________________ > Analytics mailing list > Analytics(a)lists.wikimedia.org > https://lists.wikimedia.org/mailman/listinfo/analytics

Remember that (as things currently stand) putting the thing on labs means meta-analytics ("how are the cubes being used?") being a pain in the backside to integrate with our existing storage solutions. On 8 June 2015 at 22:52, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: -- Oliver Keyes Research Analyst Wikimedia Foundation

Does one of these options support both SQL based and REST interfaces with TSV and JSON output with little user setup? I'm thinking of your typical Ubuntu/Mac/Windows user with the ability to import TSV data into a spreadsheet. On Tuesday, June 9, 2015, Dario Taraborelli &lt;dtaraborelli(a)wikimedia.org&gt; wrote:

Eric, I think we should allow arbitrary querying on any dimension for that first data block. We could pre-aggregate all of those combinations pretty easily since the dimensions have very low cardinality. For the article-level data, no, we'd want just basic timeseries querying. Thanks Gabriel, if you could point us to an example of these secondary RESTBase indices, that'd be interesting. As for relational databases not being able to handle this, I remain optimistic. Toby, we're planning on diminishing the time resolution the way RRD files do, so growth over time should not be an issue. So the only problem is can we insert hourly data in bulk without killing the db? We'll have to run tests but assuming each hour is around 1GB, PostgreSQL should be able to insert this in a few minutes without too many problems (using COPY or pg_bulkload). Finding good alternatives to investigate is important though, the more tailored to the problem the better. So I'd like to work some more on this list: * Cassandra * PostgreSQL * Voldemort (lol, otto :)) On Tue, Jun 9, 2015 at 12:59 PM, Gabriel Wicke &lt;gwicke(a)wikimedia.org&gt; wrote:

On Tue, Jun 9, 2015 at 5:23 PM, Gabriel Wicke &lt;gwicke(a)wikimedia.org&gt; wrote: only one more right now, called "agent_type". But this is just the first "cube" and we're planning a geo cube with more dimensions and are probably going to try and release data split up by access method (mobile, desktop, etc.) and other dimensions as people need them. This will be tricky as we try to protect privacy but that aside, the number of dimensions per endpoint, right now, seems to hover around 4 or 5. very cool, thx.

This thread seems to have paused for 1 or 2 days now. So summarizing, the following storage technologies have been mentioned: - PostgreSQL - MySQL - Cassandra - Voldemort And the following concerns have been raised on using something that: - We're already familiar with - Permits meta-analytics - Is queriable for json/tsv with little user setup - Withstands high throughput bulk inserts - Is queriable for slice and dice, even if we need to precompute those It seems that there aren't many candidates and that the discussion focused on SQL vs NoSQL, so what about choosing 2 stores instead of 3, one of each type, say PostgreSQL and Cassandra? Or, anyone with more thoughts or suggestions? On Wed, Jun 10, 2015 at 1:24 PM, Marcel Ruiz Forns &lt;mforns(a)wikimedia.org&gt; wrote:

I think we don’t want to build the pageview API on top of the Analytics Cluster. > On Jun 12, 2015, at 05:37, Joseph Allemandou &lt;jallemandou(a)wikimedia.org&gt; wrote: > > It allows reading / computing straight from HDFS and should be fast enough for not too bad UEx. > Maybe ? > > > On Thu, Jun 11, 2015 at 11:11 PM, Marcel Ruiz Forns &lt;mforns(a)wikimedia.org <mailto:mforns@wikimedia.org>> wrote: > This thread seems to have paused for 1 or 2 days now. > > So summarizing, the following storage technologies have been mentioned: > PostgreSQL > MySQL > Cassandra > Voldemort > And the following concerns have been raised on using something that: > We're already familiar with > Permits meta-analytics > Is queriable for json/tsv with little user setup > Withstands high throughput bulk inserts > Is queriable for slice and dice, even if we need to precompute those > It seems that there aren't many candidates and that the discussion focused on SQL vs NoSQL, so what about choosing 2 stores instead of 3, one of each type, say PostgreSQL and Cassandra? > > Or, anyone with more thoughts or suggestions? > > > On Wed, Jun 10, 2015 at 1:24 PM, Marcel Ruiz Forns &lt;mforns(a)wikimedia.org <mailto:mforns@wikimedia.org>> wrote: > If we are going to completely denormalize the data sets for anonymization, > and we expect just slice and dice queries to the database, > I think we wouldn't take much advantage of a relational DB, > because it wouldn't need to aggregate values, slice or dice, > all slices and dices would be precomputed, right? > > It seems to me that the nature of this denormalized/anonymized data sets is more like a key-value store. That's why I suggested Voldemort at first (which, they say, has a slightly faster read than Cassandra), but I see the preference for Cassandra for it being a known tool inside WMF. > So, +1 for Cassandra! > > However, if we foresee the need of adding more data sets to the same DB, or querying them in a different way, key-value store would be a limitation. > > > On Wed, Jun 10, 2015 at 1:01 AM, Dan Andreescu &lt;dandreescu(a)wikimedia.org <mailto:dandreescu@wikimedia.org>> wrote: > > > On Tue, Jun 9, 2015 at 5:23 PM, Gabriel Wicke &lt;gwicke(a)wikimedia.org <mailto:gwicke@wikimedia.org>> wrote: > On Tue, Jun 9, 2015 at 11:53 AM, Dan Andreescu &lt;dandreescu(a)wikimedia.org <mailto:dandreescu@wikimedia.org>> wrote: > Eric, I think we should allow arbitrary querying on any dimension for that first data block. We could pre-aggregate all of those combinations pretty easily since the dimensions have very low cardinality. > > Are you thinking about something like /{project|all}/{agent|all}/{day}/{hour}, or will there be a lot more dimensions? > > only one more right now, called "agent_type". But this is just the first "cube" and we're planning a geo cube with more dimensions and are probably going to try and release data split up by access method (mobile, desktop, etc.) and other dimensions as people need them. This will be tricky as we try to protect privacy but that aside, the number of dimensions per endpoint, right now, seems to hover around 4 or 5. > > > For the article-level data, no, we'd want just basic timeseries querying. > > Thanks Gabriel, if you could point us to an example of these secondary RESTBase indices, that'd be interesting. > > The API used to define these tables is described in https://github.com/wikimedia/restbase/blob/master/doc/TableStorageAPI.md <https://github.com/wikimedia/restbase/blob/master/doc/TableStorageAPI.md>, and the algorithm used to keep those indexes up to date is described in https://github.com/wikimedia/restbase-mod-table-cassandra/blob/master/doc/S… <https://github.com/wikimedia/restbase-mod-table-cassandra/blob/master/doc/SecondaryIndexes.md> and largely implemented in https://github.com/wikimedia/restbase-mod-table-cassandra/blob/master/lib/s… <https://github.com/wikimedia/restbase-mod-table-cassandra/blob/master/lib/secondaryIndexes.js>. > > very cool, thx. > > _______________________________________________ > 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> > > > > > _______________________________________________ > 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> > > > > > -- > Joseph Allemandou > Data Engineer @ Wikimedia Foundation > IRC: joal > _______________________________________________ > Analytics mailing list > Analytics(a)lists.wikimedia.org > https://lists.wikimedia.org/mailman/listinfo/analytics

Andrew, Toby, that makes perfect sense. While thinking that the distributed aspect of Impala would handle high availability issues, I very much understand that having a front-end system relying on the analytics cluster is not as good as having a dedicated storage solution. Thanks for the good point :) Joseph On Fri, Jun 12, 2015 at 9:58 PM, Toby Negrin &lt;tnegrin(a)wikimedia.org&gt; wrote: -- *Joseph Allemandou* Data Engineer @ Wikimedia Foundation IRC: joal

Thanks, Gabriel – this is super-helpful. Dan/Kevin: slightly OT, are we aware of any use case related to features that would be exposing PV data in production? I’ve seen mocks from the Discovery team with PV data embedded in articles or search interfaces and I’m not sure what their status is.

You have the wrong attitude. Pretend it is beer-soda, not beer. Beer + sprite is yummy! (I’ll let someone else figure out how this advice also applies to the database analogy.) > On Jun 8, 2015, at 19:52, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: > >> As always, I'd recommend that we go with tech we are familiar with -- mysql or cassandra. We have a cassandra committer on staff who would be able to answer these questions in detail. > > WMF uses PostGRES for some things, no? Or is that is just in labs? > > Since this data is meant to be fully public and queryable in any way, we could put it in the PostgreSQL instance on labs. We should check with labs folks, perhaps horse trade some hardware, but I think that would be a splendid solution. > > However, and I'm trying to understate this in case people are not familiar with my hyperbolic style, I'd rather drink Bud Lite Lime than use MySQL for this. MySQL is suited for a lot of things, but analytics is not one of them. > > _______________________________________________ > Analytics mailing list > Analytics(a)lists.wikimedia.org > https://lists.wikimedia.org/mailman/listinfo/analytics