求高人优化mysql，服务器负载不高， 但是查询时间长

无名分的浪漫

last pid: 88433;  load averages:  0.31,  0.44,  0.47                                                                                up 6+20:53:10  18:38:54
131 processes: 2 running, 129 sleeping
CPU:  1.6% user,  0.0% nice,  5.9% system,  0.0% interrupt, 92.4% idle
Mem: 522M Active, 2498M Inact, 563M Wired, 76M Cache, 399M Buf, 263M Free
Swap: 3906M Total, 572K Used, 3906M Free


带宽也没有用多少

                    /0   /1   /2   /3   /4   /5   /6   /7   /8   /9   /10
     Load Average   ||

      Interface           Traffic               Peak                Total
            lo0  in      0.000 KB/s         80.917 KB/s           41.202 GB
                 out     0.000 KB/s         80.917 KB/s           41.202 GB

            re0  in      1.778 KB/s          6.000 KB/s           10.158 GB
                 out    30.377 KB/s         52.244 KB/s           65.441 GB


基本上在100K左右


但是mysql查询时间太长了
装的discuz论坛查询时间



GMT+8, 2009-9-4 18:41, Processed in 0.296042 second(s),



不求太高，只要求新装的discuz论坛查询时间能到 0.0X秒的级别就够了

说一下服务器的配置

Q8200四核cpu    4G内存

WEB环境

操作系统及 PHP FreeBSD / PHP v5.2.9
服务器软件 nginx
MySQL 版本 5.1.34 上传许可 2M



mysql数据库不大， 只有1.5G左右 并且都是小数据库，由100多个小的数据库加起来总共1.5G


按现在的情况， 负载不是很高， 流量也不大， 每天估计ip也就是在1W左右(所有网站的访问量加起来)

但是mysql查询速度却很慢

求高人优化一下mysql的my.cnf文件


现在的my.cnf文件

1. [client]
   
2. #password        = your_password
   
3. port                = 3306
   
4. socket                = /tmp/mysql.sock
   
5. 
   
6. # The MySQL server
   
7. [mysqld]
   
8. port                = 3306
   
9. socket                = /tmp/mysql.sock
   
10. skip-locking
    
11. key_buffer = 512M
    
12. max_allowed_packet = 4M
    
13. table_cache = 2048
    
14. #sort_buffer_size = 32M
    
15. #read_buffer_size = 32M
    
16. #read_rnd_buffer_size = 32M
    
17. myisam_sort_buffer_size = 128M
    
18. thread_cache_size = 128
    
19. query_cache_size = 64M
    
20. # Try number of CPU's*2 for thread_concurrency
    
21. thread_concurrency = 6
    
22. wait_timeout = 20
    
23. interactive_timeout=10
    
24. tmp_table_size=128M
    
25. back_log=200
    
26. long_query_time    = 2
    
27. slow_query_log_file  = /var/log/mysql_slow.log
    
28. 
    
29. 
    
30. #skip-networking
    
31. 
    
32. server-id        = 1
    
33. set-variable    = max_connections=10000
    
34. set-variable    = max_connect_errors=99999
    
35. 
    
36. [mysqldump]
    
37. quick
    
38. max_allowed_packet = 16M
    
39. 
    
40. [mysql]
    
41. no-auto-rehash
    
42. # Remove the next comment character if you are not familiar with SQL
    
43. #safe-updates
    
44. 
    
45. [isamchk]
    
46. key_buffer = 256M
    
47. sort_buffer_size = 320M
    
48. read_buffer = 4M
    
49. write_buffer = 4M
    
50. 
    
51. [myisamchk]
    
52. key_buffer = 256M
    
53. sort_buffer_size = 320M
    
54. read_buffer = 4M
    
55. write_buffer = 4M
    
56. 
    
57. [mysqlhotcopy]
    
58. interactive-timeout

复制代码

[ 本帖最后由 无名分的浪漫 于 2009-9-5 00:05 编辑 ]

剑心通明

http://www.bsdlover.cn/html/69/n-1069.html
根据内存选个推荐的my.cnf吧

无名分的浪漫

谢谢 剑心通明 大哥了
我慢慢研究一下！！

HonestQiao

如果要简单一点的话，你实际上可以利用phpMyAdmin，查看服务器运行的情况，做针对性的调整。

snnn

我觉得你对配置文件不存在什么问题。
让mysql把slow query记录下来。然后explain看看。

flfqzrj

有报酬不，如果有报酬应会有很多人，这年头雷峰不容易找

pw东升

