[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

tuning mySql thread & instance limits

Posted on 2010-09-06
10
Medium Priority
?
593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 15

Accepted Solution

by:
Insoftservice earned 2000 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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 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