首页 > 数据库 >mysql 调优

mysql 调优

时间:2024-09-09 23:13:37浏览次数:12  
标签:buffer 缓冲 调优 innodb mysql Innodb pool size

一、缓冲池
​​​​​14.5.1 Buffer Pool
缓冲池是主内存中的一个区域,InnoDB在访问表和索引数据时将其缓存。缓冲池允许直接从内存访问经常使用的数据,从而加快处理速度。在专用服务器上,高达80%的物理内存通常分配给缓冲池。

为了提高大容量读取操作的效率,缓冲池被划分为可能容纳多行的页面。为了提高缓存管理的效率,缓冲池被实现为页面的链接列表;很少使用的数据会使用最不常用(LRU)算法的变体从缓存中过时。

了解如何利用缓冲池将频繁访问的数据保存在内存中是MySQL调优的一个重要方面。

二、innodb_buffer_pool_size
14.8.3.1 Configuring InnoDB Buffer Pool Size

innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

innodb_buffer_pool_size 默认是128M,

缓冲池的大小(字节),InnoDB缓存表和索引数据的内存区域。默认值为134217728字节(128MB)。最大值取决于CPU架构;32位系统的最大值为4294967295(2^32-1),64位系统的最大值为18446744073709551615(2^64-1)。在32位系统上,CPU体系结构和操作系统的实际最大大小可能低于规定的最大大小。当缓冲池的大小大于1GB时,将innodb_buffer_pool_instances设置为大于1的值可以提高繁忙服务器上的可伸缩性。

更大的缓冲池需要更少的磁盘I/O来多次访问相同的表数据。在专用数据库服务器上,可以将缓冲池大小设置为机器物理内存大小的80%。在配置缓冲池大小时,请注意以下潜在问题,并准备在必要时缩小缓冲池的大小。

对物理内存的竞争可能会导致操作系统中的分页。
InnoDB为缓冲区和控制结构保留额外的内存,因此总分配空间比指定的缓冲池大小大约大10%。
缓冲池的地址空间必须是连续的,这在具有在特定地址加载DLL的Windows系统上可能是一个问题。
初始化缓冲池的时间大致与其大小成正比。在具有大型缓冲池的实例上,初始化时间可能很长。要缩短初始化周期,可以在服务器关闭时保存缓冲池状态,并在服务器启动时恢复。参见第14.8.3.6节“保存和恢复缓冲池状态”。
当增加或减少缓冲池大小时,该操作将分块执行。区块大小由innodb_buffer_pool_chunk_size变量定义,该变量的默认值为128 MB。

缓冲池大小必须始终等于或是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。如果将缓冲池大小更改为不等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的值或其倍数,缓冲池大小将自动调整为等于或其倍数的值。

innodb_buffer_pool_size可以动态设置,这允许您在不重新启动服务器的情况下调整缓冲池的大小。Innodb_buffer_pool_resize_status变量报告在线缓冲池大小调整操作的状态。有关更多信息,请参阅第14.8.3.1节“配置InnoDB缓冲池大小”。

innodb_buffer_pool_chunk_size 默认是128M

innodb_buffer_pool_instances默认是8(如果innodb_buffer_pool_size < 1GB,则是1)

14.8.3.2 Configuring Multiple Buffer Pool Instances

2.1查看现有配置
mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+

2.2简单优化
把innodb_buffer_pool_size设置为1G。

专用服务器可以设为内存70%以上,个人建议innodb_buffer_pool_size设置为系统内存的50%。

最好设置为:innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

否则,innodb_buffer_pool_size自动调整可能是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的两倍。

my.cnf

# innodb缓冲池大小
innodb_buffer_pool_size=1G

# innodb缓冲池块大小
innodb_buffer_pool_chunk_size=128M

# innodb缓冲池实例数
innodb_buffer_pool_instances=8
重启数据库

调整后:

mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 1073741824 |
+-------------------------------------+----------------+

这些参数也支持在线调整,可考虑在业务低谷时调整。

Configuring InnoDB Buffer Pool Size Online

2.3配置是否合适
5.1.3 Server Option, System Variable, and Status Variable Reference
2.3.1查询缓存命中率:
mysql> show status like 'Innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 20294922 |
| Innodb_buffer_pool_read_ahead_evicted | 1240192 |
| Innodb_buffer_pool_read_requests | 299216558100 |
| Innodb_buffer_pool_reads | 1167281260 |
+---------------------------------------+--------------+

Innodb_buffer_pool_read_requests:逻辑读取请求的数量。
Innodb_buffer_pool_reads:InnoDB无法从缓冲池满足的逻辑读取数,必须直接从磁盘读取。
percent = innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%
上述的 percent>=99%,则表示当前的buffer pool满足当前的需求。否则需要考虑增加 innodb_buffer_pool_size的值。

2.3.2缓存数据页占比:
mysql> show status like 'Innodb_buffer_pool_pages%';
+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_data | 7003 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 19906085 |
| Innodb_buffer_pool_pages_free | 1021 |
| Innodb_buffer_pool_pages_misc | 167 |
| Innodb_buffer_pool_pages_total | 8191 |
+----------------------------------+----------+

innodb_buffer_pool_pages_data:InnoDB缓冲池中包含数据的页数。这个数字包括脏页和干净页。(使用压缩表时,报告的Innodb_buffer_pool_pages_数据值可能大于)
percent = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
上述的 percent>=95% 则表示当前的innodb_buffer_pool_size满足当前的需求。否则可以考虑增加 innodb_buffer_pool_size的值。

