MySQL服务器性能剖析

最新推荐文章于 2024-11-10 20:23:54 发布

原创最新推荐文章于 2024-11-10 20:23:54 发布 · 1.7k 阅读

1 ·

CC 4.0 BY-SA版权

1.1.1.5 数据库及调优专栏收录该内容

55 篇文章

订阅专栏

1 mysql服务器要根据需要尽量使用较稳定的新版本

1.1 如果用的是mysql5.1版本或更低的版本为什么要升级到mysql5.5版本上？

1.1.1 MySQL 5.5 新特性解读：http://www.oschina.net/question/12_15291

Oracle官方mysql5.5性能描述：

Oracle 公司发布了MySQL5.5版本,这也是该公司获得MySQL后,对MySQL的第一次升级。Oracle表示，按照内部的标准，在Linux 上，MySQL 5.5的读写速度比5.1版本提升了360%。在Windows Server机器上，这方面性能的提升超过了1500%。MySQL 5.5版本的吞吐量更高。

                 (1)．默认存储引擎更改为InnoDB：Multi Rollback Segments：原来InnoDB只有一个Segment，同时只支持1023的并发。现已扩充到128个Segments，从而解决了高并发的限制。
                 (2)．多核性能提升
                 (3)．复制功能（Replication）加强：MySQL5.5将首次支持半同步（semi-sync replication）在MySQL的高可用方案中将产生更多更加可靠的方案。
                 (4). 增强表分区功能：
                 (5). Insert Buffering 如果在buffer pool中没找到数据，那么直接buffer起来，避免额外的IO；Delete & Purge Buffering 跟插入一样，如果buffer pool中没有命中，先buffer起来，避免额外的IO。
                 (6)．Support for Native AIO on Linux

1.1.2 MySQL5.5特性测试报告-陶会祥(第三方测试结论)：http://wenku.baidu.com/link?url=-iYBg_oyL-u9L4gey5iWl0InJG7i-mbFNVDb2Aqv5mFQbBazf4j2PX8frAUPcPCXrRWtTuN-8vAD0rVUHGH4E79smLc3uarwath2CDWQ94i

1.1.3 mysql性能测试及不同版本的比较(第三方测试结论)：http://www.ggyun.com/tech-reports/29

1.1.2 和1.1.3 的第三方结论：相对于5.1版本，5.5版本性能有提升，但未达到官方说的那种情况提升360%。

1.1.4 MySQL5.1 和 MySQL5.5 的mysqlslap压力测试比较：http://hi.baidu.com/edeed/item/e394cf34a76eb8f3e7bb7abb
测试结论：从测试结果来看, MySQL5.5版本InnoDB引擎确实有了非常大的性能提升(几乎真是官方宣称的3倍), MyISAM引擎没有多大变化. 所以如果使用InnoDB引擎的情况下, 考虑使用MySQL5.5版本吧. 加上MySQL5.5版本的Replication可用半同步, 确实是个不错的选择.

综上所述：5.5版本总体好于5.1版本，新开发的系统建议直接使用5.5版本，对于已经使用5.1版本的系统，如果5.1满足不了性能要求，也可以升级到5.5版本

参考：mysql系统变量专题学习：http://blog.youkuaiyun.com/w402606099/article/details/8925479

第8章优化服务器设置(《高性能MySQL_第3版》)

8.1 MySQL 配置的工作原理
      MySQL配置信息：命令行参数和配置文件； Linux系统中配置文件的位置一般在/etc/my.cnf或者/etc/mysql/my.cnf.
      如果使用操作系统的启动脚本，这通常是唯一指定配置设置的地方。
      如果手动启动MySQL，例如在测试安装时，也可以在命令行指定设置。
8.1.1 语法、作用域和动态性
      配置项有多个作用域：
      (1) 服务器级的(全局作用域)
      (2) 每个连接级的(会话作用域)
      (3) 对象级的()
      修改了全局变量后，应该检查 show global variables的输出，确定已经按照期望变化了。
