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)

  • You're inserting 432 million rows per day? (almost 13 billion rows per month?!) – Michael - sqlbot Feb 24 '16 at 17:00
  • 1
    @Michael-sqlbot #bigdata baby. – Steve Eakin Feb 24 '16 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 '16 at 12:15
  • Is item #6 really 432 million rows? I hope not. – Rick James Mar 3 '16 at 6:23
  • Item #6 is, and it has actually grown since this post. – Steve Eakin Mar 7 '16 at 14:09

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,

  • 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? – Steve Eakin Feb 25 '16 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 '16 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 '16 at 11:15
  • My thought was either daily or monthly (where my -42 - -12 search would cover 2 tables... better than all.) – Steve Eakin Feb 26 '16 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 '16 at 7:47

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.


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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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