MySQL Optimization journal _mysql

Source: Internet
Author: User
Tags lowercase memory usage mysql host mysql query unique id phpmyadmin server memory
At the same time, the amount of online access continues to increase for 1G memory server Obviously feel the difficulty of severe when even every day will panic or occasional server card This problem once bothered me for more than half a month MySQL use is a very scalable algorithm, So you can usually run with little memory or give MySQL more to save for better performance.
After the installation of MySQL, the preparation file should be in the/usr/local/mysql/share/mysql directory, the preparation of a number of files, there are my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf, Different traffic sites and different configuration of the server environment, of course, need to have different configuration files.
In general, my-medium.cnf this configuration file can meet most of our needs; generally we will copy the configuration file to/etc/my.cnf only need to modify this profile on it, use mysqladmin variables Extended-status–u Root–p can see the current parameters, and there are 3 configuration parameters that are most important, that is, Key_buffer_size,query_cache_size,table_cache.
Key_buffer_size only works on the MyISAM table,
KEY_BUFFER_SIZE Specifies the size of the index buffer, which determines the speed at which indexing is processed, especially the speed at which index reads. Generally we set to 16M, actually slightly larger site this number is far from enough, by checking the status value Key_read_requests and Key_reads, you can know key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above state values can be obtained using show status like ' key_read% '). Or if you install the phpMyAdmin can be seen through the server running state, the author recommended the use of phpMyAdmin management MySQL, the following state values are I obtained through the phpMyAdmin case analysis:
This server has been running for 20 days
key_buffer_size–128m
key_read_requests–650759289
Key_reads-79112 ratio is close to 1:8,000. Good health condition
Another way to estimate key_buffer_size is to add up the size of the index of each table in your site database. For example: a larger number of table indexes add up to about 125M this figure will become larger as the table becomes larger.
Starting with 4.0.1, MySQL provides a query buffer mechanism. With query buffering, MySQL stores the SELECT statement and query results in a buffer, and in future, for the same SELECT statement (case-sensitive), the result is read directly from the buffer. According to the MySQL user manual, you can use query buffering up to 238% efficiency.
By adjusting the following parameters, you can know if the query_cache_size setting is reasonable.
Qcache Inserts
Qcache Hits
Qcache Lowmem prunes
Qcache Free Blocks
The value of Qcache total blocksqcache_lowmem_prunes is very large, indicating that the buffer is often insufficient, and the Qcache_hits value is very large, it indicates that query buffering is used very frequently, at this time need to increase the buffer size qcache_ Hits value is not large, it indicates that your query is very low repetition rate, in this case, the use of query buffering will affect efficiency, then you can consider not to query buffer. In addition, adding sql_no_cache to a SELECT statement makes it clear that query buffering is not used.
Qcache_free_blocks, if the value is very large, it indicates that there are many fragments in the buffer Query_cache_type specify whether to use query buffering
I set:
Query_cache_size = 32M
Query_cache_type= 1 Gets the following status value:
Qcache queries in cache 12737 indicates the number of bars currently cached
Qcache Inserts 20649006
Qcache hits 79060095 seems to have a very high repeat query rate.
Qcache Lowmem prunes 617913 There are so many times when cache is too low
Qcache Not Cached 189896
Qcache free memory 18573912 current remaining cache space
Qcache Free Blocks 5328 This number seems to be a bit of a lot of debris
Qcache Total Blocks 30953 If the memory allows 32M should be dot up
Table_cache Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and placed in it, so that the table content can be accessed more quickly. By checking the status values of peak time open_tables and Opened_tables, you can decide whether to increase the Table_cache value. If you find that open_tables equals Table_cache, and Opened_tables is growing, then you need to add Table_cache values (the above status values can be used show status like ' open% Tables ' Get). Note that Table_cache can not be blindly set to a large value. If set too high, the file descriptor may be insufficient, causing performance instability or connection failure.
For machines with 1G of memory, the recommended value is 128-256.
The author sets Table_cache = 256
get the following status:
Open Tables 256
Opened tables 9046 Although open_tables is already equal to Table_cache, it has been running for 20 days relative to server uptime, and opened_tables values are very low. Therefore, increasing the value of Table_cache should be of little use. If you run for 6 hours, the above values will be considered to increase table_cache.
If you don't need to log 2 into the log, turn this feature off, note that when you turn off the data before you can recover the problem, you need to manually back up the binary log, which contains all the statements that update the data, to use it to restore the data to its final state as much as possible when restoring the database. In addition, if you do synchronous replication (Replication), you also need to use binary log shipping modification.
LOG_BIN Specifies the log file, and if the file name is not provided, MySQL produces the default file name itself. MySQL automatically adds a digital citation after the filename, and each time the service is started, a new binary is regenerated. In addition, you can use Log-bin-index to specify index files, use BINLOG-DO-DB to specify a database of records, and use BINLOG-IGNORE-DB to specify a database that is not logged. Note that binlog-do-db and binlog-ignore-db specify only one database at a time, and multiple statements are required to specify multiple databases. Also, MySQL will change all the database names to lowercase, you must use all lowercase names when specifying the database, otherwise it will not work.
You just need to put a # in front of him to turn off the function.
#log-bin Open the Slow query log (slow query log) Slow query logs are useful for tracking queries that have problems. It records all queries that have been checked for Long_query_time, and records that do not use the index if necessary. The following is an example of a slow query log:
Open the slow query log, you need to set parameters log_slow_queries, Long_query_times, log-queries-not-using-indexes.
log_slow_queries Specifies the log file, and if the file name is not provided, MySQL produces the default file name itself. LONG_QUERY_TIMES Specifies the threshold value for a slow query, which defaults to 10 seconds. Log-queries-not-using-indexes is a parameter introduced later in 4.1.0, which indicates that a query that does not use an index is logged. The author sets long_query_time=10
The author sets:
Sort_buffer_size = 1M
max_connections=120
Wait_timeout =120
back_log=100
Read_buffer_size = 1M
Thread_cache=32
interactive_timeout=120
thread_concurrency = 4 Parameter Description:
Back_log
Require MySQL to have the number of connections. This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit very short) to check the connection and start a new thread. The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of this queue. Unix Listen (2) system calls to the hand album should have more details. Check your OS documentation to find the maximum value for this variable. Attempting to set Back_log above your operating system will be ineffective.
Max_connections
The maximum number of concurrent connections, 120 more than this will automatically recover, out of the problem can be automatically resolved
Thread_cache
No specific instructions were found, but it is still useful to create more than 400 threads in the 20 days after 32 and create thousands of threads a day ago.
Thread_concurrency
#设置为你的cpu数目x2, for example, only one CPU, then thread_concurrency=2
#有2个cpu, then thread_concurrency=4
Skip-innodb
#去掉innodb支持代码:
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32m-64m) where MySQL plays
# an important part, or systems up to 128M where the MySQL is used together with
# Other programs (such as a Web server)
#
# You can copy this file to
#/ETC/MY.CNF to set global options,
# MYSQL-DATA-DIR/MY.CNF to set server-specific options
# installation This directory is/var/lib/mysql) or
# ~/.MY.CNF to set user-specific options.
#
# in this file, the can use the all long options supports.
# If you are want to know which options a program supports, run the program
# with the '--help ' option.
# The following options would be passed to all MySQL clients
[Client]
#password = Your_password
Port = 3306
Socket =/tmp/mysql.sock
#socket =/var/lib/mysql/mysql.sock
# here follows entries for some specific programs
# The MySQL server
[Mysqld]
Port = 3306
Socket =/tmp/mysql.sock
#socket =/var/lib/mysql/mysql.sock
Skip-locking
Key_buffer = 128M
Max_allowed_packet = 1M
Table_cache = 256
Sort_buffer_size = 1M
Net_buffer_length = 16K
Myisam_sort_buffer_size = 1M
max_connections=120
#addnew Config
Wait_timeout =120
back_log=100
Read_buffer_size = 1M
Thread_cache=32
Skip-innodb
Skip-bdb
Skip-name-resolve
join_buffer_size=512k
Query_cache_size = 32M
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 is a security enhancement,
# If all processes this need to connect to mysqld run on the same host.
# All interaction with Mysqld must is made via Unix sockets or named pipes.
# This is the using this option without enabling named Pipes on Windows
# (via the "enable-named-pipe" option) would 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 are not set
# but would not function as a master if omitted
Server-id = 1
# Replication Slave (comment out master)
#
# To configure this host as a replication slave, can choose between
# Two methods:
#
# 1 Use the ' Change MASTER ' 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= ' 125.564.12.1 ', master_port=3306,
# master_user= ' Joe ', master_password= ' secret ';
#
# OR
#
# 2) Set the variables below. However, in the case for you choose this method, then
# Start replication for the ' even unsuccessfully, for example
# If you mistyped the password in Master-password and the slave fails to
# Connect), the slave'll create a master.info file, and any later
# change in this file to the variables ' values below'll 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 this reason, you'll want to leave the lines below untouched
# (commented) and instead use change MASTER to (= above)
#
# required Unique ID between 2 and 2^32-1
# (and different from the master)
# defaults to 2 if Master-host is set
# but would not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave-required
#master-host =
#
# The username the slave would use for authentication when connecting
# to the master-required
#master-user =
#
# The password the slave would authenticate with when connecting to
# 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 = 4M
#bdb_max_lock = 10000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir =/var/lib/mysql/
#innodb_data_file_path = Ibdata1:10m: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 = 16M
#innodb_additional_mem_pool_size = 2M
# Set ... _log_file_size to% of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[Mysqldump]
Quick
Max_allowed_packet = 16M
[MySQL]
No-auto-rehash
# Remove The next comment character if you are not familiar with SQL
#safe-updates
[Isamchk]
Key_buffer = 20M
Sort_buffer_size = 20M
Read_buffer = 2M
Write_buffer = 2M
[Myisamchk]
Key_buffer = 20M
Sort_buffer_size = 20M
Read_buffer = 2M
Write_buffer = 2M
[Mysqlhotcopy]
Interactive-timeout Supplement
Optimizes table_cachetable_cache Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and placed in it, so that the table content can be accessed more quickly. By checking the status values of peak time open_tables and Opened_tables, you can decide whether to increase the Table_cache value. If you find that open_tables equals Table_cache, and Opened_tables is growing, then you need to add Table_cache values (the above status values can be used show status like ' open% Tables ' Get). Note that Table_cache can not be blindly set to a large value. If set too high, the file descriptor may be insufficient, causing performance instability or connection failure. For machines with 1G of memory, the recommended value is 128-256.
Case 1: This case comes from a server that is not particularly busy table_cache–512open_tables–103opened_tables–1273uptime–4021421 (measured in seconds) tabl The e_cache seems to have set too high. At peak times, the number of open tables is much less than Table_cache.
Case 2: The case comes from a development server. table_cache–64open_tables–64opened-tables–431uptime–1662790 (measured in seconds) although open_tables is already equal to Table_cache, but the phase The value of the opened_tables is also very low for the server run time. Therefore, increasing the value of Table_cache should be of little use. Case 3: The case comes from a upderperforming server table_cache–64open_tables–64opened_tables–22423uptime–19538 the Table_cache is set too low in this case. Although the running time is less than 6 hours, the open_tables reaches the maximum value and the Opened_tables value is very high. This requires increasing the value of the Table_cache. Optimization key_buffer_sizekey_buffer_size Specifies the size of the index buffer, which determines the speed at which indexing is processed, especially the speed at which index reads. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above state values can be obtained using show status like ' key_read% '). Key_buffer_size only works on the MyISAM table. Even if you don't use the MyISAM table, the internal temporary disk table is the MyISAM table, and you also use this value. You can use the Check status value created_tmp_disk_tables to know the details. For machines with 1G memory, the recommended value is 16M (8-64m) If the MyISAM table is not used.
Case 1: Health status key_buffer_size–402649088 (384M) key_read_requests–597579931key_reads-56188 Case 2: Alert status key_buffer_size– The 16777216 (16M) key_read_requests–597579931key_reads-53832731 Case 1 ratio is less than 1:10,000, is a healthy situation, the case 2 ratio reached 1:11, the alarm has been ringing.

