Preventing Failed Database Connections

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Preventing Failed Database Connections

Post by TipPro »

I have a problem of random failed connections to a mySQL database located on a separate server. I am asking for any suggestions on how I can prevent these failed connections or how to deal with them.

Image

My program is an order fulfillment software using PHP, Javascript (DOJO), and MYSQL. The program sits on the company server and downloads orders collected on a remote server that runs the Web site.

Image

Each step of the program is called via AJAX. The response is set back and reported to the user so the user can see how the download program is going. So each time a database needs to be read from or written to, a new connection is made and destroyed.

Image

So if the user performs a download and there are 20 orders sitting on the server waiting to be downloaded there will be 41 connections made and destroyed. Small batches (less than 20 orders) do not usually experience any failed connections but batches over 20 orders usually experience 1 or 2 failed connections. This program on average will need to handle batches of 100 to 200 orders at a time. One of the main problems of a failed connection is that is slows down the download process considerably. I also do not want to stress out the remote server too much because that server runs the Web site and I do not want to cause any downtime to the Web site.

Does anyone have any ideas on a better way of going about this program?

Thank you,
Paul
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Preventing Failed Database Connections

Post by Benjamin »

Increase max_connections in my.cnf.
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Re: Preventing Failed Database Connections

Post by TipPro »

Thank you for the reply.

I thought max_connections is a variable setting the maximum number of concurrent connections. If I close my connection at the end of each step, my program at most will only have 1 concurrent connection. So increasing max_connections would not solve my problem. Am I understanding this correctly?

The failed database connections are described as the server is not available. I do not understand how most of the time the server is available and after a lot of repetitive calls to the database I start receiving failures.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Preventing Failed Database Connections

Post by Benjamin »

What's the error number?
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Re: Preventing Failed Database Connections

Post by TipPro »

Sorry, for my delayed response I was away at a seminar for the last two days. Anyways, I really appreciate you taking time to help me out.
What's the error number?
I am not receiving an error number or description. What is happening is that the wheels are just spinning. Should I timeout the request and try again immediately or am I going potentially stress out the server if it keeps looping timeouts and repeated requests? Usually I would just try it out but I do not want to mess up the remote mySQL database because it hosts the public Web site's data.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Preventing Failed Database Connections

Post by Benjamin »

Your problems run deeper than you think. I really need an error code. The connections may not be failing. The queries may be staying in a pending state due to tables being in a locked state waiting for other select or update queries to finish.

You should use MySQL administrator to login to the database server and watch what is going on. You should monitor the CPU usage of MySQL, check the my.cnf configuration, verify that the tables are indexed properly and go from there.

Regardless, I'm only speculating without an error code.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Preventing Failed Database Connections

Post by Eran »

Do you have any tools to inspect the ajax requests? there are several that can inspect outgoing / incoming requests and analyze response body and headers. Firebug, a firefox extension, is an excellent tool for this. You should gather the information sent back from the server, including headers - probably it returns an error which your javascript does not expect and thus doesn't handle, causing your loading animation to appear "stuck".

If you could get the error information here on the forums, I'm sure someone can come up with a good solution.
Post Reply