I need help. Apparently my database file is causing excessive loads on my webhost at Host Gator and I don't know how to fix the problem. I have Google Ads running on my forums but this is the email message I received. Anyone know how I could correct this problem? And would increasing the memory on my forums cause this problem as well?

 

Dear customer,

Due to excessive load being caused on our aerostar server by queries to the wolfe426_invision database we've been forced to disable that database at this time as other customers were being affected :

[root@aerostar ~]# mysqladmin proc | grep wolf

| 2429996 | wolfe426 | localhost | wolfe426_invision | Query | 73 | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts` |

| 2430520 | wolfe426_keiichi | localhost | wolfe426_invision | Query | 150 | Waiting for tables | UPDATE ibf_sessions SET member_name='',member_id=0,member_group=2,login_type=0,running_time=11955005 |

| 2430521 | wolfe426_keiichi | localhost | wolfe426_invision | Query | 150 | Waiting for tables | UPDATE ibf_sessions SET member_name='Valdius',member_id=3117,member_group=3,login_type=0,running_tim |

| 2430522 | wolfe426_keiichi | localhost | wolfe426_invision | Query | 150 | Waiting for tables | UPDATE ibf_sessions SET member_name='Hunter Rose',member_id=3024,member_group=3,login_type=0,running |

| 2431035 | wolfe426_keiichi | localhost | wolfe426_invision | Query | 1 | Waiting for tables | DELETE FROM ibf_sessions WHERE ip_address='122.152.128.13' |

| 2431041 | wolfe426_keiichi | localhost | wolfe426_invision | Query | 0 | Opening tables | UPDATE ibf_sessions SET member_name='Hunter Rose',member_id=3024,member_group=3,login_type=0,running |

[root@aerostar ~]# chmod 0000 /var/lib/mysql/wolfe426_invision/

[root@aerostar ~]# cat /var/cpanel/users/wolfe426 | grep "DNS=\|OWNER"

DNS=goddess-project.net

OWNER=wolfe426

I`l move your topic to Server Management, Resources and Optimization :)

| 2429996 | wolfe426 | localhost | wolfe426_invision | Query | 73 | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ * FROM `ibf_posts` |


That's usually called during a backup - was a backup being run on your database at the time?

Yes, actually I was conducting a backup of the forum database file. I've conducted backups before and never received a warning from my webhost because of this. Apparently, the whole reason for this was that there were too many queries being made which caused problems with other clients on the same server with my webhost provider.

Well, if it was running a "select * from ibf_posts" that pulls every post in your database. Appears in this case it was up to 73 seconds when this snapshot was taken. Then, any other query to the posts table would have been locked and placed in queue. If any of those queries joined other tables (i.e. ibf_members) then those other tables would have become locked - it becomes a chain effect.

Backups are best left for non-busy times (late at night/early morning).

Thanks, bfarber. I was actually looking for a way to make my forums work better without excessive load on my webhost. Currently, the server loads being reported from my site has to do with excessive loads that are a result from the database file that runs my forum. I'm curious if there's a workarouond for this.

I've noticed how IPS Beyond seems to have lower Server Loads while still maintaining a larger forum than I have yet I seem to still be having problems with higher server loads. While doing some customizing on my forums, I still haven't found a workaroound on this ... anyone have any suggestions?

I guess IPS Beyond will be on a dedicated server, hence the lower load. If you want lower loads, get a dedicated server or find a host that puts fewer sites on their servers, or runs faster servers. IPB is pretty much as optimized as it will ever be, as far as I'm aware.

Actually, IPS Beyond is on a VPS with both the company forums and the client area. :) We have no real custom modifications on IPS Beyond (used to have a thing on the forum index IPS Beyond skin that showed the download manager categories, but not anymore I think).

Just today I was reminded by something one of the Moderators on my forums mentioned ot me that I didn't realized before. These Server Load problems didn't materialize until after I upgraded to IPB 2.3.3. Has anyone noticed this either? The reason I'm asking is that the server my site rests upon has the following:

 

Processor #1 Vendor: GenuineIntel

Processor #1 Name: Intel® Xeon® CPU 3060 @ 2.40GHz

Processor #1 speed: 2400.122 MHz

Processor #1 cache size: 4096 KB

Processor #2 Vendor: GenuineIntel

Processor #2 Name: Intel® Xeon® CPU 3060 @ 2.40GHz

Processor #2 speed: 2400.122 MHz

Processor #2 cache size: 4096 KB

I've been running on this server for sometime now and it wasn't until after my forums were upgraded from 2.2.2 to 2.3.3 that I started having these problems. I'm starting to think that maybe there was some sort of bug or something ...

Actually, 2.3.3 was mainly a resource release and most customers have stated resource usage has GREATLY improved with 2.3 over 2.2 :blink:

Hmm, this is still bugging me. The current configuration on the server that my website is on is as follows:

 

Processor #1 Vendor: GenuineIntel

Processor #1 Name: Intel® Xeon® CPU 3060 @ 2.40GHz

Processor #1 speed: 2400.122 MHz

Processor #1 cache size: 4096 KB

Processor #2 Vendor: GenuineIntel

Processor #2 Name: Intel® Xeon® CPU 3060 @ 2.40GHz

Processor #2 speed: 2400.122 MHz

Processor #2 cache size: 4096 KB

The server loads, as it's reported in the stats on my forums, ranges from as low as 2.0 up through a load of 37.

37 is quite high, yes. Though, is the site actually slow at the time?

There are dozens of things that can cause issues, so you have to actually pinpoint what the problem is. Is it mysql or apache, or something else (such as email or stats programs)? Are you getting DOS'd? Is there a bad query being run, or maybe a missing database index? Or maybe mysql just needs to be configured properly?

I've experienced something similar to my dedicated machine as well. I managed to fix it however.
I had deans shoutbox installed and it always made the server - or rather mysql hang within a day or so. After i searched through deans forums for a while i found this explanation for it:

 

The problem is caused by join not using indexes:

SELECT s.*,m.* FROM ibf_d22shoutbox_shouts s LEFT JOIN ibf_members m ON ( m.id=s.s_mid ) ORDER BY s.s_date DESC LIMIT 0,10;

Causes examining couple hundreds of rows and without indexes and that's bad. Very bad. Create indexes for s_mid and s_date and it should be fine.

Run this in phpmyadmin - it did the trick for me:

ALTER TABLE ibf_d22shoutbox_shouts ADD INDEX s_mid (s_mid);

ALTER TABLE ibf_d22shoutbox_shouts ADD INDEX s_date (s_date);

That solved the trouble with the shoutbox (if you have it running).

Other than that i discovered that the initial setup done by my host had the emails (qmail) storage folder not within the /home path but in the /var path, which was much smaller. Over time the mailboxes became pretty full (i use imap most of the time and keep a lot of emails). After i moved these over to /home which is much bigger i don't experience any locksup anymore.
So you might want to check weather your problem is caused by full directories or the shoutbox. The sympthom was the same for me: the servers load went up to like 50 and only a reboot helped.

I sort of fixed it but I'm still experiencing problems with somewhat larger server loads. While it tends to remain from 1 to 10 most of the time, I'm still experiencing server load issues jumping above 20. The problem was that I was using MKPortal, a CMS, and the MKP installation integrated into the forums database. I managed to separate the MKP installation from the forum database on my site but it still spikes. There are very few mods on my forums, with the sole exception of the Mod Installer so I don't know why that would affect it.

what kind of www server sw do You use? I had similiar things when run apache2 - when I decided to move to the nginx my loads went from 10 to 1.5 :)
Now I'm very delighted user of nginx - on the same configuration of HW I can service double or even tripple amount of users online and everybody found that site is running faster

turn the boards off wait 5 mins (get a coffee or beer) come back and see what the load is like then in SSH top. If its still high then you are either DOS'd or you need to contact your host as you may be on a oversubscribed server.