{"id":338,"date":"2024-09-19T10:50:53","date_gmt":"2024-09-19T10:50:53","guid":{"rendered":"https:\/\/stradata.com.tr\/?p=338"},"modified":"2024-09-30T19:20:50","modified_gmt":"2024-09-30T19:20:50","slug":"pg_stat_io-ile-veri-tabani-kumulatif-i-o-istatistiklerinin-incelenmesi","status":"publish","type":"post","link":"https:\/\/stradata.com.tr\/?p=338","title":{"rendered":"PG_STAT_IO ile Veri Taban\u0131 K\u00fcm\u00fclatif I\/O \u0130statistiklerinin \u0130ncelenmesi"},"content":{"rendered":"\n

PostgreSQL 16 versiyonu ile birlikte gelen pg_stat_io, <\/strong>veri tabanlar\u0131ndaki I\/O ili\u015fkili i\u015flemlere ait istatistikleri k\u00fcm\u00fclatif olarak ve t\u00fcm cluster i\u00e7in <\/strong>g\u00f6steren, do\u011fru kullan\u0131ld\u0131\u011f\u0131nda ve yorumland\u0131\u011f\u0131nda olduk\u00e7a faydal\u0131 olabilecek bir view. View i\u00e7erisinde I\/O i\u015flemleri backend process t\u00fcrlerine g\u00f6re gruplanm\u0131\u015f olarak g\u00f6steriliyor, \u00f6rne\u011fin veri taban\u0131na ba\u011flant\u0131 kuran kullan\u0131c\u0131lar\u0131n i\u015flemlerine ili\u015fkin istatistikler “backend_type”=”client backend”<\/strong> \u015feklinde bir arada g\u00f6steriliyor. Di\u011fer bir kolon “context”<\/strong> ise I\/O i\u015fleminin i\u00e7eri\u011fini, bir di\u011fer anlamda \u00e7e\u015fidini belirtiyor ve “normal”, “vacuum”, “bulkread”, “bulkwrite” <\/strong>de\u011ferlerini alabiliyor. View kolonlar\u0131 ile ilgili detayl\u0131 bilgiyi buradan <\/a>inceleyebilirsiniz. Yaz\u0131m\u0131zda pg_stat_io view’\u0131n\u0131n sundu\u011fu i\u00e7eri\u011fi daha iyi anlayabilmek ad\u0131na, basit i\u015flemler sonucu ortaya \u00e7\u0131kan istatistikleri inceleyece\u011fiz.<\/p>\n\n\n\n

Testlerimize yeni bir cluster olu\u015fturarak ve k\u00fcm\u00fclatif olarak doldurulacak view’\u0131n ba\u015flang\u0131\u00e7 \u200b\u200bdurumunu sorgulayarak ba\u015fl\u0131yoruz.<\/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 autovacuum worker | relation | normal  |     8 |      0 |          0 |       0 |     8192 | 19354 |         0 |        |      0\n client backend    | relation | normal  |   144 |      0 |          0 |       0 |     8192 |  2994 |         0 |        |      0\n(2 rows)<\/code><\/pre><\/div>\n\n\n\n
\u0130ki kolonlu bir test tablosu yaratarak bu tablo i\u00e7erisine 15 bin random kay\u0131t insert edelim ve pg_stat_io view’\u0131n\u0131 sorgulayal\u0131m.<\/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,15000);\n\npostgres=# 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 autovacuum worker | relation | normal  |    32 |      0 |          0 |       2 |     8192 | 25258 |         0 |        |      0\n client backend    | relation | normal  |   230 |      0 |          0 |      73 |     8192 | 19305 |         0 |        |      0\n(2 rows)<\/code><\/pre><\/div>\n\n\n\n
Insert ile veri taban\u0131na yazma i\u015flemi yapm\u0131\u015f olmam\u0131za ra\u011fmen “writes” de\u011feri halen 0 g\u00f6r\u00fcn\u00fcyor. G\u00f6z\u00fcm\u00fcze \u00e7arpan de\u011fi\u015fiklik ise “client backend” tipi i\u00e7in “hits” kolonundaki de\u011ferin de\u011fi\u015fimi<\/strong>. Bu noktada “hits”<\/strong> kolonunu a\u00e7\u0131klamakta fayda var. Bu kolon d\u00f6k\u00fcmantasyonda shared buffers’da istenen blo\u011fun bulunma \/ eri\u015filme say\u0131s\u0131 olarak tan\u0131mlanm\u0131\u015f. PostgreSQL’de bir tabloya insert edilen veri direk olarak diskteki data file’a de\u011fil, shared buffer’a yani RAM \u00fczerine yaz\u0131l\u0131r. Bizim Insert i\u015flemimiz de, shared buffer \u00fczerindeki bo\u015f alanlara yap\u0131lan yazma ile “client backend” tipi i\u00e7in “hits” de\u011ferini artt\u0131rd\u0131.<\/p>\n\n\n\n
\u015eimdi veri taban\u0131nda manuel olarak checkpoint i\u015flemini tetikleyerek view’\u0131 yeniden sorguluyoruz.<\/p>\n\n\n\n
postgres=# checkpoint;\n\npostgres=# 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 autovacuum worker | relation | normal  |    32 |      0 |          0 |       2 |     8192 | 25589 |         0 |        |      0\n client backend    | relation | normal  |   230 |      0 |          0 |      73 |     8192 | 19358 |         0 |        |      0\n checkpointer      | relation | normal  |       |    111 |        111 |         |     8192 |       |           |        |     32\n(3 rows)<\/code><\/pre><\/div>\n\n\n\n
Sorgu sonucumuzda, Checkpointer process’i taraf\u0131ndan 111 adet write (diske yazma) i\u015flemi yap\u0131ld\u0131\u011f\u0131na ili\u015fkin bir kay\u0131t geldi. Her i\u015flem 8192 byte (op_bytes <\/strong>kolonunun de\u011feri) oldu\u011funa g\u00f6re yakla\u015f\u0131k 880 KB verinin yaz\u0131ld\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz. Bu a\u015famada tablo boyutunu sorgulad\u0131\u011f\u0131m\u0131zda tablomuzun da yakla\u015f\u0131k 600KB oldu\u011funu g\u00f6r\u00fcyoruz. Internal i\u015flemleri de hesaba katt\u0131\u011f\u0131m\u0131zda de\u011ferler tutarl\u0131 ve shared buffers’da tutulan 15 bin kay\u0131tl\u0131k verinin checkpoint i\u015flemi ile birlikte diske aktar\u0131ld\u0131\u011f\u0131n\u0131 s\u00f6yleyebiliriz. \u00c7\u0131karaca\u011f\u0131m\u0131z sonu\u00e7 ise “client backend” tipinde “write” <\/strong>say\u0131s\u0131n\u0131n artmad\u0131\u011f\u0131n\u0131 g\u00f6rmemizin, veri taban\u0131nda kullan\u0131c\u0131lar\u0131n yazma i\u015flemi yapm\u0131yor olduklar\u0131 anlam\u0131na gelmedi\u011fi.<\/p>\n\n\n\n
\u015eimdi ise tablomuzdaki verileri silerek, silme i\u015fleminin sonucunu inceleyelim. Yaln\u0131z bu silme i\u015flemini tek bir delete c\u00fcmlesi ile de\u011fil her defas\u0131nda random bir de\u011fer silmeyi 1000 defa tekrar edecek \u015fekilde \u00e7al\u0131\u015ft\u0131r\u0131yoruz.<\/p>\n\n\n\n
postgres=# do\n$$\ndeclare \n  i record;\nbegin\n  for i in 1..1000 loop\n\tdelete from io_test where c1=(SELECT floor(random() * 999 + 1)::int);\n  end loop;\nend;\n$$\n;\n\npostgres=# checkpoint;\n\npostgres=# 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 autovacuum worker | relation | normal  |    32 |      0 |          0 |       2 |     8192 |   26638 |         0 |        |      0\n client backend    | relation | normal  |   277 |      0 |          0 |      73 |     8192 | 1273446 |         0 |        |      0\n checkpointer      | relation | normal  |       |    111 |        111 |         |     8192 |         |           |        |     32\n(3 rows)<\/code><\/pre><\/div>\n\n\n\n
Silme i\u015fleminin yine “client backend” tipindeki “hits”<\/strong> de\u011ferini, bu defa \u00e7ok daha y\u00fcksek bir oranda artt\u0131rd\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz. Bu y\u00fcksek art\u0131\u015f\u0131n nedeni her bir delete c\u00fcmlesinin index bulunmayan tablonun tamam\u0131n\u0131 okumas\u0131, bu verinin de shared buffer<\/strong>‘da bulunmas\u0131.
\u015eimdi ise tabloya daha b\u00fcy\u00fck miktarda veri y\u00fckleyelim.<\/p>\n\n\n\n
postgres=# INSERT INTO io_test( c1, c2)\nSELECT floor(random() * 999 + 1)::int,\nleft(md5(random()::text),3)\nFROM generate_series(1,3000000);\n\npostgres=# checkpoint;\n\npostgres=# 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 autovacuum worker | relation | normal  |    32 |      0 |          0 |       4 |     8192 |   35316 |         0 |        |      0\n client backend    | relation | normal  |   277 |      0 |          0 |    7113 |     8192 | 2885411 |         0 |        |      0\n checkpointer      | relation | normal  |       |   7166 |       7166 |         |     8192 |         |           |        |     46\n(3 rows)<\/code><\/pre><\/div>\n\n\n\n
3 milyon sat\u0131r random veriyi insert edip sonras\u0131nda manuel checkpoint ald\u0131k. Hemen ard\u0131ndan pg_stat_io view’\u0131n\u0131 sorgulad\u0131\u011f\u0131m\u0131zda bekledi\u011fimiz gibi “client backend” tipindeki “hits” de\u011ferinin ve checkpoint’ten gelen “write” de\u011ferinin artmas\u0131n\u0131n yan\u0131nda bir de\u011ferin daha b\u00fcy\u00fck miktarda artt\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz. Bu da “client backend” tipindeki “extends” de\u011feri<\/strong>. Bu de\u011fer tablo extend, yani tablonun disk \u00fczerindeki alan\u0131n\u0131 b\u00fcy\u00fctme i\u015fleminin ka\u00e7 defa yap\u0131ld\u0131\u011f\u0131 g\u00f6sterir ve testimizde 3 milyon kay\u0131t ile 100MB’a ula\u015fan tabloda 7000’den fazla kez bu i\u015flemin yap\u0131ld\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz.<\/p>\n\n\n\n
Bu defa tablodaki verilerin yar\u0131s\u0131n\u0131 update edelim.<\/p>\n\n\n\n
postgres=# update io_test set c1=111 where c1<500;\nUPDATE 1499783\n\npostgres=# checkpoint;\n\npostgres=# 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 autovacuum worker | relation | normal  |    32 |      0 |          0 |       4 |     8192 |    46692 |         0 |        |      0\n autovacuum worker | relation | vacuum  | 22780 |   9670 |          0 |       0 |     8192 |   124412 |        32 |  22748 |\n client backend    | relation | normal  |   279 |      0 |          0 |   14145 |     8192 | 10065347 |         0 |        |      0\n checkpointer      | relation | normal  |       |  17708 |      17708 |         |     8192 |          |           |        |     50\n<\/code><\/pre><\/div>\n\n\n\n
Update i\u015flemi bittikten bir s\u00fcre sonra autovacuum i\u015flemi devreye girmi\u015f ve “backend_type”=”autovacuum worker” ve “context”=”vacuum” <\/strong>olmak \u00fczere yeni bir istatisti\u011fin geldi\u011fini g\u00f6r\u00fcyoruz. Bu i\u015flemin shared buffers dan yapt\u0131\u011f\u0131 okuman\u0131n yan\u0131nda diskten de okuma ve yazma yapt\u0131\u011f\u0131n\u0131, bu sayede test tablomuzda “dead row” <\/strong>lar\u0131n temizlendi\u011fini ve bo\u015falan alan\u0131n yeniden kullan\u0131labilir hale geldi\u011fini s\u00f6yleyebiliriz.<\/p>\n\n\n\n
\u015eimdiye kadar yapt\u0131\u011f\u0131m\u0131z i\u015flemler shared buffers i\u00e7erisinde doluluk yaratmad\u0131 ve bu nedenle yapt\u0131\u011f\u0131m\u0131z i\u015flemlerin diske yaz\u0131lmas\u0131 her zaman checkpoint i\u015flemi ile ger\u00e7ekle\u015fti. Ancak PostgreSQL’de dirty buffer’\u0131n diske yaz\u0131lmas\u0131 g\u00f6revini checkpoint d\u0131\u015f\u0131nda bir process daha yapar, bu process “background writer<\/strong>“d\u0131r ve shared buffers \u00fczerinde her zaman bo\u015f alan olmas\u0131n\u0131 sa\u011flamak amac\u0131yla gerekti\u011finde devreye girerek diske yazma i\u015flemini ger\u00e7ekle\u015ftirir. Biz de \u015fimdi shared buffers kullan\u0131m\u0131n\u0131 artt\u0131racak \u015fekilde, tablomuzdaki verinin tamam\u0131n\u0131 yeniden tablomuza insert ediyoruz.<\/p>\n\n\n\n
postgres=# insert into io_test select * from io_test ;\n\npostgres=# 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 autovacuum worker | relation | normal  |    32 |      0 |          0 |       4 |     8192 |    48536 |         0 |        |      0\n autovacuum worker | relation | vacuum  | 22780 |   9670 |          0 |       0 |     8192 |   124412 |        32 |  22748 |\n client backend    | relation | normal  |   279 |      0 |          0 |   14145 |     8192 | 10123567 |         0 |        |      0\n checkpointer      | relation | normal  |       |  32109 |      32109 |         |     8192 |          |           |        |     50\n background writer | relation | normal  |       |   6564 |       6528 |         |     8192 |          |           |        |      0\n<\/code><\/pre><\/div>\n\n\n\n
\u0130\u015fte bu operasyon shared buffers kullan\u0131m\u0131nda yer bask\u0131s\u0131 olu\u015fturdu ve pg_stat_io view’\u0131n\u0131n \u00e7\u0131kt\u0131s\u0131nda “backend_type”=”background writer” <\/strong>olmak \u00fczere yeni bir istatistik g\u00f6rd\u00fck. Bu backend process’in checkpoint ile birlikte yine diske yazma i\u015flemi yapt\u0131\u011f\u0131n\u0131 da istatistiklerden anlayabiliyoruz.<\/p>\n\n\n\n
T\u00fcm bu testler ile, pg_stat_io view’\u0131n\u0131n incelenmesinde dikkat edilmesi gereken baz\u0131 noktalar\u0131n \u00fczerinde durmak istedik. View’\u0131n \u00e7\u0131kt\u0131s\u0131nda g\u00f6rd\u00fc\u011f\u00fcn\u00fcz di\u011fer backend process’lerin ve yaz\u0131m\u0131zda incelemedi\u011fimiz kolonlar\u0131n anlamlar\u0131 i\u00e7in d\u00f6k\u00fcmantasyonu <\/a>inceleyebilir, sorular\u0131n\u0131z i\u00e7in bizimle ileti\u015fime <\/a>ge\u00e7ebilirsiniz. <\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL 16 versiyonu ile birlikte gelen pg_stat_io, veri tabanlar\u0131ndaki I\/O ili\u015fkili i\u015flemlere ait istatistikleri k\u00fcm\u00fclatif olarak ve t\u00fcm cluster i\u00e7in g\u00f6steren, do\u011fru kullan\u0131ld\u0131\u011f\u0131nda ve yorumland\u0131\u011f\u0131nda olduk\u00e7a faydal\u0131 olabilecek bir view. View i\u00e7erisinde I\/O i\u015flemleri backend process t\u00fcrlerine g\u00f6re gruplanm\u0131\u015f olarak g\u00f6steriliyor, \u00f6rne\u011fin veri taban\u0131na ba\u011flant\u0131 kuran kullan\u0131c\u0131lar\u0131n i\u015flemlerine ili\u015fkin istatistikler “backend_type”=”client backend” \u015feklinde bir arada […]<\/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-338","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/338","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=338"}],"version-history":[{"count":14,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/338\/revisions"}],"predecessor-version":[{"id":386,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/338\/revisions\/386"}],"wp:attachment":[{"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=338"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=338"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=338"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}