Slow query logger 1.3.1

[Xon]

Xon submitted a new resource:

Slow query logger - Generate a stack trace on slow queries

Generates an error log entry when a slow query is detected.

Limitations

• Slow query detection is hardcoded to 1 second
• Works on a single query at a time, not all queries for a request.
• Only logs slow public queries.

Installation

I recommend using Add-on install & upgrade or [TH] Install...

Read more about this resource...

 

[Xon]

Xon updated Slow query logger with a new update entry:

Maintenance update

Fix add-on name label. Was incorrectly "SVG Template"

Read the rest of this update entry...

 

[m1ne]

Perfect.

 

[m1ne]

So, how much info will this actually show? Anything close to mysql-slow.log when set in my.cnf?

 

[m1ne]

On install using Chris D's installer,

Only proper Resource Manager URLs are valid

still parses templates and installs though.

 

[Alpha1]

Thank you!

To alter the slow query threshold time which file should I edit?

 

[m1ne]

I don't actually see the slow query page, did it install correctly? I know there are some.

 

[Xon]

This is a debugging aid, I don't recommend leaving it on 24/7 if you have lots of things which are hitting the threshold.

So, how much info will this actually show? Anything close to mysql-slow.log when set in my.cnf?

I don't actually see the slow query page, did it install correctly? I know there are some.

It generates an error log entry, which lists the time and the URL which generated it. There is tend a stack trace to allow you to identify the codepath which generated the slow query.

It doesn't log the query itself, as often you are dealing with highly generic queries which are generated by some code path.

Thank you!

To alter the slow query threshold time which file should I edit?

https://github.com/Xon/XenForo-Slow...ad/library/SV/SlowQueryLogger/Profiler.php#L8

Just a adjust the default constructor value, it is currently 1.0 seconds. In the future I'll make it a configurable option.

 

[m1ne]

It's working now. 5 pages of slow queries in 3 hours, is that bad? Mostly 1.5 to 2.5 seconds.

 

[Xon]

It's working now. 5 pages of slow queries in 3 hours, is that bad? Mostly 1.5 to 2.5 seconds.

That means you have pages which take at least 1.5 to 2.5 seconds before they load

Now, you turn it off and go figure out who to blame

 

[rdn]

Just install and wait for server log entry right?
No options to configure.

 

[rdn]

What if I have very fast CPU and very good SSD I/O speed?
Clockspeed: 4.0 GHz
Turbo Speed: 4.4 GHz

I don't think I can reach 1.0 seconds .

 

[Xon]

Fyi, the initial start of a search is known to take a while, this appears to be a design issue where all content needs to be loaded and then verified viewability.

Just install and wait for server log entry right?
No options to configure.

Correct.

The part which comes after, figuring out why the queries are slow is either to be taken up with the add-on author or perhaps extra server resources are required. I've got some performance add-ons which may help, but this is an ongoing effort.

 

[Xon]

What if I have very fast CPU and very good SSD I/O speed?
Clockspeed: 4.0 GHz
Turbo Speed: 4.4 GHz

I don't think I can reach 1.0 seconds .

This is good!

Try seeing how search behaves, I keep getting slow queries from it

 

[rdn]

This is good!

Try seeing how search behaves, I keep getting slow queries from it

Since the last 20 minutes I installed this addon, and I got 300+ visitors real-time as of Google Analytics right now, every minute I got search entry.
So far No slow query has been log.

 

[m1ne]

No slow queries since disabling Lazy Image Loader which appeared frequently in logs. Strange.

 

[rdn]

https://github.com/Xon/XenForo-Slow...ad/library/SV/SlowQueryLogger/Profiler.php#L8

Just a adjust the default constructor value, it is currently 1.0 seconds. In the future I'll make it a configurable option.

If I want to change it to 0.5 seconds, code will be?

<?php

class SV_SlowQueryLogger_Profiler extends Zend_Db_Profiler
{
    protected $reportSlowQueries = false;
    protected $slowQuery = 0.5;

    public function __construct($reportSlowQueries = true, $slowQuery = 0.5, $enabled = false)

Line 6 and 8 should be modified?

 

[rdn]

I got first slow query log
But I'm not sure which addon to blame on this:

Error Info
Exception: Slow query detected: 1.1991 seconds, /t/threadtitle.59788/ - library/SV/SlowQueryLogger/Profiler.php:52
Generated By: User, 3 minutes ago
Stack Trace
#0 /library/Zend/Db/Statement.php(319): SV_SlowQueryLogger_Profiler->queryEnd(7)
#1 /library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /library/XenForo/Model/User.php(1177): Zend_Db_Adapter_Abstract->query('\n\t\t\t\tINSERT INT...', Array)
#3 /library/XenForo/Controller.php(473): XenForo_Model_User->updateSessionActivity(339774, '112.198.101.239', 'XenForo_Control...', 'Index', 'valid', Array, 1463803215, '')
#4 /library/XenForo/Controller.php(359): XenForo_Controller->updateSessionActivity(Object(XenForo_ControllerResponse_View), 'XenForo_Control...', 'Index')
#5 /library/XenForo/FrontController.php(358): XenForo_Controller->postDispatch(Object(XenForo_ControllerResponse_View), 'XenForo_Control...', 'Index')
#6 /library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#7 /index.php(13): XenForo_FrontController->run()
#8 {main}
Request State
array(3) {
  ["url"] => string(75) "http://www.domain.net/t/threadtitle.59788/"
  ["_GET"] => array(1) {
    ["/t/threadtitle_59788/"] => string(0) ""
  }
  ["_POST"] => array(0) {
  }
}

 

[m1ne]

I got first slow query log
But I'm not sure which addon to blame on this:

Error Info
Exception: Slow query detected: 1.1991 seconds, /t/threadtitle.59788/ - library/SV/SlowQueryLogger/Profiler.php:52
Generated By: User, 3 minutes ago
Stack Trace
#0 /library/Zend/Db/Statement.php(319): SV_SlowQueryLogger_Profiler->queryEnd(7)
#1 /library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /library/XenForo/Model/User.php(1177): Zend_Db_Adapter_Abstract->query('\n\t\t\t\tINSERT INT...', Array)
#3 /library/XenForo/Controller.php(473): XenForo_Model_User->updateSessionActivity(339774, '112.198.101.239', 'XenForo_Control...', 'Index', 'valid', Array, 1463803215, '')
#4 /library/XenForo/Controller.php(359): XenForo_Controller->updateSessionActivity(Object(XenForo_ControllerResponse_View), 'XenForo_Control...', 'Index')
#5 /library/XenForo/FrontController.php(358): XenForo_Controller->postDispatch(Object(XenForo_ControllerResponse_View), 'XenForo_Control...', 'Index')
#6 /library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#7 /index.php(13): XenForo_FrontController->run()
#8 {main}
Request State
array(3) {
  ["url"] => string(75) "http://www.domain.net/t/threadtitle.59788/"
  ["_GET"] => array(1) {
    ["/t/threadtitle_59788/"] => string(0) ""
  }
  ["_POST"] => array(0) {
  }
}

It won't be an addon unless it specifically mentions one I think. Who knows what that one is.

 

[rdn]

Browsing my Member List page generates slow query log also (1.4xx seconds to load :/ )
So I disable Member List Page .