MySQL MyISAM optimization settings, mysqlmyisam

MySQL MyISAM optimization settings, mysqlmyisam

I recently used some settings for configuring the mysql server. After testing, I found a good configuration solution. The highlights are at the end.

Let's talk about one question:
 
InnoDB and MyISAM in Mysql are the two most common table types in MySQL, each having its own advantages and disadvantages. the main difference between the two types is that InnoDB supports transaction processing and Foreign keys and row-level locks. myISAM does not support. therefore, Myisam is often considered only suitable for small projects. However, from the perspective of database requirements, MyISAM should be the first choice if it requires 99.9% stability and convenient scalability and high availability. MyISAM tables emphasize performance. The execution speed is faster than that of InnoDB tables, but transactions are not supported. most projects have fewer reads and writes, while Myisam has better read performance than innodb.
 
Optimization settings:

Key_buffer_size-this is very important for the MyISAM table. If you only use the MyISAM table, you can set it to 30-40% of the available memory. However, note that the amount of memory should be determined based on your needs, instead of cutting off half of the memory for key_buffer_size. A reasonable value depends on the index size, data volume, and load. Remember, the MyISAM Table uses the operating system cache to cache data. Therefore, you need to leave some memory for them, in many cases, the data is much larger than the index. You can view the Key_blocks_unused in show global status. If there is any surplus, the key_buffer_size is not full. If you seldom use the MyISAM table, keep the key_buffer_size smaller than 16-32 MB to meet the requirements for temporary table indexes on the disk.
 
Query_cache-this is useful if your application has a large number of reads without application-level caches. Don't set it too large, because it also requires a lot of overhead to maintain it, which causes MySQL to slow down. Usually set to 32-512 Mb. After setting, it is best to track for a period of time to check whether the operation is good. If the cache hit rate is too low under a certain load, enable it.

Sort_buffer_size-if you only have some simple queries, you don't need to increase its value, even though you have 64 GB memory. Poor performance may be reduced.
 
Query_cache_size-the Query Buffer is often used to buffer SELECT results and directly return results when the same query is executed the next time. enabling the Query Buffer can greatly improve the server speed. If you have a large number of identical queries and rarely modify tables. check the "Qcache_lowmem_prunes" status variable to check whether the current value is high enough for your load. note: When your table changes frequently or if the original query is different each time, the query buffer may cause performance degradation rather than performance improvement. note: When query_cache_limit is set, only the results smaller than this value are buffered. This setting is used to protect the Query Buffer and prevent a large result set from overwriting all other query results.

Bulk_insert_buffer_size-MyISAM uses a special tree-like cache to enable burst insertion. (These inserts are, INSERT... SELECT, INSERT... VALUES (...), (...), ..., And load data infile) faster. this variable limits the number of bytes of the buffer tree in each process. setting 0 will disable this optimization. for optimization, do not set this value to greater than "key_buffer_size ". 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 statements. allocated by each thread when necessary

Thread_cache_size-the number of threads reserved in the cache for reuse. When a client is disconnected and the number of threads in the cache is smaller than thread_cache_size, the client thread is put into the cache. this can greatly reduce the thread creation overhead when you need a large number of new connections.

Appendix: Applicable to websites with daily IP addresses of 50-100 w and PV of-w, and hardware configurations for Dell R710, dual Xeon E5620, and 16 GB memory. CentOS 5.6 64-bit system, MySQL 5.5.x stable version of some database configuration files for your reference

Copy codeThe Code is as follows:
Back_log = 300
Max_connections = 3000
Max_connect_errors = 30
Table_cache = 4096
Max_allowed_packet = 32 M
# External-locking
# Skip-networking
Binlog_cache_size = 4 M
Max_heap_table_size = 128 M
Sort_buffer_size = 16 M
Join_buffer_size = 16 M
Thread_cache_size = 16
Thread_concurrency = 8
Query_cache_size = 128 M
Ft_min_word_len = 8
# Memlock
Thread_stack = 512 K
Transaction_isolation = REPEATABLE-READ
Tmp_table_size = 128 M
# Log_slave_updates
# Log
# Log_warnings
Log_slow_queries
Long_query_time = 6
Log_long_format
................

Optimized Configuration: tested by the customer center

1. server running environment
Hardware server: Dell R710, dual Xeon E5620 CPU, 16 GB memory, 6 * GB hard drive
Operating System: CentOS5.5 X86_64 System
Mysql version: MySQL 5.5.32
Applicable to: daily IP address 100-200 W, daily PV 200-500 W
2. The specific Optimization Configuration is as follows:
Copy codeThe Code is as follows:
[Client]
Port = 3306
Socket =/tmp/mysql. sock
Default-character-set = utf8 # set the character encoding of the Client
[Mysqld]
# Generic configuration options
Port = 3306
Socket =/tmp/mysql. sock
# *** Char set ***
Character-set-server = utf8 # set the character encoding on the server

# *** Network ***
Back_log = 512
# Skip-networking # disabled by default
Max_connections = 3000
Max_connect_errors = 30
Table_open_cache = 4096
# External-locking # disabled by default
Max_allowed_packet = 32 M
Max_heap_table_size = 128 M

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

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

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

# *** Index ***
Ft_min_word_len = 8

# Memlock # disabled by default
Default-storage-engine = INNODB
Transaction_isolation = REPEATABLE-READ

# *** Tmp table ***
Tmp_table_size = 64 M

# *** Bin log ***
Log-bin = mysql-bin
Binlog_cache_size = 4 M
Binlog_format = mixed
# Log_slave_updates # disabled by default
# Log query is not enabled by default. Enabling log query affects server performance.
Log_warnings # enable warning logs

# *** 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 = 128 M
Bulk_insert_buffer_size = 256 M
Myisam_sort_buffer_size = 256 M
Myisam_max_sort_file_size = 10G
Myisam_repair_threads = 1
Myisam_recover

# *** INNODB Specific options ***
# Skip-innodb # disabled by default
Innodb_additional_mem_pool_size = 64 M
Innodb_buffer_pool_size = 6G # note that on a 32-bit system, each process may be limited to 2-3.5G user-level memory, so do not set it too high.
Innodb_data_file_path = ibdata1: 10 M: 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
# Note: innodb_flush_log_at_trx_commit = 2 if it is a game server, we recommend that you set this value to 2. If it is an application with extremely high data security requirements, we recommend that you set it to 1 and set it to 0 with the highest performance, however, if a fault occurs, data may be lost! The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. This is time-consuming. Especially when Battery backed up cache is used. Set to 2 is applicable to many applications, especially the conversion from the MyISAM table. It means writing data to the system cache instead of writing data to the hard disk. Logs are flushed to the hard disk every second, so you will not lose updates that exceed 1-2 seconds. Setting 0 is faster, but the security is poor. Even if MySQL fails, the transaction data may be lost. Value 2 can only lose data when the entire operating system is down.
# Innodb_fast_shutdown
Innodb_log_buffer_size = 16 M
Innodb_log_file_size = 512 M
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 = 32 M
[Mysql]
No-auto-rehash
[Myisamchk]
Key_buffer_size = 2048 M
Sort_buffer_size = 2048 M
Read_buffer = 32 M
Write_buffer = 32 M
[Mysqlhotcopy]
Interactive-timeout
[Mysqld_safe]
Open-files-limit = 10240

Well, the above is some personal experience in MySQL MyISAM optimization settings. Thank you for sharing these experiences. We hope that you will be able to support more customers in the future.