20220803.1 performance comments. (General)

by Joe I, Wednesday, April 19, 2023, 00:09 (374 days ago) @ Joe I

OK, so here's an update. Performance notes across all MLF versions tested:

1. Performance of forum running MLF 2.0.2

  • Home Page (20, 15), not logged in: 2 - 2.25 seconds
  • Home Page (20, 15), logged in: 2.25 - 3 seconds
  • Admin Page, logged in: 1 second
  • Contact Page, not logged in: 1 second

2. Performance of forum running MLF 2.3.25

  • Home Page (20, 15), not logged in: 1.75 - 2.5 seconds
  • Home Page (20, 15), logged in: 2.25 - 3.25 seconds
  • Admin Page, logged in: 1 second
  • Contact Page, not logged in: 1 second

3. Performance of forum running base MLF 2.4.24

  • Home Page (20, 15), not logged in: 4 - 5.5 seconds
  • Home Page (20, 15), logged in: 0.75 - 1.5 seconds
  • Admin Page, logged in: 0.5 seconds
  • Contact Page, not logged in: 0.5 second

4. Performance of forum running Mod 1 (latest_postings) MLF 2.4.24

  • Home Page (20, 15), not logged in: 0.75 - 1.5 seconds
  • Home Page (20, 15), logged in: 0.75 - 1.5 seconds
  • Admin Page, logged in: 0.5 seconds
  • Contact Page, not logged in: 0.5 second

5. Performance of forum running base MLF 20220803.1

  • Home Page (20, 15), not logged in: 9 - 13 seconds
  • Home Page (20, 15), logged in: 10.5 - 14.5 seconds
  • Admin Page, logged in: 4 seconds
  • Contact Page, not logged in: 4 seconds

6. Performance of forum running modified (remove .spam check) MLF 20220803.1

  • Home Page (20, 15), not logged in: 5 - 7 seconds
  • Home Page (20, 15), logged in: 3 - 4.5 seconds
  • Admin Page, logged in: 3 seconds
  • Contact Page, not logged in: 3 seconds

7. Performance of forum running Mod 2 (total_spam, total_postings, latest_postings) MLF 20220803.1

  • Home Page (20, 15), not logged in: 2.5 seconds
  • Home Page (20, 15), logged in: 3 seconds
  • Admin Page, logged in: 2 seconds
  • Contact Page, not logged in: 2 seconds

Notes:

Item #4 is as I tested and noted in a 2.4.24 performance post, which contains only a simple change in latest_postings to wrap rst.user_id in an IFNULL.
This is now the best performing version across all tested.

Item #7 is after tweaking and testing the longer running queries in 20220803.1, and mods to the total_spam, total_postings, and latest_postings queries in index.inc.php and main.inc.php. Note that these results are the best I've gotten so far with simple tweaks to the above queries, but I'll take one more run at them. Significantly better performance than Item #5 - base 20220803.1.

I was successful in getting even better performance out of these above queries, but changes to the code get more complex, mostly because of the additional changes necessary to display_query_where and display_query_and includes and I didn't yet dig into where those two "includes" are also used.

I have attempted a number of variations on each of these queries, using ANALYZE for results testing, and attempting subqueries, temp tables, JOIN rearranging, FORCE INDEXes, and various other methods.

