[RESOLVED] I want initializePDO method to try a little harder to connect

[sneakyimp]

I've got a situation where I have a bunch of scripts trying to connect to a very busy database. These scripts typically lock records to do work on them by updating the record_lock_microtime and record_lock_name columns of the record in question and, once they are done trying to do their work, they will come back to try and unlock the record (or remove the record if the work is complete). The problem I'm having is that my database server is occasionally too busy and will drop a connection and this currently results in a fatal exception being thrown and this in turn means that those job records that have been locked will never be unlocked. If my method that connects via PDO fails, I'd like for it to sleep for some period of time and try again. Apparently the PDO Constructor will throw an exception when the database connection attempt fails and I do have a record of these exceptions in my log files.

The problem I'm having is understanding how to structure my initializePDO method. I expect I'll just need to put this try/catch section inside a loop until it successfully connects or something...

	private function initializePDO() {
		try {
			$this->db = new PDO(DB_DSN, DB_USER, DB_PASS);
			if (!$this->db) {
				$error_msg = 'PDO create failed for slot #' . $this->slot . '. $this->db: ' . "\n" . print_r($this->db, TRUE);
				MTLog::getInstance()->error($error_msg);
				throw new Exception($error_msg);
			} else {
				MTLog::getInstance()->debug2('PDO create success in slot #' . $this->slot);
			}
		} catch (Exception $e) {
			// instead of re-throwing exception here, maybe sleep a bit and try again
			throw New Exception('initializePDO() threw an exception while trying to create a PDO connection to the db: ' . $e->getMessage());
		}
	}

A couple of things are making this tricky. First, I can't really think of a good way to replicate the circumstance of a busy server so I can get the exact exception thrown (I'm not sure if it's just a PDOException or something else). Second, I'm wondering if I give a hoot about the SQLState values. I can't seem to locate any page in the manual which might detail what the different SQLSTATE values are. Here are a couple of error entries that appear when an exception gets thrown by my current code.

2013-06-20 12:13:34 [20649] ERROR : EXCEPTION in deleteImage() main block, slot=24: initializePDO() threw an exception while trying to create a PDO connection to the db: SQLSTATE[08004] [1040] Too many connections
2013-06-20 12:13:34 [20654] ERROR : EXCEPTION in deleteImage() main block, slot=2: initializePDO() threw an exception while trying to create a PDO connection to the db: SQLSTATE[HY000] [1040] Too many connections

Does anyone see any potential problems (e.g., infinite loops) with this modified version?

	/**
	 * Initializes a PDO Connection to the database.  If the PDO constructor throws
	 * an exception, this function will keep trying to connect MAX_DB_CONNECT_ATTEMPTS times
	 * and, if it fails, will sleep pow(DB_CONNECT_SLEEP_BASE, $attempts) seconds before trying again.
	 * @throws Exception
	 */
	private function initializePDO() {
		$attempts = 0;
		do {
			try {
				$this->db = new PDO(EREP_DB_DSN, EREP_DB_USER, EREP_DB_PASS);
				if (!$this->db) {
					// i've yet to see this code execute
					$error_msg = 'PDO create failed for slot #' . $this->slot . '. $this->db: ' . "\n" . print_r($this->db, TRUE);
					MTLog::getInstance()->error($error_msg);
					throw new Exception($error_msg);
				} else {
					MTLog::getInstance()->debug2('PDO create success in slot #' . $this->slot);
				}
			} catch (Exception $e) {
				// the connection attempt throw an exception!
				$this->db = NULL;
				$attempts++;
				if ($attempts > self::MAX_DB_CONNECT_ATTEMPTS) {
					// too msny attempts!  throw an exception and stop trying to connect
					throw New Exception('initializePDO() threw an exception while trying to create a PDO connection to the db: ' . $e->getMessage());
				}
				$sleep_time = pow(self::DB_CONNECT_SLEEP_BASE, $attempts);
				MTLog::getInstance()->debug("DB connection attempt #" . $attempts . " failed in slot #" . $this->slot . ". Sleeping for " . $sleep_time . " seconds.");
				sleep($sleep_time);
			}
		} while (is_null($this->db));

} // initializePDO()

[traq]

There's a list of Error + SQLSTATE codes here
[edit: sorry, I'm assuming this is MySQL, but I just realized you don't actually say so in your post].

The loop looks fine...

But, when you say the DB "drops" a connection, do you mean it drops an existing connection? Or that a new connection fails?

