300GB per year should be trivial. It might increase slightly if we decide
to use a relational database for everything; daily data could live in
special flat files, and monthly could be in SQL.
In terms of hacks, we can use 2 bytes per date if we count days from Jan 1
2001 (day 1 A.W. = ante Wikipedia ;-) which would last for ~180 years. Down
to 10 bytes already!
And, how about two tables, one with 4 byte view counts for "much-viewed"
pages (few), and one with 2 bytes (<65.536 views/day) for the majority?
Probably saving another 1.9 bytes/row here, at the cost of more complex
low-level queries.
On Tue, Oct 1, 2013 at 11:52 PM, Christian Aistleitner <
christian(a)quelltextlich.at> wrote:
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:
2) Make simple datawarehouse schema for mysql db
(based on the current
webstatscollector datafiles)
Page Table
==========
page_id
page title
Let's ignore that table for now.
Fact Table
========
fact_id
Let's ignore "fact_id" as it's more than the bare pagecount files
provide.
page_id (FK -> Page Table)
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.
pageview date
DATE is 3 bytes
pageview count
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.
bytes served
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