One of my biggest vices in developing websites and applications online is MySQL. The majority of servers I have had the opportunity to look through in their top processes list, MySQL is the major memory hog. There are fairly simple wasy to combat the extreme memory and process usage on your server/hosting plan.

1. Start with Benchmarks

Without initial benchmarks there will be nothing to base the results of all the MySQL changes on. It is possible to say yeah something made all of my processes go down…but well you wouldnt be sure. Do something to track your earlier numbers. Get a top processes reading via the “top” function in SSH. For those more advanced users, I recommend Supersmack for more advanced benchmarks on where your troubles are coming from.

2. mtop… Use it

mtop is a great tool for showing the active MySQL processes going through your database, much like the linux ‘top’ command. This program will help identify the bottlenecks in your processes to better optimize the queries. With better optimized queries, it reduces the load on the server and thus a better running site AND you will tend to gain more traffic with a better running site.

3. –log-slow-queries

–log-slow-queries is your new best friend in fighting the bogged MySQL menace. As basic a function as it may be, it will log the queries taking too long (according to what you set it to). I like to start with a slow query time of two seconds. Once I solve all of those problem points, I move on to a 1 second query time to kill any remaining long standing queries.

4. Have a Pre-Plan

Pre-Planning your website/app and database can save massive amounts of time in the future. Taking the time to properly plan out your database and queries will prepare you for the growth and future you want for your website/app. Think about the type of MySQL database you plan to use as well as the queries you plan to run on the database.

5. Explain Queries

The problem queries are obvious, but you’re stuck on what to do with them or how to fix them. Now is a good time to run the ‘explain’ command in your SQL. ‘Explain’ breaks down the query and tells us what exactly the query is running through, be it an entire table or a few indecies. This can be an invaluble tool for showing proper places to find a JOIN or create an index to quickly grab cached information for your problem query.

There are ways I can go into detail about each of these topics and even further into how to optimize specfic types of queries. For the novice MySQL optimizer, these will definitely set you up in the right direction.

Have these tips helped you get your MySQL process down? Do you have a better solution?