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.

Please bear with me as I ask this question, which looks similar to questions already on DBA@SE.

In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database and Application Server are hosted on this server. There are two MySql databases - Prod and Staging on the same server. Some of the tables are on MyIasm engine while some on InnoDB.

root     10119     1  0  1233  1012   3 Mar03 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe ...
mysql    10233 10119  9 238047 108896 0 Mar03 ?        12:11:11 /usr/local/mysql/libexec/mysqld ...

Every day a dump of the Prod db is taken and restored in the Staging db. The total size is close to 1 GB and a few tables have more than million rows.

  • Taking the dump via mySqlDump is fast and takes less than a minute (complete dump - not just deltas from binary logs). Optimizations like turning off auto_commit, foreign_key_checks etc. have been added to the dump.

  • From a couple of days, the restoration part (to the Staging DB) has become extremely terrible as it takes ~1 hr and paralyzes the application. (The same restore on my dev box takes less than 4 minutes - configurations are different and noone's accessing it)

    • key_buffer_size is 512Mb [~12.5 % of the total RAM]. table_cache=256 is suboptimal (but there aren't too many requests during that time). read_buffer_size=1M. sort_buffer_size=64M
    • In the restore script, before inserting data each table is locked, data is inserted and then the table is unlocked.
    • Slow Query logs are enabled (and most of the queries from the dump find a place there) - Could high I/O be affecting the performance?

I am trying to figure out the root cause so that it may be mitigated. As I have limited access to the server, I am trying to gather possible questions so that I take them to the hosting providers. Could you please provide me with pointers that I should be looking at?

CPU utilization and I/O peak during this time.

Thank you.

[Edit] - After increasing innodb_buffer_pool_size, the restore time has reduced to an extent.

This is the sample dump, that gets generated:
SET FOREIGN_KEY_CHECKS=0;
SET unique_checks=0;
SET AUTOCOMMIT=0;

// The scripts go here:
DROP TABLE IF EXISTS `access_tokens`;
CREATE TABLE `access_tokens` ( ... );
LOCK TABLES `access_tokens` WRITE;
INSERT INTO `access_tokens` VALUES (...) // All of the data is in a single insert
UNLOCK TABLES;

...
// Script for other tables

SET FOREIGN_KEY_CHECKS=1;
SET unique_checks=1;
COMMIT;
SET AUTOCOMMIT=1;
share|improve this question
    
I have many questions. 1) Is the prod data all MyISAM, all InnoDB, or a mixture of both ? 2) Even though you only have one DB server, is the Prod DB and Staging DB in the same MySQL Instance, or two different MySQL Instances ? –  RolandoMySQLDBA Mar 10 at 20:54
    
1) It's a mixture of both. 60 % of the tables but (approx) 85 % of the data is on InnoDB. 2) Both the DBs are on the same MySQL Instance (as I see only one instance of mysqld on the server) and a single my.cnf. Please let me know if my understanding is incorrect. –  TJ- Mar 10 at 21:00
    
In your question, you said Optimizations like turning off auto_commit, foreign_key_checks etc. have been added to the dump.. Please show he exact mysqldump command you used to make the mysqldump. –  RolandoMySQLDBA Mar 10 at 21:12
    
@RolandoMySQLDBA I have added the sample script which is restored. I don't have the exact mysqldump command as the dump is generated by the hosting providers and I don't have access to that script right now. –  TJ- Mar 11 at 5:31

1 Answer 1

You have not included your server config so I am writing all I could think of.

You have already innodb_buffer_pool_size so skipping that.

On Staging Server use following

innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M

On mysqldump use following options

--disable-keys
--extended-insert
--add-locks

You should use change log size this way.

share|improve this answer
    
Both of my databases are running on the same mysql instance. The actual prod transactions won't be very large. Should I still increase the innodb_log_file_size and innodb_log_buffer_size? –  TJ- Mar 11 at 9:07
    
innodb_log_buffer_size is not required but innodb_log_file_size should help. –  Jai Mar 11 at 9:13
    
okay, I'll try this. Thanks. –  TJ- Mar 11 at 9:19

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.