[nycbug-talk] Re: MySQL

[Hans Zaunere]

Francisco Reyes wrote on Monday, August 22, 2005 3:22 PM:
> On Sun, 21 Aug 2005, Hans Zaunere wrote:
> 
> > Try turning on query caching.
> 
> That alone has been very helpfull. Utilization was 50% to 70% and was
> doing mostly 20 to 70 queries/second. Now utilization is
> mostly under 40%
> and QPS is 40 to 120 QPS.
> 
> > But before any of that, get the right binary.  The number one
> > problem with MySQL on FreeBSD is that people use the wrong binary.
> 
> I will eventually try that. I see is as a riskier proposition. The cache
> setting is a win win (little risk, performance increase, cpu
> utilization reduction).

Ehh, well - watch MySQL on a four-way SMP FreeBSD 4.7 box and come back to
me :)

You can skirt around the right binary, but especially when you start getting
higher concurrencies, it won't be fun.  There's little fuss/risk, but do be
aware of MySQL's version changes (noteably 4.0 -> 4.1).

> Any suggestions on values for the cache size? Started at 64K, then 128K
> now at 256K.

All depends on the schema, hit ratios of the cache after some uptime, and
how much RAM you have.

> Also the docs don't indicate if this memory is coming from the currently
> used memory pool, or if this is in excess of that. The one machine I am
> trying right now, is already using 128MB of swap so I am very
> concerned about memory.. Looking to see what I can move out of that
> machine. 

Again depends on schema - noteably what storage engine.  Different storage
engines do different things.  If you're swapping - especially if you're
using MyISAM, pull back on some buffer sizes.  The query cache is a fixed
size, so if you set it to something, it won't grow.  Other things will,
including the kernel's reliance on disk caches.

H