Martin Langhoff wrote:
My team takes care of several websites with high traffic hitting PHP and
modperl scripts, that definitely hit mysql, and I agree with the post you linked to in <http://marc.theaimsgroup.com/?l=mysql&m=99130274006318&w=2> .
I don't think pconnect() is your problem at all. Indeed, it'll be _ease_ the load as soon as traffic grows a bit more, and certainly save the day when you separate the mysql daemon to a different machine. _Keep_ pconnect() and learn how to configure the mysql and apache daemons properly.
Properly configured, Midgard (as any decent LAMP-based app) will take you over a million hits a day on a lousy machine before you can really complain.
Apache comes with a default setting of maxclients 150 and maxrequests 0 (infinite). That makes sense for a static apache, where each daemon takes <300K of RAM and (hopefully) leaks no RAM. As soon as you start using mod_php or mod_perl -- which don't <ever, during the lifetime of an apache child> release RAM back to the system memory pool once they've allocated it, you need to (a) cut down the number of apache daemons, so trim down maxclients to 50 or so, and (b) recycle daemons often, reducing maxrequests to 20 or so.
mysql also comes with extremely lazy defaults that start acting up as soon as you see significant load. I suggest you read the documentation carefully and configure your my.cnf. First thing to do is to shorten the idle connection time, so unneeded persistent connections are dropped after a minute or two of being idle. Second thing is to match the number of mysql daemons to apache daemons, and then add a few for you to be able to connect and administer (also for crontabs that use mysqladmin to rotate logs and such).
Third, turn on slow query log -- I have a script that sorts the slow queries from one of our live servers, and emails the "top ten" to the programming team daily. We EXPLAIN those queries, and every once in a while we add a key or reorder a join.
If everything is smooth with max 50 httpd and max 55 mysqld, you might want to do memory math and calculate how much does each daemon pair consume, accounting for shared memory pages and such, to know how far to go with maxclients. A good reference is the mod_perl guide by Stas Bekman (Beckman?), as most of what is sadly true for mod_perl is sadly true for PHP as well. Also following that guide you might want to run a lightweight apache + heeavy apache setup. That's what we do.
After that, is very specific to your case. Allow for bigger tmp tables, change your join limits, etc. We decided not to PACK_KEYS, as we need to do some important INSERTs and UPDATEs interactively. Sometimes INSERT DELAYED can be used. Certainly avoid MyISAM tables if you can, as they are significantly slower (this has reversed in mysql v4.x). Use temp tables if it makes sense -- it is useful to minimize table locking on tables everybody is INSERTing into. ANALYZE your tables on a weekly (or daily) cron.
We also have a crontab that counts mysql active processes, and emails if there are more than 20 or so. For interactive monitoring, you can use mytop as well.
Again, I don't think pconnect() is evil -- it is you friend!. Apache and mysql come with naive configurations, but that is hardly anyone's fault. At a certain load level, you need to configure your server a bit -- defaults won't do. Add a droplet of monitoring and you won't have problems -- unless you get slashdotted, that is.
cheers,
martin
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Your recipe is interesting and I will look into it. From what I understand about the nature of apache 1.3.x is that apache child
processes are not well suited to persist with mysql threads. For example, if you create a php page with 3 mysql_pconnect() that
have different username or dbname parameters , then the child
server will make connection to a _new_ mysql thread, dropping the
old one. On the mysql end of things, it will keep the old threads
running and idle for up to 8 hours (28800 secs is the default config).
Linux+mysql must allocate memory for every open thread. So I have seen
cases of mysql server with very high load averages and high memory usage. 85% of the cases have been solved by reducing the "idle_timeout"
amount to seconds instead of (8) hours and
using connect over pconnect destroys the thread immediately.
This used to be more of a big deal in 2.2.x linux kernels.
the 2.4.x kernel brought huge improvments for servers including
better threading.
--
Vincent Stoessel
Linux Systems Developer
vincent xaymaca.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
