Shulou(Shulou.com)06/01 Report--

Under the high load of linux, the mysql database is completely optimized (transferred) [@ more@] at the same time, the number of online visits continues to increase. When the server with 1G memory obviously feels the strain, it even crashes every day, or sometimes the server gets stuck. This problem has perplexed me for more than half a month. MySQL is a very scalable algorithm. So you can usually run with less memory or give MySQL more memory to get better performance.

After installing mysql, the configuration files should be in the / usr/local/mysql/share/mysql directory, there are several configuration files, there are my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf, different traffic sites and different server environments, of course, different configuration files are required.

In general, the configuration file my-medium.cnf can meet most of our needs; generally, we will copy the configuration file to / etc/my.cnf only need to modify the configuration file, using mysqladmin variables extended-status-u root-p you can see the current parameters, there are three configuration parameters are the most important, that is, key_buffer_size,query_cache_size,table_cache.

Key_buffer_size

Key_buffer_size only works on MyISAM tables

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. In general, we set it to 16m, but in fact, the number of slightly larger sites is far from enough. By checking the status values Key_read_requests and Key_reads, you can see whether the key_buffer_size setting is reasonable. The key_reads / key_read_requests ratio should be as low as possible, at least 1 SHOW STATUS LIKE 100 and 1 SHOW STATUS LIKE 1000 is better. (the above state values can be obtained using the state value). Or if you have installed phpmyadmin, you can see it through the running status of the server. It is recommended that you use phpmyadmin to manage mysql. The following status values are obtained by myself through phpmyadmin:

This server has been running for 20 days.

Key_buffer_size-128m

Key_read_requests-650759289

Key_reads-79112

The proportion is close to 1, 000, and the health condition is very good.

Another way to estimate key_buffer_size is to add up the space occupied by the indexes of each table in your site's database. Take this server as an example: the larger table indexes add up to about 125m, a number that increases as the table grows.

Query_cache_size

Starting with 4.0.1, MySQL provides a query buffering mechanism. Using query buffering, MySQL stores SELECT statements and query results in a buffer, and in the future, for the same SELECT statement (case sensitive), the results will be read directly from the buffer. According to the MySQL user manual, the use of query buffering can be up to 238% efficient.

You can know whether the query_cache_size setting is reasonable by adjusting the following parameters

Qcache inserts

Qcache hits

Qcache lowmem prunes

Qcache free blocks

Qcache total blocks

The value of Qcache_lowmem_prunes is very large, which means that there is often insufficient buffering. At the same time, the value of Qcache_hits is very large, which means that query buffering is used very frequently. At this time, you need to increase the buffer size. The value of Qcache_hits is not large, it means that your query repetition rate is very low. In this case, using query buffering will affect efficiency, so you can consider not using query buffering. In addition, adding SQL_NO_CACHE to the SELECT statement makes it clear that query buffering is not used.

Qcache_free_blocks, if the value is very large, indicates that there are a lot of fragments in the buffer. Query_cache_type specifies whether to use query buffering.

I set:

Query_cache_size = 32m

Query_cache_type= 1

Get the following status values:

Qcache queries in cache 12737 indicates the number of entries currently cached

Qcache inserts 20649006

Qcache hits 79060095 seems to have a high rate of repeated queries.

There have been so many cases of low cache in Qcache lowmem prunes 617913.

Qcache not cached 189896

Qcache free memory 18573912 currently has remaining cache space

The number Qcache free blocks 5328 seems to be a little big and fragmented.

Qcache total blocks 30953

If the memory allows 32 megabytes, it should be added up.

Table_cache

Table_cache specifies the size of the table cache. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents. By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_cache. If you find that open_tables equals table_cache and opened_tables is growing, then you need to increase the value of table_cache (the above state values can be obtained using SHOW STATUS LIKE 'Open%tables'). Note that table_cache cannot be set to a large value blindly. If set too high, it may result in insufficient file descriptors, resulting in unstable performance or connection failure.

For machines with 1 gigabyte 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 equal to table_cache, it has been running for 20 days relative to the server uptime, and the value of opened_tables is also very low. Therefore, increasing the value of table_cache should not be of much use. If the above value occurs after running for 6 hours, then consider increasing table_cache.

Log-bin

If you do not need to record binary log to turn off this function, note that after turning it off, you will not be able to recover the data before the problem. You need to back up manually. The binary log contains all the statements to update the data. The purpose is to use it to restore the data to its final state as much as possible when restoring the database. In addition, if you are doing synchronous replication (Replication), you also need to use binary logs to send changes.

Log_bin specifies the log file, and if you don't provide a file name, MySQL will generate the default file name itself. MySQL automatically adds numeric references to the file name, and each time you start the service, a new binary is regenerated. In addition, you can use log-bin-index to specify index files, binlog-do-db to specify databases for records, and binlog-ignore-db to specify databases that are not recorded. 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. Moreover, MySQL will change all database names to lowercase, and all lowercase names must be used when specifying the database, otherwise it will not work.

To turn off this function, just add a # in front of him.

# log-bin

Open slow query log

Enable slow log (slow query log)

Slow query logs are useful for tracking problematic queries. It records all queries that have checked long_query_time and, if necessary, records that do not use indexes. Here is an example of a slow query log:

To enable slow log, you need to set parameters log_slow_queries, long_query_times, and log-queries-not-using-indexes.

Log_slow_queries specifies the log file, and if you don't provide a file name, MySQL will generate the default file name itself. Long_query_times specifies the threshold for slow queries, which defaults to 10 seconds. Log-queries-not-using-indexes is a parameter introduced after 4.1.0 that instructs records for queries that do not use indexes. The author sets up long_query_time=10

Some other important parameters

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

The number of connections required for MySQL. This works when the main MySQL thread gets a lot of connection requests in a very short period of time, and then the main thread takes some (albeit short) time to check the connection and start a new thread. The back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect 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 listening queue for incoming TCP/IP connections. Your operating system has its own limits on the queue size. The man page for Unix listen (2) system calls should have more details. Check your OS document to find the maximum value of this variable. Trying to set the limit that back_log is higher than your operating system will not be valid.

Max_connections

The number of concurrent connections is the largest. If 120 exceeds this value, it will be restored automatically, and the problem can be solved automatically.

Thread_cache

No specific instructions were found, but the post-32 genius created more than 400 threads, compared with thousands of threads the day before, so it's still useful.

Thread_concurrency

# set to your number of cpu x2, for example, if there is only one cpu, then thread_concurrency=2

# if there are 2 cpu, then thread_concurrency=4

Skip-innodb

# remove innodb support

All my.cnf files are attached

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 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 (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/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 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 between

# 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='125.564.12.1', MASTER_PORT=3306

# MASTER_USER='joe', 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 to

# 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 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 25% 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.