MySQL在高内存、IO利用率上的几个优化点
以下优化都是基于CentOS系统下的一些MySQL优化整理,有不全或有争议的地方望继续补充完善。
以下优化都是基于CentOS系统下的一些MySQL优化整理,有不全或有争议的地方望继续补充完善。
一、mysql层面优化
设置0是事务log(ib_logfile0、ib_logfile1)每秒写入到log buffer,1是时时写,2是先写文件系统的缓存,每秒再刷进磁盘,和0的区别是选2即使mysql崩溃也不会丢数据。
脏页写的线程数,加大该参数可以提升写入性能.mysql5.5以上才有。
当系统中 脏页 所占百分比超过这个值,INNODB就会进行写操作以把页中的已更新数据写入到磁盘文件中。默认75,一般现在流行的SSD硬盘很难达到这个比例。可依据实际情况在75-80之间调节
从缓冲区刷新脏页时,一次刷新脏页的数量。根据磁盘IOPS的能力一般建议设置如下:
SAS 200SSD 5000PCI-E 10000-50000
控制innodb数据文件和redo log的打开、刷写模式。有三个值:fdatasync(默认),O_DSYNC,O_DIRECT。
三种模式如下图:
通过图可以看出O_DIRECT相比fdatasync的优点是避免了双缓冲,本身innodb buffer pool就是一个缓冲区,不需要再写入到系统的buffer,但是有个缺点是由于是直接写入到磁盘,所以相比fdatasync的顺序读写的效率要低些。在大量随机写的环境中O_DIRECT要比fdatasync效率更高些,顺序写多的话,还是默认的fdatasync更高效。
影响每秒刷新脏页的数目。规则由原来的“大于innodb_max_dirty_pages_pct时刷新100个脏页到磁盘”变为 “通过buf_flush_get_desired_flush_reate函数判断重做日志产生速度确定需要刷新脏页的最合适数目”;即使脏页比例小于 innodb_max_dirty_pages_pct时也会刷新一定量的脏页。
影响checkpoint,更平均的计算调整刷脏页的速度,进行必要的flush.(该变量为mysql衍生版本Percona Server下的一个变量,原生mysql不存在)
关掉一些访问information_schema库下表而产生的索引统计。
当重启mysql实例后,mysql会随机的io取数据遍历所有的表来取样来统计数据,这个实际使用中用的不多,建议关闭.
当更新/插入的非聚集索引的数据所对应的页不在内存中时(对非聚集索引的更新操作通常会带来随机IO),会将其放到一个insert buffer中,当随后页面被读到内存中时,会将这些变化的记录merge到页中。当服务器比较空闲时,后台线程也会做merge操作。
由于主要用到merge的优势来降低io,但对于一些场景并不会对固定的数据进行多次修改,此处则并不需要把更新/插入操作开启change_buffering,如果开启只是多余占用了buffer_pool的空间和处理能力。这个参数要依据实际业务环境来配置。
使Block在old sublist中停留时间长为1s,不会被转移到new sublist中,避免了Buffer Pool被污染BP可以被认为是一条长链表。被分成young 和 old两个部分,其中old默认占37%的大小(由innodb_old_blocks_pct 配置)。靠近顶端的Page表示最近被访问。靠近尾端的Page表示长时间未被访问。而这两个部分的交汇处成为midpoint。每当有新的Page需要加载到BP时,该page都会被插入到midpoint的位置,并声明为old-page。当old部分的page,被访问到时,该page会被提升到链表的顶端,标识为young。
由于table scan的操作是先load page,然后立即触发一次访问。所以当innodb_old_blocks_time =0 时,会导致table scan所需要的page不读的作为young page被添加到链表顶端。而一些使用较为不频繁的page就会被挤出BP,使得之后的SQL会产生磁盘IO,从而导致响应速度变慢。
这时虽然mysqldump访问的page会不断加载在LRU顶端,但是高频度的热点数据访问会以更快的速度把page再次抢占到LRU顶端。从而导致mysqldump加载入的page会被迅速刷下,并立即被evict(淘汰)。因此,time=0或1000对这种压力环境下的访问不会造成很大影响,因为dump的数据根本抢占不过热点数据。不只dump,当大数据操作的时候也是如此。
二、mysql系统层面优化
numa策略引入了node的概念,每个物理CPU都被视为一个node,而每个node都有一个local memory,相对这个node之外的其它node都属于外部访问。
NUMA的内存分配策略有localalloc(默认)、preferred、membind、interleave。
每个进程(或线程)都会分配一个优先node,对于系统默认的localalloc策略会有一个问题,对于mysql这种几乎占满整个系统内存的应用来说,很容就把某个node的资源给占满,若Linux又把一个大的资源分配到这个已经占满资源的node时,会资源不足,造成内存数据于磁盘进行交换,或者摒弃buffer_pool里的活跃数据。在实际测试中发现比如有node0、node1 两个物理node,当系统负载很高的时候,node0资源被占满,node1虽然仍有部分空闲内存,但是系统即使进行内存到磁盘交换也不会去利用node1上的空闲资源。
因此建议对于像mysql这样的单实例的庞大复杂的进程来说,关闭numa或者设置策略为交织分配内存更合理。但对于一个机器上有多少个实例,可以每个实例绑定一个CPU核上。然后就可以充分利用numa的特性,更高效。
2.增加本地端口,以应对大量连接
echo ‘1024 65000′ > /proc/sys/net/ipv4/ip_local_port_range
该参数指定端口的分配范围,该端口是向外访问的限制。mysql默认监听的3306端口即使有多个请求链接,也不会有影响。但是由于mysql是属于高内存、高cpu、高io应用,不建议把多少应用于mysql混搭在同一台机器上。即使业务量不大,也可以通过降低单台机器的配置,多台机器共存来实现更好。
3.增加队列的链接数
echo ‘1048576’ > /proc/sys/net/ipv4/tcp_max_syn_backlog
建立链接的队列的数越大越好,但是从另一个角度想,实际环境中应该使用连接池更合适,避免重复建立链接造成的性能消耗。使用连接池,链接数会从应用层面更可控些。
4.设置链接超时时间
echo ’10’ > /proc/sys/net/ipv4/tcp_fin_timeout
该参数主要为了降低TIME_WAIT占用的资源时长。尤其针对http短链接的服务端或者mysql不采用连接池效果比较明显。
三、其它层面优化的考虑
如何解决mysql启动时性能_schema占用内存过高的问题?
当MySQL启动时,如果发现其占用物理内存和虚拟内存过高,可以尝试以下两种方法来处理。首先,网络上有一种常见的建议是修改my.cnf配置文件中的几个参数,如performance_schema_max_table_instances、table_definition_cache和table_open_cache。然而,这种方法可能并不适用于所有服务器,我在自己的实例中尝试后...
MySQL在高内存、IO利用率上的几个优化点
一、mysql层面优化设置0是事务log(ib_logfile0、ib_logfile1)每秒写入到log buffer,1是时时写,2是先写文件系统的缓存,每秒再刷进磁盘,和0的区别是选2即使mysql崩溃也不会丢数据。脏页写的线程数,加大该参数可以提升写入性能.mysql5.5以上才有。当系统中 脏页 所占百分比超过这个值,INNODB就会...
mysql占用内存过高的原因
连接泄漏、第三方插件或扩展。1、连接泄漏:应用程序没有正确关闭数据库连接,会导致连接泄漏,增加MySQL的内存使用。2、第三方插件或扩展:某些第三方插件或扩展会增加MySQL的内存使用,特别是那些没有经过良好优化的插件。
MySQL占用内存过高怎么办
1,首先通过任务管理器进行进程排序,查找占用内存较大的程序进程。一般占用内存较大的进程有W3WP、sqlserver、mysqld-nt.exe;2, 站点进程w3wp 可以在cmd命令行中通过 iisapp 命令来对应是那个网站占用内存较大。可以通过设置回收时间、内存最大使用值或共用进程池来减少内存的占用,但是如果要保证网站的...
MySQL占用虚拟内存非常高怎么办
size=102M ->64M 临时表大小key_buffer_size=256m ->32M 重启mysql服务后,虚拟内存降到200以下.另外mysql安装目录下有几个文件:my-huge.ini 、my-large.ini、my-medium.ini...这几个是根据内存大小作的建议配置,新手在设置的时候也可以参考一下。2G内存的MYSQL数据库服务器 my.ini优化 (...
1G内存足以运行MySQL数据库1g内存mysql
MySQL表中的数据存在碎片,这会导致查询速度变慢。使用OPTIMIZE TABLE命令可以优化MySQL表,并合并碎片,从而提高查询速度。1G内存足以运行MySQL数据库,但我们需要合理配置和优化MySQL,以确保最佳性能和稳定性。通过上述优化建议,我们可以确保MySQL在1G内存的限制下,能够更高效地运行。
win7系统配置MySQL内存过大导致启动失败怎么处理
一般调整为系统内存的一半先使用free-m查看下系统内存大小,查看是1G内存;2、那么vi\/etc\/my.cnf,调整innodb_buffer_pool_size、key_buffer_size各为500M;注意:my.cnf以实际配置文件路径为准。3、重启OK。以上教程分享就是win7系统配置MySQL内存过大导致启动失败的解决方法,希望能够帮助到大家!
mysql配置过大内存导致无法启动怎么办 如何解决
20000 的大内存页,并且没在使用,这一部分长期空闲,并且其他程序不能使用。所以 MySQL 在使用 20G 内存左右,整个主机内存就饱和了,然后在部分条件下,就触发了 OOM,导致 mysqld 被 kill,但主机上又有 mysqld_safe 守护程序,所以又再次给拉起来,就看到了文章初的偶尔连接不上的现象。
MySQL内存消耗了解一般情况mysql一般占用内存
MySQL是一个多线程、多用户的应用程序,需要大量的内存来存储其执行过程中的所有数据和对象。为了提高性能,MySQL使用了内存池的概念,即多个MySQL连接共享一组预分配的内存块池。在默认情况下,MySQL常驻内存的大小为128M,可以通过在my.cnf文件中调整该参数来增加或减少内存的初始使用量。MySQL内存池的...
MySQL32位内存占用分析32位mysql内存占用
1. 升级到64位系统 最简单的方法是升级到64位系统,以便MySQL可以访问更多的内存。如果您已经升级到64位系统,那么MySQL默认使用4GB的Global Memory Pool和4GB的Thread Memory Pool,这可以显著提高数据库的性能。2. 减少Global Memory Pool的内存使用 如果您不能升级到64位系统,那么您需要尽量减少Global ...
冻寇太子: mysql进程CPU占用过高,一般的原因是索引不当.如果没有索引,顺序查找会带来IO占用高,你现在的IO不高,很可能是order by后面的字句没有索引造成.mysql可以记录所有执行的SQL语句,但是我们一般记录慢速SQL即可,方法是在my.ini中使用下面的指令: log-slow-queries
波密县15230817940: 如何排查mysql cpu利用率高 - ?
冻寇太子: MySQL处在高负载环境下,磁盘IO读写过多,肯定会占用很多资源,必然CP会U占用过高.占用CPU过高,可以做如下考虑:1.打开慢查询日志,查询是否是某个SQL语句占用过多资源,如果是的话,可以对SQL语句进行优化,比如优化 insert ...
波密县15230817940: mysql 优化包括哪些内容? - ?
冻寇太子: mysql的优化大的有两方面:1、配置优化配置的优化其实包含两个方面的:操作系统内核的优化和mysql配置文件的优化1)系统内核的优化对专用的mysql服务器来说,无非是内存实用、连接数、超时处理、TCP处理等方面的优化,根据自己...
波密县15230817940: linux 系统mysql 服务器内存利用率很高了怎么解决 - ?
冻寇太子: 有性能影响吗?如果没有不用管!比如我的mysql我配置给他的内存10G.计算机资源是拿来用的,不是占高了就不好.如果性能有问题,扩容或者归档不常用的数据.你实在想节省内存,innerdb引擎修改参数如下:innodb_buffer_pool_size=3G
波密县15230817940: 数据库缓存机制是什么?缓存是如何作用数据库? - ?
冻寇太子: 缓存的介质一般是内存,所以读写速度很快.但如果缓存中存放的数据量非常大时,也会用硬盘作为缓存介质.缓存的实现不仅仅要考虑存储的介质,还要考虑到管理缓存的并发访问和缓存数据的生命周期.缓存就是数据交换的缓冲区(称作Cache),当某一硬件要读取数据时,会首先从缓存中查找需要的数据,如果找到了则直接执行,找不到的话则从内存中找.由于缓存的运行速度比内存快得多,故缓存的作用就是帮助硬件更快地运行.因为缓存往往使用的是RAM(断电即掉的非永久储存),所以在用完后还是会把文件送到硬盘等存储器里永久存储.电脑里最大的缓存就是内存条了,最快的是CPU上镶的L1和L2缓存,显卡的显存是给显卡运算芯片用的缓存,硬盘上也有16M或者32M的缓存.
波密县15230817940: Mysql数据库优化可以从哪几个方面优化 - ?
冻寇太子: 1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快.因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小. 2、使...
波密县15230817940: mysql高并发会带来什么问题 - ?
冻寇太子: 由于mysql是一个连接给一个线程,当并发高的时候,每秒需要几百个甚至更多的线程,其中创建和销毁线程还好说,大不了多耗费点内存,线程缓存命中率下降还有创建销毁线程的性能增加问题---这个问题不是特别大,重点是mysql底层瞬间处理这几百个线程提交的sql(有时候一个页面会有10多条sql,cpu一次只能处理一条sql)会导致cpu的上下文切换,性能抖动,然后性能下降.
波密县15230817940: MySQL占用内存过高怎么办 - ?
冻寇太子: 服务器内存占用过高的解决方法: 1,首先通过任务管理器进行进程排序,查找占用内存较大的程序进程.一般占用内存较大的进程有W3WP、sqlserver、mysqld-nt.exe; 2, 站点进程w3wp 可以在cmd命令行中通过 iisapp 命令来对应是那个网站...
波密县15230817940: mysql是对cpu要求高还是内存要求高 - ?
冻寇太子: 这个看并发数和访问的频率,就我个人经验而言,对于高频率和高并发数的情况,两者要求都高望采纳
波密县15230817940: 面试中常问:mysql数据库做哪些优化也提高mysql性能 - ?
冻寇太子: Mysql占用CPU过高的时候,该从哪些方面下手进行优化? 占用CPU过高,可以做如下考虑: 1)一般来讲,排除高并发的因素,还是要找到导致你CPU过高的哪几条在执行的SQL,show processlist语句,查找负荷最重的SQL语句,优化该SQL...