Description:
Instance manager doesn't allow more then 1024 files to be opened by default. I have used the same configuration without mysql instance manager and have been able to get mysql to open more then 1024 files open.
Instance manager shows the following in the log file:
081120 13:43:12 [Warning] Changed limits: max_open_files: 1024 max_connections: 886 table_cache: 64
If i add the option to my instances in my my.cnf: open_files_limit=8192 then when mysql is started via instance manager i get the following message in the log file:
090106 18:09:05 [Warning] Could not increase number of max_open_files to more than 1024 (request: 2308)
Intrestingly with the value set in my.cnf i believe the server is starting up with enough file handles and it shows when a do a show global variables like '%cache%' the correct value for table cache.
I have modified limits.conf and /etc/pam.d/login to allow more then default number of open files executing a ulimit -a will show the values in limits.conf to have taken effect.
Here is my my.cnf file:
[mysql.server]
use-manager
[manager]
default-mysqld-path=/usr/local/mysql5/bin/mysql
socket=/opt/mysql/manager/tmp/manager.sock
pid-file=/opt/mysql/manager/tmp/manager.pid
password-file=/opt/mysql/manager/mysqlmanager.passwd
log=/opt/mysql/manager/log/mysql-manager.log
monitoring-interval=2
bind-address=127.0.0.1
run-as-service
[mysqld1]
nonguarded
mysqld-path=/usr/local/mysql5/bin/mysqld
skip-name-resolve
datadir=/opt/mysql/t1
tmpdir=/opt/mysql/tmp/t1
server-id = 65721
socket = /opt/mysql/tmp/mysql1.sock
port=3306
lower_case_table_names
log-slow-queries=/opt/mysql/log/t1/t1-db2-slow.log
log-error=/opt/mysql/log/t1/t1-db2.err
log-bin=/opt/mysql/log/t1/t1-db2-log-bin
relay-log=/opt/mysql/log/t1/t1-db2-relay-bin
long_query_time=3
expire_logs_days=5
log-queries-not-using-indexes
log-slow-admin-statements
log-warnings
skip-bdb
skip-innodb
#Performance Options
key_buffer_size=4G
max_allowed_packet = 32M
table_cache = 2048
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache = 300
query_cache_size = 128M
query_cache_limit = 5M
tmp_table_size = 128M
myisam_sort_buffer_size = 128M
#Timeout and Connection Settings
max_connections=250
wait_timeout=300
interactive_timeout=1200
max_allowed_packet=32M
master-host=172.16.65.170
master-user=user
master-password=7337
master-port=3306
report-host=t1-db2-t1
replicate-wild-ignore-table=quadrant_archive.%
replicate-wild-ignore-table=resolver.%
slave-skip-errors=1062
open_files_limit=8192
[mysqld2]
nonguarded
skip-name-resolve
mysqld-path=/usr/local/mysql5/bin/mysqld
datadir=/opt/mysql/t2
tmpdir=/opt/mysql/tmp/t2
server-id=65722
socket=/opt/mysql/tmp/mysql2.sock
lower_case_table_names
port=3309
log-slow-queries=/opt/mysql/log/t2/t1-db2-slow.log
log-err=/opt/mysql/log/t2/t1-db2.err
log-bin=/opt/mysql/log/t2/t1-db2-log-bin
relay-log=/opt/mysql/log/t2/t1-db2-relay-bin
long_query_time=3
expire_logs_days=5
log-queries-not-using-indexes
log-slow-admin-statements
log-warnings
skip-bdb
skip-innodb
#Performance Options
key_buffer_size=2G
max_allowed_packet = 32M
table_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache = 300
query_cache_size = 72M
query_cache_limit = 5M
tmp_table_size = 128M
myisam_sort_buffer_size = 128M
low_priority_updates
#Timeout and Connection Settings
max_connections=250
wait_timeout=300
interactive_timeout=1200
max_allowed_packet=32M
master-host=172.16.65.170
master-user=user
master-password=7337
master-port=3306
report-host=t1-db2-t2
replicate-wild-ignore-table=resolver.%
replicate-wild-ignore-table=quadrant_archive.%
slave-skip-errors=1062
open_files_limit=8192
How to repeat:
-Use instance manager
-Modify configuration to use more then 1000 file handles. Set max_connections=2000 or something and start mysql using instance manager.
Mysql instance manager will change the max connections to 887 and reduce table cache values accordingly.
