美文网首页
Mysql数据库性能影响因素

Mysql数据库性能影响因素

作者: 君子愁 | 来源:发表于2019-10-17 12:00 被阅读0次

    影响MYSQL性能因素

    1.服务器硬件

    2.操作系统

    3.数据库引擎

    4.数据库配置参数

    5.表结构的设计和SQL语句的优化


    服务器硬件

    cpu

    多个cpu,对处理数据库并发有帮助,一个SQL语句是在一个cpu执行的,多个cpu,可同时处理多个并发的SQL语句。可选择64位,16或者 32核的cpu。

    64位cpu需要安装64位系统

    cpu核数和cpu线程数理解:

    CPU个数即CPU芯片个数

    CPU的核心数是指物理上,也就是硬件上存在着几个核心。比如,双核就是包括2个相对独立的CPU核心单元组,四核就包含4个相对独立的CPU核心单元组。

    线程数是一种逻辑的概念,简单地说,就是模拟出的CPU核心数。比如,可以通过一个CPU核心数模拟出2线程的CPU,也就是说,这个单核心的CPU被模拟成了一个类似双核心CPU的功能。我们从任务管理器的性能标签页中看到的是两个CPU。 比如Inte l赛扬G460是单核心,双线程的CPU,Intel 酷睿i3 3220是双核心 四线程,Intel 酷睿i7 4770K是四核心 八线程 ,Intel 酷睿i5 4570是四核心 四线程等等。 对于一个CPU,线程数总是大于或等于核心数的。一个核心最少对应一个线程,但通过超线程技术,一个核心可以对应两个线程,也就是说它可以同时运行两个线程。

    内存

    内存大小大于数据库数据的时候,再大的内存就没啥意义了。内存可以把数据库数据缓存,加快读。对于写,每次写先缓存在内存,等写的内容多了,一次性写入磁盘,减少磁盘IO

    磁盘

    容量  传输速度  访问时间  主轴速度  物理尺寸

    Raid卡

    raid0,同时往多块磁盘读写,速度快,但是没冗余

    raid1,有一半磁盘做冗余

    raid5,只可以坏一个磁盘。因为用了奇偶校验,所以写入的时候,需要写入检验数据,所以写入的时候慢。但是读取的时候快,因为不用写入校验数据。但是坏了一块磁盘,需要调用其他所有磁盘的数据,进行恢复这块磁盘数据,会导致读写慢。可以用在从库。

    raid10,先raid1,然后再把raid1组合成raid0

    raid

    raid0 和 raid5适合用于slave数据库(因为raid0没冗余  raid5坏了一块磁盘后,要通过其他几个磁盘一起恢复,会导致当时的磁盘io很慢)

    raid1和raid10可以用于主或者从数据库

    SSD磁盘

    SSD磁盘有更好的随机读写和支持更大的并发IO大。

    PCIE-SSD性能比ssd好,但是昂贵。PCIE-SSD会占用内存,不建议使用raid,因为成本本来就昂贵了

    SSD磁盘用在随机IO比较多的场景,比如内存只有20G,但是数据库数据又100G.那么数据不可能全部缓存到内存,所以很多数据需要从磁盘读取,此时可以使用固态;可以使用在单线程负载IO场景,比如slave种的IO线程就是单线程的,可以使用SSD加快这个线程接收master的dump线程推送过来的信息。

    网络

    网络带宽如果太小,影响并发

    网络延时,影响连接和发送数据


    操作系统

    Linux


    数据库引擎

    innodb



    数据库相关配置


    文件系统配置

    linux系统下最好使用xfs文件系统

    ext4 或者ext3文件系统,需要再/etc/fastab设置:

    /dev/sda3  /  ext4  noatime,nodiratime,data=writeback 1 1

    noatime,nodiratime 分别是对访问文件和访问文件夹的时间戳不要记录,减少IO操作

    writeback 表示文件系统把数据写入磁盘,不需要记录日志,直接把原数据刷入磁盘就行,因为数据innodb自己有redo日志。(这里是三个值的 writeback   ordered【记录部分日志】 journal【记录全部日志】)

    操作系统参数配置

    net.core.somaxconn  #是Linux中的一个kernel参数,表示socket监听(listen)的backlog上限。什么是backlog呢?backlog就是socket的监听队列,当一个请求(request)尚未被处理或建立时,他会进入backlog。而socket server可以一次性处理backlog中的所有请求,处理后的请求不再位于监听队列中。当server处理请求较慢,以至于监听队列被填满后,新来的请求会被拒绝。

    mysql配置读取

    mysql --help --version|grep -A 1 'Default options'  #通过这一句命令查看操作系统mysql读取配置文件的位置的先后

    centos配置读取顺序:

    /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

    配置里有全局配置和session配置

    set global xxxx=xxxxx;  #全局配置,只对还没登陆的session有效,对于已经登陆的session,需要先退出,再登陆进来才生效。

    set xxxx=xxxxxx;  #session配置

    INODB内存分配

    innodb_buffer_pool_size  #一般为总内存的75%以上,缓存索引和数据

    key_buffer_szie  #myisam存储引擎的内存缓存大小,只缓存索引

    #下面的四个参数为每一个线程设置,一个线程就分配指定的内存。如果线程数量过多,那么总分配的内存也会很多,所以要小心配置

    sort_buffer_size  #排序缓存大小

    join_buffer_size  #连接缓冲区的大小,如果一个查询中,关联多张表,会对每个关联分配一个连接缓冲,所以每个查询会有多个缓冲。

    read_buffer_szie   #当myisam表进行全表扫描,分配读缓冲池大小,当mysql读需要才会分配。

    read_rnd_buffer_size #sout_buffer_size排序的是key_values,而不是数据。排序好key后,就去磁盘找数据,然后把数据保存再read_rnd_buffer_size,找数据这个过程是随机读的。当数据放到read_rnd_buffer_size后,再通过key去read_rnd_buffer_size读数据就是顺序读了。

    INODB IO相关配置

    提交数据得时候,先写入buffer_poo缓存,而不是直接刷入磁盘的。因为每次提交数据,都刷入磁盘,那么很消耗IO。而且每次写入磁盘,可能不一定是顺序写入的。在每次提交的时候,都会写入日志到redo文件。redo文件的磁盘刷入,是顺序的。

    innodb_log_file_size  #每一个redo日志文件的大小。根据业务大小,业务比较大的,设置大点。

    innodb_log_files_in_group  #有多少个日志组,一般一个日志组就行了

    innodb_log_buffer_size  #控制redo日志缓冲区大小,不再buffer_pool里面。不需要太大(32M-128M),因为每1s或者每次事务提交,都会自动把缓存刷入磁盘的。

    innodb_flush_log_at_trx_commit

    #####值可以为以下选项

    1)如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次都把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去,在事务提交的时候,不会主动触发写入磁盘的操作。log buffer写到文件系统中,一般只是从log buffer的内存转移的文件系统的内存缓存中,对底层IO没有压力

    2) 如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去.

    3)如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL会把log buffer刷到文件系统中去,但是每隔一秒调用文件系统的“flush”操作将缓存刷新到磁盘上去。该模式下,MySQL会每秒执行一次flush(刷到磁盘)操作。(为2性能最好)

    redo的buffer刷新图

    sync_binlog 

    值为0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

    当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。生产环境用了sync_binlog=1

    innodb_flush_method

    默认是fdatasync,调用fsync()去刷数据文件与redo log的buffer

    为O_DSYNC时,innodb会使用O_SYNC方式打开和刷写redo log(直接不经过os缓存,写入磁盘),使用fsync()刷写数据文件(使用os缓存再刷新到磁盘)

    为O_DIRECT时,innodb使用O_DIRECT打开数据文件(数据不经过os缓存,直接刷入磁盘),redo日志还是需要经过os缓存才可以刷入磁盘(生产环境使用)

    一张图就知道原理了

    innodb_file_per_table=1   #让每个表都有自己的独立空间,如果不设置为1,所有表都公用共享表空间了

    innodb_doublewrite=1  #在写入数据的时候,比如默认的一个叶是16k,当只写入了6k,然后数据库崩溃了,那么当重启做数据一致性恢复的时候,是恢复补回来的了。只有当设置为1了,才可以恢复回来。

    安全相关配置参数

    expire_logs_days  #指定自动清理binlog的天数

    max_allowed_packet  #控制MYSQL可以接收的包的大小,默认为1M,如果大于1M的插入和更新,就会出问题

    skip_name_resolve  禁止DNS查找

    sysdate_is_now   #确保sysdate()返回确定性日期

    read_only       #禁止非super权限的用户写入,一般用在slave服务器

    skip_slave_start  #禁止slave自动启动,需要手动start slave;

    sql_mode  #定义SQL模式

                     #STRICT_TRANS_TABLES为严格模式,就是插入无关内容,直接返回error,不给插入。

                    #NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

    其他常用配置

    tmp_table_size  max_head_table_size  #这两个参数是临时表大小的限制,由其中这两个参数最小的决定,临时表最大可是多少。 group by  或者  order by 排序的时候,会产生临时表。或者explain的时候,看到Using temporary的时候。 临时表只是在这个session起作用。

    max_connections   #控制允许的最大连接数


    相关文章

      网友评论

          本文标题:Mysql数据库性能影响因素

          本文链接:https://www.haomeiwen.com/subject/yhemmctx.html