My mods to 20220803.1 are below (note, these are the queries as run through SQL testing. I can post the actual php code changes if desired. The php code mods should have a net zero effect on all board installations, regardless of configuration.

total_spam
base

-- total_spam base (650 ms)
-- main.inc.php
SELECT COUNT(*) FROM mlf2_entries 
LEFT JOIN mlf2_akismet_rating ON mlf2_akismet_rating.eid = mlf2_entries.id 
LEFT JOIN mlf2_b8_rating ON mlf2_b8_rating.eid = mlf2_entries.id 
WHERE (mlf2_akismet_rating.spam = 1 OR mlf2_b8_rating.spam = 1)

mod

-- total_spam Mod 2 (400 ms)
SELECT COUNT(*) FROM mlf2_entries 
LEFT JOIN 
 (SELECT eid AS ak_eid, spam AS ak_spam FROM mlf2_akismet_rating WHERE spam = 1) AS akismet 
    ON ak_eid = mlf2_entries.id
LEFT JOIN 
 (SELECT eid AS b8_eid, spam AS b8_spam FROM mlf2_b8_rating WHERE spam = 1) AS b8 
    ON b8_eid = mlf2_entries.id 
   WHERE ak_spam = 1 OR b8_spam = 1

The differences between these two are:

  • akismet and b8 JOINs are moved into subqueries


total_postings
base

-- total_postings (no spam) - base [1 s]
-- main.inc.php
SELECT COUNT(*) 
FROM mlf2_entries 
LEFT JOIN mlf2_akismet_rating ON mlf2_akismet_rating.eid = mlf2_entries.id 
LEFT JOIN mlf2_b8_rating ON mlf2_b8_rating.eid = mlf2_entries.id 
WHERE (mlf2_akismet_rating.spam = 0 AND mlf2_b8_rating.spam = 0)

mod

 
-- total_postings (no spam) - Mod 7 [650 ms] 
-- Faster options require logic rewrite to display_spam_query_where and display+spam_query_and
-- main.inc.php
SELECT STRAIGHT_JOIN COUNT(*) 
FROM mlf2_entries 
LEFT JOIN mlf2_akismet_rating ON mlf2_akismet_rating.eid = mlf2_entries.id 
LEFT JOIN mlf2_b8_rating ON mlf2_b8_rating.eid = mlf2_entries.id 
WHERE (mlf2_akismet_rating.spam = 0 AND mlf2_b8_rating.spam = 0)

The differences between these two are:

  • Add STRAIGHT_JOIN to force order of JOINs


latest_postings
base

-- latest_postings base [1.75 s]
SELECT 
   id, pid, tid, name, user_name, mlf2_entries.user_id, UNIX_TIMESTAMP(mlf2_entries.time) AS TIME , UNIX_TIMESTAMP(mlf2_entries.time + INTERVAL 0 MINUTE) AS TIMESTAMP, UNIX_TIMESTAMP(last_reply) AS last_reply, subject, category, rst.user_id AS req_user
    FROM mlf2_entries -- FORCE INDEX (timeid)
 LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id = mlf2_entries.user_id
 LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = mlf2_entries.id AND rst.user_id = 0
 LEFT JOIN mlf2_akismet_rating ON mlf2_akismet_rating.eid = mlf2_entries.id 
 LEFT JOIN mlf2_b8_rating ON mlf2_b8_rating.eid = mlf2_entries.id
 WHERE mlf2_akismet_rating.spam = 0 AND mlf2_b8_rating.spam = 0
 ORDER BY mlf2_entries.time DESC 
 LIMIT 15;

mod

-- latest_postings Mod 3 [b][1.5 ms][/b]
SELECT 
   id, pid, tid, name, user_name, mlf2_entries.user_id, UNIX_TIMESTAMP(mlf2_entries.time) AS TIME , UNIX_TIMESTAMP(mlf2_entries.time + INTERVAL 0 MINUTE) AS TIMESTAMP, UNIX_TIMESTAMP(last_reply) AS last_reply, subject, category, rst.user_id AS req_user
    FROM mlf2_entries FORCE INDEX (TIME)
 LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id = mlf2_entries.user_id
 LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = mlf2_entries.id AND IFNULL(rst.user_id, NULL) = 0
 LEFT JOIN mlf2_akismet_rating ON mlf2_akismet_rating.eid = mlf2_entries.id 
 LEFT JOIN mlf2_b8_rating ON mlf2_b8_rating.eid = mlf2_entries.id
 WHERE mlf2_akismet_rating.spam = 0 AND mlf2_b8_rating.spam = 0
 ORDER BY mlf2_entries.time DESC 
 LIMIT 15

The differences between these two are:

  • Add FORCE INDEX (time) on mlf2_entries
  • IFNULL(rst.user_id, NULL)

IFNULL is the same change I made to improve 2.4.24 performance. Here, I’ve also needed. To add FORCE INDEX.

In the table above, you can see that just these changes make a significant difference in performance over the base 20220803.1 code. Feel free to incorporate any/all of these changes into MLF code, or ask any questions.

[edit by Auge]: I enclosed the provided SQL-code in code blocks for better readability. Column lists can overflow the block width in a few cases, but the interesting things are all still visible.

Complete thread:

 RSS Feed of thread