Shulou(Shulou.com)06/02 Report--

This article introduces the knowledge of "the basic architecture of MySQL and the sharing of two solutions to solve the memory occupation problem of long connections". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL is divided into two parts: server layer and storage engine.

The Server layer includes connectors, query caching, parsers, optimizers, executors, etc., covering most of MySQL's core service functions, as well as all built-in functions (such as date, time, math, and encryption functions, etc.). All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, and so on.

The storage engine layer is responsible for data storage and extraction. Its architecture mode is plug-in, supporting multiple storage engines such as InnoDB, MyISAM, Memory and so on. The most commonly used storage engine today is InnoDB, which has been the default storage engine since MySQL version 5.5.5.

Connector

Connection command

Mysql-h$ip-P$port-u$user-p

The mysql in the connection command is a client tool that is used to establish a connection with the server. After completing the classic TCP handshake, the connector will begin to authenticate you, using the user name and password you entered.

If the user name or password is wrong, you will receive a "Access denied for user" error and the client program finishes execution.

If the user name and password authentication is passed, the connector will find out the permissions you have in the permissions table. After that, the permission judgment logic in this connection will depend on the permissions read at this time.

After a user establishes a connection, even if the permission is modified, it will not take effect until the next re-link, because the connector finds out the permission in the permission table to judge.

After the connection is complete, if you have no subsequent action, the connection is idle, which you can see in the show processlist command. The figure in the text is the result of show processlist, where the Command column shows the line "Sleep", indicating that there is now a free connection in the system.

If the client does not move for too long, the connector will automatically disconnect it. This time is controlled by the parameter wait_timeout, and the default value is 8 hours.

Mysql > show variables like 'wait_timeout'

If the client sends a request again after the connection is disconnected, it will receive an error warning: Lost connection to MySQL server during query. At this point, if you want to continue, you need to reconnect and then execute the request.

In the database, persistent connection means that after a successful connection, the client always uses the same connection if there are persistent requests from the client. A short connection means that the connection is disconnected after a few queries are executed, and a new one is established next time.

The process of establishing a connection is usually complicated, so I suggest you minimize the action of establishing a connection, that is, try to use a long connection.

But after using all persistent connections, you may find that sometimes the memory used by MySQL increases particularly fast, because the memory temporarily used by MySQL during execution is managed in the connection object. These resources are not released until the connection is disconnected. Therefore, if the long connection accumulates, it may lead to too much memory consumption and be forcibly killed by the system (OOM). From the phenomenon, it means that MySQL has been restarted abnormally.

There are two solutions to solve the memory occupation problem of long connections: 1. Disconnect long connections regularly. Use it for a period of time, or after it is judged in the program that a large memory-consuming query has been executed, disconnect, and then query and reconnect. 2. If you are using MySQL version 5.7 or later, you can reinitialize the connection resources by executing mysql_reset_connection after each large operation. This process does not require reconnection and redo permission verification, but restores the connection to the state it was when it was just created.

About database connection pooling:

Now assume that the maximum number of maintained connections is 10, the maximum number of connections is 20, and the maximum wait time is 10000 (milliseconds)

Its process: initially there are 10 connections in the connection pool (the maximum number of connections). When a user applies for a connection, it allocates a connection to the user until all 10 connections in the connection pool are allocated, and when the 11th user applies for a connection, it will create the 11th connection and assign it to that user until the 20th connection (maximum number of connections) is assigned to the 20th user, when the 21st user applies for a connection. It needs to wait until one of the previous 20 users disconnects before assigning that connection to the 21st user. When the user disconnects (11th to 20th connections), the connection is not immediately released but waits for 10000 milliseconds (maximum wait time) before it is released. When there are no user connections, there are still 10 connections in the connection pool.

About query caching

I found a blog about disabling caching in MySQL8.0

Mysql > show variables like'% query_cache%'

Query_cache_type: ON indicates that it has been enabled)

Query_cache_type: whether to turn on caching

OFF: off

ON: always open

DEMAND: only queries that explicitly write SQL_CACHE will be sucked into the cache

Query_cache_size: the total memory space used by the cache (in bytes). This value must be an integer multiple of 1024, otherwise the actual allocation of MySQL may be different from this value (it seems that this should be related to the blcok size of the file system)

Query_cache_min_res_unit: the minimum unit size when allocating memory blocks

Query_cache_limit: the maximum result that MySQL can cache. If it exceeds, increase the value of Qcache_not_cached and delete the query result.

Query_cache_wlock_invalidate: if a data table is locked, whether the data is still returned from the cache. The default is OFF, indicating that it can still be returned.

Explanation of caching parameters in GLOBAL STAUS:

Qcache_free_blocks: the number of free blocks in the cache pool

Qcache_free_memory: the amount of free memory in the cache

Qcache_hits: number of cache hits

Qcache_inserts: number of cache writes

Qcache_lowmen_prunes: number of times to delete cache due to insufficient memory

Qcache_not_cached: the number of times the query is not cached, for example, the query result exceeds the size of the cache block, and the query contains variable functions, etc.

Qcache_queries_in_cache: the number of SQL cached in the current cache

Qcache_total_blocks: total number of cached block

Fortunately, MySQL also provides this "on-demand" approach. You can set the parameter query_cache_type to DEMAND so that query caching is not used for default SQL statements. For statements for which you are sure you want to use query caching, you can specify them explicitly with SQL_CACHE, like the following statement:

Mysql > select SQL_CACHE * from T where ID=10

MySQL version 8.0 removes the entire query cache function directly, which means that it doesn't exist at all since 8.0.

Analyzer

If you miss the query cache, you will start to actually execute the statement. First of all, to parse the SQL statement, the parser will first do "lexical analysis", and then do "syntax analysis". According to the results of lexical analysis, the parser will determine whether the SQL statement you enter satisfies the MySQL grammar according to the grammar rules. Wrong SQL, you will receive the mistakes you are most familiar with, you know.

Optimizer

Is to form an optimization scheme (index and join order optimization, which is finally executed by the executor), and then enter the executor phase.

Actuator

At the beginning of execution, first determine whether you have permission to execute a query on the table T (this is the time to judge the permissions of the "table". The connector is to verify the user's identity. If not, an error with no permission will be returned, as shown below (in the project implementation, if the query cache is hit, permission verification will be done when the query is cached back to the result. The query also calls precheck to verify permissions before the optimizer.

Mysql > select * from T where ID=10

ERROR 1142 (42000): SELECT command denied to user'baked premises localhost 'for table' T'

If you have permission, open the table and continue execution. When the table is opened, the executor uses the interface provided by the engine according to the engine definition of the table.

For example, in table T in our example, the ID field does not have an index, then the execution flow of the executor is as follows:

Call the InnoDB engine API to take the first row of the table (usually read from the hard disk in pages, rather than reading only one row at a time) to determine whether the ID value is 10, if not, skip it, and if so, store the row in the result set

Call the engine interface to take the "next row" and repeat the same judgment logic until the last row of the table is fetched.

The executor returns the recordset composed of all the rows that meet the criteria in the above traversal to the client as a result set.

At this point, the execution of the statement is complete.

This is the end of the introduction to "the basic architecture of MySQL and the sharing of two solutions to solve the memory footprint problem of long connections". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.