Tag: maintenance

  Postgresql mempunya istilah untuk fragmentasi yaitu bloat. Bloat ini muncul terhadap dua object postgresql yaitu tabel dan index. Artikel ini akan menjelaskan bagaimana cara kita mencari tabel yang ter-fragmentasi. Apabila anda menemukannya, maka tabel tersebut harus di defrag.

Untuk mencari tabel dan index mana saja yang memiliki Bloat, kita dapat menggunakan script yang telah disediakan oleh postgreSQL yang dapat dilihat disini. Berikut saya copy paste dari halaman wiki PostgreSQL:

SELECT current_database(), schemaname, tablename,
       /*reltuples::bigint, relpages::bigint, otta,*/
       ROUND( CASE WHEN otta=0
                   THEN 0.0 ELSE sml.relpages/otta::numeric
                   END,1) AS tbloat,
       CASE WHEN relpages < otta THEN 0 
                ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
       iname, /*ituples::bigint, ipages::bigint, iotta,*/
       ROUND(CASE WHEN iotta=0 OR ipages=0 
                   THEN 0.0 ELSE ipages/iotta::numeric 
                   END,1) AS ibloat,
       CASE WHEN ipages < iotta THEN 0 
               ELSE bs*(ipages-iotta) END AS wastedibytes
FROM 
( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs,
             CEIL((cc.reltuples*((datahdr+ma- 
                    (CASE WHEN datahdr%ma=0 THEN ma 
                             ELSE datahdr%ma END))+nullhdr2+4)
                     )/(bs-20::float)) AS otta,
             COALESCE(c2.relname,'?') AS iname, 
             COALESCE(c2.reltuples,0) AS ituples, 
             COALESCE(c2.relpages,0) AS ipages,
             COALESCE(CEIL((c2.reltuples*(datahdr-12)
                           )/(bs-20::float)),0) AS iotta 
                           -- very rough approximation, assumes all cols
  FROM 
  (  SELECT ma,bs,schemaname,tablename,
               ( datawidth+(hdr+ma-
                 (case when hdr%ma=0 THEN ma ELSE hdr%ma END))
               )::numeric AS datahdr,
               ( maxfracsum*(nullhdr+ma-
                 (case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))
               ) AS nullhdr2
    FROM 
    (  SELECT schemaname, tablename, hdr, ma, bs,
                  SUM((1-null_frac)*avg_width) AS datawidth,
                  MAX(null_frac) AS maxfracsum,
                  hdr+( SELECT 1+count(*)/8
                           FROM pg_stats s2
                           WHERE null_frac<>0 
                               AND s2.schemaname = s.schemaname 
                               AND s2.tablename = s.tablename
                  ) AS nullhdr
       FROM pg_stats s, 
       ( SELECT ( SELECT current_setting('block_size')::numeric) AS bs,
                    CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') 
                    THEN 27 ELSE 23 
                    END AS hdr,
                    CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
         FROM ( SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn
    ON ( cc.relnamespace = nn.oid 
            AND nn.nspname = rs.schemaname 
            AND nn.nspname <> 'information_schema' )
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

Yang perlu diperhatikan dari skrip ini adalah :

• Skrip ini diperuntukkan sebagai informasi penunjang, untuk memastikan keakuratannya harus di cek dengan modul contrib pgstattuple atau pg_freespacemap.
• Kolom tbloat adalah rasio fragmentasi di table antara besarnya table dan free space yang dimilikinya.
• Kolom wastedbytes adalah besarnya free space yang dimiliki tabel tersebut dalam satuan byte yang tidak dapat dipergunakan.
• Kolom ibloat adalah rasio fragmentasi di index antara besarnya index dan free space yang dimilikinya
• Kolom watedibytes adalah besarnya free space yang dimiliki index tersebut dalam satuan byte yang tidak dapat dipergunakan.

Apabila skrip ini dijalankan, maka akan kita dapati tabel dan index yang memiliki bloat. Perhatikan kolom kolom rasio-nya dan gabungkan dengan kolom wastebyte-nya. Perhatikan pola antar tabel. Rasio 1 pada tabel A, nilai wastebyte nya dapat berbeda dengan tabel B untuk rasio yang sama.

Lantas bagaimana menentukan mana tabel yang perlu di defrag ? Jawabannya adalah kenalilah karakteristik database anda.

Database saya memiliki beberapa tabel yang karakteristiknya berbeda. Ada tabel tabel yang sering kali memakai proses update secara perlahan lahan. Ada juga tabel yang lebih memakai metode proses delete-insert daripada update. Ada juga yang lebih banyak proses insertnya daripada proses updatenya. Ada juga tabel yang cepat sekali pertumbuhan datanya ada yang lambat.

Nah, bagaimana cara saya menentukan tabel yang perlu defrag adalah dengan membatasi ratio tbloat dan ibloat > 1.5 atau yang memiliki wastebyte > 3Mb. Dengan syarat seperti itu saya akan mendapati 5-6 tabel yang perlu di defrag setiap minggu nya. Saya tinggal melakukan defrag.

Bagaimana dengan anda ?