MySQL Benchmarking
I’ve spent a little time recently doing some simple benchmarking on our MySQL servers. MySQL comes with a nice little benchmarking suite in the sql-bench directory. When I started I was expecting to get a general idea of how our servers were performing under their current configuration. I didn’t expect to discover configuration errors, but discover them I did. I learned our “open files” limit was set too high. I checked the operating system kernel variable and it indicated the operating system would handle 4096 open files / process. However, as soon mysql would exceed 1024 open files it would fail. I set the mysql file limit to 1024 and the benchmark tests completed without any trouble.
So what did I learn from the benchmarking? It’s time to buy some new servers. When I started this job I assumed our servers wer top of the line, in terms of performance. What the benchmarking indicated was the mysql server I have running on this little dual processor pentium 3 machine, in my basement, was outperforming our UNIX servers by quite a bit. I checked into our server specs and discovered they’re only dual 400mhz processor machines (NOTE the benchmarking suite does not utilize dual processors, since the process is serialized). That explains a lot. We’ve started to run into some serious performance issues recently and it appears our processor load is getting to be too much to handle. Fortunately I was able to spread the load across both servers (originally one of them was entirely a test server), which has alleviated our load problems temporarily. That should keep us going until I can replace the servers.
Anyway, if you have some time, play around with the benchmarking suite. It was interesting to see how things changed by modifying a few variables here and there. It was particularly interesting to see the effect on performance by simply changing the tables from myisam to innodb. The sql-bench suite will give you a good idea of general performance but if you want to get serious about optimization I hear the MySQL super-smack program is the way to go. It’s completely flexible and allows you to create custom bechmark tests that will simulate requests and loads your production server will likely be handling.
August 28th, 2004 at 1:17 pm
Hey Jeff, What’s the status of wireless internet access at and around BYU? Is this something available to students living off campus but nearby? Just wondered what you see as the options are for my daughter who is no longer in a dorm on campus.
August 29th, 2004 at 11:01 am
The wireless network can be accessed by all students, however I’m not sure how far it extends off campus. Honestly, I’ve been a little disappointed in the wireless access on campus. I’ve tried using it in the building I work in and I lose the connection every few minutes or so.
There’s still a couple options for your daughter. If she’s living in an apartment, I know a lot of people who setup up a cable modem connection with a wireless modem and share it among 4 or 5 apartments. Also, it’s quite possible she can already access an open wireless network, there’s quite a few around provo. When I lived in Provo before, I noticed several open wireless networks within range of my apartment. Anyway, those are a couple ideas off the top of my head.
August 30th, 2004 at 7:12 pm
Thanks, Jeff. We’ll check ‘em out.