MySQL核心参数优化文件my.ini实现

目录
  • 一.数据库服务器配置
  • 二.CPU的优化
  • 三.内存的优化
  • 四.IO的优化
  • 五.连接的优化
  • 六.数据一致性的优化

一.数据库服务器配置

CPU:48C
内存:128G
DISK:3.2TSSD

二.CPU的优化

innodb_thread_concurrency=32
表示SQL经过解析后,允许同时有32个线程去innodb引擎取数据,如果超过32个,则需要排队;
值太大会产生热点数据,global锁争用严重,影响性能

三.内存的优化

query_cache_type=0
query_cache_size=0
缓存查询,5.6默认关闭,在应用层实现,比如MC、redis

四.IO的优化

1.innodb_buffer_pool_size=50G

类似SGA,衡量总的IO处理能力上限,一般为物理内存的60%-70%,这里128G部署2个实例,剩下28G分配给OS和mysql连接等使用

2.innodb_io_capacity=20000

每秒后台进程处理IO数据的上限,一般为IO QPS总能力的75%
比如SSD是3W QPS,75%大概是2W,双实例减半,为1W,几个实例除以几

3.innodb_log_files_in_group=4

几个innodb redo log日志组

4.innodb_log_file_size=1000M

redo log日志循化写,生产必须大于1G,
如果太小,那么innodb_buffer_pool_size的数据有可能不能及时写入redo log造成halt等待;查看是否够用?如果value大于0,则提高改参数或者增加日志组

root@master 12:51:  [(none)]> show global status like '%log_wait%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 0     |
+------------------+-------+
1 row in set (0.00 sec)
root@master 12:54:  [(none)]> show global status like '%Innodb_os_log_written%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Innodb_os_log_written | 1024  |
+-----------------------+-------+
1 row in set (0.00 sec)
#此参数大小可作为设置日志文件size大小参考值

5.innodb_flush_method=O_DIRECT

SSD直接写硬盘,不写硬盘cache,也就是绕过fsync()刷硬盘

6.innodb_max_dirty_pages_pct=50

当脏块达到innodb_buffer_pool_size的50%时,触发检查点,写磁盘

7.innodb_file_per_table=on

一表一文件,可以避免共享表空间的IO竞争

8.innodb_page_size=4k

默认是16K,这里是SSD,写SSD前要擦除,擦除单位是extent,一个extent有128个page组成,16128 > 4128 ,效率会更高

9.innodb_flush_neighbors=0

SSD设置为0,SAS打开刷新相邻块,随机访问转换为顺序访问

五.连接的优化

1.back_log=300

默认是50,TCP/IP的连接数量,一个连接占用256KB内存,最大是64MB,256 * 300 =75MB内存
和三次握手有关系

syn_queue取64和tcp_max_sync_backlog最大者,默认是1024,当瞬时很多连接进来这个参数会进行限制,否则太大容易消耗资源
accept queue取back_log和somaxconn最小者,用来防止丢包,当瞬时很多连接进来达到上限后,后来连接将超时触发重传机制
当有3000个连接进来,将队列accept queue占满,应用还没来得及将请求从队列中取出,剩下的2700个连接将被拒绝,每取走一个请求(一个连接,mysql一个线程一个连接),将创建一个thread线程

net.ipv4.tcp_max_sync_backlog= 8192  类似活动场所
sync接收队列的长度,默认是1024,当mysql在很短时间内得到很多的请求,需要增加,太大会消耗资源
太小的话会在show processlist出现未认证错误
net.core.somaxconn=1024   类似活动场所中的座位数
尽可能防止丢包,超过这个值会触发超时或者重传,限制在net.ipv4.ip_local_port_range这个范围之内

2.max_connections=3000

连接的创建和销毁都需要系统资源,比如内存、文件句柄
业务说的支持多少并发,指的是每秒请求数,也就是QPS
同一时刻并行的SQL由innodb_thread_concurrency决定,最大不能超过该值
如果一个用户的请求数据超过64MB(比如排序),就会申请临时空间,放到硬盘上
如果3000个用户同时连上mysql,最小需要内存3000256KB=750M,最大需要内存300064MB=192G,如果innodb_buffer_pool_size是80GB,可用内存不到48G,192GB>48GB,将会产生SWAP,此时将会影响性能
连接数过高,不一定带来吞吐量的提高,而且可能占用更多的系统资源
一个DB 3W QPS计算,前端有100个web服务器,每个web服务器需要300个QPS,每个QPS占用时间=网络来回时间+SQL执行时间,以20ms计算,需要6个连接数(300/1000/20ms=6)
示例1:有100台web服务器,PHP/JAVA的最大连接数可设置为:3000/100=30
示例2:有30台web服务器,要扩容到60台,web服务器连接数怎么配置?web服务器最大连接数:之前是3000/30=100,现在3000/60=50即可

3.max_user_connections=2980

剩余连接数用作管理

4.table_open_cache=1024

打开表的缓存,跟表数量没关系
1000个连接上来,都需要访问A表,那么会打开1000个表,打开1000个表是指mysql创建1000个这个表的对象,连接直接访问表对象,类似会把这张表做一个class,1000个连接都访问这个表对象,当表对象没了,重新new一个,不需要每次都打开物理表

