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.

MySQL 5.5.x on Windows, 4GB RAM.

I have a Wordpress Multi-Site install. The WPMU db contains over 6k tables (I know, horrendous... WPMU is not my favorite app right now)

I am running mysqldump early mornings, and from my monitoring tools, I am seeing that my other db sites on the server, along with the WPMU site is going down for about 10-15 min during the time when the WPMU db is being dumped. The error is "Too many connections"

MySQL is currently set to 100 connections.

What is my best path forward to mitigate the downtime? Up the connections, and throw more RAM at the box?

share|improve this question
    
Are all your tables InnoDB or MyISAM ??? –  RolandoMySQLDBA Feb 3 '12 at 18:22
    
Both InnoDB & MyISAM –  Josh Brower Feb 3 '12 at 18:28

3 Answers 3

If you convert all your tables to InnoDB, then you can perform your mysqldumps without disturbing any of DB Connections.

Here is a script to mass convert all MyISAM tables into InnoDB.

mysql -uroot -p -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql

Just execute /root/ConvertMyISAMToInnoDB.sql during a full maintenance. This needs to be done only once. Once all your tables are InnoDB, you could probably mysqldump your databases in parallel. I wrote an earlier post back in April 2011 on multiple ways to perform mysqldumps. You need option 2. You will also need to add the --single-transaction option to each mysqldump so that all the mysqldumps are launched from the same point-in-time.

However, you could still experience performance problems anyway because each table that is being mysqldump'd will push old data out of the InnoDB Buffer Pool to push in table data for each table being mysqldump'd.

Operations must slow down because queries being executed must share the InnoDB Buffer Pool with the mysqldumps that are thrashing it.

As for upping the max_connections you can do this without restarting mysql.

Please add this to my.ini

[mysqld]
max_connections=1000

You do not have to restart mysql. Just run this command in the mysql client:

mysql> SET GLOBAL max_connections = 1000;

You may need more RAM on the box to accommodate additional DB Connections as well as a Decent Sized InnoDB Buffer Pool.

share|improve this answer
    
I noticed that my avail memory tanks to less than 512mb when the dump is executing... If I bump the max connections up that much, what will that do to the avail memory? –  Josh Brower Feb 3 '12 at 18:30
    
Each DB Connection will take up to the sum of join_buffer_size, sort_buffer_size, read_buffer_size. In light of this, please be careful how high you raise max_connections. I just gave an arbitrary number of 1000. Use your discretion. –  RolandoMySQLDBA Feb 3 '12 at 18:38

What options are you using with mysqldump? WPMU is notorious for opening a LOT of connections for each page. If you are locking the tables instead of using --single-transaction during backup, some connections will be forced to wait until the backup completes.

In the DB, each WPMU site has a set of tables that are prefixed with WP_xxx_[Table Name], where xxx is the ID of the site. If your sites are independent, try backing up each site separately to decrease the time the locks are in place.

share|improve this answer

These are two things I would do.

Global Analysis of the Machine

What is the memory, io and cpu situation when the dumps are running. A program like Process Explorer http://technet.microsoft.com/en-us/sysinternals/bb896653 can help you to determine exactly what's going on with windows.

Remember that in Windows 32bit versions, 2GB is reserved for the system unless you have enterprise and you've activated the /3GB option in the boot.ini file

You will see right away if its a memory/io/cpu issue.

If its a CPU issue, you will see a lot of context switching

If its memory, then you will see things like disk swapping and you're system running really slow.

How is MySQL really performing

A program like Jet Profiler http://www.jetprofiler.com/ can give you a more in depth view of MySQL's internal behavior. Perhaps a MyISAM table is being locked by another query during the backup. The advantage to Jet Profiler is that you can let it run all day long, perferably from another machine and see what's happening over a given period of time.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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