Slow INSERT/UPDATE on InnoDB

Question

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

Answer 1

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.

Answer 2

Good morning,

I believe this is happening because InnoDB spend too much time reordering the leafs.

=> You have to resize your primary key or use a new one. You can propagate the new int unsigned into the session instead of that hash value.

You will also need to rebuild the table after dropping/inserting. Add alter table sessions engine=innodb; into your flushing script.

Is it really necessary to store a 10000 varchar in the session ? If not drop it.

The idea here is to reduce InnoDB's internal work.

You might also consider another storage such as memcached for such volatile data.

Cheers.