MySQL performance optimization-optimize parameter configuration during installation to improve service performance

Optimizing parameter configuration During installation improves service performance

Install MySQL under Linux the default configuration installed MySQL is not necessarily working in the best performance state, you need to optimize it. Generally believed that in

The following system parameters are key in the MySQL configuration file:

(1) Interactive_timeout:
The number of seconds the server waits for action on an interactive connection before closing it. An interactive client is defined as the use of the Mysql_real_connect ()

Client_interactive option for customers. The default value is 28800, and I'll change it to 7200.

(2) Back_log:
Require MySQL to have the number of connections. This parameter works when the main MySQL thread obtains a very wide number of connection requests in a very short period of time.

, 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 expected in a

There are a lot of connections in a short time, you need to add it, in other words, this value to the incoming TCP/IP connection's listening queue size. Each operating system in this

A queue size has its own limitations. Attempting to set a back_log above the operating system will be invalid.

The back_log settings in MySQL depend on the operating system
The value of this parameter cannot be greater than the value of the system parameter Tcp_max_syn_backlog under Linux
You can view the current value of Tcp_max_syn_backlog by using the following command
Cat/proc/sys/net/ipv4/tcp_max_syn_backlog
Modify the sysctl-w with the following command net.ipv4.tcp_max_syn_backlog=n
To delve into a little
TCP/IP networks typically have the following process
To establish a connection from the build socket to the bind port in Listen
Specifically to listen, is the listen (int fd, int backlog) call, here backlog and MySQL Back_log has a certain relationship,

That is, the operating system backlog or less than the Back_log value in MySQL, in the Linux kernel 2.6.6 backlog in/include/net/tcp.h by the

Tcp_synq_hsize variable definition

Watch the host Process list if you 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 the Back_log. The default value is 50, and I'll change it to 500.

(3) Max_connections:
The number of simultaneous customers allowed. 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 error. The default value is 100, and I'll change it to 1024.

(4) 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, which increases the amount of

Index (for all read and multiple writes), as much as you can afford. 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).

Whether you want to increase the value of this parameter depends mainly on the following two points:
1, Key_reads/key_read_requests: The ratio should be close to 0.01 or even smaller the better
2, Key_writes/key_write_requests: The proportion of close to 1 better
The solution is, of course, to increase the value of key_buffer_size, to actually run under the console:

Program code
SET GLOBAL key_buffer_size=16777216;
This is set to the global, if it is only the current session, the global switch to sessions can be.

(5) Record_buffer:
Each thread that carries out a sequential scan assigns a buffer of this size to each table it scans. If you do a lot of sequential scans, you might want to

To increase the 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), I changed 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 for each unique open table

The file descriptor. The default value is 64, and I'll change it to 512.

(8) 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 when disconnected, if there is room, the customer's line

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 connections and threads_created-shaped

State of the variable, you can see the role of this variable. I set it to 80.

(9) 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.

Increase the size of a temporary table by setting the Tmp_table_size option, such as a temporary table generated by an advanced group by operation. If you increase the value,

MySQL will also increase the size of the heap table, to improve the speed of the join query, we recommend optimizing the query, to ensure that the query process generated temporary

The table is in memory, preventing the temporary table from being too large to produce a MyISAM table based on the hard disk.

Mysql> show global status like ' created_tmp% ';

+ —————————— –+ ——— +

| variable_name | Value |

+ ———————————-+ ——— +

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files | 58 |

| Created_tmp_tables | 1771587 |

+ —————————— –+ ——— –+

Each time you create a temporary table, the created_tmp_tables increases, and if the temporary table size exceeds tmp_table_size, the temporary table is created on disk.

Created_tmp_disk_tables also increased, created_tmp_files represents the number of temporary file files created by the MySQL service, and the ideal configuration is:

Created_tmp_disk_tables/created_tmp_tables * 100% <= 25% For example the server created_tmp_disk_tables above

Created_tmp_tables * 100% =1.20%, it should be pretty good.

Default to 16M, adjustable to 64-256 best, thread exclusive, too large may not have enough memory I/O Jam

Note: Parameters can be adjusted by modifying the/etc/my.cnf file and restarting the MySQL implementation. Obviously, depending on the hardware configuration of the server, and

The MySQL database load is different and the parameters are set differently. So instead of copying the above parameters, you need to modify them according to different hardware and load.

For the parameters that best suit you.

Slow query analysis, optimizing indexing and configuration

Indexing and query optimization

Types of indexes

Ø General Index: This is the most basic type of index, no uniqueness, such as the restrictions.

Ø Uniqueness Index: Basically the same as normal index, but all indexed column values remain unique.

