Excessive MySQL memory usage on email::mark_notifications_by_parent()

[BaronVonMEowMEOW]

I posted the other day about a strange issue with mySQL memory being gradually consumed and not reclaimed, resulting in a crash. (intermittently restarting the db service every 1/2 hour now as a workaround)

After much debugging, I have found the query that is causing the problem and can reliably reproduce it.

notification_emails_table table has only 282 rows

however the query has an excessively large number of parent ids so i end up with a query like this:

DELETE FROM phpbb_notification_emails
WHERE notification_type_id IN (3, 4, 5, 6, 20) AND user_id = '64277' AND item_parent_id IN ('1193', '1750', '1769', '1853', '1869', '1878', '1883', '1916', '1934', '1938', '1945', '1950', '1957', '1958', '1960', '1965', '1972', '1973', '1977', '1978', '2005', '2014', <.... around 7,500 more values here>)

It takes around 15 seconds to run, and eats up 1 gb of ram each time. The ram then is not freed after the query completes.

Very weird.

Thoughts on how to mitigate this?

I tried adding indexes, and removing the compound primary key on that table and that didn't help

I also tried using integer values instead of strings, also didn't help (the columns are integers not strings so the query should pass integer values)

I will keep messing and see what I can figure out. I may have to purge everyone's notifs and old emails but that won't prevent it from happening again

[BaronVonMEowMEOW]

Trying out splitting the item parent id array into chunks of 100 as a temporary fix. Will see if this helps.
(ugly cause part of the query code is duplicated, but I'm just testing)

Code: Select all

public function mark_notifications_by_parent($notification_type_id, $item_parent_id, $user_id, $time = false, $mark_read = true)
	{
		// split item parent ids into chunks to control memory usage on large # of notifs
		if($item_parent_id !== false) {
			$chunked=array_chunk($item_parent_id,100);
			foreach($chunked as $chunk) {
				$sql = 'DELETE FROM ' . $this->notification_emails_table . '
					WHERE ' . ($notification_type_id !== false ? $this->db->sql_in_set('notification_type_id', $notification_type_id) : '1=1') .
					($user_id !== false ? ' AND ' . $this->db->sql_in_set('user_id', $user_id) : '') .
					($item_parent_id !== false ? ' AND ' . $this->db->sql_in_set('item_parent_id', $chunk, false, true) : '');
					$this->db->sql_query($sql);
			}
		}
		else {
			$sql = 'DELETE FROM ' . $this->notification_emails_table . '
				WHERE ' . ($notification_type_id !== false ? $this->db->sql_in_set('notification_type_id', $notification_type_id) : '1=1') .
				($user_id !== false ? ' AND ' . $this->db->sql_in_set('user_id', $user_id) : '');
			$this->db->sql_query($sql);
		}
	}

[AmigoJack]

I posted the other day about

Keep in mind that starting a new topic is not "reporting back" - maybe you want to call a moderator to move these posts to your initial topic?

a strange issue with mySQL memory being gradually consumed and not reclaimed, resulting in a crash.
...
I have found the query that is causing the problem and can reliably reproduce it.

When one is able to do that just thru SQL then the DBMS must be considered broken. Check which version you're running (and if it is indeed MySQL, not MariaDB) to then check if newer versions fix such a potential bug.

Code: Select all

AND user_id = '64277' AND item_parent_id IN ('1193', '1750'

That's horrible SQL: why are both user ID and item parent ID handed over as strings instead of integers? Implicit type conversion unnecessarily asks for trouble when it could have been done correctly right away. I hope it's at least coming from an extension, not an untouched phpBB installation.

[P_I]

When one is able to do that just thru SQL then the DBMS must be considered broken. Check which version you're running (and if it is indeed MySQL, not MariaDB) to then check if newer versions fix such a potential bug.

If the OP is indeed using MariaDB then it seems like it might be related to this (from another topic)

MariaDB 10.5.8, 10.4.17, 10.3.27, and 10.2.36 is now available that corrects the excessive memory usage by long list of IN parameters that is presumed to be the cause of this error.

Apologies from the MariaDB community.

We wish you a happy upgrade and continued stability.

[BaronVonMEowMEOW]

Apologies from the MariaDB community.

Apologies indeed !!! ugh.

Thank you for finding this.
I'm on mariadb-10.4.16-1

It definitely looked like a memory leak. In my previous thread I mentioned it exceeds configured memory limits.

Absolutely horriffic waste of time. But I guess I did learn a thing or two about debugging queries and performance logging

[BaronVonMEowMEOW]

Keep in mind that starting a new topic is not "reporting back" - maybe you want to call a moderator to move these posts to [url=viewtopic.php?f=556&

Either way, that's fine if they want to move it. I started a new thread to make it more clear what the bug was. I think we know the source of the problem now.

That's horrible SQL: why are both user ID and item parent ID handed over as strings instead of integers? Implicit type conversion unnecessarily asks for trouble

It is bad practice, however it didn't make any difference in the performace of the query. I tested it both ways.

MySQL lets you get away with all sorts of naughty stuff. (and no, it's not an extension. It's part of the core phpBB notifications/email class)

[Frank Rizzo]

Sorry for bumping this thread but I have started receiving the same error this morning.

Last night I upgraded to MariaDB 10.5.9 (from 10.5.8 which itself was supposed to address issues like this) and now I am getting crashes every half hour.

1. This could be a regression error with MariaDB 10.5.9? They put back in the bug that was fixed with 10.5.8?
2. This is happening on my production server (running phpbb 3.3.2, Centos 8). and my development server (running phpbb 3.3.3, Centos 7, MariaDB 10.5.9) .
3. The script that is crashing MariaDB is this:

Code: Select all

DELETE FROM phpbb3_notification_emails
                        WHERE notification_type_id IN (3, 4, 5, 6, 23) AND user_id = '5044' AND item_parent_id IN ('544', '53', '1939', '1938', '81', '1940', '804', '486', '493', '539', '7610')

(truncated, the item_parent_id list contains something like 4642 IN 'xxx')

As this is a production server that is crashing I have tried to mitigate the issue until a fix is found. What I have done is:

* Turned off board Notifications (ACP -> Board features)
* Turned off board-wide emails (ACP -> Email settings)

This seems to have stopped the issue.

[Frank Rizzo]

This is confirmed as an issue with MariaDB 10.5.9

a temporary fix to this is to add / change a setting in my.cnf

set

optimizer_max_sel_arg_weight

to a value less than 16000

10.5.9 crashes with large IN() list