Re: mysqldump insert very slow
Hey Rick,
thanks a lot for the quick reply.
> If you are using InnoDB only, then buffer_pool =
> 1400M, key_buffer = 50M
Tried that, but it had no effect.
> What were the args on mysqldump? One of them
> specifies batched INSERTs vs one row per INSERT.
I am using the standard mysqldump statement: mysqldump -u username -p database > dumpfile
As I understand it this command automatically adds the -opt parameter. Might that be a problem? But then again, the same dump file runs very quickly on a parallel server.
I used exactly that statement for a couple of years now on different servers and it was never a problem. Only now with mysql 5.5.24 in its current configuration it takes much longer to insert the dump.
> > FOREIGN_KEY_CHECKS=0
> So, the FK checks are turned off during the load.
> (Good.)
>
> > manually switch them off (SET statement on they
> mysql console) and insert the dump
> Your action was overridden by the dump.
I am a bit confused about this answer. Doesn't /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; save the FOREIGN_KEY_CHECKS value into the OLD_FOREIGN_KEY_CHECKS variable before the dump happens and after the dump is done, the /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; sets the FOREIGN_KEY_CHECKS back to the original value? So if I set FOREIGN_KEY_CHECKS to 0 before running the insert, shouldn't the FOREIGN_KEY_CHECKS values be 0 again?
At the moment, if I set the FOREIGN_KEY_CHECKS to 0 and insert the dump file the value of FOREIGN_KEY_CHECKS afterwards is 1.
> SHOW CREATE TABLE
| table1 | CREATE TABLE `table1` (
`primary_key` int(11) NOT NULL AUTO_INCREMENT,
`field1` longtext CHARACTER SET latin1 NOT NULL,
`field2` varchar(50) CHARACTER SET latin1 NOT NULL,
`field3` longtext CHARACTER SET latin1 NOT NULL,
`field4` longtext CHARACTER SET latin1 NOT NULL,
`field5` text CHARACTER SET latin1,
`field6` longtext CHARACTER SET latin1,
`field7` varchar(25) NOT NULL,
`field8` longtext CHARACTER SET latin1,
`field9` int(11) DEFAULT '-1',
`field10` tinyint(3) unsigned NOT NULL DEFAULT '1',
`field11` text CHARACTER SET latin1,
`field12` text CHARACTER SET latin1 NOT NULL,
`field13` int(11) NOT NULL DEFAULT '-1',
`field14` varchar(25) CHARACTER SET latin1 NOT NULL DEFAULT 'Unknown',
`field15` int(11) NOT NULL DEFAULT '-1',
PRIMARY KEY (`primary_key`),
UNIQUE KEY `field7_field12` (`field7`,`field12`(25)),
KEY `field15` (`field15`),
KEY `field9` (`field9`),
CONSTRAINT `field15_tbl2` FOREIGN KEY (`field15`) REFERENCES `table2` (`primary_key`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `field9_tbl3` FOREIGN KEY (`field9`) REFERENCES `table3` (`primary_key`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=179616 DEFAULT CHARSET=utf8 |
> SHOW TABLE STATUS
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| table1 | InnoDB | 10 | Compact | 126117 | 611 | 77168640 | 0 | 14204928 | 14680064 | 179616 | 2012-06-21 10:00:02 | NULL | NULL | utf8_general_ci | NULL | | |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
I hope that gives you more insight!
Thanks a lot!
Cheers!