关于long_query_time的设置,可不可以说是mysql的一个小小bug呢

本文介绍MySQL慢查询日志的开启与调整方法,包括如何修改慢查询阈值及确认更改生效的方式。同时探讨了设置日志输出目标及记录未使用索引查询的功能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

我们知道对对于MySQL的日志功能,我们可以完全自己控制到底写还是不写。一般来说,binlog我们一般会开启,而对于慢查询我们一般会在开发的时候调试和观察SQL语句的执行速度。但今天发现一个问题。在使用show variables like 'log%';查看slow日志是否开启时,发现为OFF,使用set global log_low_queries=on;成功了。而使用show variales like 'long%'时发现是10,于是想把它修改成1,照葫芦画瓢使用set global long_query_time=1,再一次查询发现还是10。乖乖砸了,使用select sleep(10),记录了日志,而是用select sleep(1)确实没有被记录。而且设置的返回记录是:

 

mysql> set global long_query_time=1;

Query OK, 0 rows affected (0.00 sec)

难道真的没有改变吗,忽然想到是不是需要重启服务器呢,想应该不是吧,我又不是修改配置文件。最后想重新开一个会话看如何。重新开一个会话查询show variables like 'long%';果然被修改了。那为什么在修改它的窗口没有反映出来呢。不解。

希望遇到这种情况的朋友不要向我一样在一个会话中不停的试。找原因,直接开过窗口就可以试验了。

 

 

另外,将MySQL慢查询日志写入表的的设置为。set global log_output='TABLE';

和慢查询相关的还有一个参数是log_queries_not_using_indexes;用来设置是否记录没有使用索引的查询。开启该查询后,如果你的日志是记录到表中的,那么你每次显示慢日志语句的时候,从该表中的查询记录也会被添加进行。

如果日志是写在文件中。用mysqldumpslow工具可以对慢查询日志文件进行浏览。该工具我也没使用过,用了后在介绍了。


转自:http://blog.youkuaiyun.com/fengbangyue/article/details/6359509

