MySQL MyISAM/InnoDB高并发优化经验

时间：2023-03-08 15:49:14

最近做的一个应用，功能要求非常简单，就是 key/value 形式的存储，简单的 INSERT/SELECT，没有任何复杂查询，唯一的问题是量非常大，如果目前投入使用，初期的单表 insert 频率约 20Hz（次/秒，我喜欢这个单位，让我想起国内交流电是 50Hz），但我估计以后会有 500Hz+ 的峰值。目前的工作成果，额定功率 200Hz（CPU 占用 10 – 20，load avg = 2），最大功率 500Hz（这时 load avg > 20，很明显，只能暂时挺挺，应该在出现这种负载前提前拆表了）

  1. INSERT DELAYED INTO

从 数据的插入开始说起。如果可以容忍结果几秒以后再生效的，可以用 INSERT DELAYED INTO，因为在我的这个结构中不需要对同一个 key 频繁的 INSERT/SELECT，因为 SELECT 我是用 Memcached 挡住了，除非 Memcached 挂了，或者数据实在老到过期了，才会去 SELECT。而且要注意，如果 PHP 不需要关心 MySQL 操作的返回结果，应该使用 unbuffered query，简单的说，在你提交 query 后，不用等待 MySQL 有返回信息就继续执行之后的 PHP 指令，具体用法是用 mysql_unbuffered_query 代替 mysql_query，如果用的 MySQLi 类，应该使用 mysqli->query($sQuery, MYSQLI_USE_RESULT);

如果 SHOW PROCESSLIST，可以看到用户名为 DELAYED 的进程，进程数量等于 INSERT DELAYED 的表的数量，因为表级锁的存在，每个表一条以上的 DELAYED 进程是没有意义的

关于这个功能的 my.cnf 配置有三条，我定为如下值

  1. delayed_insert_limit = 1000
  2. delayed_insert_timeout = 300
  3. delayed_queue_size = 5000

连接

有 人说，如果报错连接数过大，你把 max_connections 调大就 OK，如果只这么说而不讲原因，完全是句废话，你调成 1M 肯定不会再报 Too many connections（但应该会报内存溢出之类的），但如果是这样 MySQL 又何必给这个参数？

我看到的一个很有用的公式

  1. key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections

以前只有很模糊的概念，应该设的很大，但又不能太大，具体多大合适，知道这个就明确了。innoDB 的公式比这个复杂点，一并给出

  1. innodb_buffer_pool_size
  2. + key_buffer_size
  3. + max_connections * ( sort_buffer_size + read_buffer_size + binlog_cache_size )
  4. + max_connections * 2MB

还有一个看起来很有用的参数 back_log，给我一种连接池的感觉，而且它确实在起作用，我不知道如果设大了会占用多少内存，但估计不会很多。

key_buffer_size

很 多文章都告诉你越大越好，要为此分配一半左右的物理内存，这么干通常不会出问题，但肯定不是最优的，甚至可以说很无理头——分多少内存应该是根据需求决 定，而不是不管什么机器，都砍掉一半内存用作 key_buffer_size ——有的时候可能是不够，还有的时候可能是浪费。

其实最关 键的指标，还是看 SHOW GLOBAL STATUS 时的 Key_blocks_unused，只要还有剩余，就说明 key_buffer_size 没用满。有人说看 Key_reads 跟 Key_read_requests 的比值，至少要达到 1:100。这可以作为一个结果来衡量，但不够准确，因为在服务器刚启动的时候，大多数请求都要新建缓存，缓存命中比高不起来，需要运行稳定（几小时后） 再观察。我个人建议还是看 Key_blocks_unused

如果不花很长时间在运行中调试，给出一个简单的计算方法，把数据库填满，达到设计时的最大值，看看这时候索引占了多大空间，然后把所有表的索引大小加起来，就是 key_buffer_size 可能达到的最大值，当然，还要留些余地，乘个 2 或 3 之类的。

这是我做测试的时候的 phpMyAdmin 截图，可以看到 key_buffer_size 被浪费了太多。
MySQL MyISAM/InnoDB高并发优化经验
OPTIMIZE TABLE

优 化一下有好处，但会锁住表，是否值得做要权衡一下。拿我现在这个表做例子，有 text 字段，700万条记录，1.5G 大小，优化时间约两分钟，优化后性能提升了 50%，同时表的大小变为 1.4G，但随着表的频繁改写，约一天后又恢复到以前的速度，因此在我看来并不值得。

Query Cache

因为每有写操作 Query Cache 都会被清空，除了极特殊的情况（大量读，少量写，但即使这样也应该是多用 memcached 才对）完全没有必要使用这个，把 query_cache_size 设为 0 关闭这个功能吧。

