MySQL MyISAM optimization Settings drip _mysql

Recently in the configuration MySQL server need to use some of the settings, after testing found a relatively good configuration scheme, the bright spot in the end AH

Let me start with a few questions:

InnoDB and MyISAM in MySQL are the most commonly used two table types in MySQL, each with its own pros and cons. The main difference between the two types is InnoDB Supports transaction processing with foreign key and row-level locks. And MyISAM does not support it. So MyISAM are often considered only suitable for use in small projects. But from a database requirements Point of view, requires 99.9% stability, convenient scalability and high availability, that MyISAM should definitely be the first choice. Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not provide transactional support. Most projects are read and write less items, and MyISAM read performance is much stronger than InnoDB.

Optimization settings:

key_buffer_size– This is very important for the MyISAM table. If you are using only the MyISAM table, you can set it to the 30-40% of available memory. But note that the amount of memory should be determined on demand, not on any machine, cutting off half of the memory for key_buffer_size. A reasonable value depends on the size of the index, the amount of data, and the load-remember that the MyISAM table caches data using the operating system's cache. So you need to set aside some of the memory for them, in many cases the data peso is much more. Key_blocks_unused can be viewed by show GLOBAL STATUS, as long as there is surplus, the key_buffer_size is not full. If you rarely use MyISAM tables, then keep key_buffer_size below 16-32MB to accommodate the temporary table indexes that are given to the disk.

Query_cache-This is useful if your application has a large number of reads, and there is no application-level caching. Don't set it too large, because it costs a lot to maintain it, which can cause MySQL to slow down. usually set to 32-512MB. After setting up, it's best to track for a while to see if it's working well. Under certain load pressure, if the cache hit rate is too low, enable it.

sort_buffer_size– If you only have a few simple queries, then you don't need to increase the value of it, although you have 64GB of RAM. Bad luck may degrade performance.

Query_cache_size-query buffering is often used to buffer the results of a SELECT and to no longer perform direct return results the next time the same query is made. Opening query buffering can greatly improve server speed if you have a large number of the same queries and rarely modify the table. View "qcache_ Lowmem_prunes the state variable to check if the current value is high enough for your load. Note: When your table changes frequently or if your query text is different each time, query buffering may cause performance degradation rather than performance improvement. Note: Set query_cache_ Limit, only results that are less than this setting are buffered, and this setting protects the query buffer against a large result set that overwrites all other query results.

Bulk_insert_buffer_size-myisam uses special similar tree cache to make burst inserts, (these inserts are, insert ...) SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE) are faster. This variable limits the number of bytes in the buffer tree in each process. Setting to 0 closes this optimization. Do not set this value greater than "key_buffer_size" for optimization. This buffer will be allocated when burst inserts are detected.

Read_rnd_buffer_size-When a row is read from a sorted sequence, the row data is read from the buffer to prevent disk seek. If you increase this value, you can improve the performance of many order by. When required by each thread

Thread_cache_size-How many threads we keep in cache for reuse, and when a client disconnects, if the cache has fewer threads than thread_cache_size, The client thread is placed in the cache. This can greatly reduce the overhead of thread creation when you need a large number of new connections

Appendix: For the site of the day IP 50-100W,PV 100-300w, for Dell R710, dual Xeon E5620, 16G memory hardware configuration. CentOS 5.6 64-bit system, MySQL 5.5.x stable version of some of the database configuration files for your reference

Copy Code code as follows:

Back_log = 300
Max_connections = 3000
Max_connect_errors = 30
Table_cache = 4096
Max_allowed_packet = 32M
#external-locking
#skip-networking
Binlog_cache_size = 4M
Max_heap_table_size = 128M
Sort_buffer_size = 16M
Join_buffer_size = 16M
Thread_cache_size = 16
Thread_concurrency = 8
Query_cache_size = 128M
Ft_min_word_len = 8
#memlock
Thread_stack = 512K
Transaction_isolation = Repeatable-read
Tmp_table_size = 128M
#log_slave_updates
#log
#log_warnings
Log_slow_queries
Long_query_time = 6
Log_long_format
................

Optimized configuration: After cloud-dwelling community test

