MySQL MyISAM optimized setup bit by bit

Recently in the configuration of mysql server needs to use some Settings, after testing found a good configuration scheme, the highlight in the last ah

First, a few questions:

InnoDB and MyISAM in Mysql are the two most commonly used table types in MySQL, each with advantages and disadvantages. The main difference between the two types is that InnoDB supports transaction processing with foreign keys and row-level locking. MyISAM does not support it. So it's easy to think of Myisam as only suitable for small projects. But in terms of database requirements, which require 99.9% stability, easy scalability, and high availability, MyISAM is definitely the first choice. The MyISAM type of table emphasizes performance and can perform several times faster than the InnoDB type, but does not provide transaction support. Most projects are read-heavy projects, and Myisam's read performance is much better than innodb.

Optimized Settings:

This is very important for MyISAM table key_buffer_size �. If you are just using the MyISAM table, you can set it to 30-40% of the available memory. Note, however, that the amount of memory allocated should be determined according to the requirements, rather than using 1 half of the memory for key_buffer_size regardless of the machine. Reasonable values depend on index size, data volume, and load - remember that MyISAM tables use the operating system cache to cache data, so you need to set aside some memory for them, and in many cases the data is much larger than the index. ES34en_ES35en_ES36en (SHOW, GLOBAL, STATUS) If you rarely use MyISAM tables, keep key_buffer_size lower than 16-32MB to accommodate the need to give temporary table indexes to disk.

query_cache - This is useful if your application has a lot of reads and does not have application-level caching. Don't make it too large, as it is expensive to maintain, which can slow MySQL down. Normally set to 32-512Mb. It's a good idea to keep track of it for a while after you've set it up to see if it works. Enable it if the cache hit ratio is too low for a certain load.

sort_buffer_size � if you only have 1 simple query, then you need to increase its value, even though you have 64 GB memory. It might degrade performance.

query_cache_size - The query buffer is often used to buffer the results of SELECT and does not return the results directly the next time the same query is executed. Turning on the query buffer can greatly speed up the server if you have a large number of identical queries and rarely modify the tables. Check the "Qcache_lowmem_prunes" status variable to see if the current value is high enough for your load. Note: In cases where your table changes frequently or if your query text is different every time, query buffering may cause performance degradation rather than performance improvement. Note: query_cache_limit is set. Only results less than this value will be buffered. This setting is used to protect the query buffer from one very large result set overwriting all other query results.

bulk_insert_buffer_size -MyISAM uses special tree-like cache to enable burst inserts. (These inserts are,INSERT... SELECT INSERT... VALUES (...). , (...). ,... , and LOAD DATA INFILE). This variable limits the number of bytes of the buffer tree per process. Setting to 0 turns this optimization off. Do not set this value greater than "key_buffer_size" for optimization. This buffer is allocated when a burst insert is detected.

read_rnd_buffer_size - When rows are read from an already sorted sequence after sorting, the row data will be read from this buffer to prevent disk seeking. If you increase this value, you can improve the performance of ORDER and BY a lot. Assigned by each thread as needed

thread_cache_size - How many threads do we keep in cache for reuse, when 1 client disconnects, if there are fewer threads in cache than thread_cache_size, then client threads are put in cache. This can greatly reduce the overhead of thread creation when you need a lot of new connections

Appendix: For IP 50-100w, PV 100-300w sites, for Dell R710, double XE E5620, 16G memory hardware configuration. CentOS 5.6 64-bit system, MySQL 5.5.x stable version of part of the database configuration file, for your reference

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: Tested at this site

1. Running environment of the server
Hardware server: Dell R710, Dual XE E5620 CPU, 16G memory, 6*500G hard disk
Operating system: CentOS5.5 X86_64 system
Mysql version: MySQL 5.5.32
Suitable for: day IP 100-200W, day PV 200-500W sites
2. The specific optimization configuration is as follows

[client] 

port    = 3306 

socket    = /tmp/mysql.sock

default-character-set = utf8 # Sets the character encoding of the client 

[mysqld]

# generic configuration options 

port    = 3306 

socket    = /tmp/mysql.sock

#*** char set ***

character-set-server = utf8 # Sets the character encoding on the server side 

             

#*** network ***

back_log = 512

#skip-networking # It's not on by default 

max_connections = 3000

max_connect_errors = 30

table_open_cache = 4096

#external-locking # It's not on by default 

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 # It's not on by default 

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 # It's not on by default 

#log # This is not enabled by default. This is the query log. This will affect server performance 

log_warnings # Open the warning log 

             

# *** slow query log ***

slow_query_log

long_query_time = 10

# *** Replication related settings

server-id = 1

#server-id = 2 

#master-host = <hostname> 

#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 # It's not on by default 

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size = 6G # Note that in 32 On a bit system each of your processes may be limited to  2-3.5G  User level memory limits ,  So don't set it 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

# Description: 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 an application with high data security requirements, the recommended setting is 1 ; Set to 0 Highest performance, but there is a risk of data loss if something goes wrong! The default value 1 "Means every 1 Subtransaction commits or out-of-transaction instructions require the log to be written ( flush ) Hard disk, which is time consuming. In particular, use a battery-powered cache ( Battery backed up cache ). set 2 For many applications, especially from MyISAM The table is not written to the disk but to the system cache. The logs will still be logged every second flush Go to the hard drive, so you 1 Like will not lose more than 1-2 Updates in seconds. set 0 Will be faster 1 Dot, but the security aspect is relatively poor, even if MySQL A hang can also lose transaction data. The value of 2 Data loss is only possible when the entire operating system is down. 

#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