issues with pt-online-schema-change chunking size and overlaps

[Piavlo]

Hi,

I'm trying to run pt-online-schema-change, the table has primary compund
key of 7 rows,
the alter adds 2 new columns (which will also be part of the new primary
key).
The table is large with over 100M records and this is in AWS instance
running percona server 5.1, so this is heavy operation, especially as
there are serveral
slaves which cannot lag to much, so my aim is to keep the chunks not to
large (the --max-lag --recursion-method dsn will take care of throttling
the chunks)

First I got this expected error:
"Error copying rows at chunk 1 of my.table because MySQL used only 7
bytes of the PRIMARY index instead of 12"

While trying to use different values with --chunk-index-columns i was
getting errors like
"Possible infinite loop detected! The lower boundary for chunk 3 is
<2014-03-01, 2014-03-01, AU, 2014-03-01, AU, 1> and the lower boundary
for chunk 4 is also <2014-03-01, 2014-03-01, AU, 2014-03-01, AU, 1>"

So I decided just to use --chunk-index-columns 1 , since the FIRST
column of the index is DATE i want the chunks to be so that each
chunk will contain all rows for specific DATE , or in other words each
chunk is partitioned by DATE (This creates quite large chunks but i can
leave with it).
Per each DATE there are between 7M and 14M records , and current DATE
has ~4M atm.

While trying: "with --chunk-index-columns 1 --chunk-size 1M
--chunk-size-limit 10" , i got this after first large chunk suceeded:
"The lower boundary for chunk 2 is <2014-03-01> and the lower boundary
for chunk 3 is also <2014-03-01>. "

So I assume need to keep the chunk size between 7M and 14M records
without overlapping - but i'm not even sure it's possible with the chunk
size logic?
I'm thinking about something like "--chunk-index-columns 1 --chunk-size
7M --chunk-size-limit 2" but i'm afaid this will still bail out in the
middle of process
after doing much work and load on the servers for vain, as this still
does not force per DATE chunk boundary.

Is there a way to just tell the chunking algorithms to use the DATE
column as the boundary for chunks?

Thanks
Alex

[14xb2900]

Hi Alex,

How did you solve this problem? We have the same issue now.

[14xb2900]

Hi Alex,

How did you solve this problem? We have the same issue.

Op donderdag 27 maart 2014 11:47:30 UTC+1 schreef Alexander Piavlo:

[Frank Smith]

Hi, can you please show the table structure with a "show create table" ?

 

El jueves, 27 de marzo de 2014, 7:47:30 (UTC-3), Alexander Piavlo escribió:

 Hi,

I'm trying to run pt-online-schema-change, the table has primary compund
key of 7 rows,
the alter adds 2 new columns (which will also be part of the new primary
key).

......