Mysql的my.cnf最佳优化配置

DirectAdmin 官方的是最好的：
http://help.directadmin.com/item.php?id=44
我个人测试结果 这样的配置 根据自己的内存
cpu 修改一下 跑起来状态最好

1. [mysqld]
   
2. skip-locking
   
3. skip-innodb
   
4. query_cache_limit=1M
   
5. query_cache_size=32M
   
6. query_cache_type=1
   
7. max_connections=500
   
8. interactive_timeout=100
   
9. wait_timeout=100
   
10. connect_timeout=10
    
11. thread_cache_size=128
    
12. key_buffer=16M
    
13. join_buffer=1M
    
14. max_allowed_packet=16M
    
15. table_cache=1024
    
16. record_buffer=1M
    
17. sort_buffer_size=2M
    
18. read_buffer_size=2M
    
19. max_connect_errors=10
    
20. # Try number of CPU's*2 for thread_concurrency
    
21. thread_concurrency=2
    
22. myisam_sort_buffer_size=64M
    
23. log-bin
    
24. server-id=1
    
25. 
    
26. [safe_mysqld]
    
27. err-log=/var/log/mysqld.log
    
28. open_files_limit=8192
    
29. 
    
30. [mysqldump]
    
31. quick
    
32. max_allowed_packet=16M
    
33. 
    
34. [mysql]
    
35. no-auto-rehash
    
36. #safe-updates
    
37. 
    
38. [isamchk]
    
39. key_buffer=64M
    
40. sort_buffer=64M
    
41. read_buffer=16M
    
42. write_buffer=16M
    
43. 
    
44. [myisamchk]
    
45. key_buffer=64M
    
46. sort_buffer=64M
    
47. read_buffer=16M
    
48. write_buffer=16M
    
49. 
    
50. [mysqlhotcopy]
    
51. interactive-timeout
    

复制代码

第二个就是国外的了
也一样需要根据自己的服务器硬件 网站程序负载 流量 等信息 修改一下

1. 
   
2. [mysqld]
   
3. datadir=/var/lib/mysql
   
4. socket=/var/lib/mysql/mysql.sock
   
5. skip-locking
   
6. #skip-networking
   
7. safe-show-database
   
8. query_cache_limit=1M
   
9. query_cache_size=64M ## 32MB for every 1GB of RAM
   
10. query_cache_type=1
    
11. max_user_connections=200
    
12. max_connections=500
    
13. interactive_timeout=10
    
14. wait_timeout=20
    
15. connect_timeout=20
    
16. thread_cache_size=128
    
17. key_buffer=128M ## 64MB for every 1GB of RAM
    
18. join_buffer=1M
    
19. max_connect_errors=20
    
20. max_allowed_packet=16M
    
21. table_cache=1024
    
22. record_buffer=1M
    
23. sort_buffer_size=2M ## 1MB for every 1GB of RAM
    
24. read_buffer_size=2M ## 1MB for every 1GB of RAM
    
25. read_rnd_buffer_size=2M  ## 1MB for every 1GB of RAM
    
26. thread_concurrency=2 ## Number of CPUs x 2
    
27. myisam_sort_buffer_size=64M
    
28. server-id=1
    
29. log_slow_queries=/var/log/mysql-slow-queries.log
    
30. long_query_time=2
    
31. collation-server=latin1_general_ci
    
32. old-passwords
    
33. 
    
34. [mysql.server]
    
35. user=mysql
    
36. basedir=/var/lib
    
37. 
    
38. [safe_mysqld]
    
39. err-log=/var/log/mysqld.log
    
40. pid-file=/var/lib/mysql/mysql.pid
    
41. open_files_limit=8192
    
42. 
    
43. [mysqldump]
    
44. quick
    
45. max_allowed_packet=16M
    
46. 
    
47. [mysql]
    
48. no-auto-rehash
    
49. #safe-updates
    
50. 
    
51. [isamchk]
    
52. key_buffer=32M
    
53. sort_buffer=32M
    
54. read_buffer=16M
    
55. write_buffer=16M
    
56. 
    
57. [myisamchk]
    
58. key_buffer=32M
    
59. sort_buffer=32M
    
60. read_buffer=16M
    
61. write_buffer=16M
    
62. 
    
63. [mysqlhotcopy]
    
64. interactive-timeout

复制代码