From your description, I suspect you mean the former. But then, that doesn't happen when you initialize the connection, does it? Wouldn't that be happening later (at some point while you're using the connection)?

And your exception messages seem to indicate the latter... I'm not understanding something, here.

: )

[sneakyimp]

Thanks for your response (and the error codes link).

I am not sure at all whether the 'too many connections' error (which seems to be the most frequent error description by far) happens when a process initially tries to connect or whether the process has already performed some action using the database and is coming back to try another db transaction later after some time has elapsed. I suspect it is an initial connection failure in most cases but I also want to prevent the dropped connection as well. It's pretty difficult to diagnose exactly which is happening because of the forking of processes and the fact that many, many processes all write to the same file. Basically what happens is that I have N machines, each of which has one master process which connects to the database to lock-and-fetch 500 records in a database table. The master process then starts to fork off one new process to handle each locked record and that forked off process must make its own connection to the database to avoid any crosstalk between it and its parent process -- you can't have them sharing the same db connection resource.

As each forked-off child process works, it is downloading an image and then rendering thumbnails and then uploading them to a CDN -- a possibly time-consuming process involving lots of data crunching and possibly network latencies at various stages. It's a distinct possibility that the database connection might drop or time out or something between its initial connection and final database call.

[traq]

How many children are we talking about, here?

The MySQL manual says that 150 connections is the default, but that a typical server should be able to handle 500-1000 (or many more, depending on system resources).

(Aside - perhaps you could "simulate" a busy server by reducing max_connections to four or five, if you have a separate testing sever, that is...?)

[sneakyimp]

traq;11030525 wrote:

How many children are we talking about, here?

hundreds. easily 300

traq;11030525 wrote:

The MySQL manual says that 150 connections is the default, but that a typical server should be able to handle 500-1000 (or many more, depending on system resources).

I'm on top of this and have increased it to 300 thanks to copious amounts of RAM being available, but I need my application to be better-behaved because these errors cause a real mess in my database which is hard to clean up. The recent increase to 300 has resulted in a very well-behaved system for the time being but I expect that in time we'll either need to add more of these processes or we'll experience greater user traffic --probably both. I can't have my processes dying when the connection attempt fails a single time because it leaves records lock in the database which never get unlocked.

traq;11030525 wrote:

(Aside - perhaps you could "simulate" a busy server by reducing max_connections to four or five, if you have a separate testing sever, that is...?)

There are a variety of other reasons which make simulating the issue difficult. I realize that a proper simulation would be a good idea, but it would be costly and difficult and the client would likely not go for it.

[traq]

sneakyimp wrote:

I can't have my processes dying when the connection attempt fails a single time because it leaves records lock in the database which never get unlocked.

How do you unlock them when this happens? Maybe you could write a bash script and call it when you catch the exception.

....

Do you have any idea how much time might transpire between the initial and final DB calls? If it's not too long, you could change the timeout. Alternatively, I found this comment on SO (the question wasn't really related, otherwise):

IMHO, it is a better idea to have a smaller timeout and make multiple connections. Doing so will free up the connections for other processes.

beyond this, I'm not sure what else to do ...never done anything like this.

[sneakyimp]

traq;11030565 wrote:

How do you unlock them when this happens? Maybe you could write a bash script and call it when you catch the exception.

records are locked by setting a couple of columns
record_lock_microtime -- when the record was locked
record_lock_name -- IP address / name of server which locked the record

Under most circumstances, the process will finish processing the record and delete it OR it will unlock the record by contacting the database and setting those two columns to NULL. This unlocking is obviously not possible if you can't connect to the DB. delegating to a BASH script doesn't help if the BASH script can't connect either. this is why trying to connect a few times seems wise -- i'm willing to bet a little persistence will eliminate the vast majority of cases where the db connection failure causes a problem. Trying once just isn't persistent enough.

My problem right now is to determine whether i need to destroy my PDO connection, close it, or just define a new PDO object. Given that [man]PDO [/man] has no ping function, i'm leaning toward just setting $this->db to NULL and then defining a new PDO object.

traq;11030565 wrote:

Do you have any idea how much time might transpire between the initial and final DB calls? If it's not too long, you could change the timeout. Alternatively, I found this comment on SO (the question wasn't really related, otherwise):

