Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The machine has a 4-core hyper-threaded Intel i7 670 @ 2.8GHz, 16G RAM, 8G of which is InnoDB buffer pool. The database resides on Intel SSD disk. DBMS is MariaDB 5.5.30 x64 running under Windows Server 2008 R2 x64. The problem is that ALTER TABLE still runs too damn slow, i.e. converting a 100M InnoDB table to COMPRESSED row format takes about two minutes. During the operation the CPU isn't even running at full speed, showing 10-20% load. More than a half of InnoDB buffer pool is free. SSD disk active time is roughly 5% and lower. So what could be a bottleneck this time? InnoDB setup is available here.

share
    
What is your target performance for this operation? Why? Is the MySQL server otherwise idle? –  Ollie Jones Jun 6 '13 at 13:42
    
A single query is not parallelizable, so 25% CPU usage would be "maxing out" here. Or actually even 12.5%, since you mentioned HT –  Vladislav Vaintroub Jun 6 '13 at 21:33
    
Please post the original table layout and the ALTER TABLE you ran. –  RolandoMySQLDBA Jun 6 '13 at 23:56
    
Thank you for all the responses, but the reason was just plain stupid - someone have set up "Power Saver" power plan... –  CamaroSS Jun 16 '13 at 7:00
comments disabled on deleted / locked posts

migration rejected from stackoverflow.com Jun 16 '13 at 12:00

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as too localized by Aaron Bertrand Jun 16 '13 at 12:00

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

You can refer pt-online-schema-change from Percona toolkit. Essentially what it does is:

  • It works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.

  • It creates an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.

share
add comment

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