Using 2 changesets to add 2 indexes in the same table using Liquibase Percona Toolkit

MySQL & MariaDB
,
1

Hi Team,

I am using MySql 5.7, with Liquibase and Percona Toolkit .

Problem:
I want to add 2 indexes to a table, in a single ALTER TABLE query, but as per Running Liquibase Percona Toolkit Changes, I cannot use a <sql> liquibase change type with the Percona Toolkit, as it is not supported.

Probable solution, please confirm:
Would adding the indexes in 2 different changesets bring any risks or probable issues? Does doing that causes Percona to create the temporary table twice for my Account table? The table has around 600M records, so it is pretty big.

    <changeSet id="v1">
        <createIndex indexName="ACCOUNT_CREATIONDATE_IDX"  liquibasePercona:usePercona="true"
                     tableName="account">
            <column name="CREATIONDATE"/>
        </createIndex>
    </changeSet>

    <changeSet id="v2">
        <createIndex indexName="ACCOUNT_ACCOUNTID_IDX"  liquibasePercona:usePercona="true"
                     tableName="account">
            <column name="ACCOUNTID"/>
        </createIndex>
    </changeSet>

Thank you very much!

2

Hi ElRubio,

From my tests, even when using the following xml (with only one changeSet) liquibase will do it in two different pt-osc runs:

 <changeSet id="1" author="A">
  <createIndex tableName="t1" indexName="idx1"
   xmlns:liquibasePercona="http://www.liquibase.org/xml/ns/dbchangelog-ext/liquibase-percona"
   liquibasePercona:usePercona="true">
   <column name="col1"/>
  </createIndex>
   <createIndex tableName="t1" indexName="idx2"
   xmlns:liquibasePercona="http://www.liquibase.org/xml/ns/dbchangelog-ext/liquibase-percona"
   liquibasePercona:usePercona="true">
  <column name="col2"/>
  </createIndex>
 </changeSet>
</databaseChangeLog>

I checked in two ways. First, the liquibase update-sql output will show:

--  pt-online-schema-change ... --alter="ADD INDEX idx1 (col1)" --password=*** --execute h=localhost,P=3306,u=root,D=test,t=t1;

--  Instead of the following statements, pt-online-schema-change will be used;

CREATE INDEX idx1 ON test.t1(col1);

--  pt-online-schema-change ... --alter="ADD INDEX idx2 (col2)" --password=*** --execute h=localhost,P=3306,u=root,D=test,t=t1;

--  Instead of the following statements, pt-online-schema-change will be used;

CREATE INDEX idx2 ON test.t1(col2);

And when enabling the MySQL general log and running liquibase update, I saw it indeed used two different ALTER TABLE cycles (one for each pt-osc invocation):

2022-12-29T00:38:07.016117Z    33 Query CREATE TABLE `test`.`_t1_new` (
...
2022-12-29T00:38:07.035309Z    33 Query ALTER TABLE `test`.`_t1_new` ADD INDEX idx1 (col1)
...
2022-12-29T00:38:07.072460Z    33 Query RENAME TABLE `test`.`t1` TO `test`.`_t1_old`, `test`.`_t1_new` TO `test`.`t1`
2022-12-29T00:38:07.086774Z    33 Query DROP TABLE IF EXISTS `test`.`_t1_old`
...
2022-12-29T00:38:07.336200Z    35 Query CREATE TABLE `test`.`_t1_new` (
...
2022-12-29T00:38:07.350686Z    35 Query ALTER TABLE `test`.`_t1_new` ADD INDEX idx2 (col2)
...
2022-12-29T00:38:07.396281Z    35 Query RENAME TABLE `test`.`t1` TO `test`.`_t1_old`, `test`.`_t1_new` TO `test`.`t1`
2022-12-29T00:38:07.413472Z    35 Query DROP TABLE IF EXISTS `test`.`_t1_old`

If it had used pt-osc once, we should have seen:

2022-12-29T00:33:47.715386Z	   19 Query	ALTER TABLE `test`.`_t1_new` ADD index idx1 (col1), add index idx2 (col2)

I suggest that you do your own tests in this case, because I’m not a liquibase expert at all, and may have missed something. Also, asking this in a liquibase-specific forum may also yield good results.

Let me know if I can help with anything else.

Best,
AgustĂ­n.

1 Like
3

Hi Agustin_G,

Thank you very much for the reply!

And in my case, where adding the index can take some time to complete, how are the 2 alterations going to be performed? Let us exclude liquibase from the equation.

If the process for
Query ALTER TABLE test._t1_new ADD INDEX idx1 (col1)
takes 3 hours to complete for example, is the secondary
Query ALTER TABLE test._t1_new ADD INDEX idx2 (col2)
going to be ran in parallel?

Is the secondary query going to fail, or generate inconsistencies, if the first one is still running?

1 Like
4

Hi ElRubio,
You mentioned:

and

But this will depend solely on how Liquibase is designed to work, and how it will run the different pt-osc commands. It shouldn’t run pt-osc in parallel for the same table… In my case it didn’t, but the ALTER table was almost instant because the dataset was trivial to handle, so I’m not 100% sure if it really doesn’t or if it didn’t have time :slight_smile:
Again, my best advice here is that you should get a Dev/QA/Staging environment and make sure you properly test this yourself, with data as close to production as you can.
You can monitor what Liquibase is doing by using the general log, SHOW PROCESSLIST and most likely some Liquibase logs itself too.
You may also check Liquibase’s documentation and see if it’s possible to have multiple changes added to one pt-osc run. I skimmed through it, and couldn’t find any references to it. Consulting in their forum is also a good option in this regard.

Best,
AgustĂ­n.

1 Like