สรุปเกี่ยวกับค่า parameter ที่จำเป็นสำหรับการ tuning PostgreSQL database ก่อนที่จะ tuning มาทำความเข้าใจเกี่ยวกับ architecture ของ Posgresql ตามรูปข้างล่าง จะเห็นว่า Shared Memory เป็น resource สำคัญที่ทุก backgroud process และ data file ต้องการที่จะเข้าถึงและใช้งาน

Shared Memory คือ Memory ที่ reserve ไว้สำหรับ database caching และ transaction log caching ประกอบด้วย shared buffer และ WAL buffer

shared buffer ช่วยทำให้ไม่ต้องอ่านข้อมูลจาก Disk ตลอดเวลา จากการอ่านข้อมูลขึ้นมาพักไว้ที่ shared buffer และไม่ต้องอ่านจาก disk อีกครั้งเมื่อต้องการข้อมูลเดิม

WAL buffer เป็นที่เก็บข้อมูลที่มีการเปลี่ยนแปลงต่างๆ ใน database ใน memory ก่อนที่จะมีการเขียนลง WAL file เป็นรอบเวลา WAL file มีความจำเป็นมากในการทำ backup และ restore

PostgreSQL process type ประกอบด้วย 4 ประเภทดังนี้

1. Postmaster (Daeomon) process
2. Background process
3. Backend process
4. Client process

Postmaster process เป็น process ที่ start ขึ้นมาตอนที่ PostgreSQL เริ่มทำงาน โดยจะจัดการเรื่อง Recovery, initializes shared memory และสั่งให้ background process เริ่มทำงาน นอกจากนั้น Postmaster process ยังสร้าง backend process ขึ้นมาทุกครั้งที่ client เรียกใช้ PostgreSQL

Background Process ทำหลายหน้าที่ดังนี้

Method	Role
logger	เขียน error message ลง file
checkpointer writer	ตรวจสอบและเขียนข้อมูลลง file เมื่อเจอ dirty buffer
wal writer	เขียน WAL buffer ลง WAL file
autovacuum launcher	จะมีการ start autovacuum เพื่อ clean up space ที่ไม่ถูกใช้กลับมาใช้อีกครั้ง ถ้ามีการ set autovacuum=on
archiver	archive.log กรณีที่ setup WAL file ในแบบ direcoty copy
stats collector	เก็บข้อมูลของแต่ละ session (pg_stat_activty) และการทำงานของแต่ละ table (pg_stat_all_tables) และข้อมูล stat ของ database

Backend Process เป็น process ที่จัดการเกี่ยวกับ query ของ user process และส่ง result ที่ได้จาก query จำนวน process ที่จะรองรับ user request จะถูกกำหนดในค่า max_connection ซึ่งมีค่า default ที่ 100 connection ในการ query แต่ละครั้งจำเป็นต้องใช้ local memory โดยมี parameter หลักๆ คือ

1. work_mem เนื้อที่ใช้สำหรับการ sorting, สร้าง bitmap, hash joins และ merge joins. ค่า default คือ 4 MB
2. Maintenance_work_mem เนื้อที่สำหรับ Vacuum และ CREATE INDEX ค่า default คือ 64MB
3. Temp_buffer เนื้อที่หรับสร้าง Temporary Table ค่า default คือ 8 MB

Client procces คือ background process ที่ assign ให้กับทุกๆ backend user connection โดย Postmaster process จะสร้าง process ลูกเพื่อให้บริการสำหรับแต่ละ user connection โดยเฉพาะ

Postgresql Tuning parameter

ในการสร้าง Postgrsql จะต้องตรวจสอบ spec ของแต่ละ version เช่น ขนาดของเครื่อง ที่ต้องมี resource ที่เพียงพอ เช่นจะต้องมี mininum resource ที่

• CPU 1 core
• 2 GB of RAM
• 512 MB of Harddisk

Operating System

เลือกใช้ Linux distribution ที่เหมาะสมสำหรับ run Postgresql ส่วนใหญ่จะเป็น Redhat based เช่น RHEL หรือ CentOS และเลือกใช้ kernel ที่ up to date

CPU

Postgresql จะสร้าง threads (backend process) สำหรับแต่ละ connection เสมอ ซึ่งต้องการ cpu core พี่เพียงพอเพื่อจะทำให้สามารถสร้าง process ได้ ดังนั้นจำนวน cpu core จึงขึ้นกับจำนวน concurrent connection และ concurrent query

