I'm not sure which is the most appropriate forum to post this in, so sorry if I've picked the wrong one.

I'm having some problems with my XOOPS site, and am hoping someone here might have some useful suggestions. I'm running XOOPS 2.0.7.3

The site is at http://www.phantomoftheopera.com/ - if it's up when you see this, because it keeps crashing the web host's server and being switched off.

It was pretty much fine until I upgraded from NewBB 1 to NewBB 2, two or three weeks ago (which I had to do in order to have IP addresses displayed to me, to deal with an abusive poster). Then the site became very, very slow, and eventually crashed.

First time I wrote to tech support on it, they said "I would make sure that the new forum module has proper indexing on the tables. If you are doing a lot of queries, esp. on tables that don't have proper indexes things can become very, very slow."

Unfortunately I don't know databases at all so I've no idea how to find out whether it has proper indexing or not (definitely a newbie when it comes to php and MySQL), but it's the same module hundreds of other sites are using.

Today they said:

"From taking a quick look at your site, my guess is that you have too many database hits to the main page. Every time somebody loads your main page it is probably doing a lot of queries. queries for all the news items, the forum threads, the poll, etc."

So I've switched off a few of the less important threads from the main page. The forums are really the main point of the site though, and it doesn't seem as if most people have a problem running XOOPS with most recent forum posts, top 5 news items, and a poll on the front page.

Our traffic has gone up significantly in the last month, from an average of 1500 pages served a day a couple of months ago, to an average of 17146 pages served a day this month - but that doesn't strike me as a particularly huge amount of traffic compared to many sites.

The only problem we've been able to identify (I've been having a more knowledgable friend help out a lot, thank goodness) is that there were a huge number of Private Messages being held in the system. Apparently a lot of people had set the notification to PM them every time there was a post on the forums, back when the site was quiet, then not come back for a couple of months. Some users found 13,000 messages waiting for them. We've asked the web host if they can delete the bulk of the PMs from the database, and have turned off the notification options.

Do you think the massive number of PMs in the database could have been slowing MySQL down? Or do you think we have a problem in the forum module somewhere?

Or do you have any other suggestions or thoughts?

Enable MySQL debug and load the front page, at the bottom of the popup window, you should see how many queries are used to present the front page.

If the number is 50 or higher, I would recommend that you enable a couple of minutes cache on certain blocks, such as the latest forum threads - it should be fine with a minute or 5 minutes without crippling your site's information flow.

The Polls block could perhaps also be cached. However, I don't see an enormous amount of blocks and information on the front page that can explain a big strain on the server.

Try getting your host to quantify their comments. What is a "proper index"? How many are "too many database hits to the main page"?

I think you should get less strain on the server by deactivating notifications and emptying the pm table (it can be done with a manual SQL query: TRUNCATE TABLE prefix_priv_msgs - replace "prefix" with your own database prefix) since if you have say 500 users all wanting to be notified when a new post is made, you can easily get 1000+ queries every time that happens. That would be tough on the server. Emptying the PM table will probably not do all that much, but can't hurt. If you do an "OPTIMIZE TABLE prefix_priv_msgs" after clearing it out, it should be in better shape to cope with PM's afterwards (after a massive deletion of entries, the database can get a little crippled when inserting in that table again)

Basically, I would say you are on the right track, without being able to expand much more on it - but more information from your host would be good.

Thanks ever so much. They are running the queries you suggested, and we've enabled various caches.

I'll also do the debug, as soon as the site is actually working again - all was fine for three or four hours, and then I just got this message:

This page cannot be displayed due to an internal error.

If you are the administrator of this site, please visit the XOOPS Troubleshooting Page for assistance.

Error [Xoops]: Unable to connect to database in file class/database/databasefactory.php line 34


I've reported this to them, but obviously they're not the XOOPS experts. Does this line give you any clues as to what's going wrong?

If you didn´t do anything to mainfile.php then its most likely a problem with their MySQL server.

Can you access your database with control panel or phpmyadmin while the site gives that message?

had same issue, windows hosts complained that my site had almost 150 instances of php.exe running and was cause the server to crash. Moved to linux and had the same issues but they said that my site was bogging down the mySQL server, so i enabled caching on the blocks and modules (especially the xmmemberstats whos online block ITS A SQL HOG!!) and everything was ok no complaints or issues, site is actually alot quicker!

The site has apparently crashed the server again, as I'm now getting the 403 Forbidden error again, so apparently the SQL queries didn't fix things.

I'm told by users that it was running smoothly for 12 or 15 hours (whatever the XOOPS error last night was must have sorted itself out somehow, as no one did anything to it), but not while I was online, so I haven't been able to try the debug. I'm unable to use the control panel or access the account by FTP at the moment.

We did enable caching on the blocks a couple of days ago, but obviously it hasn't made a lot of difference!

I'm using phpwebhosting.com, who have Linux servers. Friend and I have had a number of sites hosted with them for a couple of years now, and in the past we've only had minor problems, with quick tech support response.

Although I haven't been able to run the debug, a friend was able to run it a few days ago, when the problems first became apparent, so I'm pasting what he said below. However, these were run before we enabled the caches.

"The queries execution varies from 5 to 13 seconds! And I think it should be less or equal to 1 sec even on a shared hosting environment and with all those users that are browsing.

"I just checked on a test site that I have - 64 queries plus 11 blocks (2 cached) takes about 0.5 - 0.6 to execute the queries. phantomoftheopera.com has almost the same number of queries/blocks."

119 queries with 14 blocks in .87 seconds. on shared server with quad 2.8GHz Xeons and 4GB Ram with hostrocket.com

Well, I'm getting some trouble with this right now... I've recently upgraded my XOOPS software from 2.0.7 to 2.0.93. Now my website admin tells me my site has been slowing down the MySQL server a lot (a Apache/2.0.53 (Debian GNU/Linux) PHP/4.3.10-10 mod_ssl/2.0.53 OpenSSL/0.9.7e Server) and he's forced to turn it off temporarily.

I don't remember turning the cache on, so I'll try that (as soon as I convince him to turn the site back on so I can at least get to the admin functions to turn it off myself :) )

Is it possible that this is a worm issue or sth? I'd like to believe my site is experiencing such an influx of guests so as to create that many queries...

OK I've uploaded a plugin and it says the load times are between 0.3-0.9 secs and the no. of queries is between 50 - 180, is that acceptable?

i have a lot of my blocks cached, but when I run MySQL debug, it shows that nothing is cached. What could be the cause of this?