Hi Karthick,
I am fine with slave lag as i have to move the database to a new HA pair. Slave
was able to catchup with binlogs once table migration completed. My major
concern is with data safety. Also its hard to split data as hot/cold.
ThanksSuresh
From: Karthick Subramanian <[email protected]>
To: Suresh Rajagopal <[email protected]>
Cc: Guillaume Lefranc <[email protected]>; Maria Discuss
<[email protected]>
Sent: Tuesday, March 14, 2017 8:45 AM
Subject: Re: [Maria-discuss] Partition a Huge table
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=1ALTER 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 <[email protected]> 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 <[email protected]>
To: Suresh Rajagopal <[email protected]>; Maria Discuss <maria-discuss@lists.
launchpad.net>
Sent: Monday, March 13, 2017 6:08 PM
Subject: Re: [Maria-discuss] Partition a Huge table
Use pt-online-schema-change.
Best regardsGL
Le mar. 14 mars 2017 à 08:35, Suresh Rajagopal <[email protected]> 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.18OS : Centos 6
ThanksSuresh______________________________ _________________
Mailing list: https://launchpad.net/~maria- discuss
Post to : [email protected]. net
Unsubscribe : https://launchpad.net/~maria- discuss
More help : https://help.launchpad.net/ ListHelp
______________________________ _________________
Mailing list: https://launchpad.net/~maria- discuss
Post to : [email protected]. net
Unsubscribe : https://launchpad.net/~maria- discuss
More help : https://help.launchpad.net/ ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