InnoDB和MyISAM是在使用MySQL最常用的两个表类型，各有优缺点，视具体应用而定。基本 的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快， 但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量 的SELECT，MyISAM是更好的选择。

InnoDB:这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需 要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表,

对于支持事物的InnoDB类型的标，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打 开也可以），将大大提高性能。

MyIASM是IASM表的新版本，有如下扩展：

  • 1、二进制层次的可移植性。
  • 2、NULL列索引。
  • 3、对变长行比ISAM表有更少的碎片。
  • 4、支持大文件。
  • 5、更好的索引压缩。
  • 6、更好的键码统计分布。
  • 7、更好和更快的auto_increment处理。

InnoDB 是 MySQL 上第一个提供外键约束的引擎，除了提供事务处理外，InnoDB 还支持行锁，提供和 Oracle 一样的一致性的不加锁读取，能增加并发读的用户数量并提高性能，不会增加锁的数量。

InnoDB 的设计目标是处理大容量数据时最大化性能，它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。

InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 有它自己的缓冲池，能缓冲数据和索引，InnoDB 还把数据和索引存放在表空间里面，可能包含好几个文件，这和 MyISAM 表完全不同，在 MyISAM 中，表被存放在单独的文件中，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。

InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件，或者是独立的表空间文件),相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

MyISAM 是MySQL缺省存贮引擎 .

每张MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex) 引伸。

因为MyISAM相对简单所以在效率上要优于InnoDB..小型应用使用MyISAM是不错的选择。

MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

以下是一些细节和具体实现的差别：

  • 1、InnoDB不支持FULLTEXT类型的索引。
  • 2、InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
  • 3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
  • 4、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
  • 5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。
  • 6、InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

综上所述，任何一种表都不是万能的，只有恰当的针对业务类型来疡合适的表类型，才能最大的发挥MySQL的性能优势。

两种类型最主要的差别就是 InnoDB 支持事务处理与外键和行级锁.而MyISAM不支持.所以Myisam往往就容易被人认为只适合在小项目中使用。

我作为使用mysql的用户角度出发，innodb和myisam都是比较喜欢的，但是从我目前运维的数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，myisam绝对是我的首选。

原因如下：

  • 1.首先我目前平台上承载的大部分项目是读多写少的项目，而myisam的读性能是比innodb强不少的。
  • 2.myisam的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成innodb比myisam体积庞大不校
  • 3.从平台角度来说，经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候 myisam 的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog 补上。如果是innodb，恐怕不可能有这么快速度，别和我说让innodb定期用导出xxx.sql机制备份，因为我平台上最小的一个数据库实例的数据 量基本都是几十G大校
  • 4.从我接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作innodb其实也是会锁表的，很多人以为innodb是行级锁，那个只是 where对它主键是有效，非主键的都会锁全表的。
  • 5.还有就是经常有很多应用部门需要我给他们定期某些表的数据，myisam的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自 己在对应版本的数据库启动就行，而innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。
  • 6.如果和myisam比insert写操作的话，innodb还达不到myisam的写性能，如果是针对基于索引的update操作，虽然myisam可能会逊色innodb,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。
  • 7.如果是用Myisam的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。

当然innodb也不是绝对不用，用事务的项目如模拟炒股项目，我就是用innodb的，活跃用户20多万时候，也是很轻松应付了，因此我个人也是很喜欢Innodb的，只是

如果从数据库平台应用出发，我还是会首选myisam.

PS:可能有人会说你myisam无法抗太多写操作，但是我可以通过架构来弥补，说个我现有用的数据库平台容量：主从数据总量在几百T以上，每天十多亿 pv的动态页面，还有几个大项目是通过数据接口方式调用未算进pv总数，(其中包括一个大项目因为初期memcached没部署,导致单台数据库每天处理 9千万的查询)。而我的整体数据库服务器平均负载都在0.5-1左右。

MyISAM和InnoDB优化：

key_buffer_size – 这对MyISAM表来说非常重要。如果只是使用MyISAM表，可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 — 记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了 — .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。

innodb_buffer_pool_size – 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。

innodb_additional_pool_size – 这个选项对性能影响并不太多，至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的内存有多少。

innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我经常设置为 64-512MB，跟据服务器大小而异。

innodb_log_buffer_size 默 认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可 以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 — 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。默认值是 1，这意味着每次提交的更新事务（或者每个事务之外的语句）都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置 为 0 就快很多了，不过也相对不安全了 — MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。

table_cache — 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度 地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适（每个线程都需要打开表），如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。

thread_cache — 线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。

query_cache — 如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通 常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。

sort_buffer_size –如果你只有一些简单的查询，那么就无需增加它的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。
文章来源：https://soulogic.com/archives/347
http://www.ha97.com/4170.html

相关文章