Slow when posting because of email notification

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

I have finaly removed this from phpbb/notification/method/email.php it solve the probleme but what could be the impact ?

Code: Select all

	/**
	* {@inheritdoc}
	*/
	public function mark_notifications_by_parent($notification_type_id, $item_parent_id, $user_id, $time = false, $mark_read = true)
	{
		$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', $item_parent_id, false, true) : '');
		$this->db->sql_query($sql);
	}
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

Hello,

Up

What could be the impact of removing this from phpbb/notification/method/email.php :

Code: Select all

	/**
	* {@inheritdoc}
	*/
	public function mark_notifications_by_parent($notification_type_id, $item_parent_id, $user_id, $time = false, $mark_read = true)
	{
		$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', $item_parent_id, false, true) : '');
		$this->db->sql_query($sql);
	}
Thanks !
HB
Registered User
Posts: 206
Joined: Mon May 16, 2005 9:30 pm
Contact:

Re: Slow when posting because of email notification

Post by HB »

TomaGo wrote: Sat Oct 30, 2021 8:50 amWhat could be the impact of removing this from phpbb/notification/method/email.php...
Presumably the table phpbb3_notification_emails would grow without bounds. How many rows does your table have now?

I haven't looked closely, but I can see how the code above could result in a costly query if it's a really huge table. This is because the only index is for (notification_type_id, item_id, item_parent_id, user_id). The best solution is (a) keeping the table size reasonable with pruning, (b) recode requests to avoid large incoming array parameters that are turned into an enormous WHERE item_parent_id IN (...) clause.
Dan Kehn
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

Thanks,

Today my table phpbb3_notification_emails is small, 354 lines and 32Ko, i'll check it sometimes.

a) What could be the good pruning sql command ?

b) Do you think you could help me to recode to limit the IN ?

Thomas
HB
Registered User
Posts: 206
Joined: Mon May 16, 2005 9:30 pm
Contact:

Re: Slow when posting because of email notification

Post by HB »

There's some guesswork going on here, we haven't confirmed the cause is an enormous IN clause. If it's the same cause that I noted here, then adding the limiting AND show below under the comment // OH MY! will do it. See markread(...) in includes/functions.php:

Code: Select all

	else if ($mode == 'topics')
	{
		// Mark all topics in forums read
		if (!is_array($forum_id))
		{
			$forum_id = array($forum_id);
		}
		else
		{
			$forum_id = array_unique($forum_id);
		}

		/* @var $phpbb_notifications \phpbb\notification\manager */
		$phpbb_notifications = $phpbb_container->get('notification_manager');

		$phpbb_notifications->mark_notifications_by_parent(array(
			'notification.type.topic',
			'notification.type.approve_topic',
		), $forum_id, $user->data['user_id'], $post_time);

		// Mark all post/quote notifications read for this user in this forum
		$topic_ids = array();
		$sql = 'SELECT topic_id
			FROM ' . TOPICS_TABLE . '
			WHERE ' . $db->sql_in_set('forum_id', $forum_id);
		
		// OH MY! Not every single topic, this can get really long.
		$sql .= " AND topic_last_post_time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))";
		
		$result = $db->sql_query($sql);
		while ($row = $db->sql_fetchrow($result))
		{
			$topic_ids[] = $row['topic_id'];
		}
		$db->sql_freeresult($result);

		$phpbb_notifications->mark_notifications_by_parent(array(
			'notification.type.quote',
			'notification.type.bookmark',
			'notification.type.post',
			'notification.type.approve_post',
			'notification.type.forum',
		), $topic_ids, $user->data['user_id'], $post_time);
This cuts off the "look back" interval for marking read to topics having a reply in the last year, which seems more than reasonable to me.
Last edited by HB on Tue Nov 09, 2021 12:14 pm, edited 1 time in total.
Dan Kehn
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

Thank you HB, i'll test it and keep you informed.
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

Hello HB,

I did a few tests today and adding your code bellow to the function.php didn't improve the posting time in my big subforum https://www.ma-bmw.com/forum/viewforum.php?f=2

Code: Select all

$sql .= " AND topic_last_post_time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))";
The only "solution" to greatly improve the posting time is to remove the code bellow from phpbb/notification/method/email.php
But the all the nofications are disabled on all the forum...

Code: Select all

	/**
	* {@inheritdoc}
	*/
	public function mark_notifications_by_parent($notification_type_id, $item_parent_id, $user_id, $time = false, $mark_read = true)
	{
		$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', $item_parent_id, false, true) : '');
		$this->db->sql_query($sql);
	}
Any other ideas of help is greatly appreciated ;)
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

