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
