服务器性能优化和Mysql性能优化

  • 影响性能的几个因素
  • 服务器硬件
    •       CPU
  •       内存
  •       IO子系统
  • 服务器系统
  • CentOS系统参数优化

    •       sysctlconf 优化
    •       limitconf参数优化
    •       磁盘调度策略
    • 文件系统对性能的影响
    • 数据库存储引擎的选择
      •       MyIsam
      •       Innodb
      •       MySQL常用存储引擎之CSV
      •       MySQL常用存储引擎之Archive
      •       MySQL常用存储引擎之Memory
      •       MySQL常用存储引擎之Federated
      •       如何选择存储引擎
    • MySQL服务器参数介绍
      •       Mysql获取配置信息
      •       Mysql配置参数的作用域
      •       为Mysql配置内存相关参数
      •       为Mysql配置IO相关参数
      •       Mysql安全相关配置参数
      •       其它常用配置参数
    • 数据库设计对性能的影响
    • 总结

     

    影响性能的几个因素

     

    服务器硬件(CPU、内存、磁盘I/O等)

    服务器系统

    数据库存储引擎的选择

    数据库参数的配置

    数据库结构设计和SQL语句


    服务器硬件

     

    CPU

    •       64位的CPU一定要工作在64位的系统下
    •       对于并发比较高的 场景,CPU的数量比频率重要
    •       对于密集型场景和复杂SQL,则CPU频率越高越好

    内存

    •       选择主板所能使用的最高频率的内存
    •       内存的大小对于性能很重要,所以尽可能的大

    I/O子系统

    •       PCIe -> SSD ->Raid10 ->磁盘 ->SAN

    服务器系统

          优先选择Linux


    CentOS系统参数优化

     

    sysctl.conf 优化

     

    image

    编辑内核相关参数

    vim /etc/sysctl.conf

     

    调整配置文件内容

    // etc/sysctl.conf

    //以下参数根据需求调整

    net.core.somaxconn = 65535 //每个端口最大的监听队列长度

    net.core.netdev_max_backlog = 65535

    net.ipv4.tcp_max_syn_backlog = 65535

    net.ipv4.tcp_tw_recycle = 1

    net.ipv4.tcp_tw_reuse = 1

    net.ipv4.tcp_fin_timeout = 10

    //调整tcp连接缓冲区的默认值和最大值

    net.core.wmem_default = 87380

    net.core.rmem_default = 87380

    net.core.rmem_max = 16777216

    net.core.wmem_max = 16777216

    //用于减少失效tcp连接占用的资源,加快资源回收的效率

    net.ipv4.tcp_keepalive_time = 120

    net.ipv4.tcp_keepalive_intvl = 30

    net.ipv4.tcp_keepalive_probes = 3

    //Linux内核中最重要的参数之一,用于定义单个共享内存段的最大值

    //注意:

    //1、这个参数应该设置为足够大,以便能在一个共享内存段下容纳下整个Innodb缓冲池的大小

    //2、这个值的大小对于64位Linux系统,可取的最大值为物理内存值减 -1 byte,建议值为大于物理内存的一半,一般取值大于Innodb缓冲池的大小即可,可以取物理内存减 -1 byte

    kernel.shmmax = 4294967295

    //这个参数当内存不足时,会对性能产生比较明显的影响

    //设置为0表示:告诉Linux内核除非虚拟内存完全占满了,否则不要使用交换分区swap

    vm.swappiness = 0

     


    limit.conf参数优化

    增加资源限制(/etc/security/limits.conf),这个文件实际上是linux PAM,也就是插入式认证模块的配置文件。打开文件数的限制

    vim /etc/security/limits.conf

     

    #加到/etc/security/limits.conf 文件末尾

    * soft nofile 65535

    * hard nofile 65535

    * soft nproc 65535

    * hard nproc 65535

     

    image


    image

     

    磁盘调度策略

     

    查看当前使用的调度策略

    image


    NOOP调度策略

    image


    Deadline调度策略

    这种策略适用于数据库

    image


    anticipatory调度策略


    image


    修改调度策略

    如下为将调度策略修改为deadline策略
    image

    #操作日志

    #查看当前系统的磁盘

    [root@10-9-134-133 block]# df -l

    Filesystem 1K-blocks Used Available Use% Mounted on

    /dev/xvda1 20641404 10335756 9257124 53% /

    tmpfs 4029028 0 4029028 0% /dev/shm

    /dev/xvdb1 103210940 81019216 16948916 83% /hotdata

    #查看当前调度策略

    [root@10-9-134-133 block]# cat /sys/block/xvdb/queue/scheduler

    noop anticipatory deadline [cfq]

    [root@10-9-134-133 block]# cat /sys/block/xvda/queue/scheduler

    noop anticipatory deadline [cfq]

    #修改当前调度策略

    [root@10-9-134-133 block]# echo deadline > /sys/block/xvdb/queue/scheduler

    [root@10-9-134-133 block]# echo deadline > /sys/block/xvda/queue/scheduler

    [root@10-9-134-133 block]# cat /sys/block/xvda/queue/scheduler

    noop anticipatory [deadline] cfq

    [root@10-9-134-133 block]#

     

    文件系统对性能的影响

     

    image

    windows下有:

    •         FAT
    •         NTFS

    Linux下有:

    •         EXT3
    •         EXT4
    •         XFS(最好,centos7已经默认采用)

    EXT3/4需要注意的地方
    image

     

    数据库存储引擎的选择

     

     

    image


    MyIsam

    mysql5.5版本之前的默认存储引擎
    1、Myisam存储引擎由MYD(数据)和 MYI(索引文件)组成,frm文件存储表结构(所以存储引擎都有)


    • myisam的特性:

      1.                 并发性和锁级别 (对于读写混合的操作不好,为表级锁,写入和读互斥)
      2.                 表损坏修复
      3.                 myisam表支持的索引类型(全文索引…)
      4.                 myisam支持表压缩(压缩后,此表为只读,不可以写入。使用myisampack压缩)

    check table tablename

    repair table tablename

    #修复myisam表还有一个命令,myisamchk ,在命令行使用这个命令,需要将数据库停止,否则可能会造成更大的表损害

     


    • myisam的限制:

      image


    • myisam适用场景:

      1.                 非事务性应用
      2.                 只读类应用
      3.                 空间类应用(唯一支持空间函数的引擎)

    Innodb

    Mysql5.5及之后版本的默认存储引擎

    •                 Innodb使用表空间进行数据存储

    当参数 innodb_file_per_table
    ON时,采用独立表空间:tablename.id,
    OFF时,采用系统表空间:ibdataX,生成一个ibdata1的文件

    mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';

    +-----------------------+-------+

    | Variable_name | Value |

    +-----------------------+-------+

    | innodb_file_per_table | OFF |

    +-----------------------+-------+

    1 row in set (0.00 sec)

    mysql>

     

    • 独立表空间和系统表空间应该如何抉择呢?

    两者比较:

    1.                 系统表空间无法简单的收缩大小(这很恐怖,会导致ibdata1一直增大,即使删除了数据也不会变小)
    2.                 独立表空间,可以通过optimize table 命令收缩系统文件
    3.                 系统表空间会产生I/O瓶颈(因为只有一个文件)
    4.                 独立表空间可以向多个文件刷新数据

    总结
                    强烈建议:对Innodb引擎使用独立表空间(mysql5.6版本以后默认是独立表空间)

    系统表转移为独立表的步骤(非常繁琐):

    1.                 使用mysqldump导出所有数据库表数据
    2.                 停止mysql服务,修改参数,并且删除Innodb相关文件
    3.                 重启mysql服务,重建mysql系统表空间
    4.                 重新导入数据

    Innodb存储引擎特性

    1.                 Innodb为事务性存储引擎
    2.                 完全支持事物的ACID特性
    3.                 Redo log (实现事务的持久性) 和Undo log(为了实现事务的原子性,存储未完成事务log,用于回滚)
    4.                 Innodb支持行级锁
    5.                 行级锁可以最大程度的支持并发
    6.                 行级锁是由存储引擎层实现的

    什么是锁

    1.                 锁的主要作用是管理共享资源的并发访问
    2.                 锁用于实现事务的隔离性

    锁的类型

    1.                 共享锁(也称为读锁)
    2.                 独占锁(也成为为写锁,排他的)
    3.                 读锁和读锁是兼容的 clip_image011[4]

    锁的粒度

    1.         表级锁
    2.         行级锁

    阻塞和死锁

            什么是阻塞
            什么是死锁

    Innodb状态检查

    mysql> SHOW ENGINE INNODB STATUS;

     

    使用场景
            Innodb适合于大多数OLTP(在线处理)0应用

     

     

    MySQL常用存储引擎之CSV

    文件系统存储特点

    1.         数据以文本方式存储在文件中
    2.         .csv文件存储表内容
    3.         .csm文件存储表的元数据,如表状态和数据量
    4.         .frm存储表的结构

    CSV存储引擎特点

    1.         以CSV格式进行数据存储
    2.         所有列 必须都是不能为NULL
    3.         不支持索引
    4.         可以对数据文件直接编辑(其他引擎是二进制存储,不可编辑)

    CSV适用场景

    image

    image

     

    MySQL常用存储引擎之Archive

     

    Archive存储引擎特点

    1.         以zlib对表数据进行压缩,磁盘I/O更少
    2.         数据存储在ARZ为后缀的文件中(表文件为a.arz,a.frm)
    3.         只支持insert 和 select 操作(不可以delete 和update,会提示没有这个功能)
    4.         只允许在自增ID列上加索引

    Archive适用场景

            日志和数据采集类应用

     

    MySQL常用存储引擎之Memory

     

    Memory存储引擎特点

    也称为HEAP存储引擎,所以数据保存在内存中(数据库重启后会导致数据丢失)

    1.         支持HASH索引(等值查找应选择HASH)和BTree索引(范围查找应选择)
    2.         所有字段都为固定长度,varchar(10) == char(10)
    3.         不支持BLOG和TEXT等大字段
    4.         Memory存储使用表级锁(性能可能不如innodb)
              最大大小由 max_heap_table_size 参数决定
    5. Memory存储引擎默认表大小只有16M,可以通过调整max_heap_table_size 参数

    Memory存储引擎与临时表

    Memory存储引擎和临时表是不同概念

    image

    Memory适用场景

    image

     

    MySQL常用存储引擎之Federated

     

    Federated存储引擎特点

    1.         提供了访问远程mysql服务器上表的方法
    2.         本地不存储数据,数据全部放在远程服务器上

    使用 Federated


    默认是禁止的。如果需要启用,需要在启动时增加Federated参数

     

    如何选择存储引擎

    参考条件:

    1.         是否需要事务
    2.         是否可以热备份
    3.         崩溃恢复
    4.         存储引擎的特有特性

    重要一点:不要混合使用存储引擎
    强烈推荐: Innodb

     

    MySQL服务器参数介绍

     

    Mysql获取配置信息

    1. 命令行参数,比如 mysql_safe --datadir = /data/sql_data (不推荐在命令行指定)
    2. 配置文件,查看配置文件的

    //查看 MySQL 配置文件加载顺序

    mysqld --verbose --help | grep -A 1 'Default options'

    //加载顺序

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

     

    Mysql配置参数的作用域

    1. 全局参数

    //下面两种设置全局参数的方式是等价的

    set global 参数名 = 参数值

    set @@global.参数名 := 参数值

     

    1. 会话参数

    //下面两种设置会话参数的方式是等价的

    set [session] 参数名 = 参数值 ;

    set @@session.参数名 := 参数值 ;

     

    为Mysql配置内存相关参数

    1. 确定可以使用的内存上限(这里要注意了,32位系统最多只能使用4G内存,所以我们要选择64位操作系统)
    2. 确定Mysql上的每个连接使用的内存

    //以下参数都是为单个连接配置的

    sort_buffer_size //排序缓冲区的内存,这个值不能过大,

    join_buffer_size //连接缓冲区的内存,关联多张表 ,这个值也不能太大

    read_buffer_size //

    read_rnd_buffer_size

     

    3.确定需要为操作系统保留多少内存 (数据库服务器应该单独配置,不合程序文件放在一个服务器)
    4. 如何为缓存池分配内存

    #不仅要缓存索引而且要缓存数据,Innodb严重依赖缓存池,应该分配足够多的内存给这个参数

    #分配值参考公式:总内存 - (每个线程所需要的内存 * 连接数)- 系统保留内存

    innodb_buffer_pool_size

    #key_buffer_size对MyISAM表性能影响很大.如果全是Innodb表,则不需要分配太大,够系统表用即可

    #查询myisam表索引占用的空间大小:

    # select sum(index_length) from information_schema.tables where engine = 'myisam'

    key_buffer_size

     

    为Mysql配置IO相关参数

    Innodb I/o相关配置

    innodb_log_file_size #控制单个事务日志大小

    innodb_log_file_in_group #控制事务文件个数(这个参数可以不用管它,不是越多越好)

    #事务日志总大小

    # innodb_log_file_in_group * innodb_log_file_size

    innodb_log_buffer_size #事务日志的缓冲区大小

    #0:表示每秒进行一次log写入cache,并flush log 到磁盘(不安全)

    #1【默认】:在每次事务提交执行log写入cache,并flush log到磁盘(效率最低,最安全)

    #2【建议】:每次事务提交,执行log数据写入到cache,每秒执行一次flush log 到磁盘

    innodb_flush_log_at_trx_commit = 2

    #innodb刷新的方式,影响innodb读取数据的方式

    innodb_flush_method = O_DIRECT #O_DIRECT 为Linux的最好选择

    #控制innodb如何使用表空间,为1表示为每个表建立单独的表空间,为0表示使用系统表空间,强烈建议设置为1

    innodb_file_per_table = 1

    innodb_doublewrite = 1 #使用双写缓存,建议启用,为了安全

     

    Myisam I/o相关配置

    delay_key_write

    #OFF : 每次写操作后刷新键缓冲中的脏块到磁盘

    #ON :只对建表时指定了delay_key_write选项的表使用延迟刷新

    #ALL :对所有Myisam表都使用延迟键写入

     

    Mysql安全相关配置参数

    expire_logs_days = 7 #指定自动清理binlog的天数,建议设置为7天

    max_allowed_packet = 32M #控制mysql可以接收包的大小,建议32M,主从都需配置一样大小

    skip-name-resolve #禁用DNS查找,建议启用

    #以下主要针对从库中配置

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

    read_only #禁止非super 权限的用户写入

    skip_slave_start #禁用slave自动恢复(不安全的崩溃,自动恢复可能是不安全的)

    #设置mysql使用的SQL模式

    #sql_mode

    strict_trans_tables

    no_engine_subtitution

    no_zero_date

    no_zero_in_date

    only_full_group_by

     

    其它常用配置参数

    sync_binlog #控制Mysql如何向磁盘刷新binlog

    tmp_table_size 和 max_heap_table_size #控制内存临时表的大小

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

     

    数据库设计对性能的影响

     

    1.         过分的反范式化为表建立太多的列
    2.         过分的范式化造成太多的表关联
    3.         在OLTP环境中使用不恰当的分区表
    4.         使用外键保证数据的完整性(尽量不使用外键约束)

    总结

     

    性能优化顺序

    1.         首先优化数据库结构设计和SQL语句
    2.         选择合理的存储引擎和参数配置(不要混合使用存储引擎)
    3.         系统选择和优化
    4.         硬件升级