At the same time, the online access volume continues to increase. For servers with 1 GB of memory, the server even crashes every day or gets stuck from time to time. This problem has plagued me for using MySQL for more than half a month. scalable algorithms, therefore, you can usually run with a small amount of memory or store MySQL more for better performance.
After installing mysql, the preparation file should be in the/usr/local/mysql/share/mysql directory, the preparation file has a few, there are my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf, websites with different traffic and server environments with different configurations must have different configuration files.
Under normal circumstances, the preparation of the my-medium.cnf file can meet most of our needs; general we will copy the configuration file to/etc/my. cnf only needs to modify this configuration file. You can use mysqladmin variables extended-status-u root-p to see the current parameters. Three configuration parameters are the most important, that is, key_buffer_size, query_cache_size, and table_cache.
Key_buffer_size only applies to the MyISAM table,
Key_buffer_size specifies the size of the index buffer, which determines the index processing speed, especially the index reading speed. Generally, we set the value to 16 M. In fact, the number of sites that are slightly larger is far from enough. By checking the status values Key_read_requests and Key_reads, we can check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above STATUS values can be obtained using show status like 'key _ read % ). Or if you have installed phpmyadmin, you can see it through the server running status. I recommend you use phpmyadmin to manage mysql. The following status values are all my instance analysis obtained through phpmyadmin:
This server has been running for 20 days
Key _buffer_size-128 M
Key_read_requests-650759289
Key_reads-79112 ratio close to 1: 8000 healthy
Another way to estimate the key_buffer_size is to add up the size of the index space of each table in your website database. Take this server as an example: the number of large table indexes is about 125 MB, which will increase as the table grows.
MySQL provides a query buffer mechanism starting from 4.0.1. Using the Query Buffer, MySQL stores the SELECT statement and query result in the buffer. In the future, the same SELECT statement (case sensitive) will be read directly from the buffer. According to the MySQL user manual, query buffering can achieve a maximum efficiency of 238%.
You can check whether query_cache_size is set properly by adjusting the following parameters.
Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
When the Qcache total blocksQcache_lowmem_prunes value is very large, it indicates that the buffer is insufficient frequently. When the Qcache_hits value is very large, it indicates that the query buffer is frequently used, in this case, you need to increase the buffer size Qcache_hits value is not big, it indicates that your query repetition rate is very low. In this case, using the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that no Query Buffer is used.
Qcache_free_blocks. If this value is very large, it indicates that many fragments in the buffer zone query_cache_type specify whether to use the query buffer.
My settings:
Query_cache_size = 32 M
Query_cache_type = 1 to get the following status values:
Qcache queries in cache 12737 indicates the number of items currently cached
Qcache inserts 20649006
Qcache hits 79060095 seems that the repeat query rate is quite high
Qcache lowmem prunes 617913 has so many times that the cache is too low
Qcache not cached 189896
Qcache free memory 18573912
Qcache free blocks 5328 seems to be a little big.
Qcache total blocks 30953 if the memory allows 32 MB, it should be added up.
Table_cache specifies the table cache size. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values Open_tables and Opened_tables of the peak time to determine whether to increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding STATUS values can be obtained using show status like 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.
For machines with 1 GB memory, the recommended value is 128-256.
I set table_cache = 256
The following status is displayed:
Opening tables 256
Opened tables 9046 although open_tables is already equal to table_cache, the value of opened_tables is also very low when it has been running for 20 days compared to the server running time. Therefore, increasing the value of table_cache should be of little use. If the preceding value appears after six hours, you need to increase the value of table_cache.
If you do not need to record binary logs, you can disable this function. After you disable it, you cannot recover the data before the problem occurs. You need to manually back up the data, the binary log contains all the statements for updating data. It is used to restore the data to the final state as much as possible when restoring the database. In addition, if you perform Replication, you also need to use binary logs to transmit modifications.
Log_bin specifies the log file. If no file name is provided, MySQL generates its own default file name. MySQL automatically adds a numeric reference after the file name. A new binary file is generated every time the service is started. In addition, you can use log-bin-index to specify the index file, binlog-do-db to specify the database for the record, and binlog-ignore-db to specify a database without record. Note: binlog-do-db and binlog-ignore-db specify only one database at a time and multiple statements are required for multiple databases. In addition, MySQL will change all database names to lowercase letters, and all database names must be in lower case when specifying the database, otherwise it will not work.
To disable this function, you only need to add the # sign before it.
# Log-bin enable the slow query log (slow query log), which is very useful for queries with tracing problems. It records all long_query_time queries. If needed, you can also record records that do not use indexes. The following is an example of slow log query:
To enable slow query logs, you must set the log_slow_queries, long_query_times, and log-queries-not-using-indexes parameters.
Log_slow_queries specifies the log file. If no file name is provided, MySQL generates its own default file name. Long_query_times specifies the threshold for slow queries. The default value is 10 seconds. Log-queries-not-using-indexes is a parameter introduced after 4.1.0. It indicates that the record does not use an index for queries. The author sets long_query_time = 10
Settings:
Sort_buffer_size = 1 M
Max_connections = 120
Wait_timeout = 120
Back_log = 100
Read_buffer_size = 1 M
Thread_cache = 32
Interactive_timeout = 120
Thread_concurrency = 4 parameter description:
Back_log
The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread. The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. The Unix listen (2) System Call manual page should have more details. Check your OS document to find the maximum value of this variable. Trying to set back_log to be higher than your operating system limit will be invalid.
Max_connections
The maximum number of concurrent connections. If 120 exceeds this value, it will be automatically restored. If a problem occurs, it can be automatically solved.
Thread_cache
No specific instructions are found, but it is useful to create more than 400 threads 20 days after 32, and thousands of threads have been created one day before.
Thread_concurrency
# Set your cpu count x2. For example, if there is only one cpu, then thread_concurrency = 2
# With 2 CPUs, thread_concurrency = 4
Skip-innodb
# Remove the innodb support code:
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32 M-64 M) where MySQL plays
# An important part, or systems up to 128 M where MySQL is used together
# Other programs (such as a web server)
#
# You can copy this file
#/Etc/my. cnf to set global options,
# Mysql-data-dir/my. cnf to set server-specific options (in this
# Installation this directory is/var/lib/mysql) or
#~ /. My. cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# With the "-- help" option.
# The following options will be passed to all MySQL clients
[Client]
# Password = your_password
Port = 3306
Socket =/tmp/mysql. sock
# Socket =/var/lib/mysql. sock
# Here follows entries for some specific programs
# The MySQL server
[Mysqld]
Port = 3306
Socket =/tmp/mysql. sock
# Socket =/var/lib/mysql. sock
Skip-locking
Key_buffer = 128 M
Max_allowed_packet = 1 M
Table_cache = 256
Sort_buffer_size = 1 M
Net_buffer_length = 16 K
Myisam_sort_buffer_size = 1 M
Max_connections = 120
# Addnew config
Wait_timeout = 120
Back_log = 100
Read_buffer_size = 1 M
Thread_cache = 32
Skip-innodb
Skip-bdb
Skip-name-resolve
Join_buffer_size = 512 k
Query_cache_size = 32 M
Interactive_timeout = 120
Long_query_time = 10
Log_slow_queries =/usr/local/mysql4/logs/slow_query.log
Query_cache_type = 1
# Try number of CPU's * 2 for thread_concurrency
Thread_concurrency = 4
# End new config
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# If all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (Via the "enable-named-pipe" option) will render mysqld useless!
#
# Skip-networking
# Replication Master Server (default)
# Binary logging is required for replication
# Log-bin
# Required unique id between 1 and 2 ^ 32-1
# Defaults to 1 if master-host is not set
# But will not function as a master if omitted
Server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose
# Two methods:
#
#1) Use the change master to command (fully described in our manual )-
# The syntax is:
#
# Change master to MASTER_HOST =, MASTER_PORT =,
# MASTER_USER =, MASTER_PASSWORD =;
#
# Where you replace, by quoted strings and
# By the master's port number (3306 by default ).
#
# Example:
#
# Change master to MASTER_HOST = '192. 564.12.1 ', MASTER_PORT = 125,
# MASTER_USER = 'job', MASTER_PASSWORD = 'secret ';
#
# OR
#
#2) Set the variables below. However, in case you choose this method, then
# Start replication for the first time (even unsuccessfully, for example
# If you mistyped the password in master-password and the slave fails
# Connect), the slave will create a master.info file, and any later
# Change in this file to the variables 'values below will be ignored and
# Overridden by the content of the master.info file, unless you shutdown
# The slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (Commented) and instead use change master to (see above)
#
# Required unique id between 2 and 2 ^ 32-1
# (And different from the master)
# Defaults to 2 if master-host is set
# But will not function as a slave if omitted
# Server-id = 2
#
# The replication master for this slave-required
# Master-host =
#
# The username the slave will use for authentication when connecting
# To the master-required
# Master-user =
#
# The password the slave will authenticate with when connecting
# The master-required
# Master-password =
#
# The port the master is listening on.
# Optional-defaults to 3306
# Master-port =
#
# Binary logging-not required for slaves, but recommended
# Log-bin
# Point the following paths to different dedicated disks
# Tmpdir =/tmp/
# Log-update =/path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
# Bdb_cache_size = 4 M
# Bdb_max_locked = 10000
# Uncomment the following if you are using InnoDB tables
# Innodb_data_home_dir =/var/lib/mysql/
# Innodb_data_file_path = ibdata1: 10 M: autoextend
# Innodb_log_group_home_dir =/var/lib/mysql/
# Innodb_log_arch_dir =/var/lib/mysql/
# You can set .. _ buffer_pool_size up to 50-80%
# Of RAM but beware of setting memory usage too high
# Innodb_buffer_pool_size = 16 M
# Innodb_additional_mem_pool_size = 2 M
# Set .. _ log_file_size to 25% of buffer pool size
# Innodb_log_file_size = 5 M
# Innodb_log_buffer_size = 8 M
# Innodb_flush_log_at_trx_commit = 1
# Innodb_lock_wait_timeout = 50
[Mysqldump]
Quick
Max_allowed_packet = 16 M
[Mysql]
No-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# Safe-updates
[Isamchk]
Key_buffer = 20 M
Sort_buffer_size = 20 M
Read_buffer = 2 M
Write_buffer = 2 M
[Myisamchk]
Key_buffer = 20 M
Sort_buffer_size = 20 M
Read_buffer = 2 M
Write_buffer = 2 M
[Mysqlhotcopy]
Interactive-timeout supplement
Optimize table_cachetable_cache to specify the table cache size. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values Open_tables and Opened_tables of the peak time to determine whether to increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding STATUS values can be obtained using show status like 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure. For machines with 1 GB memory, the recommended value is 128-256.
Case 1: This case comes from a non-particularly busy server table_cache-512open_tables-103opened_tables-1273 uptime-4021421 (measured in seconds). in this case, table_cache seems to be too high. During the peak time, the number of opened tables is much less than that of table_cache.
Case 2: A Development Server. Table_cache-64open_tables-64opened-tables-431 uptime-1662790 (measured in seconds) Although open_tables is already equal to table_cache, opened_tables has a very low value compared to the server running time. Therefore, increasing the value of table_cache should be of little use. Case 3: In this case, the table_cache-64open_tables-64opened_tables-22423 uptime-19538 of a upderconfiguring server is set too low. Although the running time is less than 6 hours, open_tables reaches the maximum value, and opened_tables has a very high value. In this way, you need to increase the value of table_cache. Optimize key_buffer_sizekey_buffer_size to specify the index buffer size, which determines the index processing speed, especially the index reading speed. Check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above STATUS values can be obtained using show status like 'key _ read % ). Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value created_tmp_disk_tables to learn the details. For machines with 1 GB memory, if the MyISAM table is not used, the recommended value is 16 M (8-64 M ).
Case 1: Health Condition key_buffer_size-402649088 (384 M) key_read_requests-597579931key_reads-56188 Case 2: alarm status key_buffer_size-16777216 (16 M) the ratio of key_read_requests-597579931key_reads-53832731 in Case 1 is less than, which is healthy. the ratio in Case 2 reaches, and the alarm has been triggered.
1) back_log: the number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread.
The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. When you observe the process list of your host and find a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value is 50. I will change it to 500.
(2) interactive_timeout: the number of seconds the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for mysql_real_connect. The default value is 28800. I will change it to 7200.
(3) key_buffer_size: The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600 (8 M). My MySQL host has 2 GB of memory, so I changed it to 402649088 (400 MB ).
(4) max_connections: number of customers allowed simultaneously. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see the Too connector connections error. The default value is 100. I will change it to 1024.
(5) record_buffer: Each thread conducting a Sequential Scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value. The default value is 131072 (128 K). I changed it to 16773120 (16 M)
(6) sort_buffer: Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. The default value is 2097144 (2 M). I changed it to 16777208 (16 M ).
(7) table_cache: number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. MySQL requires two file descriptors for each unique opened table. The default value is 64. I changed it to 512.
(Thread_cache_size: Number of threads that can be reused and saved in. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing variables in Connections and Threads_created states, you can see the role of this variable. I set it to 80.
(10) wait_timeout: the number of seconds the server waits for action on a connection before closing it. The default value is 28800. I will change it to 7200. Note: you can modify the parameters by modifying the/etc/my. cnf file and restarting MySQL. This is a relatively cautious job. The above results are just some of my views. You can further modify it based on the hardware situation of your host (especially the memory size.
I found it on the Internet. I just read it. It's not bad. I posted it here. Let's take a look. It's best to have some cool people complete it and sort it out!
========================================================== In Apache, in the architecture of PHP and MySQL, MySQL has the greatest impact on performance and is also a key core component. For Discuz! The same is true for Forum programs. Whether MySQL settings are reasonably optimized directly affects the speed and carrying capacity of the Forum! At the same time, MySQL is also the most difficult part of optimization. It not only needs to understand some MySQL professional knowledge, but also requires a long period of observation statistics and judgment based on experience, and then set reasonable parameters. Next, let's take a look at some of the basics of MySQL optimization. MySQL optimization is divided into two parts: one is the optimization of the physical hardware of the server, and the other is the optimization of MySQL itself (my. cnf.
(1) Impact of server hardware on MySQL Performance
A) disk tracing capability (disk I/O). Taking the current high-speed SCSI hard disk (7200 RPM) as an example, this hard disk is theoretically found 7200 times per second, which is determined by the physical characteristics, there is no way to change. MySQL performs a large number of complex query operations every second. You can imagine the disk read/write volume. Therefore, we usually think that disk I/O is one of the biggest factors restricting MySQL performance. For Discuz with an average daily access volume of more than 1 million PVS! Forum, due to disk I/O constraints, MySQL performance will be very low! To solve this problem, consider the following solutions: Use a RAID-0 + 1 disk array. Do not try RAID-5, mySQL's efficiency on RAID-5 disk arrays will not be as fast as you expected; instead, it will discard traditional hard disks and use faster flash storage devices. After Discuz! The company's technical engineering tests show that the use of flash storage devices is about 6-10 times faster than traditional hard disks.
B) For MySQL applications, we recommend that you use a multi-channel symmetric CPU in the S. M. P. architecture. For example, you can use two Intel Xeon GHz CPUs.
C) For a Database Server using MySQL, we recommend that the Server memory be no less than 2 GB. We recommend that you use more than 4 GB physical memory.
(2) MySQL's own factors when the above server hardware constraints are solved, let's see how MySQL's own optimization works. The optimization of MySQL is mainly to optimize and adjust the parameters in its configuration file my. cnf. The following describes some parameters that have a great impact on performance. Since the optimization settings of the my. cnf file are closely related to the server hardware configuration, we specify a hypothetical server hardware environment:
CPU: 2 Intel Xeon 2.4 GHz memory: 4 gb ddr hard drive: SCSI 73 GB
Next, we will describe the optimized my. cnf based on the above hardware configuration:
# Vi/etc/my. cnf only lists the content in the [mysqld] section in the my. cnf file. The content in other sections has little impact on MySQL running performance, so ignore it.
[Mysqld]
Port = 3306
Serverid = 1
Socket =/tmp/mysql. sock
Skip-locking
# Avoid external locks of MySQL to reduce the chance of errors and enhance stability.
Skip-name-resolve prohibits MySQL from performing DNS resolution on external connections. Using this option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally!
Back_log = 384 specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests within a short period of time, this parameter takes effect. The main thread takes a short time to check the connection and start a new thread.
The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.
Key_buffer_size = 256 M
# Key_buffer_size specifies the buffer size used for the index. Increasing the size can improve the index processing performance. This parameter can be set to 384 M or M for servers with around 4 GB of memory. Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!
Max_allowed_packet = 4 M
Thread_stack = 256 K
Table_cache = 128 K
Sort_buffer_size = the buffer size that can be used for sorting by 6 m. Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.
Read_buffer_size = buffer size available for 4 m read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!
Join_buffer_size = the buffer size that can be used by 8 m joint query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!
Myisam_sort_buffer_size = 64 M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64 m specifies the size of the MySQL Query Buffer. You can run the following command on the MySQL console:
#> Show variables like '% query_cache % ';
#> Show status like 'qcache % ';
# If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the Qcache_hits value is very large, it indicates that the query buffer is frequently used, if this value is small, it will affect the efficiency, you can consider not to query the buffer; Qcache_free_blocks, if this value is very large, it indicates that there are many fragments in the buffer.
Tmp_table_size = 256 M
Max_connections = 768 specifies the maximum number of connection processes allowed by MySQL. If the Too connector Connections error is frequently reported during Forum access, you need to increase the value of this parameter.
Max_connect_errorrs = 10000000
Wait_timeout = 10 specifies the maximum connection time of a request. For servers with around 4 GB of memory, it can be set to 5-10.
Thread_concurrency = 8 this parameter is set to the number of logical CPUs of the server × 2. In this example, the server has two physical CPUs, and each physical CPU supports H.T hyper-threading, therefore, the actual value is 4x2 = 8.
Enabling skip-networking can completely disable the MySQL TCP/IP connection mode. If the WEB server accesses the MySQL database server remotely, do not enable this option! Otherwise, the connection will fail!
---------------------------------
My. ini configuration recommendations:
Table_cache = 1024
The larger the physical memory, the larger the setting. The default value is 2402, and the value is adjusted.
Innodb_additional_mem_pool_size = 4 M
The default value is 2 MB.
Innodb_flush_log_at_trx_commit = 1
(If it is set to 0, innodb_log_buffer_size is stored after the queue is full. The default value is 1)
Innodb_log_buffer_size = 2 M
The default value is 1 MB.
Innodb_thread_concurrency = 8
Set the number of CPUs on your server to 8 by default.
Key_buffer_size = 256 M
The default value is 218 to 128.
Tmp_table_size = 64 M
The default value is 16 MB and the maximum value is 64-256.
Read_buffer_size = 4 M
The default value is 64 KB.
Read_rnd_buffer_size = 16 M
The default value is 256 kb.
Sort_buffer_size = 32 M
The default value is 256 kb.
Max_connections = 1024
The default value is 1210.
Thread _ cache_size = 120
The default value is 60.
Query_cache_size = 32 M
---------------------------------
The following is another configuration suggestion for my. ini:
Port = 3306
Default-character-set = latin1
Default-storage-engine = INNODB
SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_Create_USER, NO_ENGINE_SUBSTITUTION"
Max_connections = 120
Query_cache_size = 32 M
# Number of cached data tables. To set this parameter, see open_tables (indicating the total number of opened data tables) and opened_tables (indicating the total number of opened data tables) in the system status)
Table_cache = 256
# Temporary table size
Tmp_table_size = 12 M
# Number of cache reusable threads
Thread_cache_size = 64
Myisam_max_sort_file_size = 100G
Myisam_max_extra_sort_file_size = 100G
Myisam_sort_buffer_size = 64 M
# This is very important for MyISAM tables. If you only use the MyISAM table, you can set it to 30-40% of the available memory. 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.
Key_buffer_size = 128 M
Read_buffer_size = 1 M
Read_rnd_buffer_size = 512 K
Sort_buffer_size = 1 M
# This is very important for innodb tables
Innodb_buffer_pool_size = 256 M
# This depends on the recovery speed you need. The value M is a good balance between the appropriate recovery time and good performance.
Innodb_log_file_size = 128 M
# In most cases, 4 MB is sufficient unless a large blob data is imported to Innodb.
Innodb_log_buffer_size = 4 M
# This value depends on your program. It may be high or low. 8 represents the starting value.
Innodb_thread_concurrency = 8
Innodb_additional_mem_pool_size = 100 M
# If you are not very concerned about ACID, you can allow the loss of the last two seconds of transactions when the system is fully crash, you can set this value. it can greatly improve the efficiency of writing "short" transactions.
Innodb_flush_log_at_trx_commit = 2
Note:
In many cases, specific analysis is required.
1> If Key_reads is too large, you should increase Key_buffer_size in my. cnf to keep Key_reads/Key_read_requests above 1/100. The smaller the value, the better.
2> If Qcache_lowmem_prunes is large, you need to increase the value of Query_cache_size.
