MySQL Performance tuning (soft tuning)

Source: Internet
Author: User
Tags connection pooling

Based on a common version of MySQL server

Purpose: Speed up the responsiveness of the server

Number of processes = Process * thread


1, the query process of MySQL

1) The client sends a connection request to the server

2) server-side (connection pooling) threading to respond to user requests

3) User initiated SQL statement query database select * FROM DB.A;

4) Query cache: Record the user's SQL execution statement and query results

2 and speed up the MySQL server

1) Replace the problematic hardware (the most effective method)

such as: CPU, memory, hard disk


2 ) to tune the settings of the MySQL process

A , concurrency number

optimize the performance parameters of MySQL database:

–max_connections //The number of concurrent accesses allowed by the server, default value 100

–max_used_connections

// number of connections already in response, max_connections * 100% = 99.6% ( ideal value ≈85%)


–VI/ETC/MY.CNF//Set permanent in configuration file/etc/my.cnf

[Mysqld]

max_connections = 1000

    

Mysql>use MySQL;

mysql> Show variables like ' max_connections '; //View the maximum number of concurrent connections, default to mysql>show global status like ' Max_used_connections '; // Current number of connections

mysql> flush status; Refresh

Mysql>set global max_connections=200; Modify parameter values, currently in effect


B, timeout

mysql>Show variables like ' connect_timeout'; Connection time-out, in seconds, default 10s mysql>show variables like ' wait_timeout';

When the client connects to the server, the client does not perform any action at 28800S, the server side automatically disconnects, the number of seconds to wait on a connection before shutting it down, and the default 28800s

Mysql> set global connect_timeout=20; //Global modifications are preceded byGlobal Modifications

Mysql>set wait_timeout=7200; Not globally modified, with this command

The number of concurrent, can be set connect_timeout shorter, the number of concurrent, you can set its long


C, index buffer

–key_buffer index buffer size unit M, default is 16M, Show Unit bytes

calculate the probability formula for an index Miss cache: key_reads/key_read_requests * 100%= value is as small as possible.

high probability indicates that the odds of finding are small, and the Key_buffer value can be increased appropriately in the configuration file .

-should be increased appropriately when the probability is low Key_buffer the value

Mysql>Show variables like ' key_buffer_size ';   View the size of the index buffer mysql>show variables like ' key_read% ';

Key_read_requests Total number of index read requests key_reads not found in memory, read the number of indexes directly from the hard disk

VIM/ETC/MY.CNF//settings

[Mysqld]                 Key_buffer = 16M                     


8bit=1 bytes, 1024 bytes =1k,1024k=1m, 1024m=1g,1024g=1t


D, other settings

Sort_buffer_size = 512K

– Each thread that needs to be sorted allocates a buffer of that size. Increase this value to speed up the order by or group by query, the query displays the unit bytes

mysql> Show variables like ' sort_buffer_size ';   

read_buffer_size = 256K

– The length of the buffer reserved for read operations that read data from the data table order

mysql> Show variables like "read_buffer_size%";

Read_rnd_buffer_size = 512K

– Query results that are output in a particular order (for example, a query that uses an ORDER BY clause)

mysql> Show variables like "read_rnd_buffer_size%";

Thread_cache_size = 10

– Number of threads that can be reused for saving in the cache (default is 0) mysql> show variables like ' thread_cache_size ';

Mysql> set global thread_cache_size=10; Specify size



    3 ) Optimization of Queries

A, record slow query

Vi/etc/my.cnf

[Mysqld]

log-slow-queries //enable slow query log

log-query-time=5 //Record 5 seconds query with no result set displayed default 10 seconds

log-queries-not-using-indexes //Record not using index query

[[Email protected] ~] mysqldumpslow mysqld-slow.log //View the contents of the slow query log

B, server configuration for query caching

Mysql> Show variables like "query_cache%";

+------------------------------+---------+

| variable_name | Value |

+------------------------------+---------+

| Query_cache_limit | 1048576 | queries that exceed this size will not be cached

| Query_cache_min_res_unit |4096 | minimum size of cache block

| Query_cache_size | 0 | Query Cache Size

| Query_cache_type | On | cache type, determines what queries are cached

| Query_cache_wlock_invalidate | OFF |                                   when other clients are writing to the MyISAM table                                  If the query cache is returned , the cache result, or wait for the write operation to complete the reread table to get results

+------------------------------+---------+

Mysql> show global Status Like "qcache%";

+-------------------------+-------+

| variable_name | Value |

+-------------------------+-------+

| Qcache_free_blocks | 0 | Number of contiguous memory blocks in the cache, which indicates a possible fragmentation

| Qcache_free_memory | 0 | Free memory in the cache

| Qcache_hits | 0 | Increases each time the query hits the cache

| Qcache_inserts | 0 | Each query is inserted to increase the number of hits/inserts is not the ratio

| Qcache_lowmem_prunes | 0 | The number of times the cache is out of memory and must be cleaned up to provide space for more queries

| qcache_not_cached | 0 | The number of cache queries that are not appropriate

| Qcache_queries_in_cache | 0 | The number of current cache queries (and responses)

| Qcache_total_blocks |  0 | Number of blocks in cache

+-------------------------+-------+



This article is from the "Dave-Technology blog" blog, please be sure to keep this source http://davewang.blog.51cto.com/6974997/1858703

MySQL Performance tuning (soft tuning)

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.