Excessive MySQL memory usage on email::mark_notifications_by_parent()

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
BaronVonMEowMEOW
Registered User
Posts: 5
Joined: Thu Nov 19, 2020 8:06 am

Excessive MySQL memory usage on email::mark_notifications_by_parent()

Post by 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
Registered User
Posts: 5
Joined: Thu Nov 19, 2020 8:06 am

Re: Excessive MySQL memory usage on email::mark_notifications_by_parent()

Post by 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);
		}
	}
User avatar
AmigoJack
Registered User
Posts: 5795
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Excessive MySQL memory usage on email::mark_notifications_by_parent()

Post by AmigoJack »

BaronVonMEowMEOW wrote:
Sun Nov 22, 2020 7:52 am
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?
BaronVonMEowMEOW wrote:
Sun Nov 22, 2020 7:52 am
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.
BaronVonMEowMEOW wrote:
Sun Nov 22, 2020 7:52 am

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.
  • The worst thing about censorship is ███████████
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
User avatar
P_I
Registered User
Posts: 1323
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Western Canada
Contact:

Re: Excessive MySQL memory usage on email::mark_notifications_by_parent()

Post by P_I »

AmigoJack wrote:
Sun Nov 22, 2020 10:44 am
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)
danielgblack wrote:
Wed Nov 11, 2020 9:45 pm
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.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
BaronVonMEowMEOW
Registered User
Posts: 5
Joined: Thu Nov 19, 2020 8:06 am

Re: Excessive MySQL memory usage on email::mark_notifications_by_parent()

Post by BaronVonMEowMEOW »

P_I wrote:
Sun Nov 22, 2020 2:52 pm
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
Registered User
Posts: 5
Joined: Thu Nov 19, 2020 8:06 am

Re: Excessive MySQL memory usage on email::mark_notifications_by_parent()

Post by BaronVonMEowMEOW »

AmigoJack wrote:
Sun Nov 22, 2020 10:44 am
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.
AmigoJack wrote:
Sun Nov 22, 2020 10:44 am
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)
Post Reply

Return to “[3.3.x] Support Forum”