Top Tweets for #PostresMarathon

about 3 years ago

#PostresMarathon Day 5: How to work with pg_stat_statments, part 1 (previous tweet: https://t.co/CBP1DuZlul) There are two big areas of query optimization: 1. "Micro" optimization: analysis and improvement of particular queries. Main tool: EXPLAIN. 2. "Macro" optimization: analysis of whole or large parts of workload, segmentation of it, studying characteristics, going from top to down, to identify and improve the parts that behave the worst. Main tools: pg_stat_statements (and additions or alternatives), wait event analysis, and Postgres logs. Today we focus on how to read and use pg_stat_statements, starting from basics and proceeding to using the data from it for macro optimization. Docs: https://t.co/jXQeY4wxGp Extension pg_stat_statements (for short, "pgss") became standard de-facto for macro-analysis. It tracks all queries, aggregating them to query groups – called "normalized queries" – where parameters are removed. There are certain limitations, some of which are worth remembering: - it doesn't show anything about ongoing queries (can be found in pg_stat_activity) - a big issue: it doesn't track failing queries, which can sometimes lead to wrong conclusions (example: CPU and disk IO load are high, but 99% of our queries fail on statement_timeout, loading our system but not producing any useful results – in this case, pgss is blind) - if there are SQL comments, they are not removed, but only the first comment value is going to be present in the "query" column for each normalized query The view pg_stat_statements has 3 kinds of columns: 1. queryid – an identifier of normalized query. In the latest PG version it can also be used to connect (JOIN) data from pgss to pgsa (pg_stat_statements) and Postgres logs. Surprise: queryid value can be negative. 2. Descriptive columns: ID of database (dbid), user (userid), and the query itself (query) 3. Metrics. Almost all of them are cumulative: calls, total_time, rows, etc. Non-cumulative: stddev_plan_time, stddev_exec_time, min_exec_time, etc. In this post we'll focus only on cumulative ones. To read and interpret data from pgss, you need three steps: 1. take two snapshots corresponding to two points of time 2. calculate the diff for each cumulative metric and for time difference for the two points in time - a special case is when the first point in time is the beginning of stats collection – in PG14+, there is a separate view, pg_stat_statements_info, that has information about when the pgss stats reset happened; in PG13 and older this info is not stored, unfortunately 3. (the most interesting part!) calculate three types of derived metrics for each cumulative metric diff – assuming that M is our metric and remembering some basics of calculus from high school: a. dM/dt – time-based differentiation of the metric M b. dM/dc – calls-based differentiation (I'll explain it in detail) c. %M – percentage that this normalized query takes in the whole workload considering metric M Step 3 can be also applied not to particular normalized queries on a single host but bigger groups – for example: - aggregated workload for all standby nodes - whole workload on a node (e.g., the primary) - bigger segments such as all queries from specific user or to specific database - all queries of specific type – e.g., all UPDATE queries If your monitoring system supports pgss, you don't need to deal with working with snapshots manually – although, keep in mind that I personally don't know any monitoring that works with pgss perfectly, preserving all kinds of information discussed in this post (and I studied quite a few of Postgres monitoring tools). // Below I sometimes call normalized query "query group" or simply "group". Let's mention some metrics that are usually most frequently used in macro optimization (full list: https://t.co/UHQrD0VqEt): 1. calls – how many query calls happened for this query group (normalized query) 2. total_plan_time and total_exec_time – aggregated duration for planning and execution for this group (again, remember: failed queries are not tracked, including those that failed on statement_timeout) 3. rows – how many rows returned by queries in this group 4. shared_blks_hit and shared_blks_read – number if hit and read operations from the buffer pool. Two important notes here: - "read" here means a read from the buffer pool – it is not necessarily a physical read from disk, since data can be cached in the OS page cache. So we cannot say these reads are reads from disk. Some monitoring systems make this mistake, but there are cases that this nuance is essential for our analysis to produce correct results and conclusions. - the names "blocks hit" and "blocks read" might be a little bit misleading, suggesting that here we talk about data volumes – number of blocks (buffers). While aggregation here definitely make sense, we must keep in mind that the same buffers may be read or hit multiple times. So instead of "blocks have been hit" it is better to say "block hits". 5. wal_bytes – how many bytes are written to WAL by queries in this group There are many more other interesting metrics, it is worth exploring them all. Once you obtained 2 snapshots of pgss (remembering timestamp when they were collected), let's consider practical meaning of the three derivatives we discussed: Derivative 1. Time-based differentiation * dM/dt, where M is "calls" – the meaning is simple. It's QPS. If we talk about particular group (normalized query), it's QPS (queries per second) that all queries in this group have. 10,000 is pretty large so, probably, you need to improve the client (app) behavior to reduce it, 10 is pretty small (of course, depending on situation). If we consider this derivative for whole node, it's our "global QPS". * dM/dt, where M is "total_plan_time + total_exec_time" – this is the most interesting and key metric in query macro analysis targeted at resource consumption optimization (goal: reduce time spent by server to process queries). Interesting fact: it is measured in "seconds per second", meaning: how many seconds our server spends to process queries in this query group. *Very* rough (but illustrative) meaning: if we have 2 sec/sec here, it means that we spend 2 seconds each second to process such queries – we definitely would like to have more than 2 vCPUs to do that. Although, this is a very rough meaning because pgss doesn't distinguish situations when query is waiting for some lock acquisition vs. performing some actual work in CPU (for that, we need to involve wait event analysis) – so there may be cases when the value here is high not having a significant effect on the CPU load. * dM/dt, where M is "rows" – this is the "stream" of rows returned by queries in the group, per second. For example, 1000 rows/sec means a noticeable "stream" from Postgres server to client. Interesting fact here is that sometimes, we might need to think how much load the results produced by our Postgres server put on the application nodes – returning too many rows may require significant resources on the client side. * dM/dt, where M is "shared_blks_hit + shared_blks_read" - buffer operations per second (only to read data, not to write it). This is another key metric for optimization. It is worth converting buffer operation numbers to bytes. In most cases, buffer size is 8 KiB (check: show block_size;), so 500000 buffer hits&reads per second translates to 500000 * 8 / 1024 / 1024 GiB/sec = ~ 3.8 GiB/s of the internal data reading flow (again: the same buffer in the pool can be process multiple times). This is a significant load – you might want to check the other metrics to understand if it is reasonable to have or it is a candidate for optimization. * dM/dt, where M is wal_bytes – the stream of WAL bytes written. This is relatively new metric (PG13+) and can be used to understand which queries contribute to WAL writes the most – of course, the more WAL is written, the higher pressure to physical and logical replication, and to the backup systems we have. An example of highly pathological workload here is: a series of transactions like "begin; delete from ...; rollback;" deleting many rows and reverting this action – this produces a lot of WAL not performing any useful work. === That's it for the part 1 of pgss-related howto, in next parts we'll talk about dM/dc and %M, and other practical aspects of pgss-based macro optimization. Let me know if it was useful, and please share with your colleagues and any people who work with @PostgreSQL. I am lagging a bit in putting these posts to GitLab, but will eventually do it so everything will be in one place: https://t.co/FfvlurElTp

