PostgreSQL performance tuning

Adatbázis: SQL, XML DB

Tapasztalataitokat szeretném kérni, milyen operációs rendszer szintű, illetve PostgreSQL konfiguráció szerinti beállításokat használtok dedikált PostgreSQL kiszolgálóhoz. Hamarosan lesz egy ilyen szerverem 2 db quad core processzorral és 8GB RAM-mal, és szeretném ezt minél jobban kihasználni, hogy az adatbázis kiszolgálás teljesítménye minél jobb legyen.

  • 2687 megtekintés

( csizmadia_zoltan v | 2008. 01. 24., cs – 12:51 )

Szervusz !

A háttértár mérete, archtektúrája, a file rendszer típusa is közrejátszik;
Mekkora load várható, ill az INSERT / SELECT mennyisége és aránya, a rekordszám, stb sok minden befolyásolja.

CSZ

Valószínűleg 147GB vagy 300GB-os diszkek lesznek RAID1-ben, de megpróbálom a RAID1-0-át ha lesz elég diszk. Fájlrendszerként ext3-at gondolok, de várok más, tapasztalatból adódó javaslatot. Terhelés nagy részében SELECT-ek várhatók, időnként nagyobb mennyiségű INSERT és DELETE. A jelenlegi adatbázisok 8-10GB körüli méretűek néhány milliós rekordszámmal.

( gabrielakos v | 2008. 01. 24., cs – 13:40 )

4GB-s gépen:

shared_buffers = 2GB # min 128kB or max_connections*16kB
temp_buffers = 256MB # min 800kB
max_prepared_transactions = 5 # can be 0 or more
# (change requires restart)
work_mem = 1024MB # min 64kB
maintenance_work_mem = 1024MB # min 1MB
effective_cache_size = 3072MB

Ami még fontos hw dolog, legyen rendes raid vezérlő, lehetőleg BBWC-vel (minél nagyobb).
Persze ehhez a kernel megfelelő shm dolgait is feljebb kell húzni.
Nyilván 64 bites rendszert kell rárakni!

BBWC-s scsi vs egyszerű sata között 80-szoros(!) tps különbséget mértünk.

--
Gabriel Akos

( ekzsolt | 2008. 02. 05., k – 17:03 )

Végül 16GB RAM lett. A diszk vezérlő: LSI Logic / Symbios Logic SAS1078 PCI-X Fusion-MPT SAS, 2 darab 300GB-os SAS diszk RAID-1-ben. A vezérlőn 256MB RAM és természetesen battery backup.

A kernel paramétereket így állítottam: 

kernel.shmmax = 8589934592
kernel.shmall = 2097152
kernel.sem = 1000 32000 100 150

A postgresql.conf pedig így: 

max_connections = 100
shared_buffers = 262144                 # min 16 or max_connections*2, 8KB each
temp_buffers = 32768                    # min 100, 8KB each
work_mem = 1048576                      # min 64, size in KB
maintenance_work_mem = 1048576          # min 1024, size in KB
max_fsm_pages = 640000                  # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 3000                # min 100, ~70 bytes each
effective_cache_size = 3145728          # typically 8KB each

Mi a véleményetek?

jol nez ki, ha nagyobb terheles van, ezen a konfigon szvsz a max_connections -t viheted feljebb, adjal neki még shared_buffers -t, meg work_mem -et es a sort_mem -et is lehet emelgetni, vanamikor nagyon jottesz.
ha idonkent futtatsz vacumdb -t, akkor a vacuum_mem -et is tunningolhatod.

( ekzsolt | 2008. 02. 06., sze – 15:40 )

Játszottam egy kicsit a beállításokkal és ellenőriztem pgbench segítségével. Íme az eredmények:

postgresql.conf beállítások: 

max_connections = 100
shared_buffers = 524288
temp_buffers = 65536
work_mem = 2097152
maintenance_work_mem = 2097152
max_fsm_pages = 1048576
max_fsm_relations = 32768
effective_cache_size = 3145728

pgbench paraméterek: 

createdb test
/usr/lib/postgresql/8.1/bin/pgbench -i -s 10 test
/usr/lib/postgresql/8.1/bin/pgbench -c 10 -t 10000 test

És az eredmény: 

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 4306.410958 (including connections establishing)
tps = 4309.445974 (excluding connections establishing)

Ha nagyobb shared_buffers (996147) és effective_cache_size (5242880) méretet állítottam, csak rosszabb eredmények születtek, 2900 tps körül. Erről valahol olvastam is, hogy a túl nagy shared_buffers már csak ront a helyzeten.