Update :

HB, with your modification bellow the posting time is reduced from 12 to 6 seconds ;) So i think there is another improvment to do. Do you have an idea ?

Code: Select all

$sql .= " AND topic_last_post_time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))";
Changing 1 year to 1 day didn't change anything.

Thanks !
HB
Registered User
Posts: 206
Joined: Mon May 16, 2005 9:30 pm
Contact:

Re: Slow when posting because of email notification

Post by HB »

Narrowing down specifically which SQL statement (or code) is slow would be helpful. I would try enabling SQL debugging; see sql_explain in How to enable debug mode for phpBB 3.2+.
Dan Kehn
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

Here is the second sql query that is very low. If you can optimise this one as you did for the other one my forum will be fast ;)

Code: Select all

# Time: 2021-11-18T18:36:03.986010Z
# User@Host: test[test] @ localhost []  Id:   251
# Query_time: 2.003654  Lock_time: 0.010087 Rows_sent: 0  Rows_examined: 0
SET timestamp=1637260563;
DELETE FROM phpbb_notification_emails
			WHERE notification_type_id = 27 AND user_id = '2' AND item_parent_id IN ('43', '45', '13', '41', '62', '71', '88', '18', '64', '105', '101', '17', '103', '58', '33', '121', '123', '87', '28', '26', '40', '57', '139', '131', '24', '93', '146', '149', '150', '134', '141', '156', '160', '151', '152', '59', '22', '171', '192', '203', '189', '95', '222', '223', '228', '230', '229', '235', '247', '248', '252', '239', '27', '265', '50', '255', '277', '292', '287', '299', '312', '326', '316', '311', '236', '339', '355', '360', '338', '356', '372', '395', '217', '399', '401', '420', '438', '442', '458', '385', '462', '453', '486', '493', '478', '490', '511', '524', '532', '535', '503', '547', '558', '551', '560', '556', '386', '590', '592', '250', '595', '605', '599', '184', '92', '642', '650', '655', '659', '671', '683', '373', '690', '693', '701', '703', '722', '67', '712', '717', '674', '742', '746', '759', '774', '771', '31', '809', '826', '526', '841', '850', '857', '895', '883', '871', '874', '879', '906', '922', '925', '927', '913', '939', '948', '942', '969', '1002', '1016', '1063', '60', '1089', '1092', '1087', '1112', '1119', '1134', '1136', '1171', '1142', '1186', '1185', '1077', '1201', '1005', '1223', '1215', '1284', '1203', '1295', '16', '1312', '1327', '1345', '1350', '1309', '1372', '1379', '1383', '1385', '1325', '1402', '1413', '1408', '1424', '1419', '1430', '1432', '1449', '1454', '1448', '1456', '1457', '1458', '1471', '1455', '733', '1495', '1503', '1491', '1524', '1522', '1530', '1453', '1542', '1541', '35', '1446', '1558', '1520', '1568', '1571', '1574', '1584', '1576', '1599', '1505', '1613', '1624', '1476', '1629', '1659', '1665', '1671', '1647', '1638', '1696', '1720', '1716', '1711', '1725', '1743', '1749', '1746', '1761', '1763', '1780', '1724', '1793', '1715', '1804', '1810', '1808', '1817', '1815', '1841', '1837', '1843', '1845', '1849', '1836', '1866', '1862', '1877', '1873', '1892', '1922', '1934', '1947', '1940', '1953', '1960', '1982', '1983', '1988', '1943', '1991', '1939', '1994', '1992', '2008', '2003', '2027', '2038', '2022', '1957', '2047', '2064', '2068', '2073', '2076', '2077', '2097', '2020', '2103', '2108', '2114', '2081', '2116', '2128', '2132', '2124', '2121', '1701', '2144', '2152', '2165', '2207', '2164', '2181', '2155', '2161', '2174', '2217', '2200', '2096', '2247', '2234', '2258', '2222', '2261', '2244', '2277', '2270', '2275', '2287', '2284', '2294', '2271', '2302', '2305', '2310', '2318', '2320', '2326', '2334', '2322', '2333', '2252', '2354', '2370', '2374', '2381', '2394', '1900', '2402', '7', '2242', '2416', '2433', '2437', '2439', '2466', '2440', '2471', '2459', '2473', '1569', '2469', '2481', '2480', '2485', '2493', '2494', '2509', '2528', '2506', '2500', '2530', '2558', '2561', '2520', '2566', '2576', '2583', '2593', '2554', '2598', '2603', '2582', '643', '2649', '2651', '2340', '2656', '2642', '2671', '2669', '2710', '2187', '2748', '2660', '2764', '2765', '.......................
HB
Registered User
Posts: 206
Joined: Mon May 16, 2005 9:30 pm
Contact:

Re: Slow when posting because of email notification

Post by HB »

The notification ID "27" is notification.type.topic, so it should already be covered by the prior query. Perhaps it's a different code path? You can create a backtrace with something like this (not tested):

Code: Select all

global $phpbb_root_path;
$error_string = (new Exception)->getTraceAsString();
error_log($error_string . PHP_EOL, 3, "${phpbb_root_path}cache/backtrace.log");
The reason the query above is taking so long is (a) there's a lot of item_parent_id's in the IN clause and (b) there's no index for the tuple notification_type_id / user_id / item_parent_id, so the database has to scan each row. You can optimize by either reducing (a) or creating an index for (b) as shown below:

Code: Select all

ALTER TABLE `phpbb3_notification_emails` ADD INDEX `notification_user_item_parent_idx` (`notification_type_id`, `user_id`, `item_parent_id`);
Personally, I prefer to avoid calling the database with a ton of parameters (option a) rather than trying to "help" the database by creating indices (option b). Since this is originating in phpBB as I documented earlier, it really should be reported as a bug.
Dan Kehn
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

Hello all,

I'm back with my issue "Slow when posting" on one of my bug Sub Forum. 12/15 seconds to post a message, while it is 1 or 2 seconds on the other smaller Sub Forums.

https://www.ma-bmw.com/forum/viewforum.php?f=2

Since Phpbb 3.3.7 adding this to functions.php did not improve the posting time :
$sql .= " AND topic_last_post_time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))";
Could you please help me again ? This subforum is not usuable today :-(

Thanks !

Regards
HB
Registered User
Posts: 206
Joined: Mon May 16, 2005 9:30 pm
Contact:

Re: Slow when posting because of email notification

Post by HB »

I don't think anything related to this changed in v3.3.7.

Have you looked at the phpbb3_notification_emails table to see how many rows it has? As I noted earlier, the mark_notifications_by_parent is very inefficient if the forum has many topics and your "Présentez-vous" forum has 17847 of them. All your other forums have less than a third of that, which may explain why you only see it for that particular forum.

As I suggested earlier, enabling SQL debugging would help eliminate the guesswork. Or, you could assume this "Présentez-vous" forum is an isolated case and prune the older topics. If you prefer a less blunt approach, since that forum is for user introductions, you could prune any topic in that forum started by someone who hasn't logged in for a year or two. Since the topics date back to 2009, there's a good chance many of the topic starters are no longer active. Another option is adding code to disable notifications just for that forum (but that's a hack).

On a related note, I think this points to a phpBB bug. The phpbb3_notification_emails table should be pruned in some reasonable manner, i.e., it makes no sense to keep an entry in that table forever if the associated user hasn't logged in for ages. I modified the code to add a notification_time column in the table that's initialized to the time the entry is added. I have a background cron job remove any entries older than 6 months. I figure if someone hasn't logged in to check notifications in that long, they aren't an active member and won't care either way.
Dan Kehn
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

Thank you HB for your help, even after a few months. I appreciate. :)

My phpbb_notification_emails table is small (already pruned a few months ago). 750 lines, 96 ko.

Maybe you are right the best solution could be to prune all the old subjects in this subforum "Présentez-vous".

I'll try tomorrow on a local copy and keep you informed. If it's not enough, i'll enable SQL debugging and post the result here.

I'll keep you informed.

Thanks again !
User avatar
HiFiKabin
Community Team Member
Community Team Member
Posts: 6673
Joined: Wed May 14, 2014 9:10 am
Location: Swearing at the PC, UK
Name: James
Contact:

Re: Slow when posting because of email notification

Post by HiFiKabin »

This can not be a common issue with phpBB otherwise there would be more people reporting a similar problem.

For what its worth I have three suggestions:-

1) replace all of your board files with a fresh downloaded set and run the updater (exactly the same way that you would if you were updating your board Knowledge Base - Updating 3.3.x)

2) Switch to SMTP to send the mail and see if that helps

3) Get your hosts to fix the error and/or find a new (and better) host.
Post Reply

Return to “[3.3.x] Support Forum”