Persistent Connections

Anon

Hi all,

This is my first post and I appreciate your help in trying to figure out what exactly is going on with my server.

I have a PIII 700 MHZ Linux with 256 MBs of RAM. This server is mostly used for testing and development but also has few low traffic working sites that are visited by qround 5000 people per day in total. All sites are PHP/MySQL.

Recently, I have been getting 'too many connections' error and I find myself having to reboot server or at least stop and then restart MySQL. This sounds quite bizarre for such a server to behave as such for such a small load.

One thing to know is that PHP is compiled both as apache module and cgi stand alone.

When I queries active processes, I noted that sites that are using persistent connections (pconnect) seem to be causing this problem. The number of open connections seems to be always increasing until server clogs up. Means, it seems as if persistent connections are more like permanent connections.

My questions are the following:

1) When does a persistent MySQL connection expire? Meaning if user A comes to site at 8:00 am in the morning of Monday, till when does his process remain active on server?

2) What is the main advantage of persistent connections?

3) The Max connections on MySQL server are set to be 100. How high can I take this? I have been told that such a machine can handle 1000 connections. Do you recommend I change this variable to such? How do I change this variable through mysqladmin?

Thanks in advance for your help

Cheers

Freddie

Anon

1)Persistent connections exists while the httpd process which opened the connection lives.
I don't know if you understand (skip this if you do) that for each username that you use in your call to mysql_pconnect() function, you establish a new connection. You should use the same username to establish the connection to a given database, not a different username for each user that logs in into your site. Maybe this is what's causing all this opened connections.

2)The adventage is that you skip the db connection overhead each time a db connection is required. I don't know how mysql works, but in the case of postgresql, which forks a new process per connection, pconnect saves lots of resources (and of course, things go faster).

3) I don't know.

Anyway, the apache server has the MaxRequestsPerChild option. It establishes the number of requests that the child can process before being killed by his parent process. When the child dies, all the connections to the db are closed, and a new (unconnected) child is created. You could modify your httpd.conf like this:

MaxRequestsPerChild 500

It's not the solution that you're looking for, but at least you won't have rejected connections. I think it will keep things going on until you find out what's going on.

Hope it helps.

Anon

You have accurately identified the problem.

Persistent connections should not be used on shared-hosting environments, where multiple mysql login identities are likely to be in place, unless you're willing to buy a whole lot more RAM and radically increase the connection limit in mysql.

Each connection sucks up a considerable chunk of memory, and counts against the max connection limit that you have set in your mysql configuration.A connection is defined as a combination of a mysql login and a Web server instance.

If you're using Apache 1.3x, which is a forking server, a dozen attempts to connect with the same login ID might very well create a dozen persistent connections, because of the way Apache hands out work to child processes on a round-robin basis. You'd get zero performance boost from the persistent calls, and you'd lose a lot of system resources.

Your best option is to (a) inform your shared site customers, and (b) change php.ini:

mysql.allow_persistent = Off;

Persistent connections are a very good idea on dedicated servers with high load and a small number of mysql logins across the various PHP scripts.

[deleted]

There is exactly one solution to the 'not enough database connections' problem, and that is to increase the number of connections that your database can handle.
All other solutions just lower performance by limiting the number of resources that your script is allowed to use.

I explained some of this here:
http://www.hvt-automation.nl/yapf/faq.php?cmd=viewitem&itemid=26

1) It is not just 'how long will a persistant connection stay alive', more importantly is 'how many does the webserver require?' If your webserver can start 200 children (an average number) then it can require 200 database connections even if it only runs one script that uses one connection to one database.

2) Pconnect only saves time, not memory. In fact, it uses much more memory because there are many more connections open than there are in actual use.

3) 1000 is the hardcoded limit of MySQL.
But, one DB connection takes at least 2.5MB, so your server would need 2.5GB to allow 1000 connections.
With 256MB you can start 256/2.5=102 connections before you run out of memory, and your DB starts slowing down as it begins to use swap-memory. As memory is quite affordable these days, I'd recommend to have 128MB for the OS, 128MB for table-cache, and at least 5MB per connection. In general, database servers never have 'enough' memory.

Anon

Hello Vincent, Steve, and Francois,

Great thanks for your reply. I truly appreciate it.

I also consulted the article that Vincent posted and that was quite helpful too.

There are currently 3 mysql users on my server website:

a) root (connects to 4 databases (site that receives 3000 users per day. It does not use pconnect)

b) frank (Connects to database of site that uses pconnect and receives 1200 users per day)

c) tester (Connects to sites in beta testing, none uses pconnect, total 1000 visitors per day)

