Adding an index to a large table
Hi, I have a 200GB table with a foreign key constraint, and I need to drop an existing index and add a non-unique index to it. What is the best approach to do this with minimal impact on users? Thanks Edi
ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE; Unless you have downstream slaves that are lag sensitive. They you'll probably have to pt-online-schema-change with drop-swap FK handling, but that is not entirely risk-free (you might get FK violations during the drop-swap). On Sat, 22 Mar 2025, 19:28 edi mari via discuss, <discuss@lists.mariadb.org> wrote:
Hi, I have a 200GB table with a foreign key constraint, and I need to drop an existing index and add a non-unique index to it.
What is the best approach to do this with minimal impact on users?
Thanks Edi _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Thanks Gordan. This is a cluster and I have slaves. How does the ALGORITHM=INPLACE, LOCK=NONE; configuration impact the slaves ? On Sat, Mar 22, 2025 at 7:41 PM Gordan Bobic <gordan@shatteredsilicon.net> wrote:
ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE;
Unless you have downstream slaves that are lag sensitive. They you'll probably have to pt-online-schema-change with drop-swap FK handling, but that is not entirely risk-free (you might get FK violations during the drop-swap).
On Sat, 22 Mar 2025, 19:28 edi mari via discuss, < discuss@lists.mariadb.org> wrote:
Hi, I have a 200GB table with a foreign key constraint, and I need to drop an existing index and add a non-unique index to it.
What is the best approach to do this with minimal impact on users?
Thanks Edi _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
On Sat, 22 Mar 2025, 20:02 edi mari, <edim2525@gmail.com> wrote:
Thanks Gordan. This is a cluster and I have slaves. How does the ALGORITHM=INPLACE, LOCK=NONE; configuration impact the slaves ?
The slaves will start the ALTER when it gets in the binlog - which will happen when ALTER completes. So if it takes an hour for that ALTER to complete, your slaves will get a 1 hour lag spike that won't start to clear until they finish executing the ALTER they replicated down.
Thanks for the explanation. Edi On Sat, Mar 22, 2025 at 8:40 PM Gordan Bobic <gordan@shatteredsilicon.net> wrote:
On Sat, 22 Mar 2025, 20:02 edi mari, <edim2525@gmail.com> wrote:
Thanks Gordan. This is a cluster and I have slaves. How does the ALGORITHM=INPLACE, LOCK=NONE; configuration impact the slaves ?
The slaves will start the ALTER when it gets in the binlog - which will happen when ALTER completes. So if it takes an hour for that ALTER to complete, your slaves will get a 1 hour lag spike that won't start to clear until they finish executing the ALTER they replicated down.
Hi Gordan, Do you see any reason why we shouldn't first run the CREATE INDEX statement on the slaves and then run the same command on the master, while ensuring it's not written to the binary log? In this order, we will eliminate the slave lag. For example, on the master, we could disable binary logging temporarily: -- Disable binary logging for the current session SET sql_log_bin = 0; -- Run the CREATE INDEX command CREATE INDEX ix_index ON t1 (col1, col2) ALGORITHM=INPLACE, LOCK=NONE; -- Re-enable binary logging SET sql_log_bin = 1; Edi On Sat, Mar 22, 2025 at 9:23 PM edi mari <edim2525@gmail.com> wrote:
Thanks for the explanation.
Edi
On Sat, Mar 22, 2025 at 8:40 PM Gordan Bobic <gordan@shatteredsilicon.net> wrote:
On Sat, 22 Mar 2025, 20:02 edi mari, <edim2525@gmail.com> wrote:
Thanks Gordan. This is a cluster and I have slaves. How does the ALGORITHM=INPLACE, LOCK=NONE; configuration impact the slaves ?
The slaves will start the ALTER when it gets in the binlog - which will happen when ALTER completes. So if it takes an hour for that ALTER to complete, your slaves will get a 1 hour lag spike that won't start to clear until they finish executing the ALTER they replicated down.
On Mon, 24 Mar 2025 at 16:40, edi mari <edim2525@gmail.com> wrote:
Hi Gordan,
Do you see any reason why we shouldn't first run the CREATE INDEX statement on the slaves and then run the same command on the master, while ensuring it's not written to the binary log?
In this order, we will eliminate the slave lag.
For example, on the master, we could disable binary logging temporarily:
-- Disable binary logging for the current session SET sql_log_bin = 0; -- Run the CREATE INDEX command CREATE INDEX ix_index ON t1 (col1, col2) ALGORITHM=INPLACE, LOCK=NONE; -- Re-enable binary logging SET sql_log_bin = 1;
You can, but it taints GTID consistency across the server, if you care about that kind of thing. -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Gordan Bobic via discuss <discuss@lists.mariadb.org> writes:
On Mon, 24 Mar 2025 at 16:40, edi mari <edim2525@gmail.com> wrote:
For example, on the master, we could disable binary logging temporarily:
-- Disable binary logging for the current session SET sql_log_bin = 0; -- Run the CREATE INDEX command CREATE INDEX ix_index ON t1 (col1, col2) ALGORITHM=INPLACE, LOCK=NONE; -- Re-enable binary logging SET sql_log_bin = 1;
You can, but it taints GTID consistency across the server, if you care about that kind of thing.
Gordon, I'm curious, what do you mean here by "taints GTID consistency"? Doesn't this way exactly preserve GTID consistency, since no new GTIDs will be created on any server when sql_log_bin is set to 0? - Kristian.
On Mon, 24 Mar 2025 at 18:15, Kristian Nielsen <knielsen@knielsen-hq.org> wrote:
Gordan Bobic via discuss <discuss@lists.mariadb.org> writes:
On Mon, 24 Mar 2025 at 16:40, edi mari <edim2525@gmail.com> wrote:
For example, on the master, we could disable binary logging temporarily:
-- Disable binary logging for the current session SET sql_log_bin = 0; -- Run the CREATE INDEX command CREATE INDEX ix_index ON t1 (col1, col2) ALGORITHM=INPLACE, LOCK=NONE; -- Re-enable binary logging SET sql_log_bin = 1;
You can, but it taints GTID consistency across the server, if you care about that kind of thing.
Gordon, I'm curious, what do you mean here by "taints GTID consistency"?
Doesn't this way exactly preserve GTID consistency, since no new GTIDs will be created on any server when sql_log_bin is set to 0?
The transaction is generated locally on each node, so in theory, it would be generated with different server ids. But I guess it doesn't matter if the transaction isn't binlogged so the point is moot unless you are using Galera. -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Gordan Bobic <gordan@shatteredsilicon.net> writes:
The transaction is generated locally on each node, so in theory, it would be generated with different server ids. But I guess it doesn't matter if the transaction isn't binlogged so the point is moot unless you are using Galera.
Ah, Galera, I see, thanks for the explanation. Yes, in MariaDB replication, GTIDs are generated as part of binlogging, so no GTIDs when sql_log_bin=0. I guess Galera is different. - Kristian.
Hi, Gordan, MariaDB has a solution for that since 10.8.1, see https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#binlo... https://mariadb.com/kb/en/alter-table/#replication On Mar 22, Gordan Bobic via discuss wrote:
On Sat, 22 Mar 2025, 20:02 edi mari, <edim2525@gmail.com> wrote:
Thanks Gordan. This is a cluster and I have slaves. How does the ALGORITHM=INPLACE, LOCK=NONE; configuration impact the slaves ?
The slaves will start the ALTER when it gets in the binlog - which will happen when ALTER completes. So if it takes an hour for that ALTER to complete, your slaves will get a 1 hour lag spike that won't start to clear until they finish executing the ALTER they replicated down.
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Interesting, I've never used pt-online-schema-change with a worry for downstream slaves, it was that there were major tables that drove applications, used my millions a month. I no longer live in that world, I never had an issue during the drop/swap. And it was a goto for many years. On Sat, Mar 22, 2025 at 1:41 PM Gordan Bobic via discuss < discuss@lists.mariadb.org> wrote:
ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE;
Unless you have downstream slaves that are lag sensitive. They you'll probably have to pt-online-schema-change with drop-swap FK handling, but that is not entirely risk-free (you might get FK violations during the drop-swap).
On Sat, 22 Mar 2025, 19:28 edi mari via discuss, < discuss@lists.mariadb.org> wrote:
Hi, I have a 200GB table with a foreign key constraint, and I need to drop an existing index and add a non-unique index to it.
What is the best approach to do this with minimal impact on users?
Thanks Edi _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Thanks, Gordan and Kristian. Since we’re not using Galera, I’ll proceed with creating the index on slaves first, then on the master with `sql_log_bin = 0;`. Appreciate the guidance! https://gtasandreasapk.com/
participants (6)
-
edi mari -
Gordan Bobic -
gtasandreasapk＠gmail.com -
Jeff Dyke -
Kristian Nielsen -
Sergei Golubchik