Notification query needs tuning - can encounter MAX_JOIN_SIZE issues

[sbulen]

Description

One of the main notifications queries sometimes goes all cartesian & causes the following error in the logs:
Database Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Adding an index on id_board on log_notify addresses the issue.

From the forum:
https://www.simplemachines.org/community/index.php?topic=584569.0

The query may be found here:

SMF/Sources/tasks/CreatePost-Notify.php

Line 124 in d3a8569

SELECT

Steps to reproduce

1. I think we've only seen this happen when the host has put a strict low value on MAX_JOIN_SIZE. (7000000 in the forum example.)

Environment (complete as necessary)

• Version/Git revision: 2.1.3
• Database Type: MySQL
• Database Version: 8.0
• PHP Version: 8.1

Additional Information:

[albertlast]

sql command

SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, t.id_member_started, t.id_member_updated
FROM smf_log_notify AS ln
	INNER JOIN smf_members AS mem ON (ln.id_member = mem.id_member)
	LEFT JOIN smf_topics AS t ON (t.id_topic = ln.id_topic)
	LEFT JOIN smf_boards AS b ON (b.id_board = ln.id_board OR b.id_board = t.id_board)
WHERE ln.id_member != 13639
	AND ln.id_topic = 181 OR ln.id_board = 26516
	

[sbulen]

I compared 5 different queries, with & without the index:

Q1 - Existing
Q2 - Subquery (to try & force it to do the log_notify query first)
Q3 - Union (from albertlast in the forum)
Q4 - Union (inner joins)
Q5 - Union (inner joins, separate board & topic queries)

Adding an index to the existing query yielded the best results - by far.

Query 5 yielded the best results without adding an index.

I think the simplest solution is the best: just add the index.

Results:

Query 1 - Existing

With index:

Without index:

Query 2 - Subquery

With index:

Without index:

Query 3 - Union

With index:

Without index:

Query 4 - Union with inner joins

With index:

Without index:

Query 5 - Union with separate board & topic queries

With index:

Without index:

Raw Queries:

SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, t.id_member_started, t.id_member_updated
FROM smf_log_notify AS ln
	INNER JOIN smf_members AS mem ON (ln.id_member = mem.id_member)
	LEFT JOIN smf_topics AS t ON (t.id_topic = ln.id_topic)
	LEFT JOIN smf_boards AS b ON (b.id_board = ln.id_board OR b.id_board = t.id_board)
WHERE ln.id_member != 13639
	AND ln.id_topic = 181 OR ln.id_board = 26516;

SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, t.id_member_started, t.id_member_updated
FROM smf_members AS mem
	INNER JOIN 
	(
		SELECT id_member, id_board, id_topic, sent
		FROM smf_log_notify
		WHERE id_member != 13639
			AND id_topic = 181 OR id_board = 26516
	) AS ln ON (ln.id_member = mem.id_member)
	LEFT JOIN smf_topics AS t ON (t.id_topic = ln.id_topic)
	LEFT JOIN smf_boards AS b ON (b.id_board = ln.id_board OR b.id_board = t.id_board)

SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, t.id_member_started, t.id_member_updated
FROM smf_log_notify AS ln
	INNER JOIN smf_members AS mem ON (ln.id_member = mem.id_member)
	LEFT JOIN smf_topics AS t ON (t.id_topic = ln.id_topic)
	LEFT JOIN smf_boards AS b ON (b.id_board = t.id_board)
WHERE ln.id_member != 13639
	AND ln.id_topic = 181
UNION ALL
SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, null, null
FROM smf_log_notify AS ln
	INNER JOIN smf_members AS mem ON (ln.id_member = mem.id_member)
	LEFT JOIN smf_boards AS b ON (b.id_board = ln.id_board)
WHERE ln.id_member != 13639
	AND ln.id_board = 26516

SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, t.id_member_started, t.id_member_updated
FROM smf_log_notify AS ln
	INNER JOIN smf_members AS mem ON (ln.id_member = mem.id_member)
	INNER JOIN smf_topics AS t ON (t.id_topic = ln.id_topic)
	INNER JOIN smf_boards AS b ON (b.id_board = t.id_board)
WHERE ln.id_member != 13639
	AND ln.id_topic = 181
UNION ALL
SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, null, null
FROM smf_log_notify AS ln
	INNER JOIN smf_members AS mem ON (ln.id_member = mem.id_member)
	INNER JOIN smf_boards AS b ON (b.id_board = ln.id_board)
WHERE ln.id_member != 13639
	AND ln.id_board = 26516

SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, t.id_member_started, t.id_member_updated
FROM smf_topics AS t 
	INNER JOIN smf_log_notify AS ln ON (t.id_topic = ln.id_topic)
	INNER JOIN smf_members AS mem ON (ln.id_member = mem.id_member)
	INNER JOIN smf_boards AS b ON (b.id_board = t.id_board)
WHERE ln.id_member != 13639
	AND t.id_topic = 181
UNION ALL
SELECT
	ln.id_member, ln.id_board, ln.id_topic, ln.sent,
	mem.email_address, mem.lngfile, mem.pm_ignore_list,
	mem.id_group, mem.id_post_group, mem.additional_groups,
	mem.time_format, mem.time_offset, mem.timezone,
	b.member_groups, null, null
FROM smf_boards AS b 
	INNER JOIN smf_log_notify AS ln ON (b.id_board = ln.id_board)
	INNER JOIN smf_members AS mem ON (ln.id_member = mem.id_member)
WHERE ln.id_member != 13639
	AND b.id_board = 26516

[albertlast]

the amount of rows doesn't look good to me for an performance test,
could you go higher? somthing in a more smf.org size

[sbulen]

The above tests are on a live board with 40k users & 245k posts. Real data. Mid-sized board.

You can do the comparisons also! Queries are above!

[albertlast]

the base is real, yea,
but i see "showing rows 0",
which mean that no return happening.

a real world example is better than i trying to simulate this in beahvior.

[albertlast]

created a test case where return amount is ~4k rows,
pg 14 in ms:

Query #	4k rows	emtpy	4k rows index	empty index
1	43	0.37	45	0.10
2	44	0.37	57	0.05
3	5	0.33	5	0.07
4	4	0.35	4	0.07
5	4	0.08	4	0.09

[sbulen]

OK, you got me curious, so I rebuilt my pg large db for a side by side comparison.

Very similar DBs, pg 14 & mysql 8.0.20, both with ~2.5M posts, 1K boards, 30K users, 300K topics. Both have ~2.5M log_notify records, random distribution. Result set ~2900 rows for both, with the exact same queries. I think this is unusually high, even for a very large forum. But makes for a good test...

Query	MySQL wo/ix	MySQL w/ix	PG wo/ix	PG w/ix
1	2.08 s	1.09 s	0.20 s	0.08 s
2	2.05 s	1.06 s	0.20 s	0.08 s
3	0.89 s	0.03 s	0.18 s	0.07 s
4	0.84 s	0.02 s	0.21 s	0.07 s
5	0.83 s	0.03 s	0.21 s	0.07 s

As expected, overall pg performs way better.

The UNION queries helped MySQL a lot, but not really pg. For both, the different UNION query variants didn't make much of a difference.

Unexpected, but mysql 8 had a slight edge over pg on the UNION queries when the index was added.

So... It may make sense to do both the IX and a UNION query. It will certainly be beneficial for very large forums, especially on MySQL.

[sbulen]

We've seen a variation of this on another forum, so the issue was not a one-off. (https://www.simplemachines.org/community/index.php?topic=586123.0)

EDIT: That forum topic is unrelated.

Flagging for 2.1.5 to at least draw some further discussion.

[live627]

These solutions seem a little over engineered to me. Why not simply modify the predicate depending on the alert type?

	$request = $smcFunc['db_query']('', '
		SELECT
			ln.id_member, ln.id_board, ln.id_topic, ln.sent,
			mem.email_address, mem.lngfile, mem.pm_ignore_list,
			mem.id_group, mem.id_post_group, mem.additional_groups,
			mem.time_format, mem.time_offset, mem.timezone,
			t.id_member_started, t.id_member_updated
		FROM {db_prefix}log_notify AS ln
			JOIN {db_prefix}members AS mem ON (ln.id_member = mem.id_member)
			LEFT JOIN {db_prefix}topics AS t ON (t.id_topic = ln.id_topic)
		WHERE ' . ($type == 'topic' ? 'ln.id_board = {int:board}' : 'ln.id_topic = {int:topic}'),
		array(
			'member' => $posterOptions['id'],
			'topic' => $topicOptions['id'],
			'board' => $topicOptions['board'],
		)
	);

[sbulen]

It's been a while, but IIRC, the challenge is you can have both. A single post may trigger a set of topic notifications as well as a set of board notifications. The logic in that task is built around that, and works from a common list/query.

The problem is scalability... I think these users with strict MAX_JOIN_SIZE are basically the canaries in the coal mine for us here, letting us know we need to tune this query.

[sbulen]

Similar report here, same query: https://www.simplemachines.org/community/index.php?topic=586169.0