Re: large InnoDB table partitioning without explicit PK

[Miko M]

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.