maria-discuss team mailing list archive

[Karthick Subramanian <ksubramanian@xxxxxxxxxxxxxxx>]

Hi Suresh,


   - If the DML activities are not on the historical data (hot dataset and
   cold dataset), then shall we create a empty partitioned table and start
   populating the historical datasets into this new partitioned table using
   LOAD DATA INFILE method. {*Preferably we can do this activity in Slave
   DB. Even we can do this activity in master if we can exclude this new db
   from replication (created for partition purposes**)*}. So this will go
   in-parallel until we reach the hot dataset, that we can do one shot during
   any planned downtime. I believe this won't take much time as compared to
   your initial estimation of 24+ hours or so because we are doing the phase
   by phase data migration.


Can you also check this out:
https://mariadb.com/kb/en/mariadb/parallel-replication/#out-of-order-parallel-replication

You can consider running the partition creation in a separate replication
domain id so that this long running operation won't hinder the other
replication activities.

Ex:

SET SESSION gtid_domain_id=1 ALTER TABLE t ADD PARITION... SET SESSION
gtid_domain_id=0

"Normally, a long-running ALTER TABLE or other query will stall all
> following transactions, causing the slave to become behind the master as
> least as long time as it takes to run the long-running query. By using
> out-of-order parallel replication by setting the replication domain id,
> this can be avoided. The DBA/application must ensure that no conflicting
> transactions will be replicated while the ALTER TABLE runs."


Also I would like to understand your below statement:

> How safe its to alter table on slave and promote slave as master?

How do you take care of the data changes that's happening in this table in
master while you undergo these schema changes in slave (that you mentioned
will take 24 hours or so).

On Tue, Mar 14, 2017 at 10:38 AM, Suresh Rajagopal <sureshr7@xxxxxxxxx>
wrote:

> i have to modify primary for partitioning. pt-online-schema-change
> complaints about dropping primary.Still i can ignore the warning and
> proceed.
>
> Thanks
>
>
> ------------------------------
> *From:* Guillaume Lefranc <guillaume@xxxxxxxxxxxx>
> *To:* Suresh Rajagopal <sureshr7@xxxxxxxxx>; Maria Discuss <
> maria-discuss@xxxxxxxxxxxxxxxxxxx>
> *Sent:* Monday, March 13, 2017 6:08 PM
> *Subject:* Re: [Maria-discuss] Partition a Huge table
>
> Use pt-online-schema-change.
>
> Best regards
> GL
>
> Le mar. 14 mars 2017 à 08:35, Suresh Rajagopal <sureshr7@xxxxxxxxx> a
> écrit :
>
> Hi,
>
> What is the fastest approach to partition a huge table with 2 additional
> columns? How safe its to alter table on slave and promote slave as master?
> It takes 25 hours to alter the table for now on slave.
>
> MariaDB Version : 10.1.18
> OS              : Centos 6
>
> Thanks
> Suresh
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>