Solved

tuning mySql thread & instance limits

Posted on 2010-09-06
10
584 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
 

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
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.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cassandra Select Query 1 55
Oracle - Create Procedure with Paramater 16 57
Help in Copy Database Wizard From One SQL Server to Other SQL Server 10 32
join tables 4 51
This article describes some very basic things about SQL Server filegroups.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

920 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

15 Experts available now in Live!

Get 1:1 Help Now