MySQL’s innodb_file_per_table is a wonderful thing – most of the time. Having every table use its own .ibd file allows you to easily reclaim space when dropping or truncating tables. But in some use cases, it may cause significant performance issues.

Many of you in the audience are responsible for running automated tests on your codebase before deploying to production. If you are, then one of your goals is having tests run as quickly as possible so you can run them as frequently as possible. Often times you can change specific settings in your test environment that don’t affect the outcome of the test, but do improve throughput. This post discusses how innodb_file_per_table is one of those settings.

I recently spoke with a customer whose use case involved creating hundreds of tables on up to 16 schemas …

When innodb_file_per_table is OFF and all data is going to be stored in ibdata files. If you drop some tables of delete some data then there is no any other way to reclaim that unused disk space except dump/reload method.

When Innodb_file_per_table is ON, each table stores data and indexes in it’s own tablespace file. However, the shared tablespace-ibdata1 can still grow and you can check more information here about why it grows and what are the solutions.

http://www.mysqlperformanceblog.com/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

Following the recent blog post from Miguel Angel Nieto titled “ …

Read the original article at Thoughts on Upcoming MySQL 5.6 Defaults

During Oracle Open World 2012 and the parallel MySQL Connect conference, the new 5.6 version was announced. It’s only release candidate right now, but that means the GA release is just around the corner. With that James Day has posted changes to various of the new parameter defaults. Many of them you may not run [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. 5 Ways to Boost MySQL Scalability
2. 5 Ways to fortify MySQL …

You could say: what could be the reason for having really big number of tables? Just design the application properly! It’s not always that easy. And this post isn’t really about arguing whether having many tables is good or not, it’s about what happens in terms of memory usage if you already reached that point.

Btw what do I mean by *many*? From my experience it’s tens of thousends or even millions rather than hundreds.

The inspiration for me to write this post was strong desire to try out the latest declared improvements in that area announced to be done in MySQL 5.6. _1

What I did was a very simple test where I loaded sql dump of databases and tables definitions only into different versions of MySQL. During the load time I was monitoring memory usage of mysqld process (RSS …

There are a lot of scalability challenges we see with clients over and over. The list could easily include 20, 50 or even 100 items, but we shortened it down to the biggest five issues we see.

1. Tune those queries

By far the biggest bang for your buck is query optimization. Queries can be functionally correct and meet business requirements without being stress tested for high traffic and high load. This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn't necessarily be a good use of time to tune a query for some page off in a remote corner of your site, that didn't receive real-world traffic. So some amount of reactive tuning is common and appropriate.

Enable the slow query log and watch it. Use …

Let me just start off this post saying if you're not interested in InnoDB tablespace internals, then this might not be the post for you. :)

At any rate, whilst examining individual tablespace files (the .ibd file created when running with innodb_file_per_table option enabled) in their binary format, I noticed the initial page size for a individual tablespace did not appear to be 16k (as the default InnoDB page size is 16k).

Upon examining the actual binary data stored in the data file (just create a basic table and insert 10 rows), you can see data is written at the very beginning of the file (position 0).

One can also see data appearing at several (4) other places in this file.

I was trying to sync up what I was seeing with these InnoDB internals:

…

There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in ibdata1 file and every InnoDB table has two files e.g.  actor.frm and actor.ibd.

The annoying thing about .ibd files you can’t easily copy the an .ibd file to another MySQL server. If you try to very often you’ll get an error in the log:

InnoDB: Error: tablespace id is 10 in the data dictionary
InnoDB: but in file ./sakila/actor.ibd it is 15!

However sometimes you have to connect the .ibd file to an alien ibdata1.

There are several situation when you have to:

1. ibdata1 is erroneously removed

2. ibdata1 is …

Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.

#my.cnf
[mysqld]
innodb_file_per_table

The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.

• Backup all InnoDB tables via mysqldump
• Drop InnoDB tables
• Verify InnoDB not used
• Stop MySQL
• Enable innodb_file_per_table & simplified innodb_data_file_path (if applicable)
• Remove ibdata? files
• Start MySQL
• Create Tables
• Reload Data
• Verify InnoDB Operation

The primary reason is we are moving from …

Inspired by Baron's earlier post, here is one I hear quite frequently -

"If you enable innodb_file_per_table, each table is it's own .ibd file.  You can then relocate the heavy hit tables to a different location and create symlinks to the original location."

There are a few things wrong with this advice:

1. InnoDB does not support these symlinks.  If you run an ALTER TABLE command, what you will find is that a new temporary table is created (in the original location!), the symlink is destroyed, and the temporary table is renamed.  Your "optimization" is lost.
2. Striping (with RAID) is usually a far better optimization.  Striping a table across multiple disks effectively balances the  'heavy hit' access across many more disks.  With 1 disk/table you are …

If you use innodb_file_per_table = 1 and innodb_open_files = X (whatever amount is suitable for your server) there’s no way internal to MySQL for finding out how many IBD files InnoDB actually has open. Neither SHOW GLOBAL STATUS LIKE ‘innodb%’ nor SHOW ENGINE INNODB STATUS provide this information.

Many sites do have a growing number of tables, so you’ll want to know when it’s time to up the number (and potentially also open-files-limit). Solution: sudo lsof | grep -c “\.ibd$”