MySQL BUFFER OVERFLOW?

chads2k2

Hey... is there a way to flush out the buffer? Like clear the RAM in the MySQL engine? B/c man I have a pretty big site (which is on shared hosting) and keep getting a too many connections error. Is there a simple way of protecting against that? We have a HUGE advertising push this week and I really need to get this problem solved. So can someone help this guy out? Thank you.

Chad R. Smith
President of Broken Formula
www.brokenformula.com

[deleted]

"Is there a simple way of protecting against that?"

No.

The database simply does not have enough available connections.

MySQL has a configurable limit on the number of concurrent connections, and your database server has reached it's limit.

The only thing you can do about it is use fewer connections, but that means fewer pageviews, or increase the number of concurrent connections that the database allows.

BTW, if you are using persistant connection (mysql_pconnect), switch that off immediately, that keeps connections open even if the script has stopped, and that uses a sh*tload of resources, especially on a shared server.

chads2k2

Nahh.. i am using mysql_connect() I looked at the Pconnect and decided severly against that. I had that trouble with ASP and thought it sucked. That is why i switched to PHP. Well... is there anything else I can do to increase connection limit? Or should I just put a "Server is busy" page up? Lata!

[deleted]

Well, like I said, there are two ways to prevent this error from happening:

Use fewer connections
Increate the databaseserver's limit.

The first is usually not possible because one pageview usually only uses one connection, and the second is up to your ISP. So get on the phone and complain. The other clients on that machine must be having the same problems.

chads2k2

Yea fewer connections isn't really likely. I will complain... what is the max level MySQL can take?

[deleted]

The default is 100 and the absolute limit is 1024.

But, by the time you have 1024 concurrent queries going on, your mysql server will have died.

If you're planning to use a lkot of connections, you should consider getting your own personal database server, because if one of the other clients decides to run a heavy query it could bring your site to a halt.

chads2k2

Yea we are going to go to a OC192 Oracle connection thats dedicated soon hopefully if this site works out! We need like 7G to do it a year. Which isn't half bad. Its either OC48 or 192. I know a HUGE jump between but eh.. I can't remember. Thank you very much for answering my questions and have a great day!

Chad

bastien

like not requiring every page to make a database call...for certain things that are data-intensive but not frequently changed (like a product list) it might be worhtwhile to store that info in the DB but also create a text file with the same data in it. then access the text file and not the DB to pull out that info....

the trick here is to look at the data itself and figure what is constantly changing and what is changed only once in a while and then make the changes based on that information....

it would also involve rewriting some admin type pages that to pass new data to the DB and create / update a text file with the new data...

hth

chads2k2

True but pretty much everything on this site will be always changing. 🙁 I will look into changing a few things around and hopefully find a way to minimize the info needed. Plus the queries are fairly short and to the point. I didn't do anything that would make MySQL flip out or anything! Thanks though to everyone I really do appreciate it.

Sxooter

Also, check to make sure you're not opening connections you don't need. It's not uncommon for folks to program like so:

connect
process query
connect again
process another query
connect again again
yada.

Unless you're connecting to more than one database or processing more than one transaction at a time, you don't generally need more than 1 connection in a script.

[deleted]

"Unless you're connecting to more than one database or processing more than one transaction at a time, you don't generally need more than 1 connection in a script."

Fortunately this is not a problem because PHP uses connection-pooling. You simply cannot create two identical connections in the same script.

If the database, host and username are identical to a connection that you already opened in that script, you will simply get the same connection identifier again, no second connection will be opened.

Sxooter

Yeah, I'd forgotten that PHP does that now. Sometimes old and arcane knowledge just gets in the way, eh?

[deleted]

It's common sense to only open one connection actually. The fact that pHP uses connection pooling is sometimes pretty f-ing annoying. 🙂

csn

Originally posted by vincente
The fact that pHP uses connection pooling is sometimes pretty f-ing annoying. 🙂

How/when so?

csn

Originally posted by vincente
The fact that pHP uses connection pooling is sometimes pretty f-ing annoying. 🙂

How/when so?

[deleted]

Things like transactions using InnoDB, you cannot keep two transactions open, opening the second transaction on the same connections gives an error.

You get all kinds of funky problems when inluding() third party scripts like hitcountes etc. On a host you usually get only one DB and one username, so the tables used byt that script must be in your main database. That means the 'external' script cannot open it's own connection, which means it will fiddle with your main connection. For ex; your script opens a connection to the DB and includes the third-party script. That script also opens a connection, but you only have one DB so it get's the same connection-identifier that your main script got. When the included script is finished, it will close the database connection (because that is proper procedure) and because it got the same identifier that your main script got, it actually closes the connection that your main script opened. Now your main script continues with a closed DB connection.

More than one person have lost their hair due to that kind of problem.