InnoDB Engine Simple Update Query Takes Way Too Long (query_end)

Question

I recently added a lot of triggers to various mysql tables in order to force integrity with everything. I am worried that I might have killed my engine because simple updates are now taking very very long.

Consider:

UPDATE `partner_stats` SET earnings=1 WHERE date=CURRENT_DATE()
0 rows affected. ( Query took 0.6523 sec ) 

SELECT * FROM  `partner_stats` WHERE date = CURRENT_DATE() 
1 total, Query took 0.0004 sec

The SELECT takes 0.0004 but a simple UPDATE takes .65!

This particular table has only one row and has no triggers associated with it. Switching the engine to MyISAM fixes the problem but I will need to add triggers for this table in the future so I want to stick with InnoDB.

What is wrong with my engine? Is it too busy working with the other tables? What profiling or debugging options do I have?

EDIT: Did a profiling and it shows this:

mysql> show profile for QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000064 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000032 |
| System lock          | 0.000007 |
| init                 | 0.000051 |
| Updating             | 0.000069 |
| end                  | 0.011682 |
| query end            | 0.218070 |
| closing tables       | 0.000016 |
| freeing items        | 0.000017 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000002 |
+----------------------+----------+
12 rows in set (0.00 sec)

The two sections taking the longest are definitely end and query end. These can both be related to writing to the binlog. Here is another question with a couple possibly useful answers: http://stackoverflow.com/questions/13234290/lots-of-query-end-states-in-mysql-all-connections-used-in-a-matter-of-minutes. One of the answers says, "We changed the sync_binlog variable from 1 to 0, which means that instead of flushing binlog changes to disk on each commit, it allows the operating system to decide when to fsync() to the binlog. That entirely resolved the "query end" problem for us." — Andrew G, Jul 07 '13 at 04:14

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

You should try to optimize InnoDB Engine like explained here. On a production server with no replication, you can use:

innodb_flush_log_at_trx_commit = 2

# A value of 1 is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash.

innodb_buffer_pool_size = [75% of total memory]

innodb_log_file_size = [25% of innodb_buffer_pool_size]

innodb_log_buffer_size = [10% of innodb_log_file_size]

innodb_thread_concurrency = [2 X Number of CPUs) + Number of Disks, or 0 for autodetect]

InnoDB Engine Simple Update Query Takes Way Too Long (query_end)

1 Answers1