search
HomeDatabaseMysql Tutorial[MySQL生产环境] Innodb存储引擎内存报警问题处理过程_MySQL

bitsCN.com

 [MySQL生产环境] Innodb存储引擎内存报警问题处理过程

 

1 不停的收到email报警,内存值超过阀值80%了。

 

2 top下,mysqld进程确实占据了77.5%,再加上一些其他的辅助进程,内存usage到了81%也可以理解。

[xxx@00903 5.5.25a]$ toptop - 03:48:55 up 51 days, 17:11,  2 users,  load average: 0.09, 0.09, 0.11Tasks: 202 total,   1 running, 201 sleeping,   0 stopped,   0 zombieCpu(s):  0.2%us,  0.1%sy,  0.0%ni, 98.8%id,  0.8%wa,  0.0%hi,  0.0%si,  0.0%stMem:  28743468k total, 28452540k used,   290928k free,   467048k buffersSwap:  4194296k total,        0k used,  4194296k free,  4589332k cached  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                         23956 mysql     20   0 24.1g  21g 5408 S  1.0 77.5 390:56.59 mysqld                                                                                                                                9 root      20   0     0    0    0 S  0.3  0.0 203:16.51 ksoftirqd/1                                                                                                                      23971 mmmd      20   0  687m  64m 1960 S  0.3  0.2  73:53.23 perl                                                                                                                                 1 root      20   0 21444 1232  928 S  0.0  0.0   1:29.37 init                                                                                                                                 2 root      20   0     0    0    0 S  0.0  0.0   0:00.44 kthreadd                                                                                                                         3 看看free -m吧[xxx@00903 ~]$ free -m             total       used       free     shared    buffers     cachedMem:         28069      27828        240          0        440       -/+ buffers/cache:      22820       5249Swap:         4095          0       4095[xxx@00903 ~]$ 4 简要分析理解free -m参数值意义total: 28069,总内存大小。used: 27828,已经使用过的内存大小。free:240,剩余的内存大小。total值是used+free的值总合:[sql] mysql> select 27828+240;  +-----------+  | 27828+240 |  +-----------+  |     28068 |  +-----------+  1 row in set (0.00 sec)  -- 总和28068与28069差了一个1,可能是-m四舍五入造成的。  mysql>   

 

 

shared 不同进程间用来进行数据交换的地方,一般都是0(多个进程共享的内存总额)。

cached:4568,经常被用在磁盘的I/O请求上,如果有多个进程都要访问某个文件,于是该文件便被做成cache以方便下次被访问,这样可提供系统性能。

 

 

 

我们监控的是-/+ buffers/cache:中的useed一项22820, 总内存大小是 28069,因此一计算,确实为81%

[sql] mysql>   mysql> SELECT 22828/28069;  +-------------+  | 22828/28069 |  +-------------+  |      0.8133 |  +-------------+  1 row in set (0.00 sec)    mysql>   而且-/+ buffers/cache中的后面的used+free的总合也与第一行的Mem的total值相等:[sql] mysql> SELECT 22820+5249;  +------------+  | 22820+5249 |  +------------+  |      28069 |  +------------+  1 row in set (0.00 sec)  

 

目前没有找到问题所在,去查阅一些基础文档,有很多东西时间一长不用,都快要遗忘了

5 buffers与cached的分析

对操作系统来讲是Mem的参数buffers和cached 都是属于被使用,它认为free只有752M。

        对应用程序来讲是(-/+ buffers/cach),buffers和cached 是等同可用的,buffer和cached是为了提高程序执行的性能,当程序使用内存时,buffer和cached会很快地被使用。

        以应用来看看,以(-/+ buffers/cache)的free和used为主.我们看这个就好了.Linux为了提高磁盘和内存存取效率, 开发人员做了很多精心的设计, 除了对dentry进行缓存(用于VFS,加速文件路径名到inode的转换), 还采取了两种主要Cache方式:Buffer Cache和Page Cache.前者针对磁盘块的读写,后者针对文件inode的读写.这些Cache能有效缩短了 I/O系统调用(比如read,write,getdents)的时间.

 

6 innodb_buffer_pool_size(global)

当我们使用InnoDB存储引擎的时候,innodb_buffer_pool_size 参数可能是影响我们性能的最为关键的一个参数了,他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小,类似于 MyISAM 存储引擎的 key_buffer_size 参数,当然,可能更像是 Oracle 的 db_cache_size。简单来说,当我们操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会在这个内存区域中走一遭。

 

和key_buffer_size 对于 MyISAM 引擎一样,innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小,直接关系到 InnoDB存储引擎的性能,所以如果我们有足够的内存,尽可将该参数设置到足够打,将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中,直至全部。

 

我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率,并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。

 

7 MySQL Query Cache

在大部分的 MySQL 分发版本中,Query Cache 功能默认都是打开的,我们可以通过调整 MySQL Server 的参数选项打开该功能。主要由以下5个参数构成:

 

query_cache_limit:允许 Cache 的单条 Query 结果集的最大容量,默认是1MB,超过此参数设置的 Query 结果集将不会被 Cache

query_cache_min_res_unit:设置 Query Cache 中每次分配内存的最小空间大小,也就是每个 Query 的 Cache 最小占用的内存空间大小

query_cache_size:设置 Query Cache 所使用的内存大小,默认值为0,大小必须是1024的整数倍,如果不是整数倍,MySQL 会自动调整降低最小量以达到1024的倍数

query_cache_type:控制 Query Cache 功能的开关,可以设置为0(OFF),1(ON)和2(DEMAND)三种,意义分别如下:

