How do I efficiently partition a large MySQL (Aurora) table with multiple search columns?

Question

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)

Answer 1

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

Answer 2

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.