mysql8.0 性能优化配置 innodb_buffer_pool_size

这篇具有很好参考价值的文章主要介绍了mysql8.0 性能优化配置 innodb_buffer_pool_size。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

 一、缓冲池

15.5.1 Buffer Pool

缓冲池是主内存中的一个区域,InnoDB在访问表和索引数据时会在该区域进行缓存。缓冲池允许直接从内存访问频繁使用的数据,这加快了处理速度。在专用服务器上,通常会将高达80%的物理内存分配给缓冲池。
为了提高高容量读取操作的效率,缓冲池被划分为可能容纳多行的页面。为了提高缓存管理的效率,缓冲池被实现为页面的链接列表;很少使用的数据使用最近最少使用(LRU)算法的变体从高速缓存中老化。
了解如何利用缓冲池将频繁访问的数据保存在内存中是MySQL调优的一个重要方面。

二、innodb_buffer_pool_size

15.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系统上可能是一个问题。
  • 初始化缓冲池的时间与其大小大致成正比。在具有大型缓冲池的实例上,初始化时间可能很长。为了缩短初始化周期,可以在服务器关闭时保存缓冲池状态,并在服务器启动时恢复。参见第15.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或其倍数的值,则缓冲池大小将自动调整为等于innodd_buffer_pool_chunk_size*innodb_buffer_poor_instances或其多倍的值。

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

        如果启用了innob_dedicated_server,则如果未显式定义innodb_buffer_pool_size值,则会自动配置该值。有关更多信息,请参阅第15.8.12节“启用专用MySQL服务器的自动配置”。

innodb_buffer_pool_chunk_size 默认是128M

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

15.8.3.2 Configuring Multiple Buffer Pool Instances

2.1查看现有配置

/opt/mysql-8.0.32/bin/mysql -h 127.0.0.1 -u root -p

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_in_core_file     | ON             |
| 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     |
+-------------------------------------+----------------+
11 rows in set (0.01 sec)

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_in_core_file     | ON             |
| 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     |
+-------------------------------------+----------------+
11 rows in set (0.01 sec)

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

Configuring InnoDB Buffer Pool Size Online

2.3配置是否合适

5.1.4 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 230316 18:50:53 |
| Innodb_buffer_pool_resize_status          |                                                  |
| Innodb_buffer_pool_resize_status_code     | 0                                                |
| Innodb_buffer_pool_resize_status_progress | 0                                                |
| Innodb_buffer_pool_pages_data             | 11658                                            |
| Innodb_buffer_pool_bytes_data             | 191004672                                        |
| Innodb_buffer_pool_pages_dirty            | 0                                                |
| Innodb_buffer_pool_bytes_dirty            | 0                                                |
| Innodb_buffer_pool_pages_flushed          | 80730                                            |
| Innodb_buffer_pool_pages_free             | 53706                                            |
| Innodb_buffer_pool_pages_misc             | 172                                              |
| Innodb_buffer_pool_pages_total            | 65536                                            |
| Innodb_buffer_pool_read_ahead_rnd         | 0                                                |
| Innodb_buffer_pool_read_ahead             | 2529                                             |
| Innodb_buffer_pool_read_ahead_evicted     | 0                                                |
| Innodb_buffer_pool_read_requests          | 115191477                                        |
| Innodb_buffer_pool_reads                  | 6644                                             |
| Innodb_buffer_pool_wait_free              | 0                                                |
| Innodb_buffer_pool_write_requests         | 1598891                                          |
+-------------------------------------------+--------------------------------------------------+
20 rows in set (0.00 sec)

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

三、其他优化:

# 连接操作缓冲区,默认256K

join_buffer_size = 8M

# 排序操作缓冲区,默认256K
sort_buffer_size = 8M

# 顺序读取缓冲区,默认128K

read_buffer_size = 4M

# 随机读取缓冲区,默认128K
read_rnd_buffer_size = 8M

mysql> show variables like '%buffer_size%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608  |
| innodb_ddl_buffer_size  | 1048576  |
| 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   |
| select_into_buffer_size | 131072   |
| sort_buffer_size        | 262144   |
+-------------------------+----------+
12 rows in set (0.01 sec)

四、参考:

Mysql优化之innodb_buffer_pool_size篇

MySQL参数 之 innodb_buffer_pool_size

MySQL中innodb_buffer_pool_size的配置

MySQL基准测试innodb_buffer_pool_size对性能影响

五、文档

Chapter 8 Optimization

8.1 Optimization Overview

8.2 Optimizing SQL Statements

8.3 Optimization and Indexes

8.4 Optimizing Database Structure

8.5 Optimizing for InnoDB Tables

8.6 Optimizing for MyISAM Tables

8.7 Optimizing for MEMORY Tables

8.8 Understanding the Query Execution Plan

8.9 Controlling the Query Optimizer

8.10 Buffering and Caching

8.11 Optimizing Locking Operations

8.12 Optimizing the MySQL Server

8.13 Measuring Performance (Benchmarking)

8.14 Examining Server Thread (Process) Information

8.5 Optimizing for InnoDB Tables

8.5.1 Optimizing Storage Layout for InnoDB Tables

8.5.2 Optimizing InnoDB Transaction Management

8.5.3 Optimizing InnoDB Read-Only Transactions

