I have a datawarehouse on mySQL size being almost 1.4 TB when i checked the number of files the mysql server daemon is accessing I was surprised.
cmd user : lsof | grep mysql cnd2 used : lsof | grep mysql | wc -l 2598 Please suggest how can this be reduced.What is the impact of the same on mysql performance.
Very likely, it's possible to reduce the number of files held open by MySQL. But note that this will likely decrease performance. In terms of performance, what's more important (than the number of open files) is the closing and opening of files. (That's more expensive than holding files open.)
MySQL caches open tables, keeping recently used files open. This avoids the performance overhead associated with closing files, and then reopening them again later. The limit on the number of tables held open is set with the table_open_cache variable.
The limit on the number of files MySQL has open is set in the max_open_files variable.
Reference: http://dev.mysql.com/doc/refman/5.5/en/table-cache.html
Note that a MyISAM table is stored on disk as three files: tablename.frm, tablename.MYD and tablename.MYI. If the table is partitioned, each partition requires separate files.
With InnoDB engine, if the innodb_file_per_table option is set when a table is created, that's two files, tablename.frm and tablename.idb.
If multiple sessions are accessing a table, there will be multiple "open files" for that table.
There's also a couple of innodb log files, as well as the mysql log file (in logging is enable), the mysql general log, slow query log, etc.
Any temporary table, derived table or an intermediate result set that exceeds the limit on the size of tables stored "in memory", that's additional files will be spun out to disk, and potentially more open files.
If you are hitting a "too many open files" OS error, you can have the OS limit increased. If that's not possible, then decrease the max_open_files variable; you may also need to reduce some other related variables as well.
If you are looking at limiting the number of open files as a solution to slow performance, it's possible you are barking up the wrong tree, because reducing the number of files that MySQL can hold open will likely decrease performance.
