•  

Announcement

Announcement Module
Collapse
No announcement yet.

slow insert on table with 1.6mm rows

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

  • slow insert on table with 1.6mm rows

    #1
    The table below currently contains approx. 1.6mm records, inserts on this table are consistently slow, i.e. between 2 and 4 seconds. Any suggestions, thoughts as to where to look?

    CREATE TABLE `log` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sid` int(11) NOT NULL,
    `activity_type` enum('a','b','c) DEFAULT 'a',
    `activity_name` int(11) DEFAULT NULL,
    `activity_description` varchar(50) DEFAULT NULL,
    `activity_date` datetime NOT NULL,
    `activity_score` int(11) DEFAULT NULL,
    `lid` int(11) NOT NULL,
    `wrong` text,
    `num` tinyint(3) unsigned DEFAULT '0',
    `num_wrong` tinyint(3) unsigned DEFAULT '0',
    `num_right` tinyint(3) unsigned DEFAULT '0',
    `num_missing` tinyint(3) unsigned DEFAULT '0',
    `external_id` int(11) DEFAULT NULL,
    `end` datetime DEFAULT NULL,
    `time_offset` char(6) DEFAULT '-04:00',
    PRIMARY KEY (`id`),
    KEY `lid` (`id`),
    KEY `slid` (`sid`,`lid`),
    KEY `aname` (`activity_name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2319610 DEFAULT CHARSET=utf8
    Tags: None

  • #2
    What does the output from:

    SHOW GLOBAL VARIABLES;-- andSHOW GLOBAL STATUS;

    look like?

    And how big is the table in MB?

    Comment


    • #3
      show global status and show global variables produce a large number of rows, are you looking for any particular values?

      doing a show table status on this table produces:

      Engine: InnoDB
      Version: 10
      Row_format: Compact
      Rows: 1501814
      Avg_row_length: 112
      Data_length: 168476672
      Max_data_length: 0
      Index_length: 128663552
      Data_free: 523239424
      Auto_increment: 2320137
      Create_time: 2010-10-25 21:02:06
      Update_time: NULL
      Check_time: NULL
      Collation: utf8_general_ci
      Checksum: NULL
      Create_options:
      Comment:

      Comment


      • #4
        Well there are a bunch of them that can be interesting, but here are some ideas instead:
        1.
        What is the server doing during this time?
        High CPU, high I/O load?

        2.
        Are all inserts on this machine slow or is it only inserts to this table?

        3.
        What kind of hardware (disks) are you using?
        What is the setting of:
        innodb_flush_log_at_trx_commit
        If you don't have a raid controller then you can try to set this variable to 2 instead.

        3.
        Is the server under heavy load?
        Do you have a lot of SELECT's or UPDATES against this table during this time?

        4.
        If it's only inserts to this table that are slow and this is the only big table, how large is your innodb_buffer_pool_size?
        If the innodb buffer is too small then an insert can go very slow since it might have to swap in and out a lot of data.

        There a lot of things that can cause a problem so we have to get to know your server before we can say something useful.

        Comment


        • #5
          KEY `lid` (`id`),

          that key is redundant because id it is the same as your primary key.

          Comment


          • #6
            That was a typo, its a key on "lid".

            Comment


            • #7
              We have made the following changes to our system to try and resolve this issue and are observing.

              a) Changed table engine to myisam as this table is mostly an insert table, very little updates, some selects through a dedicated slave.

              b) Configured the server with "concurrent_insert=2" to allow inserts during selects.

              In response to your questions...

              What is the server doing during this time?
              High CPU, high I/O load?

              -- Dedicated system, currently not under heavy load

              Are all inserts on this machine slow or is it only inserts to this table?

              -- This insert seems to be the one that comes up, in particular, in the slow log.


              What kind of hardware (disks) are you using?

              -- This particular system, is not the fastest of the bunch in terms of RAID and disk configuration, we are looking at upgrading it.

              What is the setting of: innodb_flush_log_at_trx_commit

              -- innodb_flush_log_at_trx_commit_session | 3

              If you don't have a raid controller then you can try to set this variable to 2 instead.

              Is the server under heavy load?

              -- Moderate load.

              Do you have a lot of SELECT's or UPDATES against this table during this time?

              -- Limited to no updates on this table, some selects.

              If it's only inserts to this table that are slow and this is the only big table, how large is your innodb_buffer_pool_size?

              -- innodb_buffer_pool_size | 5368709120 |
              -- Although this does not matter at this time as we've changed to myisam engine.

              Thanks!

              Comment

              Working...
              X