MySQL: Using Views as Performance Improvement Tools

The most basic and most oft-repeated task that a DBA has to accomplish is to look at slow logs and filter out queries that are suboptimal, that consume lots of unnecessary resources and that hence slow down the database server. This post looks at why and how VIEWs can help against such suboptimal operations.

On LVM: How to extend Volume Groups and Logical Volumes

In the previous part of this article, we had a look at how to configure LVM, and we had successfully setup a Volume Group and two Logical Volumes. Now we are going to have a look at extending the group and volumes.

On LVM: How to setup Volume Groups and Logical Volumes.

LVM (Logical Volume Management) is a very important tool to have in the toolkit of a MySQL DBA. It allows you to create and extend logical volumes on the fly. This allows me to, say, add another disk and extend a partition effortlessly. The other very important feature is the ability to take snapshots, that you can then use for backups. All in all its a must have tool. Hence, this guide will allow you to understand various terminologies associated

Continue Reading

Understanding MySQL binary and non-binary string data types

Having reviewed different table structures designed by different people, I have come to the conclusion that binary and non-binary string data types are used without consideration of the consequences of choosing either one. The confusion stems from the fact that both non-binary and binary string data appear to store characters because they can be saved as quoted string.

A few notes on locking in MySQL

This is another article in a series of articles titled "A few notes ..." in which I will be posting some important information about locking concepts, different types of locks and what locks table engines support. Just like the previous article, the purpose of this article is to highlight important aspects that you should have in the back of your mind when developing applications.

A few notes on InnoDB PRIMARY KEY

InnoDB uses an index-organized data storage technique, wherein the primary key acts as the clustered index and this clustered index holds the data. Its for this reason that understanding the basics of InnoDB primary key is very important, and hence the need for these notes.

Understanding InnoDB transaction isolation levels

Isolation is an important part of ACID properties that guarantee that transactions are processed in a reliable manner. But there are four different levels of isolation available and you have to understand each one of them to be able to select the correct one for your needs. This post intends on explaining the four levels together with their effects on locking and performance.

On Hot Backups and Restore using XtraBackup

Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. I had been evaluating backup solution for a while but to be honest I really wasn't satisfied with the solutions available until I came across XtraBackup and I am loving it since. In this post I intend on showing

Continue Reading

Setting up Master-Slave Replication with MySQL

Replication enables data from one MySQL server to be replicated on one or more other MySQL servers. Replication is mostly used as scale-out solution. In such a solution, all writes and updates take place on the master server, while reads take place on one or more slaves. This model is actually known as master-slave replication and this is the kind of replication that I will be setting up in this post.

Tuning important Apache parameters.

Although Apache performance can be improved by adding additional hardware resources such as more RAM, faster CPU, but most of the times, the real problem is that the webserver hasn't been configured properly and is not delivering optimal performance. Hence, the reason for this post is to get the important Apache parameters configured properly so as to get the maximum performance out of Apache with existing hardware resources