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