{"id":388,"date":"2024-11-26T13:42:49","date_gmt":"2024-11-26T13:42:49","guid":{"rendered":"https:\/\/stradata.com.tr\/?p=388"},"modified":"2024-11-28T07:58:49","modified_gmt":"2024-11-28T07:58:49","slug":"postgresql-17de-pg_stat_bgwriter-ve-pg_stat_checkpointer-analizi","status":"publish","type":"post","link":"https:\/\/stradata.com.tr\/?p=388","title":{"rendered":"PostgreSQL 17’de PG_STAT_BGWRITER ve PG_STAT_CHECKPOINTER analizi"},"content":{"rendered":"\n

pg_stat_bgwriter ve pg_stat_checkpointer view’lar\u0131n\u0131n \u00f6zelliklerini ve process’lerin davran\u0131\u015flar\u0131n\u0131 inceleyece\u011fimiz bu yaz\u0131m\u0131za \u00f6ncelikle konunun ge\u00e7mi\u015finden bahsederek ba\u015flayal\u0131m. Daha \u00f6ncesinde “Background Writer” (bgwriter) <\/strong>process’inin g\u00f6revi olan “background write” <\/strong>ve “checkpoint” <\/strong>i\u015flemleri PostgreSQL 9.2 versiyonunda iki ayr\u0131 process’e b\u00f6l\u00fcnd\u00fc ve “background write” i\u015flemini bgwriter, checkpoint i\u015flemini de checkpointer <\/strong>process’i yapacak \u015fekilde g\u00fcncellendi. (9.2 versiyonundan \u00f6nce checkpointer process’i yoktu). Bu de\u011fi\u015fikli\u011fin en \u00f6nemli nedenlerinden biri \u015fu linkte <\/a>de g\u00f6rebilece\u011finiz gibi checkpoint’in son fsync i\u015flemini yapabilmek i\u00e7in “background write” i\u015flemini durdurma zorunlulu\u011fuydu ve bunun gibi fakt\u00f6rler negatif performans etkisi yarat\u0131yor idi. 9.2 versiyonunda bahsetti\u011fimiz de\u011fi\u015fiklik yap\u0131ld\u0131 ve checkpointer process’i devreye al\u0131nd\u0131, ancak background process’lerde yap\u0131lan bu de\u011fi\u015fiklik, istatistik view’lar\u0131na yans\u0131mad\u0131 ve her iki process’e ait bilgiler pg_stat_bgwriter <\/strong>view’\u0131nda tutulmaya devam etti. PostgreSQL 17 versiyonunda ise bu istatistikler de art\u0131k farkl\u0131 view’larda tutulmaya ba\u015fland\u0131.<\/p>\n\n\n\n

Bu arada, “background write”<\/strong> ve “checkpoint”<\/strong> i\u015flemlerini hat\u0131rlayacak olursak, \u00f6ncelikle her iki i\u015flemin de shared buffers’da bulunan “dirty”<\/strong> yani commit’lenmi\u015f ancak disk \u00fczerindeki data file’da g\u00fcncellemesi yap\u0131lmam\u0131\u015f veri bloklar\u0131n\u0131 diske yazd\u0131\u011f\u0131n\u0131 belirtelim. Checkpoint i\u015flemi her “checkpoint_timeout” saniyede bir, ya da WAL segment’lerin boyutunun “max_wal_size” \u0131 a\u015fmas\u0131 ile tetiklenir. Di\u011fer yandan “background write” i\u015flemi, shared buffers \u00fczerindeki temiz\/kullan\u0131labilir bloklar\u0131n say\u0131s\u0131 yetersiz g\u00f6r\u00fcld\u00fc\u011f\u00fcnde tetiklenir. Bu tetikleme sonucunda buffer alan\u0131ndaki dirty bloklar\u0131n bir k\u0131sm\u0131 diske yaz\u0131larak temiz i\u015faretlenir ve shared buffer’da yer a\u00e7\u0131l\u0131r.<\/p>\n\n\n\n

PostgreSQL d\u00f6k\u00fcmantasyonundan da checkpoint ve Background Writer kavramlar\u0131 ile ilgili detaylar\u0131 okuyabilirsiniz.
https:\/\/www.postgresql.org\/docs\/current\/wal-configuration.html<\/a>
https:\/\/www.postgresql.org\/docs\/current\/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER<\/a><\/p>\n\n\n\n

PostgreSQL 17 versiyonu ile birlikte view’lar\u0131n kolonlar\u0131 \u015fu \u015fekilde yap\u0131land\u0131r\u0131ld\u0131:<\/p>\n\n\n\n

postgres=# \\d pg_stat_bgwriter\n\n                      View "pg_catalog.pg_stat_bgwriter"\n      Column      |           Type           | Collation | Nullable | Default\n------------------+--------------------------+-----------+----------+---------\n buffers_clean    | bigint                   |           |          |\n maxwritten_clean | bigint                   |           |          |\n buffers_alloc    | bigint                   |           |          |\n stats_reset      | timestamp with time zone |           |          |\n\npostgres=# \\d pg_stat_checkpointer\n\n                     View "pg_catalog.pg_stat_checkpointer"\n       Column        |           Type           | Collation | Nullable | Default\n---------------------+--------------------------+-----------+----------+---------\n num_timed           | bigint                   |           |          |\n num_requested       | bigint                   |           |          |\n restartpoints_timed | bigint                   |           |          |\n restartpoints_req   | bigint                   |           |          |\n restartpoints_done  | bigint                   |           |          |\n write_time          | double precision         |           |          |\n sync_time           | double precision         |           |          |\n buffers_written     | bigint                   |           |          |\n stats_reset         | timestamp with time zone |           |          |<\/code><\/pre><\/div>\n\n\n\n
\u015eimdi testlerimizin ilk a\u015famas\u0131nda, yeni bir PostgreSQL cluster yarat\u0131yor ve veri taban\u0131m\u0131zda bu iki view’\u0131 sorguluyoruz:<\/p>\n\n\n\n
postgres=# select * from pg_stat_bgwriter;\n\n buffers_clean | maxwritten_clean | buffers_alloc |          stats_reset\n---------------+------------------+---------------+-------------------------------\n             0 |                0 |           175 | 2024-11-25 13:23:41.271027+00\n(1 row)\n\n\npostgres=# select * from pg_stat_checkpointer;\n\n num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset\n-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------\n         0 |             0 |                   0 |                 0 |                  0 |          0 |         0 |               0 | 2024-11-25 13:23:41.271027+00\n(1 row)<\/code><\/pre><\/div>\n\n\n\n
pg_stat_bgwriter <\/strong>\u00e7\u0131kt\u0131s\u0131nda sadece “buffers_alloc”<\/strong> de\u011ferinin s\u0131f\u0131rdan farkl\u0131 oldu\u011funu g\u00f6r\u00fcyoruz. Bu de\u011fer cluster baz\u0131nda her “buffer allocation” i\u015fleminde artan bir counter de\u011feri ve background writer process’i bu de\u011feri kendi algoritmas\u0131nda, ne zaman \u00e7al\u0131\u015faca\u011f\u0131n\u0131\/devereye girece\u011fini belirmede kullan\u0131yor. Di\u011fer kolonlardan “buffers_clean”<\/strong>, bgwriter taraf\u0131ndan temizlenilen buffer say\u0131s\u0131n\u0131, “maxwritten_clean”<\/strong> ise bgwriter process’inin bir temizleme i\u015fleminde \u00fcst s\u0131n\u0131r\u0131ndan daha fazla buffer temizlemesi sonucunda durmas\u0131n\u0131n, ka\u00e7 defa ya\u015fand\u0131\u011f\u0131n\u0131 g\u00f6steriyor. Yeni veri taban\u0131m\u0131zda bu de\u011ferlerin 0 olmas\u0131 bekliyoruz. Ayn\u0131 zamanda pg_stat_checkpointer \u00e7\u0131kt\u0131s\u0131ndaki t\u00fcm kolonlar\u0131n da yine 0 oldu\u011funu g\u00f6r\u00fcyoruz.<\/p>\n\n\n\n
\u015eimdi bir tablo yarat\u0131p, i\u00e7ine kay\u0131t insert ederek view’lar\u0131 tekrar inceleyelim:<\/p>\n\n\n\n
postgres=# create table io_test (c1 integer,c2 text);\n\npostgres=# INSERT INTO io_test( c1, c2)\nSELECT floor(random() * 999 + 1)::int,\nleft(md5(random()::text),3)\nFROM generate_series(1,3000000);\nINSERT 0 3000000\n\npostgres=# select * from pg_stat_bgwriter;\n\n buffers_clean | maxwritten_clean | buffers_alloc |          stats_reset\n---------------+------------------+---------------+-------------------------------\n             0 |                0 |         13583 | 2024-11-25 13:23:41.271027+00\n(1 row)\n\n\npostgres=# select * from pg_stat_checkpointer;\n\n num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset\n-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------\n         0 |             0 |                   0 |                 0 |                  0 |          0 |         0 |               0 | 2024-11-25 13:23:41.271027+00\n(1 row)<\/code><\/pre><\/div>\n\n\n\n
Yapt\u0131\u011f\u0131m\u0131z i\u015flem ile 3 milyon kayd\u0131 tabloya insert ettik ve view’lar\u0131 sorgulad\u0131k. Ancak hen\u00fcz ne checkpoint ne de bgwriter tetiklenmedi\u011fi i\u00e7in, t\u00fcm insert i\u015fleminin memory’de yap\u0131ld\u0131\u011f\u0131 ve diske hen\u00fcz hi\u00e7bir veri yaz\u0131lmad\u0131\u011f\u0131n\u0131 s\u00f6yleyebiliriz. Sadece insert i\u015flemi ile birlikte shared buffers’da “buffer allocation”<\/strong> yap\u0131ld\u0131\u011f\u0131n\u0131 ve buffers_alloc de\u011ferinin artt\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz.<\/p>\n\n\n\n
\u015eimdi manuel bir checkpoint tetikleyelim ve pg_stat_checkpointer view’\u0131n\u0131 tekrar inceleyelim.<\/p>\n\n\n\n
postgres=# checkpoint;\nCHECKPOINT\n\npostgres=# select * from pg_stat_checkpointer;\n\n num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset\n-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------\n         0 |             1 |                   0 |                 0 |                  0 |        397 |        84 |           13315 | 2024-11-25 13:23:41.271027+00\n(1 row)\n\n\npostgres=# select * from pg_stat_bgwriter;\n\n buffers_clean | maxwritten_clean | buffers_alloc |          stats_reset\n---------------+------------------+---------------+-------------------------------\n             0 |                0 |         13839 | 2024-11-25 13:23:41.271027+00\n(1 row)\n<\/code><\/pre><\/div>\n\n\n\n
Checkpoint ile birlikte 13315 dirty buffer blo\u011funun diske yaz\u0131ld\u0131\u011f\u0131n\u0131 (buffers_written<\/strong>), ve i\u015flemin 481 ms. (write_time+sync_time<\/strong>) s\u00fcrd\u00fc\u011f\u00fcn\u00fc g\u00f6rebiliyoruz. Ayn\u0131 bilgi “log_checkpoints<\/strong>” parametresi a\u00e7\u0131k oldu\u011funda log dosyas\u0131na da yaz\u0131lacakt\u0131r. Ayr\u0131ca view \u00e7\u0131kt\u0131s\u0131ndan sadece 1 defa “requested checkpoint”<\/strong>, yani checkpoint_timeout s\u00fcresi dolmadan istek \u00fczerine checkpoint al\u0131nd\u0131\u011f\u0131n\u0131 (num_requested=1<\/strong>) ve hen\u00fcz hi\u00e7 checkpoint_timeout s\u00fcresi dolmas\u0131 sonucu otomatik checkpoint al\u0131nmad\u0131\u011f\u0131n\u0131 (num_timed=0<\/strong>) g\u00f6rebiliyoruz.<\/p>\n\n\n\n
\u015eimdi shared buffer’da bo\u015f alan bask\u0131s\u0131 yaratmak i\u00e7in tablodaki verinin tamam\u0131n\u0131 okuyup yeniden tabloya yazal\u0131m ve bir ka\u00e7 saniye sonras\u0131nda view’lar\u0131 inceleyelim.<\/p>\n\n\n\n
postgres=# insert into io_test select * from io_test ;\nINSERT 0 3000000\n\n\npostgres=# select * from pg_stat_bgwriter;\n\n buffers_clean | maxwritten_clean | buffers_alloc |          stats_reset\n---------------+------------------+---------------+-------------------------------\n          4466 |               20 |         27558 | 2024-11-25 13:23:41.271027+00\n(1 row)\n\n\npostgres=# select * from pg_stat_checkpointer;\n\n num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset\n-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------\n         1 |             1 |                   0 |                 0 |                  0 |       4737 |       103 |           13358 | 2024-11-25 13:23:41.271027+00\n(1 row)<\/code><\/pre><\/div>\n\n\n\n
\u0130\u015flem sonucunda background writer process’inin devreye girdi\u011fini ve shared buffers alan\u0131ndan 4466 “dirty page” i diske yazarak bo\u015f yer a\u00e7t\u0131\u011f\u0131n\u0131 g\u00f6rebiliyoruz. Bu yazma i\u015flemlerinin 20 tanesi “bgwriter_lru_maxpages<\/strong>” de\u011ferinden fazla page’i yazd\u0131\u011f\u0131 i\u00e7in kesilmi\u015f (maxwritten_clean=20<\/strong>). Bu de\u011fer test ortam\u0131m\u0131zda varsay\u0131lan olarak 100 idi, e\u011fer pg_stat_bgwriter view \u00e7\u0131kt\u0131s\u0131nda maxwritten_clean kolonunun devaml\u0131 y\u00fckseldi\u011fi g\u00f6zlemleniyorsa “bgwriter_lru_maxpages” parametresini artt\u0131rmak de\u011ferlendirilebilir. pg_stat_checkpointer \u00e7\u0131kt\u0131s\u0131nda ise test esnas\u0131nda 1 otomatik checkpoint al\u0131nd\u0131\u011f\u0131n\u0131 ancak bu checkpoint ile \u00e7ok az page yaz\u0131ld\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz. Bu checkpoint’in bizim insert i\u015flemimizden \u00f6nce tamamlanm\u0131\u015f oldu\u011funu s\u00f6yleyebiliriz.<\/p>\n\n\n\n
\u015eimdi, bir otomatik checkpoint daha al\u0131nmas\u0131 i\u00e7in 5 dk’dan (checkpoint_timeout<\/strong>) biraz daha fazla bekledikten sonra view’lar\u0131 tekrar sorguluyoruz.<\/p>\n\n\n\n
postgres=# select * from pg_stat_bgwriter;\n\n buffers_clean | maxwritten_clean | buffers_alloc |          stats_reset\n---------------+------------------+---------------+-------------------------------\n          4837 |               26 |         27669 | 2024-11-11 09:50:11.810176+00\n(1 row)\n\npostgres=#  select * from pg_stat_checkpointer;\n\n num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset\n-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------\n         2 |             1 |                   0 |                 0 |                  0 |       6184 |       121 |           19140 | 2024-11-25 13:23:41.271027+00\n(1 row)<\/code><\/pre><\/div>\n\n\n\n
Son insert\/select i\u015flemimizden kaynaklanan dirty page’lerin bir k\u0131sm\u0131n\u0131 bgwriter temizlenmi\u015fti, kalan\u0131n\u0131n da otomatik checkpoint ile temizlendi\u011fini pg_stat_checkpointer view \u00e7\u0131kt\u0131s\u0131ndan g\u00f6rebiliyoruz. Checkpointer ve background writer process’lerinin istatistiklerini g\u00f6rebilece\u011fimiz bir di\u011fer view da pg_stat_io<\/strong>. Son olarak a\u015fa\u011f\u0131da da testlerimizden sonra pg_stat_io \u00e7\u0131kt\u0131s\u0131n\u0131 g\u00f6r\u00fcyoruz.<\/p>\n\n\n\n
postgres=#  select backend_type,object,context,reads,writes,writebacks,extends,op_bytes,hits,evictions,reuses,fsyncs\nfrom pg_stat_io WHERE reads <> 0 OR writes <> 0;\n\n    backend_type    |  object  | context  | reads | writes | writebacks | extends | op_bytes |  hits   | evictions | reuses | fsyncs\n--------------------+----------+----------+-------+--------+------------+---------+----------+---------+-----------+--------+--------\n client backend     | relation | bulkread |  7368 |      0 |          0 |         |     8192 |    5907 |        32 |   7336 |\n client backend     | relation | normal   |   325 |      3 |          0 |   26560 |     8192 | 6056898 |     10764 |        |      0\n autovacuum worker  | relation | normal   |   246 |      0 |          0 |      11 |     8192 |   35912 |       128 |        |      0\n autovacuum worker  | relation | vacuum   | 10994 |    320 |          0 |       0 |     8192 |   55639 |       256 |  10605 |\n standalone backend | relation | normal   |   537 |   1010 |       1010 |     661 |     8192 |   86875 |         0 |        |      0\n standalone backend | relation | vacuum   |     9 |      0 |          0 |       0 |     8192 |     906 |         0 |      0 |\n background writer  | relation | normal   |       |   5034 |       4992 |         |     8192 |         |           |        |      0\n checkpointer       | relation | normal   |       |  19431 |      19406 |         |     8192 |         |           |        |     40\n(8 rows)<\/code><\/pre><\/div>\n\n\n\n
Checkpoint i\u015flemi veri taban\u0131 performans\u0131 a\u00e7\u0131s\u0131ndan \u00e7ok \u00f6nemli bir fakt\u00f6r ve yo\u011fun veri tabanlar\u0131nda checkpoint, background write i\u015flemlerinin do\u011fru \u015fekilde tune edilmesi \u00e7ok \u00f6nemli. Bununla ilgili sorular\u0131n\u0131z olursa, bizimle ileti\u015fime <\/a>ge\u00e7ebilirsiniz.<\/p>\n\n\n\n
\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
pg_stat_bgwriter ve pg_stat_checkpointer view’lar\u0131n\u0131n \u00f6zelliklerini ve process’lerin davran\u0131\u015flar\u0131n\u0131 inceleyece\u011fimiz bu yaz\u0131m\u0131za \u00f6ncelikle konunun ge\u00e7mi\u015finden bahsederek ba\u015flayal\u0131m. Daha \u00f6ncesinde “Background Writer” (bgwriter) process’inin g\u00f6revi olan “background write” ve “checkpoint” i\u015flemleri PostgreSQL 9.2 versiyonunda iki ayr\u0131 process’e b\u00f6l\u00fcnd\u00fc ve “background write” i\u015flemini bgwriter, checkpoint i\u015flemini de checkpointer process’i yapacak \u015fekilde g\u00fcncellendi. (9.2 versiyonundan \u00f6nce checkpointer process’i yoktu). […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-388","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/388","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=388"}],"version-history":[{"count":3,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/388\/revisions"}],"predecessor-version":[{"id":393,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/388\/revisions\/393"}],"wp:attachment":[{"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}