Sitemap runs forever!

I recently updated the sitemap to the newest version and replaced the functions file per this forum and now I am getting a strange result:

Before the update:

Hello!

The vBSEO Google/Yahoo Sitemap has been successfully generated for your vBulletin forums at:
DodgeBoard.com - Forums (http://www.dodgeboard.com/forums)

Report:
============================
Click the following link for your vBSEO Google/Yahoo Sitemap Report:
http://www.dodgeboard.com/forums/vbseo_sitemap/

Summary:
============================
Forum Display: 286
Show Thread: 9554
Show Post: 64427
Member Profiles: 541
Poll Results: 43
Archive: 38

Total Indexed URLs: 74927
Total Processing Time: 103.94 seconds

Google ping: Successful.
Yahoo ping: Successful.
Ask ping: Successful.
Moreover ping: Successful.

After the update:

Hello!

The vBSEO Google/Yahoo Sitemap has been successfully generated for your vBulletin forums at:
DodgeBoard.com - Forums (http://www.dodgeboard.com/forums)

Report:
============================
Click the following link for your vBSEO Google/Yahoo Sitemap Report:
http://www.dodgeboard.com/forums/vbseo_sitemap/

Summary:
============================
Forum Display: 316
Show Thread: 10640
Show Post: 72088
Member Profiles: 599
Poll Results: 47
Archive: 40

Total Indexed URLs: 83772
Total Processing Time: 84,881.43 seconds

Google ping: Successful.
Yahoo ping: Successful.
Ask ping: Successful.
Moreover ping: Successful.
Msn ping: Successful.

What could be causing this? There are 86,000 seconds in a day. Is the script really running all day long?

Vbulletin 3.7.0
vBSEO 3.2.0 RC5
vBSEO Sitemap 2.1 w/vbseo_sitemap_functions.php fix applied
PHP 5
SQL 5

Its a permissions issue perhaps? I had exactly the same a few months ago. Allowing the folder the correct permissions sorted it out.

forums/vseo_sitemap = 755
forums/vseo_sitemap/addons = 755
forums/vseo_sitemap/data = 777
forums/vseo_sitemap/data/hits = 777

odd... none of thew stats match up at all.. not even forum count.

Total Processing Time: 84,881.43 seconds

It means that sitemap generation was interrupted and resumed on next day with the next scheduled task. The total time was counted from "start time" to "finish time", i.e. one day, although it was not running in between.
You should increase max_execution_time setting in PHP config to avoid interruptions.

odd... none of thew stats match up at all.. not even forum count.

Forum pages include forumdisplay pagination and possibly "old URLs included", so perhaps it matches.

I noticed the data folder had over 1200 files in it - so many that they woudln't all display in SmartFTP. Some of the log files were over three years old. I deleted the ones pre-08 and now the directory is displaying properly. Is it possible this had something to do with it?

From today:

Report:
============================
Click the following link for your vBSEO Google/Yahoo Sitemap Report:
http://www.dodgeboard.com/forums/vbseo_sitemap/

Summary:
============================
Forum Display: 316
Show Thread: 10655
Show Post: 72191
Member Profiles: 602
Poll Results: 47
Archive: 40

Total Indexed URLs: 83895
Total Processing Time: 3,425.32 seconds

Google ping: Successful.
Yahoo ping: Successful.
Ask ping: Successful.
Moreover ping: Successful.
Msn ping: Successful.

Still, this script ran for an hour - I'm afraid my host will give me the boot if they see scripts running that long. They pulled the plug on me about a year ago when they thought vBSEO was causing some problems. (Hostgator)

Is it possible this had something to do with it?

That should not be related.
Did you try to generate sitemap manually?

OKay- did that. Took about 1.5 min total.

[homepage] [5,359.6Kb mem used] [0s (+0s)]
[SECTION START] forumdisplay [5,359.9Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 9, total threads: 152, pages: 8 [5,360.0Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 19, total threads: 1682, pages: 85 [5,360.8Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 24, total threads: 12, pages: 1 [5,393.9Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 5, total threads: 1267, pages: 64 [5,395.1Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 8, total threads: 513, pages: 26 [5,420.4Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 12, total threads: 1180, pages: 59 [5,433.1Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 4, total threads: 79, pages: 4 [5,464.7Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 6, total threads: 90, pages: 5 [5,466.9Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 10, total threads: 110, pages: 6 [5,469.4Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 11, total threads: 56, pages: 3 [5,473.5Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 17, total threads: 146, pages: 8 [5,475.4Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 14, total threads: 68, pages: 4 [5,479.3Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 29, total threads: 21, pages: 2 [5,481.9Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 16, total threads: 166, pages: 9 [5,483.4Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 18, total threads: 109, pages: 6 [5,487.3Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 23, total threads: 179, pages: 9 [5,490.6Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 28, total threads: 44, pages: 3 [5,495.1Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 7, total threads: 20, pages: 1 [5,497.1Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 21, total threads: 48, pages: 3 [5,498.2Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 22, total threads: 103, pages: 6 [5,499.4Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 26, total threads: 3, pages: 1 [5,502.3Kb mem used] [0s (+0s)]
[forumdisplay] forum_id: 27, total threads: 58, pages: 3 [5,503.8Kb mem used] [0s (+0s)]
[SECTION START] showthread [5,504.8Kb mem used] [0s (+0s)]
[create sitemap file] filename: sitemap_1.xml.gz, number of urls: 25000 [5,472.7Kb mem used] [8s (+8s)]
[create sitemap in text format] part #2 [5,472.7Kb mem used] [8s (+0s)]
[create sitemap file] filename: sitemap_2.xml.gz, number of urls: 25000 [5,474.9Kb mem used] [60s (+52s)]
[create sitemap in text format] part #3 [5,474.9Kb mem used] [60s (+0s)]
[create sitemap file] filename: sitemap_3.xml.gz, number of urls: 25000 [5,476.0Kb mem used] [110s (+50s)]
[create sitemap in text format] part #4 [5,476.0Kb mem used] [110s (+0s)]
[archive homepage] [5,490.9Kb mem used] [156s (+46s)]
[SECTION START] forumdisplay archived [5,491.8Kb mem used] [156s (+0s)]
[SECTION START] member [5,513.1Kb mem used] [156s (+0s)]
[SECTION START] polls [5,856.7Kb mem used] [156s (+0s)]
[SECTION START] blogs [5,884.8Kb mem used] [156s (+0s)]
[addon module] vbseo_sm_vba.php [5,919.5Kb mem used] [157s (+0s)]
[create sitemap file] filename: sitemap_4.xml.gz, number of urls: 8942 [5,401.5Kb mem used] [157s (+0s)]
[create sitemap in text format] part #5 [5,401.4Kb mem used] [157s (+0s)]
[create sitemap index] filename: sitemap_index.xml.gz, number of sitemaps: 4 [5,401.6Kb mem used] [157s (+0s)]

Report:
============================
Click the following link for your vBSEO Google/Yahoo Sitemap Report:
http://www.dodgeboard.com/forums/vbseo_sitemap/

Summary:
============================
Forum Display: 316
Show Thread: 10662
Show Post: 72231
Member Profiles: 602
Poll Results: 47
Archive: 40

Total Indexed URLs: 83942
Total Processing Time: 160.90 seconds

Google ping: Successful.
Yahoo ping: Successful.
Ask ping: Successful.
Moreover ping: Successful.
Msn ping: Successful.

Looks to me like the problem started around 5-6-08 (see attachment)

Thats shortly after upgrade to vb 3.7.0, vBSEO 3.2 and Sitemap 2.1. Also my host chnaged a setting in PHP to fix some database errors we were getting. They upgraded the PHP and SQL on or around 5-3-08.

My email to Hostgator support:

I have noticed since you updated the SQL and PHP on my box two days ago, I have been receiving occasional errors from vBulletin.

"MySQL server has gone away"

I am noticing a pattern on the times I receive these errors. It appears that the errors occur at precisely 6:33 pm and 2:02 am every day. I have no crons or routines running at that time. Is it possible that the server is running a routine on the SQL at this time? I have presented the issue to vBulletin and they say it is a hosting issue, more specifically, with SQL. Here are the errors:

-------------------------------------------------------------------------
Database error in vBulletin 3.7.0:

Invalid SQL:

SELECT p.dateline,p.postid,p.threadid
FROM post AS p
WHERE p.threadid = 4158
ORDER BY p.dateline;

MySQL Error : MySQL server has gone away
Error Number : 2006
Request Date : Thursday, May 1st 2008 @ 02:02:56 AM
Error Date : Thursday, May 1st 2008 @ 02:05:21 AM
Script : http://www.dodgeboard.com/forums/cron.php?rand=61088
Referrer : http://www.dodgeboard.com/forums/showthread.php?p=70361#post70361
IP Address : 71.218.37.132
Username : Unregistered
Classname : vB_Database
MySQL Version :

Reply from Hostgator:

I don't see any issues with the sql service at those times. Your query returns almost 70,000 rows. Please refer to the following:

http://www.vbulletin.com/forum/showthread.php?t=43505

The timestamps you displayed below make 1 invalid, and we can not increase the limits as suggested in 3. Check with the vbulletin community to see if there may be a patch for this to make sure a close statement is not being issued, or to reduce the result size.

Thank you,

Michael S
Server Administrator
HostGator LLC.

From Me:

It's not just on that script. When this starts occurring it happens on ANY SCRIPT for awhile, then goes away. See attached emails. (EDIT: RESENDING- your mail box rejected because of attached emails- now they are inside this email, at the bottom)

These flooded my email account for three minutes today around 5:17 to 5:19 pm. But now it's been three hours and there have been none. Something is occurring sporadically on the server that's causing this.

vBulletin tells me it's a problem with SQL on my box. I had NEVER received these error emails until Hostgator upgraded SQL/PHP.

Is it possible that when Hostgator upgraded the SQL, that they decreased the amount of SQL connections allowed to SQL or that the defaults were accepted and that they are lower than the previous configuration?

From HostGator:

I have increased the interactive_timeout a bit on the server to prevent these issues. Please let us know if you see these errors again.

Best Regards,
Shashank Wagh,
Systems Administrator & Level III Support,
Hostgator.com LLC.

EDIT: I still get the error "MySQL server has gone away" but not as frequent. I get about two each morning instead of 10-20.

Attachment shows my sitemap generator history which indicates the date that the problems started. Why would it run ok manually?

Just now noticing that I have two scheduled tasks:

vBSEO Sitemap Generator 2:00 am
Generate sitemaps

vBSEO - Create Google/Yahoo Sitemap 4:00 am

Is this normal?

No, you should remove one of them, keep this one: "vBSEO Sitemap Generator".

OK- so I have removed the extra one but I am still having the same problem. Runs for 80,000+ seconds.

Ran manually and got:

[SECTION START] showthread [5,370.1Kb mem used] [168,229s (+80,927s)]
[create sitemap file] filename: sitemap_3.xml.gz, number of urls: 25000 [5,488.8Kb mem used] [168,233s (+4s)]
[create sitemap in text format] part #4 [5,488.7Kb mem used] [168,233s (+0s)]


Database error

The DodgeBoard.com - Forums database has encountered a problem.Please try the following:

• Load the page again by clicking the Refresh button in your web browser.
• Open the www.dodgeboard.com home page, then try to open another page.
• Click the Back button to try another link.

The www.dodgeboard.com forum technical staff have been notified of the error, though you may contact them if the problem persists.
We apologise for any inconvenience.

-------------email--------------------------------------------------------------------------

Database error in vBulletin 3.7.1:

Invalid SQL:

SELECT p.dateline,p.postid,p.threadid
FROM post AS p
WHERE p.threadid = 419
ORDER BY p.dateline;

MySQL Error : MySQL server has gone away
Error Number : 2006
Request Date : Monday, June 2nd 2008 @ 11:44:31 PM
Error Date : Monday, June 2nd 2008 @ 11:45:21 PM
Script : http://www.dodgeboard.com/forums/vbseo_sitemap/vbseo_sitemap.php
Referrer : http://www.dodgeboard.com/forums/vbseo_sitemap/index.php
IP Address : 70.167.xxx.xxx
Username : Unregistered
Classname : vB_Database
MySQL Version :

-----------2nd attempt----------------------------------------------

Ran it a second time and it completed with no errors :

[SECTION START] showthread [5,369.7Kb mem used] [168,393s (+160s)]
[archive homepage] [5,778.7Kb mem used] [168,395s (+1s)]
[SECTION START] forumdisplay archived [5,779.2Kb mem used] [168,395s (+0s)]
[SECTION START] member [5,801.7Kb mem used] [168,395s (+0s)]
[SECTION START] polls [5,590.0Kb mem used] [168,395s (+0s)]
[SECTION START] blogs [5,619.0Kb mem used] [168,395s (+0s)]
[addon module] vbseo_sm_vba.php [5,653.1Kb mem used] [168,395s (+0s)]
[create sitemap file] filename: sitemap_4.xml.gz, number of urls: 10461 [5,410.2Kb mem used] [168,395s (+0s)]
[create sitemap in text format] part #5 [5,410.2Kb mem used] [168,395s (+0s)]
[create sitemap index] filename: sitemap_index.xml.gz, number of sitemaps: 4 [5,410.5Kb mem used] [168,395s (+0s)]

--------------------Email results---------------------------------

Hello!

The vBSEO Google/Yahoo Sitemap has been successfully generated for your vBulletin forums at:
DodgeBoard.com - Forums (http://www.dodgeboard.com/forums)

Report:
============================
Click the following link for your vBSEO Google/Yahoo Sitemap Report:
http://www.dodgeboard.com/forums/vbseo_sitemap/

Summary:
============================
Forum Display: 319
Show Thread: 10849
Show Post: 73549
Member Profiles: 612
Poll Results: 48
Archive: 40

Total Indexed URLs: 85461
Total Processing Time: 168,396.57 seconds

Google ping: Successful.
Yahoo ping: Successful.
Ask ping: Successful.
Moreover ping: Successful.
Msn ping: Successful.
---------------------------------------------------------------

Looks like this one has been trying to complete for a few days.....

Any ideas on how I can cure this?

if you're losing mysql server (sql has gone away error) you're probably maxing out connections/concurrent processes to it.

MySQL :: MySQL 5.0 Reference Manual :: B.1.2.9 MySQL server has gone away


I get about 40 error reports a day on it from my site as well, usually when my rss feeds run.

This problem seems to have cropped up about the time the host upgraded the sql.

Is there anything that can be done to slow the sitemap generation, or to force the script to go easier on the server?

try setting your max URLs per sitemap to a lower figure (will make more files)
and add a pause of 10 secs or so

there's an option for this in the vb options panel for the sitemap