MySQL Forums
Forum List  »  Partitioning

Re: Partitioning dilemma
Posted by: Rick James
Date: July 21, 2010 11:58AM

Let's study this from a few different points of view...

* Accesses -- Do you "always" access "recent" rows? Or, to phrase another way, how often do you need to mess up the caching by reaching back into old partitions?

* Keys -- Remember the contorted rules of PRIMARY KEYs in PARTITION. AUTO_INCREMENT is somewhere between clumsy and/or unusable.

* ID vs Date "locality of reference" -- Are you using something like AUTO_INCREMENT for session ids and for transaction ids. If so, then all three things (Date, session_id, and xact_id) tend to grow at the "end" of the table. That is, indexes on any of these, plus the data itself, would tend to grow on the end. One hot spot each -- this is _very_ cachable.

* IDs -- AUTO_INCREMENT may have good locality; GUID has terrible.

* Will you be DELETEing rows? MyISAM or InnoDB? Partitioning by Date, then using ALTER PARTITION to do DELETEs? The last of these is very efficient and very fast. Any other deletion; well, let's discuss the ramifications on performance and locality.

* How long does a session last? -- If usually less than a day, that helps with the "locality".

* Efficiency -- In the best of worlds, only the last few blocks of data and of each index need be kept in cache, and performance shines. In the worst of worlds, performance will be terrible.

* Entire PK in cache -- may not be necessary.

* MyISAM or InnoDB?

Answer some of these questions and we can dig into details of how well PARTITION will perform, and even whether it will be any better than a pair of huge tables.

Options: ReplyQuote

Written By
July 20, 2010 08:51AM
Re: Partitioning dilemma
July 21, 2010 11:58AM
July 21, 2010 01:47PM
July 26, 2010 11:15PM
July 27, 2010 09:02AM
July 27, 2010 08:06PM

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.