Ø PRIMARY key: Primary key is a unique index, but must be specified as "PRIMARY key".

Ø full-text indexing: MySQL supports full-text indexing and Full-text search starting at 3.23.23. In MySQL, the index type of the Full-text index is fulltext. Full-text indexing

Can be created on columns of varchar or text type.

Most MySQL indexes (PRIMARY KEY, UNIQUE, index, and fulltext) are stored in the B-tree. Index of Spatial column type using R-Tree, memory table

Hash index is supported.

Single-row and multiple-column indexes (composite index)

An index can be a single-column index or a multiple-column index. Using indexes on related columns is one of the best ways to improve the performance of select operations.

Multiple-column index:

MySQL can create indexes for multiple columns. An index can include 15 columns. For some column types, you can index the left prefix of the column, and the order of the columns is important

。

A multiple-column index can be treated as an array of sorts that contain values created by connecting the values of indexed columns. In general, even the most restrictive single-column index, it

is far less restrictive than a multiple-column index.

Left-most prefix

A multiple-column index has one feature, the leftmost prefix (leftmost prefixing). If you have a multiple-column index of key (FirstName LastName age),

MySQL uses the multiple-column index when the search condition is the combination and order of the following columns:

Firstname,lastname,age

Firstname,lastname

FirstName

In other words, the equivalent is also established key (FirstName LastName) and Key (FirstName).

Indexes are mainly used for the following actions:

Ø quickly find a row that matches a WHERE clause.

Ø Delete Row. Retrieves rows from other tables when a join is performed.

Ø Key_col the max () or min () value for the specific indexed column. Optimized by the preprocessor to check for all occurrences of the index in the Key_col before the

The keyword element uses the WHERE key_part_# = constant. In this case, MySQL performs a keyword for each min () or max () expression

Find and replace it with a constant. If all expressions are replaced with constants, the query returns immediately. For example:

SELECT MIN (Key2), MAX (Key2) from TB WHERE key1=10;

Ø if the leftmost prefix of an available keyword is sorted or grouped (for example, order by key_part_1,key_part_2), sorted or grouped

a table. If Desc is followed by all key elements, the keyword is read in reverse order.

Ø In some cases, you can optimize a query so that you can retrieve values without querying the rows of data. If the query uses only numeric types from a table

And make up the leftmost column of some keywords, for faster, you can retrieve values from the index tree.

SELECT key_part3 from TB WHERE key_part1=1

Sometimes MySQL does not use indexes, even if there are indexes available. One scenario is when the optimizer estimates that using the index will require MySQL to access most of the rows in the table

。 (In this case, the table scan may be faster). However, if such a query uses limit to search only some of the rows, MySQL uses the index because it

You can find several rows faster and return them in the results. For example:

Reasonable indexing recommendations:

(1) Smaller data types are generally better: smaller data types typically require less space in disk, memory, and CPU caching, and are faster to handle.

(2) A simple data type is better: integer data has less processing overhead than characters because the strings are more complex. In MySQL, you should use the built-in

Date and time data types, rather than using strings to store the time, and storing IP addresses with integer data types.

(3) Try to avoid null: you should specify NOT NULL unless you want to store null. In MySQL, a column with null values is difficult to query optimization, because

They make indexing, index statistics, and comparison operations more complex. You should use 0, a special value, or an empty string instead of a null value.

This is part of some trivial advice and attention points to be aware of when indexing and writing SQL statements.

1. Use limit 1 When the result set has only one row of data

2. Avoid select *, always specify the columns you need

The more data you read from a table, the more slowly the query becomes. Does he increase the time the disk needs to operate, or is it independent of the database server and the Web server?

Open the case. You will experience a very long network delay, simply because data is not required to be transferred between servers.

3. Use a connection (join) to replace a subquery (sub-queries)

Connect (Join) ... It is more efficient because MySQL does not need to create temporary tables in memory to complete this logical two

Step of the query work.

4. Use enum, CHAR instead of varchar, use reasonable field attribute length

5. Use not NULL as much as possible

6. Fixed-length table will be faster

7. Split large DELETE or INSERT statement

8. The smaller the query column, the faster

Where condition

In a query, the Where condition is also a more important factor, as little as possible and reasonable where conditions are important, as far as possible in multiple conditions

, the condition that extracts as little data as possible is put in front, reducing the query time of the latter where condition.

Some where conditions cause the index to be invalid:

Øwhere clause in the query conditions are! =,mysql will not be able to use the index.

When the Øwhere clause uses the MySQL function, the index will be invalid, for example: SELECT * from TB where left (name, 4) = ' xxx '

Ø when searching for a match with like, the index is valid: SELECT * from TBL1 where name is ' xxx% '

Invalid index when '%xxx% '