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

This article mainly introduces "what is the principle of Unique SQL". In daily operation, I believe that many people have doubts about what the principle of Unique SQL is. 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 doubts about "what is the principle of Unique SQL?" Next, please follow the editor to study!

1. What is Unique SQL

When the user executes the SQL statement, each SQL statement text goes into the parser (Parser) and generates a "parse tree" (parse tree). Traversing each node in the parsing tree, ignoring the constant value, and combining each node in the tree with a certain algorithm, calculates an integer value, which is used to uniquely identify this kind of SQL. This integer value is called the same Unique SQL ID,Unique SQL ID SQL statement belongs to the same "Unique SQL".

For example, the user enters the following two SQL statements:

Select * from T1 where id = 1bot select * from T1 where id = 2

The two SQL statements are the same except that the constant values of the filter conditions are different, and the resulting parsing tree has exactly the same topology, so the Unique SQL ID is the same. So the two statements belong to the same Unique SQL as follows:

Select * from T1 where id =?

The GaussDB kernel aggregates statistics for all the above forms of SQL statements and presents them to the user through a view. In this way, we can eliminate the interference of some irrelevant constant values and obtain the statistical data of a certain kind of SQL statements, which provides a numerical basis for performance analysis and problem location.

Note that for Unique SQL ID calculations, only constant values are excluded, not other differences. For example, the SQL statement "select * from T2 where id = 1;" does not belong to the same Unique SQL as the above SQL, and the same SQL statements executed from different CN nodes do not belong to the same Unique SQL.

2. How does Unique SQL count

After receiving the SQL request, the GaussDB kernel first calculates its Unique SQL ID. If the Unique SQL ID already exists, update the relevant statistics directly. If it does not exist, first create a Unique SQL, and then update the statistics, as shown in the following figure:

The statistics of Unique SQL include execution times, response time, number of Cache/IO, row activity, time distribution and other information. You can query them through the following two views:

Gs_instr_unique_sql

Pgxc_instr_unique_sql

The former displays Unique SQL information on the current CN (Coordinator Node) node (the node that executes the current SQL command), and the latter displays Unique SQL information on all CN nodes in the system. Both views have the same format and consist of the fields in the following table:

3. How to use Unique SQL

To use the Unique SQL feature, you need to turn on the following variable switches:

Enable_resource_check (default is on)

Track_counts (default is on, which affects line activity and Cache/IO related fields)

You also need to set instr_unique_sql_count to a positive integer. This variable defaults to 0 and cannot be modified in a gsql session. It needs to be set by SIGHUP, for example:

Gs_guc reload-Z coordinator-D / path/to/coordinator1/-c "instr_unique_sql_count=20" > / dev/null

The instr_unique_sql_count parameter determines the number of unique sql collected by the system. When the number of unique collected reaches this number, the new sql is no longer collected. If you increase this value, the original unique sql information is retained and the new unique sql is collected. If you reduce this value, all collected unique sql information on the current CN node is emptied and new unique sql collection begins.

After the above variables are set, the Unique sql statistics view can be queried like a normal view, for example:

Postgres=# select node_name,query,n_calls from pgxc_instr_unique_sql Node_name | query | naughty callsMuffle-coordinator2 | select node_name,query N_calls from pgxc_instr_unique_sql | | 0 (1 row) |

The system function reset_instr_unique_sql can clean up the unique sql information. This function has three parameters, which means as follows:

Scope: if "GLOBAL", clears the data on all CN nodes; if "LOCAL", clears only the data on the current CN.

Type: if "ALL", all data is cleared; if "BY_USERID", only the unique SQL; of the specified user is cleared. If "BY_CNID", only the unique SQL of the specified CN is cleared.

Value: if type= "ALL", this parameter is meaningless; if type= "BY_USERID", this parameter is the ID of the specified user; if type= "BY_CNID", this parameter is the ID of the specified CN.

For example:

Postgres=# select reset_instr_unique_sql ('global','all',0); reset_instr_unique_sql---- t (1 row)

In addition, if the database process restarts, it will also cause the previously collected unique SQL information to be emptied.

