Too Much Database Errors

[ljianyih]

Hi,

I've a standard dedicated server under Hostgator and hosting just 1 website or vbulletin forum which is gamesdreams.com (a medium size website with around 10 million monthly pageviews).

Hostgator support team has tried and helped me to upgrade server software and tweak the server configuration over the past few months, but gamesdreams.com is still encountering lots of database errors everyday with frequent downtime.

Can vbulletin experts suggest any major php processes or mysql database connection optimization which can solve the sql over-usage problems that lead to database errors?

I'm needing expert help in this because there are simply too much of database errors sent to my mail everyday with slow website loading speed and frequent downtime from database errors. Any help which can help in solving the sql database problems will be greatly appreciated.

--------------- Added [DATE]1368474287[/DATE] at [TIME]1368474287[/TIME] ---------------

There are all sorts of database errors and here is an example (almost all errors are from showthread.php as the pages viewed are mostly the forum thread):

Database error in vBulletin 4.2.1:

Invalid SQL:

                                SELECT *
                                FROM style
                                WHERE (styleid = 1 AND userselect = 1)
                                        OR styleid = 1
                                ORDER BY styleid ASC
                                LIMIT 1;

MySQL Error   : MySQL server has gone away
Error Number  : 2006
Request Date  : Monday, May 13th 2013 @ 01:01:41 PM
Error Date    : Monday, May 13th 2013 @ 01:02:25 PM
Script        : http://gamesdreams.com/showthread.php?144763-What-s-the-Saying-Level-97-Answer-Cheat
Referrer      : http://gamesdreams.com/showthread.php?139496-What-s-the-Saying-Answers-Cheats-All-Levels
IP Address    : 66.87.120.156
Username      : Unregistered
Classname     : vB_Database_MySQLi
MySQL Version :

One of the main error sent is the style query as shown above. Since vbulletin css is loaded from php files with sql database queries, is it possible to load all the css from external file instead of loading from database to save database resources?

[nerbert]

Look at this setting:

Settings > Options > Style & Language Settings > Store CSS Stylesheets as Files?

I checked, and although I haven't used ths feature, there is a blank directory "vbulletin_css" already in clientscript. Try it out and see what happens, the worst that can happen is you will crash the Internet and bring civilization to and end!

[snakes1100]

It doesnt matter if they are stored on the file system or the DB, its still going to run the query for the style info.

The main issue is the mysql settings & the error:
MySQL Error : MySQL server has gone away

Edit my.cnf & change your timeout, then restart sql

wait_timeout = 600

If the issue persists, raise it to a higher number & restart sql again.

Hostgator support couldnt optimize a server to save their lives.

[ljianyih]

Yup, the css are already stored as files.

The problem that leads to the database errors and slow loading time of gamesdreams.com is basically the piles up of php and mysql processes. There are too much processes to be handled and the piles up of database processes eventually consume all the server memory and leads to mysql server gone away and server downtime problem (often when vbulletin errors appear, the forum pages are encountering very slow loading speed, and after more than 1 minute of waiting time, the errors appear leading to chain effect affecting all other pages with errors for a period of time).

I've contacted hostgator experts to tweak lots of the server setting, but the problem still persist as the mysql has limited memory and resources to handle high number of php or mysql processes.

Is there any major changes or sql optimization I can make on my GamesDreams vbulletin forum so that the database queries will be reduced? The error mentioned is just one of several different types of errors, and max_user_connection limit is another error type with many others (Greatly increasing the limit caused even more errors after the test out). All these errors are mainly caused by the piles up of processes, memory being consumed, server speed greatly slowed down, page cannot load with database errors, and finally leading to website downtime.

GamesDreams is just a medium size website and I believe a standard dedicated server hosting only this vbulletin forum should be able to support it nicely. Even with a much better dedicated server, I'm wondering how huge vbulletin forum like digitalpoint get loaded so smoothly (as GamesDreams is encountering hundreds and thousands of mysql database errors with frequent downtime even it has just moderate traffic).

[daveaite]

Okay try this.

Change your style, build a new one off the default vBulletin style.

Next set the new style as the only style users can choose. If its' the style thats' creating all these database issues, then changing it for good, should remove them.

I used to use the TF Theme for my vBulletin gaming site: http://buypoe.com/forum. At the time, I thought it was a great theme. However, upon switching that theme, I noticed massive changes in loading time - for better.

[CAG CheechDogg]

WOW !

Your forums front page has so much going on no wonder you have so many database errors.

First of all you have 17 redirects on your forums, you have google adsense, you have facebook crawling your site like crazy, you have avatars set to display for your latest forum posts and threads and no images have image dimensions.

I was getting tons of database errors just a few months ago and I eliminated every single database error by blocking facebook spiders and bots from crawling my site.

I also removed the avatars from every single post and thread in the forum sideblocks. I added image dimensions to every single image on the forums especially the forum home and removed the google +1 and facebook like buttons from my forums.

As soon as I removed the aforementioned "buggers" my forums had "ZERO" database errors.

If you need help you can send me a pm and I will show you what to do. It "WILL" take some time but in the end your forums will be running database error free.

I also have hostgator and they tried to give me the run around and tried to convince me to pay for a dedicated server, if you don't have one don't do it, if you have one downgrade to the business plan.

[Lynne]

You may want to post in the Server Configuration forum on vbulletin.com and have George help optimize your server configuration.

[snakes1100]

Originally Posted by ljianyih

Yup, the css are already stored as files.

The problem that leads to the database errors and slow loading time of gamesdreams.com is basically the piles up of php and mysql processes. There are too much processes to be handled and the piles up of database processes eventually consume all the server memory and leads to mysql server gone away and server downtime problem (often when vbulletin errors appear, the forum pages are encountering very slow loading speed, and after more than 1 minute of waiting time, the errors appear leading to chain effect affecting all other pages with errors for a period of time).

I've contacted hostgator experts to tweak lots of the server setting, but the problem still persist as the mysql has limited memory and resources to handle high number of php or mysql processes.

Is there any major changes or sql optimization I can make on my GamesDreams vbulletin forum so that the database queries will be reduced? The error mentioned is just one of several different types of errors, and max_user_connection limit is another error type with many others (Greatly increasing the limit caused even more errors after the test out). All these errors are mainly caused by the piles up of processes, memory being consumed, server speed greatly slowed down, page cannot load with database errors, and finally leading to website downtime.

GamesDreams is just a medium size website and I believe a standard dedicated server hosting only this vbulletin forum should be able to support it nicely. Even with a much better dedicated server, I'm wondering how huge vbulletin forum like digitalpoint get loaded so smoothly (as GamesDreams is encountering hundreds and thousands of mysql database errors with frequent downtime even it has just moderate traffic).

As i stated earlier, hostgator couldnt optimize a server if theyre lives depended on it.

Unfortunately no, there isnt any major changes you can simply make to SQL to make it perform the way you want it to, the entire server needs to be checked to see whats going on.

As we have no details on your server, its hardware or any other details about the server, its not a simple thing to diagnose from here.

[friendlymela]

..............