MySQL性能优化

  • max_connections=512:最大连接数,解释哈。
  • query_cache_type

含义:查询缓冲类型。

影响:为1是使用缓冲,2是除非使用SQL_CACHE才进行缓冲。对于缓冲而言,数据并不是实时的,有一定的延时。但是对于实时性要求不高的查询短时间内多次执行,是不划算的,这个时候就需要缓存。并且缓存中是区分空格和大小写的,如果大小写不一致和空格不一致,也会认为是不同的SQL,不会利用到缓存。虽然不设置查询缓冲,有时可能带来性能上的损失,但有一些SQL语句需要实时地查询数据,或并不经常使用(可能一天就执行一两次),这样就需要把缓冲关了,可以采用临时关闭的方法SELECT SQL_NO_CACHE。

建议:一般设置为1。

  • query_cache_size=32M

含义:指定MySQL查询结果缓冲区的大小。

影响:如果应用程序有大量读,而且没有应用程序级别的缓存,那么这很有用。不过不要设置太大,因为维护它也需要不少开销,这会导致MySQL变慢。

建议:通常设置为 32-512Mb。设置完之后最好是跟踪一段时间,查看是否运行良好。在一定的负载压力下,如果缓存命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整太低了,就启用它,如果命中率已经不错了,就可以把他调小一点。对于2G的内存,先从16M开始,一倍一倍的增加,直到命中率比较稳定为止。设置后可以使用show variables like ‘%query_cache%';进行查询.

  • table_cache=256

含义:table高速缓存的数量

影响:当某一连接访问一个表时,MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开,则会直接访问缓存中的表已加快查询速度;如果该表未被缓存,则会将当前的表添加进缓存并进行查询。

建议:通常设置为256。

  • tmp_table_size=18M

含义:临时表高速缓存的缓冲区大小

影响:通过设置tmp_table_size选项来增加一张临时表的大小,例如做高级GROUP BY操作生成的临时表。如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表。

建议:默认是32M,建议64M以内。

官网文档:This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.

  • log-bin=binlog.log:bin日志
  • binlog_cache_size: 一般环境2MB~4MB是一个合适的选择,事务较大且写入频繁的数据库环境可以适当调大,但不建议超过32MB
  • bulk_insert_buffer_size: 如果经常需要使用批量插入的特殊语句来插入数据,可以适当调大该参数至16MB~32MB;
  • thread_cache_size=8

含义:缓存可重用的线程数。

影响:这个参数设置线程的缓存,线程的创建和销毁的开销可能很大,因为每个线程的连接/断开都需要。如果应用程序中有大量的跳跃并发连接并且线程较多的话,就要加大它的值。它的目的是在通常的操作中无需创建新线程。

建议:通常至少设置为16。

  • myisam_max_sort_file_size=64G

含义:允许使用的临时文件最大值。

影响:当对MyISAM表重建索引时(repair/alter table/load data infile),允许使用的临时文件最大值;如果超过此限制索引创建则改用key cache,此时show processlist会显示该线程处于”repair with keycache”而非”repair by sorting”,前者逐条创建索引记录;另外,当指定的tmpdir目录空间不足时也会导致类似情形;

建议:我的设置为64G

  • myisam_sort_buffer_size=8M

含义:MyISAM表发生变化时重新排序所需的缓冲。

影响:MyISAM表发生变化时重新排序所需的缓冲。如果你经常使用CREATE INDEX or ALTER TABLE,那么可以设置大一点,一般小于64M。

建议:小于64M。

官网文档:The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

  • key_buffer_size=256M

含义:用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写)。

影响:对于MyISAM表的影响不是很大,MyISAM会使用系统的缓存来存储数据,所以大量使用MyISAM表的机器内存很快就会耗尽。但是,如果你将该值设得过大(例如,大于总内存的50%),系统将转换为页并变得极慢。MySQL依赖操作系统来执行数据读取时的文件系统缓存,因此你必须为文件系统缓存留一些空间。

建议:先设置为内存的25%,观察性能变化。

如果不使用MyISAM存储引擎,key_buffer_size设置16MB足以,用来缓存一些系统表信息等。如果使用 MyISAM存储引擎,在内存允许的情况下,尽可能将所有索引放入内存,简单来说就是“越大越好”

  • read_buffer_size=2M

含义:顺序查询操作所能使用的缓冲区大小。

影响:和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。

建议:一般设置为2M再观察变化。

  • read_rnd_buffer_size=2M

含义:随机查询操作所能使用的缓冲区大小。

影响:每个线程独享。

建议:一般设置为2M再观察变化。

也就是说这个也不是全局性的,是每个客户端的缓冲区,个人建议2M以内

  • sort_buffer_size=256K

含义:为每个需要进行排序的线程分配该大小的一个缓冲区。

影响:增加这值加速ORDER BY或GROUP BY操作。不过该参数对应的分配内存是每连接独占的,如果有100个连接,那么实际分配的总共排序缓冲区大小为100×sort_buffer_size。

建议:一般设置为2M以内。

官方文档:Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.

