MySQL数据库MyISAM/InnoDB高并发优化经验随笔

发布日期:2018-10-11 点击次数:
  题外话:移动互联网的出现,可以创造很多可能,比如像在小公司呆着的运维或码农可能按以前很少接触到高并发的写入或访问量,但现在移动互联网的时代下,好像特别容易就碰到了!今天分享一个自己实际中的解决方案。部分内容来自教主分享。
  my.cnf 配置有三条,我定为如下值
  delayed_insert_limit = 1000
  delayed_insert_timeout = 300
  delayed_queue_size = 5000
  不要以为简单的给 max_connections 调大就OK,不分应用场合的话,完全是句废话,不然 MySQL 又何必给这个参数?
  我看到的一个很有用的公式
  key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections
以前只有很模糊的概念,应该设的很大,但又不能太大,具体多大合适,知道这个就明确了。innoDB 的公式比这个复杂点,一并给出
  innodb_buffer_pool_size
  + key_buffer_size
  + max_connections * ( sort_buffer_size + read_buffer_size + binlog_cache_size )
  + max_connections * 2MB
  还有一个看起来很有用的参数 back_log,给我一种连接池的感觉,而且它确实在起作用,我不知道如果设大了会占用多少内存,但估计不会很多。
  key_buffer_size
  很多文章都告诉你越大越好,不错也不完全对!
  其实关键的指标,还是看 SHOW GLOBAL STATUS 时的 Key_blocks_unused,只要还有剩余,就说明 key_buffer_size 没用满.至于法老们说的 Key_reads 跟 Key_read_requests 的比值,至少要达到 1:100,甚至推荐1:1000,需要等数据库运行稳定几小时后再观察。个人建议还是看 Key_blocks_unused
  OPTIMIZE TABLE
  优化表,优化一下是有好处的,但看值不值!频繁改写的表,短时间的性能提升这样的就不要优化了。
  Query Cache
  如果前端没有memcached抗着,那就开着吧。建议是关了它 query_cache_size 设为 0 ,前端的查询使用memached缓存起来。
  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 的内存。搞不好也许会降低性能。
  show variables 详解
  back_log
  MySQL主线程检查连接并启动一个新线程这段时间内,可以设置多少个请求可以被存在堆栈中
  connect_timeout
  连接超时 检测方法 telnet ip 3306
  delayed_insert_timeout
  INSERT语句的执行超时时间。若超时,mysql将终止本次INSERT操作。
  delayed_insert_limit 
  检查是否有任何SELECT语句未执行,将数据先交给内存队列,然后慢慢地插入
  delayed_queue_size
  为INSERT DELAYED分配多一个队列;如果队列满了,当有客户端插入时将等待直到队列又有空间了
  备注:INSERT DELAYED来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多
  flush_time
  多少秒所有表都关闭 释放资源
  interactive_timeout
  客户端和服务器交互的空闲超时时间 该系统变量仅当客户端连接服务器时设置了“MYSQL_CLIENT_INTERACTIVE”标志才生效 启用MYSQL_CLIENT_INTERACTIVE模式,连接数据库服务器
  join_buffer_size
  表和表联接的缓冲区的大小,连接 添加所以。一般多个表连接 使用该项
  key_buffer_size
  索引的缓冲区大小,它决定索引处理的速度,尤其是索引读的速度
  lower_case_table_names
  大小写状态 1为忽略大小写 大写变为小写 0为去问大小写
  long_query_time
  如果一个查询所用时间超过它(以秒计),Slow_queries记数器将被增加。
  max_allowed_packet
  包的大尺寸,消息缓冲区被初始化,大小为net_buffer_length,需要时增加max_allowed_packet
  max_connections
  允许的同时客户的数量
  max_connect_errors
  当主机连接mysql服务器出现设定连接错误,就会出现屏蔽掉该主机发起的任何连接,在mysqld重新启动后才可以重新连接
  max_delayed_threads
  设定大启动线程来处理INSERT DELAYED语句。
  max_heap_table_size
  用户可以创建的内存表(memory table)的大小,与tmp_table_size一起限制了内部内存表的大小,内存表是使用哈希散列索引把数据保存在内存中, 读写速度快
  max_sort_length
  在排序BLOB或TEXT值时使用的字节数
  max_tmp_tables 
  客户能同时保持打开的临时表的较大数量
  max_write_lock_count
  缺省情况下,写操作的优先级要高于读操作。这就会有个问题:当我发出若干个写请求,就会堵塞所有读请求,直到写全都处理完,才有机会处理读,当设置后,系统处理一个写操作,就会暂停写,给读操作执行的机会。
  net_buffer_length 
  建立连接时的连接缓冲和结果缓冲
  query_buffer_size 
  查询时缓冲区大小
  record_buffer
  每张表分配的缓冲区的大小
  sort_buffer 
  进行排序的连接分配的缓冲区的大小
  table_cache
  表缓存大小
  thread_concurrency
  这个变量是针对Solaris系统的 CPU核数的2倍
  thread_cache_size
  当客户端断开之后,服务器处理此客户的线程将会缓存起来以响应
  下一个客户而不是销毁
  tmp_table_size
  临时表大小
  thread_stack
  每个线程的栈大小
  wait_timeout
  连接空闲超过设定值 则断开
  转自:吕滔博客
分享到:
×

微信扫一扫分享