Mysql handler read rnd next как исправить

Значительное время в генерации динамических страницы, занимает извлечение данных из базы сайта. Для того что бы ускорить этот процесс необходимо оптимизировать настройки сервера баз данных, схему базы данных, код запросов.

mysql> SHOW VARIABLES LIKE ‘%query_cache%’;
+——————————+————+
| Variable_name                | Value      |
+——————————+————+
| have_query_cache             | YES        |
| query_cache_limit            | 1048576    |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 2147483648 |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
+——————————+————+
6 rows in set (0.00 sec)

2. Оптимизация запросов.

Один из самых простых способов улучшить производительность базы данных — это добавить индексы к таблицам баз данных.
Проверить эффективность существующих индексов в базе данных можно с помощью запроса SHOW STATUS LIKE ‘handler_read%’;

mysql> SHOW STATUS LIKE ‘handler_read%’;
+————————+——-+
| Variable_name         | Value |
+————————+——-+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 68    |
+————————+——-+

long_query_time=1
slow_query_log = /var/lib/mysql/mysql-slow-queries.log

Параметр long_query_time указывает серверу БД учитывать запросы выполняющиеся более 1 секунды.
Эти запросы будут записывать в файл указанный в опции  slow_query_log (в разных версиях mysql используется параметр slow_query_log либо log-slow-queries, используйте подходящий вашему серверу),  В нашем случае «длинные» запросы  будут записывать в файл /var/lib/mysql/mysql-slow-queries.log
Необходимо его создать:

touch /var/lib/mysql/mysql-slow-queries.log

, и выдать права серверу БД на запись в него:

chown mysql:mysql /var/lib/mysql/mysql-slow-queries.log

После этого перезапускаем сервер БД:

или, в случае использования сервера баз данных Mariadb, команда:

systemctl restart mariadb

Через некоторое время файл наполнится записями о «тяжелых» запросах.

Прочитаем  его содержимое:

cat /var/lib/mysql/mysql-slow-queries.log

Получаем, например:

SET timestamp=1293244487;
# administrator command: Init DB;
# Time: 101225  4:34:50
# User@Host: test[pura] @ localhost []
# Query_time: 2.452726  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1293244490;
select ItemID, ItemName, ItemAlias, ItemPic1, BrandID, SexID, NodeTitle, NodeDescription, NodeKeywords from Item where ItemGroupID=»15″ and ItemVisible=1  and LanguageID=1 order by ItemSortOrder;
# Time: 101225  4:38:51
# User@Host: test[pura] @ localhost []
# Query_time: 3.196396  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1293244731;
select ItemID, ItemName, ItemAlias, ItemPic1, BrandID, SexID, NodeTitle, NodeDescription, NodeKeywords from Item where ItemGroupID=»27″ and ItemVisible=1  and LanguageID=1 order by ItemSortOrder;
# Time: 101225  4:57:50
# User@Host: test[pura] @ localhost []
# Query_time: 2.334119  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1293245870;
select ItemID, ItemName, ItemAlias, ItemPic1, BrandID, SexID, NodeTitle, NodeDescription, NodeKeywords from Item where ItemGroupID=»22″ and ItemVisible=1  and LanguageID=1 order by ItemSortOrder;

Из них видно, что тормозят запросы к таблице Item;
Посмотрим текущие поля таблицы:

mysql> SHOW CREATE TABLE Item G;
*************************** 1. row ***************************
       Table: Item
Create Table: CREATE TABLE `Item` (
  `ItemID` int(11) NOT NULL AUTO_INCREMENT,
  `ItemGroupID` int(11) NOT NULL DEFAULT ‘0’,
  `BrandID` int(11) NOT NULL DEFAULT ‘0’,
  `DepartmentID` int(11) NOT NULL DEFAULT ‘0’,
  `SexID` int(2) NOT NULL DEFAULT ‘1’,
  `ItemName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
  `ItemName2` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
  `ItemAlias` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
  `ItemPic1` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
 `ItemDescription` blob,
  `ItemShortDescription` blob,
  `ItemPrice` int(11) NOT NULL DEFAULT ‘0’,
  `ItemPriceBel` int(11) NOT NULL DEFAULT ‘0’,
  `ItemPriceEuro` float NOT NULL DEFAULT ‘0’,
  `ItemPriceUSD` float NOT NULL DEFAULT ‘0’,
  `ItemPriceMargin` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
  `ItemPriceDiscount` int(11) NOT NULL DEFAULT ‘0’,
  `ItemNumber` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
  `ItemCode` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
  `ItemVolume` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
  `ItemYears` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT »,
  `ItemSold` int(11) NOT NULL DEFAULT ‘0’,
  `NodeTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `NodeDescription` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `NodeKeywords` blob,
  `ItemSortOrder` int(11) NOT NULL DEFAULT ‘0’,
  `ItemVisible` int(1) NOT NULL DEFAULT ‘1’,
  `LanguageID` int(11) NOT NULL DEFAULT ‘0’,
  PRIMARY KEY (`ItemID`)
) ENGINE=MyISAM AUTO_INCREMENT=11584 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

