MySQL Optimized performance my.cnf detailed

Provides a MySQL 5.6 version suitable for MY.CNF profiles on 1GB memory VPS (click here to download the file):

2. [Client]
4. port=3306
6. Socket=/tmp/mysql.sock
10. [Mysqld]
12. port=3306
14. Socket=/tmp/mysql.sock
18. Basedir=/usr/local/mysql
20. Datadir=/data/mysql
22. Pid-file=/data/mysql/mysql.pid
24. User=mysql
26. bind-address=0.0.0.0
28. server-id=1# means the serial number of the machine is 1, generally speaking is the meaning of master
32. Skip-name-resolve
34. #禁止MySQL对外部连接进行DNS解析, this option can be used to eliminate the time for DNS resolution for MySQL. But be aware that if you turn on this option,
36. #则所有远程主机连接授权都要使用IP地址方式, otherwise MySQL will not be able to handle connection requests properly
40. #skip-networking
44. back_log=600
46. #MySQL能有的连接数量. This works when the primary MySQL thread gets very many connection requests in a very short period of time,
48. The #然后主线程花些时间 (albeit very short) checks the connection and starts a new thread. The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request.
50. #如果期望在一个短时间内有很多连接, you need to add it. That is, if MySQL's connection data reaches Max_connections, the new request will be present in the stack,
52. #以等待某一连接释放资源, the number of the stack is Back_log, and if the number of pending connections exceeds Back_log, the connection resource will not be granted.
54. #另外, this value (Back_log) is limited to the size of your operating system's listening queue for incoming TCP/IP connections.
56. #你的操作系统在这个队列大小上有它自己的限制 (You can check your OS documentation to find the maximum value of this variable), trying to set the limit of Back_log above your operating system will be invalid.
60. max_connections=1000
62. #MySQL的最大连接数, if the server has a large number of concurrent connection requests, it is recommended that this value be increased to increase the amount of concurrent connections, although this is based on the fact that the machine can support, because if the number of connections between MySQL will provide a connection buffer for each connection, it will cost more memory, Therefore, to adjust the value appropriately, you can not blindly increase the value set. The ' conn% ' wildcard character can be used to view the number of connections in the current state to decide the size of the value.
66. max_connect_errors=6000
68. #对于同一主机, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. If you need to unblock the host, execute: flushhost.
72. open_files_limit=65535
74. #MySQL打开的文件描述符限制, default minimum 1024; When Open_files_limit is not configured, compare the values of max_connections*5 and ulimit-n, whichever is larger,
76. #当open_file_limit被配置的时候, compare the values of open_files_limit and max_connections*5, whichever is larger.
80. table_open_cache=128
82. #MySQL每打开一个表, some data is read into the Table_open_cache cache, and when MySQL does not find the appropriate information in this cache, it is read on the disk. Default Value 64
84. #假定系统有200个并发连接, you need to set this parameter to 200*n (N is the number of file descriptors required for each connection);
86. #当把table_open_cache设置为很大时, if the system does not handle so many file descriptors, then the client fails, the connection is not
90. max_allowed_packet=4m
92. #接受的数据包大小; it is safe to increase the value of this variable because additional memory is allocated only when needed. For example, MYSQLD will allocate more memory only if you issue a long query or if mysqld must return a large result row.
94. #该变量之所以取较小默认值是一种预防措施 to capture error packets between the client and server and to ensure that memory overflows are not caused by accidental use of large packets.
98. binlog_cache_size=1m
100. #一个事务, when there is no commit, the log that is generated is logged to the cache, and the log is persisted to disk when the transaction submission needs to be committed. Default Binlog_cache_size size 32K
104. Max_heap_table_size=8m
106. The size of the #定义了用户可以创建的内存表 (memorytable). This value is used to calculate the maximum row value for the memory table. This variable supports dynamic change
110. tmp_table_size=16m
112. #MySQL的heap (stacked) Table buffer size. All unions are completed within a DML instruction, and most unions can be done without a temporary table.
114. #大多数临时表是基于内存的 (HEAP) table. Temporary tables with large record lengths (the and the length of all columns) or tables containing BLOB columns are stored on the hard disk.
116. #如果某个内部heap (stacked) table size exceeds Tmp_table_size,mysql You can automatically change the in-memory heap table to a hard disk-based MyISAM table as needed. You can also increase the size of the staging table by setting the Tmp_table_size option. In other words, if the value is raised, MySQL will increase the size of the heap, which can improve the speed of the join query.
120. Read_buffer_size=2m
122. #MySQL读入缓冲区大小. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer.
124. #如果对表的顺序扫描请求非常频繁, and you think the frequent scan is going too slow, you can improve its performance by increasing the value of the variable and the size of the memory buffer
128. Read_rnd_buffer_size=8m
130. #MySQL的随机读缓冲区大小. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query,
132. #MySQL会首先扫描一遍该缓冲 to avoid disk searches, improve query speed, and if you need to sort large amounts of data, you can adjust the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriate to avoid excessive memory overhead
136. Sort_buffer_size=8m
138. #MySQL执行排序使用的缓冲大小. If you want to increase the speed of the order, first see if you can let MySQL use the index instead of the extra sort stage.
140. #如果不能, you can try increasing the size of the sort_buffer_size variable
144. Join_buffer_size=8m
146. #联合查询操作所能使用的缓冲区大小, as with Sort_buffer_size, the allocated memory for this parameter is also per-connection exclusive
150. Thread_cache_size=8
152. The #这个值 (default 8) indicates that the number of threads saved in the cache can be re-used, and if there is room in the cache when disconnected, the client's thread is placed in the cache.
154. #如果线程重新被请求, then the request will be read from the cache, and if the cache is empty or a new request, then the thread will be recreated, if there are many new threads,
156. #增加这个值可以改善系统性能. By comparing the variables of the connections and threads_created states, you can see the effect of this variable. (–> indicates the value to adjust)
158. #根据物理内存设置规则如下:
160. #1G->8
162. #2G->16
164. #3G->32
166. #大于3G->64
170. Query_cache_size=8m
172. #MySQL的查询缓冲大小 (starting with 4.0.1, MySQL provides a query buffering mechanism) using query buffering, MySQL stores the SELECT statement and query results in a buffer,
174. #今后对于同样的SELECT语句 (case-sensitive), the result is read directly from the buffer. Depending on the MySQL user manual, the use of query buffering can be up to 238% efficient.
176. #通过检查状态值 ' qcache_% ', you can tell if the Query_cache_size setting is reasonable: If the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering,
178. #如果Qcache_hits的值也非常大, the query buffer is used very frequently, and you need to increase the buffer size; If the value of qcache_hits is not large, your query repetition rate is very low.
180. #这种情况下使用查询缓冲反而会影响效率, you can consider buffering without querying. Additionally, adding Sql_no_cache in the SELECT statement can make it clear that the query buffer is not used
184. Query_cache_limit=2m
186. #指定单个查询能够使用的缓冲区大小, default 1M
190. key_buffer_size=4m
192. #指定用于索引的缓冲区大小, increase it to get better processing index (for all read and multiple writes), to the extent that you can afford it. If you make it too big,
194. #系统将开始换页并且真的变慢了. The parameter can be set to 384M or 512M for a server that has around 4GB. By checking the status values key_read_requests and Key_reads,
196. #可以知道key_buffer_size设置是否合理. The proportional key_reads/key_read_requests should be as low as possible,
198. #至少是1:100,1:1000 is better (the above status values can be obtained using showstatuslike ' key_read% '). Note: This parameter value setting is too large to be the overall efficiency of the server is reduced
202. Ft_min_word_len=4
204. #分词词汇最小长度, default 4
208. Transaction_isolation=repeatable-read
210. #MySQL支持4种事务隔离级别, they were:
212. #READ-uncommitted,read-committed,repeatable-read,serializable.
214. #如没有指定, MySQL defaults to repeatable-read,oracle default is read-committed
218. Log_bin=mysql-bin
220. Binlog_format=mixed
222. expire_logs_days=30# more than 30 days of binlog Delete
226. log_error=/data/mysql/mysql-error.log# Error log path
228. Slow_query_log=1
230. long_query_time=1# slow query time more than 1 seconds is slow query
232. Slow_query_log_file=/data/mysql/mysql-slow.log
236. Performance_schema=0
238. Explicit_defaults_for_timestamp
242. #lower_case_table_names =1# is case insensitive
246. Skip-external-locking#mysql option to avoid external locking. This option is turned on by default
250. default-storage-engine=innodb# Default storage Engine
254. Innodb_file_per_table=1
256. #InnoDB为独立表空间模式, each table in each database generates a data space
258. #独立表空间优点:
260. #1. Each table has a self-contained table space.
262. #2. The data and indexes for each table will exist in the table space themselves.
264. #3. You can implement a single table to move through different databases.
266. #4. Space can be recycled (except droptable operation, the table is empty to recover)
268. #缺点:
270. #单表增加过大, such as more than 100G
272. #结论:
274. #共享表空间在Insert操作上少有优势. Others do not have a separate table space to perform well. When enabling a stand-alone tablespace, make reasonable adjustments: Innodb_open_files
278. innodb_open_files=500
280. #限制Innodb能打开的表的数据, if the tables in the library are particularly numerous, please add this. This value is 300 by default.
284. innodb_buffer_pool_size=64m
286. #InnoDB使用一个缓冲池来保存索引和原始数据, not like MyISAM.
288. #这里你设置越大, the less disk I/O you need to access the data in the table.
290. #在一个独立使用的数据库服务器上, you can set this variable to 80% of the server's physical memory size
292. #不要设置过大, otherwise, the competition for physical memory can cause a page break in the operating system.
294. #注意在32位系统上你每个进程可能被限制在2 -3.5g User-level memory limits,
296. #所以不要设置的太高.
300. Innodb_write_io_threads=4
302. Innodb_read_io_threads=4
304. #innodb使用后台线程处理数据页上的读写I/O (input and output) request, change according to your CPU core number, default is 4
306. #注: These two parameters do not support dynamic changes, you need to add this parameter to the MY.CNF, after modification, restart the MySQL service, the allowable value range from 1-64
310. Innodb_thread_concurrency=0
312. #默认设置为0, which means no limit on concurrency, it is recommended to set to 0, better to play CPU multi-core processing capacity, increase concurrency
316. Innodb_purge_threads=1
318. #InnoDB中的清除操作是一类定期回收无用数据的操作. In previous releases, the purge operation was part of the main thread, which meant that it could clog other database operations at runtime.
320. #从MySQL5. Version 5.X, this operation runs on a separate thread and supports more concurrent numbers. The user can set the innodb_purge_threads configuration parameter to select whether the purge operation uses a single
322. #独线程, the parameter is set to 0 by default (not using a separate thread), and setting to 1 means using a separate purge thread. Recommended for 1
326. innodb_flush_log_at_trx_commit=2
328. #0: If the value of Innodb_flush_log_at_trx_commit is 0,logbuffer per second, the log file will be brushed to disk, and no action is taken when committing the transaction (execution is performed by the MySQL masterthread thread.
330. #主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件 (Redolog). Whether the transaction has been committed or not, the default log file is Ib_logfile0,ib_logfile1
332. #1: When set to the default value of 1, the Logbuffer brush is written to the log each time a transaction is committed.
334. #2: If set to 2, each commit transaction will write a log, but does not perform the brush operation. The log file is brushed on a per-second schedule. Note that there is no guarantee that 100% per second will be brushed to disk, depending on the scheduling of the process.
336. #每次事务提交的时候将数据写入事务日志, and here the write is only called the file system write operation, and the file system is cached, so this write does not guarantee that the data has been written to the physical disk
338. #默认值1是为了保证完整的ACID. Of course, you can set this configuration to a value other than 1 in exchange for higher performance, but you will lose 1 seconds of data when the system crashes.
340. #设为0的话, when the mysqld process crashes, it loses the last 1 seconds of the transaction. Set to 2, the last 1 seconds of data will be lost only if the operating system crashes or loses power. InnoDB will ignore this value when doing a recovery.
342. #总结
344. #设为1当然是最安全的, but the performance page is the worst (as opposed to the other two parameters, but not unacceptable). If the data consistency and integrity requirements are not high, can be set to 2, if only for performance, such as high concurrent write log server, set to zero for higher performance
348. Innodb_log_buffer_size=2m
350. #此参数确定些日志文件所用的内存大小, in M. Larger buffers can improve performance, but unexpected failures will cause data loss. MySQL developer recommends setting the 1-8m between
354. innodb_log_file_size=32m
356. #此参数确定数据日志文件的大小, larger settings can improve performance, but also increase the time it takes to recover a failed database
360. Innodb_log_files_in_group=3
362. #为提高性能, MySQL can write log files to multiple files in a circular fashion. Recommended setting is 3
366. Innodb_max_dirty_pages_pct=90
368. #innodb主线程刷新缓存池中的数据 to make the dirty data scale less than 90%
372. innodb_lock_wait_timeout=120
374. #InnoDB事务在被回滚之前可以等待一个锁定的超时秒数. InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB uses the Locktables statement to notice the lock setting. The default value is 50 seconds
378. Bulk_insert_buffer_size=8m
380. #批量插入缓存大小, this parameter is for the MyISAM storage engine. For increased efficiency when inserting 100-1000+ records at once. The default value is 8M. Can be doubled to the size of the data volume.
384. Myisam_sort_buffer_size=8m
386. #MyISAM设置恢复表之时使用的缓冲区的尺寸, when you sort MyISAM an index-allocated buffer in repairtable or by using CreateIndex to create an index or altertable process
390. myisam_max_sort_file_size=10g
392. #如果临时文件会变得超过索引, do not use the Quick Sort index method to create an index. Note: This parameter is given as a byte
396. Myisam_repair_threads=1
398. #如果该值大于1, MyISAM table indexes are created in parallel in the repairbysorting process (each index is in its own line range)
402. interactive_timeout=28800
404. #服务器关闭交互式连接前等待活动的秒数. The interactive client is defined as a client that uses the Client_interactive option in Mysql_real_connect (). Default value: 28,800 seconds (8 hours)
408. wait_timeout=28800
410. #服务器关闭非交互连接之前等待活动的秒数. Initializes the session Wait_timeout value based on the global Wait_timeout value or global interactive_timeout value when the thread starts,
412. #取决于客户端类型 (defined by the connection option Client_interactive Mysql_real_connect ()). Parameter default value: 28,800 seconds (8 hours)
414. #MySQL服务器所支持的最大连接数是有上限的, because the build of each connection consumes memory, so we want the client to be connected to MySQLServer after the corresponding operation is processed,
416. #应该断开连接并释放占用的内存. If your mysqlserver have a large number of idle connections, they will not only consume memory in vain, but if the connection keeps accumulating,
418. #最终肯定会达到MySQLServer的连接上限数, this will report the ' Toomanyconnections ' error. For the value setting of wait_timeout, it should be judged according to the operation condition of the system.
420. #在系统运行一段时间后, the connection status of the current system can be viewed through the showprocesslist command, if a connection process with a large number of sleep states is found, the parameter setting is too large,
422. #可以进行适当的调整小些. To set Interactive_timeout and wait_timeout at the same time will not take effect.
426. [Mysqldump]
428. Quick
430. max_allowed_packet=16m# Maximum packet length sent and accepted by the server
434. [Myisamchk]
436. Key_buffer_size=8m
438. Sort_buffer_size=8m
440. read_buffer=4m
442. write_buffer=4m

More information: http://www.wangzhanjianshegs.com/

MySQL Optimized performance my.cnf detailed