A brief summary of the optimization method of Mysql table It's pretty comprehensive. _mysql

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

Proportion close to 1:8,000 health condition very good

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
Qcache Total Blocks

Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient, while the value of qcache_hits is very large, it indicates that query buffering use very frequently, at this time need to increase the buffer size Qcache_hits value is not small, It shows 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

The following status values are obtained:

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 you should add a dot

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

Turn on slow query log (slow query log) Slow query logs are useful for tracking problematic queries. 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

# set to your CPU number x2, for example, only one CPU, then thread_concurrency=2
#有2个cpu, then thread_concurrency=4
Skip-innodb
#去掉innodb支持



--------------------------------------------------------------------------------------------------------------- --------------------

Code:

# 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

----------------------------------------------------------------------------------

Add

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.