8.5.4 Optimizing InnoDB Redo Logging

8.5.5 Bulk Data Loading for InnoDB Tables

8.5.6 Optimizing InnoDB Queries

8.5.7 Optimizing InnoDB DDL Operations

8.5.8 Optimizing InnoDB Disk I/O

8.5.9 Optimizing InnoDB Configuration Variables

8.5.10 Optimizing InnoDB for Systems with Many Tables

14.8.3 InnoDB Buffer Pool Configuration

14.8.3.1 Configuring InnoDB Buffer Pool Size

14.8.3.2 Configuring Multiple Buffer Pool Instances

14.8.3.3 Making the Buffer Pool Scan Resistant

14.8.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)

14.8.3.5 Configuring Buffer Pool Flushing

14.8.3.6 Saving and Restoring the Buffer Pool State文章来源地址https://www.toymoban.com/news/detail-490161.html

到了这里,关于mysql8.0 性能优化配置 innodb_buffer_pool_size的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MySQL索引与优化篇】InnoDB数据存储结构

    索引结构给我们提供了高效的索引方式,不过索引信息以及数据记录都是保存在文件上的,确切说是存储在页结构中。另一方面,索引是在存储引擎中实现的,MySQL服务器上的 存储引擎 负责对表中数据的读取和写入工作。不同存储引擎中 存放的格式 一般是不同的。 由于 I

    2024年02月07日
    浏览(52)
  • MYSQL8优化

    从 设计 上优化 从 查询 上优化 从 索引 上优化 从 存储 上优化 参数 解释 Com_select 执行select操作的次数,一次查询只累加1 Com_insert 执行INSERT操作的次数,对于批量插入的INSERT操作,值累加一次 Com_update 执行UPDATE操作的次数 Com_delete 执行DELETE操作的次数 Innodb_rows_read select查询

    2023年04月21日
    浏览(31)
  • MySQL8.0 优化器介绍(三)

    GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。 GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。 作者: 奥特曼爱小怪兽 文章来源:GreatSQL社区原创 往期回顾 MySQL8.0 优化器介绍(一) MySQL8.0 优化器介绍(二) 本篇将进一步深入介绍优化器相关的

    2023年04月19日
    浏览(33)
  • MySQL8.0 优化器介绍(二)

    GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。 GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。 作者: 奥特曼爱小怪兽 文章来源:GreatSQL社区投稿 上一篇 MySQL8.0 优化器介绍(一)介绍了成本优化模型的三要素:表关联顺序,与每张表返回的行数

    2023年04月17日
    浏览(32)
  • 深入探索MySQL的innodb_thread_concurrency配置

    含义和作用 innodb_thread_concurrency 参数限定了InnoDB存储引擎可以同时执行的线程数目。这个设置有助于控制并发操作,从而影响数据库处理事务的效率。通过限制活跃线程数量,可减少上下文切换的开销,避免过多的并发线程竞争资源导致性能降低。 查询和配置方式 查询当前

    2024年01月25日
    浏览(57)
  • MySQL8配置文件详解

    MySQL 8 是一款常用的关系型数据库管理系统,它提供了全面而强大的功能来满足不同的数据管理需求。为了更好地控制其功能和性能,配置文件是MySQL 8的重要组成部分之一。在此文章中,我们将详细介绍MySQL 8的配置文件。 MySQL 8 的配置文件是一个文本文件,它包含了许多用于

    2024年02月17日
    浏览(53)
  • Mysql8.0 安全配置

            目前业界的MySQL主流分支版本有Oracle官方版本的MySQL、Percona Server for mysql、MariaDB 。每个分支都有自己的特色,功能也存在不同的差异。我们平常使用的官方版本是指的ORACLE官方版本的Mysql, 官方版本主要分为社区版和企业版 , 两者的差异主 要在一些定制插件方面

    2024年02月17日
    浏览(42)
  • Mysql8安装教程与配置

                            添加密码       出现报错 MySQL error 1042: Unable to connect to any of the specified MySQL hosts. Failed to connect to MySQL Server 8.0.23 after 10 attempts. Ended configuration step: Starting the server 使用Win+R -- 运行 “services.msc” -- 打开service服务管理器,找到刚才安装mysql的服务名称

    2024年02月13日
    浏览(47)
  • MySQL8.0的下载、安装、配置

    1.1 MySQL四大版本 MySQL Community Server 社区版本,开源免费,自由下载,但不提供官方技术支持,适用于 大多数普通用户。 MySQL Enterprise Edition 企业版本,需付费,不能在线下载,可以试用30天。提供了更多的 功能和更完备的技术支持,更适合于对数据库的功能和可靠性要求较高

    2024年02月08日
    浏览(45)
  • Ubuntu服务器配置mysql8

    先更新apt-get apt-get update 安装mysql apt-get install mysql-server-8.0 修改mysql的root密码,ubuntu安装的服务器默认在本机是不需要密码的, 先进入MySQL mysql -uroot -p , 使用mysql数据库 use mysql; , 然后执行 alter user \\\'root\\\'@\\\'localhost\\\' identified by \\\'你的密码\\\'; 最后刷新信息 FLUSH PRIVILEGES; 先进入MySQL mysq

    2024年02月16日
    浏览(49)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包