PostgreSQL 8.2 efektivně

PostgreSQL 8.2 efektivnˇe Administrace Pavel Stˇehule http://www.pgsql.cz

25. 2. 2007

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

1

Omezen´ı pˇr´ıstupu k datab´azi

2

´ zba datab´aze Udrˇ

3

Spr´ava uˇzivatel˚ u

4

Export, import dat

5

Z´alohov´an´ı, obnova datab´aze

6

Konfigurace datab´aze

7

Monitorov´an´ı datab´aze

8

Instalace doplˇ nk˚ u

9

Postup pˇri pˇrechodu na novou verzi

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

1 / 36

25. 2. 2007

2 / 36

Omezen´ı pˇr´ıstupu k datab´azi Pˇrehled

Pˇr´ıstup k datab´azi lze omezit na u ´rovni protokolu listen_addresses = ’localhost’, ’*’ port = 5432 urˇcen´ım autentifikaˇcn´ı metody uˇzivatele pro kaˇzdou datab´azi - soubor pg hba.conf. V´ychoz´ı nastaven´ı pˇreloˇzen´e PostgreSQL je absolutnˇe benevoletn´ı. PostgreSQL v distribuc´ıch je bezpeˇcn´a. Minim´aln´ı doporuˇcen´a u ´roveˇ n je md5. na modern´ıch o.s. typu unix lze pro lok´aln´ı pˇr´ıstup pouˇz´ıt metodu ident. Soubor pg ident.conf slouˇz´ı k mapov´an´ı unix. jmen na uˇzivatelsk´e u ´ˇcty PostgreSQL.

Zmˇena hesla Heslo zmˇen´ıme pˇr´ıkazem: ALTER USER name PASSWORD ’heslo’; Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

3 / 36

Omezen´ı pˇr´ıstupu k datab´azi Pozn´ amky I.

Zapomenut´e heslo uˇzivatele postgres V pg hba.conf nastav´ıme uˇzivateli postgres methodu trust. Po dobu, kdy je tato ovˇeˇrovac´ı metoda povolena je nutn´e zabr´anit pˇr´ıstupu dalˇs´ım uˇzivatel˚ um k serveru. Spuˇstˇen´ı datab´aze v single reˇzimu Nem˚ uˇzeme-li zajistit izolaci datab´aze, lze spustit PostgreSQL server v tzv. single reˇzimu. Tento reˇzim se pouˇz´ıv´a jednak pˇri inicializaci clusteru, jednak v pˇr´ıpadˇe nutn´ych z´asah˚ u do syst´emov´eho katalogu, kter´e jsou v norm´aln´ım reˇzimu zak´az´any. Neodborn´ y z´ asah v tomto reˇ zimu m˚ uˇ ze v´ est k poˇskozen´ı syst´ emov´ eho katalogu s d˚ usledkem nedeterministick´ eho chov´ an´ı datab´ azov´ eho syst´ emu vedouc´ı ke ztr´ atˇ e uloˇ zen´ ych dat. bash-3.1$ postmaster --single template1 -D /usr/local/pgsql/data/ PostgreSQL stand-alone backend 8.3devel backend> alter user postgres password ’omega’; Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

4 / 36

Omezen´ı pˇr´ıstupu k datab´azi Pozn´ amky II.

Zamezen´ı zad´av´an´ı hesla Standardnˇe komunikaci mezi serverem a klientem zajiˇst’uje knihovna libpq. Ta pˇred explicitn´ım vyˇz´ad´an´ım hesla kontroluje, zda-li neexistuje soubor ˜/.pgpass. Pokud tento soubor nen´ı dostateˇcnˇe zabezpeˇcen, je ignorov´an. Tento textov´y soubor obsahuje ˇr´adky ve form´atu hostname:port:database:username:password.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

5 / 36

´ zba datab´aze Udrˇ Spr´ ava datov´eho adres´ aˇre

Inicializace Slovo cluster m´a v PostgreSQL v´yznam prostoru pro vˇsechny datab´aze, ke kter´ym lze pˇristupovat urˇcenou IP adresou a portem. Vˇsechny datab´aze v clusteru sd´ıl´ı konfiguaraci a uˇzivatele. Na jednom poˇc´ıtaˇci lze provozovat v´ıce cluster˚ u stejn´ych nebo r˚ uzn´ych verz´ı PostgreSQL. initdb [OPTION]... [DATADIR] -E, --encoding=ENCODING urˇ c´ ı k´ odov´ an´ ı --locale=LOCALE urˇ c´ ı locales

Pozn´amky v clusteru nic nemazat (pg resetxlog) z clusteru nekop´ırovat datov´e soubory (nepˇrenosn´e) lze kop´ırovat cel´y adres´aˇr clusteru (zastaven´e PostgreSQL) lze kop´ırovat cel´y adres´aˇr clusteru (aktivn´ı export write ahead logu)

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

6 / 36

´ zba datab´aze Udrˇ Spr´ ava datov´eho adres´ aˇre