В таблице есть лишь один первичный ключ по полю ItemID.
В запросах идет выборка по столбцу ItemGroupID, ключа для его нет, поэтому добавим его:

mysql> ALTER TABLE Item ADD INDEX `ItemGroupID`(`ItemGroupID`);

Анализируем таблицу, что бы ключи применились:

mysql> ANALYZE TABLE Item;

Дополнительную информацию вы можете найти в свободных источниках. Рекомендуем вам следующие ресурсы:
Использование оператора EXPLAIN (получение информации о SELECT)
* http://dev.mysql.com/doc/refman/5.6/en/explain.html
* http://www.mysql.ru/docs/man/EXPLAIN.html

Optimising mysql is very well commented on the net, and you’ll find huge information on how to do this. There is never “best parameters”, the best parameters is those fits your needs, box hardware, mysql usage…
So, I’ll not give the best parameters but rather how to define these ones. Make some tests, and you’ll quickly find your own parameters.

There a lot of available parameters but only few one are very important to tweak your mysql box.

The most important variables are (for me, and it is not exhaustive):

-   max_connections
-   wait_timeout
-   thread_cache_size
-   table_cache
-   key_buffer_size
-   query_cache_size
-   tmp_table_size

To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.

1 — The two most important variables : Table_cache and Key_buffer_size

• If Opened_tables is big, then your table_cache variable is probably
  too small.
  table_cache 64
  Open_tables 64
  Opened_tables 544468

This is the first serious problem. «The table_cache is the number of open
tables for all threads. MySQL, being multi-threaded, may be running many
queries on the table at one time, and each of these will open a table.»
Therefore, even though we only have a few tables, we will need many more
open_tables.

The Opened_tables value is high and shows the number of
cache misses. Getting the table_cache size correct is one of the two best
things you can do to improve performance.

• If Key_reads is big, then your key_buffer_size variable is probably
  too small. The cache hit rate can be calculated with
  Key_reads/Key_read_requests.
  key_buffer_size 16M
  Key_read_requests 2973620399
  Key_reads 8490571
  (cache hit rate = 0.0028)

“The key_buffer_size affects the size of the index buffers and the speed
of index handling, particularly reading.» The MySQL manual (and other sources) say that
«Key_reads/Key_read_request ratio should normally be < 0.01.» This is the
other most important thing to get correct. Here the value seems to be correct (< 0.01)

Also check key_write_requests and key_writes.
The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine)

Here is a very interesting web pointer : table_cache and key_buffer_size

2 — Others important settings are : Wait_timeout, max_connexion, thread_cache

A little explanation :
Generaly you have a lot of mysql process that are sleeping because wait_timeout are not set low. So I make sure that the wait_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.

The problem is you also have to increment your max_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.
The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.

So the solution is to use the Thread_cache (from mysql doc) :
“How many threads we should keep in a cache for reuse. When a client disconnects, the client’s threads are put in the cache if there aren’t more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn’t give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.”

• If Threads_created is big, you may want to increase the
  thread_cache_size variable. The cache hit rate can be calculated with
  Threads_created/Connections.
  thread_cache_size 0
  Threads_created 150022
  Connections 150023

This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.

you may try this formula : table_cache = opened table / max_used_connection

3 — Finally, you may also have a look at : tmp_table_size and Handler_read_rnd / Handler_read_rnd_next

• If Created_tmp_disk_tables is big, you may want to increase the
  tmp_table_size variable to get the temporary tables memory-based instead
  of disk based.

tmp_table_size 32M
Created_tmp_disk_tables 3227
Created_tmp_tables 159832
Created_tmp_files 4444

Created_tmp_disk_tables are the «number of implicit temporary tables on
disk created while executing statements» and Created_tmp_tables are
memory-based. Obviously it is bad if you have to go to disk instead of
memory. About 2% of temp tables go to disk, which doesn’t seem too bad
but increasing the tmp_table_size probably couldn’t hurt either.

• If Handler_read_rnd is big, then you probably have a lot of queries
  that require MySQL to scan whole tables or you have joins that don’t use
  keys properly.
  Handler_read_rnd 27712353
  Handler_read_rnd_next 283536234

These values are high, that we could probably stand to improve
the indexes and queries.

I hope this will help some of you to more understand how it is possible to optimise MYSQL to fit your needs, hardaware box, or mysql current usage.

In MYSQL status, Handler_read_rnd_next value is very high.

I am aware that, this value will be incremented when a query is executed which is not having proper indexes.

But, even when we execute show status like ‘Handler_read_rnd_next’, this value is getting incremented by 2.

Based on this status flag, we are monitoring some stats.

So every time, this stats are showing critical.

Can we exclude these ‘show’ execution counts from ‘Handler_read_rnd_next’ count.

One more example for this,

There is a table with 10 rows, table is indexed on column ‘data’, and if we execute the following query:

select data from test where data = 'vwx' -> returns one row

and if we check value of ‘Handler_read_rnd_next’, it got incremented by 7.

Following is result of explain command for the above query:

explain select data from test where data = 'vwx';

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

1, 'SIMPLE', 'test', 'ref', 'data', 'data', '35', 'const', 1, 'Using where; Using index'

Is there any way to restrict this value, or can i know why this value is getting incremented very fast.