root@master 14:44:  [(none)]> show variables like '%table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 1024  |
+------------------+-------+
1 row in set (0.00 sec)
root@master 14:46:  [(none)]> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 19    |
| Opened_tables | 113   |
+---------------+-------+
2 rows in set (0.00 sec)

可以考虑设置为max_connections或者max_connections*查询同时用到的表个数或者

5.thread_cache_size=512

都是短连接进来容易产生短连接风暴
会话层:事务状态、认证会话
连接层:网络连接、包传输
一个用户 对应 一个session 对应 一个connection
connection - thread:操作系统调用
3000个用户进来使用cache的512个线程,用完就放回去,避免创建、销毁线程的开销

6.wait_timeout=120

指的是app应用连接mysql进行操作完毕后,空闲120秒后断开

7.interactive_timeout=120

指的是mysql client连接mysql进行操作完毕后,空闲120秒后断开

六.数据一致性的优化

1.innodb_flush_log_at_trx_commit=1

0,不管有没有提交,每秒钟都写到binlog日志里
1,每次提交事务,都会把log buffer的内容写到磁盘里去,对日志文件做到磁盘刷新,安全最好
2,每次提交事务,都写到操作系统缓存,由OS刷新到磁盘,性能最好

2.sync_binlog=1

0,事务提交后,mysql不做fsync之类的刷盘,由文件系统来决定什么落盘
n,多少次提交,每n次提交持久化磁盘
生产设为1

3.日志写盘过程

1)三个update会话,三个线程都会产生的操作日志
2 )commit后提交到公共的cache中,三个进程之间不能相互看到对方的操作内容
3)经过write写入到标准I/O cache中,也就是文件系统句柄,线程缓存
4)如果需要让其他线程看到文件句柄内容,就需要通过flush刷新到全局可见文件系统缓存
5)最后最重的一步是将内存数据sync落盘

