Slow INSERTs on one table

jamesgolickjamesgolick EntrantInactive User Role Participant
Queries like this are pretty common in our slow query logs:

228826-# [email protected]: fetlife[fetlife] @ app4.dal.fetlife [10.18.5.79]
228827-# Thread_id: 42698 Schema: fetlife_production
228828-# Query_time: 7.656149 Lock_time: 0.000023 Rows_sent: 0 Rows_examined: 0 Rows_affected: 1 Rows_read: 2
228829:INSERT INTO `friendship_requests` (`user_id`, `requested_by_user_id`, `created_at`, `updated_at`) VALUES (?, ?, '2012-02-21 03:37:50', '2012-02-21 03:37:50');

This table has been behaving funny for a while. Almost no lock time, only 2 rows read, 1 affected, and almost 8 seconds of runtime.

CREATE TABLE `friendship_requests` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`requested_by_user_id` int(11) NOT NULL,
`created_at` datetime default NULL,
`updated_at` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_friendship_requests_on_user_id_and_requested_by_user_ id` (`user_id`,`requested_by_user_id`),
KEY `index_friendship_requests_on_user_id_and_created_at` (`user_id`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=13940603 DEFAULT CHARSET=utf8

and

+
+
| count(*) |
+
+
| 983848 |
+
+

No other table in our system has this issue.

Ideas?

Comments

  • xaprbxaprb Mentor Inactive User Role Leader
    I think we need more info. I'd enable PROFILE in the slow query log and see what it says next time this happens.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.