Memory and mysql_pconnect

Anon

When I use mysql_pconnect to connect to my database a lot of threads is made from the same application and is eating my memory. When i use mysql_connect the problem dosen't appear. As I understand the persistant connection only should appear once !?. Anyone had the same problem ?.

Lars Brandi Jensen

Anon

pconnect is a timesaver, not a resource saver.

A pconnect appears once for every instance of your script and for each instance is appears once per user/pw/database.

So, if you open your 3 instances of your script (open it in 3 browser windows at the same time) you get 3 connections for each user/pw/database you use in your script.

So if your script uses 2 connections to different databases, you end up with 2x3=6 connections.

These connections stay open for as long as you set the timeout value.
While these connections are open, starting your script again should re-use the existing connections, saving the time of opening a new connection.

If you use normal connections, the connections will be opened and closed as required by the script, giving you fewer open connections, but higher cpu load.

Anon

Hmmmm, okay, but now i have 10 open connections. Without mysql_pconnect MySQL uses about 36 MB, with pconnect 163 MB. That a lot I think. Well i have to adjust the allowed time for persistant connection to a lower value. They ar remaining for days right now.

Lars Brandi Jensen

Anon

You'll have to experiment to see if using persistant connections increases performance for you.
If it does not do that significantly, then it's just using your memory and using normal connections might be better.

If you have memory to burn, use pconnects.

Anon

Yeah.. sounds iteresting.. I have the same problem, more less, but one question only.. how to adjust the connection time?

Anon

Hi!
<<
These connections stay open for as long as you set the timeout value.
While these connections are open, starting your script again should re-use the existing connections, saving the time of opening a new connection.

You are stating that they should re-use the existing connections. But thats the problem i have on my server. If i hit refresh 10 times on the same page, where pconnect is used, it opens 10 new connections. Am i getting something wrong here? or there is a problem on my server?

Thanks
Artur

Anon

Nope, you got me, I have not clue why that would happen.

My setup also uses pconnect and I cannot get it to open more than 7 connections.

(atleast, without using more than one simultaneous browser session)

Anon

Question for you, are you using netscape?
I found that refreshing a page under netscape causes lots of connections to show up under mysql's SHOW PROCESSLIST, however under IE, only 1 is created.

I'm investigating further, let me know if you find anything.

Andrew

Anon

Hmmm, quite interesting. I am using netscape 4.75. I've just tried it out with netscape 6 beta. The difference is 5 persistant connections generated by 4.75 and only 2 by ver 6 beta.

Lars Brandi Jensen

Anon

Did some more testing, seems like most everything makes more connections, tried apache's benchmarking tool, lynx etc. Could it be IE's KeepAlive HTTP? Another thing, is that the connections eventually get reused after there are about 7 or 8. Very odd indeed.

Andrew

Anon

I think is happening because the web server has a number of child processes that take http requests, usually between 5-10 children on a low-use apache engine like the one we probably all develop on.

Any one of these children can take a php request, and when pconnect is called from your script, the php engine only opens and keeps the connection open for that child.

So if on a subsequent request you get served by a different child of apache, that child will not have a connection yet.

Then, when all children have had a chance to service a request, and have opened a connection, you'll start to see frequent re-use.

Your comments about IE's KeepAlive are probably accurate, by keeping the same HTTP connection open, the same apache child is being used, which has already opened up its database connection.

aleczapka

So to fix it I should change the KeepAlive variable? and set it to what?

KeepAlive is not set to 30.000 (or above) and this is the value I can see in mysql processlist...

so what should I do.. how to tell mysql or apache "hey you guys, dont keep such old processes - kill all if they reached, lets say, 5000"....

btw: does 5000 means 5000 seconds?

Anon

My connection are not even reuse, I often have 200 process all in sleep mode? What can I do the de process to reuse themself? thanks

aleczapka

Generally, pconnect saves your processor time, while connect saves your memory. Choose what is more appropriate for you.

And now the most important thing, I have a server with a quite high load so sometimes, I got this message - "could not connect to mysql server..." - maximum connections limit was reached, but I had a lot of threads with timeout over 30.000 - quite to much... and I was looking for some answer here and on some other resources in interenet, but could find anything.

Well good news for everyone with the same problem, as old proverb says: RTFM 🙂

In mysql manual is written anything, you ever would like to know...

The parameter to adjust the threads timeout is "wait_timeout" - by default set to 28.000.

Now, to set this value you could to it in two ways: one is, to put in your /etc/my.cnf (of wherever your mysql config file is) or via command line with --set-variable wait_timeout=value (--set-variable var=option)

Anon

so for VERY high load sites... connect or pconnect? that is my question.

[deleted]

pconnect and lots and lots of memory.

Anon

I run a website that receives around 10k unique users a day, often up to 300-400 users on at a time. We have been having problems with too many connections for mySQL lately and we have 1 gig of ram.

I'll try switching pconnect to connect and let you guys know how it works.

Anon

Yeah, I have a site that has about 25,000 unique visitors a day, and we recently ran into this same problem. We have 4 webservers talking to one dual processor MySQL server with 500 MB of RAM. We were using pconnect until this last week when we were reaching our max_connections of 250, 350, and then 450. We couldn't bump it up anymore because our MySQL server started swapping at 450 connections.

Since then, we have switched to regular mysql_connect and all machines are now happy. Our MySQL server is sleeping again, but our webservers our doing a little more work. Also, I have seen no visual difference in page load time.

From my research and experience, I would suggest only using mysql_pconnect if you have tons of RAM on the DB server (1+ G😎 and you can tweak the wait_timeout variable in MySQL to something manageable. The default is 8 hours, which was way too long in our situation.