Jeff wrote:
>
> Ugh...
>
> mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep"
>
> And it returned 200 sleeping connections, all persistant connections
> from our app servers and 4 threads_running
>
> Also a show status gave me a max_used_connections of 236.
>
> If that's the case then I can probably only set it to about 250 which
> means if I set my innodb_buffer_pool_size = 100M and dropping my
> key_buffer_size to 250, I'll need 1884M of ram according to the formula
> above, which is dangerously close to the 2G limit specified in the
> warning on the link above.
>
> Currently the key_reads to Key_reads_requests is about 1:1970 with the
> key_buffer_size of 384M, so I guess I can safely drop this to 250M
>
> Even if I changed the entire DB over to InnoDB, and pushed the
> key_buffer_size down really low it wouldn't drop the total memory usage
> below 1600M.
>
> So what is this telling me? I need more ram or less connections or I
> should just stay with MyISAM?
>
> Thanks,
>
> Jeff
>
I would suggest taking a hard look at why your application servers are
creating 200 sleeping connections, and if that is necessary. You may
also be able to reduce sort_ and read_buffer_size to 1M each, but I
couldn't tell you how that might affect your application, so you may not
want to do that. (Does anyone on the list have experience modifying these?)
I think the biggest issue will be the system's RAM - the 2G limit on
MySQL's total allocated RAM is a per-process hard limit on 32-bit
architecture, but most 32-bit systems benefit greatly from having more
than 2G total RAM (the OS may use the rest for disk caching, etc). If,
say, your server had 4G RAM, then you could safely configure MySQL to
use very close to 2G, and performance should fly. With only 2G in the
system, setting MySQL to use as much RAM as possible would leave next to
nothing for the OS or other processes, and that is the problem (as I see
it).
However, that said, more RAM is not always the answer. You may get much
more of a performance increase by modifying your application code so
that it doesn't "waste" so many connections (thus allowing you to
allocate plenty of RAM to the innodb_buffer_pool).
Of course, you can do both (just to play it safe, right?). ;)
Best Regards,
Devananda vdv