It's likely to take anywhere from 5 to 30 seconds, depending on responsiveness of remote servers and time required to crunch the image data. I think increasing the timeout is a bad idea given that most processes are not actually using the connection but rather waiting on network latencies. In fact, it would likely make the 'too many connections' problem worse if every process is hoarding its db connection.

traq;11030565 wrote:

beyond this, I'm not sure what else to do ...never done anything like this.

I appreciate your input, and mostly worry that my 'improved' function above is to too primitive -- I'm not sniffing the type of exception that might be throw in connecting, I'm not responding differently based on different types of exceptions, I'm not closing any PDO connections, I'm not checking first if there's an existing PDO resource which exists (and it not null) but nevertheless cannot connect. I'm wondering what happens to a PDO connection that has timed out. I'm guessing it's still a resource object but don't know of any quality about it that I might test which would let me know that it has timed out and I need to reconnect. I suspect that we'll only get an exception thrown on a timed-out PDO object when we try to run a query.

[traq]

sneakyimp;11030567 wrote:

records are locked by setting a couple of columns
record_lock_microtime -- when the record was locked
record_lock_name -- IP address / name of server which locked the record

Under most circumstances, the process will finish processing the record and delete it OR it will unlock the record by contacting the database and setting those two columns to NULL. This unlocking is obviously not possible if you can't connect to the DB. delegating to a BASH script doesn't help if the BASH script can't connect either. this is why trying to connect a few times seems wise -- i'm willing to bet a little persistence will eliminate the vast majority of cases where the db connection failure causes a problem. Trying once just isn't persistent enough.

mm. Yeah, that's a difficult situation.

sneakyimp;11030567 wrote:

It's likely to take anywhere from 5 to 30 seconds, depending on responsiveness of remote servers and time required to crunch the image data. I think increasing the timeout is a bad idea given that most processes are not actually using the connection but rather waiting on network latencies. In fact, it would likely make the 'too many connections' problem worse if every process is hoarding its db connection.

Agreed.

sneakyimp;11030567 wrote:

I'm not sniffing the type of exception that might be throw in connecting, I'm not responding differently based on different types of exceptions, I'm not closing any PDO connections, I'm not checking first if there's an existing PDO resource which exists (and it not null) but nevertheless cannot connect. I'm wondering what happens to a PDO connection that has timed out. I'm guessing it's still a resource object but don't know of any quality about it that I might test which would let me know that it has timed out and I need to reconnect. I suspect that we'll only get an exception thrown on a timed-out PDO object when we try to run a query.

But you could be doing those things. 🙂

[sneakyimp]

traq;11030569 wrote:

But you could be doing those things. 🙂

sigh. I suppose.

Consider this, though:
How do you check to see if a PDO object is connected? As far as I can tell you can't. There is no ping function.
How do you close a PDO Object? As far as I can see there is no close function either. Should I unset it? How do I know when it's time to unset it?

As you can imagine, if I must attempt a query before I can tell if a PDO connection has timed out, my code might get considerably more complicated -- I'd have to put that try-try-again code wherever I want to call a query.

[traq]

maybe you could extend PDO with a ping() method. Try to [font=monospace]SELECT 1[/font], or something, and reconnect if you get an exception.

I think a connection closes (on the PHP end, anyway) when the PDO object has no more references (so, assuming you've only assigned it to the one variable, just do [font=monospace]$PDO = null[/font]). I don't think you can explicitly close it on the MySQL side (so, another reason a long timeout is bad).

[NogDog]

Are you by any chance using persistent connections? My experience suggests they're more likely to leave "zombie" processes hanging around and other similar problems, at least with MySQL.

[sneakyimp]

AFAIK, I'm not "using" persistent connections. I just read a bit and see that the PDO constructor takes a 4th parameter that would let me specify PDO::ATTR_PERSISTENT = false but I am not doing this.

my server does permit persistent connections:

mysql.allow_persistent => On => On
mysql.max_persistent => Unlimited => Unlimited
mysqli.allow_persistent => On => On
mysqli.max_persistent => Unlimited => Unlimited

But the default behavior appears to be to use non-persistent connections:

$db = new PDO(EREP_DB_DSN, EREP_DB_USER, EREP_DB_PASS);
var_dump($db->getAttribute(PDO::ATTR_PERSISTENT)); // yields bool(false)

[Derokorian]

According to this page You can close a connection by setting the variable holding the object to something else, so I would assume unset works just as well. Note: A user comment on a separate page suggests that using a derived class and persistant connections does NOT work as of 5.4 see bug report