到此这篇关于MySQL核心参数优化文件my.ini实现的文章就介绍到这了,更多相关MySQL my.ini内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql my.ini 配置文件详解

    Mysql my.ini 配置文件详解 #BEGIN CONFIG INFO #DESCR: 4GB RAM, 只使用InnoDB, ACID, 少量的连接, 队列负载大 #TYPE: SYSTEM #END CONFIG INFO # # 此mysql配置文件例子针对4G内存 # 主要使用INNODB #处理复杂队列并且连接数量较少的mysql服务器 # # 将此文件复制到/etc/my.cnf 作为全局设置, # mysql-data-dir/my.cnf 作为服务器指定设置 # (@loc

  • mysql5.7以上版本配置my.ini的详细步骤

    Windows 64 位 mysql 5.7以上版本包解压中没有data目录和my-default.ini和my.ini文件以及服务无法启动的解决办法以及修改初始密码的方法. mysql官网下载地址 以5.7.20版本为例 首先安装包解压后,没有网上教程里面提到的data文件夹和my-default.ini,如下图所示 配置环境变量请参照网上教程,这个一般没有问题,即在环境变量中Path中加入MYSQL解压包bin所在路径,我的是: D:\softnew\MYSQL\mysql-5.7.20-w

  • mysql中找不到my.ini文件的问题及解决

    目录 mysql找不到my.ini文件 问题描述 解决办法 mysql免安装版找不到my.ini配置文件 mysql找不到my.ini文件 问题描述 刚刚在修改mysql默认配置的时候,发现找不到my.ini文件.按照其他搬运工的说法,打开隐藏的文件也是不行.自己摸索了半天终于找到解决方案. 解决办法 1.打开“隐藏文件”显示: 2.在url哪里输入: %ProgramData% 2.1.回车以后会得到如下结果,打开mysql文件夹 2.2.然后会得到如下 打开MySQLServer那个文件夹里

  • 对MySQL配置参数 my.ini/my.cnf的详细解析

    以下的文章主要描述的是对MySQL配置参数 my.ini/my.cnf的详细解析,我们主要是以实例演示的方式来对MySQL配置参数 my.ini/my.cnf的实际操作步骤进行说明,以下就是相关内容的具体描述. 1.获取当前配置参数 要优化MySQL配置参数,首先要了解当前的配置参数以及运行情况.使用下列命令可以获得目前服务器使用的配置参数: 复制代码 代码如下: mysqld –verbose –help mysqladmin variables extended-status –u root

  • MySQL5.7缺少my.ini文件的解决方法

    my.ini是什么? my.ini是MySQL数据库中使用的配置文件,修改这个文件可以达到更新配置的目的. my.ini存放在哪里? my.ini存放在MySql安装的根目录,如图所示: 我安装的MySQL 5.7缺少my.ini文件,需要自己创建. 大致内容如下 [mysqld] character-set-server=utf8 #绑定IPv4和3306端口 bind-address = 0.0.0.0 port = 3306 sql_mode="STRICT_TRANS_TABLES,NO

  • MySQL5.6.17数据库安装 如何配置My.ini文件

    最近在做项目开发时用到了MySql数据库,在看了一些有关MySql的文章后,很快就上手使用了.在使用的过程中还是出现了一些问题,因为使用的是绿色免安装版的MySql所以在配置的时候出现了一些问题,该篇文章就主要针对MySql绿色版的配置及其使用进行讨论. 一.MySql概述 MySql数据库是有瑞典MySql AB公司开发,现在该公司被Oracle收购属于Oracle所有.同SQL Server类似,它也是基于关系型数据库的数据库管理系统,在Web应用方面MySQL是最好的RDBMS之一,因为它

  • MySQL8.0中的my.ini文件位置说明

    目录 MySQL8.0的my.ini文件位置 my.ini文件所在位置及如何利用记事本编辑其内容 如何找到my.ini文件 如何以管理员权限编辑 my.ini 文件 MySQL8.0的my.ini文件位置 MySQL8.0默认安装路径C:\Program Files\MySQL,安装文件夹内容如图: 发现在安装目录中找不到my.ini文件,该文件在C:\ProgramData\MySQL\MySQL Server 8.0目录下. 如图: 另外,数据库文件也在该目录的Data文件夹(即:C:\Pr

  • MySQL核心参数优化文件my.ini实现

    目录 一.数据库服务器配置 二.CPU的优化 三.内存的优化 四.IO的优化 五.连接的优化 六.数据一致性的优化 一.数据库服务器配置 CPU:48C内存:128GDISK:3.2TSSD 二.CPU的优化 innodb_thread_concurrency=32表示SQL经过解析后,允许同时有32个线程去innodb引擎取数据,如果超过32个,则需要排队:值太大会产生热点数据,global锁争用严重,影响性能 三.内存的优化 query_cache_type=0query_cache_siz

  • MYSQL配置参数优化详解

    MySQL参数优化对于不同的网站,及其在线量,访问量,帖子数量,网络情况,以及机器硬件配置都有关系,优化不可能一次性完成,需要不断的观察以及调试,才有可能得到最佳的效果. 1)连接请求的变量 1.max_connections MySQL的最大连接数,如果服务器的并发连接请求量较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,MySQL回味每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值. 数值过小经常会出现ERROR 1

  • MySQL性能全面优化方法参考,从CPU,文件系统选择到mysql.cnf参数优化

    本文整理了一些MySQL的通用优化方法,做个简单的总结分享,旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作,至于具体的SQL优化,大部分通过加适当的索引即可达到效果,更复杂的就需要具体分析了,可以参考本站的一些优化案例或者联系我们 1.硬件层相关优化 1.1.CPU相关 在服务器的BIOS设置中,可调整下面的几个配置,目的是发挥CPU最大性能,或者避免经典的NUMA问题: 1.选择Performance Per Watt Optimized(DAPC)模式,发挥CPU最大性能,跑

  • mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

    此配置是老男孩生产线上使用的配置,在培训的时候,他给的,我在这里,对各参数添加了中文说明 这配置已经优化的不错了,如果你的mysql没有什么特殊情况的话,可以直接使用该配置参数 MYSQL服务器my.cnf配置文档详解 硬件:内存16G [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/my

  • MySQL优化配置文件my.ini(discuz论坛)

    在Apache, PHP, MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数. 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化. 一.服务

  • my.cnf(my.ini)重要参数优化配置说明

    MyISAM存储引擎 MyISAM存储引擎适用于读多写少,对读性能要求比较高的系统 官方文档:http://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html Key_buffer_size,可以设置为内存的30%-40%左右.通过show variables like '%key_buffer_size%'; 通过 show global status like '%key_blocks_unused%' 查看是否还有剩余,如果

  • PHP中关于php.ini参数优化详解

    PHP引擎php.ini参数优化 无论是apache还是nginx,php.ini都是适合的.而php-fpm.conf适合nginx+fcgi的配置 首先选择产品环境的php.ini(php.ini-production) /home/oldboy/tools/php-5.3.27/php.ini-development /home/oldboy/tools/php-5.3.27/php.ini-production 1.打开php的安全模式 php的安全模式是个非常重要的php内嵌的安全机制

  • PHP引擎php.ini参数优化深入讲解

    PHP引擎php.ini参数优化 无论是apache还是nginx,php.ini都是适合的.而php-fpm.conf适合nginx+fcgi的配置 首先选择产品环境的php.ini(php.ini-production) /home/oldboy/tools/php-5.3.27/php.ini-development /home/oldboy/tools/php-5.3.27/php.ini-production 1.打开php的安全模式 php的安全模式是个非常重要的php内嵌的安全机制

  • MySQL配置文件my.cnf参数优化和中文详解

    Mysql参数优化对于新手来讲,是比较难懂的东西,其实这个参数优化,是个很复杂的东西,对于不同的网站,及其在线量,访问量,帖子数量,网络情况,以及机器硬件配置都有关系,优化不可能一次性完成,需要不断的观察以及调试,才有可能得到最佳效果. 复制代码 代码如下: [client]port = 3306socket = /tmp/mysql.sock [mysqld]port = 3306socket = /tmp/mysql.sock basedir = /usr/local/mysqldatadi

随机推荐