Sunday, May 26, 2013

Playing hid-and-seek with databases

As far as I know there isn't a well accepted set of best practices for MySQL, but there are many best practices known and used by most MySQL DBA's. One of those best practices is that the datadir must not be equal to a mountpoint; it has to be a subdirectory of a mountpoint. I learned this the hard way a few years ago when I used a NetApp Filer via NFS as a data directory. The NetApp filer exposed the snapshots via a .snapshot directory. A database in MySQL is a directory, so MySQL thought that the .snapshot directory was a database. This resulted in some issues with our monitoring scripts, so we had to create a mysql_data directory and move all the databases to that directory.

For other setups directories like lost+found, .zfs, etc. gave similar issues.

In MySQL 5.6 a new feature was introduced to make it possible to make some databases hidden. To do this a ignore-db-dir option needs to be specified for each directory. On a running server the list of ignored database directories is shown in the ignore_db_dirs variable.

mysql> SHOW DATABASES LIKE 'testdir%';
+---------------------+
| Database (testdir%) |
+---------------------+
| testdir3            |
+---------------------+
1 row in set (0.00 sec)

mysql> \! ls -ld data/testdir*
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir1
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir2
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir3
mysql> SELECT @@ignore_db_dirs;
+-------------------+
| @@ignore_db_dirs  |
+-------------------+
| testdir1,testdir2 |
+-------------------+
1 row in set (0.00 sec)

mysql> \! grep ignore-db-dir my.sandbox.cnf
ignore-db-dir='testdir1'
ignore-db-dir='testdir2'

This helps a lot if there are foreign directories in the datadir, but it's always better to not have those directories there as it might be loaded into the database with a LOAD DATA statement or it might get deleted with a DROP DATABASE statement. So the best practices is still to have a data directory which is a subdirectory of a mountpoint, not the mountpoint itself.

A hidden directory is still available and can be used, it's just not shown in the output of various commands.

Wednesday, May 15, 2013

Calculating the InnoDB free space - part 2

This is part 2, you can find part 1 here.

So in part 1 we learned how to calculate the free space within InnoDB. But unfortunately that won't always work perfectly.

The first issue: the DATA_FREE column in the INFORMATION_SCHEMA.TABLES table will not show a sum of the free space of each partition. This means that if you have innodb_file_per_table disabled and are using partitioning then you must divide DATA_FREE by the number of partitions.
This is Bug #36312.

Example:
mysql> SELECT CONCAT(T.TABLE_SCHEMA,'.',T.TABLE_NAME) AS TABLE_NAME,
    -> P.PARTITION_NAME AS PART,IBT.SPACE,IBD.PATH,T.DATA_FREE AS T_DATA_FREE,
    -> P.DATA_FREE AS P_DATA_FREE FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT JOIN INFORMATION_SCHEMA.PARTITIONS P ON P.TABLE_SCHEMA=T.TABLE_SCHEMA 
    -> AND P.TABLE_NAME=T.TABLE_NAME 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES IBT 
    -> ON IBT.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
    -> OR IBT.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME,'#P#',P.PARTITION_NAME) 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_DATAFILES IBD 
    -> ON IBD.SPACE=IBT.SPACE WHERE ENGINE='InnoDB' ORDER BY T.TABLE_SCHEMA,T.TABLE_NAME;
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
| TABLE_NAME                 | PART | SPACE | PATH                              | T_DATA_FREE | P_DATA_FREE |
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
| innodbfreespacetest.t1     | NULL |     6 | ./innodbfreespacetest/t1.ibd      |     4194304 |     4194304 |
| innodbfreespacetest.t11    | p2   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p1   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p0   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p4   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p3   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t13    | NULL |    46 | ./innodbfreespacetest/t13.ibd     |     4194304 |     4194304 |
| innodbfreespacetest.t2     | NULL |     0 | NULL                              |    80740352 |    80740352 |
| innodbfreespacetest.t5     | p1   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p0   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p4   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p3   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p2   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t6     | p4   |    15 | ./innodbfreespacetest/t6#P#p4.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p3   |    14 | ./innodbfreespacetest/t6#P#p3.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p2   |    13 | ./innodbfreespacetest/t6#P#p2.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p1   |    12 | ./innodbfreespacetest/t6#P#p1.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p0   |    11 | ./innodbfreespacetest/t6#P#p0.ibd |           0 |           0 |
| mysql.innodb_index_stats   | NULL |     2 | ./mysql/innodb_index_stats.ibd    |           0 |           0 |
| mysql.innodb_table_stats   | NULL |     1 | ./mysql/innodb_table_stats.ibd    |           0 |           0 |
| mysql.slave_master_info    | NULL |     4 | ./mysql/slave_master_info.ibd     |           0 |           0 |
| mysql.slave_relay_log_info | NULL |     3 | ./mysql/slave_relay_log_info.ibd  |           0 |           0 |
| mysql.slave_worker_info    | NULL |     5 | ./mysql/slave_worker_info.ibd     |           0 |           0 |
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
23 rows in set (0.05 sec)

This example is on MySQL 5.6.10.
Tables t1 and t13 have their own tablespace.
Table t2 is in the system tablespace.
Tables t5 and t11 are partitioned and in the system tablespace, these tables show the real DATA_FREE multiplied by the number of partitions. The DATA_FREE for individual partitions is correct.
 
For some old 5.1 versions the DATA_FREE might be show in kilobytes instead of bytes. (and there is no column in which the measurement unit size is stored)


