One way to enhance database performance is to break large database files into smaller chunks. These smaller chunks can either be all on the same hard drive, or they can be spread across different hard drives. Either way, this process is referred to as “partitioning” the database. But, there are a few problems with this process.
First, partitioning capabilities have to be compiled into your implementation of MySQL. These capabilities are compiled into the MySQL implementations that come with Red Hat Enterprise Linux 6 and CentOS 6, but they are not compiled into the MySQL implementations that come with Red Hat Enterprise Linux 5 and CentOS 5.
There are two ways to see if your implementation of MySQL supports partitioning. Using the mysql client, log into MySQL and enter:
show variables;
If you don’t see a line that says “have_partitioning YES”, then your server does not support partitioning.
You can also enter:
show plugins;
+————+——–+—————-+———+———+
| Name | Status | Type | Library | License |
+————+——–+—————-+———+———+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
+————+——–+—————-+———+———+
7 rows in set (0.00 sec)
If you don’t see a line that says “partition ACTIVE”, then your server does not support partitioning.
Secondly, the database partitioning process is largely beyond your control as a Linux administrator. You can set up the different drives to store the databases, and you can tweak the MySQL settings, but the actual database partitioning is done by MySQL database programmers. They would create the different database partitions by using special code when creating the database tables.
Lastly, there’s the fact that the two major MySQL storage engines handle partitioning in different manners. The “MyISAM” engine allows the database programmer to place different chunks of the database files on different drive partitions, while the “InnoDB” engine tends to ignore those directives.
Not to worry, though. There is an easy way to enhance database performance by placing the tables for different databases on different drives. It’s not quite as good or flexible as true database partitioning, but it’s better than nothing. Here, we’ll present two procedures. One is for MyISAM, and the other is for InnoDB.
MyISAM
When using the MyISAM engine, all files for a particular database will be stored in a directory that has the same name as the database. These directories will be created in the “/var/lib/mysql” directory.
To place different databases on different drives, simply create a partition on another drive in the usual manner, create a mount point directory, and then mount the partition. For our purposes, we’ve created the “/dev/sdb1” partition, and we’ve mounted it as “mysql_home”. If you have many databases, you can add as many drives as your machine will allow and place a different database on each one.
Now, ensure that the MySQL daemon is stopped, and use “mv” to move a database directory to the drive partition that you just mounted. Your command would look something like:
sudo mv db1/ /mysql_home/
Next, create a symbolic link to that directory within your “/var/lib/mysql” directory.
sudo ln -s /mysql_home/db1 /var/lib/mysql/db1
Restart the MySQL daemon, and you should be good.
InnoDB
By default, the InnoDB storage engine also creates separate database directories, but only uses those directories to hold files with formatting information for the databases. InnoDB, by default, stores all database tables for all databases in the “/var/lib/mysql/ibdata1” file. As you can imagine, this could result in a table file that would eventually become very large and very unwieldy.
To change that behavior, open the “/etc/my.cnf” file in your text editor, and add the following line:
innodb_file_per_table
Save the file, and restart the MySQL daemon. Now, whenever your programmers create a database using the InnoDB engine, a separate table file will be created for each database. Also, the table files for each database will be stored in their associated database directories, just as it happens with MyISAM.
The remaining steps are exactly the same as what we’ve already outlined in the MyISAM procedure.
