Pitanje optimizacije postaje veoma akutno kada se pojave usporavanja rada, a pritom ima vrlo malo dokumentacije na ovu temu, a kamoli na ruskom jeziku. U nastavku ćemo pokušati da razmotrimo osnovne aspekte optimizacije PostgreSQL-a.
Diskovi i fajl sistemi
Ako vaš server ima više fizičkih diskova, možete rasporediti fajlove baze podataka i dnevnik transakcija na različite diskove:
– Zaustavite server.
– Preselite direktorijume pg_clog i pg_xlog, koji se nalaze u direktorijumu sa bazama podataka, na drugi disk.
– Na starom mestu kreirajte simboličku vezu.
– Pokrenite server.
Konfiguracija (putem uređivanja fajla postgresql.conf)
Podešavanje resursa
shared_buffers
Veličina memorije deljene između procesa PostgreSQL-a, koja je potrebna za izvršavanje aktivnih operacija. Ne treba navoditi preveliku količinu, jer PostgreSQL koristi i keš memoriju diska.
Vrednosti:
Srednja količina podataka i 256-512 MB dostupne memorije: 16-32 MB
Velika količina podataka i 1-4 GB dostupne memorije: 64-256 MB
temp_buffers
Keš za privremene objekte, prvenstveno za privremene tabele.
Može se postaviti oko 16 MB.
max_prepared_transactions
Broj transakcija koje se istovremeno pripremaju.
Za rad sistema 1C ovaj parametar nema značaja, jer se PREPARE TRANSACTION ne koristi.
Može se ostaviti podrazumevana vrednost – 5.
work_mem
Specijalna memorija, koristi se za sortiranje i keširanje tabela, za jedan upit.
Prilikom postavljanja ovog parametra treba uzeti u obzir broj konkurentnih upita koji se izvršavaju u isto vreme.
Za memoriju od 1-4 GB preporučuje se postavljanje na 32-128 MB.
maintenance_work_mem
Memorija koja se koristi za operacije VACUUM, CREATE INDEX, ALTER TABLE i FOREIGN KEY.
Treba postaviti veću vrednost nego za work_mem. Prevelike vrednosti će dovesti do korišćenja swap memorije.
Za memoriju od 1-4 GB preporučuje se postavljanje na 128-512 MB.
max_stack_depth
Specijalni stek za server, idealno bi trebalo da se poklapa sa veličinom steka podešenom u jezgru operativnog sistema. Postavljanje veće vrednosti nego što je u jezgru može dovesti do grešaka.
Preporučuje se postavljanje na 2-4 MB.
max_fsm_relations
Maksimalan broj tabela za koje će se pratiti slobodno mesto u opštoj karti slobodnog prostora. Ovi podaci se prikupljaju tokom VACUUM operacije.
Postavite parametar u skladu sa brojem tabela u vašoj bazi, sa rezervom. (Primjenjivo za verzije do 8.4)
max_fsm_pages
Broj blokova za koje će se čuvati informacije o slobodnom mestu. Informacije se čuvaju u deljenoj memoriji, za svaki zapis potrebno je po 6 bajtova.
Korišćenje ovog parametra omogućava izbegavanje korišćenja VACUUM FULL za bazu, dovoljan je običan VACUUM. (Primjenjivo za verzije do 8.4)
Ovaj parametar mora biti najmanje 16 * max_fsm_relations.
Ovaj parametar se automatski postavlja prilikom kreiranja baze pomoću alata initdb.
Može se postaviti i ručno: kao početnu aproksimaciju možete uzeti polovinu od prosečnog broja zapisa koji se menjaju (UPDATE ili DELETE) između pokretanja komande VACUUM.
Ovu vrednost možete proceniti (baza treba da radi već neko vreme) izvršavanjem:
vacuum full analyze;
NOTICE: number of page slots needed (196272) exceeds max_fsm_pages (153600)
SAVET: Razmotrite povećanje konfiguracionog parametra "max_fsm_pages" na vrednost veću od 196272.
max_files_per_process
Maksimalan broj fajlova koje proces i njegovi podprocesi mogu istovremeno da otvore.
Smanjite ovaj parametar ako tokom rada dobijate poruku "Too many open files".
Pisanje transakcionih logova na disk
commit_delay i commit_siblings
Vrednost commit_delay izražava se u mikrosekundama (podrazumevana vrednost je 0). Vrednost commit_siblings izražava se u komadima (podrazumevana vrednost je 5).
commit_delay određuje kašnjenje između upisa zapisa u keš transakcionog loga i njegovog bacanja na disk. Ako je pri uspešnom završetku transakcije aktivno najmanje commit_siblings transakcija, zapis će biti odložen na vreme commit_delay. Ako se tokom tog vremena završi druga transakcija, njihove promene će biti bacene na disk zajedno, koristeći jedan sistemski poziv. Ovi parametri će ubrzati rad ako se paralelno izvršava mnogo "sitnih" transakcija.
fsync
Ovaj parametar je zadužen za bacanje podataka iz keša na disk pri završetku transakcija.
Ako postavite njegovu vrednost
fsync=off
onda podaci neće biti odmah upisani na disk nakon završetka operacija. To može značajno povećati brzinu operacija insert i update, ali postoji rizik od oštećenja baze ako dođe do kvara (neočekivano isključenje napajanja, kvar operativnog sistema, kvar disk podsystema).
Koristite ovu mogućnost samo ako imate pouzdane UPS (neprerivne izvore napajanja) i softver koji bezbedno zaustavlja sistem pri niskom nivoju baterije.
Ne isključujte fsync tokom rada Postgre SQL?-a na Windows platformi, zbog nestabilnosti sistema.
wal_sync_method
Metod koji se koristi za prisilno upisivanje podataka na disk.
Ako je fsync=off, onda se ovaj parametar ne koristi.
Moguće vrednosti:
open_datasync – upisivanje podataka metodom open() sa parametrom O_DSYNC
fdatasync – poziv metode fdatasync() nakon svakog commit-a
fsync_writethrough – poziv fsync() nakon svakog commit-a, ignorišući paralelne procese
fsync – poziv fsync() nakon svakog commit-a
open_sync – upisivanje podataka metodom open() sa parametrom O_SYNC
Nisu svi metodi dostupni na određenim platformama. Podrazumevano se postavlja prvi koji je dostupan u sistemu.
full_page_writes
Postavite ovaj parametar na off ako je fsync=off. Inače
Kada je ovaj parametar uključen (on), Postgre SQL? upisuje sadržaj svake stranice u transakcioni log tokom prve modifikacije tabele nakon kontrolne tačke. To je neophodno jer se stranice mogu delimično upisati ako tokom procesa OS "padne". To dovodi do toga da se na disku nalaze novi podaci pomešani sa starima. Nivo zapisa u transakcioni log možda neće biti dovoljan za potpuno vraćanje podataka nakon "pada". full_page_writes garantuje ispravno vraćanje, po cenu povećanja količine podataka upisanih u transakcioni log. (Pošto se transakcioni log uvek počinje od kontrolne tačke. Jedini način smanjenja obima upisa je povećanje intervala kontrolnih tačaka).
wal_buffers
Količina memorije korišćena u SHARED MEMORY-u za vođenje transakcionih logova.
Pri dostupnoj memoriji od 1-4GB preporučuje se postavljanje na 256-1024kb
Optimizacija upita
enable_nestloop
Определяет использование планировщика запросов. В некоторых случаях выключение планировщика может уменьшать время исполнения отдельных запросов, в том числе и значительно. Однако в подавляющем большинстве случаев планировщик позволяет уменьшить время выполнения запросов. Не следует его выключать, по крайней мере на постоянной основе.
random_page_cost
Устанавливает у планировщика оценку "стоимоти" не последовательного перебора данных. По умолчанию 4.0. Уменьшение этого значения по отношению к seq_page_cost вызовет у планировщика предпочтение сканирования индекса, увеличение — наоборот сделает сканирование индекса "дороже". Вы можете изменить оба значения чтобы изменить отношение "стоимости" дисковых операций ввода/выдода, по отношениб с "стоимости" использования процессора, которая бадет описана следующими параметрами.
На серверах с быстрыми дисковыми массивами имеет смысл уменьшать изначальную настройку до 3.0, 2.5 или даже до 2.0. Если же активная часть вашей базы данных много больше размеров оперативной памяти, попробуйте поднять значение параметра. Можно подойти к выбору оптимального значения и со стороны производительности запросов. Если планировщик запросов чаще, чем необходимо, предпочитает последовательные просмотры (sequential scans) просмотрам с использованием индекса (index scans), понижайте значение. И наоборот, если планировщик выбирает просмотр по медленному индексу, когда не должен этого делать, настройку имеет смысл увеличить. После изменения тщательно тестируйте результаты на максимально широком наборе запросов. Никогда не опускайте значение random_page_cost ниже 2.0; если вам кажется, что random_page_cost нужно еще понижать, разумнее в этом случае менять настройки статистики планировщика.
cpu_tuple_cost
Устанавливает у планировщика оценку "стоимоти" затрат на обработку каждой строки во время выполнения запроса. По умолчанию 0,01.
cpu_index_tuple_cost
Устанавливает у планировщика оценку "стоимоти" затрат на обработку каждого индекса во время операции сканирования индекса. По умолчанию 0,005
cpu_operator_cost
Устанавливает у планировщика оценку "стоимоти" затрат на выполнение каждого оператора или функции во время выполнения запроса. По умолчанию 0.0025.
effective_cache_size
Передает данные планировщику запросов об объёме памяти, которая используется ОС для кэширования файлов, для одного запроса.
Этот параметр в ОС можно посмотреть в настройках:
Для Windows: в Диспетчере задач, Закладка Быстродействие, Физическая память-Системный кэш.
Для Linux: наберите команду free, необходимое значение в столбце cached (в kB)
Данное значение необходимо разделить на количество конкурентных запросов в один момент времени (среднее количество подключений к базе + запас).
default_statistics_target
Устанавливает глубину статистики по таблицам. БОльшие значения могут повысить время выполнения команды ANALYZE, но улучшат построение плана запроса.
Рекомендуется устанавливать порядка 100.
constraint_exclusion
Включает или отключает использование планером ограничений CONSTRAINT в таблицах при построении запросов.
Рекомендуется установить значение on, при этом, если Вы изменяете CONSTRAINT у таблиц, необходимо обновить их статистику выполнив ANALYZE, в противном случае будут построены неверные планы запросов.
Сбор статистики
stats_command_string
Da li slati prikupljaču statistike informacije o trenutno izvršenoj komandi i vremenu njenog početka izvršavanja.
Postaviti na on
stats_start_collector
Da li uključiti prikupljanje statistike.
Postaviti na on
stats_row_level, stats_block_level
Da li prikupljati informacije o aktivnosti na nivou redova i blokova, redom.
Postaviti
stats_row_level=on
stats_block_level=off
stats_reset_on_server_start
Da li resetovati statistiku pri ponovnom pokretanju servera
Postaviti na off
Autovacuum
VACUUM – skupljanje „otpada“. VACUUM oslobađa prostor zauzet „mrtvim“ podacima. Prilikom običnih operacija nad podacima, Postgre SQL? ne briše podatke fizički iz tabela; to se dešava operacijom VACUUM.
autovacuum
Da li uključiti autovacuum (automatsko pokretanje VACUUM-a), postaviti na on
autovacuum_naptime
Vreme pauze između pokretanja Autovacuum-a.
Zavisi od toga koliko često se podaci u vašim tabelama ažuriraju. Može biti oko 5 minuta, podrazumevano je 1 minut.
autovacuum_analyze_threshold
Određuje minimalni broj umetanja, ažuriranja ili brisanja potrebnih za slanje signala za pokretanje ANALYZE u bilo kojoj tabeli. Ovaj parametar se može promeniti za pojedinačne tabele menjanjem parametara skladištenja.
autovacuum_vacuum_threshold
Određuje minimalni broj umetanja, ažuriranja ili brisanja potrebnih za slanje signala za pokretanje VACUUM-a u bilo kojoj tabeli. Ovaj parametar se može promeniti za pojedinačne tabele menjanjem parametara skladištenja.
Zaključavanja
max_locks_per_transaction
Broj zaključavanja po jednoj transakciji: postaviti oko 250
deadlock_timeout
Vreme trajanja međusobnih blokova. Postaviti oko 2 sekunde.
Moguća greška
Nakon ispravke konfiguracionog fajla, Postgre SQL možda neće da se pokrene, već će izbaciti grešku:
FATAL: could not create shared memory segment:...
Failed system call was shmget(key=5432001, size=140075008, 03600).
Ova greška obično znači da je zahtev PostgreSQL-a za segmentom zajedničke memorije premašio SHMMAX parametar vašeg kernela. Možete ili smanjiti veličinu zahteva ili ponovo konfigurisati kernel sa većim SHMMAX-om. Da biste smanjili veličinu zahteva (trenutno 140075008 bajtova), smanjite PostgreSQL-ov shared_buffers parametar (trenutno 16384) i/ili njegov max_connections parametar (trenutno 10).
Ako je veličina zahteva već mala, moguće je da je manja od SHMMIN parametra vašeg kernela, u kom slučaju je potrebno povećati veličinu zahteva ili ponovo konfigurisati SHMMIN.
Dokumentacija PostgreSQL-a sadrži više informacija o konfiguraciji zajedničke memorije.
Za ispravku je potrebno povećati parametar SHMMAX u sistemu. To se može uraditi na sledeći način (za Linux OS):
Izvršite komandu:
echo 150829120 > /proc/sys/kernel/shmmax
navevši svoju novu vrednost za parametar (koja tačno – možete pogledati u logu greške Postgre SQL?). Ova operacija postavlja parametar u toku rada, ali nakon ponovnog pokretanja sistema promene će biti izgubljene. Da bi se to sprečilo, dodajte sledeću liniju u fajl /etc/sysctl.conf:
kernel.shmmax = 150829120
navevši svoju vrednost.
Primer konfiguracije za server sa 2G RAM-a
/var/lib/pgsql/data/postgresql.conf:
to be done
/etc/sysctl.conf:
to be done
Srednja podešavanja
Prosečna konfiguracija za maksimalnu performanse. Moguće je da će za konkretan slučaj biti bolje prilagođene druge postavke. Pažljivo proučite ovaj vodič i podesite PostgreSQL operišući se na osnovu ove informacije.
RAM — veličina memorije
* shared_buffers = 1/8 RAM-a ili više (ali ne više od 1/4);
* work_mem na 1/20 RAM-a;
* maintenance_work_mem na 1/4;
* max_fsm_relations na planirani broj tabela u bazama * 1.5;
* max_fsm_pages na max_fsm_relations * 2000;
* fsync = true;
* wal_sync_method = fdatasync;
* commit_delay = od 10 do 100;
* commit_siblings = od 5 do 10;
* effective_cache_size = 0.9 od vrednosti cached koju pokazuje free;
* random_page_cost = 2 za brze CPU-e, 4 za spore;
* cpu_tuple_cost = 0.001 za brze CPU-e, 0.01 za spore;
* cpu_index_tuple_cost = 0.0005 za brze CPU-e, 0.005 za spore;
* autovacuum = on
* autovacuum_vacuum_threshold = 1800
* autovacuum_analyze_threshold = 900
Održavanje baze