Re: pg_stat_bgwriter
On Sun, Oct 13, 2019 at 06:27:35PM -0700, dangal wrote: Dear I would like to share with you to see what you think about the statistics of pg_stat_bgwriter postgres = # select * from pg_stat_bgwriter; checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_ backend_fsync | buffers_alloc | stats_reset --- + - + --- + -- + --- - + --- + -- + - + - -- + --- + --- 338 | 6 | 247061792 | 89418 | 2939561 | 19872289 | 54876 | 6015787 | 0 | 710682240 | 2019-10-06 19: 25: 30.688186-03 (1 row) postgres = # show bgwriter_delay; bgwriter_delay 200ms (1 row) postgres = # show bgwriter_lru_maxpages; bgwriter_lru_maxpages --- 100 (1 row) postgres = # show bgwriter_lru_multiplier; bgwriter_lru_multiplier - 2 (1 row) Do you think it should increase bgwriter_lru_maxpages due to the value of maxwritten_clean? Do you think it should increase bgwriter_lru_maxpages, bgwriter_lru_multiplier, and decrease bgwriter_delay due to the value of buffers_backend compared to buffers_alloc? Do you think a modification is necessary? What values would you recommend? buffers_alloc does not really matter, here, IMO. You need to compare buffers_checkpoint, buffers_backend and buffers_clean, and ideally you'd have (checkpoints > clean > backend). In your case it's already buffers_checkpoint | buffers_clean | buffers_backend 2939561 | 19872289 | 6015787 You could make bgwriter even more aggressive, but that's unlikely to be a huge improvement. You should investigate why buffers_checkpoint is so low. This is usually a sign of shared_buffers being too small for the active set, so perhaps you need to increase shared_buffers, or see which queries are causing this and optimize them. Note: FWIW, a single snapshot of pg_stats* may be misleading, because it's cumulative, so it's not clear how accurately it reflects current state. Next time take two snapshots and subtract them. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: pg_stat_bgwriter
Thanks a lot, always helping I attached a snapshot that I take every 12 hours of the pg_stat_bgwriter select now,buffers_checkpoint,buffers_clean, buffers_backend from pg_stat_bgwriter_snapshot; now | buffers_checkpoint | buffers_clean | buffers_backend ---++---+- 2019-10-07 12:00:01.312067-03 | 288343 | 1182944 | 520101 2019-10-08 00:00:02.034129-03 | 475323 | 3890772 | 975454 2019-10-08 12:00:01.500756-03 | 616154 | 4774924 | 1205261 2019-10-09 00:00:01.520329-03 | 784840 | 731 | 1601278 2019-10-09 12:00:01.388113-03 |1149560 | 8395288 | 2456249 2019-10-10 00:00:01.841054-03 |1335747 | 11023014 | 2824740 2019-10-10 12:00:01.354555-03 |1486963 | 11919462 | 2995211 2019-10-11 00:00:01.519538-03 |1649066 | 14400593 | 3360700 2019-10-11 12:00:01.468203-03 |1979781 | 15332086 | 4167663 2019-10-12 00:00:01.343714-03 |2161116 | 17791871 | 4525957 2019-10-12 12:00:01.991429-03 |2323194 | 18324723 | 5139418 2019-10-13 00:00:01.251191-03 |2453939 | 19059149 | 5306894 2019-10-13 12:00:01.677379-03 |2782606 | 19391676 | 5878981 2019-10-14 00:00:01.824249-03 |2966021 | 19915346 | 6040316 2019-10-14 12:00:01.869126-03 |3117659 | 20675018 | 6184214 I tell you that we have a server with 24 gb of ram and 6gb of shared_buffers When you tell me that maybe I am running too low of shared_buffers, the query I run to see what is happening is the following: The first 10 are insert, update and an autovaccum select calls, shared_blks_hit, shared_blks_read, shared_blks_dirtied from pg_stat_statements where shared_blks_dirtied> 0 order by shared_blks_dirtied desc limit 10 calls | shared_blks_hit | shared_blks_read | shared_blks_dirtied ---+-+--+- 41526844 | 1524091324 | 74477743 |40568348 22707516 | 1317743612 | 33153916 |28106071 517309 | 539285911 | 24583841 |24408950 23 |23135504 |187638126 |15301103 11287105 | 383864219 | 18369813 |13879956 2247661 | 275357344 | 9252598 | 6084363 13070036 | 244904154 | 5557321 | 5871613 54158879 | 324425993 | 5054200 | 4676472 24955177 | 125421833 | 5775788 | 4517367 142807488 | 14401507751 | 81965894 | 2661358 (10 filas) Another query SELECT pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name = 'shared_buffers') ::integer, 1) AS buffers_percent, round(100.0 * count(*) * 8192 / pg_table_size(c.oid), 1) AS percent_of_relation FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.oid, c.relname ORDER BY 3 DESC LIMIT 10; bufferedbuffers_percent percent_of_relation 3938 MB;64.1; 53.2 479 MB; 7.8;21.3 261 MB; 4.3;99.3 163 MB; 2.6;0.1 153 MB; 2.5;6.7 87 MB; 1.4;1.2 82 MB; 1.3;81.6 65 MB; 1.1;100.0 64 MB; 1.0;0.1 53 MB; 0.9;73.5 -- Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Re: pg_stat_bgwriter
On Mon, Oct 14, 2019 at 08:18:47PM +0200, Tomas Vondra wrote: > Note: FWIW, a single snapshot of pg_stats* may be misleading, because > it's cumulative, so it's not clear how accurately it reflects current > state. Next time take two snapshots and subtract them. For bonus points, capture it with timestamp and make RRD graphs. I took me awhile to get around to following this advice, but now I have 12+ months of history at 5 minute granularity across all our customers, and I've used my own implementation to track down inefficient queries being run periodically from cron, and notice other radical changes in writes/reads I recall seeing that the pgCluu project does this. http://pgcluu.darold.net/ Justin
Re: pg_stat_bgwriter
On Mon, Oct 14, 2019 at 01:12:43PM -0700, dangal wrote: Thanks a lot, always helping I attached a snapshot that I take every 12 hours of the pg_stat_bgwriter select now,buffers_checkpoint,buffers_clean, buffers_backend from pg_stat_bgwriter_snapshot; Please show us the deltas, i.e. subtract the preceding value (using a window function, or something). FWIW 12 hours may be a bit too coarse, but it's better than nothing. now | buffers_checkpoint | buffers_clean | buffers_backend ---++---+- 2019-10-07 12:00:01.312067-03 | 288343 | 1182944 | 520101 2019-10-08 00:00:02.034129-03 | 475323 | 3890772 | 975454 2019-10-08 12:00:01.500756-03 | 616154 | 4774924 | 1205261 2019-10-09 00:00:01.520329-03 | 784840 | 731 | 1601278 2019-10-09 12:00:01.388113-03 |1149560 | 8395288 | 2456249 2019-10-10 00:00:01.841054-03 |1335747 | 11023014 | 2824740 2019-10-10 12:00:01.354555-03 |1486963 | 11919462 | 2995211 2019-10-11 00:00:01.519538-03 |1649066 | 14400593 | 3360700 2019-10-11 12:00:01.468203-03 |1979781 | 15332086 | 4167663 2019-10-12 00:00:01.343714-03 |2161116 | 17791871 | 4525957 2019-10-12 12:00:01.991429-03 |2323194 | 18324723 | 5139418 2019-10-13 00:00:01.251191-03 |2453939 | 19059149 | 5306894 2019-10-13 12:00:01.677379-03 |2782606 | 19391676 | 5878981 2019-10-14 00:00:01.824249-03 |2966021 | 19915346 | 6040316 2019-10-14 12:00:01.869126-03 |3117659 | 20675018 | 6184214 I tell you that we have a server with 24 gb of ram and 6gb of shared_buffers When you tell me that maybe I am running too low of shared_buffers, the query I run to see what is happening is the following: The question is how that compared to database size, and size of the active set (fraction of the database accessed by the application / queries). I suggest you also track & compute shared_buffers cache hit ratio. The first 10 are insert, update and an autovaccum select calls, shared_blks_hit, shared_blks_read, shared_blks_dirtied from pg_stat_statements where shared_blks_dirtied> 0 order by shared_blks_dirtied desc limit 10 calls | shared_blks_hit | shared_blks_read | shared_blks_dirtied ---+-+--+- 41526844 | 1524091324 | 74477743 |40568348 22707516 | 1317743612 | 33153916 |28106071 517309 | 539285911 | 24583841 |24408950 23 |23135504 |187638126 |15301103 11287105 | 383864219 | 18369813 |13879956 2247661 | 275357344 | 9252598 | 6084363 13070036 | 244904154 | 5557321 | 5871613 54158879 | 324425993 | 5054200 | 4676472 24955177 | 125421833 | 5775788 | 4517367 142807488 | 14401507751 | 81965894 | 2661358 (10 filas) Unfortunately, this has the same issue as the data you shared in the first message - it's a snapshot with data accumulated since the database was created. It's unclear whether the workload changed over time etc. But I guess you can use this to identify queries producing the most dirty buffers and maybe see if you can optimize that somehow (e.g. by removing unnecessary indexes or something). Another query SELECT pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name = 'shared_buffers') ::integer, 1) AS buffers_percent, round(100.0 * count(*) * 8192 / pg_table_size(c.oid), 1) AS percent_of_relation FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.oid, c.relname ORDER BY 3 DESC LIMIT 10; bufferedbuffers_percent percent_of_relation 3938 MB;64.1; 53.2 479 MB; 7.8;21.3 261 MB; 4.3;99.3 163 MB; 2.6;0.1 153 MB; 2.5;6.7 87 MB; 1.4;1.2 82 MB; 1.3;81.6 65 MB; 1.1;100.0 64 MB; 1.0;0.1 53 MB; 0.9;73.5 It's generally a good idea to explain what a query is supposed to do, instead of just leaving the users to figure that out. In any case, this is a snapshot at a particular moment in time, it's unclear how how that correlates to the activity.
Re: Optimising a two column OR check
On Sat, 12 Oct 2019 at 17:46, Andrew Gierth wrote: > > "MichaelDBA" == MichaelDBA writes: > > MichaelDBA> Nope, vacuumed it and still got the bitmap index scans. > > Let's see your explains. Here's mine: > > # set enable_seqscan=false; -- because I only have a few rows > SET > # insert into friend values (1,2),(2,5); > INSERT 0 2 > # vacuum analyze friend; > VACUUM > # explain analyze SELECT user1_id FROM friend WHERE user2_id=2 UNION ALL > select user2_id FROM friend WHERE user1_id=2; >QUERY > PLAN > > > Append (cost=0.13..8.32 rows=2 width=4) (actual time=0.009..0.014 rows=2 > loops=1) >-> Index Only Scan using friend_user2_id_user1_id_idx on friend > (cost=0.13..4.15 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1) > Index Cond: (user2_id = 2) > Heap Fetches: 0 >-> Index Only Scan using friend_pkey on friend friend_1 > (cost=0.13..4.15 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1) > Index Cond: (user1_id = 2) > Heap Fetches: 0 > Planning Time: 0.271 ms > Execution Time: 0.045 ms > (9 rows) > > Note that you have to put some actual rows in the table; if it is > completely empty, you'll not get a representative result. > Confirming what's been said - the whole thing works fine on 10. I can't get index only scans on 9.6, but that's a dev machine anyway. Now if only hash indexes supported multiple column, that'd probably result in all my data being returned from a single read of a hash bucket, but that's going into microoptimisation territory :) Thanks!
