MySQL Forums
Forum List  »  Partitioning

Re: Modify primary key for partitioning
Posted by: Rick James
Date: August 30, 2012 10:26PM

> PRIMARY KEY (`ID_MSG`,`RECEIVED_DATE`),
> /*!50500 PARTITION BY RANGE COLUMNS(RECEIVED_DATE)

That is likely to be a good pair -- it is usually less efficient to put the "partition by" column first in an index. This is because the first thing that happens is "partition pruning", wherein some partitions are excluded from further consideration.

> I tried the opposite order, it uses the index for the received_date but (as expected) not for ID_MSG (when received_date is not in the and I got some UPDATE on this column which took ages. So I decided to let the index like that (`ID_MSG`,`RECEIVED_DATE`)

Let's see the UPDATE. And any other queries that may be affected.

Plan A: Smaller ranges for the partitions -- this would make the pruning more effective.
Plan B: both indexes: PK(id, date), INDEX(date, id). The drawback is the extra effort for maintinaing the extra index.

What is the value of innodb_buffer_pool_size? Hopefully less than the 1GB you have for mysql.

> Some tables have up to 24 indexes

That is usually a mistake. An index on a true/false column is almost never used; DROP it. Similarly for indexes on single fields where the field has only a few distinct values, and is somewhat evenly distributed. Use "compound" indexes where it makes sense.

> and some others don't have any

Yuck!

InnoDB really likes to have an _explicit_ PRIMARY KEY -- either a 'natural' pk, or an artificial AUTO_INCREMENT.

> AVG(LENGTH(XMLCERTIFICATE)) : 320578
> AVG(LENGTH(XMLPOSTIT)) : 2511

Ouch. How often do you need those two fields? I'm thinking about "vertically partitioning" them into another table.

Interesting to hear that MariaDB was noticeably faster. Perhaps because it includes Percona's Xtradb instead of InnoDB.

> then more, shorter, partitions may be better.

Probably. But I would not go beyond 100 partitions.

> I don't use compression on the production database.

If your app compressed XMLCERTIFICATE, you could save _half_ of your 80GB! Smaller -> more cacheable -> faster. OK, maybe it would not save that much, since you are already using InnoDB's "COMPRESSED". Still, you would be doing it in a different way.

> Prevent from a too long backup strategy. Currently a full system copy is done. I'm thinkink of synchronising the data files (database <=> last full backup)

I'm not sure that will work for InnoDB, even with the unchanging partitions. Test before you depend on it!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Modify primary key for partitioning
1620
August 30, 2012 10:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.