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 direc...

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.NA...

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? &nbsp 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. &nbsp 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). &nbsp 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. &nbsp If innodb_file_per_table was always enabled (new default for 5.6!) th...

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!