2

I have a large and rapidly growing table. It takes in about 5k rows / second and that will be doubling in about a month. The table is Amazon's Aurora (heavily modified InnoDB.)

As of today, total aggregate rows is around 4B.

Data is mostly searched on in 6 ways:

  1. count(distinct col1,col2) where timestamp range (1 day range)
  2. count(distinct col1,col2) where timestamp range (30 day range)
  3. count(1) where country IN (list,of,countries) and timestamp range (30 day range)
  4. count(1) where foreign_key = int and timestamp range (1 day range)
  5. count(1) where foreign_key = int and timestamp range (30 day range)
  6. select * where timestamp range (1 day range)

The foreign_key, timestamp and country columns are all indexed.

Data is only really "active" for a 90 day periods with most select's happening between -42 and -12 days.

How do I effectively partition this able to speed up those 6 queries?

And, almost as important, how do I accomplish this with minimal downtime? (assumption here is to create a new table, move all inserts to the new table & copy old data in... like I would do with any new index)

5
  • You're inserting 432 million rows per day? (almost 13 billion rows per month?!) Feb 24, 2016 at 17:00
  • 1
    @Michael-sqlbot #bigdata baby. Feb 24, 2016 at 17:58
  • I had one similar receiving data from a syslog service taking in this much data (and up to 10k per second when busy), it was a great deal of fun! :)
    – Dave Rix
    Feb 25, 2016 at 12:15
  • Is item #6 really 432 million rows? I hope not.
    – Rick James
    Mar 3, 2016 at 6:23
  • Item #6 is, and it has actually grown since this post. Mar 7, 2016 at 14:09

2 Answers 2

2

I would consider using a date range as the partitioning scheme, and splitting by week which you can do using a function against the timestamp field. Using days would create too many partitions, and months would not help your queries much especially when the range spans two months.

Using the range partition, you can create mutliple partitions covering your active time period, and out into the future for a year, then nearer the end of that range you can add partitions to the schema as required.
If necessary, you can also drop the old partitions when they are no longer needed.

I can't prepare a sample partition schema for you, as I'm running short of time at the moment, but the MySQL docs have pretty good coverage for this type of partitioning.

Hope that helps,
Dave

9
  • How would the range look if each day I need to look between -42 and -12 days? Does that mean that each day there would be a 30 day range that starts 42 days ago and ends 12 days ago? Feb 25, 2016 at 14:54
  • 1
    Assume that you build the range based on calendar weeks (Mon-Sun), and today is Feb 26th. Your query will be asking for a range from Jan 14th to Feb 14th (approx), and will touch 5 weekly partitions. If you used monthly, you'd touch 2 monthly partitions which would include an additional 3 weeks of data, which at your volumes would be considerable!
    – Dave Rix
    Feb 26, 2016 at 11:14
  • You can't create a partition for each day covering data -42 to -12, as that would mean data would need to be in multiple partitions, which is not allowed.
    – Dave Rix
    Feb 26, 2016 at 11:15
  • My thought was either daily or monthly (where my -42 - -12 search would cover 2 tables... better than all.) Feb 26, 2016 at 18:01
  • Definitely, but with your volume, 5 weekly tables would still be better than 2 monthly tables - roughly 5/8 of the data to be processed.
    – Dave Rix
    Feb 29, 2016 at 7:47
1

Queries #1-5 are best done with Summary tables.

The table for Query one is populated from

SELECT day, COUNT(DISTINCT col1, col2) FROM ...

Building Summary Tables.

Question... Can query #2 be derived from summing up the Summary for the 30 days? If so, then you don't need another table for it. If not, then do you need can you live with about a 1% error in the COUNT-DISTINCT? If so, summing the counts is complex, either have 30-day counts as well (in another table), or Rollup uniques counts.

Query #3 is derived from

SELECT day, country, COUNT(*) FROM ... GROUP BY day, country

Then SUM() the counts to get any day-range.

Queries 4, 5 are like #3.

Query #6 -- explain what you are going to do with 432M rows; you should consider how else to perform the real query.

Partitioning...

The only use for partitioning in what you have describe is not for performance, but for DELETEing after 90 days. Details

But... Even better would be to collect the data for only one day, summarize it, then toss the entire day's data.

If you have trouble with ingesting data at 5K/sec, see high speed ingestion.

Having 3 secondary keys on a 40-billion row table is untenable. Once the buffer_pool cannot hold enough of 4 BTrees (data + 3 secondary indexes), you won't be able to insert at 5K/sec. due to I/O. With summary tables, you can probably get rid of all the secondary keys. Then, the table is "append only", which involves very little I/O.

Please provide SHOW CREATE TABLE. I would like to critique the datatypes. Changing an INT to a MEDIUMINT will save 40GB of disk space! And make the data more cacheable.

2
  • Query #2 can not be derived from a sum of the query #1. Mar 7, 2016 at 14:07
  • As a note, the DB can handle about 85k / second inserts with this table in load tests Mar 7, 2016 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.