• Welcome to Simple Machines Community Forum. Please login or sign up.
September 17, 2021, 10:02:19 AM

News:

Wondering if this will always be free?  See why free is better.


[2.1 RC1] - Track IP very slow

Started by Ben_S, February 18, 2019, 08:06:17 AM

Previous topic - Next topic

Ben_S

Tracking by IP seems excessively slow, query running for > 250 seconds
?action=profile;area=tracking;sa=ip;searchip=***;u=38590

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('***')

|| *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 ||
Liverpool FC Forum with 14 million+ posts.

albertlast

Which mysql/mariadb version you use?

Ben_S

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.
Liverpool FC Forum with 14 million+ posts.

albertlast

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.

live627

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.

albertlast

count(*) should be faster as count(column)
maybe the execution plane did change by this...

Arantor

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.
No good deed goes unpunished
All helpful urges should be circumvented


Arantor

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.
No good deed goes unpunished
All helpful urges should be circumvented

albertlast

You find the same manuel statement on the older version,
so nothing wrong here to link to the newest one.

Arantor

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.
No good deed goes unpunished
All helpful urges should be circumvented

live627


Advertisement: