The Librarian
09-18-2007, 12:36 PM
Here are my stats:
Forum:
~1300 members
~ 600 active
~40-50 concurrent - most ever ~90
I've been running ibProArcade with 32 games installed.
Current Server situation:
Hosted by: HostGator
Level: Baby
Subdomains 6 / unlimited
Parked Domains 0 / 999
Addon Domains 0 / 999
MySQL Databases 1 / unlimited ---there are actually two but they've suspended the one I was using for my active forum
Disk Space Usage 342.83 Megabytes
MySQL Disk Space 364.02 Megabytes
Disk space available 4657.17 Megabytes
Bandwidth (this month) 11046.36 Megabytes
Email Accounts 4 / unlimited
Email Forwarders 4
Current problem: (this is long, but I want you to have the full picture so you can give good advice)
On 7/30 I upgraded to vBulletin 3.6.8 and had HostGator move me to a mySQL5 server.
On 8/1 I got this error message:
mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
/forum/includes/class_core.php on line 274
I started getting one or two of these a day so every day I would go into the AdminCP and optimize. That seemed to help until 8/29 when it crashed my site completely.
I contacted HG and the site came back up. After that I started running the optimize through phpMYAdmin in cPanel.
Then two days later my site went down again and this time didn't come up for a while. HG finally replied with this:
We were doing some maintenance on PHP on the server. We had to compile in some changes but this is the entire reason the site has been up and down for the past bit of time. If you have any further issues please let us know. We apologize for the downtime.
On 8/6 the site went down *again* and I got this notice when I contacted them:
Hostgator implies a 30 concurrent connections per mysql user limit on script to prevent heavy mysql load on the server and to hunt down scripts that use unnecessary mysql connections due to bad coding practices.
Most connections to your database should drop very quickly so this error usually indicates that your site is receiving large amounts of traffic, or your scripts are not configured properly.
These errors can be prevented by making some coding tweaks into your code.
1. Use of mysql_connect() for connections to mysql dbs in your code. The advantage is :
"The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close()."
A detailed explanation can be found at :
http://us3.php.net/manual/en/function.mysql-connect.php
2. Use of mysql_close() explicitly. Although mysql_connect() closes the connections, use of mysql_close() in conjunction with it would make sure that the connection is totally closed.
3. Prevent use of mysql_pconnect(). The disadvantage is :
"The connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect)"
More details at :
http://us3.php.net/manual/en/functio...l-pconnect.php
If this issue is just due to large traffic, you may need to look into either semi-dedicated, or dedicated services with us in order to handle this traffic.
But suddenly it came back, so I just asked them to check the stats and let me know what was going on....I never heard back about that.
I did start looking for information about overload so I could figure out what was happening.
Then on 8/11 (just five days later) the site had a short outage again so I wrote to HostGator again and pushed them for more answers to *why* this was happening.
I truly did not believe I was using that many resources. As a matter of fact I was pretty sure I wasn't using any more resources than I have the past three months. My most users online was three months ago and I didn't have any errors then. I pushed them about the fact that this was a new phenomenon with my forum... and finally got them to look into it more. (NOTE: I now believe it might be the Arcade games... but I still don't really know for sure and don't know how to figure it out.)
I got this answer:I don't think you are using that many resources at this time either. It looks like MySQL was actually down, and the configuration needed to be repaired. MySQL 5 isn't entirely stable with CPanel yet.
and when asked as to whether the situation should improve I received this reply:
Right, basically as MySQL 5 improves and cPanel releases updates which use newer versions or patched versions of MySQL 5, stability will continue to improve.
Things got a lot better - no error messages for several days and then yesterday I got this (along with over 500 error messages):
Your account was suspended for a Terms of Service violation: Excessive MySQL/CPU use. A sample of the log is attached at
the end of this message. Many times, excessive MySQL activity is caused by (a) a long-running process that locks a table,
causing other queries to back up, (b) a query that is not optimized ][example: select all from ... and involving a large or
complex query], (c) huge table copies/maintenance during peak hours.
Please respond and let us know how you will prevent this situation from recurring, so we can restore access. Thanks.
NOTE:, the following are just possible fixes or suggestions, and are not endorsed or supported by HostGator.
They are included in the hope that they may apply to your situation, and/or help you reduce the amount of resources your SQL
queries consume. As always, it's best to backup any data before making any changes or adjustments.
First and foremost, you may need to optimize your tables. The frequency depends on the size and usage of the database, but
most databases would benefit from doing something like this on a yearly basis: a) Enter your phpMyAdmin/MySQL control panel.
Click on the database (not the table, the database name), and on the right hand column your tables should be listed. Scroll
down till you see the .Check all. link. Click on that link, make sure all database tables are checked and then from the dropdown
next to it, and carefully select .Optimize table..
Additionally, adding indexes to your table(s) may improve performance. If you're not sure what you're doing, it's best not to
modify any table; caution is recommended. There are various articles (http://www.whenpenguinsattack.com/2006/03/11/optimizing-mysql-tables/).
It may be best to google for something like [Your Software Name] MySQL indexes for suggestions.
Wordpress Users: (http://wordpress.org/): It is important to keep up to date with this software; vulnerabilities have been found
in past versions. Regardless, installing the Wordpress Cache plugin can greatly reduce resource usage and improve performance
(http://wordpress.org/extend/plugins/wp-cache/). If you're not using the current version of Wordpress, keep in mind that v2.1+ is much
faster than older versions.
Mambo Users: (http://www.mamboserver.com/): As of May 2007, the current version is 4.6.2. Older versions likely have security issues.
It is important to update to the current version (http://mamboxchange.com/frs/?group_id=5), especially since Mambo/Joomla is an extremely
popular exploit target due to the widespread use of this software.
Joomla Users: (http://www.joomla.org/) As of May 2007, the current version is 1.0.12. Older versions have critical security issues, and
must be upgraded to the current version (http://dev.joomla.org/content/blogcategory/21/86/). Mambo/Joomla is an extremely popular exploit
target due to the widespread use of this software. You may want to look at a plugin that optimizes the tables
(http://forum.joomla.org/index.php?topic=160051.msg771662).
osCommerce: (http://www.oscommerce.com/). You may want to search for oscommerce speed optimize on Google.
[root@gator104php5sql5 ~]# mysqladmin proc | grep databasename
| 725 | username| localhost | databasename | Sleep | 24 | | |
| 732 | username| localhost | databasename | Sleep | 11 | | |
| 738 | username| localhost | databasename | Sleep | 24 | | |
| 739 | username| localhost | databasename | Sleep | 1 | | |
| 747 | username| localhost | databasename | Sleep | 22 | | |
| 752 | username| localhost | databasename | Sleep | 22 | | |
| 756 | username| localhost | databasename | Sleep | 1 | | |
| 757 | username| localhost | databasename | Sleep | 11 | |
I have NO IDEA what those numbers at the bottom mean...but I've contacted HG and told them that I'll remove whatever I need to - games or whatever (I've got the Arcade hack but only about 32 games installed, and when I check my "who's online" I only see a handful of people playing games)- to get the site back up. I've also told them that I'll move to a semi-dedicated server temporarily while we figure out exactly what is using so many resources and what I need to do to fix it.
Unfortunately the last I heard from them was at 8pm last night (CST) - IOW, it's been more than 12 hours since I've heard from them. I've written three times to say I'll pay for the upgrade, I'll remove things from the site, I'll do whatever, but I can't do anything with the database gone!
My site is new (just since January)...and I'm not a programmer so I feel completely lost. I don't know what to do other than wait for HG to respond.
I'm not happy with their service, and want a new host that will be better about responding.
Can anybody help me? Please!
I've been spending $10 monthly, but I could probably go as high as $30... but there's no way I can afford to pay $75-100 a month forever. My site just doesn't make that much money right now.
Forum:
~1300 members
~ 600 active
~40-50 concurrent - most ever ~90
I've been running ibProArcade with 32 games installed.
Current Server situation:
Hosted by: HostGator
Level: Baby
Subdomains 6 / unlimited
Parked Domains 0 / 999
Addon Domains 0 / 999
MySQL Databases 1 / unlimited ---there are actually two but they've suspended the one I was using for my active forum
Disk Space Usage 342.83 Megabytes
MySQL Disk Space 364.02 Megabytes
Disk space available 4657.17 Megabytes
Bandwidth (this month) 11046.36 Megabytes
Email Accounts 4 / unlimited
Email Forwarders 4
Current problem: (this is long, but I want you to have the full picture so you can give good advice)
On 7/30 I upgraded to vBulletin 3.6.8 and had HostGator move me to a mySQL5 server.
On 8/1 I got this error message:
mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
/forum/includes/class_core.php on line 274
I started getting one or two of these a day so every day I would go into the AdminCP and optimize. That seemed to help until 8/29 when it crashed my site completely.
I contacted HG and the site came back up. After that I started running the optimize through phpMYAdmin in cPanel.
Then two days later my site went down again and this time didn't come up for a while. HG finally replied with this:
We were doing some maintenance on PHP on the server. We had to compile in some changes but this is the entire reason the site has been up and down for the past bit of time. If you have any further issues please let us know. We apologize for the downtime.
On 8/6 the site went down *again* and I got this notice when I contacted them:
Hostgator implies a 30 concurrent connections per mysql user limit on script to prevent heavy mysql load on the server and to hunt down scripts that use unnecessary mysql connections due to bad coding practices.
Most connections to your database should drop very quickly so this error usually indicates that your site is receiving large amounts of traffic, or your scripts are not configured properly.
These errors can be prevented by making some coding tweaks into your code.
1. Use of mysql_connect() for connections to mysql dbs in your code. The advantage is :
"The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close()."
A detailed explanation can be found at :
http://us3.php.net/manual/en/function.mysql-connect.php
2. Use of mysql_close() explicitly. Although mysql_connect() closes the connections, use of mysql_close() in conjunction with it would make sure that the connection is totally closed.
3. Prevent use of mysql_pconnect(). The disadvantage is :
"The connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect)"
More details at :
http://us3.php.net/manual/en/functio...l-pconnect.php
If this issue is just due to large traffic, you may need to look into either semi-dedicated, or dedicated services with us in order to handle this traffic.
But suddenly it came back, so I just asked them to check the stats and let me know what was going on....I never heard back about that.
I did start looking for information about overload so I could figure out what was happening.
Then on 8/11 (just five days later) the site had a short outage again so I wrote to HostGator again and pushed them for more answers to *why* this was happening.
I truly did not believe I was using that many resources. As a matter of fact I was pretty sure I wasn't using any more resources than I have the past three months. My most users online was three months ago and I didn't have any errors then. I pushed them about the fact that this was a new phenomenon with my forum... and finally got them to look into it more. (NOTE: I now believe it might be the Arcade games... but I still don't really know for sure and don't know how to figure it out.)
I got this answer:I don't think you are using that many resources at this time either. It looks like MySQL was actually down, and the configuration needed to be repaired. MySQL 5 isn't entirely stable with CPanel yet.
and when asked as to whether the situation should improve I received this reply:
Right, basically as MySQL 5 improves and cPanel releases updates which use newer versions or patched versions of MySQL 5, stability will continue to improve.
Things got a lot better - no error messages for several days and then yesterday I got this (along with over 500 error messages):
Your account was suspended for a Terms of Service violation: Excessive MySQL/CPU use. A sample of the log is attached at
the end of this message. Many times, excessive MySQL activity is caused by (a) a long-running process that locks a table,
causing other queries to back up, (b) a query that is not optimized ][example: select all from ... and involving a large or
complex query], (c) huge table copies/maintenance during peak hours.
Please respond and let us know how you will prevent this situation from recurring, so we can restore access. Thanks.
NOTE:, the following are just possible fixes or suggestions, and are not endorsed or supported by HostGator.
They are included in the hope that they may apply to your situation, and/or help you reduce the amount of resources your SQL
queries consume. As always, it's best to backup any data before making any changes or adjustments.
First and foremost, you may need to optimize your tables. The frequency depends on the size and usage of the database, but
most databases would benefit from doing something like this on a yearly basis: a) Enter your phpMyAdmin/MySQL control panel.
Click on the database (not the table, the database name), and on the right hand column your tables should be listed. Scroll
down till you see the .Check all. link. Click on that link, make sure all database tables are checked and then from the dropdown
next to it, and carefully select .Optimize table..
Additionally, adding indexes to your table(s) may improve performance. If you're not sure what you're doing, it's best not to
modify any table; caution is recommended. There are various articles (http://www.whenpenguinsattack.com/2006/03/11/optimizing-mysql-tables/).
It may be best to google for something like [Your Software Name] MySQL indexes for suggestions.
Wordpress Users: (http://wordpress.org/): It is important to keep up to date with this software; vulnerabilities have been found
in past versions. Regardless, installing the Wordpress Cache plugin can greatly reduce resource usage and improve performance
(http://wordpress.org/extend/plugins/wp-cache/). If you're not using the current version of Wordpress, keep in mind that v2.1+ is much
faster than older versions.
Mambo Users: (http://www.mamboserver.com/): As of May 2007, the current version is 4.6.2. Older versions likely have security issues.
It is important to update to the current version (http://mamboxchange.com/frs/?group_id=5), especially since Mambo/Joomla is an extremely
popular exploit target due to the widespread use of this software.
Joomla Users: (http://www.joomla.org/) As of May 2007, the current version is 1.0.12. Older versions have critical security issues, and
must be upgraded to the current version (http://dev.joomla.org/content/blogcategory/21/86/). Mambo/Joomla is an extremely popular exploit
target due to the widespread use of this software. You may want to look at a plugin that optimizes the tables
(http://forum.joomla.org/index.php?topic=160051.msg771662).
osCommerce: (http://www.oscommerce.com/). You may want to search for oscommerce speed optimize on Google.
[root@gator104php5sql5 ~]# mysqladmin proc | grep databasename
| 725 | username| localhost | databasename | Sleep | 24 | | |
| 732 | username| localhost | databasename | Sleep | 11 | | |
| 738 | username| localhost | databasename | Sleep | 24 | | |
| 739 | username| localhost | databasename | Sleep | 1 | | |
| 747 | username| localhost | databasename | Sleep | 22 | | |
| 752 | username| localhost | databasename | Sleep | 22 | | |
| 756 | username| localhost | databasename | Sleep | 1 | | |
| 757 | username| localhost | databasename | Sleep | 11 | |
I have NO IDEA what those numbers at the bottom mean...but I've contacted HG and told them that I'll remove whatever I need to - games or whatever (I've got the Arcade hack but only about 32 games installed, and when I check my "who's online" I only see a handful of people playing games)- to get the site back up. I've also told them that I'll move to a semi-dedicated server temporarily while we figure out exactly what is using so many resources and what I need to do to fix it.
Unfortunately the last I heard from them was at 8pm last night (CST) - IOW, it's been more than 12 hours since I've heard from them. I've written three times to say I'll pay for the upgrade, I'll remove things from the site, I'll do whatever, but I can't do anything with the database gone!
My site is new (just since January)...and I'm not a programmer so I feel completely lost. I don't know what to do other than wait for HG to respond.
I'm not happy with their service, and want a new host that will be better about responding.
Can anybody help me? Please!
I've been spending $10 monthly, but I could probably go as high as $30... but there's no way I can afford to pay $75-100 a month forever. My site just doesn't make that much money right now.