Friday, May 30, 2008
Windows Max Connections
At my work I was having a weird problem with a Server 2003 running MySQL 5.0.54 that would start dropping connections with an php XML based api call from our external partners. We have have a successful connection remotely and everything would be running fine and bang 5 minutes into the api call I would see mysqli cant connect errors. What made matters more funny, most usually the errors would come in pairs or sometimes triplets. To begin I increased the max_packet_size from 6M up to 10Mb, increased timeout_connection, and it seemed to correct the error. The issues surrounding the error, Mysqli was making a single connection, perform a query, a read, and an update if necessary. make a new connection and perform the cycle again. After increasing the max packet, we ran 10,000 record tests 3 seperate times with no errors. The next day, Start to get in the api feed and the same errors popped up again. Almost like clockwork, 5 minutes into the feed, mysqli kicked back can not connect in pairs about every 25-35 records. My last ditch effort, I researched max TCP connections and time_wait settings on server 2003. The default is 5000 open connections and 120 seconds windows will hold at time_wait until closing. using a registry hack, I set the max at 65534 and put time_wait down to 25 seconds. Bingo, all my problems went away. With the api, using a single mysql connection per api call and not having the benefit of mysql persistant connections due to using mysqli libs, I was hitting 5000 tcp connections about 5 minutes into my api feed and due to windows having the hold for 120 seconds, windows wasnt releasing the port back to free in time so 2-3 requests would come in faster than windows could close the port. interesting huh?
Subscribe to:
Posts (Atom)