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;