0(OFF):关闭 Query Cache 功能,任何情况下都不会使用 Query Cache

1(ON):开启 Query Cache 功能,但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后,将不使用Query Cache

2(DEMAND):开启 Query Cache 功能,但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后,才使用 Query Cache

query_cache_wlock_invalidate:控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache,如果设置为 1(TRUE),则在写锁定的同时将失效该表相关的所有 Query Cache,如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache。

 

重新补充了mysql的缓存基础知识,然后根据slow log查到了有一些慢sql,可是如何释放已经的缓存呢? 用了flush query cache; 没有效果,内存使用率仍然在81%。

 

知道mysql在执行查询的时候,特别是需要table scan的时候,数据是一点点进入内存的,在mysql的buffer pool中有链表结构的存在,page是io的基本单位,一个个的page读进去,即使只访问一条记录,也要读一个page,这是没办法的事情,table scan的表记录数越多,读到内存的page就越多,这样内存就慢慢涨到了81%了。正常处理流程是:当内存涨的时候首先是swap,在swap加警告,然后告警后关掉swap,之后就是排查问题,没必要内存到90%就搞掉。

 

记得Mysql不都是会自动释放内存资源的吗?为什么线上的db没有释放,这只是一台replication从库,用来做备份恢复所用的,没有应用业务在使用。

 

之后过了2小时,在慢查询sql执行完毕过后2小时,内存使用率自动降下来了,回到75%了。

bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How to install MySQL on Linux systemHow to install MySQL on Linux systemApr 29, 2025 pm 03:57 PM

Installing MySQL on Linux can be done through the package manager. The specific steps are as follows: 1. On Ubuntu, use apt to update the package list and install the MySQL server; 2. On CentOS, use yum to install the MySQL community version and start the service. After installation, basic configuration needs to be performed, such as setting the root password and creating database and users.

Creation and usage scenarios of MySQL viewsCreation and usage scenarios of MySQL viewsApr 29, 2025 pm 03:54 PM

MySQL views are virtual tables generated based on SQL queries. 1. Create a view: Use the CREATEVIEW statement combined with SELECT query. 2. Usage scenarios: simplify complex queries, data abstraction and ensure data consistency. 3. Optimization strategy: simplify underlying queries, use indexes, and consider materialized views.

Specify character sets and collations when creating databases in MySQLSpecify character sets and collations when creating databases in MySQLApr 29, 2025 pm 03:51 PM

When creating a database in MySQL, character sets and collation rules should be specified to ensure data accuracy and improve query performance. 1) Use the CREATEDATABASEmy_databaseCHARACTERSETutf8mb4COLLATIONutf8mb4_unicode_ci command to create a database, select the utf8mb4 character set and utf8mb4_unicode_ci collation. 2) utf8mb4 supports more Unicode characters, while utf8mb4_unicode_ci provides case-insensitive comparisons. 3) Ensure that the application layer and database layer use the same character set and collation rules to avoid potential problems.

How to sort and rank data in MySQLHow to sort and rank data in MySQLApr 29, 2025 pm 03:48 PM

In MySQL, sorting uses the ORDERBY clause, and ranking uses the RANK(), DENSE_RANK(), and ROW_NUMBER() functions. 1. Sort: Use ORDERBY clause, such as SELECT*FROMemployeesORDERBYsalaryDESC; 2. Ranking: Use window functions, such as SELECTemployee_name, salary, RANK()OVER(ORDERBYsalaryDESC)ASrankFROMemployees; these operations are based on SQL query optimizer and execution engine, and are often used to sort quickly or merge sort, and ranking depends on window function calculation.

Creation and calling methods of MySQL stored proceduresCreation and calling methods of MySQL stored proceduresApr 29, 2025 pm 03:45 PM

To create and call stored procedures in MySQL, follow the following steps: 1. Create stored procedures: Use the CREATEPROCEDURE statement to define stored procedures, including names, parameters, and SQL statements. 2. Compile stored procedures: MySQL compiles stored procedures into executable code and stores them. 3. Call stored procedure: use CALL statement and pass parameters. 4. Execute stored procedures: MySQL executes the SQL statements in it, processes parameters and returns the result.

How to set up MySQL service automatically startsHow to set up MySQL service automatically startsApr 29, 2025 pm 03:42 PM

The MySQL service can be set to automatically start on Windows, Linux, and macOS. 1) On Windows, use the command "scconfigmysqlstart=auto" to configure. 2) On Linux, enable it using "sudosystemctlenablemysql". 3) On macOS, create and load the launchd configuration file to achieve automatic startup.

How to view detailed structure information of MySQL tablesHow to view detailed structure information of MySQL tablesApr 29, 2025 pm 03:39 PM

The methods to view the MySQL table structure include: 1. Use the DESCRIBE command to view column information; 2. Use the SHOWCREATETABLE command to view table creation statements; 3. Use information_schema to query more detailed information. These methods help to quickly understand table structure and improve work efficiency.

Detailed explanation of the installation steps of MySQL on macOS systemDetailed explanation of the installation steps of MySQL on macOS systemApr 29, 2025 pm 03:36 PM

Installing MySQL on macOS can be achieved through the following steps: 1. Install Homebrew, using the command /bin/bash-c"$(curl-fsSLhttps://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)". 2. Update Homebrew and use brewupdate. 3. Install MySQL and use brewinstallmysql. 4. Start MySQL service and use brewservicesstartmysql. After installation, you can use mysql-u

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment