How many files does InnoDB have open?

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$”

3 thoughts on “How many files does InnoDB have open?”

  1. If you want to check the open files for the mysql instance in question you can check with this command assuming you have the sudo access or the root login credentials.

    [root@qw-p-dbc2-node3 ~]# lsof -p 18284 |grep ibd |wc -l
    298

    where 18284 is the process id of my mysql instance. which you can get with this

    [root@qw-p-dbc2-node3 ~]# ps -ef|grep mysql

    1. Yep so the post already notes the lsof approach – the reason for the post was about the need for an *internal* way to see how many files are open. That is, a status variable you can check from with mysql.

      But it’s an old post (2009). There is now a way, in MariaDB with XtraDB you have a status variable Innodb_num_open_files.

  2. MySQL 5.6 and MariaDB 10.0 introduce Innodb_num_open_files
    This little addition only took about eh, almost 5 years ;-)
    I should’ve just submitted a patch for that…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>