MySQL Problems On Mac OS X Server

by Shawn @ 6:59 pm · filed under Server Admin

For the last two months or so, I've been having a strange problem with my primary MySQL Server that required that the mysqld process (not the server itself) be restarted. The first image shows the CPU usage of the server, with the red arrows being the points I restarted the mysqld process (queries per second and types of queries do not change over time).

The server itself is a dual processor Xserve G5 with 5GB RAM, 1.2TB drive space, etc. so resources really are not an issue. Also, I should point out that no other services (like a web server) are running on the machine (it's strictly MySQL only). Basically the longer the database server ran, the slower it would become (there are no "bad" queries anywhere either). After 24 hours it was slow enough that it needed to be restarted. I tried tweaking MySQL config options, throwing more memory at various aspects of MySQL.

While watching the processlist, I noticed something strange a few times. Something that should be blazingly fast (like an insert into a tiny HEAP table) was taking a VERY long time (sometimes 90 seconds), and not only that it was hanging all the query threads (even ones hitting unrelated databases). So then I had the idea to look at the query cache since that's shared by all databases, and when you update a table, it needs to flush the queries from the cache for that table (which would explain why unrelated databases' queries would hang while it was flushing the query cache for the HEAP table being updated). So to test this theory I rolled the query cache memory allocation from 256MB to 2MB. Low and behold... it worked! In my situation, the less memory you allocate to the query cache, the faster MySQL was. Then I remembered something that was added to Mac OS X 10.3 (I believe) and that's the ulimit functionality. It's actually a nice feature and can prevent a runaway process from taking down a machine. But in my case it was preventing MySQL (which really is the only process) from using very many resources. Still not sure why it would slowly get worse, but maybe it has to do with the more memory MySQL wanted to use over time, the more swap disk memory it was forced to use. Who knows... and to be honest, I don't care, I'm just glad it works now.

Once I got that squared away, this is what my MySQL server CPU usage looks like:

Now, hopefully someone out there will find themselves in the same problem and this will be your solution, rather than spend months screwing with it (this ulimit stuff would apply to other BSD variants like FreeBSD, not just Mac OS X).

Run this from the shell (as root):

sysctl -w kern.maxfiles=122880
sysctl -w kern.maxfilesperproc=102400

This allows a single process (mysqld in my case) to have up to 102,400 files open at once at the kernel level (more than enough).

Add this to your /etc/sysstl.conf file (or create it if needed):

kern.maxfiles=122880
kern.maxfilesperproc=102400

This makes the settings work when you reboot the machine.

