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

This article will explain in detail how PostgreSQL uses system tables to analyze postgresql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The system tables in the database provide the function of showing the current database status, in which these system tables can monitor the status of the system, query the status of the execution plan, and display as part of the server management status.

It is important for any database to understand and skillfully use these system tables.

Generally speaking, what to do if customers start to complain about the slow response of the postgresql used by your application, or if you find that some of the query feedback is so slow that it is already available to the naked eye.

1 it is the same to view cache hit ratio in other databases. If your memory does not support the buffering of the system, and the data you need cannot reside in memory, it will often generate fault page (a term for some databases that read data that is not in memory), then you must check one of your system parameters, cache hit ratio, which is recommended to be at least 95%. If it reaches 99%, it is a satisfactory number.

The difference is that each database is more convenient and convenient for querying. From the database I have mastered, the method for PG to get cache hit ratio is relatively simple.

Select sum (heap_blks_read) as heap_read

Sum (heap_blks_hit) as heap_hit

Sum (heap_blks_hit) / (sum (heap_blks_hit) + sum (heap_blks_read)) as ratio

From pg_statio_user_tables

In fact, after studying the pg_statio_uer_tables table, it is easy to find that the index parameters of a variety of systems can be changed through the pg_statio_user_tables table.

And in fact, this pg_statio_user_tables is a view that changes from pg_statio_all_tables.

SELECT pg_statio_all_tables.relid

Pg_statio_all_tables.schemaname

Pg_statio_all_tables.relname

Pg_statio_all_tables.heap_blks_read

Pg_statio_all_tables.heap_blks_hit

Pg_statio_all_tables.idx_blks_read

Pg_statio_all_tables.idx_blks_hit

Pg_statio_all_tables.toast_blks_read

Pg_statio_all_tables.toast_blks_hit

Pg_statio_all_tables.tidx_blks_read

Pg_statio_all_tables.tidx_blks_hit

FROM pg_statio_all_tables

WHERE (pg_statio_all_tables.schemaname ALL (ARRAY ['pg_catalog'::name,' information_schema'::name]) AND pg_statio_all_tables.schemaname! ~'^ pg_toast'::text

And what will cause the problem of low cache hit ratio?

1 the designed table stores large fields or other data that is not suitable for storage in a traditional database, such as large pictures or a large number of text, and is often called

2 due to the problem of vacuum, dead tuple was not cleaned up in time

3 the query has not been optimized, and a large number of queries have taken the way of sequential scans.

4 you do not have enough memory to carry out the query activity you are currently facing

Well, if the next question starts from the point of view of finding which table may have a problem, you can take a look at it immediately.

2 pg_stat_database this system table, so that the table can clearly give the following information

1 how many transactions are generated by each database

2 how many transactions are rolled back, (you can see some problems from this point)

3 the read-write ratio of the overall database, and the ratio of tup_fetched to tup_inserted, tup_updated, tup_deleted and

4 the ratio of the feedback of query data to the search of actual data, that is, the number of rows returned by how many data to find and the corresponding number of rows retrieved by the database tup_fetched / tup_returned

5 whether the database has a deadlock

Wait, the above information. It should be possible to confirm that at least that database is hot, or if the index is not right compared with the historical data index, then you can continue to look for problems in this database.

After confirming the database, the next step is to start confirming the problems with the tables in the database.

3 pg_stat_all_tables

Select * from pg_stat_all_tables where relname not like 'pg%' and relname not like' sql%'

Through pg_stat_all_tables, you can sort out the tables in the current database, such as the insert and update del of the data of a table, and the ratio used in the query, as well as the ratio of the query, as well as knowing the time of the last autovacuum of a table, and other useful information, especially through the tracking of the parameter n_dead_tup, you can find out whether a table has a transaction without commit, which creates a large number of dead_tup or long transactions. Causing a sharp rise in dead_tup in a certain period of time, etc., problems.

Then after we get the evidence, we can report it to the relevant developers and use the slow query of POSTGRESQL to further confirm some design problems, or the lack of indexes in statements.

This is the end of this article on "how PostgreSQL uses system tables to analyze postgresql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.