XF 2.2 Slow response clicking on user's IP

[Mr. Jinx]

Clicking on 'ip' to reveal the user's IP is currently very slow on my site.
It takes about 1 minute for a result to show up.

I checked the xf_ip table, it has 2M records.
Checking a specific user id that is slow shows it has about 6000 ip records logged.
This is nothing special I guess.

Is there anything serverside I can check to debug this?

 

[Rhodium]

Clicking on 'ip' to reveal the user's IP is currently very slow on my site.
It takes about 1 minute for a result to show up.

I checked the xf_ip table, it has 2M records.
Checking a specific user id that is slow shows it has about 6000 ip records logged.
This is nothing special I guess.

Is there anything serverside I can check to debug this?

Are you tried with: https://xenforo.com/community/resources/slow-query-logger.6139/ ?

 

[Mr. Jinx]

Good one! I installed it and it immediately detected a slow query.
However, it doesn't reveal the complete query as far as I can see:

#0 src/XF/Db/Mysqli/Statement.php(108): SV\SlowQueryLogger\Db\Mysqli\SlowQueryLogAdapter->logQueryCompletion()
#1 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#2 src/XF/Db/AbstractAdapter.php(150): XF\Db\AbstractAdapter->query('
            SELECT ip
    ...', Array)
#3 src/XF/Repository/Ip.php(95): XF\Db\AbstractAdapter->fetchOne('
            SELECT ip
    ...', Array)
#4 src/XF/Entity/User.php(272): XF\Repository\Ip->getLoggedIp('user', 5263, 'register')
#5 [internal function]: XF\Entity\User->getIp('register')
#6 src/XF/Template/Templater.php(1166): call_user_func_array(Array, Array)
#7 internal_data/code_cache/templates/l4/s1/public/helper_ip.php(36): XF\Template\Templater->method(Object(SV\DailyStatistics\XF\Entity\User), 'getIp', Array)
#8 src/XF/Template/Templater.php(799): XF\Template\Templater->{closure}(Object(MaZ\AMP\XF\Template\XF22\Templater), Array, NULL)
#9 src/addons/MaZ/AMP/XF/Template/Templater.php(123): XF\Template\Templater->callMacro('helper_ip', 'ip_block', Array, Array, Object(XF\Template\MacroState))
#10 internal_data/code_cache/templates/l4/s1/public/content_ip_view.php(14): MaZ\AMP\XF\Template\Templater->callMacro('helper_ip', 'ip_block', Array, Array)
#11 src/XF/Template/Templater.php(1626): XF\Template\Templater->{closure}(Object(MaZ\AMP\XF\Template\XF22\Templater), Array, NULL)
#12 src/addons/MaZ/AMP/XF/Template/XF22/Templater.php(52): XF\Template\Templater->renderTemplate('content_ip_view', Array, true, NULL)
#13 src/XF/Template/Template.php(24): MaZ\AMP\XF\Template\XF22\Templater->renderTemplate('public:content_...', Array)
#14 src/XF/Mvc/Renderer/Json.php(84): XF\Template\Template->render()
#15 src/XF/Mvc/Renderer/Json.php(68): XF\Mvc\Renderer\Json->renderHtmlFallback('XF:Ip\\Ip', 'public:content_...', Array)
#16 src/XF/Mvc/Dispatcher.php(458): XF\Mvc\Renderer\Json->renderView('XF:Ip\\Ip', 'public:content_...', Array)
#17 src/XF/Mvc/Dispatcher.php(440): XF\Mvc\Dispatcher->renderView(Object(XF\Mvc\Renderer\Json), Object(XF\Mvc\Reply\View))
#18 src/XF/Mvc/Dispatcher.php(400): XF\Mvc\Dispatcher->renderReply(Object(XF\Mvc\Renderer\Json), Object(XF\Mvc\Reply\View))
#19 src/XF/Mvc/Dispatcher.php(58): XF\Mvc\Dispatcher->render(Object(XF\Mvc\Reply\View), 'json')
#20 src/XF/App.php(2326): XF\Mvc\Dispatcher->run()
#21 src/XF.php(488): XF\App->run()
#22 index.php(20): XF::runApp('XF\\Pub\\App')
#23 {main}

 

[Mr. Jinx]

Update:
With all add-ons disabled, still the same long delays.
This is what the debug log tells me. Any thoughts?

SELECT ip
FROM xf_ip
WHERE content_type = ?
AND content_id = ?
AND action = ?

ORDER BY log_date DESC
LIMIT 1
Params: user, 5263, register
Run Time: 11.911609

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_ip	index	content_type_content_id	log_date	4		863	Using where

1. SV\SlowQueryLogger\Db\Mysqli\SlowQueryLogAdapter->logQueryExecution() in src/XF/Db/Mysqli/Statement.php at line 70
2. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
3. XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 150
4. XF\Db\AbstractAdapter->fetchOne() in src/XF/Repository/Ip.php at line 95
5. XF\Repository\Ip->getLoggedIp() in src/XF/Entity/User.php at line 272
6. XF\Entity\User->getIp()
7. call_user_func_array() in src/XF/Template/Templater.php at line 1166
8. XF\Template\Templater->method() in internal_data/code_cache/templates/l4/s4/public/helper_ip.php at line 36
9. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 799
10. XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l4/s4/public/content_ip_view.php at line 14
11. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 1626
12. XF\Template\Templater->renderTemplate() in src/XF/Template/Template.php at line 24
13. XF\Template\Template->render() in src/XF/Mvc/Renderer/Html.php at line 48
14. XF\Mvc\Renderer\Html->renderView() in src/XF/Mvc/Dispatcher.php at line 458
15. XF\Mvc\Dispatcher->renderView() in src/XF/Mvc/Dispatcher.php at line 440
16. XF\Mvc\Dispatcher->renderReply() in src/XF/Mvc/Dispatcher.php at line 400
17. XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 58
18. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2326
19. XF\App->run() in src/XF.php at line 488
20. XF::runApp() in index.php at line 20

 

[Rhodium]

It's slow in my forum too, not as slow but some take 3-5 seconds, maybe adding and index could help.

 

[Mr. Jinx]

I tried pruning the ip logs to 365 days (in test env.) which makes it fast again.
I think searching the 2.000.000+ records might be a bit too much. Maybe elasticsearch could help here.

Found this old thread talking about the same, but in this case 51M records, so that's a lot more:

Partial fix - xf_ip table & cookie_login & IP check

With the cookie_login being logged to the xf_ip table, checking the IP of content or running shared IP checks now takes massively longer. Since XenForo 1.5.8 (2015-02 or so), Spacebattles has accumulated some 18 million records for cookie_login*. Checking the IP of content often takes...

xenforo.com

I'll look at the index, thanks!

 

[Joe Link]

Watching this