How MySQL Uses Disk Space

Data Directories

MySQL stores the location to the folder containing the database in the variable called datadir and the binaries in basedir.

mysql> show variables where variable_name in ('datadir', 'basedir');
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| basedir | /usr |
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
2 rows in set (0.00 sec)

Database Folder

Each database corresponds to a single folder under the datadir variable value regardless of what tables you created. The database is storage engine independent. One database can contain tables with different storage engines.

mysql, test and performance_schema are folders for each individual database.

root@ubuntu:/var/lib/mysql# ls -l
total 122912
-rw-r----- 1 mysql mysql 56 Aug 11 11:19 auto.cnf
-rw-r--r-- 1 root root 0 Aug 11 11:19 debian-5.7.flag
-rw-r----- 1 mysql mysql 291 Aug 11 16:11 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Aug 11 17:28 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 11 17:28 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 11 11:19 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Aug 11 16:11 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Aug 11 11:19 mysql
drwxr-x--- 2 mysql mysql 4096 Aug 11 11:19 performance_schema
drwxr-x--- 2 mysql mysql 12288 Aug 11 11:19 sys

Lets create test database.

mysql> create database test;

Check the file system in the/var/lib/mysql folder and notice the test directory created for the test database.

root@ubuntu:/var/lib/mysql# pwd
/var/lib/mysql
root@ubuntu:/var/lib/mysql# ls -l
-rw-r----- 1 mysql mysql 414 Aug 11 11:19 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Aug 11 11:19 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 11 11:19 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 11 11:19 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Aug 11 11:19 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Aug 11 11:19 mysql
drwxr-x--- 2 mysql mysql 4096 Aug 11 11:19 performance_schema
drwxr-x--- 2 mysql mysql 12288 Aug 11 11:19 sys
drwxr-x--- 2 mysql mysql 4096 Aug 11 15:40 test

Tables

A table format file (.frm) is created for each table regardless of the storage engine. The file are placed under the specific database folder.

MyISAM creates a .myd for for stroing the table data and .myi for indexes.

InnoDB stores the data in a tablespace or in a.ibd if the server option innodb_file_per_table is on.

InnoDB Storage Engine Files

The InnoDB storage engine uses tablespaces and logfiles. The tablespace contains data and index information for all InnoDB tables. If the innodb_file_per_table is on, each table which contain its own data in its onw tablespace.

mysql> create table person (first_name varchar(50) primary key) engine=innodb;Query OK, 0 rows affected (0.01 sec)

Check the filesystem and notice the person.ibd tablespace created for the person table.

root@ubuntu:/var/lib/mysql/test# ls -l
total 112
-rw-r----- 1 mysql mysql 65 Aug 11 17:27 db.opt
-rw-r----- 1 mysql mysql 8572 Aug 11 17:28 person.frm
-rw-r----- 1 mysql mysql 98304 Aug 11 17:28 person.ibd

The default tablespace name is ibdata1 and the default log files are named ib_logfile0 and ib_logfile1.

MySQL Server Logs

By default no logs are enabled, except the error log on Windows.

The General Query Log

Established client connections and statements received from clients .

mysql> show variables like 'general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/ubuntu.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)

The Binary Log

The binary log contains "events" that describe database changes such as table creation operations or changes to table data. The binary log is not used for statements such as SELECT or SHOW that do not modify data. Only complete transactions are logged.

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)

The Slow Query Log

The slow query log contains SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively.

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/ubuntu-slow.log |
+---------------------+--------------------------------+
2 rows in set (0.01 sec)

Administrative queries and queries without using indexes are not logged.

mysql> show variables where variable_name in ('log_slow_admin_statements', 'log_queries_not_using_indexes');
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
| log_slow_admin_statements | OFF |
+-------------------------------+-------+
2 rows in set (0.00 sec)

The DDL Log

Records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE. MySQL uses this log to recover from crashes occuring in the middle of a metadata operation. The log file file is kept under the data directory with the file name dd_log.log