mariadb配置文件优化参数mysql优化

abc

1. mariadb数据库优化需要根据自己业务需求以及根据硬件配置来进行参数优化，下面是一些关于mariadb数据库参数优化的配置文件。
   
2. 
   
3. 1 如下为128G内存32线程处理器的mariadb配置参数优化：
   
4. [client]
   
5. #password= your_password
   
6. port= 3306         
   
7. socket= /tmp/mysql.sock
   
8. !includedir /opt/local/mysql/wsrep
   
9. # The MySQL server
   
10. [mysqld]
    
11. port= 3306
    
12. socket= /tmp/mysql.sock
    
13. basedir = /opt/local/mysql
    
14. datadir=/opt/local/mysql/data                   #数据库存放目录
    
15. relay-log=/opt/local/mysql/relaylog/s74-relay-bin                        
    
16. pid-file = /opt/local/mysql/mysql.pid
    
17. log-error = /opt/local/mysql/logs/mysqld.log
    
18. open_files_limit = 65535                        #
    
19. #skip-locking
    
20. skip-external-locking                           #跳过外部锁定
    
21. back_log=3000                                   #暂存的连接数量  
    
22. skip-name-resolve                               #关闭mysql的dns反查功能
    
23. memlock                                         #将mysqld 进程锁定在内存中
    
24. lower_case_table_names = 1
    
25. #query_response_time_stats=1
    
26. #core-file
    
27. #core-file-size = unlimited
    
28. query_cache_type=1                              #查询缓存  (0 = off、1 = on、2 = demand)
    
29. performance_schema=0                            #收集数据库服务器性能参数
    
30. net_read_timeout=3600                           #连接繁忙阶段（query）起作用
    
31. net_write_timeout=3600                          #连接繁忙阶段（query）起作用
    
32. key_buffer_size = 32M                           #设置索引块缓存大小
    
33. max_allowed_packet = 128M                       #通信缓冲大小
    
34. table_open_cache = 1024                         #table高速缓存的数量
    
35. sort_buffer_size = 12M                          #每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存
    
36. read_buffer_size = 8M                           #顺序读取数据缓冲区使用内存
    
37. #sort_buffer_size = 32M
    
38. #read_buffer_size = 32M
    
39. read_rnd_buffer_size = 32M                      #随机读取数据缓冲区使用内存
    
40. myisam_sort_buffer_size = 32M                   #MyISAM表发生变化时重新排序所需的缓冲
    
41. thread_cache_size = 120                         #重新利用保存在缓存中线程的数量
    
42. query_cache_size = 64M
    
43. join_buffer_size = 8M                           #Join操作使用内存
    
44. bulk_insert_buffer_size = 32M                   #批量插入数据缓存大小
    
45. delay_key_write=ON                              #在表关闭之前，将对表的update操作指跟新数据到磁盘，而不更新索引到磁盘，把对索引的更改记录在内存。这样MyISAM表可以使索引更新更快。在关闭表的时候一起更新索引到磁盘
    
46. delayed_insert_limit=4000
    
47. delayed_insert_timeout=600
    
48. delayed_queue_size=4000
    
49. # Try number of CPU's*2 for thread_concurrency
    
50. # The variable only affects Solaris!
    
51. thread_concurrency = 64                         #CPU核数 * 2
    
52. max_connections=1500                            #最大连接（用户）数。每个连接MySQL的用户均算作一个连接
    
53. max_connect_errors=30                           #最大失败连接限制
    
54. interactive_timeout=600                         #服务器关闭交互式连接前等待活动的秒数
    
55. wait_timeout=3600                               #服务器关闭非交互连接之前等待活动的秒数
    
56. slow_query_log                                  #慢查询记录日志
    
57. long_query_time = 0.1                           #慢查询记录时间  0.1秒
    
58. slow_query_log_file=/opt/local/mysql/logs/slow_query.log            #慢查询日志路径
    
59. #log_slow_verbosity=full
    
60. log_slow_verbosity=query_plan                   #
    
61. # Replication Master Server (default)
    
62. # binary logging is required for replication
    
63. log-bin=/opt/local/mysql/binlog/mysql-bin                               #binlog 名称
    
64. log-slave-updates                               #从master取得并执行的二进制日志写入自己的二进制日志文件中
    
