innodb_file_per_table

If there is one MySQL/MariaDB server option I’d say would be most important for sysadmins to know about it would be innodb_file_per_table. The thing that makes innodb_file_per_table important is that it improves the survivability of your data. It is enabled by default starting with MySQL 5.6 but is not in MariaDB – it must be explicitly set.

The traditional behavior of MySQL’s innodb engine is to store all its indexes in a single file, the monolithic ibdata, which tended to grow YUGE over time. The innodb_file_per_table option instead creates a separate index file (with an .idb extension) for each table in the database. As a result your changes of recovery after the corruption or accidental deletion of any given file is vastly improved.

Google around for discussions over the details of why I say that, and for other pros cons (the biggest con I’ve experienced is a marked drop in performance when doing bulk queries – like massive search and replace operations when cloning a WordPress instance).

This entry was posted in Database, Web on by .

About phil

My name is Phil Lembo. In my day job I’m an enterprise IT architect for a leading distribution and services company. The rest of my time I try to maintain a semi-normal family life in the suburbs of Raleigh, NC. E-mail me at philipATlembobrothersDOTcom. The opinions expressed here are entirely my own and not those of my employers, past, present or future (except where I quote others, who will need to accept responsibility for their own rants).