ค่าเริ่มต้นของ cpu ควรจะเริ่มที่ 4 cores เพื่อรองรับ application ขนาดเล็ก ที่มีจำนวน connection อยู่ที่ประมาณ 24 connections แต่ถ้าเกิดเหตุการณ์ overload ก็ต้องเพิ่มจำนวน core มากขึ้น บางกรณีอาจต้องมีจำนวน core มากกว่า 48 cores เพื่อรองรับจำนวน connection หลายร้อย connections

Tuning Tips: เทียบจำนวน transaction per second (TPS) กรณีที่ใช้ feature hyper-threading จะได้ TPS น้อยกว่าไม่ใช้ สำหรับ query ที่ไม่ซับซ้อนและมีการเรียกใช้เยอะ จำนวน core จะมีผลต่อ TPS มากกว่าความเร็วของ cpu

Memory

Memory สำคัญมากๆ สำหรับ Postgresql performance และค่าที่สำคัญคือ shared_buffers ซึ่งเป็นเนื้อที่สำหรับ postgresql ใช้เพื่อเป็น data caching การที่มี data ถูก load เข้าไปใน cache มากเท่าไหร่ ก็จะทำให้ query ทำงานเร็วขึ้นเท่านั้น นอกจากนั้นพื้นที่นี้ยังใช้สำหรับ sorting เพื่อ return ผลกลับไปยัง client กลับกันถ้าทุกครั้งต้องมีการอ่านจาก disk เนื่องจากไม่มีข้อมูลอยู่ใน cache หรือเนื่อที่สำหรับ cache น้อยก็จะทำให้การทำงานช้ากว่าจะได้ผลลัพธ์

Tuning Tips: ค่าเริ่มต้นสำหรับ shared_buffers คือ 25% ของ memory ที่เหลือทั้งหมด เพื่อให้มีพื้นที่สำหรับ OS ใช้ในการ caching data และ ​run process อื่นๆ ด้วยนอกเหนือจาก database การเพิ่ม work_mem สามารถทำให้ sorting ทำงานได้เร็วขึ้น แต่ต้องไม่มากจนเกินไป เพราะจะทำให้ memory ของทั้งเครื่องถูกใช้จนหมดได้ เพราะทุกครั้งที่มีการ query และ sorting พื้นที่ส่วนนี้จะถูกใช้ผันตามจำนวน query และ sort จึงต้องเริ่ม tuning ด้วยค่าที่น้อยๆ ก่อน แล้วค่อยๆ เพิ่มขึ้นจนได้ค่าที่เหมาะสม แล้วควรจะใช้ free command (free -h) เพือ set ค่า effective_cache_size ตามจำนวน memory ที่เหลืออยู่ รวมกับ cache ทั้งนี้ planner ใน postgresql ก็จะรู้ว่าควรจะใช้ memory ได้เท่าไหร่ตามที่ OS cache มีให้ใช้อยู่จริง

Disk

ความเร็วของการอ่านเขียน Disk มีผลอย่างมากต่อ performance ของ database เพราะต้องมีการ load ข้อมูลจาก disk ไปยัง shared_buffer ในทุกครั้งที่ query และการ sync จาก memory ไปยัง disk

Tuning Tips: กรณีของ SSD disk set ค่า random_page_cost ที่ 1.5 หรือ 2 (ค่า default คือ 4) จะส่งผลดีต่อ planner เพราะการดึงข้อมูลแบบสุ่มจะให้ผลลัพธ์ที่เร็วกว่าอ่านจาก disk แบบปกติ

Initial Configuration Settings

โดยพื้นฐานแล้ว tuning จะอิงกับ performance ของเครื่องเป็นหลัก เพราะปัจจัยเรื่อง application ที่การใช้งานเปลี่ยนแปลงตามเวลา ต้องพิจารณาเฉพาะในแต่ละ user case เพื่อให้ได้ค่าที่เหมาะสมด้วยการทำ benchmark

shared_buffers: ควร set ที่ 25% (1/4) ของ memory ที่เหลืออยู่ในขณะนั้นของเครื่อง ถ้าเครื่องเหลือน้อยกว่า 1GB ควร set ที่ 1/8

work_mem: ค่า default คือ 4 MB การปรับค่าขึ้นอยู่กับปัจจัยของ temp file ว่าถูกสร้างบ่อยครั้ง และ file มีขนาดเล็ก (10 mb) หรือไม่ ค่าที่แนะนำคือ 1/4 system memory / max_connection การ tuning ต้องพิจารณาความถี่ของการ query ด้วย ตัวอย่างถ้า work_mem 1024mb ถ้ามี 16 worker ก็จะต้อง request memory ถึง 16GB การ set มากไปจะทำให้ system memory เต็ม ส่งผลให้ host มีปัญหา out of memory ได้

