Skip to content

Tag Archives: long time

MySQL – Innodb Slow Shutdown – Dirty Buffer Pages

Friday, September 3, 2010

If you have a MySQL server that reserves a large amount of memory for the Innodb buffer pool, you may have seen that it can often take a very long time to shutdown.  During the shutdown process, the server will not be able to serve any requests.  From my experience, I have seen that it can take 1-5 minutes per GB of buffer to perform a shutdown.  So, for example, if you have a server with 50G of memory allocated to the Innodb Buffer Pool, and 50% of the buffer is dirty (unwritten to disk), it can take anywhere from 25-125 minutes to shutdown.  Multiple hour shutdowns on large, busy Innodb MySQL servers is not unheard of.

Luckily, there is a fairly easy way to speed up shutdown while still allowing the server to stay up and handle requests.  The secret is to lower the maximum percentage of dirty pages allowed.  This is controlled by the global variable innodb_max_dirty_pages_pct. On my servers I usually have this set at 75%. This variable can be changed at run-time, which allows us to modify this variable to speed up system shutdown.

First, enter the command :

set global innodb_max_dirty_pages_pct=0;

You can then enter the following to verify the new value:

show global variables where variable_name rlike ‘dirty’;

To monitor the number of dirty pages that have not been written to disk, enter the following:

show global status where variable_name rlike ‘dirty’;

You will see this number begin to drop, although it is unlikely to ever reach 0, once it drops to a low level and reaches a plateau, you can then shutdown the server. You will find that the server will shutdown very quickly, allowing you to minimize downtime.

Filed in Innodb, MySQL, Optimization | Also tagged , , , , , , , , , , , , , | Comments (0)

How to increase MySQL performance when loading a Dump File

Thursday, August 19, 2010

In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.

Filed in MySQL, Restore Backup | Also tagged , , , , , , , , , , | Comments (0)

MySQL Removing duplicate rows – Part II

Tuesday, May 11, 2010

Using ALTER IGNORE TABLE [TABLENAME] ADD UNIQUE INDEX `UNIQUE_INDEX` ([FIELDNAME]) to remove duplicate rows in a table is a fast an efficient process, however on large tables where the physical size is larger than server memory, the ALTER statement can take a long time to run in a production environment.

If you need to remove duplicates on a very large table (we recently used this on a table of 77 million rows), try this method :

delete t1 from table t1, table t2
where t1.duplicate_field= t2.duplicate_field (add more if need ie. and t1.duplicate_field2=t2.duplicate_field2)
and t1.unique_field > t2.unique_field
and breakup into ranges to run faster

If you use an auto-incrementing ID field as the primary key, use this as your unique field, and in the Where clause to run on a range of records to break into smaller operations.

Filed in MySQL | Also tagged , , , , , , , , , | Comments (0)