Advertisement:

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

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,726
  • 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: 616
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,726
  • 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: 616
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

  • Developer
  • SMF Hero
  • *
  • Posts: 5,729
  • 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.

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 616
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: 72,655
    • 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.
Please don’t PM me for paid work, I’m not for hire, and even if I was, I doubt you could afford me.
USD$150 per hour. Typical waiting list 3 months.

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 616
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: 72,655
    • 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.
Please don’t PM me for paid work, I’m not for hire, and even if I was, I doubt you could afford me.
USD$150 per hour. Typical waiting list 3 months.

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 616
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: 72,655
    • 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.
Please don’t PM me for paid work, I’m not for hire, and even if I was, I doubt you could afford me.
USD$150 per hour. Typical waiting list 3 months.

Offline live627

  • Developer
  • SMF Hero
  • *
  • Posts: 5,729
  • 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 »