问题描述
某些查询在我的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)中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！