FreeBSD and MySQL - mysqld eats CPU alive

[Alasdair Lumsden]

On Sun, 2004-08-01 at 04:08, adp wrote: 
> FreeBSD 4.4, 4.7, 4.9, 4.10
> MySQL 3.x and 4.x
> Typical load: 50 qps
> With and without replication enabled.
> Some sites are SELECT heavy, some are INSERT heavy.
> 
> For one site I think we will be moving from FreeBSD to Linux for the MySQL
> servers since MySQL seems to run like a champ on Linux. We will continue to
> use FreeBSD for everything else.
> 
> Anyone experienced this problem? Is it mysqld or FreeBSD? I can't pinpoint
> the exact issue.

We've got MySQL running on several FreeBSD servers here, some doing 100
qps+, and have had no problems what so ever. We've been using MySQL 3
and 4 on FreeBSD since FreeBSD 4.5 upwards.

Here is an example status output from one of our servers:

# /usr/local/mysql/bin/mysqladmin -u root -p status
Enter password:
Uptime: 1271242  Threads: 8  Questions: 76203621  Slow queries: 370 
Opens: 2461857  Flush tables: 2  Open tables: 511  Queries per second
avg: 59.944


You haven't mentioned the source of your MySQL installs. Are you using
MySQL from ports, packages, or a binary or source distribution from the
MySQL website?

We compile MySQL from source using the following:

setenv CFLAGS "-O3 -mpentiumpro"
setenv CXX gcc
setenv CXXFLAGS "-O3 -mpentiumpro -felide-constructors -fno-exceptions
-fno-rtti -DEAPI"
./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/mysql/var
--with-unix-socket-path=/var/lib/mysql/mysql.sock
--with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static
--enable-assembler


You also haven't mentioned specifically what happens when there is a
problem.. You say the machine becomes unresponsive.. what happens if the
queries die down, does the box become responsive again?

The only thing I can think of causing this is if queries start to come
in faster than the box can process - they will build up and up until the
machine, as you say, becomes unresponsive. This would indicate some form
of bottleneck somewhere.

Monitoring the output of "systat -vmstat" might give some clues as to
whats going on.

I would certainly investigate the cause of this before abandoning
FreeBSD. Any threads on google relating to FreeBSD and MySQL not working
together are vastly inaccurate or out of date.

There are some performance issues with MySQL on FreeBSD 4 with regards
to threading. Try using MySQL linked against LinuxThreads, which should
give a performance boost. You can specify -DWITH_LINUXTHREADS when
installing from ports.

We have a test box running FreeBSD 5.2.1 with MySQL 4, linked against
the new kse threading library, which should in theory provide
performance comparable/superior to MySQL on a linux box. This box has
been completely stable - no MySQL or FreeBSD lockups or crashes.

Given that 5.3 and -STABLE are due out soon, it might be worth waiting
for those, rather than switching to Linux.

However we, and many many other people, run high load MySQL
installations on FreeBSD without problem, so I would say your problems
are specific to your setup, and not something that is a widespread
issue.