2

we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this do able, if not what is best way to partition this large table

2 Answers 2

1

First, let's discuss what benefit you will get from partitioning. Please provide SHOW CREATE TABLE (with the partitioning). Partitioning, in itself, does not provide any performance benefits. Let's see the query/queries that you think will benefit.

pt-online-schema-change is likely to be a way to convert to partitioning with virtually no downtime. But check carefully; there may be restrictions on partitioning.

The manual says nothing about whether you can have replication between a non-partitioned on the master and a partitioned table on the slave. The silence of the manual worries me; there could be issues.

3
  • I understand we can partition on slave and not on master and replication still works and manual also do not say anything about it, I was worried would this also apply to AWS RDS Mysql too ? Jun 14, 2015 at 10:02
  • 2
    I know of no reason for RDS to cause additional limitations. Create a separate database, put a small non-partitioned table Master. Then ALTER it on a Slave. Perform some operations and see what happens.
    – Rick James
    Jun 14, 2015 at 16:50
  • @rogermoore - I suspect that you must use binlog_format=STATEMENT when the Primary's and Replica's tables differ in partitioning. Meanwhile, ROW is becoming the standard. Test on a separate pair of servers (or Docker instances). Suggest you start a Question with just this issue (replicating non-PARTITION to PARTITION).
    – Rick James
    Jun 27, 2022 at 16:47
0

yes you can.

Can run the partitions on replica [with read_only disable on param group, no need of restart cos its dynamic]. Once the partition and replica in sync, you can promote it as master [it will promote as a new master. M1->s1 == after promote it will be like M1 , S1. ]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.