Simple Machines Community Forum

SMF Development => Bug Reports => Fixed or Bogus Bugs => Topic started by: Ben_S on February 18, 2019, 08:06:17 AM

Title: [2.1 RC1] - Track IP very slow
Post by: Ben_S on February 18, 2019, 08:06:17 AM
Tracking by IP seems excessively slow, query running for > 250 seconds
?action=profile;area=tracking;sa=ip;searchip=***;u=38590

Code: [Select]
SELECT COUNT(*) AS message_count        FROM yabbse_messages AS m
            INNER JOIN yabbse_boards AS b ON (b.id_board = m.id_board)
        WHERE 1=1 AND m.poster_ip >= unhex('***') and m.poster_ip <= unhex('***')
Code: [Select]
|| *id* || *select_type* || *table* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *Extra* ||
|| 1 || SIMPLE || b || index || id_board || id_board || 2 ||  || 38 || Using index ||
|| 1 || SIMPLE || m || ref || ID_BOARD || ID_BOARD || 2 || rawk_testforum.b.id_board || 214766 || Using where ||
Title: Re: [2.1 RC1] - Track IP very slow
Post by: albertlast on February 18, 2019, 11:22:49 AM
Which mysql/mariadb version you use?
Title: Re: [2.1 RC1] - Track IP very slow
Post by: Ben_S on February 18, 2019, 12:57:50 PM
Think it is still on 5.6 for legacy reasons, I'll have a look when I'm at a computr next and update it to the latest supported ver and see what indexes / changes are needed.
Title: Re: [2.1 RC1] - Track IP very slow
Post by: albertlast on February 18, 2019, 01:06:01 PM
smf 2.1 use some features (cte) only when a rdbms avaible with the needed feature set,
in case of mysql you need 8.0.

but this wouldn't change you tracking ip issue.
Title: Re: [2.1 RC1] - Track IP very slow
Post by: live627 on March 06, 2019, 05:51:03 PM
In attempting to test this, I keep getting white pages/memory overflows. Must dig deeper.

I changed the above query to SELECT COUNT(m.id_msg) AS message_count and now it's down from 5 seconds to one.
Title: Re: [2.1 RC1] - Track IP very slow
Post by: albertlast on March 07, 2019, 12:58:32 PM
count(*) should be faster as count(column)
maybe the execution plane did change by this...
Title: Re: [2.1 RC1] - Track IP very slow
Post by: Arantor on March 07, 2019, 01:01:17 PM
COUNT(*) was only as fast as COUNT(columname) in MySQL on MyISAM tables; this is not true for InnoDB tables (or at least never used to be true) and it would instead do a table scan since unlike MyISAM tables, the number of rows isn't pre-stored.

Using COUNT(table-pk) is the fastest way I know of doing it on InnoDB, which is what everyone should be using anyway since MyISAM tables are rubbish.
Title: Re: [2.1 RC1] - Track IP very slow
Post by: albertlast on March 07, 2019, 01:18:04 PM
The manuel say something different: https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count
Title: Re: [2.1 RC1] - Track IP very slow
Post by: Arantor on March 07, 2019, 01:26:02 PM
As usual, you're talking about the latest version which I assure you continues to be the minority.

Considering that Ubuntu's latest LTS (18.04) ships with 5.7 and I guarantee you people will continue to use that on the server for the next 5-10 years (as it is a 10 year LTS), that's possibly a more realistic benchmark of what to optimise for?

If I really wanted to do scale, I'd look at something like Amazon Aurora, which currently also runs 5.6/5.7 depending on your version.
Title: Re: [2.1 RC1] - Track IP very slow
Post by: albertlast on March 07, 2019, 01:49:18 PM
You find the same manuel statement on the older version,
so nothing wrong here to link to the newest one.
Title: Re: [2.1 RC1] - Track IP very slow
Post by: Arantor on March 07, 2019, 01:51:32 PM
If only it actually behaved according to the manual, then, as opposed to the point above where it demonstrably behaves better, and has a known basis for doing so that is not at all new.
Title: Re: [2.1 RC1] - Track IP very slow
Post by: live627 on March 07, 2019, 08:51:18 PM
My fix for this: https://github.com/SimpleMachines/SMF2.1/pull/5489