Advertisement:

Author Topic: [2.1 RC1] - Track IP very slow  (Read 1480 times)

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,724
  • xxx
[2.1 RC1] - Track IP very slow
« 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 ||
Liverpool FC Forum with 14 million+ posts.

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 566
Re: [2.1 RC1] - Track IP very slow
« Reply #1 on: February 18, 2019, 11:22:49 AM »
Which mysql/mariadb version you use?

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,724
  • xxx
Re: [2.1 RC1] - Track IP very slow
« Reply #2 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.
Liverpool FC Forum with 14 million+ posts.

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 566
Re: [2.1 RC1] - Track IP very slow
« Reply #3 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.

Offline live627

  • On Hiatus
  • SMF Hero
  • *
  • Posts: 5,620
  • Gender: Male
    • live627 on Facebook
    • live627 on GitHub
    • live627 on LinkedIn
    • @live627 on Twitter
    • livemods
Re: [2.1 RC1] - Track IP very slow
« Reply #4 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.
Try not to become a man of success, but rather try to become a man of value.
- Albert Einstein

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 566
Re: [2.1 RC1] - Track IP very slow
« Reply #5 on: March 07, 2019, 12:58:32 PM »
count(*) should be faster as count(column)
maybe the execution plane did change by this...

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,014
    • StoryBB/StoryBB on GitHub
Re: [2.1 RC1] - Track IP very slow
« Reply #6 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 566
Re: [2.1 RC1] - Track IP very slow
« Reply #7 on: March 07, 2019, 01:18:04 PM »

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,014
    • StoryBB/StoryBB on GitHub
Re: [2.1 RC1] - Track IP very slow
« Reply #8 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 566
Re: [2.1 RC1] - Track IP very slow
« Reply #9 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.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,014
    • StoryBB/StoryBB on GitHub
Re: [2.1 RC1] - Track IP very slow
« Reply #10 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline live627

  • On Hiatus
  • SMF Hero
  • *
  • Posts: 5,620
  • Gender: Male
    • live627 on Facebook
    • live627 on GitHub
    • live627 on LinkedIn
    • @live627 on Twitter
    • livemods
Re: [2.1 RC1] - Track IP very slow
« Reply #11 on: March 07, 2019, 08:51:18 PM »
Try not to become a man of success, but rather try to become a man of value.
- Albert Einstein