MySQL 5.1 Threads

U

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.
 
maybe try going back to mysql 5.0? The 5.1. series is pretty new GA release...
 
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.
 
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 :-(
 
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.
 
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.
 
unixeagle said:
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.
Click to expand...

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.
 
[solved]: MySQL 5.1 Threads

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, 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
 
You must log in or register to reply here.
Back
Top