Mysql – Is it safe to run optimze on mutliple tables in parallel in thesql (percona)

MySQLoptimizationperconatable

I have a slave on which no query is coming. Lot of data has been archived and deleted from almost all tables. Size of tables vary from 80Gb to few Mbs. And there are around 130 such tables.

Mysql (Percona) version is "Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1"

I am running optimize on them, one at a time, but since optimization is a time consuming process, would it be Okay if I run optimize on multiple tables (say 3 to 4) at a time? I have enough disk space for 3 to 4 tables to be copied in the same partition.

I am assuming optimize on one table simple locks that table alone and has no effect at all on the other tables, hence it would be perfectly safe. But any caveats and former experience is dealing with this is welcomed.

Best Answer

As KJavalik pointed out in the Comments, it it safe, but Disk IO will increase and may choke other operations. So in case the Server is not serving any traffic/queries, you may try, else be careful.

Also keep an eye on the free disk space as optimize command will copy the remaining data of the table being optimized to a new table, and if the slave is in replication, new data will also take up some space.

So in case you are optimizing 2 tables of 50Gb each, for example, and prior experience suggests that post optimization each table will be around 35 Gb, then its good to have at least 90Gb of free space (35 Gb + 35 Gb + 20 Gb of extra free space for other tables to grow in case the database is in replication)

Related Solutions

Mysql – Percona vs MySQL

Percona Server is a fork of MySQL that came into being about 7 years ago. The Percona employees are mostly former MySQL people. They care about InnoDB performance so much that they inject 30,000 lines of code into its version of MySQL to make those enhancements possible. They have been doing this since MySQL 5.0 and are still at it today.

MySQL (eh Oracle [still hate saying it]) has been trying to keep up with it Percona via its InnoDB plugin (since MySQL 5.1.38, now fully native in MySQL 5.5)

Percona once did an aggressive comparison of MySQL versions. In most cases LEFT UNCONFIGURED, older versions of MySQL outperform newer versions. You must tune MySQL to get the most of it. Switching to Percona may not get enhancements you are looking for. Even with Percona, you must find out what performance enhancements come out-of-the-box and what is settings you need to configure otherwise.

Mysql – “Site Offline” MySQL server failing to start and stop

OMG !!! Look at the output of df -h

Size: 20G Used: 19G Available 0 Use Percent: 100%

YOU HAVE NO DISK SPACE !!!

Get more diskspace freed up or have linode increase diskspace.

According to MySQL 5.0 Certification Study Guide

enter image description here Page 408,409 Section 29.2 Bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

In reality, mysqld is not dead. It is waiting for free diskspace to continue whatever it normally does. Even during startup, if mysqld makes a temp table, it is a MyISAM table. No space left on the disk? No problem. MyISAM just patiently waits for you to make free space.

If you have an error file like /var/log/mysql/log, clear its contents like this:

echo -n > /var/log/mysqld.log

If the error file in /var/lib/mysql/my-machine.err then do this:

echo -n > /var/lib/mysql/my-machine.err

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Percona vs MySQL

Mysql – “Site Offline” MySQL server failing to start and stop

Related Question