2.4如何判断MySQL使用内存会不会过高
可能还有有一些担心,所有参数设置完毕后MySQL的占用会过高导致内存溢出,那么我们可以算一下他会不会太高。
通过下面的SQL语句:
SELECT ((@@key_buffer_size+@@innodb_buffer_pool_size+@@innodb_log_buffer_size)/1024/1024)+((@@read_rnd_buffer_size+@@read_buffer_size+@@myisam_sort_buffer_size+@@sort_buffer_size+@@join_buffer_size)/1024/1024*@@max_connections);
最终单位为MB
若该值不超过系统可用内存,说明还好(理论)

2.5其他命令
mysql> show status like 'Innodb_buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 220313 7:31:02 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 6999 |
| Innodb_buffer_pool_bytes_data | 114671616 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 19905034 |
| Innodb_buffer_pool_pages_free | 1024 |
| Innodb_buffer_pool_pages_misc | 168 |
| Innodb_buffer_pool_pages_total | 8191 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 20294410 |
| Innodb_buffer_pool_read_ahead_evicted | 1240164 |
| Innodb_buffer_pool_read_requests | 299111990637 |
| Innodb_buffer_pool_reads | 1167212424 |
| Innodb_buffer_pool_wait_free | 1193110 |
| Innodb_buffer_pool_write_requests | 156029072 |
+---------------------------------------+--------------------------------------------------+

mysql> show engine innodb status \G
mysql> SHOW GLOBAL STATUS \G 太多了。

三、其他优化:
join_buffer_size = 8M (默认256K)
sort_buffer_size = 8M (默认256K)

read_buffer_size = 4M (默认128K)
read_rnd_buffer_size = 8M(默认256K)

mysql> show variables like '%buffer_size%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
+-------------------------+----------+
————————————————

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

原文链接:https://blog.csdn.net/haveqing/article/details/124788083

标签:buffer,缓冲,调优,innodb,mysql,Innodb,pool,size
From: https://www.cnblogs.com/php12-cn/p/18405580

相关文章

  • Python 操作 MySQL 数据库
    什么是MySQLdb?如何安装MySQLdb?数据库连接创建数据库表数据库插入操作数据库查询操作数据库更新操作删除操作执行事务错误处理Python标准数据库接口为PythonDB-API,PythonDB-API为开发人员提供了数据库应用编程接口。Python数据库接口支持非常多的数据库,你......
  • flask-docker更新(mysql更新为容器)
    前言之前用docker编写了一个flask的简单demo,但是里面的mysql用的本地的,当时是自己偷懒用了本地的,现在空余时间重新处理了mysql容器重新更新一下一、docker-compose.yml示例version:'3'services:flask:build:context:./appcontainer_name:flask......
  • Mysql Innodb存储引擎原理—链接如下
    MysqlInnodb存储引擎|ProcessOn免费在线作图,在线流程图,在线思维导图ProcessOn是一个在线协作绘图平台,为用户提供强大、易用的作图工具!支持在线创作流程图、思维导图、组织结构图、网络拓扑图、BPMN、UML图、UI界面原型设计、iOS界面原型设计等。同时依托于互联网实现了人......
  • 记录一次【截止目前最新版本MySql安装教程】MySql-9.0.1-winx64
    本次记录是目前最新版本9.0.1的安装记录,跟之前版本还是有区别的MySQL社区版下载地址:https://dev.mysql.com/downloads/mysql/安装整体步骤如下:下载MySQL版本;配置环境变量也可以不配置配置安装配置文件my.ini执行安装命令mysqld--install创建随机密码mysqld--initialize--con......
  • [MySQL]一次死锁排查
    字节面试题在可重复隔离级别下,这种情况会发生什么?答案:死锁下面我们用一个具体的例子来展示:首先我们有一张这样的表下面我们来执行一些语句,可以看到结果输出了,检测到发生了死锁Deadlockfoundwhentryingtogetlock;tryrestartingtransaction下面具体分析,首先......
  • 部署K8S集群(四):部署MySQL
    密码文件:MySQLSecret#VGVzdA==->Test#ZGJyZWFkZXI=->root#cGFzc0AxMjM0->pass@1234mkdir-p/opt/mysqlmkdir-p/data/mysqlcat>/opt/mysql/mysql-secret.yaml<<EOFapiVersion:v1kind:Secretmetadata:name:mysql-secrettype:......
  • MySQL——视图(二)视图管理(7)删除视图
            当视图不再需要时,可以将其删除,删除视图时,只能删除视图的定义,不会删除数据。删除一个或多个视图可以使用DROPVIEW语句,删除视图的基本语法格式如下所示:DROPVIEWIIEEXISTS]view_name[,view_name1]...[RESTRICT|CASCADE]        在上述......
  • MySQL——视图(三)应用实例——视图的应用
            本节将通过一个应用案例让读者熟练掌握在实际开发中创建并使用视图的完整过程。1.案例的目的        掌握视图的创建、查询、更新和删除操作。        假如有来自河北和山东的三个理科学生报考北京大学(PekingUniversity)和清华大学(Tsinghua......
  • MySQL的SQL语句
    一、SQL1、概念 SQL:StructureQueryLanguage(结构化查询语言),SQL最早是被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准。后来被国际化标准组织(ISO)采纳为关系型数据库语言的国际标准。​2、分类​1)DDL(DataDefinitionLanguage):数据定义语言,用来定义数据库对象......
  • JSP课程设计|基于Jsp和MySql实现的农场信息管理系统
    3.1基本开发环境配置根据上述要求,结合项目开发实际需要,我们将项目所需基本开发环境清单列出如下所示。操作系统:MicrosoftWindow10编程语言:Java编程IDE:JetBrains-IntellijIdeaUltimate服务器:Apache-Tomcat-9.0.6数据库:MySQL5.7.21CommunityServer由于这些环......