<think>我们正在分析MySQL中SHOWFULLPROCESSLIST显示的内存使用过高的问题,特别是memory_used和memory_used_by_query这两个值过高的原因及优化方法。首先,需要明确memory_used和memory_used_by_query的含义:-memory_used:表示该线程当前总共使用的内存量(单位通常是字节)。-memory_used_by_query:表示该线程当前执行中的查询所使用的内存量(单位通常是字节)。这两个值过高通常意味着MySQL的某些线程(或查询)消耗了过多的内存资源,可能导致服务器内存不足,进而影响整体性能。###可能的原因1.**复杂查询**:执行复杂的SQL语句(如包含大量排序(ORDERBY)、分组(GROUPBY)、多表连接(JOIN)或子查询)可能导致内存使用增加,特别是当处理大量数据时。2.**排序和临时表**:当MySQL无法在内存中完成排序操作时,会创建临时表。如果内存不足,可能使用磁盘临时表,但在此之前会尝试使用内存临时表。如果排序的数据量过大,内存临时表会占用大量内存。3.**连接缓冲区**:例如,连接操作(JOIN)可能会使用连接缓冲区(joinbuffer)来存储中间结果。如果连接的表很大或连接条件复杂,连接缓冲区可能会占用较多内存。4.**缓冲区和缓存设置**:MySQL的某些配置参数(如sort_buffer_size,join_buffer_size,read_buffer_size等)设置过大,即使实际查询不需要这么多内存,每个连接也会分配这些缓冲区,导致内存使用过高。5.**大量并发连接**:当并发连接数很高时,即使每个连接使用的内存不多,总体内存使用也会很高。6.**处理大量数据**:例如,SELECT*FROMlarge_table这样的查询,如果结果集很大,且客户端处理较慢,服务器需要将结果集保存在内存中直到发送完成。7.**内存泄漏或MySQLbug**:极少数情况下,可能是MySQL本身的内存管理问题导致内存使用持续增长。###优化方法####1.优化SQL查询-避免使用SELECT*,只选择需要的列。-优化复杂查询,例如将大查询拆分成多个小查询,减少单次处理的数据量。-为经常用于排序、分组和连接的列创建合适的索引,以减少排序操作和临时表的使用。-使用EXPLAIN分析查询执行计划,找出可能的性能瓶颈。####2.调整MySQL配置参数根据实际情况调整以下参数(注意:调整参数需要重启MySQL或动态设置,但需谨慎):-**sort_buffer_size**:用于排序操作的缓冲区大小。如果发现很多排序操作导致内存使用高,可以适当减小该值(但设置过小可能导致使用磁盘临时表,影响性能)。默认值通常为256K到2M,根据实际需要调整。-**join_buffer_size**:用于连接操作的缓冲区大小。对于没有使用索引的连接操作,会使用连接缓冲区。可以适当减小该值(默认值通常为256K)。注意:如果所有连接都使用索引,则不需要调整。-**read_buffer_size**:用于顺序扫描表时的缓冲区大小。默认值通常为128K。如果有很多全表扫描,可以适当减小。-**tmp_table_size**和**max_heap_table_size**:这两个参数决定了内存临时表的最大大小。如果内存临时表超过这个大小,就会转换为磁盘临时表(MyISAM或InnoDB磁盘临时表)。可以适当增加这两个值(但要考虑服务器总内存),以避免使用磁盘临时表,但也要防止单个查询占用过多内存。-**max_connections**:限制最大并发连接数,防止因连接数过多导致内存耗尽。####3.监控和分析-使用`SHOWFULLPROCESSLIST`定期检查当前运行的查询,找出消耗内存高的查询。-使用性能模式(PerformanceSchema)来监控内存使用情况,例如启用memory相关的instrument和consumer来收集详细的内存使用数据。-使用慢查询日志(slowquerylog)来记录执行时间超过阈值或内存使用高的查询,然后针对这些查询进行优化。####4.应用程序优化-在应用程序层进行缓存,减少对数据库的查询次数。-控制查询结果集的大小,特别是分页查询时使用LIMIT限制返回的行数。-优化应用程序的连接管理,使用连接池并确保及时关闭不再使用的连接。####5.升级硬件或调整架构-如果优化后内存仍然不足,可以考虑增加服务器的物理内存。-对于读多写少的场景,可以考虑使用读写分离架构,将读请求分散到从库上。###示例:分析高内存使用查询假设我们发现某个查询的`memory_used_by_query`很高,可以通过以下步骤分析:1.从`SHOWFULLPROCESSLIST`中获取该查询的SQL语句。2.使用`EXPLAIN`分析该查询的执行计划,查看是否使用了临时表、文件排序等。3.检查相关表的结构和索引,确保有合适的索引来避免排序和临时表。4.如果发现是排序导致的内存使用高,考虑是否可以减少排序的数据量(例如添加WHERE条件限制)或优化排序字段的索引。例如,一个包含大结果集排序的查询:```sqlSELECT*FROMordersORDERBYorder_dateDESC;```如果orders表很大,这个排序操作可能会消耗大量内存。优化方法可以是:-添加索引:`ALTERTABLEordersADDINDEX(order_date);`,这样排序可能直接使用索引而不需要额外的排序操作。-或者分页查询:`SELECT*FROMordersORDERBYorder_dateDESCLIMIT100;`,减少单次返回的数据量。###结论优化MySQL内存使用需要结合具体查询和服务器配置。通过优化查询语句、调整配置参数以及合理的架构设计,可以有效降低内存使用。同时,持续的监控是确保数据库稳定运行的关键。§§相关问题§§1.如何监控MySQL中每个线程的内存使用情况?2.如何调整MySQL的缓冲区大小以避免内存浪费?3.在MySQL中,如何识别和优化导致高内存使用的慢查询?4.MySQL中临时表的使用对性能有什么影响?如何优化?</think>### MySQL SHOW FULL PROCESSLIST 中 memory_used 和 memory_used_by_query 过高的原因及优化方法 #### 一、核心概念解析 1. **`memory_used`** 表示**线程当前总内存消耗**(包括连接缓冲区、临时表、排序缓存等),单位为字节[^2]。 2. **`memory_used_by_query`** 表示**当前执行查询占用的内存**(如排序、聚合、临时表操作),单位为字节[^3]。 #### 二、内存过高的主要原因 ```mermaid graph LR A[内存过高原因] --> B[查询设计问题] A --> C[配置不合理] A --> D[并发压力] B --> B1(大表无索引排序/分组) B --> B2(未限制结果集大小) C --> C1(join_buffer_size过大) C --> C2(sort_buffer_size过量) D --> D1(高并发连接) D --> D2(长事务堆积) ``` 1. **低效查询设计** - 大结果集排序(`ORDER BY`)或分组(`GROUP BY`)未使用索引 - 多表`JOIN`未优化导致笛卡尔积爆炸 - 未使用`LIMIT`的分页查询(如`SELECT * FROM billion_row_table`) 2. **配置参数不当** - `join_buffer_size`或`sort_buffer_size`设置过大 - `tmp_table_size`超过实际内存容量 - `read_buffer_size`/`read_rnd_buffer_size`冗余分配 3. **系统资源瓶颈** - 并发连接数过高(`max_connections`超限) - 长事务未提交阻塞内存释放 - 未及时关闭空闲连接(`wait_timeout`设置过长) #### 三、优化方案与实操步骤 ##### 1. 紧急缓解措施 ```sql -- 终止高内存进程(需SUPER权限) KILL [process_id]; -- 强制刷新线程缓存(生产环境慎用) FLUSH STATUS; ``` ##### 2. 查询优化方案 - **索引优化** ```sql -- 为排序字段添加索引 ALTER TABLE orders ADD INDEX idx_amount(total_amount); -- 覆盖索引优化分组查询 SELECT product_id, COUNT(*) FROM sales USE INDEX (idx_product) -- 强制使用索引 GROUP BY product_id; ``` - **限制结果集** ```sql -- 分页查询必须加LIMIT SELECT * FROM user_logs ORDER BY log_time DESC LIMIT 0, 100; -- 限制100条 ``` - **拆分复杂查询** ```sql -- 将多表JOIN拆分为临时表 CREATE TEMPORARY TABLE tmp_orders ENGINE=MEMORY AS (SELECT order_id FROM orders WHERE status='paid'); SELECT * FROM tmp_orders JOIN order_details USING(order_id); ``` ##### 3. 参数调优建议 | 参数 | 建议值 | 作用 | |------|--------|------| | `join_buffer_size` | 4M-16M | 避免小表全扫描占用过大缓存 | | `sort_buffer_size` | 2M-8M | 防止单排序操作消耗GB级内存 | | `max_heap_table_size` | 64M | 控制内存临时表上限 | | `tmp_table_size` | =`max_heap_table_size` | 同步设置 | | `wait_timeout` | 300秒 | 及时回收空闲连接 | **配置示例**(my.cnf): ```ini [mysqld] join_buffer_size = 8M sort_buffer_size = 4M max_heap_table_size = 64M tmp_table_size = 64M wait_timeout = 300 ``` ##### 4. 监控与诊断工具 - **实时分析** ```sql SHOW ENGINE INNODB STATUS; -- 查看缓冲池使用 SELECT * FROM sys.memory_by_thread_by_current_bytes; -- 线程内存排行 ``` - **慢查询定位** ```sql -- 启用慢查询日志 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 2; -- 捕获>2秒的查询 ``` #### 四、预防性架构设计 1. **读写分离** 将报表类查询迁移到只读副本,避免影响主库。 2. **连接池管理** 应用层设置连接池上限(如HikariCP的`maximumPoolSize`)。 3. **内存告警机制** 配置Prometheus+Alertmanager监控规则: ```yaml - alert: HighMySQLMemory expr: mysql_global_variables_memory_used > 0.8 * mysql_global_variables_total_memory for: 5m ``` > **关键提示**:若优化后仍持续出现高内存使用,需检查是否存在内存泄漏(如未释放的游标或临时表),可尝试升级MySQL版本或排查应用代码[^3]。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值