MySQL Forums :: Partitioning :: large InnoDB table partitioning without explicit PK


Advanced Search

Re: large InnoDB table partitioning without explicit PK
Posted by: Miko M ()
Date: November 18, 2014 01:13AM

Thanks for the hints, please find my feedback inline.

> > * from Table1 where col2 like '2014-11-16 1%';
> -->
> WHERE col1 >= '2014-11-16 10:00:00'
> AND col1 < '2014-11-16 10:00:00' + INTERVAL 10
> HOUR
[Miko] Sorry, my mistake, I should have written:
"* from Table1 where col2 like '2014-11-16 10:%';"
(10 min chunks of data)
And I've noticed that the condition of:
"WHERE col1 >= '2014-11-16 10:00:00' AND col1 < '2014-11-16 10:00:00' + INTERVAL 10 MINUTE"
does not help much with MariaDB 5.2, the partition pruning is triggered for such condition.
However it works well in more recent versions (5.5.x and 10.0.x), I've already tested it.

> > [Miko] You mean analyse/check/optimise/repair
> table, is it correct?
>
> No. Things like walking through the table to
> delete parts of it, or to study the values. As I
> say, subtle.
[Miko] Ok, I see.

> > [Miko] There will be a "fresh" installation with
> empty DB from the very beginning.
> > But I still need to test performance
> degradation. This is my biggest headache I have to
> say.
>
> Because of the eventual size of the table (and
> other issues), your testing should get a lot
> closer to the eventual size. We are seeing some
> issues now, but there may be bigger issues as it
> grows. The insertion rate going from 5M -> 50M
> scares me the most. (Hence, I have dwelled on
> it.) Having all the activity in a single
> partition that is 1% of the total makes it all
> feasible.
>
> The concern over the binlog may be solved via RBR.
[Miko] I've tested it for the table size of 250GB with two options:
~ 24 partitions 10 GB each (avg 10k inserts per sec)
~ 96 partitions 3GB each (avg 3k inserts per sec)
As I said Ive also developed a piece of SQL procedures that add and delete partitions when particular thresholds are reached (this is the case for 24 partitions 10 GB each when the max occupation has been reached before date retention period of 96 partitions).
And from what you are saying, I presume that even the true working set was big (36M records, 10GB of partition size) it still fitted into the memory (innodb_buffer_pool_size ~ 40GB) and did not result in any significant performance degradation.

Options: ReplyQuote


Subject Views Written By Posted
large InnoDB table partitioning without explicit PK 2489 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1407 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1241 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 987 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 1009 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 892 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 994 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 960 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 1003 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1051 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 944 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1171 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1042 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1071 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1058 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1110 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1202 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1127 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 965 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 934 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 924 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 1013 Miko M 12/01/2014 11:16AM


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.