8.1.2 设置变量的副作用
8.1.3 入门
8.1.4 通过基准测试迭代优化
8.2 什么不该做
8.3 创建MySQL 配置文件:基础配置文件
       [mysqld]
       # GENERAL
       datadir = /var/lib/mysql
       socket = /var/lib/mysql/mysql.sock
       pid_file = /var/lib/mysql/mysql.pid
       user = mysql
       port = 3306
       storage_engine = InnoDB
       # INNODB
       innodb_buffer_pool_size = <value>
       innodb_log_file_size = <value>
       innodb_file_per_table = 1
       innodb_flush_method = O_DIRECT
       # MyISAM
       key_buffer_size = <value>
       # LOGGING
       log_error = /var/lib/mysql/mysql-error.log
       log_slow_queries = /var/lib/mysql/mysql-slow.log
       # OTHER
       tmp_table_size = 32M
       max_heap_table_size = 32M
       query_cache_type = 0
       query_cache_size = 0
       max_connections = <value> (默认值100,最大值16384)
       thread_cache_size = <value>
       table_cache_size = <value>
       open_files_limit = 65535
       [client]
       socket = /var/lib/mysql/mysql.sock
       port = 3306
参数设置：
innodb_buffer_pool_size如何配置？P373 修改后通过show engine innodb status \G;验证修改配置是否生效。
max_connections=16384
open_files_limit = 65535,在典型的Linux系统上我们把它设置得尽可能大。现代操作系统中打开文件句柄开箱都很小。如果这个参数不够大，将会碰到经典的24号错误，“打开的文件太多(too many open files)”
max_prepared_stmt_count=1048576默认值：16382 值域：0~1048576
ulimit -n 10240 打开文件句柄数: FATAL: error 2004: Can't create TCP/IP socket (24)
8.3.1 检查MySQL 服务器状态变量
8.4 配置内存使用
    MySQL的内存消耗分为两类：可以控制的内存和不可以控制的内存。
8.4.1 MySQL 可以使用多少内存？
8.4.2 每个连接需要的内存 .
      innodb:innodb_buffer_pool
      myisam:key_buffer_size
8.4.3 为操作系统保留内存 .
      至少应该为操作系统保留1GB-2GB的内存--如果机器内存更多就再多预留一些。我们建议2GB或总内存的5%最为基准，以较大者为准。
8.4.4 为缓存分配内存
      (1) InnoDB缓冲池
      (2) InnoDB日志文件和MyISAM数据的操作系统缓存
      (3) MyISAM键缓存
      (4) 查询缓存
      (5) 无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存。
8.4.5 InnoDB 缓冲池（Buffer Pool）
      InnoDB缓冲池并不仅仅缓存索引、还缓存行的数据、自适应哈希索引、插入缓冲(Insert Buffer)、锁、以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序地写回。
      InnoDB严重依赖缓冲池，你必须确认为它分配了足够的内存。可以使用show 命令或innotop这样的工具监控InnoDB缓冲池的内存利用情况。
