First, a bit of background.
I have a web app that does about 250k - 300k page views a day, w/ 150k uniques. At peak hours, I hit about 600 qeries per a sec. The app runs on a LAMP setup. I have 5 web servers behind a load balancer. Each webserver has a proxy that connects to a remote mysql database server thats managed by a startup which provides a db hosting service. The mysql server is set w/ a max_connection=1000. I have seen warnings of opening more connections than this setting.
From talking to the engineer at the db hosting service, my problem is that I'm creating too many mysql connections which are very expensive and have a lot of overhead. According to him, if I implement connection pooling, it would reduce the load in the number of connections I'm making and improve the overall realiability and speed.
I believe the closest thing to connection pooling in php is the mysql_pconnect() call. I've spent a good day researching this function call, and from what I've read, creating perstistent connections to the mysql server should help me out. If you have experience that states otherwise, please let me know! There's a lot of conflicting information on the mysql_pconnect call on the internet, and I want to be sure I have my facts straight.
I am aware of some of the "gotchas" that suround mysql_pconnect( ie. transactions, saving mysql variables, etc...). I have spent a good deal of time reading the comments regarding the mysq_pconnect function in the php manual. What I would really like help on is preparing my webservers and mysql to use this call.
1. Keep the wait_timeout on mysql config low. This is the most common tip I've seen on the interent. I'm gonna try 15 secs.
2. Tweak the thread_cache_size. Currently, I have it set at 8.
3. Limit the number of apache processes on each server so I don't open more connections than the mysql server can handle.
Step 3, is where I'm a bit lost on. I know the mysql server's max_connection settings is set at 1000. If I have 5 webservers, that would mean each webserver should only have a max of 200 apache process running. How do I do this?
Replacing all my mysql_connect() calls w/ mysql_pconnect() makes me really nervous. Therefore, I would like to only replace this call on the portions of the code that execute the most popular queries. Is there any reason why this approach would notmake sense?
Also, if I'm missing anything or you believe there's a flaw in my decision to use mysql_pconnect, please let me know.
Thank you!
help preparing for mysql_pconnect usage
Moderator: General Moderators
-
mechamecha
- Forum Commoner
- Posts: 32
- Joined: Thu May 31, 2007 8:49 pm