Calculating the InnoDB free space

Recently someone asked my if it's possible to find the total free space within InnoDB. I thought this would be very easy as the INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we could just use SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES couldn't we?
 
So what does the DATA_FREE column tell us? It tells us the free data within InnoDB for that particular table. A table can share a tablespace with multiple other tables.
 
The tablespace which is used by a table depends on whether the innodb_file_per_table was enabled during table creation and/or at the last time the table was rebuild (e.g. by OPTIMIZE TABLE).
 
If innodb_file_per_table was always disabled then this query probably reports the correct free space:
SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;

This is because all tables will share 1 tablespace.
 
If innodb_file_per_table was always enabled (new default for 5.6!) then this would report the free space:
SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB';

This is because eache table will have it's own tablespace.
 
But how could we combine these two?
 
To give this a try I create a MySQL 5.6.10 sandbox with 4 tables of which only 2 have their own tablespace.
 
My first try is to use my udf_fileexists UDF:
 
mysql> SELECT TABLE_NAME,DATA_FREE,
    -> udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) AS ibd_file 
    -> FROM information_schema.tables WHERE ENGINE='InnoDB';
+----------------------+-----------+----------+
| TABLE_NAME           | DATA_FREE | ibd_file |
+----------------------+-----------+----------+
| t1                   |   4194304 |        1 |
| t2                   |   4194304 |        1 |
| t3                   |  66060288 |        0 |
| t4                   |  66060288 |        0 |
| innodb_index_stats   |         0 |        1 |
| innodb_table_stats   |         0 |        1 |
| slave_master_info    |         0 |        1 |
| slave_relay_log_info |         0 |        1 |
| slave_worker_info    |         0 |        1 |
+----------------------+-----------+----------+
9 rows in set (0.02 sec)

mysql> SELECT (SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE 
    -> NOT udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) 
    -> AND ENGINE='InnoDB' LIMIT 1) + (SELECT SUM(DATA_FREE) 
    -> FROM INFORMATION_SCHEMA.TABLES WHERE 
    -> udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) 
    -> AND ENGINE='InnoDB') AS TOTAL_DATA_FREE;
+-----------------+
| TOTAL_DATA_FREE |
+-----------------+
|        74448896 |
+-----------------+
1 row in set (0.03 sec)

 
So that works, but it requires loading a UDF. Luckily it's also possible to only use INFORMATION_SCHEMA by using the INNODB_SYS_TABLES table. This works only for 5.6 and newer.
 
mysql> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.DATA_FREE,ST.SPACE,SD.PATH 
    -> FROM INFORMATION_SCHEMA.TABLES T LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ST 
    -> ON ST.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_DATAFILES SD ON SD.SPACE=ST.SPACE WHERE T.ENGINE='InnoDB';
+---------------------+----------------------+-----------+-------+----------------------------------+
| TABLE_SCHEMA        | TABLE_NAME           | DATA_FREE | SPACE | PATH                             |
+---------------------+----------------------+-----------+-------+----------------------------------+
| mysql               | innodb_table_stats   |         0 |     1 | ./mysql/innodb_table_stats.ibd   |
| mysql               | innodb_index_stats   |         0 |     2 | ./mysql/innodb_index_stats.ibd   |
| mysql               | slave_relay_log_info |         0 |     3 | ./mysql/slave_relay_log_info.ibd |
| mysql               | slave_master_info    |         0 |     4 | ./mysql/slave_master_info.ibd    |
| mysql               | slave_worker_info    |         0 |     5 | ./mysql/slave_worker_info.ibd    |
| innodbfreespacetest | t1                   |   4194304 |     6 | ./innodbfreespacetest/t1.ibd     |
| innodbfreespacetest | t2                   |   4194304 |     7 | ./innodbfreespacetest/t2.ibd     |
| innodbfreespacetest | t3                   |  66060288 |     0 | NULL                             |
| innodbfreespacetest | t4                   |  66060288 |     0 | NULL                             |
+---------------------+----------------------+-----------+-------+----------------------------------+
9 rows in set (0.02 sec)

mysql> SELECT (SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ST ON 
    -> ST.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) WHERE SPACE<>0) + 
    -> (SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ST 
    -> ON ST.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
    -> WHERE SPACE=0 LIMIT 1) AS INNODB_FREE_GLOBAL;
+--------------------+
| INNODB_FREE_GLOBAL |
+--------------------+
|           74448896 |
+--------------------+
1 row in set (0.04 sec)
Calculating the free space can be useful if you don't have autoextend enabled on your datafiles (requires innodb_file_per_table to be disabled). It can also be useful to calculate how much space could be freed by running OPTIMIZE TABLE (requires innodb_file_per_table to be enabled).
 
Conclusion:
If you're on 5.6 you can use INFORMATION_SCHEMA and for 5.5 and earlier you have to use a UDF or have (a script) to peek in the MySQL datadir.

Tuesday, May 14, 2013

MySQL User Group NL Meetup May 31 at Snow

The third meeting for the MySQL User Group NL will be hosted by Snow B.V. in the Snow office in Geldermalsen.
 
The Agenda:
  • Choosing the Best Sharding Policy - Doran Levari (ScaleBase, using a video link)
  • Performance Monitoring with Statsd and Graphite - Art van Scheppingen (Spil Games)
  • Basic MySQL performance tuning for sysadmins - Daniël van Eeden (Snow)
Please RSVP on the meetup.com page.

The user group now has more than 100 members!