We faced MySQL restores DB VERY slow: ~3 min for 15 MB dump. This is a new dedicated server with a lot of RAM.
We were told the most possible reason is GUID is uded as primary key in many tables:
CREATE TABLE `acl_actions` ( `id` char(36) NOT NULL, `date_entered` datetime NOT NULL, `name` varchar(150) default NULL, ........ `category` varchar(100) default NULL, `deleted` tinyint(1) default '0', PRIMARY KEY (`id`), KEY `idx_aclaction_id_del` (`id`,`deleted`), KEY `idx_category_name` (`category`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
This will certainly affect bulk insert performance as in the case of a dump import as the MYI file which holds indexes will be doing random I/O rather that the boost it would receive for sequential I/O in the case of a monotonically incrementing integer. Your key_buffer_size also comes in to play but does not seem to be undersized in this case based on key blocks free and the watermark key blocks used.
I'm not sure with that person and moreover, I have not enough experience myself, so I have to ask here: Why on Earth there will be random IO? Because id is almost random so index's content will be not-so-linear?
Is it possible to decrease performance so drastically because of GUIDs? Is there any workaround except replace UIDs to autoincremental ID globally?
UPD: after all we found the main reason for slowness: binlog. We have disabled binlog and this reduces import time x12!