MySQL Forums :: Partitioning :: Advice on partitioning

Advanced Search

Re: Advice on partitioning
Posted by: Rick James ()
Date: April 24, 2015 05:34PM

Based on what you say, I see no advantage from PARTITIONing. Furthermore, 1000 partitions will show you down (until about 5.7).

But maybe PARTITIONing could be useful...

Please provide SHOW CREATE TABLE -- This is to study the potential impact of PARTITIONing on the indexes, and vice versa.

Please provide a sampling of the important queries -- This is to judge which (if any!) will be sped up by PARTITIONing.

Please provide some size information -- This is to get a feel for CPU-bound vs IO-bound.

Some notes:
* In almost no case will a point query (eg, SELECT on PRIMARY KEY) be sped up by partitioning.
* INSERTs/UPDATEs open _all_ partitions before deciding what to do -- This can be a drawback, especially for 1000 partitions. (Oracle is working on 'fixing' that.)
* I have yet to see a significant performance improvement for anything other than BY RANGE.

Since you say that BY HASH is 'better', I am eager to hear more details.

Options: ReplyQuote

Subject Views Written By Posted
Advice on partitioning 1600 me su 04/24/2015 01:40PM
Re: Advice on partitioning 912 Rick James 04/24/2015 05:34PM
Re: Advice on partitioning 850 Mehdi Salarkia 04/27/2015 09:05AM
Re: Advice on partitioning 896 Rick James 04/27/2015 02:22PM
Re: Advice on partitioning 749 Drew Morris 04/28/2015 11:02AM
Re: Advice on partitioning 783 Rick James 04/28/2015 12:32PM
Re: Advice on partitioning 810 Mehdi Salarkia 04/28/2015 04:07PM
Re: Advice on partitioning 659 Mehdi Salarkia 06/15/2015 06:39PM
Re: Advice on partitioning 803 Rick James 06/15/2015 07:05PM
Re: Advice on partitioning 751 Mehdi Salarkia 06/16/2015 06:40PM

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.