1), Back_log: Requirements MySQL can have the number of connections. This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit very short) to check the connection and start a new thread.
The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of this queue. Attempting to set Back_log above your operating system will be ineffective. When you look at your host process list, find a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | NULL to connect process, it is necessary to increase the value of Back_log. The default value is 50, and I'll 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, and I'll change it to 7200.

(3), Key_buffer_size: The index block is buffered and is shared by all threads. Key_buffer_size is the size of the buffer used for the index block, adding that it can be better processed by indexing (for all read and multiple writes), to the extent that you can afford that much. If you make it too big, the system will start to change pages and it really slows down. The default value is 8388600 (8M), my MySQL host has 2GB memory, so I changed it to 402649088 (400MB).

(4), Max_connections: The number of customers allowed at the same time. Increase this value to increase the number of file descriptors required by mysqld. This number should be increased, otherwise you will often see Too many connections errors. The default value is 100, and I'll change it to 1024.

(5), Record_buffer: Each thread that carries out a sequential scan assigns a buffer of this size to each table scanned by it. If you do a lot of sequential scans, you might want to add that value. The default value is 131072 (128K) and I change it to 16773120 (16M)

(6), Sort_buffer: Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by action. The default value is 2097144 (2M) and I change it to 16777208 (16M).

(7), Table_cache: The number of tables opened for all threads. Increasing this value can increase the number of file descriptors required by mysqld. MySQL requires 2 file descriptors for each unique open table. The default value is 64, and I'll change it to 512.