4. Using Unique SQL to assist the positioning problem

The unique sql view provides a wealth of information, and users can choose the information that is helpful to them according to their needs. This section illustrates several ways to use this view according to the actual situation encountered by customers in the production environment, which can be used as a reference for performance optimization.

4.1 query disk contention caused by abnormal row activity

Abnormal line activity can cause disk contention, causing the business to run slowly. By looking at the fluctuations of metrics such as the number of rows scanned, the functions returned, and the number of rows changed, we can find abnormal row activity and help locate the cause.

Postgres=# select sum (n_returned_rows) n_returned_rows, sum (n_tuples_fetched) sum (n_tuples_returned) n_tuples_returned, sum (n_tuples_inserted) nasty tuplings inserted n_tuples_updated, sum (n_tuples_deleted) n_tuples_deleted from pgxc_instr_unique_sql N_returned_rows | n_tuples_fetched | n_tuples_returned | n_tuples_inserted | n_tuples_updated | deleted Tupperware deleted funds- -+-234 | 0 | 0 | 0 | 0 | 0 (1 row)

4.2 query the usage of resources by Top SQL

Based on the indexes such as execution time, CPU time, number of scan lines, physical read / logical read, we can sort the SQL statements in the unique SQL view, find out those SQL statements that take up the most resources, and analyze the impact and causes on performance to help find and locate problems. For example

Sort by SQL execution chronological order or reverse order:

SELECT user_name, unique_sql_id, query, total_elapse_time FROM pgxc_instr_unique_sql ORDER BY total_elapse_time ASC or DESC

Sort sequentially or in reverse order by CPU time consumed by SQL execution:

SELECT user_name, unique_sql_id, query, cpu_time FROM pgxc_instr_unique_sql ORDER BY cpu_time ASC or DESC

Sort rows in SQL order or in reverse order:

SELECT user_name, unique_sql_id, query, n_tuples_returned FROM pgxc_instr_unique_sql ORDER BY n_tuples_returned ASC or DESC

Sort in order or reverse order by total scan lines of SQL:

SELECT user_name, unique_sql_id, query, n_tuples_fetched + n_tuples_returned FROM pgxc_instr_unique_sql ORDER BY n_tuples_fetched + n_tuples_returned ASC or DESC

Sort sequentially or inversely by the time SQL executes the executor:

SELECT user_name, unique_sql_id, query, execution_time FROM pgxc_instr_unique_sql ORDER BY execution_time ASC or DESC

Sort in order or reverse order by the number of physical reads performed by SQL:

SELECT user_name, unique_sql_id, query, n_blocks_fetched FROM pgxc_instr_unique_sql ORDER BY n_blocks_fetched ASC or DESC

Sort in order or reverse order by the number of logical reads performed by SQL:

SELECT user_name, unique_sql_id, query, n_blocks_hit FROM pgxc_instr_unique_sql ORDER BY n_blocks_hit ASC or DESC

4.3 query number of logical / physical reads

Too many logical / physical reads may cause SQL statements to take up more CPU time. The number of logical / physical read blocks of sql statements can be obtained by querying the unique SQL view, which helps to determine the reason for the slow response:

Query the number of physical read blocks:

SELECT n_blocks_fetched FROM pgxc_instr_unique_sql

Number of query logical read blocks:

SELECT n_blocks_hit FROM pgxc_instr_unique_sql

4.4 diagnosing poor performance due to insufficient memory quota

If the database buffer is set too small, the execution result of each SQL statement cannot be cached. If there is other SQL execution after the current SQL execution, the execution results of the previous SQL cache in memory will be squeezed out. In the next round, if the current SQL is executed again, you need to read data from disk for physical IO, but cannot get the data directly from the cache, resulting in poor performance of SQL execution.

Whether the buffer quota is large enough can be judged by the hit rate. Buffer hit ratio = n_blocks_hit/n_blocks_fetched. You can diagnose whether there is insufficient memory quota by querying unique SQL:

SELECT (n_blocks_hit/ n_blocks_fetched) AS hit_ratio from pgxc_instr_unique_sql

At this point, the study of "what is the principle of Unique SQL" 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.