tuning mySql thread & instance limits

Hi,

I received alerts from my CA monitoring that the instance limit for ss_goodnet
was breached while the thread's limit for vvvv  was breached.

Which are the parameters below (see the 2 .cnf files below) I should adjust
upwards & what's the recommended value to increase to?

After editing the .cnf file to increase the values, what's the command to restart
mySql.  Sorry, I'm completely a newbie to mySql.  I'm running on Redhat RHES 5.4


============== ss_goodnet instance ========================
[mysqld]
server-id=1
datadir=/data/mysql/ss_goodnet/data
socket=/data/mysql/ss_goodnet/mysql.sock
port=8003
log-bin=/data/mysql/ss_goodnet/log/ss_goodnet_mysqld_bin
local-infile=1
secure-auth
skip-symbolic-links
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_allowed_packet = 1024M
max_connections=128
expire_logs_days=10
log_bin_trust_function_creators=1
#replicate-do-db=mysql
#replicate-do-db=dbnsconnect
#binlog-do-db=mysql
#binlog-do-db=dbnsconnect
relay-log=nscppdb1-relay-bin
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
wait_timeout=900
key_buffer_size=3072M
sort_buffer_size=64M
myisam_sort_buffer_size=64M
innodb_data_home_dir=/data/mysql/ss_goodnet/data
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir =/data/mysql/ss_goodnet/data
innodb_buffer_pool_size = 3072M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency=32
table_open_cache=1024
thread_cache_size=16
slow_query_log=1
long_query_time=3
ft_min_word_len=2
ft_stopword_file=''

[mysql.server]
user=mysql
#basedir=/usr/local/mysql

[mysqld_safe]
log-error=/var/log/ss_goodnet_mysqld.log
pid-file=/var/run/ss_goodnet_mysqld.pid

=================  vvvv instance ===============================

[mysqld]
server-id=1
max_allowed_packet=1024M
datadir=/data/mysql/vvvv/data
socket=/data/mysql/vvvv/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
port=8005
log-bin=/data/mysql/vvvv/log/vvvv_mysqld_bin
log_bin_trust_function_creators=1
local-infile=1
secure-auth
skip-symbolic-links
sql_mode=NO_AUTO_CREATE_USER
max_allowed_packet=1024M
max_connections=128
expire_logs_days=5
relay-log=nscppdb1-relay-bin
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
wait_timeout=900
key_buffer_size=3072M
sort_buffer_size=64M
myisam_sort_buffer_size=64M
innodb_data_home_dir=/data/mysql/vvvv/data
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir =/data/mysql/vvvv/data
innodb_buffer_pool_size = 3072M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency=32
table_open_cache=1024
thread_cache_size=16
slow_query_log=1
long_query_time=3
lower_case_table_names=1
binlog_format=mixed

[mysql.server]
user=mysql
#basedir=/var/lib/

[mysqld_safe]
log-error=/var/log/vvvv_mysqld.log
pid-file=/var/run/mysqld/vvvv_mysqld.pid
sunhuxAsked:
Who is Participating?
 
InsoftserviceConnect With a Mentor Commented:
hi,

[mysqld]
local-infile=0
innodb_buffer_pool_size = 716M
table_cache = 1024
log_slow_queries=/var/log/mysqld.slow.log

where 716M is nearly 70% of ur memory size.
0
 
sunhuxAuthor Commented:

or could this be a Linux thread & instance settings?

How do I go about to check Linux thread / instance settings?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
sunhuxAuthor Commented:

at Linux OS level

"sysctl -a"  would give Instance & Thread parameters too which currently are
set as :
kernel.threads-max : 270335
inotify.max-user-instance : 128

Could the above 2 parameters' limit cause problems to mySQL?
0
 
InsoftserviceConnect With a Mentor Commented:
Hi,
U mean to say that the location to get my.cnf in linux right?
or u want to check the thread used in linux ==>> on cmd prompt type  == top
0
 
InsoftserviceConnect With a Mentor Commented:
hi,
Order:
      File Location:
      Description:
1.
      /etc/my.cnf       For setting global options
2.
      /var/lib/mysql/my.cnf       For setting server-specific options
3.
      ~/my.cnf       For setting user-specific options (if applicable)
0
 
InsoftserviceConnect With a Mentor Commented:
Hi,

No it would be always more.
It depends how much hits ur getting. Just change it to the above setting .It would resolve ur issue .
0
 
sunhuxAuthor Commented:

or should run the following tool to get a recommendation :
(somehow my DBA was not able to login using mysql login id nor admin id) :
http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner
0
 
sunhuxAuthor Commented:
0
 
InsoftserviceConnect With a Mentor Commented:
hi,
go for
http://www.webhostingtalk.com/archive/index.php/t-888148.html
one since, howtoforge does not gives compete info.even i had faced an issue on sendmail some days before.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.