Re: Mysql seems to be bogged down

[Erin ONeill]

Hey Mike -
there are some pretty good db tuners looking at your problem. I'm a bit concerned with some of your my.cnf settings. I think upgrading your OS and APPs really will boost a lot of things but I'm afraid you'll use the same settings in your my.cnf file.

These concerned me a bit:
sort_buffer_size = 50M
read_buffer_size = 50M
join_buffer_size=50M

You have max connections set to 200. So let's ignore your global buffers for a second. With the thread buffers math you need the following RAM:
150(50+50+50) * 200 = 30 GB

You have how much ram?? I don't remember you saying you had 35 GB of ram??

I cut and pasted those settings above because I thought my eyes were fooling me. These are usually set MUCH lower (mine are around 1-2M).

I'm on MySQL 4.0 so I don't know if you can SET GLOBALS in 3.23?? if you can then:
SET GLOBAL sort_buffer_size = 2M;

otherwise you've got to change your my.cnf and flip the mysql server. :(

That could be why MySQL is stalling out -- you're using up all your RAM. Once you make those changes (be sure to set the slow_query_log while you're at it) then optimize your queries.

I love watching queries leave my slow_query_log! :)

have fun.
erin