Re: [midgard-user] tuning mysql: persistent connections to mysqlfrom midgard

[Martin Langhoff]

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]