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

This article mainly introduces "what is the basic principle of MYSQL PROXYSQL". In daily operation, I believe that many people have doubts about the basic principle of MYSQL PROXYSQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the question of "what is the basic principle of MYSQL PROXYSQL?" Next, please follow the editor to study!

PorxySQL as a kind of middleware using MYSQL, why is it one of the more popular middleware at present? personal summary has the following points.

1 friendly user interface, the operation of PROXYSQL basically understands the syntax of MYSQL, although it is based on SQLLITE database, but basically most of the configurations in the operation do not involve restart, only need to upload the configuration to memory and take effect immediately.

2 as the master in judging the high availability of various ways of MYSQL, there are many kinds of support, MGR, MHA,PXC, basically the general high availability methods of MYSQL on the market support, and there are also related configuration options for MGR multi-owners.

3 configuration is simple, if there is no need for special configuration, then only three steps, PROXYSQL can be configured as a simple path to support MYSQL high-availability access.

Since PROXYSQL has so many benefits, and the first two articles also talked about the simple configuration of PROXYSQL FOR MHA and PROXYSQL FOR INNODB CLUSTER. Today, we need to take a look at some details of the principle and configuration of PROXYSQL, and finally do a stress test to see how much access pressure PROXYSQL itself can withstand.

In practice, most of the commands we operate are in memory, and the database main is the memory interface for human-computer interaction. Most commands are uploaded to memory and written to disk after operating in memory. Disk is actually the location where proxySQL's configuration data is saved. If it is lost or damaged, you will encounter the problem of configuration loss after PROXYSQL restart, so it is necessary for PROXYSQL to back up the database regularly.

There are four important tables.

List of database servers that mysql_servers PROXYSQL accepts MYSQL

Mysql_users PROXYSQL connects to the list of MYSQL users who must already be established in the MYSQL.

Mysql_query_rules PROXYSQL's rules for some settings of the query

Global_variables stores variables that show the system being applied by the current system

Relative to the above four tables, the following command set requires memory operation data, download to disk and upload to run time after configuring the related tables.

LOAD MYSQL USERS TO RUNTIME

SAVE MYSQL USERS TO DISK

LOAD MYSQL SERVERS TO RUNTIME

SAVE MYSQL SERVERS TO DISK

LOAD MYSQL QUERY RULES TO RUNTIME

SAVE MYSQL QUERY RULES TO DISK

LOAD MYSQL VARIABLES TO RUNTIME

SAVE MYSQL VARIABLES TO DISK

LOAD ADMIN VARIABLES TO RUNTIME

SAVE ADMIN VARIABLES TO DISK

Another question is what variables must be changed by restarting PROXYSQL

The three variables mysql-interface, mysql-threads, and mysql-stacksize must be rebooted to take effect.

With regard to query cache, MYSQL itself actually has a related query cache at 5.X, but in fact it is not commonly used and is basically in a closed state. PROXYSQL also has query cache to return the results in high-frequency queries. Some people may say that this function is also a chicken rib. In fact, I agree with half of this view.

Whether a function is a chicken rib depends on the scenario in which it is applied and the situation it is needed. the situation in the picture below is

It is commonly used in some scenarios, so think differently. If you replace the previous redis with PROXYSQL query cache, some simple functions can also be replaced.

Unlike MySQL query caching, which provides a common cache space for all tables, we must define cached traffic in the proxyysql query cache. This is done by defining query rules that match the traffic to be cached and set a "cache_ttl" for the cached results.

Query in proxysql with the following statement

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC

We found frequently run statements

INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply) VALUES (0xAC80A5EA0101522)

Then we enter this statement into proxysql

LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK

Then run the loaded statement.

The next time you encounter the same statement, you will output the data to the visitor using the result of PROXYSQL memory in the system 5000ms.

At this point, the study of "what are the basic principles of MYSQL PROXYSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.