Announcement

Announcement Module
Collapse
No announcement yet.

Slow INSERTs on one table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow INSERTs on one table

    Queries like this are pretty common in our slow query logs:

    228826-# User@Host: 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_requeste d_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?

  • #2
    I think we need more info. I'd enable PROFILE in the slow query log and see what it says next time this happens.

    Comment

    Working...
    X