Database Optimization

Databases are the key to fast and secure websites. There are a few aspects to consider:
  • Server side configuration
  • Optimized and normalized database structure
  • Scalable and efficient SQL Queries
Not only your website database has to be optimized / normalized but also the SQL queries should be efficient. 

Server side configuration

Having your server optimized for MySQL is not easy. Fortunately there are numerous tools to help such as MySQL tuner. This tool helps by providing recommendations for the  MySQL configuration after examining your server resources and MySQL settings. 

Besides tuning your MySQL configuration file you can also mount your /tmp to the server RAM. This is done by specifying /tmp as tmpfs in /etc/fstab as follows:

none /tmp tmpfs size=1G,nr_inodes=100k,mode=01777 0 0

Note: The above setting will require reboot or manual mount / remount of the /tmp. Also, this might already been done so check it first to make sure you are not duplicating the settings. The above means that /tmp will be mounted in RAM with 1GB space and a limit of 100 000 inodes.

Next thing that helps is using a separate hard disk for MySQL data directory. Both these enhancements should help decrease the I/O wait considerably.

Optimized and normalized database structure

SQL Database optimization is a straight forward process in which you go through normalization steps. We will have a new tutorial about it very soon.

Scalable and efficient SQL Queries

The last absurd SQL query I saw was the one in OsCommerce products info page. There is an option similar to 'Clients who bought this item also bought...'. I am sure this is a nice feature to have in your shop but the query was:

# Query_time: 48 Lock_time: 0 Rows_sent: 229 Rows_examined: 65929
select * from orders_products opa, orders_products opb, orders o, products p where opa.products_id = 'XXXX' and opa.orders_id = opb.orders_id and opb.products_id != 'XXXX' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc;

Having such a query on a shop with 10s products and 100s orders shouldn't be a big deal. However, as seen from above there are thousands of products and orders.  This made the server (8 cores Xeon, 12 GB ram) perform a 48 seconds MySQL slow query. 

No matter what optimizations you make this query cannot be fast. There could be slight improvement if you mount your /tmp in memory, for example. You could also try caching as many similar queries as possible. Though it is simply not feasible to cache all the queries for XXXX products, not to mention that this cache should be purged from time to time.

This example comes to show that queries have to be well-designed with scalability in mind. If you have any doubts - simply test before going into production.

blog comments powered by Disqus