MySQL connection resource usage and Status Analysis (V1)

Source: Internet
Author: User

1. How to view the connection informationA MySQL connection is a thread. View the connection status: mysql> show status; threads_cached | 0 | threads_connected | 1 | // Number of connections in connection | threads_created | 3 | // Number of connections that have been created | threads_running | 1 | // Number of running Connections | uptime | 22947 | view connection parameters: mysql> show variables; | max_connections | 16384 // maximum number of connections allowed to view the connection thread status: mysql> show processlist; + ---- + ----------- + ------ + --------- + ------ + ------- + ------------------ + | ID | user | host | dB | command | time | state | info | + ---- + ----------- + ------ + --------- + ------ + ------- + ---------------- + | 3 | Denny | localhost | null | query | 0 | null | show processlist | + ---- + ----------- + ------ + --------- + ------ + ------- + ------------------ + 1 row in SET (0.00 Sec) 2 connection Resource AllocationKey_buffer_size specifies the size of the buffer for the index. Increasing the size can improve the index processing performance. Read_buffer_size = the buffer size used by the 4 m read query operation. Join_buffer_size = size of the buffer that can be used by the 8 m joint query operation. sort_buffer_size

Note:: The space for each connection to use a specific thread. The following parameters are exclusive to each connection: the stack (64kb by default, the variable thread_stack) connects to the cache (variable net_buffer_length) and the result cache (variable net_buffer_length) the access cache and result cache can be dynamically expanded to max_allowed_packet as needed. Max_allowed_packet = 4 m if there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 mb. For each MyISAM Table opened, the index file is opened once, and the data file is opened once for each concurrent running thread. For each parallel thread, a table structure, the column structure and size of each column are allocated to 3 * N(Where NIs the length of the largest row, rather than the Blob column ). A blob column requires 5 to 8 bytes plus the BLOB Data Length. The MyISAM storage engine maintains an additional row cache for internal applications. Flush TABLE statement or Mysqladmin flush-tableCommand to immediately close all unused tables and mark all tables in use as disabled. In this way, the memory in most use can be effectively released. Flush table does not return results until all tables are closed. The memory usage below cannot exceed 2 GB: (some systems impose file size restrictions, and a table is a file) innodb_buffer_pool_size + key_buffer_size + max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size) + max_connections * 2 MB // thread Stack
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.