2

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

  • Performance of insert/updates depends not only h/w configuration of server, it also depends on size of table, number of rows table have, number of indexes, primary keys and foreign key constraints. – Arihant Godha May 23 '14 at 9:32
  • Right, you're correct, i forgot to say that the table sessions is empty (i did a TRUNCATE), so i guess the number of rows is the problem. Indexes and primary keys are in the thread description and the table doesn't have foreign keys – Sal00m May 23 '14 at 9:38
  • Try adding this innodb_flush_log_at_trx_commit = 0 in your /etc/my.cnf – Arihant Godha May 23 '14 at 9:50
  • @ArihantGodha, yeah, i did it and does not make so much diference (i comment it in the paragraph What i tried) – Sal00m May 23 '14 at 10:04
  • @ArihantGodha i update with the times with innodb_flush_log_at_trx_commit equals 0 and 1. A delay of 0,09 it's normal? – Sal00m May 23 '14 at 10:13
3

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.

  • In my case it improved the performance of a batch process in x30. Many thanks. – appartisan Feb 21 '17 at 2:22
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.