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'm using "Mail", not SMTP

I did a test with 500 and 500 and no change, it take 12 sec to post a simple message.

Thanks for trying to help me :-)
User avatar
Crizzo
Translations & International Support Teams Manager
Translations & International Support Teams Manager
Posts: 1645
Joined: Thu Apr 23, 2009 1:20 pm
Location: Stuttgart, Germany
Name: Christian
Contact:

Re: Slow when posting because of email notification

Post by Crizzo »

Even in an empty new subforum?

Maybe your submit just triggers the cronjob with takes care of the mail sending. But needs further investigation. :|
My extensions for phpBB: CDB
German phpBB Support at www.phpbb.de
TomaGo
Registered User
Posts: 124
Joined: Sat Jan 21, 2017 10:42 am

Re: Slow when posting because of email notification

Post by TomaGo »

In an empty subforum or with just a few (10, 100..) topic posting a message is very fast.
HB
Registered User
Posts: 204
Joined: Mon May 16, 2005 9:30 pm
Contact:

Re: Slow when posting because of email notification

Post by HB »

TomaGo wrote: Mon Oct 11, 2021 10:26 am Yes as i said in my first message it is fast if i disable the boards mails.
Maybe it's your SMTP server that's slowing things down. Here's a short script that sends an email.

Code: Select all

<?php

// standard hack prevent 
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.'.$phpEx); 
include_once($phpbb_root_path . 'includes/functions_messenger.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();

$start_time = microtime(true);

// Send test e-mail
$messenger = new messenger();
$messenger->subject("Test Message");
$messenger->to('[email protected]', 'Admin');
$messenger->template("admin_send_email");
$messenger->assign_vars(array('MESSAGE'	=> "This is a test message. Please delete it."));
$messenger->send();

$elapsed_time = round((microtime(true) - $start_time), 2);

echo "Finished in $elapsed_time seconds";

?>
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, Finished in 0.25 seconds so everything seems good on the mail side !

I did more test and it appear that it is slow mainly (Only?) on the biggest subforum with 17 000 topic and 82 000 messages
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5850
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: Slow when posting because of email notification

Post by thecoalman »

TomaGo wrote: Mon Oct 11, 2021 2:24 pm I did more test and it appear that it is slow mainly (Only?) on the biggest subforum with 17 000 topic and 82 000 messages
You are allowing people to subscribe to forums? Set up test group/user and set the forum permission for "Can subscribe forum" to no.

Edit----- My mistake, try setting registered users group to never for "Can subscribe forum". This is just a test, you can switch it back afterward.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
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 @thecoalman,

I did the test, "Can subscribe forum" set to "Never" on my bigest subforum.

No change, it stil take around 10 sec to post a simple message ... :?

Any other suggestion / idea ?

Thanks for your help.
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 52767
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Slow when posting because of email notification

Post by stevemaury »

I don't think that permission is retroactive. Users are still subscribed to topics they were subscribed to before the Never permission.

Backup the forums_watch and topics_watch tables. Then run this SQL ( Executing SQL Queries in phpMyAdmin ):

Code: Select all

UPDATE phpbb_topics_watch SET notify_status = 0 WHERE notify_status = 1;
UPDATE phpbb_forums_watch SET notify_status = 0 WHERE notify_status = 1;
Then test.

If your table prefix is not phpbb_ change accordingly.
I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
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 @stevemaury, even if this didn't improve the posting speed :roll:

I have even delete everything in the 4 tables :
phpbb_forums_watch
phpbb_topics_watch
phpbb_forums_track
phpbb_topics_track

No improvment, this is crazy !

If i post in a subforum with only a few hundred message it is very fast (lets say 1 second)
If i post in the subforum with 20 000 messages it is very slow (10 to 12 seconds)
If i modify a post in the subforum with 20 000 messages it is very fast

If i disable the mail in ACP / Communication everything if very fast !
User avatar
chanlon1
Registered User
Posts: 228
Joined: Wed Mar 09, 2005 10:01 pm
Location: Belfast, N. Ireland
Contact:

Re: Slow when posting because of email notification

Post by chanlon1 »

Check the number of records/rows you have in the notifications table.
I had a similar problem with slowness on my site and it turned out that the notification records weren’t getting deleted correctly.
When someone was posting it was checking millions of records in the table before letting the post get through.
HB
Registered User
Posts: 204
Joined: Mon May 16, 2005 9:30 pm
Contact:

Re: Slow when posting because of email notification

Post by HB »

I agree with chanlon1. With the caveat that it's offered with no warranty and you should backup your database first, here's some "housecleaning" code that may inspire your inner Marie Kondo. :lol:

Code: Select all

    // Delete old watch list / forum watch list entries
    $table_ary = array(TOPICS_TRACK_TABLE, FORUMS_TRACK_TABLE);
    foreach ($table_ary as $table)
    {
        $sql = "DELETE FROM $table WHERE mark_time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))";
        $db->sql_query($sql);
    }
    
    $sql = "DELETE FROM " . NOTIFICATIONS_TABLE . " WHERE notification_time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))";
    $db->sql_query($sql);
  
    $sql = 'SELECT user_id, username
		FROM ' . USERS_TABLE . '
		WHERE 
        user_regdate < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY)) AND
        user_lastvisit > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR)) AND
		user_lastvisit < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY)) AND
		user_type <> ' . USER_IGNORE . ' ORDER BY user_lastvisit ASC LIMIT 100';
    $result = $db->sql_query($sql);
    $users_data = array();
    while ($row = $db->sql_fetchrow($result)) {
        $users_data[$row['user_id']] = $row;
    }
    $db->sql_freeresult($result);

    $table_ary = array(TOPICS_WATCH_TABLE, FORUMS_WATCH_TABLE, TOPICS_TRACK_TABLE, FORUMS_TRACK_TABLE, BOOKMARKS_TABLE, USER_NOTIFICATIONS_TABLE);
    foreach ($table_ary as $table)
    {
        $sql = "DELETE FROM $table WHERE " . $db->sql_in_set('user_id', array_keys($users_data));
        $db->sql_query($sql);
    }
    
    // Expire subscriptions for threads with no activity for a year.
    $sql = "SELECT w.topic_id FROM " . TOPICS_WATCH_TABLE . " w, " . TOPICS_TABLE . " t WHERE w.topic_id = t.topic_id AND t.topic_last_post_time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))";
    $result = $db->sql_query($sql);
    $tid = array();
    while ($row = $db->sql_fetchrow($result)) {
        $tid[] = $row['topic_id'];
    }
    $db->sql_freeresult($result);

    if (!empty($tid)) {
        $sql = "DELETE FROM " . TOPICS_WATCH_TABLE . " WHERE " . $db->sql_in_set('topic_id', $tid);
        $db->sql_query($sql);
    }
