Re: [PLUG] Optimizatoins? MySQL, 1.2Gb tables, lots of random selects...

[Mike Cramer <cramer@webkist.com>]

Kyle R . Burton wrote:
> They're persistient.  Once the worker process connects to the database, the
> connection stays open.

I use mysql in a situation that does many many small queries and found 
there were a few places in which the default settings of Mysql just 
didn't work out under linux. In particular the "table_cache" and 
"thread_cache_size" variables. We were running into a situation where 
persistant connections would cause us to hit the max_connections limit, 
but increasing the max_connections would cause the response time to fall 
off until the machine just couldn't get anything done.

Increasing the table_cache and thread_cache allowed us to turn off 
persistant connections and respond to far more queries than we could 
otherwise. Persistant connections are like "keep-alive" in http. It 
works reasonably well for moderate loads, but the overhead doesn't allow 
it to scale very well. Particularly if the connections are not reused 
often enough. But creating a new connection from scratch in mysql is 
actually pretty painless and as a result, makes persistance less 
necessary. Particularly since most Apache-centered persistance seems to 
amount to just caching the database handle instead of some sort of more 
intelligent connection pooling mechanism.

Anyway...if you are doing large infrequent queries, the "table_cache" 
might be the thing to tune. If you are doing frequent small queries, 
turn off persistance, and increase your "thread_cache_size".

Oh...and all of this tuning was on a PII 800 running Linux kernels 
>2.4.16 or so and Mysql server version 3.23.36 (and recently upgraded 
to 3.23.49). As with all tuning, your hardware/os specifics will surely 
change the sweet-spot significantly.

--
Mike Cramer
http://www.webkist.com/


______________________________________________________________________
Philadelphia Linux Users Group       -      http://www.phillylinux.org
Announcements-http://lists.phillylinux.org/mail/listinfo/plug-announce
General Discussion  -  http://lists.phillylinux.org/mail/listinfo/plug