查询保持“统计"不变.状态长时间在Google Cloud SQL(MySQL 5.5)中使用 [英] Query stays "statistics" state for long time in Google Cloud SQL (MySQL 5.5)
问题描述
某些查询在我的Google Cloud SQL数据库中长时间保持统计"状态. (MySQL 5.5)
Some queries stays "statistics" state for long time in my Google Cloud SQL Database. (MySQL 5.5)
在这篇文章之后，我将optimizer_search_depth更改为0.但是有些查询的统计时间仍然很长.
After this post, I changed optimizer_search_depth to 0. But some queries still have long statistics time.
> 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
如上所示，查询时间很长.我认为这是长时间统计状态的原因，但是我的应用程序需要这种类型的查询.
As shown above, query is very long. I think this is the cause of long statistics state, but my app needs this type of query.
如何避免长时间统计问题?
How can I avoid long statistics issue?
[更新]
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 |
+------------------------------+-----------+
推荐答案
在1GB服务器中，innodb_buffer_pool_size的大小不能超过200M.将其设置为800M将导致交换. MySQL希望其缓存保留在RAM中.当它们交换到磁盘上时，性能会遭受极大的损害.
In a 1GB server, do not have innodb_buffer_pool_size more than about 200M. Setting it to 800M will cause swapping. MySQL expects its caches to stay in RAM; when they get swapped to disk, performance suffers terribly.
您的表可能很大，无法完全缓存.因此，表扫描"将耗尽高速缓存，使高速缓存无用，并且查询将以磁盘速度运行.要么找到一种避免此类查询的方法，要么获得更多的RAM.
Your table is probably to big to be cached entirely. So, a "table scan" will blow out cache, making the cache useless and the query will run at disk speed. Either find a way to avoid queries like that, or get more RAM.
这篇关于查询保持“统计"不变.状态长时间在Google Cloud SQL(MySQL 5.5)中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