Nik Samokhvalov

@samokhvalov

about 3 years ago

#PostgresMarathon day 4. Understanding how sparsely tuples are stored in a table Previous tweet: https://t.co/ypYAzuGZ4y Today, we'll discuss tuples and their locations in pages – this is quite entry-level material but useful in many cases. Understanding physical layout of rows in tables may be important in many cases, especially during performance optimization efforts. Some terms: - Page / buffer / block – unit of storage on disk and in Postgres buffer pool (loaded to RAM unchanged), in most cases 8 KiB (check it: "show block_size;"), it holds a portion of a table or index. - Tuple – physical version of a row in a table. - Tuple header – metadata about a tuple, including transaction ID, visibility info, and more. - Transaction ID (same as XID, tid, txid) – unique identifier for a transaction in Postgres: - It's allocated for modifying transactions. Read-only ones have "virtualxid" to avoid "wasting" XIDs, since they are still 32-bit as of PG16. There is work in progress to switch to 64-bit.(https://t.co/c8lvjuDarq) - You can get a XID allocated for your transactions calling function pg_current_xact_id() or, in PG12 and older, txid_current(). Tuple header has interesting "hidden", or "system" columns (docs: https://t.co/LGGIltSqub): - ctid – a hidden (system) column that represents the physical location of tuple in table, it has the form of two integers (X, Y), where: - X is page number starting from 0 - Y is sequential number of tuple inside the page starting from 1 - xmin, xmax – XIDs of transactions that created this row version (tuple), and deleted it (making this tuple "dead") If we need to understand how sparsely some tuples are stored, we can just include ctid into the SELECT clause of the query. For example, we have the following table and a simple query to it: nik=# \d t1 Table "public.t1" Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+--------- id | bigint | | not null | user_id | double precision | | | Indexes: "t1_pkey" PRIMARY KEY, btree (id) "t1_user_id_idx" btree (user_id) nik=# select * from t1 where user_id = 101469; id | user_id --------+--------- 28414 | 101469 235702 | 101469 478876 | 101469 495042 | 101469 555593 | 101469 626491 | 101469 635785 | 101469 702725 | 101469 (8 rows) To understand physical locations of these rows, just include "ctid" to the SELECT clause of the same query: nik=# select ctid, * from t1 where user_id = 101469; ctid | id | user_id ------------+--------+--------- (153,109) | 28414 | 101469 (1274,12) | 235702 | 101469 (2588,96) | 478876 | 101469 (2675,167) | 495042 | 101469 (3003,38) | 555593 | 101469 (3386,81) | 626491 | 101469 (3436,125) | 635785 | 101469 (3798,95) | 702725 | 101469 (8 rows) – each row is stored in a different page (153, 1274, etc). This is not the best situation for the performance of this query – a lot of IO is needed. Now, if we want to see what tuples are present in page 1274, we can use a trick by converting ctid to point (via text since direct conversion is not possible) and extracting the first number of the two – the page number. nik=# select ctid, * from t1 where (ctid::text::point)[0] = 1274 order by ctid limit 15; ctid | id | user_id -----------+--------+--------- (1274,1) | 235691 | 225680 (1274,2) | 235692 | 617397 (1274,3) | 235693 | 233968 (1274,4) | 235694 | 714957 (1274,5) | 235695 | 742857 (1274,6) | 235696 | 837441 (1274,7) | 235697 | 745413 (1274,8) | 235698 | 752474 (1274,9) | 235699 | 102335 (1274,10) | 235700 | 908715 (1274,11) | 235701 | 909036 (1274,12) | 235702 | 101469 (1274,13) | 235703 | 599451 (1274,14) | 235704 | 359470 (1274,15) | 235705 | 196437 (15 rows) nik=# select count(*) from t1 where (ctid::text::point)[0] = 1274; count ------- 185 (1 row) – overall, there are 185 rows, and our row for user_id=101469 is also present, at position 12. (Note, however, that this will be a very slow query for larger tables since it requires a Seq Scan, and we cannot create an index on "ctid" or other system columns. For queries that are aiming to find particular ctid "...where ctid = '(123, 456)'", though, performance is going to be good thanks to Tid Scan, see https://t.co/sXHXH0NcvT). Confirming that the original query, indeed, involves many buffer operations (also see Day 1 where we talked about importance of BUFFERS ): nik=# explain (analyze, buffers, costs off) select * from t1 where user_id = 101469; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Index Scan using t1_user_id_idx on t1 (actual time=0.131..0.367 rows=8 loops=1) Index Cond: (user_id = '101469'::double precision) Buffers: shared hit=11 Planning Time: 0.159 ms Execution Time: 0.383 ms (5 rows) – 11 buffer hits, it is 88 KiB. This is to retrieve 8 rows. Here is how we can determine the size of those 8 rows: nik=# select sum(pg_column_size(t1.*)) from t1 where user_id = 101469; sum ----- 317 (1 row) Thus, the Postgres executor must handle 88 KiB to return 317 bytes – this is far from optimal. Since we have an Index Scan here, some of those buffer hits are index-related, some – to get data from heap (table) How to improve? Option 0. Don't do anything but understand what's happening. Perhaps, you don't need to make significant improvements, as none of the options discussed below are perfect. Avoid over-optimization. But understand how sparse tuples are located and be ready to double-check it. In some cases, the fact that the target tuples stored too sparsely can be a significant factor for query performance, leading to timeouts. In this case, do consider the following tactics. Option 1. Maintain tables and indexes in good shape: - Table bloat control: bloat is regularly analyzed, prevented by well-tuned autovacuum and regularly removed by pg_repack - Index maintenance: bloat control as well + regular reindexing, because index health declines over time even if autovacuum is well-tuned (btree health degradation rates improved in PG14, but those optimization does not eliminate the need to reindex on regular basis in heavily loaded systems) - Partitioning: one of benefits of partitioning is improved data locality Option 2. Use index-only scans instead of index scans. This can be achieved by using mutli-column indexes or covering indexes, to include all the columns needed for our query. For our example: nik=# create index on t1(user_id) include (id); CREATE INDEX nik=# explain (analyze, buffers, costs off) select * from t1 where user_id = 101469; QUERY PLAN ----------------------------------------------------------------------------------------- Index Only Scan using t1_user_id_id_idx on t1 (actual time=0.040..0.044 rows=8 loops=1) Index Cond: (user_id = '101469'::double precision) Heap Fetches: 0 Buffers: shared hit=4 Planning Time: 0.179 ms Execution Time: 0.072 ms (6 rows) – 4 buffer hits instead of 11, much better Option 3: Physically reorganize the table according to the index / column values: This is physical reorganization of the table. It has two downsides: - You need to choose which index to use for it – only one index. Therefore, it will help only to specific subset of the queries on the workload and can be useless for other queries - UPDATEs of the rows will move tuples, decreasing the benefits of CLUSTER, so it might be needed to repeat it. There are two ways to reorganize the table - SQL command CLUSTER (docs: https://t.co/kqXl7DAUU3) – not an online operation, cannot be recommended for live systems that cannot afford maintenance windows - pg_repack (https://t.co/O402sSAdFG) has option "--order-by=<..>", which allows achieving an effect similar to CLUSTER but in an online fashion, without downtime. For our example: nik=# cluster t1 using t1_user_id_idx; CLUSTER And now the query: nik=# explain (analyze, buffers, costs off) select * from t1 where user_id = 101469; QUERY PLAN --------------------------------------------------------------------------------- Index Scan using t1_user_id_idx on t1 (actual time=0.035..0.040 rows=8 loops=1) Index Cond: (user_id = '101469'::double precision) Buffers: shared hit=4 Planning Time: 0.141 ms Execution Time: 0.070 ms (5 rows) – also just 4 buffer hits – same as for the approach with covering index and Index-Only Scan. Though, here we have Index Scan. === That's it for today – we discussed ctid. Sometime in the future, we'll continue with xmin and xmax and a deep inspection of table/index pages for practical reasons. Subscribe, share, like! Not to forget, I'm mirroring these tips in this GitLab repo: https://t.co/bvxQNRjaMw

30K

27K

Top Tweets for #PostresMarathon

Last Seen Hashtags on Sotwe

Trends for you

Most Popular Users