MySQL MyISAM Optimization Settings drip

Let's start by saying something:

MySQL's InnoDB and MyISAM are the two most common table types used in MySQL, each with its pros and cons. The main difference between the two types is InnoDB Transactional and foreign key and row level locks are supported. MyISAM is not supported. So MyISAM tend to be considered only suitable for use in small projects.but from the database requirements point of view, the requirement of 99.9% stability, convenient scalability and high availability, the MyISAM should definitely be the first choice. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support. Most projects are read and write less, and MyISAM read performance is much stronger than InnoDB. Optimization settings: key_buffer_size– This is very important for the MyISAM table. If you use only the MyISAM table, you can set it to the 30-40% of available memory. But note that the amount of memory should be based on demand, not whatever machine, cut off half of the memory used as key_buffer_size. The reasonable value depends on the index size, the amount of data, and the load-remember, the MyISAM table caches the data using the operating system's cache. Therefore, we need to set aside some memory for them, in many cases the data peso is mostly. Can be viewed through the key_blocks_unused of show GLOBAL STATUS,as long as there is surplus, it means that key_buffer_size is useless. If you rarely use the MyISAM table, you also keep the key_buffer_size below 16-32MB to accommodate the temporary table index that is given to the disk.

Query_cache-This is useful if your application has a lot of read and no application-level caching. Do not set it too big, because it also requires a lot of overhead to maintain it, which can cause MySQL to become slower. Typically set to 32-512MB. It's a good idea to track a period of time to see if it's working well. Under a certain load pressure, if the cache hit rate is too low, enable it.

sort_buffer_size– If you have only a few simple queries, then you don't need to increase its value, even though you have 64GB of memory. Maybe it will degrade performance. Query_cache_size-query buffering is often used to buffer the results of a SELECT and no longer executes the direct return result at the next same query.Opening query buffering can greatly improve server speed if you have a large number of identical queries and rarely modify tables. Check the "qcache_lowmem_prunes" status variable to see if the current value is high enough for your load. Note: In situations where your table is constantly changing or if your query text is different every time, query buffering may cause performance degradation rather than performance gains. Note: Set Query_cache_limit, only the results less than this setting will be buffered, this setting is used to protect the query buffer,  Prevents a large result set from overwriting all other query results. Bulk_insert_buffer_size-myisam uses special tree-like caches to make burst insertions, (these inserts are, insert ...). SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE) are faster. This variable limits the number of bytes in the buffer tree per process.  Setting to 0 turns off this optimization. For optimization do not set this value greater than "key_buffer_size". This buffer is assigned when a burst insert is detected. Read_rnd_buffer_size-When a row is read from a sorted sequence after sorting, the row data is read from the buffer to prevent the disk from seeking a path. If you increase this value, you can improve the performance of many order by. Each thread is assigned when needed Thread_ Cache_size-How many threads we keep in the cache for reuse, and when a client disconnects, if the thread in the cache is less 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:For the daily IP 50-100W,PV 100-300w site, the hardware configuration for the Dell R710, dual Xeon E5620, 16G memory. CentOS 5.6 64-bit system, MySQL 5.5.x stable version of the partial database configuration file, for everyone reference Back_log = 300max_connections = 3000max_connect_errors = 30table_cache = 4096max_allowed_packet = 32M#external-locking# Skip-networkingbinlog_cache_size = 4mmax_heap_table_size = 128msort_buffer_size = 16mjoin_buffer_size = 16Mthread_ Cache_size = 16thread_concurrency = 8query_cache_size = 128mft_min_word_len = 8#memlockthread_stack = 512Ktransaction_ Isolation = Repeatable-readtmp_table_size = 128m#log_slave_updates#log#log_warningslog_slow_querieslong_query_time = 6log_long_format ....... .....

MySQL MyISAM Optimization Settings drip