Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

"query end" step very long at random times

Tags:

mysql

innodb

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

like image 715
Lliane Avatar asked Aug 04 '11 07:08

Lliane


People also ask

Why are my queries taking so long?

Slow queries can mean your database does more work than it needs to, which means it's using more resources than it needs to. When limited resources like CPU or I/O run out, everything can start to slow down. Inefficient use of resources is also a problem when you're not using the resources you have.

What is a slow query?

The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.


1 Answers

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.

like image 174
Lliane Avatar answered Sep 27 '22 20:09

Lliane