Um´ıstˇen´ı datov´eho adres´aˇre liˇs´ı se dle zvyklost´ı konkr´etn´ıch distribuc´ı: default /usr/local/pgsql/data, vytv´aˇr´ı se ruˇcnˇe Red Hat /var/lib/pgsql/data, vytv´aˇr´ı se automaticky, pˇri prvn´ım startu

Kontrola korektn´ıho chov´an´ı LOCALE Okamˇzitˇe po instalaci ovˇeˇrte, zda je korektnˇe podporov´ano n´arodn´ı prostˇred´ı. Nejl´epe SELECT upper(’pˇ r´ ıliˇ s ˇ zlut’ouˇ ck´ y k˚ uˇ n’). Vybran´e locale clusteru se mus´ı shodovat s k´odov´an´ım datab´aze, napˇr. pro UTF8 mus´ıme pouˇz´ıvat locale cs CZ.UTF8.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

7 / 36

´ zba datab´aze Udrˇ Opakuj´ıc´ı se ˇcinnosti

Pravideln´e 1x dennˇ e VACUUM ANALYZE (cron, autovacuum), 1x mˇes´ıˇcnˇe REINDEX datab´aze nebo alespoˇ n nejˇcastˇeji modifikovan´ych tabulek,

Nepravideln´e pokud doch´az´ı vyhrazen´y prostor na disku pro data VACUUM FULL, pokud hroz´ı pˇreteˇcen´ı ˇc´ıtaˇce transakc´ı (varov´an´ı v logu) FACUUM FREEZE (1x za nˇekolik let), anal´yza pomal´ych dotaz˚ u (limit 200ms) a jejich eliminace pˇrid´an´ım nov´ych index˚ u. ˇciˇstˇen´ı datov´eho sch´ema (nutno z´alohovat a dokumentovat) odstranˇen´ı nepouˇz´ıvan´ych tabulek (pg stat all tables), odstranˇen´ı nepouˇz´ıvan´ych index˚ u (pg stat all indexes).

Kaˇ zd´ y index zab´ır´ a prostor na disku a zpomaluje operace INSERT, UPDATE, DELETE. Proto indexy vytv´aˇr´ıme pouze tehdy, kdyˇz maj´ı nˇejak´y efekt. Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

8 / 36

´ zba datab´aze Udrˇ Zastaven´ı, spuˇstˇen´ı PostgreSQL

Start, Reload, Restart serveru /etc/init.d/postgres (start|reload|restart|stop|status) pg ctl lze specifikovat reˇzimy ukonˇcen´ı smart ˇcek´a, aˇz se odhl´as´ı vˇsichni klienti, fast neˇcek´a na odhl´aˇsen´ı klientu, provede u ´pln´y proces ukonˇcen´ı, immediate skonˇc´ı okamˇzitˇe s d˚ usledkem obnovy po nekorektn´ım ukonˇcen´ı pˇri pˇr´ıˇst´ım startu.

Preferujeme co nejˇsetrnˇejˇs´ı moˇznou metodu. V pˇr´ıpadˇe, podivn´eho chov´an´ı jednoho klientsk´eho procesu, lze zaslat sign´al sigint obsluˇzn´emu procesu klienta.

Ukonˇcen´ı klienta z prostˇred´ı SQL 1

2

z´ısk´an´ı pid probl´emov´eho klienta select procpid, usename, current_query from pg_stat_activity; procpid | usename | current_query 10144 | root | select fce(); odstranˇen´ı procesu select pg cancel backend(10144);

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

9 / 36

´ zba datab´aze Udrˇ Zastaven´ı, spuˇstˇen´ı PostgreSQL

Obnova po hav´arii Pokud server nebyl ukonˇcen korektnˇe, je moˇzn´e, ˇze v pamˇeti z˚ ustanou servisn´ı procesy PostgreSQL. Ty je nutn´e pˇred opˇetovn´ym spuˇstˇen´ım serveru ukonˇcit. V´yjmeˇcnˇe je nutn´e vyˇcistit sd´ılenou pamˇet’ pˇr´ıkazem ipcclean. Tak´e je nutn´e explicitnˇe odstranit soubor dbcluster/postmaster.pid. Za norm´aln´ıch okolnost´ı se spust´ı automaticky proces obnovy datab´aze na z´akladˇe u ´daj˚ u uloˇzen´ych ve write ahead logu.

Doporuˇcen´ı Je celkem na m´ıstˇe ovˇeˇrit integritu datab´aze dumpem datab´aze. V pˇr´ıpadˇe probl´em˚ u reindexace datab´aze vˇcetnˇe syst´emov´ych tabulek obnova ze z´alohy identifikace poˇskozen´ych ˇr´adk˚ u a jejich odstranˇen´ı. Pˇr´ıznakem poˇskozen´e datab´aze je p´ad serveru pˇri sekvenˇcn´ım ˇcten´ı. Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

10 / 36

