Why is Percona pt-online-schema-change performing so badly?
<p>We've been using <a href="https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html" rel="nofollow">Percona OSC</a> for a while now to make changes to our mysql schema without locking the tables and it has worked great, typically adding a new column or index to "large" innodb tables (~3.8 million rows) within a couple of hours.</p>

<p>However, the last update I tried was only 40% complete after running for 7 hours (overnight, during our quietest period), with an estimate of a further 11 hours to complete (which keeps increasing). All bar 4GB of the available memory on the RedHat server was being used - 32GB, which we've recently upgraded from 16GB.</p>

<p>So what's going on here? Why could the time taken suddenly have jumped so high? Have we just reached some kind of threshold that percona / mysql / the server can't cope with? Are there any configs we can tweak to improve performance?</p>

<p>The table has 32 columns and 12 indexes (including the primary key and 2 other unique indexes). I know that's a lot, but as I say until recently it performed just fine.</p>

<p>The table also has several foreign keys pointing to it which we set to update using the drop_swap method.</p>

<p>The full command I used was:</p>

<pre><code>pt-online-schema-change --execute --ask-pass --set-vars innodb_lock_wait_timeout=50 --alter-foreign-keys-method=drop_swap
--alter "ADD is_current TINYINT(1) DEFAULT '1' NOT NULL" u=admin,p=XXXXXXX,D=xxxxx_live,t=applicant
</code></pre>

<p>The innodb_buffer_pool_size is currently set to 2147483648 - should this be increased? If so, by how much? The web server (apache/php/symfony) is also running on this box.</p>

<p>The last change I made on this particular table was to change the collation of 1 field to utf8_bin (the other fields are utf8_unicode_ci) - could that make a difference?</p>