Knowledgebase

MySQL Server Overload – Simultaneous Connections

When you are using content management systems like WordPress, Joomla or the majority of shopping carts you will find yourself using MySQL databases. When you are using databases you can possibly run into a problem with too many simultaneous connections trying to reach your database. When you are in a shared hosting environment using MySQL you have a maximum of  40 simultaneous connections reaching your database at a time. The number of 40 may seem low to you, but is far greater than the amount of connections for a normal website.

Looking at the term “simultaneous connections” it appears very vague if you do not understand it in reference to web hosting services. Simultaneous connections usually references when a visitor connects to your website they activate a single connection to your MySQL database. After the page loads completely the connection to the database is closed. Each time a different page is loaded the connection is created again and then once again closed. Inside your MySQL database, you can reach a server overload if your site is an extremely high traffic site. This can also become a problem if you are getting very little traffic and your site is not coded correctly. When a site is not coded correctly it sometimes does not close the connections to the database server.

If either of these issues happens in your MySQL server database on a shared server, you could possibly face a MySQL server overload violation. If your website and database receives a terms of service violation ticket here are two steps you can take to optimize your database.

1. Review any coded website pages that open a connection to your MySQL database. Review each page to make sure each connection string opens and closes correctly based on the coded website page. You also should review that each connection connects and disconnects at a reasonable speed.
2.  Review your database to make sure that it is using queries that are not taking too long to run. Each connection should be closed in a timely manner. The amount of time a connection takes to close also directly impact the speed visitors experience when browsing your site.

There are several steps you can take to be proactive regarding MySQL Server Overload issues with your account. The following three steps can assist you in protecting yourself from being suspended for too many simultaneous connections to your MySQL database.

1. If the coding of your site uses the MySQL_pconnect php function to create a connection to your MySQL database you are leaving yourself vulnerable. They MySQL_pconnect php function does not close the connection to your database after the MySQL_close command is ran and causes any unused connections to your server to not automatically close. If you are using this software you should enable connections to use mysql_connect in place of mysql_pconnect. If the coding of your site cannot use mysql_connect, then please contact your website developer or development forums to find a solution that fits your site.
2. While you are coding your website make sure that all MySQL connections are opened and closed properly during the build process. If in the build process your connections do not open and close correctly you are able to to solve the issue before you complete the develop stage. You may also use the following query to check how many current connections your site has as well: SELECT *, COUNT(*) FROM information_schema.processlist
3. Any queries that your website executes should be kept as short possible. If your execution files are kept short and open and close correctly you greatly lower the risk of having too many simultaneous connections open on your account at one time even if you have a site with a higher incoming traffic.

Also Read

Powered by WHMCompleteSolution