MySQL 5.1 Threads

[unixeagle]

Hello All,

I run a vBulletin board on my servers. I recently installed 7.1 RELEASE-amd64.

My Setup is:

Intel Xeon 2xQuad Core 2.33GHz, 2x6 MB cache, 8 GB RAM and 15K SCSI hard disk.

mysql51-server, php5.2.9 and nginx/0.6.35.

Nginx is connected to the php-cgi back end through a unix socket. I used spawn-fcgi from: http://redmine.lighttpd.net/projects/spawn-fcgi/

Now, the forum runs well for some time, and all of the sudden, all the php-cgi spawned process are stuck in sbwait state.

If I restart mysql server, all php-cgi process are back to accept state. Here is where I noticed the problem is not related to the web server or to it's php-cgi backend. If I attach a gdb to one of the frozen php-cgi processes and get a backtrace, I see it is in read() in /lib/libc.so.7.

Attaching a gdb to the mysqld process, I always get the error in libthr.so.7.

I think there is a problem with the mysqld threads, as when I try to /usr/local/etc/rc.d/mysql-server stop, I cant get it to stop, instead, it restarts and I can see it got a new PID. Each time I issue killall -9 mysqld, I get a new mysqld PID and mysqld never dies.

Reading a lot of articles online, I found out a problem with mysql and the freebsd threading libraries. Most of the articles were outdated though, back in 2003 and 2002, but I think mysql and freebsd are still having troubles with each other.

I tried to compile mysql with all possible options, build_static, optimize and use_fast_mutexes but nothing seemed to work.

I have a lot of faith in freebsd, and I'm so depressed with this problem. I know I must be missing something, but I ran out of ideas and my brain is really numb, please help.

Regards.

[danger@]

maybe try going back to mysql 5.0? The 5.1. series is pretty new GA release...

[phoenix]

Kris Kenneway did some MySQL and PostgreSQL benchmarks last year that showed MySQL 5.0 to be much faster than 5.1. If possible, you should try a downgrade to MySQL 5.0.

As for the exact nature of the issue you are facing ... no idea. Sorry.

[unixeagle]

Thanks a lot, I'll give 5.0 a try. I'll let you know about how is it going.

Regards.

[danger@]

BTW, in that time MySQL 5.1 wasn't supposed to be a GA release by mysql.com, however it is now.

Anyway, I have observed some problems with mysql locking itself, where I am unable to kill it in a normal way. It happens suddenly and restarting it through rc.d script simply doesn't work. I have to manually kill -9 the mysql pid, and I always end up with damaged tables :-(

[Mel_Flynn]

Is this with or without fast mutexes or does it make no difference?

[SirDice]

Have a look at table locking, slow-query logs and InnoDB vs. MyISAM. We've had quite a lot of MySQL perfomance problems (on Solaris). Just by optimising the filesystem (no file caching vs. caching), changing certain tables to InnoDB and optimizing queries (slow-query log) we where able to improve performance dramitically.

[unixeagle]

danger@, yes that is exactly what happens with me, I can't restart mysql using the rc script. I have to manually kill it with kill -9, the problem is it sometimes doesn't respond to the -9 signal, instead, I see the mysqld pid has changed, and mysqld is still running. This is the behavior of -15 signal. The only way to kill it is: for x in {1..100}; do killall -9 mysqld; done.


Mel_Flynn, I tried to compile 5.1 with fast_mutexes, I got a little bit of improved performance, but the problem still there.

SirDice, I'm running a vBulletin 3.8.x, so I believe the queries are optimized and the code is written in a performance in mind manner. Otherwise, I totally agree with you about the file system optimization, and the query optimization.


Anyways, I started to suspect that the cause of my problem is a hardware related. It is not like I have a corrupted disk or something, but I think I need to separate the database server from the webserver. Or, at least install the data dir of mysql onto a separate physical drive. Thus, mysql and nginx are not fighting each other for I/O activity.

I'm in the process of doing this. I'll update you with the results when I finish.

[Mel_Flynn]

danger@, yes that is exactly what happens with me, I can't restart mysql using the rc script. I have to manually kill it with kill -9, the problem is it sometimes doesn't respond to the -9 signal, instead, I see the mysqld pid has changed, and mysqld is still running. This is the behavior of -15 signal. The only way to kill it is: for x in {1..100}; do killall -9 mysqld; done.


First kill off the mysqld_safe wrapper script, then -9 mysqld.

I suspect this to be a locking problem, it usually takes a few releases of a new minor branch to get these fixed, as FreeBSD users are generally more conservative about upgrading core components. The key being: complain @ mysql.com's bugzilla and keep pinging reports till it's fixed.

[unixeagle]

Hello,
It's been a while since my last reply, I've been busy fixing this problem.

Anyhow, I think my suspects were right. When the load increases on the board, the I/O operations become a problem.

I installed mysql on another server, and things went very well since that.
I benchmarked this setup with siege (freshmeat.net/projects/siege), and I could get a 600 concurrent hits with no problem.

I compiled mysql from ports, I used 5.0.77 and did a BUILD_OPTIMIZED=YES and BUILD_STATIC=YES.

Thank you guys for the help and supporting ideas. :beer