1

Mysql stored procedure taking too much time after migration to new server. Stored procedure taking too much time to return results. My cnf file as follows

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
bind-address        = 0.0.0.0
key_buffer_size     = 384M
max_allowed_packet  = 16M
table_open_cache    = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_stack        = 192K
thread_cache_size       = 38
myisam-recover-options  = BACKUP
query_cache_limit   = 1M
query_cache_size        = 185M
max_binlog_size   = 100M
query_cache_size = 185M 
tmp_table_size = 66M
thread_cache_size = 38
innodb_log_buffer_size=6M
innodb_buffer_pool_size=557M
innodb_log_file_size=256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

Is there any way to optimize more.

rakesh pakkam
  • 144
  • 11

1 Answers1

0

Suggestions for your my.cnf [mysqld] section

REMOVE the following to allow MySQL DEFAULTS to work for you and avoid OVERPROVISIONING RAM requirements.

THERE are MULTIPLES of

sort_buffer_size
read_buffer_size
read_rnd_buffer_size

REMOVE ONE 

thread_cache_size
query_cache_size

ADD

max_heap_table_size=66M

because it should always be same as tmp_table_size

Please view my profile, Network profile for contact info including my Skype ID and get in touch with me, please.

Wilson Hauck
  • 2,094
  • 1
  • 11
  • 19