Inside22,

First of all every time you ask questions write MySQL version and Platform.

1.
[!!] Query cache efficiency: 1.5%
So it indicates that your data and queries changes frequently that q_cache simple cant cache your selects. or your select dont use q_cache
I advice just disable query_cache permanently:


2.
[!!] Joins performed without indexes: 3122
You can enable slow_query_log with logging queries that dont use indexes.
Write into my.cnf under [mysqld] catalog:


But it is very memory consuming process. because your slow log may grow very large. Just run MySQL for a 3 hours eg. to log queries then disable this feature.
log_queries_not_using_indexes = 0

3.
Разве буфер это не то место где кешируются запросы?
NO. Read documentation for innodb_buffer_pool:
InnoDB Buffer Pool

InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory

4.
WOW 2.0G per thread??!! Something going wrong here.
151x2G=302G
302G+5.3G~~307.3 G = 1976% of installed RAM :D

It must be 2.2 or 2.8 MB per thread. Your per-session variables must be given too large it is very dangerous:
Check out them:

miksoft,

sorry i dont refresh page while writing comment. You have already answered ;)

miksoft,

Thumbs up! :)

netwind,

there is really a big reason to panic. you should not touch per-session variables untill the query really needs it.
From High Perfomance MySQL 3rd edition book page 336 chapter 8:


1. read_buffer_size
MySQL doesn’t allocate any memory for this buffer until a query needs it, but then
it immediately allocates the entire chunk of memory specified here.

2. read_rnd_buffer_size
MySQL doesn’t allocate any memory for this buffer until a query needs it, and then
it allocates only as much memory as needed. (The name max_read_rnd
_buffer_size would describe this variable more accurately.)

3. sort_buffer_size
MySQL doesn’t allocate any memory for this buffer until a query needs to do a
sort. However, when there’s a sort, MySQL allocates the entire chunk of memory
immediately, whether the full size is required or not.


You should not raise the value of a per-connection setting globally unless you know it’s
the right thing to do. Some buffers are allocated all at once, even if they’re not needed,
so a large global setting can be a huge waste. Instead, you can raise the value when a
query needs it.
The most common example of a variable that you should probably keep small and raise
only for certain queries is sort_buffer_size, which controls how large the sort buffer
should be for filesorts. MySQL performs some work to initialize the sort buffer after
allocating it.
In addition, the sort buffer is allocated to its full size even for very small sorts, so if you
make it much larger than the average sort requires, you’ll be wasting memory and
adding allocation cost.
In summary, a large sort buffer can be very expensive, so don’t increase its size unless
you know it’s needed.

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 :



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 ;)