Making your servers a bit more "green" with smart system adminstration, part 4: MySQL databases

Armijn Hemel, May 16, 2010, 4812 views.

"Green IT" can only work if you have a combined approach of optimization on all levels. Knowing how to get the most out of the components you are using is a must.

Tags: , ,

Database optimizations

Many websites these days are database driven, so a badly configured database installation has direct impact on the performance of a site. A properly configured database means less resources are wasted. In this article we will highlight a few things we have encountered in the past.

Overhead in MySQL databases

As we said in an earlier article about quick and dirty MySQL optimizations keeping your databases tidy is good for performance. With the script in that article you can get rid of the cruft that can accumulate in the database. But cleaning out the attic is not the only optimization that is possible. For example, it won't help with two common performance issues: the amount of database connections and the use of an index on a database.

MySQL database connections

Once we got a cold call from a company who had a severe performance issue and they needed a "Linux nerd" to solve it and one of their employees remembered us from our university days. Their new website had just gone live and was getting a lot of traffic, but the website was incredibly slow most of the time, but once a request "got through" it was very quick. The load on the servers was averaging 0.00 so there had to be some bottleneck, but they didn't know where. A quick look at the MySQL settings learned that the amount of database connections was way lower than the amount of hits they were getting. We set it to 25 times as much and restarted MySQL, and the performance issues were gone.

Using an index on MySQL databases

MySQL allows you to set one or more indexes on one or more database columns. Without an index you would have to search through all rows in a database table every time you make a query. With an index you sacrifice some diskspace for a massive speedup. The wikipedia article about database indexes nicely explains the background.

There are plenty of websites where the database is small enough to make searches without an index bearable, but it makes it hard to scale to bigger databases, or to more sites. We always try to pick the right indexes on our databases when setting up new sites. So far we seem to doing quite OK.

Don't use a database

The best way to prevent that a database is the bottleneck is to not use a database when it is not necessary.

There are many sites that use a database to dynamically generate a page, even when this is completely not necessary, because the content of the page is always the same. If this is the case you can use a templating engine such as smarty to generate pages, write them to a cache and serve them statically. The database is only used during the generation process, but not when serving the pages. For us this has worked really well. Of course, there are plenty of pages where dynamic content is needed and we still need a database for that. But, when we don't have to use a database, we won't.

Social networking: Tweet this article on Twitter Pass on this article on LinkedIn Bookmark this article on Google Bookmark this article on Yahoo! Bookmark this article on Technorati Bookmark this article on Delicious Share this article on Facebook Digg this article on Digg Submit this article to Reddit Thumb this article up at StumbleUpon Submit this article to Furl

Talkback

respond to this article