In this blog post, I will look at disk utilization and saturation.

In my previous blog post, I wrote about CPU utilization and saturation, the practical difference between them and how different CPU utilization and saturation impact response times. Now we will look at another critical component of database performance: the storage subsystem. In this post, I will refer to the storage subsystem as “disk” (as a casual catch-all). 

The most common tool for command line IO performance monitoring is

iostat

, which shows information like this:

root@ts140i:~# iostat -x nvme0n1 5
Linux 4.4.0-89-generic (ts140i)         08/05/2017      _x86_64_        (4 CPU)
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          0.51    0.00    2.00    9.45    0.00   88.04
Device:         rrqm/s   wrqm/s …

Problem: You've a large table (or two) in a database on a partition that's running out of space, and you want to see if you can move that table to another drive.

Solution: Well, several actually. No silver bullet, but several options, some with conditions and some that require preparation. Let's look at some background information first.

How MySQL Stores Data 

OK, that's somewhat of an ambitious heading for an incidental paragraph or two, so to tone it back a bit, I'll summarise briefly.

• The data directory is where MySQL stores databases, and it's set by the datadir server option. Each database is stored in a subdirectory of the data directory. You can also save a considerable amount of space without moving data around, by …

Indexes are a very important part of databases and are used frequently to speed up access to particular data item or items. So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to store these indexes, because unless you understand the inner working of an index, you will never be able to fully harness its power.

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 with LVM, together with setting up LVM volumes and in a later part will also show you how to extend...

We added a 500GB 7.2K SATA/300 Hitachi Deskstar E7K500 16MB disk to one of our dev servers and partitioned using fdisk and formatted the partition with etx3. When we tried mounting the same, we got the following error :

[root@xyz user]# mount -t ext3  /dev/sdb1 /mysql
mount: /dev/sdb1 already mounted or /mysql busy

lsof didn’t provide any open files that might be linked to this problem or there was any “famd” running. Finally doing the following steps to remove the logical devices from the device-mapper driver helped us fix the problem.

[root@xyz user]# dmsetup ls
ddf1_44656c6c202020201028001510281f033832b7a2f6678dab   (253, 0)
ddf1_44656c6c202020201028001510281f033832b7a2f6678dab1  (253, 1)

[root@xyz user]# dmsetup remove ddf1_44656c6c202020201028001510281f033832b7a2f6678dab1
[root@xyz user]# dmsetup ls
…

I’ve been wanting to write a backup script for a while now that does the following: reads the partition information for the directory that you are backing up into and computes the used/available/percentage-available space. Then it reads the total data size from the MySQL tables and ensures that you have enough space on disk (for [...]

MySQL Cluster is well known as an in-memory database which can be restrictive (a server typically has a RAM limit) so it may surprise some to learn that you can also store MySQL Cluster data on disk.  Disk Data Tables have been in MySQL Cluster for quite a while, the first GA release with them included was 6.2.  They do have caveats which I will describe here, many of which already have plans to be improved in future versions.
How Disk Data Tables work

A set of UNDO logs and data files can be created which are on every data node in the cluster.  The UNDO logs bring the disk data back to a consistent point so that during node restart the REDO logs can be replayed when a node is started.

Not all data in a disk table is actually stored on disk, the main two things that are not are indexed columns (the entire column is in main memory, not just the index) and the first 256 bytes of a …

Every hour, we saw the diskspace on our root partition go up from 45 %  to 90 % and come back to 45 %. Initially thought mysql was processing some temp tables and in the next hour we kept watching the temp location. In terms of files / diskspace usage using “du / ls” nothing changed. But lsof provided an interesting output.

Output 1 :

mysqld    31719   mysql    6u   REG                8,1          0   149240 /var/tmp/ibOLLuRa (deleted)
mysqld    31719   mysql    7u   REG                8,1        102   149243 /var/tmp/ibNuSvI9 (deleted)
mysqld    …