Inside22,
Here is What i found from your my.cnf as misconfiguration:
* max_allowed_packet=2048M value is very high. in my production server i gave this value only 16M
* key_buffer_size=2048M if your tables primarily InnoDB why use 2G myisam key buffer?. also in my server i put this value only 16M
* myisam_sort_buffer_size=1024M . the default value for this variable is only 8M and let it be as default.
* innodb_additional_mem_pool_size=1024M is too high again. Maybe the 20M will be better for this variable.
But if you want exaclty optimize this value. You can check and find optimal value as :
1.
2.
3.
4.
5.
6.
7.
show engine innodb status;
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated xxxx; in additional pool allocated xxxx
Dictionary memory allocated xxxxx
25-30% more than the value from "in additional pool allocated"
But i dont think that it is very important at this moment.
* innodb_buffer_pool_size=2G too low. if you have Centos with 16G with InnoDB why you dont give for eg 10G to innodb_buffer?
* join_buffer_size=256M what kind of join will use 256M? do not touch per-session variables instead of try to do first of all query optimization with proper indexing techniques. So i suggest to delete this from my.cnf
You can read about join_buffer_size from
What is join buffer?
If to give large value to this variable you will likely see following bug/error:
Join buffer allocation fails silently
*автор после этого 3-х секундный запрос начал выполнятся за 0,15 секунд, но только со второго раза. После рестарта ДБ первый раз запрос выполняется все равно по 3 секунды.
Maybe it is because of your query_cache so i advice to disable query_cache enable slow_query_log run for a while and check the slow query log with a tool from percona toolkit pt-query-digest.
If problem exists and you can not solve open new topic with your slow query. experts surely will help.
The main question of this topic , i think is answered. the pros-cons of variables observed by experts such
miksoft , Akina , netwind
So dear
Inside22 read all comments take your notes.
I think that was the best discussion in forum that i participate it is really great pleasure to be with you ;)
