GUID as reason for MySQL import slowness

Question

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;

The explanation:

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!

Answer 1

The old cliche SIZE MATTERS applies here without a doubt.

Smaller keys would load into a table faster that with biggers (This becomes even more evident with InnoDB). The bigger the columns in the index, the fewer the keys would be residing in BTREE pages. Thus, you get taller BTREE trees. Even a BTREE height of 3 or more, which is bad for a small table, would be the result of having large columns. The same would apply to the name (up to 150 characters) and category (up to 100 characters) fields. Making all columns CHAR instead of VARCHAR would increase SELECT performance, but the significant increase of diskspace + BTREE node management would throw SELECT performance under the bus as the table grows.

Looking at all of your keys I can see this table being extermely lopsided in index size verses table size.

Please run this query

SELECT data_lenth,index_length
FROM information_schema.tables
WHERE table_name = 'acl_action';

You will see the combined sum of all index pages being as large or larger than the table. From a physical standpoint, the file acl_actions.MYI will be bigger that acl_actions.MYD.

Here is where random I/O would come into play: Notice your index idx_aclaction_id_del

Notice that you have it defined as

KEY `idx_aclaction_id_del` (`id`,`deleted`),

If you ever decide to query for deleted records, you will get an full index scan because of the order of trhe columns in this index.

KEY `idx_aclaction_del_id` (`deleted`,`id`),

That way all deleted keys are grouped together. Likewise, all non-deleted rows are grouped together.

Try altering the table design like this

CREATE TABLE `acl_actions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `guid` 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`),
  UNQIUE KEY  (`guid`),
  KEY `idx_aclaction_id_del` (`deleted`,`id`),
  KEY `idx_category_name` (`category`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You should run the following query

SELECT name,categrory FROM acl_actions PROCEDURE ANALYSE();

This will recommend the correct sizes for those columns with the given dataset.