effective_cache_size: set ตามจำนวน memory ที่เหลือของเครืองรวมกับ cache ข้อมูลได้จาก free -h command

Logging

configuration พื้นฐานคือ

log_checkpoints = on
log_connections = on
log_disconnections = on
log_temp_files = 0
log_min_duration_statement = -1

PostgreSQL Tuning Queries

enable pg_stat_statement สำหรับการทำ Profiling และ statistic

#add pg_stat_statements to postgresql.conf, require restart
shared_preload_libraries = 'pg_stat_statements

#create the pg_stat_statements extension
create extension pg_stat_statements;

# add the buffercache extension
create extension pg_buffercache;

Query เพื่อหาว่า table ไหนใช้ buffercache มากผิดปกติ

SELECT  c.relname,
 pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent,
 round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS percent_of_relation,
 round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1) AS percent_of_table
FROM    pg_class c
  INNER JOIN pg_buffercache b
  ON b.relfilenode = c.relfilenode
  INNER JOIN pg_database d
  ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 10;

Query เพื่อหา waiting หรือ block

SELECT * FROM pg_stat_activity 
WHERE wait_event IS NOT NULL AND backend_type = 'client backend';

กรณีที่ query ถูก block จาก connection อื่น ใน Postgresql 9.6 ขึ้นไป ใช้ function pg_blocking_pids() เพื่อให้ได้ process ID ที่ถูก block แล้วค่อยหา statistic

SELECT * FROM pg_stat_activity 
WHERE pid IN (SELECT pg_blocking_pids())

Query statistic ของแต่ละ type

select query, calls, total_exec_time, min_exec_time, max_exec_time, mean_exec_time, stddev_exec_time, rows
from public.pg_stat_statements order by total_exec_time desc;

Query แสดง statistic ของ sequential scan

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg
FROM  pg_stat_user_tables 
WHERE   seq_scan > 0 
ORDER BY seq_tup_read DESC;SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg
FROM  pg_stat_user_tables 
WHERE   seq_scan > 0 
ORDER BY seq_tup_read DESC;

Query แสดงเวลารวม และเวลาเฉลี่ย

SELECT query, total_exec_time, calls, mean_exec_time 
FROM   pg_stat_statements 
ORDER BY total_exec_time DESC;

การ reset statistic

SELECT pg_stat_statements_reset();
SELECT pg_stat_reset();

Query แสดงขนาดของ table

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
    , (toast_bytes * 100)/NULLIF(table_bytes,0) as toast_percent
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a
where table_schema = 'public'
order by table_bytes desc;

Query จำนวน live, dead rows และ last vacuum

SELECT schemaname, relname, n_live_tup, n_dead_tup, (n_dead_tup * 100)/(NULLIF(n_live_tup, 0)) as dead_tup_percent, last_autovacuum, last_vacuum FROM pg_stat_all_tables
	where schemaname = 'pg_toast' or schemaname = 'public'
	ORDER BY n_live_tup DESC
	LIMIT 20;

• last_vacuum และ last_autovacuum ต้องภายใน 24 ชั่วโมง
• ค่า dead_tup_percent ต้องไม่มากเกินไปสำหรับ config_str, config_blob, config_version การที่ค่ามากเกินไปเกิดจาก cleaner ทำงานช้าส่งผลให้รอบของ vacuum ช้ากว่าที่จะเป็น

Query ตรวจสอบการทำงานของ Vacuum

SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, state, query
FROM pg_stat_activity 
WHERE query LIKE '%autovacuum%' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY xact_start;

ค่า xact_runtime สูง อาจจะเป็นเพราะ maintenance_work_mem มีค่าน้อยไป อาจจะต้องพิจารณาเพิ่มเป็น 1 GB

Query เพื่อหา query ที่ใช้เวลานาน

SELECT left(query, 100) AS short_query,
round(total_time::numeric, 2) AS "total_time (msecs)",
calls,
round(mean_time::numeric, 2) AS "mean_time (msecs)",
round(stddev_time::numeric, 2) AS "stddev_time (msecs)",
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

…end…

References

understanding-postgresql-architecture

postgresql document

Setting Up an Optimal Environment for PostgreSQL