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.

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!

MySQL: Every detail matters

Some bugs can have a high impact if it causes data corruption, security issues or simply causes MySQL to crash. But what about small bugs and small usability issues?

This entry from the MySQL 5.6.8 release notes is an interesting one:
InnoDB: On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration option and the actual size of the ib_logfile* files that make up the redo log. This behavior required manually removing the redo log files after changing the value of innodb_log_file_size. The fix causes MySQL to write all dirty pages to disk and re-create the redo log files during startup if it detects a size mismatch. (Bug #14596550)

So previously you had to shutdown MySQL without fast shutdown enabled, then move the ib_logfile's away and start MySQL again. This is not too hard or complicated. But I have seen some serious issues related to this procedure.

Someone changed the log file size in my.cnf anticipating to execute the procedure in a day or two, but then someone else restarted MySQL. Then the MySQL storage engine was not registered, so the data was not available. Then someone thought that MySQL was at fault and restored all tables from the latest dump. As the NO_ENGINE_SUBSTITUTION sql_mode was not set the tables were restored as MyISAM tables.

This fix not only prevents issues like this but it also helps to easily automate the resize of the logfiles on multiple machines. And it makes it possible to change the logfile size with puppet.

I would like to see more fixes of small bugs and usability issues, just like the Every Detail Matters project for GNOME. It helps to keep MySQL a user friendly database.

There are more fixes like this in MySQL 5.6, for example the updated defaults.

One of the things I would like to see to be fixed is the combination of features. For example it is not possible to use:

• partitioning and foreign keys
• InnoDB compression with combined table spaces (or raw disk tablespaces)
• spatial indexes and InnoDB 
• Full text parsers plugins and InnoDB

MySQL 5.6 already fixed a number of similar issues. It's now possible to use full text indexes and InnoDB and to use read-only InnoDB.

Instant InnoDB

I've reviewed the Instant InnoDB book. It's a good starting point if you're learning about InnoDB, which is used for most MySQL installations.

As a consultant I've seen many situations where some basic InnoDB setting (buffer pool size, log file size, file per table) can have a huge effect on the performance of the database and make it easier to manage.

The website of the book can be found here.

Running Percona XtraDB Cluster in a Sandbox on Ubuntu

I wanted to do some experimentation with Percona XtraDB Cluster (Galera) and I didn't want to use virtual machines. I'm already using MySQL Sandbox for many other projects so that's the natural choice.

I've downloaded the tarball for Percona XtraDB Cluster 5.5.29 and I've extracted it to ~/opt/mysql/5.5.29-pxc.

Then I've installed 3 nodes:
make_sandbox 5.5.29-pxc -- --sandbox_port 4551 \
--sandbox_directory msb_5_5_29-pxc01 

make_sandbox 5.5.29-pxc -- --sandbox_port 4552 \
--sandbox_directory msb_5_5_29-pxc02 

make_sandbox 5.5.29-pxc -- --sandbox_port 4553 \
--sandbox_directory msb_5_5_29-pxc03 

But when I try to start a node this error happens:
130327 14:21:03 [Note] WSREP: wsrep_load(): loading provider library '/home/dvee
den/mysql/5.5.29-pxc/lib/libgalera_smm.so'
130327 14:21:03 [ERROR] WSREP: wsrep_load(): dlopen(): libssl.so.10: cannot open shared object file: No such file or directory
130327 14:21:03 [ERROR] WSREP: wsrep_load(/home/dveeden/mysql/5.5.29-pxc/lib/libgalera_smm.so) failed: Invalid argument (22). Reverting to no provider.

There is no libssl.so.10 on my Ubuntu 12.10 system, but there is a libssl.so.1.0.0.

The fix for this issue is:
cd ~/opt/mysql/5.5.29-pxc/lib/
ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 libssl.so.10
ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 libcrypto.so.10

The symlinks are created inside the library directory for Percona XtraDB Cluster, this way I don't need to tinker with the global library directories.

MyISAM in a MySQL 5.6 InnoDB Only instance

With MySQL 5.5 the default storage engine was changed to InnoDB. This was a good step as most users expected MySQL to support transactions, row level locking and all the other InnoDB features, but with 5.1 they sometimes forgot to choose the right storage engine. As most databases don't have multiple storage engines and many MySQL users changed the default storage engine to InnoDB this made the switch to MySQL easier and the default behaviour more in line with what people expect from a relational database.

Changing the storage engine can of course be done on a per table or per instance basis (default-storage-engine in my.cnf). The temporary tables created with 'CREATE TEMPORARY TABLE ...' should not be forgotten. The performance of InnoDB or MyISAM for temporary tables can have quite some impact, especially with slow storage, a buffer pool which is too small to hold the complete dataset or very small temporary tables. In MySQL 5.6 there is a new variable introduced to set the default storage engine for temporary tables: default_tmp_storage_engine. This makes it possible to use MyISAM or MEMORY as default temporary storage engine, which could benefit performance in some cases.

A great new feature of MySQL 5.6 is the full text indexing support for InnoDB. Now you can switch your fulltext tables to InnoDB! (after careful testing of course as results may differ)
So do we still need MyISAM? The answer is Yes.

The system tables (mysql.*) are mostly MyISAM. The general log and slow query log tables are in CSV format. In 5.6 there are also some InnoDB tables: innodb_index_stats, innodb_table_stats and optionally slave_master_info and slave_relay_log_info. It's not supported to change these MyISAM tables to InnoDB.

mysql> SELECT engine, COUNT(*) FROM information_schema.tables
    -> WHERE table_schema='mysql' GROUP BY engine;
+--------+----------+
| engine | COUNT(*) |
+--------+----------+
| CSV    |        2 |
| InnoDB |        5 |
| MyISAM |       21 |
+--------+----------+
3 rows in set (0.00 sec)

The implicit temporary tables as created by MySQL (e.g. not with CREATE TABLE) are still in MyISAM format. This can be shown by running some query which uses 'Using temporary; Using filesort' and while the query runs looking in the tmpdir location:

Session1:

select a.user,a.host,b.user from mysql.user a, mysql.user b, mysql.user c,
mysql.user d ,mysql.user e,mysql.user f, mysql.user g, mysql.user h order by a.user,b.user

Session2:

mysql> select @@tmpdir;
+----------------------------------------+
| @@tmpdir                               |
+----------------------------------------+
| /home/dveeden/sandboxes/msb_5_6_10/tmp |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> \! ls -l /home/dveeden/sandboxes/msb_5_6_10/tmp
total 4044
-rw-rw---- 1 dveeden dveeden 4120272 Mar 18 13:30 #sql_69cd_0.MYD
-rw-rw---- 1 dveeden dveeden    1024 Mar 18 13:30 #sql_69cd_0.MYI

And the third one: mysqldump uses MyISAM.
A view is first restored as a MyISAM table and after all views are restored then they are converted to 'real' views. This is needed as views might be dependent on each other.
With performance_schema it's possible to 'see' the usage of MyISAM. With 5.6 not everything is enabled by default, so first check the setup_* tables to see what's enabled and what's not. In setup_objects the system tables are disabled, which can easily give wrong results if you're interested in MyISAM usage.

mysql> SELECT * FROM performance_schema.events_waits_summary_global_by_event_name 
    -> WHERE event_name LIKE 'wait/io/file/myisam/%'\G
*************************** 1. row ***************************
    EVENT_NAME: wait/io/file/myisam/data_tmp
    COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0
*************************** 2. row ***************************
    EVENT_NAME: wait/io/file/myisam/dfile
    COUNT_STAR: 145525
SUM_TIMER_WAIT: 4793712275400
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 32940608
MAX_TIMER_WAIT: 2244115016
*************************** 3. row ***************************
    EVENT_NAME: wait/io/file/myisam/kfile
    COUNT_STAR: 329
SUM_TIMER_WAIT: 17011553368
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 51706768
MAX_TIMER_WAIT: 236143040
*************************** 4. row ***************************
    EVENT_NAME: wait/io/file/myisam/log
    COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0
4 rows in set (0.00 sec)

mysql> SELECT event_name,object_name,COUNT(*),SUM(TIMER_WAIT) FROM performance_schema.events_waits_history_long 
    -> WHERE event_name LIKE 'wait/io/file/myisam/%' GROUP BY event_name,object_name\G
*************************** 1. row ***************************
     event_name: wait/io/file/myisam/dfile
    object_name: /home/dveeden/sandboxes/msb_5_6_10/tmp/#sql_69cd_0.MYD
       COUNT(*): 84
SUM(TIMER_WAIT): 1120085576
*************************** 2. row ***************************
     event_name: wait/io/file/myisam/kfile
    object_name: /home/dveeden/sandboxes/msb_5_6_10/tmp/#sql_69cd_0.MYI
       COUNT(*): 6
SUM(TIMER_WAIT): 501999104
2 rows in set (0.02 sec)

Some possible sources of high(er) MyISAM usage:

• The mysql.proc table might be read often if you're often calling stored procedures. (seen with 5.5). This might also be true for events.
• Monitoring which checks things (too) often.
• Usage of server side help

And InnoDB doesn't support spatial indexes, so if you need them you still need MyISAM. Storing spatial information can be done with InnoDB, so if you can do without index you don't need MyISAM:

mysql> CREATE TABLE geo_m (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i),
    -> SPATIAL KEY (g)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE geo_i (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i),
    -> SPATIAL KEY (g)) ENGINE=InnoDB;
ERROR 1464 (HY000): The used table type doesn't support SPATIAL indexes
mysql> CREATE TABLE geo_i (i INT NOT NULL,
    -> g GEOMETRY NOT NULL, PRIMARY KEY (i)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

If you're interested in performance_schema, make sure to attend the MySQL Virtual Developer Day.

So MyISAM is still a critical part of MySQL and you should not just forget about it, even if you're only using InnoDB.