65. replicate-ignore-db=mysql                       #不同步的表
    
66. # binary logging format - mixed recommended
    
67. binlog_format=row                               #binlog 格式 分别为 row=行格式 丶 mixed=混合格式 丶 STATEMENT=SQL语句复制模式
    
68. event_scheduler=1                               #计划任务 事件调度器
    
69. # required unique id between 1 and 2^32 - 1
    
70. # defaults to 1 if master-host is not set
    
71. # but will not function as a master if omitted
    
72. server-id= 1                                 #
    
73. # Point the following paths to different dedicated disks
    
74. #tmpdir= /tmp/
    
75. #log-update = /path-to-dedicated-directory/hostname
    
76. # Uncomment the following if you are using InnoDB tables
    
77. #innodb_data_home_dir = /data/mysql-5.1.48/mysql-data/
    
78. #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
    
79. #innodb_log_group_home_dir = /data/mysql-5.1.48/mysql-data/
    
80. # You can set .._buffer_pool_size up to 50 - 80 %
    
81. # of RAM but beware of setting memory usage too high
    
82. innodb_file_format=barracuda
    
83. innodb_file_format_max=barracuda
    
84. innodb_file_per_table=1
    
85. innodb_fast_shutdown=0
    
86. innodb_buffer_pool_size = 90000M
    
87. innodb_buffer_pool_instances = 4
    
88. #innodb_additional_mem_pool_size = 20M
    
89. #innodb_use_sys_malloc = 20M
    
90. # Set .._log_file_size to 25 % of buffer pool size
    
91. innodb_log_file_size = 512M
    
92. #innodb_log_buffer_size = 8M
    
93. innodb_log_files_in_group = 3
    
94. innodb_flush_log_at_trx_commit = 2
    
95. innodb_lock_wait_timeout = 3
    
96. innodb_rollback_on_timeout = on
    
97. innodb_flush_method=O_DIRECT
    
98. transaction-isolation=READ-COMMITTED
    
99. innodb_thread_concurrency=0
    
100. innodb_io_capacity=800
     
101. innodb_purge_threads=1
     
102. innodb_open_files=65535
     
103. #innodb_stats_update_need_lock=0
     
104. #innodb_flush_neighbor_pages=0
     
105. #innodb_aio_pending_ios_per_thread=256
     
106. #for binlog_format=row
     
107. innodb_autoinc_lock_mode=2
     
108. #innodb_fast_checksum = 1
     
109. innodb_read_io_threads = 8
     
110. innodb_write_io_threads = 12
     
111. innodb_stats_on_metadata = 0
     
112. #使用线程池处理连接
     
113. thread_handling=pool-of-threads
     
114. thread_pool_oversubscribe=30
     
115. thread_pool_size=64
     
116. thread_pool_idle_timeout=7200
     
117. thread_pool_max_threads=2000
     
118. #查询优化器开关
     
119. #optimizer_switch='index_condition_pushdown=on'
     
120. #optimizer_switch='mrr=on'
     
121. #optimizer_switch='mrr_sort_keys=on'
     
122. #optimizer_switch='mrr_cost_based=off'
     
123. #mrr_buffer_size=32M
     
124. #optimizer_switch='join_cache_incremental=on'
     
125. #optimizer_switch='join_cache_hashed=on'
     
126. #optimizer_switch='join_cache_bka=on'
     
127. #join_cache_level=4
     
128. #join_buffer_size=32M
     
129. #join_buffer_space_limit=32M
     
130. [mysqldump]
     
131. quick
     
132. max_allowed_packet = 16M
     
133. [mysql]
     
134. no-auto-rehash
     
135. # Remove the next comment character if you are not familiar with SQL
     
136. #safe-updates
     
137. [myisamchk]
     
138. key_buffer_size = 256M
     
139. sort_buffer_size = 256M
     
140. read_buffer = 2M
     
141. write_buffer = 2M
     
142. [isamchk]
     
143. key_buffer_size = 256M
     
144. sort_buffer_size = 256M
     
145. read_buffer = 2M
     
146. write_buffer = 2M
     
147. [mysqlhotcopy]
     
148. interactive-timeout
     
149. 2 如下为256G内存64线程处理器的mariadb配置参数优化：
     
150. [client]
     
151. #password= your_password
     