安装了类似eAccelerator的程序了吗？
eAccelerator可以在一定程度上提高php的性能（经过实际测试，以bbs.codeday.com为例，在100人在线的时候，首页打开时间大概提高了0.02秒（未安装之前是0.06左右，安装之后为0.04），也就是大概提升了三分之一的速度。
见：http://bbs.laxjyj.com/read-htm-tid-29886.html

wangbin

这个不算高，优化一下DZ吧，去掉不要的功能。

wangbin

1. web# cat my.cnf
   
2. [client]
   
3. #password       = your_password
   
4. port            = 3306
   
5. socket          = /tmp/mysql.sock
   
6. 
   
7. [mysqld]
   
8. port            = 3306
   
9. socket          = /tmp/mysql.sock
   
10. skip-innodb
    
11. #skip-bdb
    
12. bind-address=127.0.0.1
    
13. skip-name-resolve
    
14. skip-locking
    
15. back_log = 1024
    
16. #log-bin
    
17. set-variable = key_buffer=4096M
    
18. set-variable = max_allowed_packet=4M
    
19. set-variable = table_cache=1024
    
20. set-variable = thread_cache=512
    
21. set-variable = join_buffer_size=32M
    
22. set-variable = sort_buffer=64M
    
23. set-variable = record_buffer=256M
    
24. set-variable = max_connections=1024
    
25. set-variable = wait_timeout=20
    
26. set-variable = interactive_timeout=120
    
27. set-variable = max_connect_errors=40000
    
28. set-variable = long_query_time=1
    
29. set-variable = max_heap_table_size=256M
    
30. set-variable = tmp_table_size=128M
    
31. set-variable = thread_concurrency=16
    
32. set-variable = myisam_sort_buffer_size=256M
    
33. 
    
34. key_buffer_size = 4096M
    
35. max_allowed_packet = 4M
    
36. wait_timeout = 20
    
37. table_open_cache = 2048
    
38. sort_buffer_size = 8M
    
39. read_buffer_size = 8M
    
40. read_rnd_buffer_size = 16M
    
41. myisam_sort_buffer_size = 256M
    
42. thread_cache_size = 64
    
43. query_cache_size = 256M
    
44. # Try number of CPU's*2 for thread_concurrency
    
45. thread_concurrency = 16
    
46. 
    
47. 
    
48. # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    
49. # if all processes that need to connect to mysqld run on the same host.
    
50. # All interaction with mysqld must be made via Unix sockets or named pipes.
    
51. # Note that using this option without enabling named pipes on Windows
    
52. # (via the "enable-named-pipe" option) will render mysqld useless!
    
53. #
    
54. #skip-networking
    
55. 
    
56. # Replication Master Server (default)
    
57. # binary logging is required for replication
    
58. #log-bin=mysql-bin
    
59. 
    
60. # required unique id between 1 and 2^32 - 1
    
61. # defaults to 1 if master-host is not set
    
62. # but will not function as a master if omitted
    
63. server-id       = 1
    
64. 
    
65. # Replication Slave (comment out master section to use this)
    
66. #
    
67. # To configure this host as a replication slave, you can choose between
    
68. # two methods :
    
69. #
    
70. # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
    
71. #    the syntax is:
    
72. #
    
73. #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
    
74. #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
    
75. #
    
76. #    where you replace <host>, <user>, <password> by quoted strings and
    
77. #    <port> by the master's port number (3306 by default).
    
78. #
    
79. #    Example:
    
80. #
    
81. #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
    
82. #    MASTER_USER='joe', MASTER_PASSWORD='secret';
    
83. #
    
84. # OR
    
85. #
    
86. # 2) Set the variables below. However, in case you choose this method, then
    
87. #    start replication for the first time (even unsuccessfully, for example
    
88. #    if you mistyped the password in master-password and the slave fails to
    
89. #    connect), the slave will create a master.info file, and any later
    
90. #    change in this file to the variables' values below will be ignored and
    
91. #    overridden by the content of the master.info file, unless you shutdown
    
92. #    the slave server, delete master.info and restart the slaver server.
    
93. #    For that reason, you may want to leave the lines below untouched
    
94. #    (commented) and instead use CHANGE MASTER TO (see above)
    
95. #
    
96. # required unique id between 2 and 2^32 - 1
    
97. # (and different from the master)
    
98. # defaults to 2 if master-host is set
    
99. # but will not function as a slave if omitted
    
100. #server-id       = 2
     
101. #
     
102. # The replication master for this slave - required
     
103. #master-host     =   <hostname>
     
104. #
     
105. # The username the slave will use for authentication when connecting
     
106. # to the master - required
     
