Montego Scripts: Forums
 

 

View next topic
View previous topic
Post new topic   Reply to topic
Author Message
Slackervaara
Newbie
Newbie



Joined: Nov 03, 2007
Posts: 127

PostPosted: Fri Sep 10, 2010 1:41 pm Reply with quote

HTML Newsletter have functioned without problems now for a couple of years for PHP-Nuke 7.6, but the last weeks I have started to get problems. After hitting send test newsletter I get blank page - but sometimes I get the test newsletter, but not all times.

My site is pretty big with 249000 forumposts and over 120 active topics each week that is going to be included in the newsletter.

I have noted that if I diminish the forum topics from 120 to 100 I can send the newsletter, but I still get blank page.

Today I also got this error message:

ENCOUNTERED ERROR IN SQL: MySQL client ran out of memory

SQL = SELECT * FROM `nuke_bbposts`

ERROR IN MODULE: HTML_Newsletter

ERROR MSG: Unable to retrieve Statistics for number of forum posts

I had to skip statistics to send out the newsletter.


What I suspect is that my site seems to have too many forumposts that may create problems on sending the newsletter.

Is it possible to make the SQL queries more efficient or after the queries make a short pause - sleeper , so the PHP script will work better?
 
View user's profile Send private message
Guardian
Site Admin
Site Admin



Joined: Jul 18, 2005
Posts: 401

PostPosted: Sat Sep 11, 2010 1:14 pm Reply with quote

There isn't much you can do other than reduce the number of forum posts you are trying to fetch. It could be a limitation with the servers ODBC driver, server memory or simply the inability of the server to handle the required queries if it is being used in a 'shared' hosting environment.
The nuke_bbposts table actually tracks post id's and topic id's and forum id's so I think you would need to fetch the entire table in order for it to then calculate which forums are are the 'latest' ones.

It is possible to 'cache' the result of the query but you still need to run the initial query so that isn't going to help you too much.
You may have to resort to either retrieving fewer forum posts and sending your newsletter more often to compensate or consider a VPS or dedicated server.

mySQL itself can handle that number of queries with ease (I'm basing that on some work I did recently for a financial institution in Slovakia where we were running through hundreds of thousands of users), so I'm suspecting this is more of a hardware or server configuration limitation to be honest.
 
View user's profile Send private message
Slackervaara
Newbie
Newbie



Joined: Nov 03, 2007
Posts: 127

PostPosted: Sat Sep 11, 2010 1:57 pm Reply with quote

Thanks! I have further investigated this problem upon send and it seems to be caused by this code of admin_make_nls.php line 215 that calculates the total amounts of forum posts:

Code:
//Total Amount of forum Posts

    $sql                                        = "SELECT * FROM `".$prefix."_bbposts`";
    $result9                                 = msnl_fSQLCall( $sql );


I run this SQL query in phpMyAdmin on the database and it took 0,0531 seconds

If I remove this query from admin_make_nls.php the problem will probably be gone, but maybe this query can be made more efficient?
 
View user's profile Send private message
montego
Site Admin/Owner
Site Admin/Owner



Joined: Feb 12, 2005
Posts: 1393

PostPosted: Sat Sep 11, 2010 2:28 pm Reply with quote

Actually, for just getting counts, yes, this code can be much more efficient. I'll try and get to looking for replacement code this weekend. I'm not sure right now whether that query is used elsewhere or not... hang tight.

_________________
“To err is human, but when the eraser wears out ahead of the pencil, you’re overdoing it.”
-- Josh Jenkins 
View user's profile Send private message Visit poster's website
montego
Site Admin/Owner
Site Admin/Owner



Joined: Feb 12, 2005
Posts: 1393

PostPosted: Sat Sep 11, 2010 3:37 pm Reply with quote

Slack,

Just looked at it quickly. For each of the stat counters, you could try something like this. Here is the original code for the member count:

Code:

  /*
   * Total Members
   */
  $sql = 'SELECT `user_id` FROM `' . $user_prefix . '_users` WHERE `username` <> \'Anonymous\'';
  if (!$result = msnl_fSQLCall($sql)) {
    msnl_fRaiseAppError(_MSNL_ADM_MAKE_ERR_DBGETSTATSUSR);
  } else {
    $msnl_iStatsTotUsr = $db->sql_numrows($result);
  }


Could be changed to this:

Code:

   /*
    * Total Members
    */
   $sql = 'SELECT COUNT(`user_id`) FROM `' . $user_prefix . '_users` WHERE `username` <> \'Anonymous\'';
   if (!$result = msnl_fSQLCall($sql)) {
      msnl_fRaiseAppError(_MSNL_ADM_MAKE_ERR_DBGETSTATSUSR);
   } else {
      list($msnl_iStatsTotUsr) = $db->sql_fetchrow($result);
   }


Do something like this for each one of these (especially the forum posts). Also, make sure you optimize your DB often so that the database statistics are kept up-to-date with all your activity.

Let me know how this goes? Thx.
 
View user's profile Send private message Visit poster's website
Slackervaara
Newbie
Newbie



Joined: Nov 03, 2007
Posts: 127

PostPosted: Sun Sep 12, 2010 2:56 am Reply with quote

Many thanks!

I have tried this and it worked very well and the code above for total members did also work:
Code:
//Total Amount of forum Posts

    $sql                                        = "SELECT COUNT(*) FROM `".$prefix."_bbposts`";
    if (!$result = msnl_fSQLCall($sql)) {
      msnl_fRaiseAppError(_MSNL_ADM_MAKE_ERR_DBGETSTATSUSR);
   } else {
      list($msnl_iStatsTotPosts) = $db->sql_fetchrow($result);
   }
 
View user's profile Send private message
montego
Site Admin/Owner
Site Admin/Owner



Joined: Feb 12, 2005
Posts: 1393

PostPosted: Sun Sep 12, 2010 11:19 am Reply with quote

I'll add these changes (for all the counts) to my 1.4 issue list. Thanks Slack for verifying the change.

_________________
“To err is human, but when the eraser wears out ahead of the pencil, you’re overdoing it.”
-- Josh Jenkins 
View user's profile Send private message Visit poster's website
Display posts from previous:       
Post new topic   Reply to topic

View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001-2007 phpBB Group
All times are GMT - 4 Hours
 
Forums ©
linear-bunch
linear-bunch
linear-bunch