8

I'm benchmarking a web application, and I have a problem that occurs on about 1% of my queries, mostly UPDATES (but also sometimes INSERT). I did a profiling on those requests and it seems it's the query end step that takes a lot of time.

starting  0.000029
checking permissions  0.000005
Opening tables    0.000017
System lock   0.000005
init  0.000032
Updating  0.000052
end   0.000030
**query end   1.825892**
closing tables    0.000025
freeing items 0.000020
logging slow query    0.000007
logging slow query    0.000029
cleaning up   0.000008

As I went through the documentation

end : This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.

query end : This state occurs after processing a query but before the freeing items state.

So does this mean the cleanup of my UPDATE is taking time ? What does this step do exactly, how can I improve the performances ?

Thanks

Community
  • 1
  • 1
Lliane
  • 831
  • 2
  • 7
  • 15

1 Answers1

12

Issue solved by adding

innodb_flush_log_at_trx_commit = 0

in the /etc/my.cnf

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.

Lliane
  • 831
  • 2
  • 7
  • 15
  • 7
    Setting innodb_flush_log_at_trx_commit=0 isn't a solution to adopt lightly, it fundamentally changes the durability of your transactions! –  Dec 11 '12 at 16:12
  • 2
    Agreed. Setting innodb_flush_log_at_trx_commit naively can be dangerous, and the answer is misleading. – Pedro Werneck Feb 01 '13 at 00:58
  • @PedroWerneck -- I tried the solution and it reduced my "query end" from 0.0006 to 0.00007. Any alternative solution? – ipruthi Jun 30 '13 at 05:12
  • Solution to what? query end 0.0006 doesn't seem to be a problem. – Pedro Werneck Jul 03 '13 at 13:22