152. port= 3306         
     
153. socket= /tmp/mysql.sock
     
154. !includedir /opt/local/mysql/wsrep
     
155. # The MySQL server
     
156. [mysqld]
     
157. port= 3306
     
158. socket= /tmp/mysql.sock
     
159. basedir = /opt/local/mysql
     
160. datadir=/opt/local/mysql/data                   #数据库存放目录
     
161. relay-log=/opt/local/mysql/relaylog/s74-relay-bin                        
     
162. pid-file = /opt/local/mysql/mysql.pid
     
163. log-error = /opt/local/mysql/logs/mysqld.log
     
164. open_files_limit = 65535                        #
     
165. #skip-locking
     
166. skip-external-locking                           #跳过外部锁定
     
167. back_log=3000                                   #暂存的连接数量  
     
168. skip-name-resolve                               #关闭mysql的dns反查功能
     
169. memlock                                         #将mysqld 进程锁定在内存中
     
170. lower_case_table_names = 1
     
171. #query_response_time_stats=1
     
172. #core-file
     
173. #core-file-size = unlimited
     
174. query_cache_type=1                              #查询缓存  (0 = off、1 = on、2 = demand)
     
175. performance_schema=0                            #收集数据库服务器性能参数
     
176. net_read_timeout=3600                           #连接繁忙阶段（query）起作用
     
177. net_write_timeout=3600                          #连接繁忙阶段（query）起作用
     
178. key_buffer_size = 32M                           #设置索引块缓存大小
     
179. max_allowed_packet = 128M                       #通信缓冲大小
     
180. table_open_cache = 1024                         #table高速缓存的数量
     
181. sort_buffer_size = 12M                          #每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存
     
182. read_buffer_size = 8M                           #顺序读取数据缓冲区使用内存
     
183. #sort_buffer_size = 32M
     
184. #read_buffer_size = 32M
     
185. read_rnd_buffer_size = 32M                      #随机读取数据缓冲区使用内存
     
186. myisam_sort_buffer_size = 32M                   #MyISAM表发生变化时重新排序所需的缓冲
     
187. thread_cache_size = 120                         #重新利用保存在缓存中线程的数量
     
188. query_cache_size = 64M
     
189. join_buffer_size = 8M                           #Join操作使用内存
     
190. bulk_insert_buffer_size = 32M                   #批量插入数据缓存大小
     
191. delay_key_write=ON                              #在表关闭之前，将对表的update操作指跟新数据到磁盘，而不更新索引到磁盘，把对索引的更改记录在内存。这样MyISAM表可以使索引更新更快。在关闭表的时候一起更新索引到磁盘
     
192. delayed_insert_limit=4000
     
193. delayed_insert_timeout=600
     
194. delayed_queue_size=4000
     
195. # Try number of CPU's*2 for thread_concurrency
     
196. # The variable only affects Solaris!
     
197. thread_concurrency = 96                          #CPU核数 * 2
     
198. max_connections=1500                            #最大连接（用户）数。每个连接MySQL的用户均算作一个连接
     
199. max_connect_errors=30                           #最大失败连接限制
     
200. interactive_timeout=600                         #服务器关闭交互式连接前等待活动的秒数
     
201. wait_timeout=3600                               #服务器关闭非交互连接之前等待活动的秒数
     
202. slow_query_log                                  #慢查询记录日志
     
203. long_query_time = 0.1                           #慢查询记录时间  0.1秒
     
204. slow_query_log_file=/opt/local/mysql/logs/slow_query.log            #慢查询日志路径
     
205. #log_slow_verbosity=full
     
206. log_slow_verbosity=query_plan                   #
     
207. # Replication Master Server (default)
     
208. # binary logging is required for replication
     
209. log-bin=/opt/local/mysql/binlog/mysql-bin                               #binlog 名称
     
210. log-slave-updates                               #从master取得并执行的二进制日志写入自己的二进制日志文件中
     
211. replicate-ignore-db=mysql                       #不同步的表
     
212. # binary logging format - mixed recommended
     
213. binlog_format=row                               #binlog 格式 分别为 row=行格式 丶 mixed=混合格式 丶 STATEMENT=SQL语句复制模式
     
214. event_scheduler=1                               #计划任务 事件调度器
     