107. #master-user     =   <username>
     
108. #
     
109. # The password the slave will authenticate with when connecting to
     
110. # the master - required
     
111. #master-password =   <password>
     
112. #
     
113. # The port the master is listening on.
     
114. # optional - defaults to 3306
     
115. #master-port     =  <port>
     
116. #
     
117. # binary logging - not required for slaves, but recommended
     
118. #log-bin=mysql-bin
     
119. #
     
120. # binary logging format - mixed recommended
     
121. #binlog_format=mixed
     
122. 
     
123. # Point the following paths to different dedicated disks
     
124. #tmpdir         = /tmp/
     
125. #log-update     = /path-to-dedicated-directory/hostname
     
126. 
     
127. # Uncomment the following if you are using InnoDB tables
     
128. #innodb_data_home_dir = /var/db/mysql/
     
129. #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
     
130. #innodb_log_group_home_dir = /var/db/mysql/
     
131. # You can set .._buffer_pool_size up to 50 - 80 %
     
132. # of RAM but beware of setting memory usage too high
     
133. #innodb_buffer_pool_size = 384M
     
134. #innodb_additional_mem_pool_size = 20M
     
135. # Set .._log_file_size to 25 % of buffer pool size
     
136. #innodb_log_file_size = 100M
     
137. #innodb_log_buffer_size = 8M
     
138. #innodb_flush_log_at_trx_commit = 1
     
139. #innodb_lock_wait_timeout = 50
     
140. 
     
141. [mysqldump]
     
142. quick
     
143. max_allowed_packet = 16M
     
144. 
     
145. [mysql]
     
146. no-auto-rehash
     
147. # Remove the next comment character if you are not familiar with SQL
     
148. #safe-updates
     
149. 
     
150. [myisamchk]
     
151. key_buffer_size = 512M
     
152. sort_buffer_size = 512M
     
153. read_buffer = 8M
     
154. write_buffer = 4M
     
155. 
     
156. [mysqlhotcopy]
     
157. interactive-timeout

复制代码

wangbin

1. last pid: 98260;  load averages:  0.55,  0.60,  0.54                                                                 up 57+09:01:48  14:53:28
   
2. 353 processes: 2 running, 351 sleeping
   
3. CPU:  4.7% user,  0.0% nice,  2.2% system,  0.0% interrupt, 93.1% idle
   
4. Mem: 1837M Active, 16G Inact, 410M Wired, 85M Cache, 63M Buf, 12G Free
   
5. Swap: 32G Total, 32G Free
   
6. 
   
7.   PID USERNAME    THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
   
8. 56708 mysql        66  44    0  4597M  1366M ucond  1   0:22  3.03% mysqld
   
9. 98221 www           1   4    0 11160K  5084K kqread 2   0:07  0.49% nginx
   
10. 98149 www           1   4    0 49196K 12908K accept 1   0:01  0.29% php-cgi
    
11. 98115 www           1   4    0 49196K 13112K accept 1   0:01  0.20% php-cgi
    
12. 98081 www           1   4    0 49196K 13140K accept 0   0:02  0.10% php-cgi
    
13. 98109 www           1   4    0 49196K 12724K accept 1   0:02  0.10% php-cgi
    
14. 98096 www           1   4    0 49196K 13184K accept 1   0:02  0.10% php-cgi
    
15. 98093 www           1   4    0 49196K 13188K accept 1   0:02  0.10% php-cgi
    
16. 98094 www           1   4    0 49196K 12852K accept 2   0:02  0.10% php-cgi
    
17. 98111 www           1   4    0 49196K 12952K accept 2   0:02  0.10% php-cgi
    
18. 98097 www           1   4    0 49196K 13140K accept 0   0:02  0.10% php-cgi
    
19. 98082 www           1   4    0 49196K 12852K accept 3   0:02  0.10% php-cgi
    
20. 98078 www           1   4    0 49196K 13196K accept 0   0:02  0.10% php-cgi
    
21. 98092 www           1   4    0 49196K 12944K accept 0   0:02  0.10% php-cgi
    
22. 98102 www           1   4    0 49196K 12760K accept 3   0:02  0.10% php-cgi
    
23. 98106 www           1   4    0 49196K 12824K accept 0   0:02  0.10% php-cgi
    
24. 98107 www           1   4    0 49196K 13132K accept 2   0:02  0.10% php-cgi
    
25. 98067 www           1   4    0 49196K 13148K accept 1   0:02  0.10% php-cgi
    
26. 98066 www           1   4    0 49196K 12944K accept 0   0:02  0.10% php-cgi

复制代码