Solved

tuning mySql thread & instance limits

Posted on 2010-09-06
10
587 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:sunhux
  • 6
  • 4
10 Comments
 
LVL 15

Accepted Solution

by:
Insoftservice earned 500 total points
ID: 33610089
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
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 500 total points
ID: 33610102
0
 

Author Comment

by:sunhux
ID: 33610151

or could this be a Linux thread & instance settings?

How do I go about to check Linux thread / instance settings?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:sunhux
ID: 33610202

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
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 500 total points
ID: 33610230
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
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 500 total points
ID: 33610243
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
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 500 total points
ID: 33610260
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
 

Author Comment

by:sunhux
ID: 33610501

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
 

Author Comment

by:sunhux
ID: 33610508
0
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 500 total points
ID: 33615074
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question