MySQL Optimized connection optimization

Posted by Talks on 2012/02/23 |

The first chapter of the preface
Second connection optimization
Third Article index optimization
Fourth query optimization
The fifth chapter to the actual combat

Connection optimization

Connection optimization primarily refers to the parameter adjustments that are involved in the client connection database and the process of opening data tables and indexes in response to requests from clients. The original can be referred to here or here. (original link http://ddbiz.com/?p=950)
Although the compilation and linking of different MySQL distributions also affect the client's link requests, because my system is mostly directly installed MySQL's release package, and rarely make changes, so about manually compiling MySQL to achieve the purpose of optimization, there is no way to talk about, Perhaps there will be a chance to get involved in the future.

Depending on how the memory is used in MySQL, the following parameters affect each request to the client:

2. open-files-limit

   Command-line arguments: –open-files-limit=#
   ini/cnf parameter: open-files-limit
   MySQL variable: open_files_limit
   Global variables, non-dynamic adjustment, the range of values from 0 to 65535.
   Open_files_limit refers to the number of file handles that MySQL can open. When this value is insufficient, the Too many open files error is raised. How many file handles are required and are calculated according to Max_connections and Table_open_cache.
   One interesting phenomenon is that in my 64bit Linux, –open-files-limit or –open_files_limit can be set to more than 64k, such as:
   

   Open-files-limit may be limited by operating system, such as Linux,/proc/sys/fs/file-max, which limits the maximum number of file handles that can be opened by the system. As with Oracle's installation requirements for Linux, the minimum requirement is to exceed 64k. You can modify the/etc/sysctl.conf, add or modify the fs.file-max= #来增加系统最大打开值, do not forget to modify, with the sysctl-p to enable the new value (the above operation is Centos/rhel).
   in Linux, there is also a parameter may limit the system maximum open file value, that is/etc/security/limits.conf
   
   How to modify its value, please refer to the system documentation

   Affected by the following parameters: System-Restricted
   affects the following parameters: Max_connections Table_open_cache
   Adjusts the trigger condition: This parameter needs to be adjusted when the system appears Too many open files.

4. Thread_stack

   Command-line arguments: –thread_stack=#
   INI/CNF parameter: Thread_stack
   MySQL variable: thread_stack
   Global variables, not dynamically adjusted.
   The default is 192k in 32bit system, 256k is the default in 64bit system. The first thing to talk about is thread_stack because he has a key influence on the max_connections below.

   Thread_stack the stack size for the default thread in the stack size,windows in the operating system plane is 1M, and Linux varies depending on the version, typically at 8m or 10m. In several of my CentOS 5.x/6.x, the default stack size is 10M (10 times times higher than Windows)

   Ulimit-s
   10240

   Stack size is an important parameter in the 32bit OS, and reducing the stack size of one thread can increase the number of threads, such as from 10m to 64k. But in 64bit Linux (kernel version >= 2.6.x), it might not be important to allow/proc/sys/vm/overcommit_memory,stack size.

   Affected by the following parameters: None
   will affect the following parameters: Max_connections
   Adjust the trigger condition: Max_connections has reached the maximum allowable value of the current system.

5. Max_connections

   Command-line arguments: –max_connections or –max-connections
   INI/CNF Definition: max_connections
   MySQL variable: max_connections
   Global variables, Dynamically adjustable

   The number of concurrent connections allowed by the MySQL database.
   For a site with a large number of visitors (high PV values), it can sometimes occur: Too many connections error. You can consider increasing this value. For MySQL, the maximum number of concurrent connections that can be supported depends on a number of factors, including:

   1. Operating system threading model, operating system version (see THREAD_SIZE)
   2. Amount of memory available
   3. Memory usage/workload per connection (see Thread_size)
   4. Expected server Response time

   With memory permitting, 32bit windows can support up to 2000 concurrent requests (because the maximum supported memory for a single process is 2G, while a default thread requires a resource of 1MB), and 64bit Windows can calculate the number of threads that can be supported based on memory. (for thread estimates available in Windows, refer to mark Russinovich's article pushing the Limits of windows:processes and Threads, or refer to a Microsoft brief (process address space).)
   While the factors in Linux may be more complex, but Stack_size is still the same as in Windows, is an important factor restricting the number of threads, the maximum number of threads under Liunx also has a default value, Cat/proc/sys/kernel/threads-max, When this value is not adjusted, MySQL's max_connections should be much smaller than it.

   In real-world applications, the number of concurrent numbers that can be supported will be much less than the theoretical value, because each thread cannot be disconnected just by an empty connection. cpu/memory loss when the thread is working can reduce the available provisioning of the entire system. For MySQL, it provides a parameter to resize the stack size: Thread_stack.
   MySQL max_connections * thread_stack should be less than available memory, according to the official MySQL document (doc5.5), Linux (or Solaris), can support 500 to 1000 concurrent connections, if the work of each connection is very small , and the server memory is large, then you can support the link to 10k. Under Windows, there is a limit of (open Tables*2+open Connection) < 2048. So:

   Affected by the following parameters: Thread_stack table_open_cache open_file_limit
   will affect the following parameters: None
   Adjust trigger conditions: When threads_connected (show status like ' threads_connected ') approaches max_connections, action should be taken to increase the number of concurrent numbers.

7. thread_cache_size

   Command-line arguments: –thread_cache_size
   ini/cnf definition: thread_cache_size
   mysql variable: thread_cache_size
   global variable, dynamically adjustable, default value 0, maximum 16k

   MySQL uses this parameter to qualify the database service, caching how many threads are used by a common client. If the server has hundreds of new connections per second, the value should be higher. Determine if you need to increase thread_cache_size by evaluating connections and threads_created.
   mysql> Show status like '%connections% '; output
   connections attempts to connect requests (including requests that cannot establish a connection successfully)
   Max_used_connections Maximum number of concurrent connections

   mysql> show status like ' threads_c% '; output
   threads_cached current number of cache threads
   threads_connected Current number of connections
   thread_created thread creation number

   When the utilization of the connection cache (thread cache hit = (connections–threads_created)/connections*100%) is low, Indicates that MySQL needs to create more threads (the thread cache is not enough) to accept client requests.

   Is affected by the following parameters: None
   affects the following parameters: None
   Adjust trigger condition: When thread cache hit is low, action should be taken to increase thread_cache_size this value.

9. table_open_cache/table_cache

   Command-line arguments: –table-open-cache
   ini/cnf definition: table_open_cache
   mysql variable: table_open _cache
   global variable, dynamically adjustable, default value 400, max 512k

   MySQL Open Table descriptor, cached in Table_open_cache, Table_open_cache >= max_ Connections * 2, this is because some tables require two file characters, such as the MyISAM table, as well as file characters for index, temp table, and so on. The self-linked query statement will open an additional file character for the table.

   A targeted setting is to find all the most complex query statements related to the database (including self-links, Left/right/outer joins, and group statistics) to see how many data tables these links will open, setting this value to N, then

   Table_open_cache > Max_connections * N

   Is affected by the following parameters: Max_connections open_file_limit
   will affect the following parameters: Max_ Connections
   Adjust trigger condition: When opened_tables (show status like ' Opened_tables ') value is large, action should be taken to increase table_open_cache this value.

11. Net_buffer_length

    Command-line arguments: –net_buffer_length
    INI/CNF Definition: Net_buffer_length
    MySQL variable: thread_cache_size
    Global variables, dynamically adjustable, default value 16k, range 1k to 1m.

    The buffering and result buffering of client connections can be dynamically adjusted ( automatically adjusted , meaning that set NET_BUFFER_LENGTH=XXX is not valid) to the maximum max_allowed_packet size. This value reverts to the initial value after each SQL statement is completed. When there is not enough memory – this is a very rare situation, after all, now that the memory is so cheap – or if the concurrent connections are large, you can reduce the initial value appropriately, such as 1k.

    Affected by the following parameters: Max_allowed_packet
    will affect the following parameters: None
    Adjust trigger conditions: If you want to load/import/export large amounts of data, when the query results contain large data fields, such as TEXT,BLOB, etc.

13. Max_allowed_packet

    Command-line arguments: –max_allowed_packet
    INI/CNF Definition: Max_allowed_packet
    MySQL variable: max_allowed_packet
    Global variables, dynamically adjustable, default value 1m, range 1k to 1g.

    The client and server Max_allowed_packet need to be consistent, or the client's max_allowed_packet is larger than the Max_allowed_packet on the server side.

    Affected by the following parameters: None
    will affect the following parameters: None
    Adjust trigger conditions: If you want to load/import/export large amounts of data, when the query results contain large data fields, such as TEXT,BLOB, etc.

    Will q:max_allowed_packet and net_buffer_length affect load data infile?
    A:no

15. Wait_timeout

    Command-line arguments: –wait_timeout
    INI/CNF Definition: Wait_timeout
    MySQL variable: wait_timeout
    Global variables, dynamically adjustable, default 8 hours, range 1 seconds to 31536000.
    Wait_timeout defines the most common idle time for a connected client when no query action is made.
    Note: There will be no impact on established connections.
    You can view the status of the current database connection through show processlist, such as:
    [Singlepic id=96 w=320 h=240 Float=none]

    Affected by the following parameters:
    will affect the following parameters: Max_connections
    Adjust trigger conditions: Short link, high concurrency in the system application.

Previous preface chapter Next index optimization

MySQL Optimized connection optimization