Re: [Maria-discuss] Partition a Huge table

2017-03-14 Thread Suresh Rajagopal 
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-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  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





   ___
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

Re: [Maria-discuss] Partition a Huge table

2017-03-14 Thread Karthick Subramanian 
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 
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 <
> 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  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
>
>
___
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

[Maria-discuss] MariaDB 10.1.22 now available

2017-03-14 Thread Daniel Bartholomew 
The MariaDB project is pleased to announce the immediate availability
of MariaDB 10.1.22. This is a stable (GA) release. See the Release
Notes and Changelogs for details.


- - Links  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

MariaDB 10.1.22
 - Release Notes: https://mariadb.com/kb/en/mdb-10122-rn/
 - Changelog: https://mariadb.com/kb/en/mdb-10122-cl/
 - Downloads: https://downloads.mariadb.org/mariadb/10.1.22

 About MariaDB 10.1:
  - https://mariadb.com/kb/en/what-is-mariadb-101/

 APT and YUM Repository Configuration Generator:
  - https://downloads.mariadb.org/mariadb/repositories/



- - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - -

There are several upcoming MariaDB-focused webinars and many
previously held ones available to watch on an on-demand basis at:

 - https://mariadb.com/news-events/webinars


- - MariaDB Books  - - - - - - - - - - - - - - - - - - - - - - - - - -

There is an ever-growing library of MariaDB books available to help
you get the most out of MariaDB. See the MariaDB Books page for
details and links:

 - https://mariadb.com/kb/en/mariadb/books/


- - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - -

MariaDB includes a User Feedback plugin. This plugin is disabled by
default. If enabled, it submits basic, completely anonymous MariaDB
usage information. This information is used by the developers to
track trends in MariaDB usage to better guide development efforts.

If you would like to help make MariaDB better, please add
"feedback=ON" to your my.cnf or my.ini file!

See http://mariadb.com/kb/en/user-feedback-plugin for more
information.


- - Quality  - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

The project always strives for quality, but in reality, nothing is
perfect. Please take time to report any issues you encounter at:

 - http://mariadb.org/jira


- - Support MariaDB  - - - - - - - - - - - - - - - - - - - - - - - - -

If you would like to contribute to the MariaDB Foundation, please see
the "contributing" and "donations" pages. We also have merchandise
available in a cafepress store. All proceeds go to support the
MariaDB Foundation.

 - https://mariadb.com/kb/en/contributing

 - https://mariadb.org/donate/


We hope you enjoy MariaDB!

--
MariaDB
Website - http://mariadb.org
Twitter - http://twitter.com/mariadbfdn
- http://twitter.com/mariadb
Google+ - http://google.com/+mariadb
   Facebook - http://fb.com/MariaDB.dbms
 Knowledge Base - http://mariadb.com/kb

___
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