215. # required unique id between 1 and 2^32 - 1
     
216. # defaults to 1 if master-host is not set
     
217. # but will not function as a master if omitted
     
218. server-id= 1                                 #
     
219. # Point the following paths to different dedicated disks
     
220. #tmpdir= /tmp/
     
221. #log-update = /path-to-dedicated-directory/hostname
     
222. # Uncomment the following if you are using InnoDB tables
     
223. #innodb_data_home_dir = /data/mysql-5.1.48/mysql-data/
     
224. #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
     
225. #innodb_log_group_home_dir = /data/mysql-5.1.48/mysql-data/
     
226. # You can set .._buffer_pool_size up to 50 - 80 %
     
227. # of RAM but beware of setting memory usage too high
     
228. innodb_file_format=barracuda
     
229. innodb_file_format_max=barracuda
     
230. innodb_file_per_table=1
     
231. innodb_fast_shutdown=0
     
232. innodb_buffer_pool_size = 180000M
     
233. innodb_buffer_pool_instances = 4
     
234. #innodb_additional_mem_pool_size = 20M
     
235. #innodb_use_sys_malloc = 20M
     
236. # Set .._log_file_size to 25 % of buffer pool size
     
237. innodb_log_file_size = 512M
     
238. #innodb_log_buffer_size = 8M
     
239. innodb_log_files_in_group = 3
     
240. innodb_flush_log_at_trx_commit = 2
     
241. innodb_lock_wait_timeout = 3
     
242. innodb_rollback_on_timeout = on
     
243. innodb_flush_method=O_DIRECT
     
244. transaction-isolation=READ-COMMITTED
     
245. innodb_thread_concurrency=0
     
246. innodb_io_capacity=800
     
247. innodb_purge_threads=1
     
248. innodb_open_files=65535
     
249. #innodb_stats_update_need_lock=0
     
250. #innodb_flush_neighbor_pages=0
     
251. #innodb_aio_pending_ios_per_thread=256
     
252. #for binlog_format=row
     
253. innodb_autoinc_lock_mode=2
     
254. #innodb_fast_checksum = 1
     
255. innodb_read_io_threads = 8
     
256. innodb_write_io_threads = 12
     
257. innodb_stats_on_metadata = 0
     
258. #使用线程池处理连接
     
259. thread_handling=pool-of-threads
     
260. thread_pool_oversubscribe=30
     
261. thread_pool_size=64
     
262. thread_pool_idle_timeout=7200
     
263. thread_pool_max_threads=2000
     
264. #查询优化器开关
     
265. #optimizer_switch='index_condition_pushdown=on'
     
266. #optimizer_switch='mrr=on'
     
267. #optimizer_switch='mrr_sort_keys=on'
     
268. #optimizer_switch='mrr_cost_based=off'
     
269. #mrr_buffer_size=32M
     
270. #optimizer_switch='join_cache_incremental=on'
     
271. #optimizer_switch='join_cache_hashed=on'
     
272. #optimizer_switch='join_cache_bka=on'
     
273. #join_cache_level=4
     
274. #join_buffer_size=32M
     
275. #join_buffer_space_limit=32M
     
276. [mysqldump]
     
277. quick
     
278. max_allowed_packet = 16M
     
279. [mysql]
     
280. no-auto-rehash
     
281. # Remove the next comment character if you are not familiar with SQL
     
282. #safe-updates
     
283. [myisamchk]
     
284. key_buffer_size = 256M
     
285. sort_buffer_size = 256M
     
286. read_buffer = 2M
     
287. write_buffer = 2M
     
288. [isamchk]
     
289. key_buffer_size = 256M
     
290. sort_buffer_size = 256M
     
291. read_buffer = 2M
     
292. write_buffer = 2M
     
293. [mysqlhotcopy]
     
294. interactive-timeout
     
295. 
     
296. 
     
297. 作者：若有所思11
     
298. 链接：https://www.jianshu.com/p/ac05107db343
     
299. 來源：简书
     
300. 简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。
     
301. “我们知道，世界上存在着已知的已知事物，也就是说有些事情我们知道自己知道，而我们也知道世上存在着被人所知的不明事物，这就是说有些事情我们知道自己不知道。同时，世上还存在着我们不知道的不明事物，也就是说我们不知道自己不知道。”

复制代码