I just again had to reboot my server (I have to do it every 12 hours because of this mysql running out of resources kind of thing. Before I rebooted this I ran top command in telnet and got

idle: 97.8%

Meaning, I believe, that server is using only 2.2% of its ability.

Also before I rebooted I looked at list of actib=ve MySQL processes. I got around 80 for user frank and none for others. A typical line would look like this:

2466 frank localhost myid250 Sleep 22933 NULL
2511 frank localhost myid250 Sleep 160393 NULL
2562 frank localhost myid250 Sleep 12704 NULL
3442 frank localhost myid250 Sleep 11846 NULL
3580 frank localhost myid250 Sleep 110370 NULL
3583 frank localhost myid250 Sleep 10365 NULL
3982 frank localhost myid250 Sleep 5800 NULL
3985 frank localhost myid250 Sleep 2337 NULL

Where number after SLEEP is some sort of time measurement.

So I guess the problem is with that particular user that uses permanent connections. And even though root user is far busier it is not impacting the server.

My httpd.conf has the following lines:

MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 8
MaxSpareServers 20
StartServers 10
MaxClients 150
MaxRequestsPerChild 30000

My questions is this respect:

1) Do any of httpd.conf variables have any role to play in optimizing server? Do they impact MySQL ability to handle connections?

2) The 5MB per connection means that 256 MBs are not enough for the 100 connections has (You are stating that this much is needed for operating system and cache alone). So when you say, how many the server can handle, I guess you are relying on available RAM and so 500 connections would require more than 1GB of RAM.

2) You mentionned RAM requirements per persistent connection. Is it same for non persistent connections but just freed faster?

3) In case I take persistent connections out, does the 97.2% idle mean I can have hundreds of thousands of visitors per day with same 256 MB?It seems that 5000 visitors have only used 2.8% of resources.

4) Based on what you said in your article, it seems a bad idea to have multiple databases on our server. What we are doing is a network of sites, each has its own database, and all databases have same mysql user (root). All these sites (around 20) are planned to be on same server. We can however join all 20 databases together and create one mega database with around 200 tables! Then we only have to worry about one MySQL connection per user instead of multiple as user is moving around networks. Is this a recommended option you think? Or will we be shooting ourselves in the foot?

5) If there a difference on how many actual mysql users are accessing various databses? Meaning if we have 20 sites and 20 databases none uses persistent connections, is there any difference on how whether these 20 databases are connected via 4 (5 tables each) or 1 mysql users?

6) You mentionned that hardcore limit of mysql server is 1000 connections. How can this allow Yahoo! finance to use MySQL for their operations? I am sure they have tons more connections than just 1000. Does anyone have any explanation?

Again I thank you all for your help. God bless

Freddie

[deleted]

1) yes and no. You could try to keep the max number of apache children below the max connectiohns of MySQL...

2) yes. You should avoid using swap as much as possible, especially on a database, which get's all it's speed from keeping data in memory.

3) The 97%idle just means the ON AVERAGE your CPU uses about 3% of it's computing power. It could use that 3% to handle your load plus keep 500 mysql processes running.
The CPU is not your problem, it's simply MySQL that is running out of free connections.

Also, the number of hits/day says nothing, it's the concurrency that causes problems. If you get 10 concurrent visitors, your system load will be much higher than when you get 20 hits in sequence.

4) "Based on what you said in your article, it seems a bad idea to have multiple databases on our server"

Not at all! it is only 'bad' when used with pconnect, because every database adds X more connections to your requirements.
100 apache children with 10 databases=1000 connections. But, without pconnect you will get 100 apache-children with 1 connection each, and that's only 100 connections.

5) without pconnect, one apache child can run one script at a time, and thus use one database connection at a time. Those connections can be to as many different databases using as many differenty users as you like. Point is that as soon as the script ends, the connection is closed, freeing up the memory for the next connection.

6) Yahoo just use several servers that replicate data to eachother.
Plus, a company like Yahoo can afford to use larger servers, like SUN's 64-CPU machines. they will eat MySQL for lunch at it's highest possible load.(if they really do use MySQL, poor sods) Your single-cpu PC however will hand in it's notice probably even before you hit 500 connections.

Anon

Hey Vincent,

Thanks again.

So if I understood you correctly, there is no point in joing all network sites databases in one large database, correct?

As for Yahoo and MySQl, I got my info from MySQL site:

http://mysql.com/articles/us/yahoo_finance.html

They say their MySQL database handles 260 million record tables!

Cheers,

Freddie

[deleted]

"So if I understood you correctly, there is no point in joing all network sites databases in one large database, correct?"

In your case; none whatsoever.

About MySQL:
How many rows a database can hold is no measure for how 'good' it is. MySQL can work with 250M rows, but if you have three of those tables and they are linked by foreign-keys, you'll need to run three queries to safely delete one record. In PostgreSQL for example, that would be solved by adding a trigger. Not as fast, but 1000% more reliable.

Anon

Vincent,

Thanks a million for your help

Freddie