Spr´ava uˇzivatel˚ u createuser Usage: createuser [OPTION]... [ROLENAME] Options: -s, --superuser -S, --no-superuser -d, --createdb -D, --no-createdb -r, --createrole -R, --no-createrole -l, --login -L, --no-login -i, --inherit -I, -c, -P, -E, -N, -e, -q,

--no-inherit --connection-limit=N --pwprompt --encrypted --unencrypted --echo --quiet

Pavel Stˇ ehule (http://www.pgsql.cz)

role will be superuser role will not be superuser role can create new databases role cannot create databases role can create new roles role cannot create roles role can login (default) role cannot login role inherits privileges of roles it is a member of (default) role does not inherit privileges connection limit for role (default: no limit) assign a password to new role encrypt stored password do not encrypt stored password show the commands being sent to the server don’t write any messages PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

11 / 36

25. 2. 2007

12 / 36

Spr´ava uˇzivatel˚ u CREATE ROLE Command: CREATE ROLE Description: define a new database role Syntax: CREATE ROLE name [ [ WITH ] option [ ... ] ] where option can be:

| | | | | | | | | | | |

SUPERUSER | NOSUPERUSER CREATEDB | NOCREATEDB CREATEROLE | NOCREATEROLE CREATEUSER | NOCREATEUSER INHERIT | NOINHERIT LOGIN | NOLOGIN CONNECTION LIMIT connlimit [ ENCRYPTED | UNENCRYPTED ] PASSWORD ’password’ IN ROLE rolename [, ...] ROLE rolename [, ...] ADMIN rolename [, ...] USER rolename [, ...] SYSID uid

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

Spr´ava uˇzivatel˚ u V´yklad

Pravidla chov´an´ı rol´ı I. Z´avislosti mezi rolemi tvoˇr´ı orientovan´y graf, kter´y nesm´ı obsahovat cyklus (Pavel m˚ uˇze z´ıskat pr´ava Tom´aˇse, z´aroveˇ n ale Tom´aˇs nem˚ uˇze z´ıskat pr´ava Pavla). Uˇzivatel z´ısk´a pr´ava rol´ı, jejichˇz je ˇclenem (ke kter´ym m´a pˇr´ıstup, kter´e m˚ uˇze pˇrevz´ıt). CREATE ROLE tom_a_pavel IN ROLE tom, pavel; uˇze pˇrevz´ıt roli Tom´aˇse nebo Pavla (je to nadˇrazen´a role Role tom a pavel m˚ tˇemto rol´ım), a m´a pr´ava jako Tom´aˇs a Pavel dohromady. Roli m˚ uˇzeme definovat tak´e tak, ˇze urˇc´ıme kdo tuto roli m˚ uˇze pouˇz´ıvat. CREATE ROLE developer ROLE tom, pavel; Roli developer m˚ uˇze pouˇz´ıt jako Tom´aˇs tak Pavel. Pokud role nem´a atribut INHERIT, pak se uˇzivatel do t´eto role mus´ı explicitnˇe pˇrenout pˇr´ıkazem SET ROLE developer. Cokoliv je vytvoˇreno (vlatsnˇeno) touto rol´ı m˚ uˇze pouˇz´ıvat (pˇr´ıstup, modifikace struktury) jak Tom´aˇs, tak Pavel.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

13 / 36

Spr´ava uˇzivatel˚ u V´yklad

Pravidla chov´an´ı rol´ı II. Uˇzivatel m˚ uˇze zmˇenit vlastnictv´ı objekt˚ u ke kter´ym m´a pr´ava pˇr´ıkazem: ALTER TABLE objekt OWNER TO developer; ale nem˚ uˇze se vzd´at sv´ych pr´av, tj. nem˚ uˇze zmˇenit vlastnictv´ı tak, aby pˇriˇsel o objekt (nelze datab´azov´y objekt darovat nˇekomu nezn´am´emu s n´ımˇz nem´am nic spoleˇcn´eho.

Rekapitulace CREATE ROLE vytvoˇr´ı novou roli. GRANT co TO komu delegov´an´ı urˇcit´eho pr´ava roli. GRANT r1 TO r2 role r2 z´ısk´av´a stejn´a pr´ava jako m´a role r1. REVOKE odejmut´ı pr´av. ALTER TABLE .. OWNER TO .. zmˇena vlastnictv´ı objektu. \dg zobrazen´ı rol´ı a ˇclenstv´ı v psql.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

14 / 36

Export, import dat Moˇznosti

SQL dump tabulek a struktury Syst´emov´ym pˇr´ıkazem pg dump dok´aˇzeme exportovat tabulku (nebo v´ıce tabulek) a to jak data, tak strukturu. V´ysledn´y soubor obsahuje SQL pˇr´ıkazy. Data lze uloˇzit jako sadu pˇr´ıkaz˚ u INSERT nebo jako jeden pˇr´ıkaz COPY.

COPY na serveru Tento pˇr´ıkaz vytvoˇr´ı (pˇreˇcte) textov´y soubor (hodnoty oddˇelen´e ˇc´arkou nebo tabel´atorem) uloˇzen´y na serveru. Pˇri zpracov´an´ı nedoch´az´ı k pˇrenosu dat po s´ıti. Mus´ıme m´ıt k dispozici prostor na serveru pˇr´ıstupn´y pro uˇzivatele postgres.

COPY z klienta ˇ (ukl´ad´a) Obdoba pˇr´ıkazu COPY implementovan´a jako pˇr´ıkaz konzole psql. Cte soubory na strannˇe klienta, zajiˇst’uje pˇrenos dat po s´ıti, a zpracov´an´ı na stranˇe serveru. Form´at souboru je stejn´y jako na u pˇr´ıkazu COPY na serveru.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

15 / 36

Export, import dat pg dump

pg_dump [OPTION]... [DBNAME] -a, --data-only -c, --clean -C, --create -d, --inserts -E, --encoding=ENCODING -s, --schema-only --disable-triggers -t, --table=TABLE

Pavel Stˇ ehule (http://www.pgsql.cz)

pouze data pˇ redˇ rad´ ı pˇ r´ ıkazy pro zruˇ sen´ ı objekt˚ u vloˇ z´ ı pˇ r´ ıkazy k vytvoˇ ren´ ı objekt˚ u pouˇ zije INSERT m´ ısto COPY pouˇ zije urˇ cen´ e k´ odov´ an´ ı pouze sch´ ema po dobu naˇ c´ ıt´ an´ ı dat blokuje triggery pouze urˇ citou tabulku

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

16 / 36

Export, import dat COPY

COPY tablename [ ( column [, ...] ) ] (FROM|TO) { ’filename’ | (STDIN|STDOUT) } [ [ WITH ] [ BINARY ] [ HEADER ] [ OIDS ] [ DELIMITER [ AS ] ’delimiter’ ] [ NULL [ AS ] ’null string’ ] [ CSV [ HEADER ] [ QUOTE [ AS ] ’quote’ ] [ ESCAPE [ AS ] ’escape’ ] [ (FORCE NOT NULL column [, ...]| FORCE QUOTE column [, ...]) ]

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

17 / 36

Export, import dat Efektivita

Pˇrehled jednotliv´ych metod Uveden´a tabulka obsahuje u ´daje o importu jednoho mili´ onu ˇr´adek jednosloupcov´e celoˇc´ıseln´e tabulky (testov´ano na notebooku Prestigio Nobile 156, P1.6, 500M). Metoda INSERTS (autocommit on) INSERTS (autocommit off) COPY \COPY (UDP) COPY BINARY COPY (+ 1 index)

Velikost 37,8 M 37,8 M 6,8 M 6.8 M 10 M 6.8 M

ˇ Cas 10 min 2.2 min 10 sec 10 sec 7 sec 17 sec

Z´avˇer Preferovat COPY, Zruˇsit vˇsechny indexy (nejsn´aze pomoc´ı SQL procedury), zablokovat triggery (ALTER TABLE name DISABLE TRIGGER ALL). Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

18 / 36

Export, import dat Import dat z dostupn´e ˇziv´e datab´ aze

Import pomoc´ı univerz´aln´ıho datov´eho rozhran´ı Pro opakovan´e importy se vyplat´ı pouˇz´ıt jednoduchou funkci v untrusted PLperlu a v t´eto funkci se pˇripojit ke zdrojov´e datab´azi, z´ıskat data a ta vr´atit jako v´ysledek funkce. Odpadaj´ı t´ım nˇekdy komplikovan´e transformace dat (napˇr. mezi PostgreSQL a MySQL) a tak´e se sn´aze tato metoda automatizuje. CREATE PROCEDURE exec_ora(connection_string text, sqlquery text) RETURNS SETOF RECORD AS $$ use DBI; $dbh = DBI->connect(’dbi:Oracle:’, $_[0], ’’, {}); $sth = $dbh->prepare($_[1]); $sth->execute(); while ($dt = $sth->fetchrow_hashref) { return_next $dt; } $sth->finish(); $dbh->disconnect(); return undef; $$ LANGUAGE plperlu; SELECT * FROM exec_mysql(’anonymous/tiger@heslo’, ’SELECT a,b FROM data WHERE ins = ’10.2.2007’) AS (a int, b int); Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

19 / 36

Z´alohov´an´ı, obnova datab´aze Pˇrehled

Pˇrehled technik z´alohov´an´ı K dispozici jsou tˇri zp˚ usoby z´alohov´an´ı: SQL dump pˇr´ıkazy pg dump, pg restore. Data lze uloˇzit jako SQL skript nebo v speci´aln´ım komprimovan´em form´atu. z´aloha na u ´rovni souborov´eho syst´emu Server mus´ı b´ yt zastaven! Lze z´alohovat a obnovovat pouze kompletn´ı db. cluster. tar -cf backup.tar /usr/local/pgsql/data online z´alohov´an´ı je zaloˇzeno na tzv. write ahead logu (WAL), coˇz je soubor do kter´eho se zapisuj´ı vˇsechny zmˇeny v datech jeˇstˇe pˇredt´ım neˇz se zap´ıˇs´ı do datov´ych soubor˚ u. Prim´arnˇe tento log slouˇz´ı k obnovˇe datab´aze po hav´arii, m˚ uˇzeme jej vˇsak exportovat, uloˇzit a pouˇz´ıt pro vytvoˇren´ı z´alohy. jedin´e moˇzn´e ˇreˇsen´ı pr˚ ubˇeˇzn´eho z´alohov´an´ı, n´aroˇcn´e na diskov´y prostor, n´aroˇcn´e na administraci, z´alohov´an´ı i obnova je velice rychl´e, z´aloha nen´ı kompatibiln´ı mezi 32 a 64 bit platformami.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

20 / 36

Z´alohov´an´ı, obnova datab´aze Vytvoˇren´ı z´ alohy exportov´ an´ım WAL

Postup 1

2

3

4

V postgresql.conf nastav´ıme archive command. Tento pˇr´ıkaz zajist´ı pˇrenesen´ı segment logu na bezpeˇcn´e m´edium. PostgreSQL neodstran´ı segment, dokud pˇr´ıkaz neprobˇehne bezchybnˇe. archive_command = ’cp -i "%p" /mnt/server/archivedir/"%f"’ Export logu aktivujeme vol´an´ım select pg start backup(’navesti’). Jako n´avˇest´ı m˚ uˇzeme pouˇz´ıt libovoln´y ˇretˇezec, napˇr. cesta k z´aloze. V tomto pˇr´ıpadˇe m˚ uˇzeme bezpeˇcnˇe za chodu zkop´ırovat obsah dovoh´eho adres´aˇre PostgreSQL. Nen´ı tˇreba z´alohovat adres´aˇr pg xlog. po dokonˇcen´ı kop´ırov´an´ı deaktivujeme export logu vol´an´ım SELECT pg stop backup(). Export logu m˚ uˇze bˇeˇzet libovolnou dobu, coˇz je z´aklad pr˚ ubˇeˇzn´eho z´alohov´an´ı.

Pr˚ ubˇeˇzn´e z´alohov´an´ı Segment se exportuje po naplnˇen´ı (16MB) nebo po pˇrednastaven´em ˇcasov´em intervalu (8.2). Efektivnˇe jej lze komprimovat. Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

21 / 36

Z´alohov´an´ı, obnova datab´aze Obnova ze z´ alohy exportovan´eho WAL

Postup 1 2 3

4

5

Zaz´alohujte si aktu´aln´ı cluster (vˇcetnˇe pg xlog). Pˇrekop´ırujte data ze z´alohy (zaz´alohovan´y datov´y adres´aˇr). Upravte soubor recovery.conf a uloˇzte jej v adres´aˇru clusteru. Vzor naleznete v podadres´aˇri shared. Minim´aln´ı zmˇenou je nastaven´ı poloˇzky archive command. do nadres´aˇre pg xlog zkop´ırujte vˇsechny nezaz´alohovan´e soubory z adres´aˇre pg xlog (to jsou WAL segmenty, kter´e vznikly po deaktivaci exportu). Nastartujte server. Pˇri startu se automaticky spust´ı proces obnovy na z´akladˇe WAL.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

22 / 36

Konfigurace datab´aze Volba souborov´eho syst´emu

Vliv souborov´eho syst´emu na v´ykon datab´aze Nelze obecnˇe ˇr´ıci, kter´y souborov´y syst´em je optim´aln´ı. Pˇri umˇ el´ ych testech bylo poˇrad´ı souborov´ych syst´em˚ u n´asleduj´ıc´ı: JFS, ext2, Reiser3, ext3, XFS. Rozd´ıl mezi nejpomalejˇs´ı a nejrychlejˇs´ı testovac´ı konfigurac´ı byl 30% (coˇz se nebere jako natolik v´yznamn´a hodnota, aby se o tom pˇr´ıliˇs diskutovalo). U ”high” ˇradiˇcu je nutn´e explicitnˇe povolit write cache (bateri´ı z´alohovan´e ˇradiˇce).

Z´avˇer pouˇz´ıvejte takov´y souborov´y syst´em, kter´y je pro v´as d˚ uvˇeryhodn´y (RAID 10), na UNIXech pouˇz´ıvejte mount parametr noatime, pokud jste jiˇstˇeni UPS, lze pro ext3 pouˇz´ıt mount parametr data=writeback. v´ykonovˇe se dostanete na u ´roveˇ n ext2, v ˇz´adn´em pˇr´ıpadˇe se nedoporuˇcuje zmˇenit konfiguraˇcn´ı parametr fsync na off, pokuste se um´ıstit WAL na jin´y nejl´epe RAID 1 disk neˇz data (symlink) . verifikujte konfiguraci testem pgbench, kter´y by mˇel zobrazovat r´amcovˇe srovnateln´e hodnoty s jinou instalac´ı PostgreSQL na podobn´em hw. Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

23 / 36

Konfigurace datab´aze Pˇridˇelen´ı pamˇeti

Strategie PostgreSQL m´a m´ıt pˇridˇeleno maximum pamˇeti, aniˇz by zpomalila o.s. Pˇridˇelen´ı pamˇeti je urˇceno hodnotami urˇcit´ych parametr˚ u v postgresql.conf. Pouze parametr work mem lze nastavovat dynamicky. Neexistuje u ´pln´a shoda ohlednˇe doporuˇcen´ych hodnot. Jako rozumn´y minim´aln´ı kompromis lze br´at hodnoty z konfigurace verze 8.2.

postgresql.conf I. shared buffers velikost cache pro syst´emov´e objekty [32..256] MB (6..10%). work mem velikost alokovan´e pracovn´ı pamˇeti pro kaˇ zd´ e spojen´ı, omezuje pouˇzit´ı diskov´e mezipamˇeti pˇri operaci sort, urˇcuje maxim´aln´ı velikost hash tabulek pˇri operaci hash join, lze ji nastavit dynamicky pˇred n´aroˇcn´ym dotazem [1..10] MB. max fsm pages, max fsm relation urˇcuje maxim´aln´ı poˇcet diskov´ych str´anek, pro kter´e se udrˇzuje mapa voln´ych str´anek (pˇr´ıkaz DELETE). Pˇr´ıliˇs mal´a hodnota zp˚ usob´ı nevyuˇz´ıv´an´ı uvolnˇen´ych blok˚ u a alok. nov´ych. Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

24 / 36

Konfigurace datab´aze Pˇridˇelen´ı pamˇeti

postgresql.conf I. maintenance work mem velikost pamˇeti pouˇz´ıvan´e pro pˇr´ıkazy jako jsou VACUUM nebo CREATE INDEX. Jelikoˇz nen´ı pravdˇepodobn´e, ˇze by doˇslo k soubˇehu prov´adˇen´ı tˇechto pˇr´ıkaz˚ u lze bezpeˇcnˇe tuto hodnotu nastavit v´yraznˇe vyˇsˇs´ı neˇz je work mem. Doporuˇcuje se 32 ... 256MB nebo 50..75% velikosti nejvˇetˇs´ı tabulky.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

25 / 36

Konfigurace datab´aze Parametrizace pl´ anovaˇce dotaz˚ u

Pozn´amka Aˇz na v´yjimky, parametry t´ykaj´ıc´ı se v´ybˇeru nejvhodnˇejˇs´ıho zp˚ usobu prov´adˇen´ı dotazu jsou urˇcen´e pouze pro v´yvoj´aˇre PostgreSQL, a maj´ı umoˇznit vzd´alenou diagnostiku nahl´aˇsen´ych probl´em˚ u.

postgresql.conf II. efective cache size pˇredpokl´adan´a velikost celkov´e diskov´e vyrovn´avac´ı pamˇeti pouˇzit´e pro jeden dotaz (vˇcetnˇe shared buffers PostgreSQL a ˇc´asti sys. cache pouˇzit´e pro soubory PostgreSQL). Pozor na soubˇeˇzn´e dotazy z r˚ uzn´ych tabulek, kter´e se mus´ı vej´ıt do dostupn´eho prostoru. Tato hodnota nesouvis´ı se skuteˇcnou potˇrebou pamˇeti. Vyˇsˇs´ı hodnota znamen´a preferenci indexu (vyˇsˇs´ı pravdˇepodobnost, ˇze cennovˇe n´aroˇcnˇejˇs´ı index nalezneme v cache). Niˇzˇs´ı preferenci sekvenˇcn´ıho ˇcten´ı tabulky. V´ychoz´ı nastaven´ı je 128 M. V Linuxu RAM - o.s. ostatn´ı aplikace (Linux agresivnˇe pouˇz´ıv´a cache).

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

26 / 36

Konfigurace datab´aze Parametrizace procesu autovacuum

Strategie ˇ ejˇs´ı proveden´ı pˇr´ıkazu VACUUM neˇz je nutn´e, je menˇs´ım zlem neˇz nedostateˇcnˇe Castˇ ˇcast´e prov´adˇen´ı tohoto pˇr´ıkazu. Spouˇst´ı se periodicky (cron) nebo pˇri pˇrekroˇcen´ı dynamick´e prahov´e hodnoty (autovacuum). Tato hodnota roste s velikost´ı tabulky.

postgresql.conf III. stats row level aktualizace provozn´ıch statistik (reˇzie 20%). autovacuum povolen´ı samotn´eho procesu (vyˇzaduje provozn´ı statistiky) Pˇr´ıkaz VACUUM se spust´ı, pokud poˇcet modifikovan´ych ˇr´adk˚ u je vˇetˇs´ı neˇz autovacuum vacuum threshold + (autovacuum vacuum scale factor ∗ velikost tabulky ). Pˇribliˇznˇe 20% poˇctu ˇr´adek v tabulce. Pˇr´ıkaz ANALYZE se spust´ı, pokud poˇcet modifikovan´ych ˇr´adk˚ u je vˇetˇs´ı neˇz autovacuum analyze threshold + (autovacuum analyze scale factor ∗ velikost tabulky ). Pˇribliˇznˇe 10% poˇctu ˇr´adek v tabulce.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

27 / 36

Monitorov´an´ı datab´aze Sledov´ an´ı aktivity

Pohled do syst´emov´ych tabulek pohled pg stat activity obsahuje pˇrehled ˇcinnosti pˇrihl´aˇsen´ych klient˚ u. pohled pg stat database obsahuje poˇcet pˇrihl´aˇsen´ych klient˚ u k jednotliv´ym datab´az´ım. pohled pg stat all tables obsahuje provozn´ı statistiky tabulek. pohled pg stat all indexes obsahuje provozn´ı statistiky index˚ u. pohled pg locks obsahuje seznam aktivn´ıch z´amk˚ u.

postgresql.conf IV. Sledov´an´ı d´eletrvaj´ıc´ıch a chybn´ych dotaz˚ u: log min error statement = error loguje vˇsechny chybn´e SQL pˇr´ıkazy. log min duration statement = 200 loguje vˇsechny SQL pˇr´ıkazy prov´adˇen´e d´ele neˇz 200ms. Na vytˇeˇzen´ı u ´daj˚ u z logu lze pouˇz´ıt tzv. PostgreSQL log analyzer pgFouine.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

28 / 36

Monitorov´an´ı datab´aze Zjiˇstˇen´ı obsazen´eho prostoru datab´ azov´ymi objekty -- setˇ r´ ıdˇ en´ y v´ ypis seznamu datab´ az´ ı podle velikosti root=# select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname) desc; datname | pg_size_pretty ------------+---------------root | 168 MB postgres | 4088 kB regression | 3864 kB ... -- setˇ r´ ıdˇ en´ y v´ ypis tabulek v datab´ azi podle velikosti root=# select n.nspname, c.relname, pg_size_pretty(pg_total_relation_size(c.oid)) from pg_class c left join pg_catalog.pg_namespace n on n.oid = c.relnamespace where c.relkind = ’r’ order by pg_total_relation_size(c.oid) desc limit 10; Schema | relname | pg_size_pretty ------------+----------------+---------------root | test_data | 138 MB public | accounts | 25 MB pg_catalog | pg_proc | 864 kB pg_catalog | pg_depend | 744 kB pg_catalog | pg_attribute | 536 kB public | history | 344 kB Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

29 / 36

25. 2. 2007

30 / 36

Monitorov´an´ı datab´aze Monitorov´ an´ı pod´ılu nedostupn´ych z´ aznam˚ u -- funkce pgstattuple a pgstatindex z bal´ ıcku pgstattuple postgres=# \x Expanded display is on. postgres=# select * from pgstattuple(’foo’); -[ RECORD 1 ]------+-----table_len | 8192 tuple_count | 0 tuple_len | 0 tuple_percent | 0 dead_tuple_count | 2 dead_tuple_len | 93 dead_tuple_percent | 1.14 free_space | 8064 free_percent | 98.44 postgres=# select * from pgstatindex(’foox’); -[ RECORD 1 ]------+----version | 2 tree_level | 0 index_size | 8192 root_block_no | 1 internal_pages | 0 leaf_pages | 1 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 0.93 leaf_fragmentation | 0 Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

Instalace doplˇnk˚ u Postup

Pozn´amka PostgreSQL je navrhov´an minimalisticky, tj. co nemus´ı b´yt ˇc´ast´ı j´adra, pˇresouv´a se do rozˇsiˇruj´ıc´ıch (contrib) modul˚ u. Pˇr´ıkladem m˚ uˇze b´yt tsearch2, fuzzystrmatch, pgcrypto nebo pgbench. Uk´azka obsahuje instalaci doplˇ nku orafce, coˇz je sada funkc´ı inspirovan´a knihovnou RDBMS Oracle. 1 2 3

4

Pokud jste v adres´aˇri contrib make; make install, V priv´atn´ım adres´aˇri make USE PGXS=1; make USE PGXS=1 install Z podadres´aˇre postgresql share/contrib naˇc´ıst soubor orafce.sql jako superuser psql db < /usr/local/pgsql/share/contrib/orafce.sql U nˇekter´ych doplˇ nk˚ u je nutn´e explicitnˇe zpˇr´ıstupnit nov´e funkce pˇr´ıkazem GRANT. T´ımto zp˚ usobem urˇcujeme, kdo sm´ı nov´e funkce pouˇz´ıvat.

pgbench pgbench je jednoduch´a klientsk´a aplikace, kter´a generuje unifikovanou z´ateˇz serveru PostgreSQL. V podstatˇe nelze jednoznaˇcnˇe interpretovat v´yslednou hodnotu ud´avaj´ıc´ı poˇcet realizovan´ych z´akaznick´ych transakc´ı za vteˇrinu. Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

31 / 36

Instalace doplˇnk˚ u Testov´ an´ı

Regresn´ı test Kaˇzd´y doplnˇek obsahuje sadu testu umoˇzn ˇuj´ıc´ıch alespoˇ n r´amcovˇe ovˇeˇrit funkˇcnost na dan´e platformˇe. Pokud selˇze regresn´ı test, reportujte popis chyby a platformy spr´avci testovan´eho doplˇ nku. make USE_PGXS installcheck

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

32 / 36

Postup pˇri pˇrechodu na novou verzi Pl´ an

Pozn. Pˇri minoritn´ı zmˇenˇe (zmˇena za destinou teˇckou) jsou verze datovˇe kompatibiln´ı (tud´ıˇz staˇc´ı pouze zmˇenit bin´arn´ı soubory). Pˇri aktualizaci mezi nekompatibiln´ımi verzemi je tˇreba prov´est dump datab´aze. V pˇr´ıpadˇe, kdy budeme migrovat na novˇejˇs´ı verzi je doporuˇcov´ano, aby dump byl provedem pˇr´ıkazem pg dump z novˇejˇs´ı verze (tj. nejdˇr´ıve aktualizujeme klientsk´e aplikace, pot´e server ... vˇsechny PostgreSQL aplikace se dok´aˇzou pˇripojit k serveru jin´e verze (pouze dostanete varov´an´ı)). Aktu´aln´ı verze dok´aˇz´ı naˇc´ıst dump z pg dump verze 7.0.

Tip Migraci m˚ uˇzeme zkr´atit paraleln´ım provozem nov´eho serveru na jin´em portu a migrac´ı pˇres rouru: pg_dumpall -p 5432 | psql -d postgres -p 6543 Ovˇeˇrte si, ˇze V´am nikdo v t´e dobˇe nepˇristupuje k SQL server˚ um. Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

33 / 36

Postup pˇri pˇrechodu na novou verzi Moˇzn´e probl´emy

Migraci vˇ zdy testujte Jeˇstˇe pˇred dumpem v novˇejˇs´ı verzi vytvoˇrete z´alohu v aktu´aln´ı verzi. Nen´ı zaruˇceno, ˇze se starˇs´ı klient dok´aˇze pˇripojit k novˇejˇs´ımu serveru, tj. bez t´eto z´alohy by cesta zpˇet mohla b´yt obt´ıˇzn´a. Prostudujte si odpov´ıdaj´ıc´ı RELEASE NOTES. V ide´aln´ım pˇr´ıpadˇe m´ate k dispozici UNIT testy. Upgrade pˇr´ıliˇs neodkl´adejte, je jistˇejˇs´ı udˇelat nˇekolik menˇs´ıch krok˚ u, neˇz jeden skok. Nov´e verze PostgreSQL vych´azej´ı jednou roˇcnˇe. Zaruˇcenˇe podporovan´e jsou dvˇe verze zp´atky (oprava chyb, bezpeˇcnostn´ı z´aplaty, atd). Jako optim´aln´ı je upgrade kaˇzde dva roky.

Kde mohou nastat probl´emy? pˇr´ısnˇejˇs´ı kontrola UTF8 ve verzi 8.2 (tj. pˇredchoz´ı dump m˚ uˇze b´yt povaˇzov´an za nekorektn´ı). Oprava - pouˇzit´ı filtru iconv. pˇr´ıliˇs star´a verze PostgreSQL (7.3 a starˇs´ı) - prov´adˇejte upgrade inkrement´alnˇe.

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

34 / 36

Postup pˇri pˇrechodu na novou verzi Zrychlen´ı naˇcten´ı dumpu

Tip Naˇc´ıt´an´ı dump souboru lze urychlit doˇcasn´ym pˇrenastaven´ım n´ıˇze urˇcen´ych konfiguraˇcn´ıch parametr˚ u. Tyto hodnoty jsou urˇcen´e pouze pro naˇc´ıt´an´ı dat (pˇredpokl´adaj´ı jednouˇzivatelsk´y reˇzim), kter´e lze v pˇr´ıpadˇe probl´em˚ u opakovat, a nejsou urˇcen´e pro produkˇcn´ı reˇzim. fsync = off shared_buffers = [1/3 of memory] wal_buffers = 1MB checkout_timeout = 1h checkpoint_segments = 300 maintenance_work_mem = [1/3 of memory] Nezapomeˇ nte, ˇze pro upgrade potˇrebujete minim´alnˇe 2x tolik voln´eho prostoru, cen´ı importu nezapomeˇ nte jako je aktu´aln´ı velikost datov´eho adres´aˇre. Po dokonˇ konfiguraci nastavit na provozn´ı hodnoty. fsync = off m˚ uˇ ze v´ est ke ztr´ atˇ e dat. Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e

25. 2. 2007

35 / 36

25. 2. 2007

36 / 36

PostgreSQL 8.2 efektivnˇe Administrace Pavel Stˇehule http://www.pgsql.cz

25. 2. 2007

Pavel Stˇ ehule (http://www.pgsql.cz)

PostgreSQL 8.2 efektivnˇ e