mysql and freebsd (4.6.2)

[ultraslacker]

Anyone had good experiences with these two? I've played around with optimization levels, kernel settings, followed recommendations from mysql.org, etc. AND mysql is still a cpu hog!

Seems to stay around 15% of CPU but can rocket up to 80% - white knuckles time because that is a production server! I've never seen this behaviour running it on linux.

[Sxooter]

Almost sounds like it can't get buffer memory or something, so it's having to access everything from the drives each time or something.

Have you cranked up your cache settings in mysql?

If that doesn't fix it, maybe you should try postgresql and see if it has a problem on that box. I could be a kernel setting for something like shmmax or whatnot.

[ultraslacker]

I've cranked up the mysql configurations.

set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=400
set-variable = sort_buffer=10M
set-variable = record_buffer=10M
set-variable = thread_cache=32
set-variable = thread_concurrency=4
set-variable = max_connections=1000
set-variable = open_files_limit=5000
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=64M

I have been running it with the following kernel options -

added for postgresql

options SYSVSEM
options SYSVMSG
options SEMMAP=256 # amount of entries in semaphore map
options SEMMNI=256 # number of semaphore identifiers in the system
options SEMMNS=512 # number of semaphores in the system
options SEMMNU=512 # number of undo structures in the system
options SHMSEG=256
options SHMMAXPGS=16384

I also have postgres on the box, which is behaving. Originally I was worried about the box, as it is an athlon 1600 (1 gig of ram), but the system is just occupied with mysql - and there isn't much database activity showing in processlist.

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
788 mysql 38 0 408M 12608K RUN 0:18 25.13% 25.00% mysqld
655 nobody 2 0 7372K 4940K sbwait 0:00 0.39% 0.39% httpd
792 nobody 2 0 5376K 3136K sbwait 0:00 0.15% 0.15% httpd
890 nobody 2 0 5764K 3536K sbwait 0:00 0.20% 0.10% httpd
897 nobody 2 0 5416K 3132K sbwait 0:00 0.11% 0.05% httpd
286 root 2 0 5196K 2588K select 1:25 0.00% 0.00% httpd
89290 nobody 18 0 6772K 4912K lockf 0:57 0.00% 0.00% httpd
89288 nobody 18 0 6808K 4884K lockf 0:56 0.00% 0.00% httpd
89295 nobody 2 0 6884K 5004K sbwait 0:56 0.00% 0.00% httpd
89296 nobody 2 0 7312K 5180K sbwait 0:55 0.00% 0.00% httpd
89294 nobody 18 0 6832K 4908K lockf 0:55 0.00% 0.00% httpd
89289 nobody 2 0 6836K 4972K sbwait 0:55 0.00% 0.00% httpd
89291 nobody 18 0 6936K 5012K lockf 0:55 0.00% 0.00% httpd
89287 nobody 18 0 6772K 4900K lockf 0:55 0.00% 0.00% httpd
89293 nobody 2 0 6896K 5016K sbwait 0:55 0.00% 0.00% httpd
89292 nobody 2 0 6880K 4948K sbwait 0:54 0.00% 0.00% httpd
126 root 2 0 2532K 1344K select 0:18 0.00% 0.00% named
97491 nobody 18 0 6932K 5000K lockf 0:14 0.00% 0.00% httpd
97441 nobody 18 0 6776K 4732K lockf 0:14 0.00% 0.00% httpd
97440 nobody 2 0 6804K 4712K sbwait 0:13 0.00% 0.00% httpd
76 root 2 0 960K 420K select 0:07 0.00% 0.00% syslogd
28647 pgsql 2 0 55392K 2048K select 0:05 0.00% 0.00% postgres

[Sxooter]

Well, I'd guess that it's either improperly indexed, or your queries aren't optimized for MySQL. What kind of queries are eating up all that CPU time?

[ultraslacker]

If that were the case I would expect to see this high cpu usage on linux - which I haven't. And I get a high cpu usage even with no activity on database. sigh

Thanks for your help, though!

[Sxooter]

Oh, it's doing that with no queries running? Wow, that's pretty bad. Anything in the logs of mysql or bsd give a clue? I'm at a loss as to what's causing it. I'm no mysql expert though, I much prefer Postgresql.

[ultraslacker]

Me too, although I'm forced to use mysql for the moment. I feel like an idiot for plunging into freebsd without testing if mysql had problems with it.

Postgres runs great on it though. 😃

[Sxooter]

I looked on google and found a few posts around the net about MySQL having high CPU utilization on BSD, and one of them mentioned that having a high record_buffer setting can cause that.