继前几天写了两篇关于MySQL数据库优化的文章之后(《wordpress 数据库优化,解决主机CPU占用达到100%的烦恼》《optimize table tablename 优化MySQL数据表》),MySQL的优化是不是就结束了呢?答案是,远没有结束,bug还挺多,我们需要一个一个地把消耗服务器资源的问题找出来并加以解决。

今天又遇到了一个新问题,mysql下执行 show processlist;命令是,发现 Copying to tmp table | SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postme 执行时间非常长,非常消耗服务器资源,造成网站卡顿。那么,我们需要按照下面的方法将服务器做一下调整就可以了。以下是原文,我就直接复制过来,不再翻译了。

You may need to try setting certain variables within your session

These particular values may be too small for your DB Connection to fulfill the query efficiently. These can be set within as follows:

  • To see what values these settings have currently do the following:
    • SHOW VARIABLES LIKE 'max_heap_table_size';
    • SHOW VARIABLES LIKE 'tmp_table_size';
  • To set max_heap_table_size to 64M do the following:
    • SET max_heap_table_size = 1024 * 1024 * 64;
  • To set tmp_table_size to 32M do the following:
    • SET tmp_table_size = 1024 * 1024 * 32;

Please consult the MySQL Documentation on Temp Table Usage

If you cannot set these values within your own session, contact your hosting provider to dynamically set them in your my.cnf.

Give it a Try !!!



您的电子邮箱地址不会被公开。 必填项已用*标注