(, Thread_cache_size: The number of threads stored in that can be reused.) If there is, a new thread is obtained from the cache, and if there is space when disconnected, the client's line is placed in the cache. If there are a lot of new threads, in order to improve performance you can have this variable value. By comparing the variables of connections and threads_created states, we can see the effect 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, and I'll change it to 7200. Note: Parameters can be adjusted by modifying the/etc/my.cnf file and restarting the MySQL implementation. This is a more cautious work, the results above is only a few of my views, you can be based on your own host hardware conditions (especially memory size) to further modify.

I found from the Internet, I have just looked at, but also good, hair here, we look, the best to have a cow to complement the perfect then, and then tidy up!

======================================== in Apache, PHP, MySQL architecture, MySQL has the most impact on performance, but also the core part of the key. For the discuz! Forum program is also the case, MySQL settings are reasonable optimization, directly affect the speed and load of the forum! At the same time, MySQL is also the most difficult part of optimization, not only need to understand some MySQL expertise, but also need a long time to observe statistics and based on experience to judge, and then set reasonable parameters. Below we understand some MySQL optimization foundation, the MySQL optimization I divide into two parts, one is the server physical hardware optimization; the second is the optimization of MySQL itself (MY.CNF).

(1) The impact of server hardware on MySQL performance
(a) Disk seek capability (disk I/O), with the current high speed SCSI hard drive (7200 rpm), for example, the hard drive theoretically seeks 7,200 times per second, which is determined by the physical properties and has no way to change. MySQL every second in a large number of complex query operations, on the disk read and write volume imaginable. As a result, disk I/O is generally considered to be one of the biggest constraints on MySQL performance, and for discuz! forums with daily average visits above 1 million PV, MySQL performance will be very low due to disk I/O constraints! To address this constraint, consider the following solutions: Use a raid-0+1 disk array, and be careful not to try to use Raid-5,mysql on a RAID-5 disk array not as fast as you expect; discard traditional hard drives and use faster flash storage devices. Through the discuz! company's technical engineering test, the use of flash storage devices can be 6-10 times higher than the traditional hard drive speed.
b CPU for MySQL applications, recommend the use of S.M.P. Architecture of the multi-channel symmetric CPU, for example: two Intel Xeon 3.6GHz CPU can be used.
C Physical memory for a database server using MySQL, server memory is not recommended to be less than 2GB, and it is recommended to use more than 4GB of physical memory.

