Slow INSERT/UPDATE on InnoDB

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

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.

StackExchange Database Administrators Q#65753, answer score: 3