线上连载讨论：数据库运维之MySQL初级应用（有奖）

chenyx

回复 49# ning_lianjie


    那样的话,如果备份数据,删除无用数据,文件本身不变小,拷贝也麻烦啊.
    有没有强制mysql回收innodb删除的命令

小版主杀手

对  我确实是加了这个log_queries_not_using_indexes
以后优化的话可以从这些sql语句中添加索引考虑

ning_lianjie

回复 51# chenyx

没有命令
不过innodb的后台线程master_thread,每10秒钟都会检查并删除无用的Undo页.每页的大小是16K

从源代码中可以发现,InnoDB存储引擎每次最多删除20个undo页

chenyx

了解了,谢谢ning

ning_lianjie

回复 52# 小版主杀手

如果有大量的小查询,设置这个参数会消耗磁盘IO,而且少于10万行的数据,加不加索引,效果差不多的,经验之谈,仅供参考,哈哈

ning_lianjie

回复 54# chenyx


    客气了,互相学习吧,通过沟通,我也有了"温故而知新"的感觉.

小版主杀手

回复  小版主杀手

如果有大量的小查询,设置这个参数会消耗磁盘IO,而且少于10万行的数据,加不加索引,效果 ...
ning_lianjie 发表于 2011-09-23 15:30

多谢 难怪 我觉得我ssh登陆这台mysql服务器 感觉打命令都很慢 但是实际CPU 内存什么的都没有一点 看样子要取消掉这个开启慢查询的了 ，尤其是不加索引的查询
另外在my.cnf中加入
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index=mysql-bin.index
会导致系统变很慢吗也

ning_lianjie

回复 25# yuhongchun
嗯,这些内容可以再搞个话题,专门讨论一下.呵呵

ning_lianjie

回复 57# 小版主杀手

慢查询可以打开,只需要把不加索引那个取消.
二进制日志文件(binlog)按照官方的说法,只降低1%的性能,而且binlog可以基于时间点进行数据恢复,强烈建议:打开.除非遇到大量插入,更新,删除的系统,而这些数据也没有保留价值,这样的话,可以关闭boinlog

ning_lianjie

回复 57# 小版主杀手

贴一个配置文件,是MySQL 5.5版的,如果用到5.0或5.1版本,有一些选项需要注释掉.

1. # MySQL client
   
2. [client]
   
3. port            = 3306
   
4. socket          = /usr/local/mysql/data/mysql.sock
   
5. 
   
6. # MySQL server 5.5.x
   
7. [mysqld]
   
8. port            = 3306
   
9. socket          = /usr/local/mysql/data/mysql.sock
   
10. 
    
11. thread-concurrency = 16
    
12. default-storage-engine = INNODB
    
13. transaction-isolation = REPEATABLE-READ
    
14. 
    
15. # *** NETWORK ***
    
16. back-log = 50
    
17. max-connections = 1024
    
18. max-user-connections = 0
    
19. max-connect-errors = 100
    
20. net_buffer_length = 8K
    
21. max-allowed-packet = 16M
    
22. interactive-timeout = 900
    
23. wait-timeout = 900
    
24. #bind-address=192.168.250.253
    
25. skip-name-resolve
    
26. 
    
27. # *** SESSION THREAD ***
    
28. thread-cache-size = 200
    
29. thread-stack = 192K
    
30. 
    
31. # *** TABLE CACHE ***
    
32. max-heap-table-size = 64M
    
33. tmp-table-size = 64M
    
34. table-open-cache = 2048
    
35. sort-buffer-size = 8M
    
36. join-buffer-size = 8M
    
37. 
    
38. # *** QUERY Specific options ***
    
39. query-cache-limit = 2M
    
40. query-cache-min-res-unit = 4096
    
41. query-cache-size = 64M
    
42. #query-cache-strip-comments = 0
    
43. query-cache-type = 1
    
44. query-cache-wlock-invalidate = 0
    
45. 
    
46. # *** LOG ***
    
47. slow-query-log = 1
    
48. slow_query_log_file = mysql.slow
    
49. long-query-time = 1
    
50. #log-queries-not-using-indexes
    
51. log-error = mysql.error
    
52. 
    
53. # ***  Replication related settings ***
    
54. server-id           = 1
    
55. #binlog-format       = mixed
    
56. binlog-cache-size   = 32K
    
57. max-binlog-size     = 512M
    
58. sync-binlog         = 0
    
59. #innodb-support-xa  = 1
    
60. log-bin             = mysql-bin
    
61. log-bin-index       = mysql-bin.index
    
62. relay-log           = mysql-relay-bin
    
63. relay-log-index     = mysql-relay-bin.index
    
64. expire-logs-days    = 3
    
65. log-slave-updates   = 1
    
66. #read-only           = 1
    
67. skip-slave-start
    
68. 
    
69. #report-host         = FQDN(/IP)
    
70. #report-port         = 3306
    
71. 
    
72. # *** MyISAM Specific options ***
    
73. ft-min-word-len = 4
    
74. read-buffer-size = 2M
    
75. read-rnd-buffer-size = 16M
    
76. key-buffer-size = 1024M
    
77. bulk-insert-buffer-size = 64M
    
78. myisam-sort-buffer-size = 16M
    
79. myisam-max-sort-file-size = 10G
    
80. myisam-repair-threads = 1
    
81. myisam-recover
    
82. 
    
83. # *** INNODB Specific options ***
    
84. innodb-fast-shutdown = 1
    
85. innodb-force-recovery = 0
    
86. #innodb-buffer-pool-size = 4G
    
87. innodb-additional-mem-pool-size = 16M
    
88. innodb-log-buffer-size = 8M
    
89. innodb-log-file-size = 256M
    
90. innodb-log-files-in-group = 3
    
91. innodb-mirrored-log-groups = 1
    
92. #innodb-data-file-path = ibdata1:10240M:autoextend
    
93. #innodb-autoextend-increment = 8M
    
94. innodb-file-per-table = 1
    
95. innodb-open-files=30000
    
96. innodb-write-io-threads = 8
    
97. innodb-read-io-threads = 8
    
98. innodb-thread-concurrency = 16
    
99. innodb-flush-log-at-trx-commit = 2
    
100. innodb-flush-method = O_DIRECT
     
101. innodb-max-dirty-pages-pct = 90
     
102. innodb-lock-wait-timeout = 30
     
103. 
     
104. [mysqldump]
     
105. quick
     
106. max-allowed-packet = 16M
     
107. 
     
108. [mysql]
     
109. no-auto-rehash
     
110. 
     
111. [myisamchk]
     
112. key-buffer-size = 512M
     
113. sort-buffer-size = 512M
     
114. read-buffer = 8M
     
115. write-buffer = 8M
     
116. 
     
117. [mysqlhotcopy]
     
118. interactive-timeout
     
119. 
     
120. [mysqld-safe]
     
121. open-files-limit = 60000
     
122. 
     

复制代码