Mysql – Slow INSERT/UPDATE on InnoDB

mysql-5.5performance

I have a problem with the performance of my MySQL server, let me explain:

I instaled a MySQL server, version 5.5.20

Server version: 5.5.20-log Source distribution

SELECT queries runs fast on every table but INSERT/UPDATES queries run very slow, for example, for this table:

CREATE TABLE `sessions` (
  `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `session_expires` int(11) NOT NULL DEFAULT '0',
  `session_data` varchar(10000) COLLATE utf8_unicode_ci NOT NULL,
  `session_current_application` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'okn',
  PRIMARY KEY (`session_id`),
  KEY `INDEX_SessionExpires` (`session_expires`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I get this results:

mysql> insert into sessions values ('qweasd',1400,'data','test');
Query OK, 1 row affected (0.14 sec)

mysql> show profile;
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000057 |
| checking permissions         | 0.000005 |
| Opening tables               | 0.000016 |
| System lock                  | 0.000006 |
| init                         | 0.000008 |
| update                       | 0.000045 |
| Waiting for query cache lock | 0.000003 |
| update                       | 0.000008 |
| end                          | 0.000002 |
| query end                    | 0.140482 |
| closing tables               | 0.000021 |
| freeing items                | 0.000044 |
| logging slow query           | 0.000002 |
| cleaning up                  | 0.000003 |
+------------------------------+----------+
14 rows in set (0.00 sec)

Are this timestamps normal for a simple INSERT query?

The server has 16GB of RAM and 6 CPUs so i don't think the hardware is the problem.

What i tried:

  • Well, i read a lot about performance, and i check innodb_flush_log_at_trx_commit (https://stackoverflow.com/questions/6937443/query-end-step-very-long-at-random-times) i tried with 0 and 1 and times are similar, shorter with 0 but not much (from 0,13 to 0,09), and i think the insert should be faster.

  • Based on this thread i check my RAID but is a software RAID 1, so doesn't seem my problem comes from this.

  • Deactivate the query_cache, when i did it, the querys are even slower…

Query cache:

mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 67108864  |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

I'm totally lost and don't know what to do… any help will be greatly appreciated

PS: This thread could be related with this one

Best Answer

As you can see, the process that takes most time is "query end". There is an interlocking problem when multiple threads want to write the file at the same time, this way the log will be flushed every second:

innodb_flush_log_at_trx_commit = 0

in the /etc/my.cnf file

Also, you should try Optimize InnoDB as explained above for future query improvement.

Related Question