We used Percona OSC for a while to make changes to our mysql schema without locking tables, and it did a great job, usually adding a new column or index to innodb "large" tables (~ 3.8 million rows) for several hours.
However, the last update I tried was only 40% full after working for 7 hours (overnight, during our quietest period), with an estimate of another 11 hours (which continues to grow). All 4 GB of available memory on the RedHat server was used - 32 GB, which we recently updated from 16 GB.
So what is going on here? Why is the time suddenly soaring so high? Have we just reached some threshold that the percona / mysql / server cannot handle? Are there any configs that we can configure to improve performance?
The table contains 32 columns and 12 indexes (including the primary key and 2 other unique indexes). I know this a lot, but, as I say, until recently, it performed just fine.
The table also has several foreign keys that point to them, which we set to update using the drop_swap method.
I used the full command:
pt-online-schema-change
Innodb_buffer_pool_size is currently set to 2147483648 - should it be increased? If so, how much? The web server (apache / php / symfony) also works in this field.
, , , 1 utf8_bin ( - utf8_unicode_ci) - ?