0

I've got an InnoDB table over 50GB with indexes. The query I'm running looks like this:

pt-online-schema-change h=localhost,D=db,t=table --critical-load="Threads_running=100" --check-slave-lag "h=XX.XX.XX.XX" --max-lag 10
--execute --pass PASS 
--alter "REORGANIZE PARTITION max INTO (
PARTITION p19 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-05-01 00:00:00') ),
PARTITION p20 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-06-01 00:00:00') ),
PARTITION max VALUES LESS THAN maxvalue
)"

No matter what settings I use, I get the Threads_running issue. I have also tried:

--chunk-size 100 (also tried 20)

--max-load="Threads_running=20"

Shouldn't it just wait until threads running gets lowered? Why does it keep quitting? I could raise the critical load to allow 200 threads, but I'm afraid of the server crashing.

Any advice?

  • What is the standard load on your server when the tool is not running? – jkavalik Jun 10 '16 at 20:33
  • In the future, be sure to create the 'next' partition before anything is written to PARTITION max. That way the REORGANIZE will be very fast. – Rick James Jun 26 '16 at 5:19
  • Cut back on (1) max_connections and (2) the max number of threads allowed in the clients (such as MaxClients in Apache). – Rick James Jun 26 '16 at 5:20
0

The problem was that every now and then an unrelated query put a lot of stress on the server and was causing the threads running to nearly max out. It was doing this regularly before the schema change was first run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.