Pt-online-schema-change Running simultaneously for multiple databases

Polyglot Projects Percona Toolkit
,
1

Imagine this scenario:

  • Need to do an ALTER to add in index i.e: ADD INDEX index_name (column_name)
  • Within a host, there are 30 databases with same schema
  • Need to run pt-online-schema-change for each database
  • I created a Python script that using threading, run 30, same command in same time but pointing to a different database.

Question

Can pt-online-schema-change run with no problem under such scenario?

More info

I did test it but for now on my local setup. Most of queries will work however, for some sporadic databases I get the following error:

Cannot connect to MySQL: DBI connect('database_name;host=localhost;mysql_read_default_group=client','root',...)
 failed: Too many connections at /usr/bin/pt-online-schema-change line 2345.
18 at /usr/bin/pt-online-schema-change line 8758.

While this is a strong indication that pt-online-schema-change is not designed to be used this way, could also be that is due to my local setup.
So I’d like to check opinions on this.
Maybe there is a command that ‘enables’ this?

2

You don’t have enough max-connections allowed. Increase this variable in your config.

More importantly, you don’t need pt-osc for simply adding an index. Adding indexes is an online operation. When you ALTER TABLE .. ADD INDEX(col1), writes and reads are still allowed in other connections. Adding indexes does not block/lock anything other than your session which executed the SQL.

3

Hi, ok so is the max-connections of MySQL?
Anyways, really?

I think is something from latest MySQL correct?indexing - Create an index on a huge MySQL production table without table locking - Stack Overflow
Meaning from MySQL 5.6 adding an index wont lock read and write and therefore, we don’t need this percona tool?

Did I understand this correctly?

Also, Do i need to add something specific to an alter statement ? (I know this is related to MySQL but if happen that you know I’d be glad otherwise I ll look it up!)

4

MySQL 5.6 is not “latest” in any way; it’s from 2013. This functionality has been around 10+ years. MySQL 8 expanded this more with INSTANT ALTER TABLE.

No. Just ALTER TABLE foo ADD INDEX (col1, col2) and it will process online. Just a reminder, your session executing the ALTER will be blocked, but other sessions will still be able to read/write to the table.

5

Ok thanks for the information, very helpful!.
For clarification I know latest version is not 5.6 :smiley: