1

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.

Improve this question
15
  • 1
    What is the bottleneck? Optimize table is usually not very CPU intensive, so disk IO seems probable limiting factor. Might 4 optimizes utilize disk better than one or will it lead to degradation? (I do not have the answer myself, may depend on lot of things - used RAID, SSD etc)
    – jkavalik
    Commented Sep 2, 2015 at 8:09
  • @jkavalik Disk is a Flash SSD Drive. Need to have it since our DBs are huge in size and server huge traffic. So Disk IO will not be an issue for sure. Bottleneck is lack of my knowledge and experience :)
    – Gautam Somani
    Commented Sep 2, 2015 at 8:36
  • @jkavalik Also since the DB is not answering any queries as of now, so we are Okay to get the maximum and complete utilization of the SSD disk.
    – Gautam Somani
    Commented Sep 2, 2015 at 8:38
  • Well, it takes time so it is doing something, either computation or IO, and there is not much to compute on optimize table so thats why I guess IO will be quite saturated, but yes, second optimize might utilize the disk even in those cases when the first one has to actually compute something. And it seems to be used as there is a tool/tutorial for it from a reliable source xaprb.com/blog/2007/10/03/… - I would run 2 or 3 top but can't back it up with any real stats.
    – jkavalik
    Commented Sep 2, 2015 at 8:43
  • @jkavalik Okay. But then why do I need a tool. I know the free disk space, the size of the table currently being optimized and the table which I can easily optimize in the currently available disk space. And I have scripts in place which will alert me via sms of any impending disk space issue. I can simply run another optimize table command manually right? Or script it to check for disk usage and free disk space and do or wait accordingly?
    – Gautam Somani
    Commented Sep 2, 2015 at 8:48

3 Answers 3

Reset to default
1

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)

Improve this answer
0

You have an excellent case for PARTITION BY RANGE(TO_DAYS(date)) and have weekly (or monthly) partitions.

Every week (or month) archive off the oldest partition, then instantly DROP PARTITION. That is much faster than DELETE. Since Partitions are separate entities, you immediately get all the disk space from the dropped partition back (assuming innodb_file_per_table). Meanwhile, the rest of the data is unaffected by the DROP.

Your inserts, I assume, will be only into the 'latest' partition. This may help some in its cacheability.

More on time-series partitioning, including reference code for the partition maintenance. The actions take essentially zero downtime, unlike OPTIMIZE.

To move to Partitioning, there are normally no changes to SELECTs or INSERTs. The INDEXes on the table need some rethink. Provide SHOW CREATE TABLE and the main SELECTs for further advice. (Suggest starting a new thread.)

(A side note... OPTIMIZE PARTITION mistakenly rebuilds the entire table; this can be worked around by using ALTER TABLE .. REORGANIZE PARTITION of the partition into itself.)

Improve this answer
2
  • We do have them too, on daily basis, but only for the tables which grow at an excess rate per hour. We keep last some partitions and then archive them and drop them (that is automated). But for the other ones, we prefer to keep them that way. Else the code will get too complex.
    – Gautam Somani
    Commented Sep 4, 2015 at 7:02
  • If you are modifying only the 'latest' partition, then you should only need one REORG per partition. Nothing will un-optimize the 'old' partitions.
    – Rick James
    Commented Sep 4, 2015 at 16:13
0

If you can, I would stop the slave to prevent any locking on tables you'll try to optimize. (table being optimized will be locked). When using Optimize Table, secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. I've used Alter table engine =same engine ; It will force Mysql to recreate the table. (but might have same issue with indexes)

If you're trying to optimize all the indexes (secondary too) on your tables, i would use a different approach.

Improve this answer
1
  • "empty" alter table is what optimize actually does - for innodb at least. And Percona implemented extended fast index creation in 5.5.16 - but there are some exceptions (non-unique, no foreign keys - that is a bit unfortunate one)
    – jkavalik
    Commented Sep 4, 2015 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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