Запрос долгое время сохраняет состояние статистики в Google Cloud SQL (MySQL 5.5)

Некоторые запросы долгое время остаются в состоянии «статистики» в моей базе данных Google Cloud SQL. (Майскл 5.5)

После этого сообщения я изменил Optimizer_search_depth на 0. Но некоторые запросы по-прежнему имеют долгое время статистики.

> select @@optimizer_search_depth;
+--------------------------+
| @@optimizer_search_depth |
+--------------------------+
|                        0 |
+--------------------------+

> show processlist;
+----+------+-----------+------+---------+------+------------+-----------------+
| Id | User | Host      | db   | Command | Time | State      | Info            |
+----+------+-----------+------+---------+------+------------+-----------------+
|  4 | root | localhost | mydb | Query   |   84 | statistics | SELECT * FROM ..|

Таблица и подсчет приведены ниже.

> describe mytable;
+----------+---------------+------+-----+---------------------+-----------------------------+
| Field    | Type          | Null | Key | Default             | Extra                       |
+----------+---------------+------+-----+---------------------+-----------------------------+
| col1     | varchar(50)   | NO   | PRI | NULL                |                             |
| col2     | varchar(50)   | NO   | PRI | NULL                |                             |
| col3     | decimal(15,4) | NO   |     | NULL                |                             |
| col4     | decimal(15,4) | NO   |     | NULL                |                             |
| col5     | decimal(15,4) | NO   |     | NULL                |                             |
| col6     | decimal(15,4) | NO   |     | NULL                |                             |
| col7     | varchar(50)   | YES  |     | NULL                |                             |
| col8     | decimal(15,4) | NO   |     | NULL                |                             |
| col9     | decimal(15,4) | NO   |     | NULL                |                             |
| col10    | varchar(8)    | NO   |     | NULL                |                             |
| col11    | varchar(30)   | NO   |     | NULL                |                             |
| col12    | timestamp     | NO   |     | 0000-00-00 00:00:00 |                             |
| col13    | timestamp     | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| col14    | int(11)       | NO   |     | NULL                |                             |
+----------+---------------+------+-----+---------------------+-----------------------------+

> select count(*) from mytable;
+----------+
| count(*) |
+----------+
|   852304 |
+----------+

Запрос такой.

SELECT * FROM mytable WHERE 
((col1 = 'FFP60003' AND col2 = '360' ) OR 
(col1 = 'FIU51001' AND col2 = '210' ) OR 
(col1 = 'FIU51003' AND col2 = '360' ) OR 
(col1 = 'FPC60001' AND col2 = '240' ) OR 
(col1 = 'SLU50006' AND col2 = '360' ) OR 
... (about 2000-3000 and/or) ...
(col1 = '89969' AND col2 = '270' ) ) AND col14 > 0

Как показано выше, запрос очень длинный. Я думаю, что это причина длинного состояния статистики, но моему приложению нужен этот тип запроса.

Как избежать длинной статистики?

[Обновление]

SHOW CREATE TABLE и SHOW VARIABLES LIKE '%buffer%' указаны ниже.

> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `col1` varchar(50) NOT NULL COMMENT 'col1',
  `col2` varchar(50) NOT NULL COMMENT 'col2',
  `col3` decimal(15,4) NOT NULL COMMENT 'col3',
  `col4` decimal(15,4) NOT NULL COMMENT 'col4',
  `col5` decimal(15,4) NOT NULL COMMENT 'col5',
  `col6` decimal(15,4) NOT NULL COMMENT 'col6',
  `col7` varchar(50) DEFAULT NULL COMMENT 'col7',
  `col8` decimal(15,4) NOT NULL COMMENT 'col8',
  `col9` decimal(15,4) NOT NULL COMMENT 'col9',
  `col10` varchar(8) NOT NULL COMMENT 'col10',
  `col11` varchar(30) NOT NULL COMMENT 'col11',
  `col12` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'col12',
  `col13` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'col13',
  `col14` int(11) NOT NULL COMMENT 'col14',
  PRIMARY KEY (`col1`,`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 > SHOW VARIABLES LIKE '%buffer%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| bulk_insert_buffer_size      | 8388608   |
| innodb_buffer_pool_instances | 1         |
| innodb_buffer_pool_size      | 805306368 |
| innodb_change_buffering      | all       |
| innodb_log_buffer_size       | 8388608   |
| join_buffer_size             | 131072    |
| key_buffer_size              | 8388608   |
| myisam_sort_buffer_size      | 8388608   |
| net_buffer_length            | 16384     |
| preload_buffer_size          | 32768     |
| read_buffer_size             | 131072    |
| read_rnd_buffer_size         | 262144    |
| sort_buffer_size             | 2097152   |
| sql_buffer_result            | OFF       |
+------------------------------+-----------+

person N.F.    schedule 18.03.2015    source источник
comment
EXPLAIN SELECT ..., вероятно, скажет, что он выполнил сканирование таблицы, на что указывает ALL. Это потому, что OR сложно оптимизировать.   -  person Rick James    schedule 18.03.2015
comment
Если col14 достаточно избирательна, то INDEX(col14) поможет. Укажите SHOW CREATE TABLE mytable -- нам нужно увидеть точные индексы, а также тип двигателя. (При необходимости вы можете скрыть или удалить большинство столбцов.) Также SHOW VARIABLES LIKE '%buffer%'. Сколько у вас оперативной памяти?   -  person Rick James    schedule 18.03.2015
comment
Я обновил свой пост. col14 не является избирательным. Более 850 000 записей имеют значение 1.   -  person N.F.    schedule 18.03.2015
comment
Тип экземпляра Cloud SQL — D2. Это имеет 1 ГБ ОЗУ.   -  person N.F.    schedule 18.03.2015


Ответы (1)


На сервере объемом 1 ГБ размер innodb_buffer_pool_size не должен превышать 200 МБ. Установка его на 800M вызовет подкачку. MySQL ожидает, что его кеши останутся в оперативной памяти; когда они выгружаются на диск, производительность ужасно падает.

Ваша таблица, вероятно, слишком велика, чтобы ее можно было полностью кэшировать. Таким образом, «сканирование таблицы» приведет к выгрузке кеша, что сделает кеш бесполезным, и запрос будет выполняться со скоростью диска. Либо найдите способ избежать таких запросов, либо получите больше оперативной памяти.

person Rick James    schedule 18.03.2015
comment
Я изменил тип экземпляра Cloud SQL D8 с 4 ГБ ОЗУ. Это имеет некоторый эффект, но по-прежнему имеет состояние статистики 50 секунд. После того, как я обновил тип экземпляра до D8, innodb_buffer_pool_size становится равным 3221225472. Google автоматически устанавливает это значение и не разрешает его изменять. - person N.F.; 18.03.2015
comment
3G/4G плотный; это не оставляет много места для ОС и для других буферов, кешей и т. д. Существуют ли метрики, чтобы увидеть, происходит ли подкачка экземпляра? - person Rick James; 18.03.2015
comment
Была ли облачная служба настроена на 800M на виртуальной машине 1G? Должен ли 1G включать ОС и т. д.? Если да, смените провайдера, они не знают, что делают. - person Rick James; 18.03.2015