Where am I wasting resources? How to fix this problem?

[Mel Flynn]

On Thursday 30 April 2009 21:49:13 Chuck Swiger wrote:
> Hi, VeeJay--
>
> On Apr 30, 2009, at 12:02 PM, VeeJay wrote:
> > Guys, I am not very good on freebsd, its you guys who help me to
> > keeping my
> > server up... I hope you can spare a few minutes to sort this
> > problem...
> >
> > last pid: 19656;  load averages:  1.00,  1.00,
> > 1.00
> > up 2+05:00:12  19:18:47
> > 3049 processes:2 running, 3047 sleeping
> > CPU: 12.5% user,  0.0% nice,  0.1% system,  0.0% interrupt, 87.4% idle
> > Mem: 6253M Active, 3810M Inact, 921M Wired, 128K Cache, 214M Buf,
> > 4683M Free
> > Swap: 32G Total, 32G Free
> >
> > PID USERNAME    THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU
> > COMMAND
> > 830 mysql      1500  44    0  1670M   813M ucond  1   0:00 100.00%
> > mysqld
>
> It sure looks like you're running into a system limit with the maximum
> # of threads available to the mysql process.

There's no such limit, see pthread_create(3) and pthread.h:
#define PTHREAD_THREADS_MAX                     __ULONG_MAX

but the 1500 is suspicious. Suspicious enough to be a MySQL configuration 
value or compile time option. The only way to get to the bottom of it, is to 
watch the number of threads in the mysql process and attach ktrace to it the 
moment it approaches 1500, to see if pthread_create actually does return 
EAGAIN and get a hint as to where. My suspicion however is that the thread 
abstraction of MySQL sets EAGAIN.

A my.cnf certainly would help.
-- 
Mel