(2) MySQL's own factors when the above server hardware constraints are resolved, let's see how MySQL's own optimization is operating. The optimization of MySQL itself is mainly to adjust the parameters of its configuration file MY.CNF. Here are some of the parameters that have a greater impact on performance. Since the optimization settings of the my.cnf file are closely related to the server hardware configuration, we specify an imaginary server hardware environment:
Cpu:2 Intel Xeon 2.4GHz Memory: 4GB DDR hard drive: SCSI 73GB
Below, we are based on the above hardware configuration combined with a MY.CNF has been optimized to explain:
# VI/ETC/MY.CNF the contents of the [MYSQLD] paragraph in the my.cnf file are listed below and the other paragraphs have little impact on the performance of the MySQL operation, so ignore it.
[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-locking
# avoid MySQL external locking, reduce error probability and enhance stability.
Skip-name-resolve disables MySQL for DNS resolution of external connections, which eliminates the time that MySQL can perform DNS resolution. Note, however, that if this option is turned on, all remote host connection authorizations should be in the IP address mode, otherwise MySQL will not handle the connection request properly!



Back_log = 384 Specifies the number of possible connections to MySQL. When the MySQL main thread receives a very wide number of connection requests within a short time, this parameter takes effect, and 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 on the stack in the short time before MySQL temporarily stops responding to the new request. If the system has many connections in a short time, you need to increase the value of the parameter, which specifies the size of the incoming TCP/IP connection's listening queue. The different operating systems have their own limits on the size of this queue. Attempting to set Back_log above your operating system will be ineffective. The default value is 50. For Linux systems It is recommended to set to an integer less than 512.


Key_buffer_size = 256M
# KEY_BUFFER_SIZE Specifies the size of the buffer to use for the index, increasing it for better index processing performance. This parameter can be set to 256M or 384M for servers with around 4GB. Note: The value of this parameter set too large will be the overall efficiency of the server down!
Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K
Sort_buffer_size = the size of the buffer that can be used when the 6M query is sorted. Note: This parameter corresponds to the allocated memory is per connection exclusive! If there are 100 connections, the total sorted buffer size that is actually allocated is 100x6 = 600MB. Therefore, for the existence of around 4GB server recommended set to 6-8m.


read_buffer_size = buffer size that can be used by the 4M read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!


Join_buffer_size = 8M the size of the buffer used by the Federated query operation, as in Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!


Myisam_sort_buffer_size = 64M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64M Specifies the size of the MySQL query buffer. You can observe this by performing the following command at the MySQL console:
# > Show VARIABLES like '%query_cache% ';
# > Show STATUS like ' qcache% ';
# If the value of the qcache_lowmem_prunes is very large, it shows that the buffer is often not enough; if the value of qcache_hits is very large, the query buffer is used very frequently, if the value is small but it will affect efficiency, then you can consider not to query buffer; qcache _free_blocks, if the value is very large, it indicates that there are a lot of fragments in the buffer.


Tmp_table_size = 256M
Max_connections = 768 Specifies the maximum number of connection processes allowed by MySQL. You need to increase the value of a too Many connections error message when you visit the forum frequently.


Max_connect_errors = 10000000
Wait_timeout = 10 Specifies the maximum connection time for a request, and the server with about 4GB of memory can be set to 5-10.


Thread_concurrency = 8 This parameter takes the value of the server logical CPU number x2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4x2 = 8


Skip-networking turn on this option to completely shut down MySQL TCP/IP connections, and do not turn on the MySQL database server if the Web server is remotely connected! Otherwise you will not be able to connect properly!

————————————————————————————————–
My.ini Configuration Recommendations:

table_cache=1024
The larger the physical memory, the larger the setting. The default is 2402, tuned to 512-1024 best

innodb_additional_mem_pool_size=4m
The default is 2M

Innodb_flush_log_at_trx_commit=1
(set to 0 is to wait until the innodb_log_buffer_size queue is full and then unified storage, the default is 1)

Innodb_log_buffer_size=2m
The default is 1M

Innodb_thread_concurrency=8
A few of your server CPUs are set to a few, recommended by default generally 8

key_buffer_size=256m
The default is 218 to 128 best

tmp_table_size=64m
The default is 16M up to 64-256.

read_buffer_size=4m
The default is 64K

read_rnd_buffer_size=16m
The default is 256K

sort_buffer_size=32m
The default is 256K

max_connections=1024
Default is 1210

thread_cache_size=120
Default is 60

query_cache_size=32m


————————————————————————————————–

The following is another My.ini configuration recommendation:

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=32m

#缓存数据表数量, set this parameter to see the open_tables in the System state (representing the total number of currently open data tables) and Opened_tables (representing the total number of open tables)
table_cache=256

#临时表的大小
tmp_table_size=12m

#缓存可重用的线程数
Thread_cache_size = 64

myisam_max_sort_file_size=100g
myisam_max_extra_sort_file_size=100g
myisam_sort_buffer_size=64m

#这对MyISAM表来说非常重要. If you are using only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load-#记住, the MyISAM table uses the operating system's cache to cache the data, so you need to set aside some memory for them, and in many cases the data peso is much larger.
key_buffer_size=128m

read_buffer_size=1m
read_rnd_buffer_size=512k
sort_buffer_size=1m

#这对innodb表来说非常重要
Innodb_buffer_pool_size = 256M

#这取决于你需要的回复速度. 128M This value is a good balance between proper recovery time and good performance.
Innodb_log_file_size = 128M

#大多数情况4M足够, unless you are importing large BLOB data into InnoDB, you can add a little.
innodb_log_buffer_size=4m

#这个值取决于你的程序, may be high or low. 8 is the starting value.
Innodb_thread_concurrency=8

innodb_additional_mem_pool_size=100m

#如果你不是很关心ACID, you can set this value by allowing the two-second transaction to be lost when the system is completely crash. It can greatly improve the efficiency of "short" writing transactions.
innodb_flush_log_at_trx_commit=2


Attention:
Many situations require concrete analysis
1> if the key_reads is too large, then the MY.CNF should be key_buffer_size larger, keep key_reads/key_read_requests at least 1/100, the smaller the better.
2> If the qcache_lowmem_prunes is large, the value of query_cache_size should be increased.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.