0

I'm seeing intermittent load spikes on my server, which correspond with a bunch of entries in MySQL's slow query log of the form:

# Time: 140326 21:00:40
# User@Host: admin[admin] @ localhost []
# Query_time: 14.713922  Lock_time: 0.000035 Rows_sent: 0  Rows_examined: 0
SET timestamp=1395867640;
INSERT INTO `jos_session` ( `session_id`,`time`,`username`,`gid`,`guest`,`client_id` ) VALUES ( 'sb39o10gdjhtacbkajeal0tp54','1395867625','','0','1','0' );

Now, it seems crazy to me that such a simple query could take so long. So, I presume this is due to something like too many requests in a short time, or some other request locking the table, or something like that. So, my question is along the lines of... Is it possible for MySQL to lock a table to inserts? What kinds of things should I be looking for to find the reason for these slow queries? I presume that it's not simply that this small table is actually taking that long to perform an insert. Especially since when I do a similar insert when the high load's not happening, it takes just no time at all.

In case it helps:

+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| ijkt_session | InnoDB |      10 | Compact    |   52 |            315 |       16384 |               0 |        49152 |   4194304 |           NULL | 2014-03-31 13:22:22 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

I guess the real question is, what can happen to an InnoDB table which could cause a simple insert of a single row to take so very long? Even if the table was huge, which it's not at ALL, it's tiny... I am imagining some kind of massive runaway index which is keeping track of every record that ever was, or something like that...? Or am I off base in thinking that it's impossible for a single insert to take a very long time?

Jeremy Warne
  • 3,437
  • 2
  • 30
  • 28
  • yes, mysql can lock an entire table for inserts, but for innodb it'd have to be YOU issuing the lock. – Marc B Apr 10 '14 at 17:43
  • What version are you on and have you tried flushing the table? – Elin Apr 11 '14 at 00:05
  • 1.5; what do you mean flushing the table? clearing out all the rows? have tried that; in normal operation it only has a few rows in it anyway, haven't ever had the chance to check the numbers of rows during a spike – Jeremy Warne Apr 11 '14 at 04:12
  • 1.5 is very old code, it does not really function well without a lot of customization on PHP 5.3 and 5.4 and I don't know how many people ever ran it on Innodb. Also I don't know what caching or session management you are using but what was supported in 1.5 is not always even present on modern servers. So it could be a lot of things. I wouldn't be surprised if there are 100s of notices when the update happens. The question is, what changed in your environment or site usage? – Elin Apr 12 '14 at 01:48

0 Answers0