Your sort_buffer_size value seems extremely high. The default is 2M. I’d recommend going no larger than that since there is a performance penalty for going higher. Some people recommend smaller values such as 256kB. One thing to remember is this is per-client-session, it’s not a global value. Large values will add up fast.

也就是说sort_buffer_size是单个客户端缓冲区的值,并不是全局性,个人不建议设置太大,建议到256KB-2M之间;

  • innodb_additional_mem_pool_size=2M

innodb_additional_mem_pool_size这个参数一般建议调整到8MB~16MB,如果表特别多,可以调整到32MB,可以根据error log中的信息判断是否需要增大;

  • innodb_flush_log_at_trx_commit=1

innodb_max_dirty_pages_pct: 根据以往的经验,重启恢复的数据如果要超过1GB的话,启动速度会比较慢,几乎难以接受,所以建议不大于 1GB/innodb_buffer_pool_size(GB)*100 这个值。当然,如果你能够忍受启动时间比较长,而且希望尽量减少内存至磁盘的flush,可以将这个值调整到90,但不建议超过90

  • innodb_log_buffer_size=1M

 

innodb_log_buffer_size默认是1MB,系的如频繁的系统可适当增大至4MB~8MB。默认的设置在中等强度的写入负载及短事物处理时,性能还可以。但是存在大量更新操作或者负载较大时,就要慢慢增加这个参数的值了。不过不要设置太大,会浪费内存。它每秒都会刷新一次,所以不用设置超过1s所需的内存空间,一般来说不建议超过32MB。

  • innodb_buffer_pool_size=47M

对InnoDB的效率影响很大,因为InnoDB会把尽可能多的数据和索引缓存在缓冲区,这个类似与Oracle的Buffer Pool:如果只采用InnoDB,可以把这个参数调大一点,大约内存的70%左右。如果不使用InnoDB存储引擎,innodb_buffer_pool_size可以不用调整这个参数,当然,如果数据量不会暴增并且不是特别大,这个参数还是不要太大了,浪费空间。

  • innodb_log_file_size=128M

如果对 Innodb 数据表有大量的写入操作,那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要。然而设置太大了,就会增加恢复的时间,因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复。在高写入负载尤其是数据集很大的时候,这个值非常重要,值越高性能越好,不过可能会增加数据恢复的时候。一般设置为128M。

  • innodb_thread_concurrency=8

InnoDB使用操作系统线程来处理用户的事务请求。(在事务提交或回滚之前可能给InnoDB引擎带来很多的请求)。在现代化操作系统和多核处理器的服务器,上下文切换是有效的,大多数工作负载运行没有任何并发线程数量的限制。在MySQL 5.5及以上版本中,MySQL做了可伸缩性的改进,它减少了这种在InnoDB内部限制并发执行线程数量的需要。

如果工作负载一直较为严重甚至偶尔达到顶峰,建议先设置innodb_thread_concurrency=128,并通过不断的降低这个参数,96, 80, 64等等,直到发现能够提供最佳性能的线程数,例如,假设系统通常有40到50个用户,但定期的数量增加至60,70,甚至200。你会发现,性能在80个并发用户设置时表现稳定,如果高于这个数,性能反而下降。在这种情况下,建议设置innodb_thread_concurrency参数为80,以避免影响性能。

在MySQL 和 InnoDB之前的版本系列中,innodb_thread_concurrency的默认值,以及其隐含的限制并发线程执行的数量都进行过调整。在当前最新版本的mysql中,innodb_thread_concurrency的默认值为0,它表示默认情况下不限制线程并发执行的数量。

需要注意的是,InnoDB导致线程睡眠当且仅当并发线程的数量是有限的时候。如果对线程并发执行的数量没有限制,所有的请求都会被认为是可调度的,也就是说,如果innodb_thread_concurrency的值设置为0,innodb_thread_sleep_delay的值将会被忽略。

当有对线程数量进行限制(即innodb_thread_concurrency参数> 0),InnoDB通过允许多个请求在一个SQL语句执行过程中进入InnoDB,而不必遵守innodb_thread_concurrency设置的参数限额,来减少上下文的切换开销,当一个SQL语句(如join语句)包含在InnoDB的多行操作时,InnoDB会分配 指定数量的“入场券”,允许一个线程反复使用最小的开销。

  • innodb_max_dirty_pages_pct

innodb_max_dirty_pages_pct 这个参数和上面的各个参数不同,他不是用来设置用于缓存某种数据的内存大小的一个参数,而是用来控制在 InnoDB Buffer Pool 中可以不用写入数据文件中的Dirty Page 的比例(已经被修但还没有从内存中写入到数据文件的脏数据)。这个比例值越大,从内存到磁盘的写入操作就会相对减少,所以能够一定程度下减少写入操作的磁盘IO。

但是,如果这个比例值过大,当数据库 Crash 之后重启的时间可能就会很长,因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中。同时,过大的比例值同时可能也会造成在达到比例设定上限后的 flush 操作“过猛”而导致性能波动很大。

Leave a Comment