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
To: Suresh Rajagopal
Cc: Guillaume Lefranc ; Maria Discuss
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-paralle...
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 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
To: Suresh Rajagopal ; Maria Discuss
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 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 : maria-discuss@lists.launchpad. net
Unsubscribe : https://launchpad.net/~maria- discuss
More help : https://help.launchpad.net/ ListHelp
______________________________ _________________
Mailing list: https://launchpad.net/~maria- discuss
Post to : maria-discuss@lists.launchpad. net
Unsubscribe : https://launchpad.net/~maria- discuss
More help : https://help.launchpad.net/ ListHelp
