(转)根据mysql的status信息优化mysql - 肖小胖的博客 - ITeye博客

`

flowercat

浏览: 361784 次

最近访客更多访客>>

rainshow

mokyle

yyzhanglbb

daotadefeng

博主相关

博客

微博

相册

收藏

留言

关于我

文章分类

社区版块

存档分类

最新评论

string2020： select * from tab where user_id ...
（转）JPA JPQL
hety163：
Java 位运算符
hiberatejss： lz,~这个位运算的作用和反码是一样的吗？
Java 位运算符
skcmm：博主，有个问题想请教下。我在/etc/hosts声明一个域名， ...
nginx泛域名解析
kid_ren：写的好
Java 位运算符

(转)根据mysql的status信息优化mysql

博客分类：

linux

MySQL Cache 应用服务器 thread SQL

阅读更多

1, 查看MySQL服务器配置信息
mysql> show variables;
2, 查看MySQL服务器运行的各种状态值
mysql> show global status;
3, 慢查询
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | OFF   |
| slow_launch_time | 2     |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 279   |
+---------------------+-------+
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | OFF   |
| slow_launch_time | 2     |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 279   |
+---------------------+-------+
配置中关闭了记录慢查询（最好是打开，方便优化），超过2秒即为慢查询，一共有279条慢查询
4, 连接数

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 498   |
+----------------------+-------+
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 498   |
+----------------------+-------+
设置的最大连接数是500，而响应的连接数是498
max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）
5, key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb

mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+

mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Key_read_requests | 25629497 |
| Key_reads         | 66071    |
+-------------------+----------+
mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+
mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Key_read_requests | 25629497 |
| Key_reads         | 66071    |
+-------------------+----------+
一共有25629497个索引读取请求，有66071个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =0.27%
需要适当加大key_buffer_size

mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 10285 |
| Key_blocks_used   | 47705 |
+-------------------+-------+
mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 10285 |
| Key_blocks_used   | 47705 |
+-------------------+-------+
Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% （理想值 ≈ 80%）
6，临时表

mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 4184337 |
| Created_tmp_files       | 4124    |
| Created_tmp_tables      | 4215028 |
+-------------------------+---------+
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 4184337 |
| Created_tmp_files       | 4124    |
| Created_tmp_tables      | 4215028 |
+-------------------------+---------+
每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数：
Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 99% （理想值<= 25%）

mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 134217728 |
| tmp_table_size      | 134217728 |
+---------------------+-----------+
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 134217728 |
| tmp_table_size      | 134217728 |
+---------------------+-----------+
需要增加tmp_table_size
7,open table 的情况

mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 1024 |
| Opened_tables | 1465 |
+---------------+-------+
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 1024 |
| Opened_tables | 1465 |
+---------------+-------+
Open_tables 表示打开表的数量，Opened_tables表示打开过的表数量，如果Opened_tables数量过大，说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小，我们查询一下服务器table_cache值

mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 1024 |
+---------------+-------+
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 1024 |
+---------------+-------+
Open_tables / Opened_tables * 100% =69% 理想值（>= 85%）
Open_tables / table_cache * 100% = 100% 理想值 (<= 95%)
8, 进程使用情况

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 31    |
| Threads_connected | 239   |
| Threads_created   | 2914 |
| Threads_running   | 4     |
+-------------------+-------+
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 31    |
| Threads_connected | 239   |
| Threads_created   | 2914 |
| Threads_running   | 4     |
+-------------------+-------+
如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置：

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 32    |
+-------------------+-------+
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 32    |
+-------------------+-------+
9, 查询缓存(query cache)

mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2226     |
| Qcache_free_memory      | 10794944 |
| Qcache_hits             | 5385458 |
| Qcache_inserts          | 1806301 |
| Qcache_lowmem_prunes    | 433101   |
| Qcache_not_cached       | 4429464 |
| Qcache_queries_in_cache | 7168     |
| Qcache_total_blocks     | 16820    |
+-------------------------+----------+
mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2226     |
| Qcache_free_memory      | 10794944 |
| Qcache_hits             | 5385458 |
| Qcache_inserts          | 1806301 |
| Qcache_lowmem_prunes    | 433101   |
| Qcache_not_cached       | 4429464 |
| Qcache_queries_in_cache | 7168     |
| Qcache_total_blocks     | 16820    |
+-------------------------+----------+
Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：每次查询在缓存中命中时就增大
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的          free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。
Qcache_total_blocks：缓存中块的数量。
我们再查询一下服务器关于query_cache的配置：

mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 33554432 |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 33554432 |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
各字段的解释：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小
query_cache_size：查询缓存大小
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size

怎么配置MySQL服务器，但考虑到服务器硬件配置的不同，具体应用的差别，那些文章的做法只能作为初步设置参考，我们需要根据自己的情况进行配置优化，好的做法是MySQL服务器稳定运行了一段时间后运行，根据服务器的”状态”进行优化。

　　 mysql> show global status;
　　可以列出MySQL服务器运行各种状态值，另外，查询MySQL服务器配置信息语句：

　　 mysql> show variables;
　　一、慢查询

　　 mysql> show variables like '%slow%';
　　+------------------+-------+

　　| Variable_name | Value |

　　+------------------+-------+

　　| log_slow_queries | ON |

　　| slow_launch_time | 2 |

　　+------------------+-------+

　　mysql> show global status like '%slow%';

　　+---------------------+-------+

　　| Variable_name | Value |

　　+---------------------+-------+

　　| Slow_launch_threads | 0 |

　　| Slow_queries | 4148 |

　　+---------------------+-------+

　　配置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有4148个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁：http://www.percona.com /docs/wiki/release:start，记得找对应的版本。

　　打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。

　　二、连接数

　　经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：

　　 mysql> show variables like 'max_connections';
　　+-----------------+-------+

　　| Variable_name | Value |

　　+-----------------+-------+

　　| max_connections | 256 |

　　+-----------------+-------+

　　这台MySQL服务器最大连接数是256，然后查询一下服务器响应的最大连接数：

　　mysql> show global status like 'Max_used_connections';

　　MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是：

　　Max_used_connections / max_connections * 100% ≈ 85%

　　最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

　　三、Key_buffer_size

　　key_buffer_size是对MyISAM表性能影响最大的一个参数，下面一台以MyISAM为主要存储引擎服务器的配置：

　　 mysql> show variables like 'key_buffer_size';
　　+-----------------+------------+

　　| Variable_name | Value |

　　+-----------------+------------+

　　| key_buffer_size | 536870912 |

　　+-----------------+------------+

　　分配了512MB内存给key_buffer_size，我们再看一下key_buffer_size的使用情况：

　　 mysql> show global status like 'key_read%';
　　+------------------------+-------------+

　　| Variable_name | Value |

　　+------------------------+-------------+

　　| Key_read_requests | 27813678764 |

　　| Key_reads | 6798830 |

　　+------------------------+-------------+

　　一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：

　　key_cache_miss_rate = Key_reads / Key_read_requests * 100%

　　比如上面的数据，key_cache_miss_rate为0.0244%，4000个索引读取请求才有一个直接读硬盘，已经很BT 了，key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘)，如果key_cache_miss_rate在 0.01%以下的话，key_buffer_size分配的过多，可以适当减少。

　　MySQL服务器还提供了key_blocks_*参数：

　　 mysql> show global status like 'key_blocks_u%';
　　+------------------------+-------------+

　　| Variable_name | Value |

　　+------------------------+-------------+

　　| Key_blocks_unused | 0 |

　　| Key_blocks_used | 413543 |

　　+------------------------+-------------+

　　Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数，比如这台服务器，所有的缓存都用到了，要么增加key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设置：

　　 Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
　　四、临时表

　　 mysql> show global status like 'created_tmp%';
　　+-------------------------+---------+

　　| Variable_name | Value |

　　+-------------------------+---------+

　　| Created_tmp_disk_tables | 21197 |

　　| Created_tmp_files | 58 |

　　| Created_tmp_tables | 1771587 |

　　+-------------------------+---------+

　　每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是： Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

分享到：

（转）基于MySQL5.0中limit的高性能分析 | sudo免密码

2010-02-25 11:36
浏览 2208
评论(0)
分类:数据库
查看更多

评论

发表评论

您还没有登录,请您登录后再发表评论

相关推荐

mysql中status状态说明: 根据status状态对Mysql数据库进行优化： 1、连接数 1.1 show variables like ‘max_connections’; 1.2、 show global status like ‘Max_used_connections’; 1.3、设置最大连接数值方法： mysqld服务器...

mysql5.6性能优化: 在MySQL中，可以通过`SHOW STATUS`命令来查看一系列有关数据库性能的参数。这些参数可以帮助我们了解当前数据库的运行状态，并为后续的优化工作提供参考依据。 ##### 常用参数： - **Slow_queries**：记录了慢查询...

通过showstatus来优化MySQL数据库: 通过 show status 命令可以获取 MySQL 数据库的各种状态信息，从而对数据库进行优化。下面是通过 show status 命令获取的信息和对应的优化建议： 1. 慢查询配置：通过 show variables like '%slow%' 和 show global...

MySQL的or、in、union与索引优化: ### MySQL的or、in、union与索引优化在数据库查询优化中，索引的使用至关重要，它能显著提升查询速度。本文将基于一个具体的业务场景来探讨在MySQL中使用`union all`、`in`、`or`以及负向查询（如`!=`）时如何有效...

大神解读MySQL status意义: MySQL是世界上最受欢迎的关系型数据库管理...理解并充分利用`SHOW STATUS`提供的信息，是MySQL性能管理和优化的关键步骤。通过持续监控和调整，我们可以确保MySQL数据库以最佳状态运行，为业务提供可靠且高效的支撑。

MySQL性能优化的最佳21条经验: 16. **监控和调优**：使用SHOW STATUS和SHOW VARIABLES查看MySQL运行状态，适时调整参数。 17. **使用慢查询日志**：记录执行时间超过阈值的查询，分析并优化。 18. **避免在索引字段上使用运算符**：如`>`, `, `!...

MySQL DBA性能优化文档每天必看: MySQL DBA 性能优化文档每天必看 MySQL DBA 性能优化是一个复杂的过程，需要根据实际情况进行调整和优化。...需要使用 status 信息对 MySQL 进行具体的优化，并且需要根据实际情况选择合适的优化方法。

MySQL status解读.zip_MYSQL: MySQL是世界上最流行的关系型数据库管理系统之一，其性能监控和优化是DBA日常..."MySQL status解读.pdf"文件应该提供了更详细的信息，包括更多状态变量的解释和实际应用案例，可以帮助读者深入理解MySQL的性能监控。

MySql数据库性能优化: 使用 SHOW STATUS 语句可以查看 MySql 数据库的性能参数。常用的参数包括： * Slow_queries：慢查询次数 * Com_(CRUD)：操作的次数 * Uptime：上线时间查询优化查询优化是指优化数据库查询的速度和效率。下面将...

MySQL数据库服务器优化详细: ### MySQL数据库服务器优化详解在IT领域，MySQL作为全球最流行的开源关系型数据库管理系统之一，其性能优化一直是DBA（数据库管理员）和技术人员关注的重点。本文将深入解析MySQL数据库服务器优化的关键点，涵盖...

mysql配置优化基本信息: MySQL配置优化是提升数据库性能的关键步骤，特别是在面对CPU和I/O瓶颈时。CPU瓶颈往往出现在数据加载到内存或从磁盘读取时，而当数据量超过内存容量，磁盘I/O成为主要瓶颈。此外，如果数据库应用在网络环境中，网络...

MySQL高级优化文档: - **SHOW STATUS** 和 **SHOW VARIABLES**：获取MySQL服务器的运行状态和配置信息。 - **Percona Toolkit** 和 **pt-query-digest**：用于分析性能问题，提供改进建议。 9. **定期维护** - **索引重建**：定期...

MySQL索引优化课件: MySQL索引优化是数据库性能提升的关键技术之一，尤其在处理大量数据时，高效索引能够显著加快查询速度，降低服务器负载。本课件主要聚焦于MySQL数据库的索引原理、优化策略以及相关存储过程和触发器的应用。首先，...

MySql性能优化集合--满满的干货: #### 一、MySQL优化概述 MySQL作为一种广泛使用的开源关系型数据库管理系统，在数据处理方面表现优秀。然而，在高并发、大数据量的情况下，可能会遇到性能瓶颈。因此，进行MySQL性能优化是非常必要的。MySQL优化...

高并发基础之-MySql性能优化.pdf: 在MySQL中，我们可以通过`SHOW STATUS`语句来查看数据库的性能参数，如`Slow_queries`，它是记录执行时间超过预设阈值的SQL查询次数，这对于识别和优化慢查询非常有用。如果发现`Slow_queries`数值较高，应深入分析...

mysql_system_status.vbs: mysql_system_status.vbs

Global site tag (gtag.js) - Google Analytics