Mysql 5.7 performance tuning. Stored procedure taking too much time to respond

Question

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.

Answer 1

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.