Dan Kehn
User avatar
chanlon1
Registered User
Posts: 228
Joined: Wed Mar 09, 2005 10:01 pm
Location: Belfast, N. Ireland
Contact:

Re: Slow when posting because of email notification

Post by chanlon1 »

@TomaGo

Have read of viewtopic.php?p=15387991#p15387991

This is the thread where I ended up fixing my slowness issue.
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,

Thanks again for your help, but the problem is still there ...

I completely emptied the tables (0 ligne) :

phpbb_bookmarks
phpbb_user_notifications
phpbb_forums_watch
phpbb_forums_track
phpbb_topics_watch
phpbb_topics_track
phpbb_bookmarks
phpbb_user_notifications

On one of my subforum (the biggest with 80 000 messages) it is still very slow to post (10/12 seconds) , on alle the other it is fast (1-2 secondes max).

Where can it come from ? :twisted:
User avatar
david63
Registered User
Posts: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: Slow when posting because of email notification

Post by david63 »

Perhaps a long shot but have you tried optimising the database tables?
David
Remember: You only know what you know and - you don't know what you don't know!

I now no longer support any of my extensions but they will start to become available here
User avatar
chanlon1
Registered User
Posts: 228
Joined: Wed Mar 09, 2005 10:01 pm
Location: Belfast, N. Ireland
Contact:

Re: Slow when posting because of email notification

Post by chanlon1 »

Can you put a link to your board so we can take a look?

Also, if reducing the rows hasn't made any obvious changes, you may want to try looking at the Debug & Debug Extra in the config.php file, and also enabling Slow Queries in mysql.
That may help narrow down what queries may be causing the slowness.
Post Reply

Return to “[3.3.x] Support Forum”