Slow query logger 2.2.2

[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. No configuration required.

Once it has been running and reporting issues, I recommend disabling the add-on until you have pushed out fixes or understood why the previous logged queries are occurring.

It is not recommended to leave this add-on enabled.

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.

Limitations

• ...

Read more about this resource...

 

[cdub]

Does this still work?

 

[Ozzy47]

I would have to say with 38 downloads and zero bug reports, that yes it is very likely that it works.

 

[cdub]

It says unmaintained.

 

[Ozzy47]

Unmaintained does not mean an addon does not work, just that the developer is not actively supporting it.

 

[Xon]

I use this on my own sites (and some other sites I help run), but do not offer free support for what this add-on reports.

 

[Xon]

Xon updated Slow query logger with a new update entry:

2.0.1 - Maintenance update

• Remove debug code from release which disabled logging slow queries
• Require XF2.1+
• Add a add-on icon

Read the rest of this update entry...

 

[JoyFreak]

I’ve had this on over night and no log has been produced yet? Does this mean no issues have been found? How long should I have it on for?

 

[Xon]

"It depends"

Leaving it on with period of low usage probably isn't going to report anything but the most expensive report queries. What tends to trigger it is when the database is under load and poor query behaviour is no longer masked by having filled caches to read from.

 

[Xon]

Xon updated Slow query logger with a new update entry:

2.1.0 - Feature update

• Require php 7+
• Update add-on options to have their own additional input-group and to be better formatted
• Add "Too many queries threshold" option to help detect N+1 query behaviour. With various options to skip possible false positives.

Read the rest of this update entry...

 

[Xon]

Expect a number of my add-ons (and XF bug reports), as I slowly identify and patch various N+1 query behaviour in pages which normally aren't terribly visible

 

[Joe Link]

Anyone using this with XF 2.2?

 

[Xon]

Yes, I use it during development and occasionally on my live site. I wouldn't recommend leaving it running all the time but it is a useful debugging tool

 

[Joe Link]

Yes, I use it during development and occasionally on my live site. I wouldn't recommend leaving it running all the time but it is a useful debugging tool

Great, thanks

 

[Nirjonadda]

@Xon We have lot of Slow query logger but what addon doing this slowdown?

Exception: Slow query: 2.2358 seconds, /job.php src/addons/SV/SlowQueryLogger/Db/Mysqli/SlowQueryLogAdapter.php:387

Generated by: Unknown account Nov 28, 2021 at 7:03 PM

Stack trace

#0 src/XF/Db/Mysqli/Statement.php(110): SV\SlowQueryLogger\Db\Mysqli\SlowQueryLogAdapter->logQueryCompletion()
#1 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#2 src/XF/Mvc/Entity/Finder.php(1354): XF\Db\AbstractAdapter->query('
            SELECT `xf_...')
#3 src/XF/Repository/User.php(354): XF\Mvc\Entity\Finder->fetchOne()
#4 src/XF/Repository/Counters.php(22): XF\Repository\User->getLatestValidUser()
#5 src/addons/DBTech/InfoPanels/XF/Repository/Counters.php(13): XF\Repository\Counters->getForumStatisticsCacheData()
#6 src/addons/SV/DailyStatistics/XF/Repository/Counters.php(23): DBTech\InfoPanels\XF\Repository\Counters->getForumStatisticsCacheData()
#7 src/addons/SV/BookmarkEssentials/XF/Repository/Counters.php(20): SV\DailyStatistics\XF\Repository\Counters->getForumStatisticsCacheData()
#8 src/XF/Repository/Counters.php(30): SV\BookmarkEssentials\XF\Repository\Counters->getForumStatisticsCacheData()
#9 src/XF/Cron/Counters.php(17): XF\Repository\Counters->rebuildForumStatisticsCache()
#10 src/XF/Job/Cron.php(37): XF\Cron\Counters::rebuildForumStatistics(Object(XF\Entity\CronEntry))
#11 src/XF/Job/Manager.php(260): XF\Job\Cron->run(8)
#12 src/XF/Job/Manager.php(202): XF\Job\Manager->runJobInternal(Array, 8)
#13 src/XF/Job/Manager.php(86): XF\Job\Manager->runJobEntry(Array, 8)
#14 job.php(43): XF\Job\Manager->runQueue(false, 8)
#15 {main}

Request state

array(4) {
  ["url"] => string(8) "/job.php"
  ["referrer"] => string(34) "/login/login"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

Exception: Slow query: 7.3713 seconds, / src/addons/SV/SlowQueryLogger/Db/Mysqli/SlowQueryLogAdapter.php:387

Generated by: arn43 Nov 28, 2021 at 6:05 PM

Stack trace

#0 src/XF/Db/Mysqli/Statement.php(110): SV\SlowQueryLogger\Db\Mysqli\SlowQueryLogAdapter->logQueryCompletion()
#1 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#2 src/XF/Db/AbstractAdapter.php(147): XF\Db\AbstractAdapter->query('
                SELECT thr...', 8)
#3 src/addons/DBTech/InfoPanels/Repository/Stats.php(135): XF\Db\AbstractAdapter->fetchRow('
                SELECT thr...', 8)
#4 src/addons/DBTech/InfoPanels/Widget/WelcomePanel.php(39): DBTech\InfoPanels\Repository\Stats->getVisitorMostActiveForum()
#5 internal_data/code_cache/widgets/_63_dbtech_infopanels_above_nodes_welcome_panel.php(5): DBTech\InfoPanels\Widget\WelcomePanel->render()
#6 src/XF/Template/Templater.php(7509): XF\SubContainer\Widget->{closure}(Object(MaZ\AMP\XF\Template\XF22\Templater), Array, Array)
#7 src/XF/SubContainer/Widget.php(168): XF\Template\Templater->renderWidgetClosure(Object(Closure), Array)
#8 src/XF/Template/Templater.php(1928): XF\SubContainer\Widget->getCompiledWidget(Array, Array)
#9 internal_data/code_cache/templates/l1/s4/public/forum_list.php(219): XF\Template\Templater->widgetPosition('forum_list_abov...', Array)
#10 src/XF/Template/Templater.php(1651): XF\Template\Templater->{closure}(Object(MaZ\AMP\XF\Template\XF22\Templater), Array, NULL)
#11 src/addons/MaZ/AMP/XF/Template/XF22/Templater.php(52): XF\Template\Templater->renderTemplate('forum_list', Array, true, NULL)
#12 src/XF/Template/Template.php(24): MaZ\AMP\XF\Template\XF22\Templater->renderTemplate('public:forum_li...', Array)
#13 src/XF/Mvc/Renderer/Html.php(50): XF\Template\Template->render()
#14 src/XF/Mvc/Dispatcher.php(460): XF\Mvc\Renderer\Html->renderView('XF:Forum\\Listin...', 'public:forum_li...', Array)
#15 src/XF/Mvc/Dispatcher.php(442): XF\Mvc\Dispatcher->renderView(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#16 src/XF/Mvc/Dispatcher.php(402): XF\Mvc\Dispatcher->renderReply(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#17 src/XF/Mvc/Dispatcher.php(60): XF\Mvc\Dispatcher->render(Object(XF\Mvc\Reply\View), 'html')
#18 src/XF/App.php(2345): XF\Mvc\Dispatcher->run()
#19 src/XF.php(512): XF\App->run()
#20 index.php(20): XF::runApp('XF\\Pub\\App')
#21 {main}

Request state

array(4) {
  ["url"] => string(1) "/"
  ["referrer"] => string(118) "/conversations/?__cf_chl_tk=jYp0ZItJSlKt74M5GbhFdWWaN37vIAqDZneat7WfpG8-1638025226-0-gaNycGzNuP0"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

Exception: Slow query: 1.2512 seconds, / src/addons/SV/SlowQueryLogger/Db/Mysqli/SlowQueryLogAdapter.php:387

Generated by: arn43 Nov 28, 2021 at 6:08 PM

Stack trace

#0 src/XF/Db/Mysqli/Statement.php(110): SV\SlowQueryLogger\Db\Mysqli\SlowQueryLogAdapter->logQueryCompletion()
#1 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#2 src/XF/Mvc/Entity/Finder.php(1387): XF\Db\AbstractAdapter->query('
            SELECT `xf_...')
#3 src/addons/XFA/TrophiesEnhancement/Widget/Leaderboard.php(19): XF\Mvc\Entity\Finder->fetch()
#4 internal_data/code_cache/widgets/_77_xfa_te_trophies_leadboard.php(5): XFA\TrophiesEnhancement\Widget\Leaderboard->render()
#5 src/XF/Template/Templater.php(7509): XF\SubContainer\Widget->{closure}(Object(MaZ\AMP\XF\Template\XF22\Templater), Array, Array)
#6 src/XF/SubContainer/Widget.php(168): XF\Template\Templater->renderWidgetClosure(Object(Closure), Array)
#7 src/XF/Template/Templater.php(1928): XF\SubContainer\Widget->getCompiledWidget(Array, Array)
#8 internal_data/code_cache/templates/l1/s7/public/forum_list.php(216): XF\Template\Templater->widgetPosition('forum_list_side...', Array)
#9 src/XF/Template/Templater.php(1651): XF\Template\Templater->{closure}(Object(MaZ\AMP\XF\Template\XF22\Templater), Array, NULL)
#10 src/addons/MaZ/AMP/XF/Template/XF22/Templater.php(52): XF\Template\Templater->renderTemplate('forum_list', Array, true, NULL)
#11 src/XF/Template/Template.php(24): MaZ\AMP\XF\Template\XF22\Templater->renderTemplate('public:forum_li...', Array)
#12 src/XF/Mvc/Renderer/Html.php(50): XF\Template\Template->render()
#13 src/XF/Mvc/Dispatcher.php(460): XF\Mvc\Renderer\Html->renderView('XF:Forum\\Listin...', 'public:forum_li...', Array)
#14 src/XF/Mvc/Dispatcher.php(442): XF\Mvc\Dispatcher->renderView(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#15 src/XF/Mvc/Dispatcher.php(402): XF\Mvc\Dispatcher->renderReply(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#16 src/XF/Mvc/Dispatcher.php(60): XF\Mvc\Dispatcher->render(Object(XF\Mvc\Reply\View), 'html')
#17 src/XF/App.php(2345): XF\Mvc\Dispatcher->run()
#18 src/XF.php(512): XF\App->run()
#19 index.php(20): XF::runApp('XF\\Pub\\App')
#20 {main}

Request state

array(4) {
  ["url"] => string(1) "/"
  ["referrer"] => string(23) "/"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

 

[Xon]

Can you provide a list of indexes and the columns that are on the xf_user table? What version of MySQL/MariaDb?

The first query is a stock query, which should preform OK. The second is @DragonByte Tech's InfoPanels and the 3rd is @XFA XFA/TrophiesEnhancement. I'm not sure what they are querying for.

 

[Nirjonadda]

Can you provide a list of indexes

Where can find out indexes?

the columns that are on the xf_user table?

I think lot of columns tab?

 

[interforo]

I got this:

Stack trace
#0 src/XF/Db/Mysqli/Statement.php(110): SV\SlowQueryLogger\Db\Mysqli\SlowQueryLogAdapter->logQueryCompletion()
#1 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#2 src/XF/Db/AbstractAdapter.php(157): XF\Db\AbstractAdapter->query('
            SE...', Array)
#3 src/addons/AwesomeForo/TopUser/Repository/TopUser.php(708): XF\Db\AbstractAdapter->fetchAll('
            SE...')
#4 src/addons/AwesomeForo/TopUser/Widget/TopXUser.php(23): AwesomeForo\TopUser\Repository\TopUser->getYearlyTopPosters(10)
#5 internal_data/code_cache/widgets/_44_top_user_side.php(5): AwesomeForo\TopUser\Widget\TopXUser->render()
#6 src/XF/Template/Templater.php(7509): XF\SubContainer\Widget->{closure}(Object(SV\StandardLib\XF\Template\Templater), Array, Array)
#7 src/XF/SubContainer/Widget.php(168): XF\Template\Templater->renderWidgetClosure(Object(Closure), Array)
#8 src/XF/Template/Templater.php(1928): XF\SubContainer\Widget->getCompiledWidget(Array, Array)
#9 internal_data/code_cache/templates/l5/s9/public/forum_list.php(216): XF\Template\Templater->widgetPosition('forum_list_side...', Array)
#10 src/XF/Template/Templater.php(1651): XF\Template\Templater->{closure}(Object(SV\StandardLib\XF\Template\Templater), Array, NULL)
#11 src/XF/Template/Template.php(24): XF\Template\Templater->renderTemplate('forum_list', Array)
#12 src/XF/Mvc/Renderer/Html.php(50): XF\Template\Template->render()
#13 src/XF/Mvc/Dispatcher.php(460): XF\Mvc\Renderer\Html->renderView('XF:Forum\\Listin...', 'public:forum_li...', Array)
#14 src/XF/Mvc/Dispatcher.php(442): XF\Mvc\Dispatcher->renderView(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#15 src/XF/Mvc/Dispatcher.php(402): XF\Mvc\Dispatcher->renderReply(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#16 src/XF/Mvc/Dispatcher.php(60): XF\Mvc\Dispatcher->render(Object(XF\Mvc\Reply\View), 'html')
#17 src/XF/App.php(2345): XF\Mvc\Dispatcher->run()
#18 src/XF.php(512): XF\App->run()
#19 index.php(20): XF::runApp('XF\\Pub\\App')
#20 {main}
Request state
array(4) {
  ["url"] => string(7) "/forum/"
  ["referrer"] => bool(false)
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

can you help me debug and fix?

 

[XFA]

Can you provide a list of indexes and the columns that are on the xf_user table? What version of MySQL/MariaDb?

The first query is a stock query, which should preform OK. The second is @DragonByte Tech's InfoPanels and the 3rd is @XFA XFA/TrophiesEnhancement. I'm not sure what they are querying for.

I am just querying for users having trophy points > 0 ordered by value desc.

 

[Xon]

@Nirjonadda and @interforo can you try running this command?

optimize table xf_user

It'll rebuild statistics and such on the table which may help.

Where can find out indexes?

If you can get the full output of the following statement somehow, as phpmyadmin tends to truncate it;

show create table xf_user;