How to partition a MyISAM table by day in MySQL

I want to keep the last 45 days of log data in a MySQL table for statistical reporting purposes. Each day could be 20-30 million rows. I'm planning on creating a flat file and using load data infile to get the data in there each day. Ideally I'd like to have each day on it's own partition without having to write a script to create a partition every day. 开发者_运维问答

Is there a way in MySQL to just say each day gets it's own partition automatically?

thanks

---

I would strongly suggest using Redis or Cassandra rather than MySQL to store high traffic data such as logs. Then you could stream it all day long rather than doing daily imports.

You can read more on those two (and more) in this comparison of "NoSQL" databases.

If you insist on MySQL, I think the easiest would just be to create a new table per day, like logs_2011_01_13 and then load it all in there. It makes dropping older dates very easy and you could also easily move different tables on different servers.

---

er.., number them in Mod 45 with a composite key and cycle through them...

Seriously 1 table per day was a valid suggestion, and since it is static data I would create packed MyISAM, depending upon my host's ability to sort.

Building queries to union some or all of them would be only moderately challenging.

1 table per day, and partition those to improve load performance.

---

Yes, you can partition MySQL tables by date:

CREATE TABLE ExampleTable (
  id INT AUTO_INCREMENT,
  d DATE,
  PRIMARY KEY (id, d)
) PARTITION BY RANGE COLUMNS(d) (
  PARTITION p1 VALUES LESS THAN ('2014-01-01'),
  PARTITION p2 VALUES LESS THAN ('2014-01-02'),
  PARTITION pN VALUES LESS THAN (MAXVALUE)
);

Later, when you get close to overflowing into partition pN, you can split it:

ALTER TABLE ExampleTable REORGANIZE PARTITION pN INTO (
  PARTITION p3 VALUES LESS THAN ('2014-01-03'), 
  PARTITION pN VALUES LESS THAN (MAXVALUE)
);

This doesn't automatically partition by date, but you can reorganize when you need to. Best to reorganize before you fill the last partition, so the operation will be quick.

---

I have stumbled on this question while looking for something else and wanted to point out the MERGE storage engine (http://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html).

The MERGE storage is more or less a simple pointer to multiple tables, and can be redone in seconds. For cycling logs, it can be very powerfull! Here's what I'd do:

Create one table per day, use LOAD DATA as OP mentionned to fill it up. Once it is done, drop the MERGE table and recreate it including that new table while ommiting the oldest one. Once done, I could delete/archive the old table. This would allow me to rapidly query a specific day, or all as both the orignal tables and the MERGE are valid.

CREATE TABLE logs_day_46 LIKE logs_day_45 ENGINE=MyISAM;
DROP TABLE IF EXISTS logs;
CREATE TABLE logs LIKE logs_day_46 ENGINE=MERGE UNION=(logs_day_2,[...],logs_day_46);
DROP TABLE logs_day_1;

Note that a MERGE table is not the same as a PARTIONNED one and offer some advantages and inconvenients. But do remember that if you are trying to aggregate from all tables it will be slower than if all data was in only one table (same is true for partitions, as they are basically different tables under the hood). If you are going to query mostly on specific days, you will need to choose the table yourself, but if partitions are done on the day values, MySQL will automatically grab the correct table(s) which might come out faster and easier to write.