1. The server's operating environment
Hardware server: Dell R710, dual Xeon E5620 CPU, 16G memory, 6*500g hard drive
Operating system: CentOS5.5 x86_64 System
MySQL version: MySQL 5.5.32
Apply to: Day IP 100-200w, solar PV 200-500w site
2. The specific optimization configuration is as follows

Copy Code code as follows:

[Client]
Port = 3306
Socket =/tmp/mysql.sock
Default-character-set = UTF8 #设置客户端的字符编码
[Mysqld]
# Generic configuration options
Port = 3306
Socket =/tmp/mysql.sock
#*** Char Set * * * * *
Character-set-server = UTF8 #设置服务器端的字符编码

#*** Network * * *
Back_log = 512
#skip-networking #默认没有开启
Max_connections = 3000
Max_connect_errors = 30
Table_open_cache = 4096
#external-locking #默认没有开启
Max_allowed_packet = 32M
Max_heap_table_size = 128M

# * * * Global Cache * * * *
Read_buffer_size = 8M
Read_rnd_buffer_size = 64M
Sort_buffer_size = 16M
Join_buffer_size = 16M

# * * * * thread * * *
Thread_cache_size = 16
Thread_concurrency = 8
Thread_stack = 512K

# * * * Query Cache * * * *
Query_cache_size = 128M
Query_cache_limit = 4M

# * * * * INDEX * * *
Ft_min_word_len = 8

#memlock #默认没有开启
Default-storage-engine = INNODB
Transaction_isolation = Repeatable-read

# * * * * TMP table * * *
Tmp_table_size = 64M

# * * * BIN Log * * * *
Log-bin=mysql-bin
Binlog_cache_size = 4M
Binlog_format=mixed
#log_slave_updates #默认没有开启
#log #默认没有开启, here is the query log, open can affect server performance
Log_warnings #开启警告日志

# * * * * SLOW Query log * * * *
Slow_query_log
Long_query_time = 10
# * * * * Replication Related Settings
Server-id = 1
#server-id = 2
#master-host = #master-user = <username>
#master-password = <password>
#master-port = <port>
#read_only
#*** MyISAM Specific Options
Key_buffer_size = 128M
Bulk_insert_buffer_size = 256M
Myisam_sort_buffer_size = 256M
Myisam_max_sort_file_size = 10G
Myisam_repair_threads = 1
Myisam_recover

# * * * * INNODB Specific Options * * * *
#skip-innodb #默认没有开启
Innodb_additional_mem_pool_size = 64M
Innodb_buffer_pool_size = 6G #注意在32位系统上你每个进程可能被限制在 2-3.5g User level memory limit, so do not set too high.
Innodb_data_file_path = Ibdata1:10m:autoextend
#innodb_data_home_dir = <directory>
Innodb_write_io_threads = 8
Innodb_read_io_threads = 8
#innodb_force_recovery =1
Innodb_thread_concurrency = 16
Innodb_flush_log_at_trx_commit = 2
#说明: Innodb_flush_log_at_trx_commit = 2 If it is a game server, it is recommended that this value be set to 2; if it is a highly demanding application for data security, the recommended setting is 1; set to 0 performance is highest, but if there is a failure, the data may be in danger of loss! The default value of 1 means that every instruction outside the transaction commit or transaction needs to write the log to the (flush) hard disk, which is time consuming. Especially when using battery-powered caching (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table, it means that the system cache is written without writing to the hard disk. The log still flush to the hard drive every second, so you won't normally lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security aspect is poor, even if MySQL hangs may lose the transaction data. A value of 2 only loses data when the entire operating system hangs.
#innodb_fast_shutdown
Innodb_log_buffer_size = 16M
Innodb_log_file_size = 512M
Innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method =o_dsync
Innodb_lock_wait_timeout = 120
[Mysqldump]
Quick
Max_allowed_packet = 32M
[MySQL]
No-auto-rehash
[Myisamchk]
Key_buffer_size = 2048M
Sort_buffer_size = 2048M
Read_buffer = 32M
Write_buffer = 32M
[Mysqlhotcopy]
Interactive-timeout
[Mysqld_safe]
Open-files-limit = 10240

Okay, here's some personal experience with the MySQL MyISAM optimization setup, thanks to these people for sharing. I hope you will support the cloud-dwelling community in the future.