MySQL优化建议

1. key_buffer_size(VARIABLES)是针对Key_reads(STATUS)太大(Key_reads/Key_read_requests比率要尽量小,一般在1/1000以下);table_cache(VARIABLES)是针对Opened_tables(STATUS)太大,Opened_tables太大说明很多打开表的操作(访问一个表时,mysql将先打开这个表)必须在关闭其它表的状态下进行,所以必须增大table_cache的值 2. thread_created(STATUS)值比较大,而max_connections(VARIABLES)为300及max_used_connections(STATUS)不到300,可以考虑将thread_cache_size设为300,目的就是为了使thread_created/Connections比值尽量小!即增加thread的重用性!(手册上讲的很详细) 3. 为使Created_tmp_disk_tables(STATUS)尽量变为0,可以再适当增加tmp_table_size,这是为了尽量使临时表在内存中操作!同时也可以考虑增大max_tmp_tables(VARIABLES)的值! Handler_read_rnd(STATUS)以及Handler_read_rnd_next(STATUS)很大,表明使用很多需要遍历表的查询或者没有很好的使用索引!解决这个问题需要优化你的SQL语句!同样,Slow_queries很大也表明了类似的问题!(Slow_queries是指超过long_query_time(VARIABLES)设定的秒数的SQL语句执行数量) 5. Key_blocks(STATUS)相对太小,说明关键缓存使用率很低,这也说明了Key_buffer_size太小! 6. Sort_scan(STATUS)比较大,说明使用了比较多的表扫描排序,也即没有很好的使用索引(后面的判断是个人所见,呵呵) 7. Talbe_locks_waited很大,说明有很多表锁定操作没有立即执行,这也需要通过优化表结构和SQL语句来避免,当然,在应用层也可以考虑更有效的使用表锁定(锁表与解锁之间的操作尽量不要有耗时的SQL操作)

2010-01-22 · 1 min · 13 words · kevin

InnoDB线程并发检查机制

InnoDB在接受MySQL线程调用时,有一个并发线程的检查机制,通过innodb_thread_concurrency参数进行控制。如果参数设置大于0,则表示检查机制开启,允许进入的线程数就是参数的值。等于0则禁用并发检查。 在新的MySQL线程调用Innodb接口前,Innodb会检查已经接受的请求线程数,如已经超过innodb_thread_concurrency设置的限制,则该请求线程会等待innodb_thread_sleep_delay微秒后尝试重新请求,如果第二次请求还是无法获得,则该线程会进入线程队列休眠。重试两次的机制是为了减少CPU的上下文切换的次数,以降低CPU消耗,这和Oracle中latch的spin机制是同样的道理。如果请求被Innodb接受,则会获得一个次数为innodb_concurrency_tickets(默认500次)的通行证,在次数用完之前,该线程重新请求时无须再进行前面所说innodb_thread_concurrency的检查。 上述检查逻辑在源码storage/innobase/srv/srv0srv.c(Innodb很多参数都可以在该文件中找到定义)的srv_conc_enter_innodb函数中,有兴趣的可以仔细阅读一下,代码比较浅显,不难理解。另外,如果是一个已经持有lock的线程,则通过调用srv_conc_force_enter_innodb函数可以无视该检查,这是为了避免线程长时间持有锁影响性能,且可能增加死锁的机率。除此之外,slave线程也是有无视检查直接通行的权限。 简单思考一下上述机制,可以得出一个初步的推论:在数据库并发请求较小的情况下,从性能上来说禁用检查机制应该是更好的,毕竟执行检查机制本身也需要加锁(Mutex)。当并发线程很高的情况下,则开启检查机制对性能更有利。至于具体innodb_thread_concurrency设置为多少,可能就需要在不同的条件下实际的做一下测试了,不同的硬件环境,不同的MySQL版本和Innodb版本,应该都会有一些区别。 源代码中对于innodb_thread_concurrency参数的注释如下: /* The following controls how many threads we let inside InnoDB concurrently: threads waiting for locks are not counted into the number because otherwise we could get a deadlock. MySQL creates a thread for each user session, and semaphore contention and convoy problems can occur withput this restriction. Value 10 should be good if there are less than 4 processors + 4 disks in the computer. Bigger computers need bigger values. Value 0 will disable the concurrency check. */ ulong srv_thread_concurrency = 0; 因为检查机制需要Mutex保护(Mutex-based Model),所以开启检查本身也有性能消耗,并且扩展性也会受到限制,在MySQL5.4版本中引入了一种新的机制(Timer-based Model),这里就不讨论了,实际上XtraDB存储引擎里已经包含Timer-based Model,通过参数innodb_thread_concurrency_timer_based可以开启,默认为OFF。在MySQL5.4的srv0srv.c的源代码中的注释中,可以看到Google和Percona的版权声明,看来MySQL5.4中吸引了很多第三方的改进代码,值得期待。

2009-11-13 · 1 min · 92 words · kevin