Edit the /Library/StartupItems/MySQLCOM/MySQLCOM script that MySQL installs (it's part of the automatic startup package), and somewhere near the beginning, add this:

ulimit -n 7000
ulimit -c unlimited
ulimit -d unlimited
ulimit -s 65536

You could put it somewhere else, but I chose this file since it doesn't get overwritten when you upgrade MySQL. This lets the mysqld process use more than the default per user resources that Mac OS X Server allows.

You may want to adjust your settings as you see fit for your situation, but that's what I used.

I'm just happy it's no longer a problem for me!!!!! Yaaaayyy!!!!

Permalink

5 Responses to “MySQL Problems On Mac OS X Server”

Allison Says:
October 11th, 2005 at 11:31 pm

God dammit, Hogan…let’s get back to pics of peeing statues and the like. Your last three posts might has well have been in Portuguese.

Tom Says:
February 12th, 2006 at 5:03 pm

We’ve been struggling with a similar issue. Our website is fine (everything on one Xserve). Then, when people start making updates to content, MySQL slows to a crawl and forces us to restart Apache to kill pending threads.

Any more to share on this issue? We’ll give your suggestions a try ASAP.

Thanks for sharing!!!

Gary Says:
November 7th, 2007 at 11:44 am

Seeing a similar issue here, running MySQL 5.0.45 (32-bit) on a dual-Xeon Xserve with Mac OS X server 10.4.10. Our query cache is set at 1 MB (it has been all along), and we still developed this problem. The weird thing is that the server had been running well for several months, and then just recently started exhibiting the issue - now we can’t get rid of it, and have to resort to restarts.

Have you run across any further insights on this problem? Thanks for posting your experiences - it’s always nice to know you’re not alone in the wilderness…

Command-line PHP script CPU usage goes sky-high, stays there--why? | keyongtech Says:
January 21st, 2009 at 8:29 pm

[…] Command-line PHP script CPU usage goes sky-high, stays there–why? Hello, I have a command-line PHP script–called Listener–that is designed to run indefinitely with a predictable CPU usage and memory footprint. In a nutshell, it’s a multi-client socket server that waits for incoming connections, processes incoming data, stores results in a MySQL database, and basically gets on with its life. And it works. No errors or serious problems to speak of. And I’ve been running it for a couple years on an Xserve dual-G5 2GHz w/ OS X Server 10.4.11). Six months ago, the program would run for days, even a couple weeks, without a hitch. The only reason I would stop the script is for some other purpose, like a software update. However, the number of socket clients connecting in the past 3-4 months has steadily increased, and this seems to have exposed (if not created) a strange performance "issue" with PHP 5.2.4, MySQL 5.0.45 and/or Mac OS X Server 10.4.11. (I say "and/or" because I am unsure where the problem’s cause really lies.) Basically, after the script has been running for a day or so (processing essentially the amount data that used to take two weeks), the CPU usage of the machine goes from 30% (normal) to 80-90%. This appears to be irrespective of the number of clients connected to the server at the time, but rather the amount of time the script has been running (and therefore cumulative cycles it’s operated, data processed, MySQL queries executed, etc.). And the CPU usage stays high, even when the actual load (number of clients) decreases. At this time, if I run top, I get the following info: 22512 mysqld 91.6% 8:22:12 31 106 125 305M+ 3.20M 260M 475M 17104 php 7.2% 81:14.01 1 15 145 5.08M 7.80M 10.9M- 87.5M 22537 Terminal 6.6% 2:59:59 22 176 240 12.3M 21.2M 18.3M- 236M […] At first I thought, okay, it’s MySQL’s fault. Maybe a lot of slow- running queries. But the slow query log is pretty clean. So maybe it’s a combination of Mac OS X and MySQL and PHP? I Googled for a similar problem, and finally ran across this article: http://www.shawnhogan.com/2005/10/my…-x-server.html ….where the author describes a very similar CPU usage pattern. I tried his suggested fixes and they seemed to have helped a little (or maybe it’s my wishful thinking–hard to tell), since the high CPU load issue doesn’t appear to happen as soon… But it still happens. Anyway, I’m really stumped as to what to do next, where to look, etc. If I stop the script, and restart it (but not the MySQL itself), CPU usage goes back to normal–for about a day or two. The only thing I thought might be connected is how many short-lived PHP child processes Listener creates–around 20-30,000 per day. Sounds higher, but on average it’s just one every 2-3 seconds. Anyway, although the number of child processes isn’t concurrent, would there be a problem with the number of historical child processes in view of ulimits or kern.maxfilesperproc? Anyway suggestions, tips, or links are much appreciated. Thanks. ….Rene […]

Jo Says:
May 25th, 2009 at 2:39 pm

Hogan, you are the hero today! I’ve been fighting here with a brand new Mac Pro, 2 Quad Core Xeons, 12 GB of memory, raid disks and the whole company turned their heads on me, because I had the exact same issue with my MySQL Server becoming slower all the time! I pushed the maxfiles and the maxfilesperproc ten times up like you suggested and voila - everybody is happy again

(I only see now, that your post is as old as from 2005, but the issue is obviously still relevant in 2009 with Mac OS X 10.5.7 and MySQL 5.1.34)

Thanx a lot for the great hint!

Jo

Leave a Reply