{"id":320,"date":"2024-06-11T12:57:25","date_gmt":"2024-06-11T12:57:25","guid":{"rendered":"https:\/\/stradata.com.tr\/?p=320"},"modified":"2024-09-30T06:54:28","modified_gmt":"2024-09-30T06:54:28","slug":"postgresqlde-crash-recovery-mekanizmasini-etkileyen-faktorler","status":"publish","type":"post","link":"https:\/\/stradata.com.tr\/?p=320","title":{"rendered":"PostgreSQL’de Crash Recovery Mekanizmas\u0131n\u0131 Etkileyen Fakt\u00f6rler"},"content":{"rendered":"\n

OLTP veri tabanlar\u0131ndan beklentimiz sistemin ani\/beklenmedik kapanmas\u0131ndan sonra tekrar veri kay\u0131ps\u0131z ve tutarl\u0131 bir \u015fekilde, yani commit edilmi\u015f i\u015flemlerin tamamland\u0131\u011f\u0131, commit edilmemi\u015f i\u015flemlerin geri al\u0131nd\u0131\u011f\u0131 bir \u015fekilde tekrar a\u00e7\u0131lmas\u0131d\u0131r. Bu i\u015fleme crash recovery <\/strong>ismini veriyoruz ve PostgreSQL’de crash recovery mekanizmas\u0131n\u0131n merkezinde WAL (Write Ahead Log) <\/strong>var. PostgreSQL, bahsetti\u011fimiz beklentiyi kar\u015f\u0131lamak i\u00e7in gerekli t\u00fcm yetene\u011fe WAL sistemi sayesinde sahip, ancak bir PostgreSQL veri taban\u0131nda bu i\u015flemin sorunsuz tamamlanaca\u011f\u0131n\u0131, veri taban\u0131n\u0131n her kapan\u0131\u015fta tutarl\u0131 ve veri kay\u0131ps\u0131z a\u00e7\u0131laca\u011f\u0131n\u0131 s\u00f6yleyebilmek i\u00e7in baz\u0131 konfig\u00fcrasyonlar\u0131 incelemek gerekli. Bu yaz\u0131da crash recovery mekanizmas\u0131n\u0131 etkileyecek parametreler, varl\u0131k sebepleri ve konfig\u00fcrasyon opsiyonlar\u0131n\u0131 inceleyece\u011fiz.<\/p>\n\n\n\n

\u00d6ncelikle WAL yazma sistemini hat\u0131rlayal\u0131m; veri taban\u0131ndaki bir transaction ile ilgili bilgiler WAL dosyas\u0131na yaz\u0131lmadan \u00f6ncelikle WAL buffers<\/strong> ismini verdi\u011fimiz memory alan\u0131na yaz\u0131l\u0131r. Bunu WAL yazma i\u015flemini h\u0131zland\u0131rmak i\u00e7in yap\u0131lan bir cache uygulamas\u0131 olarak d\u00fc\u015f\u00fcnebiliriz. Ancak kullan\u0131c\u0131 transaction’\u0131 commit etti\u011finde, bu i\u015fleme ili\u015fkin t\u00fcm bilginin WAL buffers’dan disk \u00fczerindeki WAL dosyalar\u0131na yaz\u0131lmas\u0131 \u015fartt\u0131r ve bu diske yazma tamamlanmadan kullan\u0131c\u0131ya commit sonucu d\u00f6nd\u00fcr\u00fclmez. \u0130\u015fte crash recovery’de commit edilmi\u015f verinin kaybedilmemesini sa\u011flayan bu WAL yazma i\u015flemidir, \u00e7\u00fcnk\u00fc transaction’a ili\u015fkin de\u011fi\u015fiklikler tablolar\u0131n disk \u00fczerindeki dosyalar\u0131na hen\u00fcz aktar\u0131lmam\u0131\u015f olsa bile, bilgilerin diskteki WAL dosyalar\u0131nda bulunmas\u0131 sayesinde veri kayb\u0131 ya\u015fanmaz. Ancak s\u00f6z konusu WAL yazma i\u015flemindeki tek cache fakt\u00f6r\u00fc WAL buffer de\u011fildir. A\u015fa\u011f\u0131da g\u00f6r\u00fcld\u00fc\u011f\u00fc gibi bu yazma i\u015flemi sistem konfig\u00fcrasyonuna ba\u011fl\u0131 olmak \u00fczere farkl\u0131 cache mekanizmalar\u0131ndan ge\u00e7er.<\/p>\n\n\n\n

\n
\n

WAL Buffer
\u2193
OS Cache
\u2193
RAID Controller Cache
\u2193
Disk Drive Cache
\u2193
WAL Segment<\/strong><\/p>\n<\/div>\n\n\n\n

<\/div>\n<\/div>\n\n\n\n

Bir WAL write i\u015fleminin OS cache katman\u0131ndan ge\u00e7erek diske yazman\u0131n tamamland\u0131\u011f\u0131ndan emin olmak i\u00e7in PostgreSQL “fsync” <\/strong>ya da benzer sistem \u00e7a\u011fr\u0131lar\u0131ndan birini kullan\u0131r. (Sistem \u00e7a\u011fr\u0131s\u0131 -system call- bir program\u0131n i\u015fletim sistemi \u00e7ekirde\u011finden hizmet talep etmesi olarak tan\u0131mlanabilir.) Ancak bu mekanizma PostgreSQL’de bir parametre ile a\u00e7\u0131l\u0131p kapanabilmektedir. Bu parametrenin ismi de yine “fsync” dir. Varsay\u0131lan olarak “on” de\u011feri ile a\u00e7\u0131k olan bu mekanizma e\u011fer “fsync=off” \u015feklinde kapat\u0131l\u0131rsa, PostgreSQL WAL write i\u015flemlerini “fsync” veya benzeri bir validasyon sa\u011flamadan i\u015fletim sistemine g\u00f6nderecek ve diske yaz\u0131ld\u0131\u011f\u0131n\u0131 garanti edemeyecektir. Peki bu neden tercih edilebilir? Veri taban\u0131m\u0131zdaki veriler kolayl\u0131kla yeniden olu\u015fturulabiliyorsa ve mevcut veri taban\u0131m\u0131zda \u00e7ok y\u00fcksek yo\u011funluklu i\u015flemleri daha performansl\u0131 yapmak istiyorsak “fsync=off” konfig\u00fcrasyonunu de\u011ferlendirebiliriz. Ancak ani bir kapanmada veri taban\u0131m\u0131z\u0131n tutarl\u0131 a\u00e7\u0131lamayabilece\u011fini, veri bozulmas\u0131 ve veri kayb\u0131 ihtimalinin oldu\u011funu g\u00f6z \u00f6n\u00fcnde bulundurmal\u0131y\u0131z. “fsync=off” durumunda veri taban\u0131 i\u015flem s\u00fcrelerinin yakla\u015f\u0131k ne kadar de\u011fi\u015fece\u011fini g\u00f6rmek i\u00e7in a\u015fa\u011f\u0131daki k\u00fc\u00e7\u00fck testi inceleyelim. “fsync=on” durumda yakla\u015f\u0131k 70 sn. s\u00fcren INSERT i\u015fleminin, “fsync=off” set edildi\u011finde 38 sn. ye d\u00fc\u015ft\u00fc\u011f\u00fcni g\u00f6r\u00fcyoruz. UPDATE i\u015flemi ise 5 sn. den 3.6 sn. ye d\u00fc\u015f\u00fcyor. (Farkl\u0131 ko\u015fullarda ve farkl\u0131 test y\u00f6ntemlerinde etkinin de\u011fi\u015fti\u011fini g\u00f6rebilirsiniz.)<\/p>\n\n\n\n

postgres=# CREATE TABLE Customers (\n    ID  serial ,\n    Name varchar(50) NOT NULL,\n    Phone varchar(15) NOT NULL,\n    PRIMARY KEY (ID)\n);\nCREATE TABLE\n\npostgres=# \\timing\nTiming is on.\n\n\n-- fsync=on\n\npostgres=# INSERT INTO Customers( Name, Phone)\nSELECT  left(md5(random()::text),10),\nleft(md5(random()::text),10)\nFROM generate_series(1,10000000);\nINSERT 0 10000000\nTime: 69956.565 ms (01:09.957)\n\npostgres=# UPDATE Customers set Phone = 1111111111 where ID<1000000;\nUPDATE 999999\nTime: 4942.934 ms (00:04.943)\n\npostgres=# DELETE FROM Customers where ID>9000000;\nDELETE 1000000\nTime: 818.573 ms\n\n\n-- Drop and recreate table\n-- fsync=off\n\npostgres=# INSERT INTO Customers( Name, Phone)\nSELECT  left(md5(random()::text),10),\nleft(md5(random()::text),10)\nFROM generate_series(1,10000000);\nINSERT 0 10000000\nTime: 37993.590 ms (00:37.994)\n\npostgres=#  UPDATE Customers set Phone = 1111111111 where ID<1000000;\nUPDATE 999999\nTime: 3636.351 ms (00:03.636)\n\npostgres=# DELETE FROM Customers where ID>9000000;\nDELETE 1000000\nTime: 1019.045 ms (00:01.019)\n<\/code><\/pre><\/div>\n\n\n\n
Yukar\u0131da “fsync ya da benzeri sistem \u00e7a\u011fr\u0131lar\u0131” olarak ifade etti\u011fimiz y\u00f6ntemi belirleyen parametre ise wal_sync_method<\/strong> parametresidir. Bu parametrenin alaca\u011f\u0131 de\u011ferlere \u015fu linkten<\/a> eri\u015febilirsiniz. \u0130\u015fletim sistemi platformuna g\u00f6re varsay\u0131lan de\u011fer de\u011fi\u015fir. \u00d6rne\u011fin varsay\u0131lan de\u011fer Linux i\u015fletim sisteminde fdatasync<\/strong>, Windows’da ise open_datasync<\/strong>‘tir. Bu de\u011feri de\u011fi\u015ftirmek i\u00e7in t\u00fcm bu sistem \u00e7a\u011fr\u0131lar\u0131n\u0131n nas\u0131l \u00e7al\u0131\u015ft\u0131klar\u0131n\u0131 iyi anlamak ve ge\u00e7erli\/bilin\u00e7li bir sebebe sahip olmak gerekli. Aksi durumda varsay\u0131lan de\u011ferin de\u011fi\u015ftirilmesini \u00f6nermiyoruz.<\/p>\n\n\n\n
WAL yazma mekanimas\u0131nda fark\u0131nda olmam\u0131z gereken bir di\u011fer katman ise RAID controller cache<\/strong> katman\u0131d\u0131r. Sistemimizde, \u00fczerinde cache olan RAID kart\u0131 kullan\u0131yorsak t\u00fcm I\/O i\u015flemleri bu cache \u00fczerinden ge\u00e7mektedir. Cache y\u00f6ntemi olarak “Write-Back Cache”<\/strong> kullan\u0131l\u0131yorsa, WAL write i\u015flemi RAID controller cache’e yaz\u0131l\u0131p, disk drive \u00fczerine hen\u00fcz yaz\u0131lmadan PostgreSQL’e yazma i\u015fleminin tamamland\u0131\u011f\u0131 bilgisi d\u00f6nmektedir. (Bu noktada RAID cache y\u00f6ntemleri hakk\u0131nda daha fazla bilgi almak i\u00e7in bu yaz\u0131y\u0131 <\/a>okuman\u0131z\u0131 tavsiye ederiz. ) Write-Back cache kullan\u0131lacaksa RAID kart \u00fczerinde BBU (Battery Backup Unit) <\/strong>ismi verilen pillerin olmas\u0131 \u00e7ok \u00f6nemlidir. Bu piller sayesinde g\u00fc\u00e7 kesintisi durumunda RAID controller cache \u00fczerindeki veriler hemen kaybolmaz ve g\u00fc\u00e7 geri gelene kadar cache BBU yard\u0131m\u0131yla ayakta kal\u0131r. \u0130\u015fte bu katmandaki konfig\u00fcrasyon da PostgreSQL veri taban\u0131m\u0131z\u0131n WAL tutarl\u0131l\u0131\u011f\u0131n\u0131, yani ba\u015far\u0131l\u0131 bir “crash recovery” i\u015fleminin garanti edilip edilmedi\u011fini belirleyen bir di\u011fer etkendir. RAID controller katman\u0131nda oldu\u011fu gibi disk drive katman\u0131nda da cache kullan\u0131m\u0131 s\u00f6z konusu olabilir. Bu cache konfig\u00fcrasyonunu i\u015fletim sistemleri \u00fczerinden sorgulayabilir ve kapabiliriz. \u00d6rne\u011fin Linux’de SATA diskler i\u00e7in “hdparm”, SCSI diskler i\u00e7in “sdparm” komutlar\u0131 kullan\u0131labilir. Daha detayl\u0131 bilgiye \u015fu linkten<\/a> eri\u015febilirsiniz.<\/p>\n\n\n\n
WAL yazma i\u015flemini etkileyen bir ba\u015fka parametre ise “full_page_writes”<\/strong> parametresidir. PostgreSQL’de checkpoint tetiklemesi ile \u00fczerinde de\u011fi\u015fiklik yap\u0131lacak olan page’lerin (veri bloklar\u0131) de\u011fi\u015fiklik yap\u0131lmadan \u00f6nceki halinin tamam\u0131 WAL \u00fczerine yaz\u0131l\u0131r. Bu sayede page \u00fczerine yazma esnas\u0131nda ya\u015fanacak bir crash durumuna kar\u015f\u0131, page’in orijinal versiyonu WAL \u00fczerinde yedeklenmi\u015f olur. Bu yedekleme yap\u0131lmaz ise veri taban\u0131 a\u00e7\u0131l\u0131\u015f\u0131nda page \u00fczerinde bir k\u0131sm\u0131 g\u00fcncellenmi\u015f bir k\u0131sm\u0131 g\u00fcncellenmemi\u015f bir veri seti, yani bir corruption olmas\u0131 muhtemel olacakt\u0131r. Bu parametreyi full_page_writes=off<\/strong> ile kapatmak, veri taban\u0131 \u00fczerindeki i\u015flemlerde performans getirisi sa\u011flasa da, crash recovery i\u015fleminin sa\u011fl\u0131kl\u0131 yap\u0131lamama riskini yani crash durumunda veri kayb\u0131 ihtimalini ortaya \u00e7\u0131karacakt\u0131r.<\/p>\n\n\n\n
Bu noktada, testimizi tekrar ederek “full_page_writes=off” durumunda kabaca ne kadarl\u0131k bir performans kazanc\u0131 olaca\u011f\u0131n\u0131 g\u00f6relim. \u0130\u015flemleri tekrarlad\u0131\u011f\u0131m\u0131zda INSERT i\u015fleminin “fsync=on” ve “full_page_writes=off” iken yakla\u015f\u0131k 51sn. s\u00fcrd\u00fc\u011f\u00fcn\u00fc g\u00f6r\u00fcyoruz.<\/p>\n\n\n\n
-- Drop and recreate table\n-- fsync=on\n-- full_page_writes=off\n\npostgres=# INSERT INTO Customers( Name, Phone)\nSELECT  left(md5(random()::text),10),\nleft(md5(random()::text),10)\nFROM generate_series(1,10000000);\nINSERT 0 10000000\nTime: 51134.514 ms (00:51.135)\n\npostgres=#  UPDATE Customers set Phone = 1111111111 where ID<1000000;\nUPDATE 999999\nTime: 3929.867 ms (00:03.930)\n\npostgres=# DELETE FROM Customers where ID>7000000;\nDELETE 1000000\nTime: 933.309 ms\n<\/code><\/pre><\/div>\n\n\n\n
Son olarak, WAL yazma i\u015flemini direk olarak etkileyen bir ba\u015fka unsur, “Asenkron Commit”<\/strong> \u00f6zelli\u011fine bakal\u0131m. Bu \u00f6zellik asl\u0131nda varsay\u0131lan olarak senkron \u00e7al\u0131\u015fan, yani WAL dosyas\u0131na yazma i\u015flemi tamamlanmadan kullan\u0131c\u0131ya commit sonucunu d\u00f6nmeyen mekanizmay\u0131 de\u011fi\u015ftirir. “synchronous_commit=off” <\/strong>kullan\u0131larak devreye al\u0131nan “Asenkron Commit” konfig\u00fcrasyonu ile WAL kay\u0131tlar\u0131n\u0131n her commit\u2019te yaz\u0131lmas\u0131 beklenmez. WAL yazma i\u015flemi maksimum “wal_writer_delay” x 3ms olmak \u00fczere ertelenebilir. “Asenkron Commit” asl\u0131nda, crash durumunda en son i\u015flemlerin kaybolmas\u0131 riskini kabul ederek, i\u015flemlerin daha h\u0131zl\u0131 tamamlanmas\u0131na olanak tan\u0131yan bir ba\u015fka se\u00e7enektir. “synchronous_commit” parametresini on ve off d\u0131\u015f\u0131nda set edebilece\u011fimiz farkl\u0131 de\u011ferler de mevcuttur ve a\u015fa\u011f\u0131da g\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere parametrenin de\u011ferleri, e\u011fer mevcut ise standby veri taban\u0131ndaki veri tutarl\u0131l\u0131\u011f\u0131n\u0131 da kontrol etmektedir.<\/p>\n\n\n\n
    \n
  • off <\/strong>= primary’de tutarl\u0131 commit yok<\/li>\n\n\n\n
  • local <\/strong>= primary’de tutarl\u0131 commit var<\/li>\n\n\n\n
  • remote_write <\/strong>= local + PostgreSQL crash sonras\u0131 standby’da tutarl\u0131 commit<\/li>\n\n\n\n
  • on <\/strong>(varsay\u0131lan) = remote_write + OS crash sonras\u0131 standby’da tutarl\u0131 commit (senkron replication kullan\u0131l\u0131yorsa)<\/li>\n\n\n\n
  • remote_apply <\/strong>= on + standby sorgu tutarl\u0131l\u0131\u011f\u0131<\/li>\n<\/ul>\n\n\n\n
    Sonu\u00e7 olarak, inceledi\u011fimiz t\u00fcm bu konfig\u00fcrasyon se\u00e7enekleri ve parametreler, PostgreSQL veri taban\u0131m\u0131z\u0131n crash recovery davran\u0131\u015f\u0131n\u0131 etkiliyor. Bu yaz\u0131da, crash sonras\u0131 veri tabanlar\u0131m\u0131z\u0131n tutarl\u0131 a\u00e7\u0131lmas\u0131n\u0131 garanti etmek i\u00e7in hangi etkenleri incelememiz gerekti\u011fini g\u00f6rd\u00fck ve ayn\u0131 zamanda performans\u0131n tutarl\u0131l\u0131ktan daha \u00f6nemli oldu\u011fu durumlarda da hangi parametreler ile bunu sa\u011flayabilece\u011fimizi inceledik.     <\/p>\n\n\n\n
    \"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
    OLTP veri tabanlar\u0131ndan beklentimiz sistemin ani\/beklenmedik kapanmas\u0131ndan sonra tekrar veri kay\u0131ps\u0131z ve tutarl\u0131 bir \u015fekilde, yani commit edilmi\u015f i\u015flemlerin tamamland\u0131\u011f\u0131, commit edilmemi\u015f i\u015flemlerin geri al\u0131nd\u0131\u011f\u0131 bir \u015fekilde tekrar a\u00e7\u0131lmas\u0131d\u0131r. Bu i\u015fleme crash recovery ismini veriyoruz ve PostgreSQL’de crash recovery mekanizmas\u0131n\u0131n merkezinde WAL (Write Ahead Log) var. PostgreSQL, bahsetti\u011fimiz beklentiyi kar\u015f\u0131lamak i\u00e7in gerekli t\u00fcm yetene\u011fe WAL […]<\/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-320","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/320","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=320"}],"version-history":[{"count":17,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/320\/revisions"}],"predecessor-version":[{"id":374,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=\/wp\/v2\/posts\/320\/revisions\/374"}],"wp:attachment":[{"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=320"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=320"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/stradata.com.tr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=320"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}