[Maria-discuss] Partition a Huge table
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
Hi Suersh I would add a second slave as insurance before doing this to provide some risk mitigation. Make sure you have adequate spare space on your slave as you will want as much again as the table to make changes. make sure you have chosen the right approach to your partitioning before doing anything. We have made a number of changes to our system over the past three years to grow and often made them on the slave and promoted it as the master, applied the schema change on the old master and added it back as the slave. We couldn't afford the two or three hours to lock key tables to add columns and took this approach Cheers Peter On Tue, Mar 14, 2017 at 10:34 AM, Suresh Rajagopal <sureshr7@gmail.com> wrote:
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- *Peter McLarty* *DBA * *WHSR* Compare The Market P: +61 7 3377 8952 M: +61 4 0209 4238 http://www.comparethemarket.com.au “Progress is impossible without change, and those who cannot change their minds cannot change anything.” - George Bernard Shaw -- Compare The Market is a brand and trading name of Compare The Market (Pty) Ltd (CTM). This email is for the intended addressee and is confidential and subject to copyright. If you are not the intended addressee, confidentiality has not been waived and any use, interference with, or disclosure of this email is unauthorised. If you are not the intended addressee please immediately notify CTM and then delete the email. CTM does not warrant that this email is error or virus free.
Thanks Peter. I am moving this database to a new datacenter. So i do have a backup slave. I tried this approach on QA and worked fine. Need to know if there is any known issues before i migrate the prod. From: Peter Mclarty <peter.mclarty@comparethemarket.com.au> To: Suresh Rajagopal <sureshr7@gmail.com> Cc: Maria Discuss <maria-discuss@lists.launchpad.net> Sent: Monday, March 13, 2017 6:07 PM Subject: Re: [Maria-discuss] Partition a Huge table Hi SuershI would add a second slave as insurance before doing this to provide some risk mitigation. Make sure you have adequate spare space on your slave as you will want as much again as the table to make changes. make sure you have chosen the right approach to your partitioning before doing anything. We have made a number of changes to our system over the past three years to grow and often made them on the slave and promoted it as the master, applied the schema change on the old master and added it back as the slave. We couldn't afford the two or three hours to lock key tables to add columns and took this approach CheersPeter On Tue, Mar 14, 2017 at 10:34 AM, Suresh Rajagopal <sureshr7@gmail.com> wrote: 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 -- Peter McLartyDBA WHSRCompare The MarketP: +61 7 3377 8952M: +61 4 0209 4238http://www.comparethemarket.com.au “Progress is impossible without change, and those who cannot change their minds cannot change anything.” - George Bernard Shaw Compare The Market is a brand and trading name of Compare The Market (Pty) Ltd (CTM). This email is for the intended addressee and is confidential and subject to copyright. If you are not the intended addressee, confidentiality has not been waived and any use, interference with, or disclosure of this email is unauthorised. If you are not the intended addressee please immediately notify CTM and then delete the email. CTM does not warrant that this email is error or virus free.
Use pt-online-schema-change. Best regards GL Le mar. 14 mars 2017 à 08:35, Suresh Rajagopal <sureshr7@gmail.com> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@adishatz.net> To: Suresh Rajagopal <sureshr7@gmail.com>; 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 <sureshr7@gmail.com> 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
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=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@gmail.com> 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@adishatz.net> *To:* Suresh Rajagopal <sureshr7@gmail.com>; 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 regards GL
Le mar. 14 mars 2017 à 08:35, Suresh Rajagopal <sureshr7@gmail.com> 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@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
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 <ksubramanian@paycommerce.com> To: Suresh Rajagopal <sureshr7@gmail.com> Cc: Guillaume Lefranc <guillaume@adishatz.net>; Maria Discuss <maria-discuss@lists.launchpad.net> 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 <sureshr7@gmail.com> 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@adishatz.net> To: Suresh Rajagopal <sureshr7@gmail.com>; 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 <sureshr7@gmail.com> 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
participants (4)
-
Guillaume Lefranc -
Karthick Subramanian -
Peter Mclarty -
Suresh Rajagopal