MySQL Bugs: #56418: Could not set open-files-limit in multiple mysql instances.

Bug #56418	Could not set open-files-limit in multiple mysql instances.
Submitted:	31 Aug 2010 18:24	Modified:	16 Sep 2010 5:56
Reporter:	Umang Gopani	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	5.1.47, 5.1.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	open-files-limit

Description:
I have multiple mysql instances configured on a host. While trying to set open-files-limit to something else other than the default value (1024), the value is not taken by the mysql server.

Notice here that mysql server instance  while starting takes the value of open-files-limit to 5000
******************************************************************************************************
mysql    20232 20167  0 Aug30 ?        00:05:15 /usr/local/mysql/bin/mysqld --no-defaults --master-host=10.0.0.221 --master-port=3306 --master-user=replicant --master-password=SMSreplicant --skip-slave-start --replicate-ignore-db=mysql --relay-log=/var/log/mysql/mysql-chatintl/relay.log --relay-log-info-file=/var/log/mysql/mysql-chatintl/relay-log.info --relay-log-index=/var/log/mysql/mysql-chatintl/relay-log.index --character-set-server=utf8 --default-character-set=utf8 --user=mysql --port=3308 --socket=/var/run/mysqld/mysqld1.sock --pid-file=/var/run/mysqld/mysqld1.pid --log-error=/var/log/mysql/mysqld-chatintl.err --long_query_time=5 --basedir=/usr/local/mysql --datadir=/mnt/data1/mysql-chatintl --skip-locking --key_buffer=256M --max_allowed_packet=1M --open-files-limit=50000 --table_cache=256 --sort_buffer_size=30M --net_buffer_length=8K --read_buffer_size=20M --read_rnd_buffer_size=10M --myisam_sort_buffer_size=16M --language=/usr/local/mysql/share/english --bulk_insert_buffer_size=128M --query_cache_size=20M --thread_cache_size=5 --query_cache_limit=10M --bind-address=0.0.0.0 --expire_logs_days=14 --log-bin=/mnt/data1/mysql-bin-chatintl/mysql-bin-chatintl.log --server-id=5 --tmpdir=/tmp/ --innodb_buffer_pool_size=1G --innodb_additional_mem_pool_size=20M --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_file_size=50M --innodb_log_buffer_size=20M --set-variable=innodb_log_files_in_group=2 --innodb_flush_log_at_trx_commit=0 --innodb_lock_wait_timeout=50 --innodb_thread_concurrency=8 --innodb_flush_method=O_DIRECT
******************************************************************************************************

But when I run show global variables , it shows value as 1024
******************************************************************************************************
mysql> show global variables like "%open_files_limit%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  | 
+------------------+-------+
******************************************************************************************************

Please find below my my.cnf as attached 

How to repeat:
NA

Suggested fix:
This does not happen in case of single instance of mysql server where the mysql manager part is not used

******************************************************************************************************
[mysql.server]
use-manager

[manager]
default-mysqld-path                             =  /usr/local/mysql/bin/mysqld
socket                                          = /var/run/mysqld/mysqldmanage.sock
port                                            = 3309
pid-file                                        = /var/run/mysqld/mysqldnamage.pid
password-file                                   = /etc/mysqlmanager.passwd
run-as-service
log                                             = /var/log/mysql/mysqlmanage.err

******************************************************************************************************

Please suggest me some work around or a fix.

Any updates on this ?

Thank you for the report.

What `ulimit -a` for user you run mysqld as outputs?

I have not set specific ulimit for mysql. 
So it must be using the system wide default ulimit of 1024. 

I have never set a ulimit for mysql instance on the other mysql instances running with 5.1 where I have set open-files-limit to 5000 and it has worked fine. 
It is only with this multiple mysql server instance , that I see this problem. 
So I am thinking ulimit should not be a problem.

Thank you for the feedback.

This is not a bug if ulimit reports you can have no more than 1024 open files.

setting the ulimit (no of open files) for user root works. 
So the thing is open-files-limit takes directly the value of ulimit for root, irrespective of whatever is set in the config file.

From test machine 
cat /etc/security/limits.conf |grep 15000 
root hard nofile 15000 
root soft nofile 15000

and I see
mysql> show global variables like "open_files_limit"; 
+------------------+-------+ 
| Variable_name | Value | 
+------------------+-------+ 
| open_files_limit | 15000 | 
+------------------+-------+ 
1 row in set (0.00 sec)

I have set open-files-limit to 5000 for both the instances in config, still we see 15000 as the limit set.