Tag Archives: Percona Toolkit

Alter mysql tables on the fly, without locking them

Publicat la 22 January 2019 No comments

If you need to perform real-time ALTER TABLE processes on MySQL (InnoDB, TokuDB) tables, a great tool for the job is the Percona Toolkit.

The Percona Toolkit includes a utility (pt-online-schema-change) to perform such a process, without write-locking tables, or having to manually create a temporary table and triggers to synchronize the data during the process.

[codesyntax lang="bash"]

time pt-online-schema-change --host my-awesome-database.example.net --user=user --password=secret --execute --print --no-drop-old-table --alter "DROP INDEX kenny" D=my-database,t=my-table

[/codesyntax]

[codesyntax lang="bash"]

time pt-online-schema-change --hostmy-awesome-database.example.net --user=user --password=secret --execute --print --no-drop-old-table --alter "add source_id int(10) unsigned DEFAULT NULL"D=my-database,t=my-table

[/codesyntax]

Kill all mysql queries having query time greater than 1 minute

Publicat la 12 January 2016 No comments

At this point there are two approaches to achieve this. One is using pt-kill from Percona Toolkit, and the other one is to use a bash script with a lot of pipes :)
Why would someone use the second approach? I don't know, perhaps because there is no Percona Toolkit available.

[codesyntax lang="bash"]

for i in $(mysql -e "show processlist" | egrep -v "system user" | grep '^[0-9]' | awk '{print $6" "$1}' | sort -nr -k1 | awk -v threshold="60" '$1 > threshold' | awk '{print $2}'); do mysql -e "kill $i"; done

[/codesyntax]

If you can/want to use pt-kill and don't know how, please read this.