Todd Pinkerton

In my last post, I talked about how hard it was to find good resources for newbies to learn database tuning, especially with the very popular MySQL database -- the free or commercial version. This post will talk you through the basics of database tuning. Ready? here we go: 1) hardware is important. Ideally, your mySQL server will be on its own dedicated box. Sharing it with Apache and the rest of your application may be fine starting off, but eventually you'll want a dedicated machine for it. This also helps establish the right infrastructure for scalability, so you can add more webserver boxes which all connect to your database machine. So of course, things like RAM (speed and amount of), disk (speed and size of), and CPU horsepower all matter. Get as much as you can. 2) get the right tools. To even begin to tune the performance of your database, you need to be able to measure it. So get some good tools like * mytop (monitor mysql queries and performance) * ab (apache benchmark, for load-testing apache) * top (cpu/memory performance, process management) * iostat (disk/cpu performance) and and learn how to use them. Test your database using whatever queries and scenarios your ...

These days, everybody claims to be building the next YouTube, the next Google, "the Flickr of ..." something. They talk about slick AJAX web2.0 interfaces, or the awesome features they are building, or how great the content is. But deep down, every one of these Web2.0-could-be site's operator has a dreadful, paralyzing fear of the S-word: Scalability. Sooner or later, any moderately successful web application will need support a large number of users, lots of requests, and consume tons of bandwidth. I wonder what percentage of those "google adsense whores" paychecks go to paying their bandwidth bills? There exists all over the net tips to improve your website performance -- from configuring Apache with Worker threads, to tuning your CSS -- but usually the biggest performance bottleneck in a web application is the database. I was having a chat last week with a web startup (that shall remain nameless) about how hard it is to get support for their MySQL database. MySQL is the most popular open-souce and free database, but they also sell a commercial version. This company was trying to buy the commercial product -- trying to give MySQL their money! -- and having trouble getting the support they wanted. ...