On Wed, Mar 01, 2000 at 01:48:30AM +0800, Silver CHEN wrote:
> There are more than 15 servers in this service, and under some
> conditions, queries will 'jammed'. 150-200 are just an 'average'
> value, and it won't cause the problem I described.
>
> I have to 'reduce the probability' of 'jammed' conditions. Add
> CPU is the only way that I can imagine now. Breaking DB, multiple
> instances are not what I want, like I said.
>
> I use FreeBSD for many years,
First off, MySQL on FreeBSD cannot currently make use of SMP because
of the userland pthreads library. You might try using the LinuxThreads
port (a port of the Linux threading library to FreeBSD - this is _not_
the Linux emulation. You might try the Linux emulation, too, though :-).
Secondly, you haven't provided evidence that the problem is CPU
related. Try using /usr/sbin/iostat to monitor CPU and disk load
on the machine. Even better, work out a means of graphing these
values (I recommend MRTG, Yahoo can lead you to it), and watch
what the graphs look like over a couple days. What you're highly
likely to see is that the iostat tps values will tend to plateau or
even cup during peak loads. This would indicate that you are completely
I/O bound.
Of course, if the CPU idle time is always <25% or so, then your
SMP thought is most likely correct.
FreeBSD pthreads also has another problem with MySQL. Since disk I/O
is always non-blocking, pthreads basically serializes all disk I/O
for a process, meaning that you can't have multiple pending requests,
meaning that the OS can't do interesting optimizations like elevator
sorting. Running two instances of MySQL (do _not_ use --skip-locking!)
can alleviate this. I've been running multiple instances on a production
machine for over a month, now, and really made things faster. I've
got some pthreads patches to fix this problem, but haven't had time
to put them into production, yet.
[Warning: If you run with multiple instances on the same database,
be very very very careful when doing maintenance. For instance, if
you alter a table from one instance while the second instance has the
table open, the second instance will retain a reference to the
original table until such time as it closes the table files - which,
due to the table cache, will probably be never! This also applies
to optimizing tables, and to things like "delete from TABLE;" (no
where clause). I drop back to a single MySQL instance before
running that type of command.]
Later,
scott
