Efficient MySQL schema with partitioning for huge dataset (7.300.000.000 rows and roughly 80 GB of data)

Question

This is a follow up to my question "Efficiently storing 7.300.000.000 rows" (http://stackoverflow.com/questions/665614/efficiently-storing-7-300-000-000-rows).

I've decided to use MySQL with partitioning and the preliminary schema looks like this:

CREATE TABLE entity_values (
  entity_id MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, # 3 bytes = [0 .. 16.777.215]
  date_id SMALLINT UNSIGNED DEFAULT 0 NOT NULL, # 2 bytes = [0 .. 65.535]
  value_1 MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, # 3 bytes = [0 .. 16.777.215]
  value_2 MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, # 3 bytes = [0 .. 16.777.215]
  UNIQUE KEY (entity_id, date_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PARTITION BY HASH(entity_id) PARTITIONS 25;

This gives:

• Rows = 7.300.000.000 rows (as per the requirements stated in the previous post)
• Size/row = 11 bytes (3+2+3+3)
• Total size = 7.300.000.000 rows * 11 bytes = 80.300.000.000 bytes = 80.3 GB
• Partitions = 25 (3.2 GB / partition, the partion size is somewhat arbitrary)

Please note that I've dropped the primary key from the original design since the "id" column won't be used.

Now to my question - given the requirements outlined in my previous post and the schema above, do you have any suggestions on further optimizations/tweaks that can be made? Or is the above schema "optimal" given that I've decided to use MySQL?

Update: I tried loading the current data set into the schema above and the 8.570.532 rows took 212.000.000 bytes worth of disk space, which gives roughly 24.7 bytes per row.

Update: Please note that the index covering entity_id+date_id will be used also for queries only targeting entity_id.

Answer 1

You indicated in your previous question that you will retrieve all rows for an entity_id; however, if you plan on retrieving date ranges on specific entities, you could use Sub-paritioning (also known as composite partitioning). Depending on your usage, you could have your main partition be entity_id and the sub-partition as year or other date range. You can also reverse that if it makes sense in your system.

Answer 2

If you typically retrieve all (or most) the data for one entity Id you should consider making the index just entity id and not (entity_id,date_id) -- unless you need the database to do unique checks.

The effect is to make the index smaller so you get more of it in memory. Your goal should be to get the index to be in memory. Even if you need to do SELECT..ORDER BY DATE you will find that MySQL can order 3650 values in a fraction of a second on the fly (without the index). This issue is the time to read the rows from disk.

However your main performance issue is that the INSERTs result in data for one entity spread out across the disk requiring a disk access of each (entity,date) which will make your query run at a few hundred rows per second. Your partitioning wont help this becaause each entity is in a single partition and the rows are spread across its disk. (RAID0 on the disks will help a bit).

To get efficient retrieval you need to get the data for a entity to be contiguous on disk, which means reordering the data from the INSERT order. You can do this with MySQL ALTER TABLE.. ORDER BY ... but it takes forever. I have had a 182M row table doing a ALTER TABLE.. ORDER BY running for the last 2 weeks and it hasn't finished yet.

That's why I wrote a custom storage engine!

By the way, I'm not sure that you gain anything at all by partitioning unless you are partitioning across multiple servers -- or at least multiple disks. The hard work that MySQL has to do isn't made any easier by partitioning. It's all about disk access times.

Putting each partition on a different disk might help. I would not have more than twice as many partitions as you have physical disks. 2 times, rather than 1 times, would give some queuing benefits, but I doubt that would have much effect. I doubt you get much better than a single non-partitioned table using RAID0 across as many disks as you can.

The performance of this application is determined by the number of disk seeks, and therefore helped if you can do more seeks per second.

You gain some processing parallelism (assuming you have multiple processors) with partitioning but your system is going to be I/O bound, not processor bound. If you get your processor utilization as high as 2% you are probably doing something you don't need do (or something that isn't your application).

I have been writing, optimizing and operating this kind of application for the nine years using MySQL ... and I have all the scars you might expect from the experience. Once your data is vastly larger than the size of your memory (which is my definition of "huge"), the entire performance issue is Disk I/O which means primary the number of disk seeks. Good luck!!

Answer 3

One thing I don't quite understand, is how you plan to prune your data. You have 2M rows per day, but you haven't specified how much data you plan to keep. At some point you will want to expire data by age (in all likelihood).

At this point, you'll want to do it by dropping partitions, NOT by executing a delete which locks up every single partition for an incredibly long time (as it has to do a full table scan to find the rows to delete), then leaves your table no smaller as the partitions are full of holes.

Partitioning by hash of entity_id might seem sensible for searching, but partitioning by time could ease contention when you come to prune old data, and will definitely be a good thing.

MyISAM has a feature called "concurrent insert" which you will almost definitely need to use all the time in order to achieve concurrency and performance; this mandates a "no deletes" rule, meaning that you can only do deletes by dropping partitions.

But dropping partitions is also good because you can get the disc space back.

Having said all of this, 80G isn't that big and I might have been tempted to store it all in a single table, and use InnoDB to enable concurrent access.

Oh yes, and if you did use InnoDB, you could have a primary key of entity_id, date_id, which means it would cluster rows with the same entity_id. You'd probably want a secondary index on date_id to enable efficient pruning.

Please test this with your production data sizes and let us know what you find!