8.4.6 MyISAM 键缓存（Key Caches）
      MyISAM的键缓冲也被称为键缓冲，MyISAM自身只缓存索引，不缓存数据(依赖操作系统缓存数据库)。如果大部分是MyISAM表，就应该为键缓冲分配比较多的内存。
      key_buffer_size
      在决定键缓存需要分配多少内存之前，先去了解MyISAM索引实际上占用多少磁盘空间是很有帮助的。不需要把减缓冲设置得比需要缓存的索引数据还大。
      查询information_schema表的index_length字段，把他们的值相加，就可以得到索引存储占用的空间：
        select sum(index_length) from information_schema.tables where engine = 'MYISAM';
      如果是类UNIX系统，也可以使用下面的命令：
        $ du -sch 'find /path/to/mysql/data/directory/ -name "*.MYI"'
      应该把键缓存设置的多大？不要超过索引的总大小，或者不超过为操作系统缓冲保留总内存的25%-50%,以更小的为准。
      键缓冲区的使用率:
        100 - ((Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )
      记住，MyISAM使用操作系统缓存来缓存数据文件，通常数据文件比索引要大。
      最后，即使没有任何MyISAM表，依然需要将key_buffer_size设置为较小的值，MySQL服务器优势会在内部使用MyISAM表。
   MySQL 键缓存块大小(Key Block Size)
      myisam_block_size变量控制着索引块大小。
8.4.7 线程缓存(Thread Cache)
      thread_cache_size变量指定了MySQL可以保持在缓存中的线程数。
8.4.8 表缓存（Table Cache）
      table_cache_size = <value>
      如果遇到MySQL无法打开更多文件的错误，那么可能需要增加MySQL允许打开文件的数量。可以通过在my.cnf文件中设置open_files_limit服务器变量来实现。
8.4.9 InnoDB 数据字典（Data Dictionary）
      InnoDB有自己的表缓存，可以称为表定义缓存或者数据字典。
      innodb_file_per_table = 1
      如果可以最好把innodb_open_files的值设置的足够大以使服务器可以保持所有的.ibd文件同时打开。
8.5 配置MySQL 的I/O 行为 --------------------》9:44 --
      保证数据立刻并且一致地写到磁盘是很昂贵的，如果能够冒一点磁盘写可能没有真正持久化到磁盘的风险，就可以增加并发性和减少I/O等待，但是必须决定可以容忍多大的风险。
8.5.1 InnoDB I/O 配置
    InnoDB事务日志: 整体的日志文件大小受控于innodb_log_file_size 和 innodb_log_files_in_group两个参数，这对写性能非常重要。
      日志文件的总大小是每个文件的大小之和。默认情况下，只有两个5MB的文件，总共10MB. 对高性能工作来说这太小了。至少需要几百MB,或者甚至上GB的日志文件。
      InnoDB使用多个文件作为一组循环日志。通常不需要修改默认的日志数量，只修改每个日志文件的大小即可。
      变量innodb_log_buffer_size可以控制日志缓冲区的大小。通常不需要把日志缓冲区设置得非常大。推荐的范围是1MB - 8MB,除非要写很多相当大的BLOB记录。
      作为一个经验法则，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。
      日志缓冲必须被刷新到持久化存储，以确保提交的事物完全被持久化了。如果和持久相比更在乎性能，可以修改innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度。
      innodb_flush_log_at_trx_commit = 0 把日志缓冲写到日志文件，并且每秒钟刷新一次，但是事务提交时不做任何事。
      innodb_flush_log_at_trx_commit = 1 把日志缓冲写到日志文件，并且每次事务提交都刷新到持久化存储。这是默认的(并且是最安全的)设置，该设置能保证不会丢失任何已经提交的事务，除非磁盘或者操作系统是“伪”刷新。
      innodb_flush_log_at_trx_commit = 2 每次提交时把日志缓冲写到日志文件，但是并不刷新。InnoDB每秒钟做一次刷新。
      了解清楚"把日志缓冲写到日志文件"和“把日志刷新到持久化存储”之间的不同是很重要的。在大部分操作系统中，把缓冲写到日志只是简单地把数据从InnoDB的内存缓冲转移到了操作系统的缓冲，也是在内存里，并没有真的把数据写到了持久化存储。
    InnoDB怎样打开和刷新日志以及数据文件：
      使用innodb_flush_method选项可以配置InnoDB如何跟文件系统相互作用。
      innodb_flush_method=fdatasync :在非Windows系统上市默认值：InnoDB用fsync()刷新数据和日志文件。操纵系统缓冲，从而双重缓冲。
           innodb_file_per_table选项会导致每个文件独立地做fsync(),这意味着写多个表不能合并到一个I/O操作。
      innodb_flush_method=O_DIRECT, InnoDB对数据文件使用O_DIRECT标记或directio()函数，这依赖于操作系统。在(Linux FreeBSD,以及Solaris5.0以后)是支持的。不像O_DSYNC标记，它同时会影响读和写。
           这个设置依然使用fsync()来刷新文件到磁盘，但是会通知操作系统不要缓存数据，也不要用预读。这个选项完全关闭了操作系统缓存，并且使所有的读和写都直接通过存储设备，避免了双重缓冲。
           大部分系统上，这个实现用fcntl()调用来设置文件描述符的O_DIRECT标记。
      innodb_flush_method=ALL_O_DIRECT 这个现象在Percona Server 和 MariaDB中可用。它使得服务器在打开日志文件时，也能使用标准MySQL中打开数据文件的方式(O_DIRECT).
      innodb_flush_method=O_DSYNC :这个选项使日志文件调用open()函数时设置O_SYNC标记。它使得所有的写同步--换个说法，只有数据写到磁盘后写操作才返回。这个现象不影响数据文件。
      innodb_flush_method=async_unbuffered （Windows下的默认值）
      innodb_flush_method=unbuffered （Windows下）这个选项与async_unbuffered类似，但是不使用原生异步I/O.
      innodb_flush_method=normal （Windows下）这个选项不要使用原生异步I/O或者无缓冲I/O.
      innodb_flush_method=Nosync和littersync 只为开发使用。
      建议：如果使用类UNIX操作系统并且RAID控制器带有电池保护的写缓存，我们建议使用O_DIRECT。如果不是这样，默认值或者O_DIRECT都可能是最好的选择，具体要看应用类型。
   InnoDB表空间：
      InnoDB把数据保存在表空间内，本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。
      InnoDB用表空间实现很多功能，并不只是存储表和索引。还保存了回滚日志(旧版本行)、插入缓冲(Insert Buffer)、双写缓冲(Doublewrite Buffer)以及其他内部数据结构。
      配置表空间：innodb_data_file_path配置项定制表空间文件。
        innodb_data_home_dir = /var/lib/mysql/
        innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G
        或者：
        innodb_data_file_path = /disk1/ibdata1:1G;/disk2/ibdata2:1G;...
        用RAID控制器是分布负载更聪明的方式。
       设置表空间自动扩展:
            ...ibdata3;1G:autoextend
       设置限制了自动扩展文件最多到2GB:
            ...ibdata3:1G:autoextend:max:2G
       innodb_file_per_table选项让InnoDB为每张表使用一个文件。
     双写缓冲(Doublewrite Buffer):
       InnoDB用双写缓冲来避免也没写完整所导致的数据损坏。

   其他的I/O配置项
       sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘。
          sync_binlog=0 默认值是0,意味着MySQL并不刷新，由操作系统自己决定什么时候刷新缓存到持久化设备。
          sync_binlog=1 如果这个值比0大，它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作。
          设置sync_binlog=1可能比innodb_flush_log_at_trx_commit=1对性能的损害要大得多，尤其是网络文件系统，例如NFS.

8.5.2 MyISAM 的I/O 配置
   ??
8.6 配置MySQL 并发 .
8.6.1 InnoDB 并发配置
      最基本的限制并发的方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少线程进入内核，0表示不限制。
      并发值公式：innodb_thread_concurrency并发值 = CPU数量 * 磁盘数量 * 2
      使用线程池(Thread Pool)来限制并发。 ---- 不成熟 MariaDB已经重新实现了
8.6.2 MyISAM 并发配置
8.7 基于工作负载的配置
8.7.1 优化BLOB 和TEXT 的场景
8.7.2 优化排序（Filesorts）.ad
8.8 完成基本配置
8.9 安全和稳定的设置
8.10 高级InnoDB 设置
8.11 总结