Solved

tuning mySql thread & instance limits

Posted on 2010-09-06
10
578 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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:sunhux
Comment Utility

or could this be a Linux thread & instance settings?

How do I go about to check Linux thread / instance settings?
0
 

Author Comment

by:sunhux
Comment Utility

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
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
0
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 500 total points
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now