POSTGRESQL PERFORMANCE — STATISTICS (PG_STAT_STATEMENTS)

İstatistikler veritabanındaki viewleri kullanarak ulaşıp veritabanı çalışması hakkında detaylı bilgilere ulaşabileceğimiz bilgileri içermektedir. Bu Bilgiler nelerdir;

  • Tablo İstatistikleri
  • Time-Consimuming queries
  • Tablo I/O
  • Index İstatistikleri
  • Index I/O
  • Waits vs…

En önemli ve hızlı sonuç alınabilecek Performance view pg_stat_statements  view idir. Sistem yüküne neden olan darboğaz tespiti için kullanabiliriz.

PG_STAT_STATEMENTS

Sistemde çalışan sorgular hakkında detaylı bilgiyi bu viewden öğrenebiliriz, ancak bu modul varsayılan olarak kurulu gelmemektedir. Aşağıda bu modulün kurulumu anlatılmaktadır.

  • postgresql.conf dosyasında shared_preload_libraries comment yapılmış durumda ise comment kısmını kaldırılmalıdır.
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- pg_stat_statements.max maximum izlenecek ifade sayısıdır defaultta 5000 olarak gelir.
/*  pg_stat_statements.track Modülün hangi ifadeleri izlediğini kontrol eder. Geçerli değerler top(doğrudan istemciler tarafından yayınlanan ifadeleri), all(Tüm ifadeleri) ve none(istatistikleri toplamayı devre dışı bırak).
*/
  • restart database
  • CREATE EXTENSION pg_stat_statements;” ifadesini çalıştıralım
  • postgresql13-contrib paketi yüklü değilse yükleyelim;
postgres=#  create extension pg_stat_statements;
CREATE EXTENSION

$ psql pgbench
pgbench=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# \d pg_stat_statements
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          |
 dbid                | oid              |           |          |
 queryid             | bigint           |           |          |
 query               | text             |           |          |
 plans               | bigint           |           |          |
 total_plan_time     | double precision |           |          |
 min_plan_time       | double precision |           |          |
 max_plan_time       | double precision |           |          |
 mean_plan_time      | double precision |           |          |
 stddev_plan_time    | double precision |           |          |
 calls               | bigint           |           |          |
 total_exec_time     | double precision |           |          |
 min_exec_time       | double precision |           |          |
 max_exec_time       | double precision |           |          |
 mean_exec_time      | double precision |           |          |
 stddev_exec_time    | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |
 wal_records         | bigint           |           |          |
 wal_fpi             | bigint           |           |          |
 wal_bytes           | numeric          |           |          |

Buradan her sogunun kaç defa çağırıldığını ve çalışma sürelerini öğrenebileceğiz. Bu viewin anlamlandırılmasına kısaca değinecek olursak, stddev bize sorgunun çalışma zamanın değişken olup olmadığını, shared_ ile başlayan kısımlar sorgunun cevabının shared pool ile ilişkisini göstermeketedir. Değişken çalışma süreleri ise varsa aşağıdaki maddeler değerlenirilmelidir.

  • Lock ve Concurrency durumları
  • Farklı planlar mı kullanılıyor.

TOP 10 SQL for (POSTGRESQL-13) bazı column isimleri postgresql versiyonlarında farklı olduğundan bu sorgu farklı versiyonda çalışmayacaktır.

SELECT round((100 * total_exec_time / sum(total_exec_time)                       
           OVER ())::numeric, 2) percent,                        
           round(total_exec_time::numeric, 2) AS total,                  
           calls,                                                   
           round(mean_exec_time::numeric, 2) AS mean,                    
           substring(query, 1, 40)                                  
 FROM  pg_stat_statements                                               
           ORDER BY total_exec_time DESC                                               
           LIMIT 10;


 percent |  total  | calls | mean  |                substring
---------+---------+-------+-------+------------------------------------------
   99.92 | 5896.48 |   528 | 11.17 | /*pga4dash*/                            +
         |         |       |       | SELECT $1 AS chart_name, pg
    0.03 |    1.96 |     2 |  0.98 | SELECT set_config($1,$2,$3) FROM pg_sett
    0.02 |    1.13 |     3 |  0.38 | SELECT DISTINCT att.attname as name, att
    0.01 |    0.45 |     3 |  0.15 | SELECT at.attname, ty.typname, at.attnum
    0.00 |    0.22 |     2 |  0.11 | select * from pg_stat_statements
    0.00 |    0.16 |     1 |  0.16 | SELECT round(($1 * total_exec_time / sum
    0.00 |    0.12 |     2 |  0.06 | SELECT                                  +
         |         |       |       |              gss_authenticated, e
    0.00 |    0.11 |     3 |  0.04 | SELECT at.attname, at.attnum, ty.typname
    0.00 |    0.10 |     1 |  0.10 | SELECT oid, pg_catalog.format_type(oid,
    0.00 |    0.10 |     2 |  0.05 | SELECT      +
         |         |       |       |             roles.oid as id, role

Genelde sistemde kötü çalışan ve darboğaz yatan bir sql varsa yukarıdaki listede çıkacaktır.

Veritabanı düzeyindeki istatistikler ise pg_stat_database ile incelenir.

postgres=# \d pg_stat_database
                        View "pg_catalog.pg_stat_database"
        Column         |           Type           | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
 datid                 | oid                      |           |          |
 datname               | name                     |           |          |
 numbackends           | integer                  |           |          |
 xact_commit           | bigint                   |           |          |
 xact_rollback         | bigint                   |           |          |
 blks_read             | bigint                   |           |          |
 blks_hit              | bigint                   |           |          |
 tup_returned          | bigint                   |           |          |
 tup_fetched           | bigint                   |           |          |
 tup_inserted          | bigint                   |           |          |
 tup_updated           | bigint                   |           |          |
 tup_deleted           | bigint                   |           |          |
 conflicts             | bigint                   |           |          |
 temp_files            | bigint                   |           |          |
 temp_bytes            | bigint                   |           |          |
 deadlocks             | bigint                   |           |          |
 checksum_failures     | bigint                   |           |          |
 checksum_last_failure | timestamp with time zone |           |          |
 blk_read_time         | double precision         |           |          |
 blk_write_time        | double precision         |           |          |
 stats_reset           | timestamp with time zone |           |          |

Top QUERY SQL

TOP 10 I/O Query

 select userid::regrole, dbid, query
    from pg_stat_statements
    order by (blk_read_time+blk_write_time)/calls desc
    limit 10;

Top 10 Consuming Query

SELECT round((100 * total_time / sum(total_time)                       
           OVER ())::numeric, 2) percent,                        
           round(total_time::numeric, 2) AS total,                  
           calls,                                                   
           round(mean_time::numeric, 2) AS mean,                    
           substring(query, 1, 200)                                  
 FROM  pg_stat_statements                                               
           ORDER BY total_time DESC                                               
           LIMIT 10; 

Top 10 Response Time query

select userid::regrole, dbid, query
    from pg_stat_statements
    order by stddev_time desc
    limit 10;

Top 10 Memory Query

select userid::regrole, dbid, query
    from pg_stat_statements
    order by (shared_blks_hit+shared_blks_dirtied) desc
    limit 10;

Top 10 Temp Using Query

yugabyte=# select userid::regrole, dbid, query
    from pg_stat_statements
    order by temp_blks_written desc
    limit 10;

İstatistikleri Resetlemek için;

yugabyte=# select pg_stat_statements_reset();