I try to use pt-online-schema-change to add an ordinary index. The existing table is already having one primary unique index and two other ordinary indexes.
The script works but needs >24 hours.
If I drop the two additional indexes before, it takes only 1 hour.
The re-creation of the two indexes takes 40 minutes later on.
So I wonder: Can I make the script to not copy the non primary indexes to the temporary table in the beginning and, instead, create the indexes and the new index (alter table) at the end? This would drastically increase the speed for me.
If you only need to add a new secondary index, then pt-osc is not needed. Adding a secondary index is an online, non-blocking, operation. If you use pt-osc, then you are asking the entire table be rebuilt just to add another index. This is too much! Start a new screen/tmux session, log in to MySQL and ALTER to add the index. It may take a couple hours for this session to finish, but while that is happening, other sessions/transactions will be running just fine. The screen/tmux is there just in case your network drops so you can resume the session.
I’m on MariaDB 5.5 and there the index creation is blocking because there is no “online” operation. The version 5.5 is still the standard in CentOS 7, which we run.
I already tried and the table is fully locked during index creation.
MariaDB 5.5 officially died in April 2020. 10.1 in 2019, 10.2 in 2020, and 10.3 died 2 days ago. 5.5 is NOT the standard for CentOS 7.
5.5 was released in 2012, which means you are running 10+ year old database software full of bugs, performance issues, and security holes.
You should upgrade to MariaDB 10.4 or Percona MySQL 8.0 (to be more in line with the MySQL community) as soon as possible to get on to a database which is modern, current, and is being maintained.
PT-OSC does not create a temporary table. It creates a brand new table which it modifies, then copies all the data from old table to new table, then swaps the tables. There is no option to not copy indexes as that is not how PT-OSC generates the DDLs for creating the new table.
Anyway, so the script is not able to create the indexes and make the alter statement at the end of the copy job. So the indexes are in place during the copy and slow down the copy process by the factor 24 for me.
Maybe the script is not suitable for the old versions of MariaDB. So it will not work for me.
Also, I do not understand why you always mark your answer as “Solution”. My question is not solved at all… And the suggestion for a database update in my production system is not really a solution because I mainly like to prevent downtime. Upgrading the database will cause even more downtime…
Any other ideas on creating that index without downtime?
You’re running a database version that is no longer supported even by the creators of that database. Upgrading will take about 5-10m of downtime per version. yum remove 5.5, then yum install 10.1, start it up, run the upgrade script, then repeat for each major version. You’re wanting features that exist in modern versions of MySQL/MariaDB yet you don’t want to upgrade to get those features.
You might be able to get away with something like this, but this is very much untested:
First, drop all secondary indexes. Then create them all at the same time:
Your final option is to do a replica promotion. Create a replica, if you don’t already have one, then add the index on the replica. However long this takes, it won’t impact production. Upgrade MariaDB while you’re at it. Then, do a swap of replica to primary. Downtime should be around 30s depending on how fast you can type.
pt-osc is absolutely suitable. It does exactly what you need it to do. You are just not happy with how long it takes which has nothing to do with the script itself and has everything to do with how much CPU/Memory/Disk IO you have and how busy MySQL is during the process.
What I gave you is indeed a solution, and is the same solution we would provide even if you were a customer of ours. It’s just not a solution you are a happy with. That doesn’t invalidate it as a solution. I’ve provided two more solutions in this reply.
We will do the upgrade. But for that downtime we scheduled a date shortly after christmas this year. Until then, we need to stay with that.
The pt-osc script executes the alter statements on the new table before copying the data rows. So the inserts will again run on all the indexes, which makes it very slow.
That was my initial question. If I can make it to execute the alter statements after the rows were copied, all would be fast for me. But then, the triggers would have to wait for that table, which is obviously the reason it was done this way. Seems impossible for the moment.
Yes, I understand. We have to discuss that option internally as it involves more than just me to successfully handle it.
Thank you very much for your help and opinion. I mark your last answer as solution then.
And the suggestion for a database update in my production system is not really a solution because I mainly like to prevent downtime. Upgrading the database will cause even more downtime…