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!

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

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 .

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