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 2455 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1388 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1219 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 972 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 992 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 875 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 976 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 942 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 989 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1033 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 926 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1151 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1029 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1055 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1042 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1092 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1176 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1112 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 952 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 918 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 911 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 999 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.