CODEWITHSUNDEEP
mysql
Phil Kulak
Phil Kulak

Reputation: 7220

Really Slow MySQL Insert Query

I've got a table with about half a million records in it. It's not huge. A couple varchar(255) fields, some ints, a float, and a couple timestamps. There are indices on the ints as well as foreign key constraints. Inserts are taking forever. I'm talking 1-4 seconds to insert one row. I've had to deal with slow select queries plenty of times, but I'm stuck trying to figure out what's going on with this insert.

EDIT: Okay, I was really just asking for ideas on how to debug this, but, here's all the tables involved. Inserting into "ingredients" is what takes forever. Hopefully throwing a good portion of my schema onto the web doesn't bite me later...

CREATE TABLE `ingredients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quantity` float DEFAULT NULL,
  `food` varchar(255) NOT NULL,
  `unit_id` int(11) DEFAULT NULL,
  `ingredient_group_id` int(11) DEFAULT NULL,
  `order_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `range` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `unit_id` (`unit_id`),
  KEY `ingredient_group_id` (`ingredient_group_id`),
  CONSTRAINT `ingredients_ibfk_1` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`),
  CONSTRAINT `ingredients_ibfk_2` FOREIGN KEY (`ingredient_group_id`) REFERENCES `ingredient_groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=269974 DEFAULT CHARSET=utf8

CREATE TABLE `units` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `abbreviation` varchar(255) CHARACTER SET latin1 NOT NULL,
  `type` int(11) NOT NULL,
  `si` float NOT NULL,
  `lower_bound` float DEFAULT NULL,
  `lower_unit_id` int(11) DEFAULT NULL,
  `upper_bound` float DEFAULT NULL,
  `upper_unit_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lower_unit_id` (`lower_unit_id`),
  KEY `upper_unit_id` (`upper_unit_id`),
  CONSTRAINT `units_ibfk_1` FOREIGN KEY (`lower_unit_id`) REFERENCES `units` (`id`),
  CONSTRAINT `units_ibfk_2` FOREIGN KEY (`upper_unit_id`) REFERENCES `units` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

CREATE TABLE `ingredient_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `recipe_id` int(11) NOT NULL,
  `order_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `recipe_id` (`recipe_id`),
  CONSTRAINT `ingredient_groups_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32739 DEFAULT CHARSET=utf8

Upvotes: 0

Views: 2781

Answers (3)

Phil Kulak
Phil Kulak

Reputation: 7220

Turns out I had a trigger that was falling prey to this bug:

http://bugs.mysql.com/bug.php?id=9021

I turned it from an IN to an = and now inserts are running in 0.00 seconds.

I totally forgot I had a trigger hooked up to this table. That's my fault. Sorry to anyone who wasted their time trying to help me out, but thank you so much anyway.

Upvotes: 1

Javier
Javier

Reputation: 62583

lots of information missing, but the things i'd check first:

  • if on MyISAM tables: extremely fragmented files, especially index files. Use filefrag to check that. This can happen if the database grew slowly with time. If so, just shut down MySQL, copy the database directory, rename the original and new copies and restart MySQL

  • if you use InnoDB tables: a file-based datastore, again too fragmented. In this case, fragmentation can be both at filesystem level (check and handle as above) or at datastore level, for that use the InnoDB tools. In the worst case a block-device-based datastore (which can't get externally fragmented) can exhibit a bad case of internal fragmentation.

  • some index with extremely low cardinality. That is, a non-unique index with few distinct values present, that is, lots of repeats. This indexes approach asymptotically a linear list, with O(n) time profiles. This can be either an index on the table or the referred foreign index.

  • reader contention. unlikely, but a huge number of concurrent readers can stall a single writer.

Edit:

After reading your definitions, i think ingredients.unit_id and ingredients.ingredient_group_id are the first candidates to check, since they seem to have very low cardinality.

The first one is unlikely to be useful (do you plan to select all ingredients that are measured in spoons?), so you can probably just drop it.

The second one can be very useful; but if there are few ingredient groups, the cardinality can be very low, degrading performance. To raise cardinality, add some part to make it more discriminating. If no other field is likely to appear in a query together with group id, just add the main id or creation date, making it (ingredient_group_id, id) or (ingredient_group_id, created_at). Seems counterintuitive to add complexity to make it faster, but it can really help. As a bonus, you can add a sort by created_at to any query that selects by ingredient_group_id without performance penalty.

Upvotes: 3

The Scrum Meister
The Scrum Meister

Reputation: 30111

You might want to look at the ingredients.unit_id index, since it has a low selectivity.

are the inserts happening concurrently?

Upvotes: 1

Related Questions