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
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With