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 central memory hog. There are relatively simple ways 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 wouldn’t be sure. Do something to track your earlier numbers. Get top processes reading via the “top” function in SSH. I recommend Supersmack for more advanced benchmarks for those more advanced users on where your troubles are coming from.
2. mtop… Use it
mtop is an excellent 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 optimize the queries better. 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.
–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 plan out your database and queries properly 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 apparent, but you’re stuck on what to do with them or how to fix them. Now is an excellent time to run the ‘explain’ command in your SQL. ‘Explain’ breaks down the query and tells us what precisely the query is running through, be it an entire table or a few indices. This can be an invaluable tool for showing proper places to find a JOIN or create an index to grab cached information for your problem query quickly.
There are ways I can go into detail about each of these topics and even further into how to optimize specific types of queries. For the novice MySQL optimizer, these will set you up in the right direction.
Have these tips helped you get your MySQL process down? Do you have a better solution?