jeden z klientów eo, firmy gdzie pracuję, właśnie kupił nowy serwer pod bazy danych. ponieważ maszynkę trzeba przetestować, stwierdziłem, że przy okazji porównam sobie jej wydajność z innym serwerem – nieużywanym już starszym serwerem bazodanowym.

poprzednia maszynka:

• 2 procesory amd opteron 250, 2.4ghz
• 4 giga ramu
• 4 dyski scsi, 15krpm, spięte sprzętowym raidem w dwie macierze raid1 po dwa dyski.
• postawiona pod centosem z kernelem 2.6.9-22.ELsmp

czyli tak naprawdę bez przesadnych rewelacji.
testowałem przy pomocy takiego skryptu:

#!/bin/bash
export TOTAL_TRANS=1000000
for C in 1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100
do
pg_ctl -w -l logs/x.log start
dropdb bench
createdb bench
pgbench -i -s 750 -U pgdba -d bench
TRANS=$[ $TOTAL_TRANS / $C ]
pgbench -s 750 -c $C -t $TRANS -U pgdba -d bench 2> /dev/null > /home/pgdba/bench-$CC-$C.std
pg_ctl -m immediate stop
killall -9 postgres
killall -9 postmaster
killall -9 postgres
killall -9 postmaster
done

proste i konkretne. baza bench ma około 13 giga (aha. gdybyście chcieli powtórzyć test u siebie – pgbench się wywala na -s 750 (na niższych trochę pewnie też) i trzeba go zapatchować – w pgbench.c znaleźć polecenia tworzenia tabel i zastąpić inty bigintami.

postgresql'a użyłem 8.1.4, postgresql.conf zawierał wartości domyślne, zmieniłem jedynie 2 parametry:

max_connections = 110
shared_buffers = 65536
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

czyli jak widać nic specjalnego – chodziło mi nie o tuning postgresa, tylko o sprawdzenie o ile mi mniej więcej przyspieszy system po zmianie sprzętu.

wyniki starszego serwera:

rzuca się w oczy stabilność wydajności przy przyroście jednocześnie połączonych klientów.

nowa maszynka:

• 4 procesory amd opteron 875, 2.2ghz
• 32 giga ramu
• 2 dyski sas na system, 14 dysków w zewnętrzen macierzy scsi, 15krpm, spięte w jedną dużą macierz raid10
• postawiona pod debinanem testing z kernelem 2.6.16-2-amd64-k8-smp

test i konfiguracja postgresa – identyczne.

wyniki:

tym razem rzuca się w oczy brak stabilności wyników przy większej ilości połączeń.

ale też liczby są sporo wyższe 🙂 ponad 6 krotnie wyższe.

zakładam, że brak stabilności może wynikać z tego, że dla tej maszyny ta baza była mała. tzn. baza mająca 13 giga dla maszyny z 4giga ramu to sporo. ale dla maszyny z 32 giga ramu, to drobiazg.

zrobię jeszcze jeden test (będzie dłuższy, więc wyniki pewnie dopiero w poniedziałek) – dokonfiguruję postgresa do końca, i zrobię test na bazie danych tak ze 110 – 120 giga. postaram się zrobić testy do większej ilości klientów.

może to wyjaśni przyczynę dziwnych wahań wyników.

aha – testy nie są idealne – pgbench ma swoje standardowe problemy. dodatkowo – ta maszyna, co prawda nie jest używana normalnie, ale chodzi na niej cron który np. w nocy włącza “updatedb" to pewnie trochę zaburza wyniki.

jeszcze tylko muszę się dowiedzieć ile te maszynki kosztowały aby móc porównać “bang-for-bucks" 🙂

możliwe, że kojarzycie firmę enterprisedb. firma ta sprzedaje “paczkowanego" postgresa z dodatkami – replikacją, debuggerem funkcji pl/*, narzędziami upraszczającymi migrację z oracle'a.

co istotne – enterprisedb bardzo aktywnie pomaga rozwijać opensource'owego postgresa – zatrudniając core-developerów, backportując swoje zmiany i ogólnie mocno się angażując.

ostatnio miały miejsce 2 sympatyczne wydarzenia związane z enterprisedb.

po pierwsze – 1 sierpnia poinformowali, że zakończyli drugą rundę zbierania finansów i mają teraz o 20 milionów dolarów więcej. kasa zostanie przeznaczona na merketing i poszerzanie rynków sprzedaży.

mimo, że pewnie wolałbym by wpakowali te 20 milionów w programistów o tyle widzę kolosalny sens tego co robią. jeśli faktycznie uda im się sprzedać więcej licencji, pakietów supportowych itd, to powstanie mocna grupa płacących użytkowników. a mając płacących uzytkowników będzie prościej o ludzi którzy będą mogli implementować nowe fajne rzeczy w postgresie – także tym open source'owym.

po drugie – sun microsystems (ta sama firma która ostatnio zwolniła 5000 ludzi), reklamował sie niedawno, że będzie oferował support do postgresa. teraz (informacja z wczoraj) okazało się, że support świadczyć będzie enterprisedb. po prostu sun podpisał z nimi umowę na świadczenie supportu w imieniu sun'a.

dzięki temu jawi się kilka rzeczy:

• postgresql zyskuje realną, silną pomoc od suna
• support świadczony przez suna będzie na wysokim poziomie – i nie dlatego, że sun na szybko kogoś wyszkoli, ale dlatego, że support będą świadczyli ludzie którzy na postgresie “zęby pozjadali"
• enterprisedb na tym zarobi 🙂

to wszystko powoduje, że z coraz większym optymizmem patrzę na przyszłość swojej ulubionej bazy.

na listach postgresowych pojawiły się wątki tyczące tego, że ludzie (w tym np. ja) nie dostrzegli wszystkich zmian.

faktycznie tak jest – bazowałem na “todo", a okazuje się, że jest sporo fajnych zmian nie uwzględnionych w tym dokumencie.

podrzucam zatem drugą część zmian:

• wartości wielorekordowe. używane np. przy insertach (insert into table (a,b,c) values (1,2,3), (4,5,6), …; – choć nie wiem czy taka dokładnie składnia będzie) – ale także wszędzie gdzie jest dopuszczalny (sub-)select.
• pg_dump będzie obsługiwał wielokrotne -n oraz -t, oraz umożliwiał wybieranie obiektów do zdumpowania przy pomocy regexpa. dla mnie osobiście jest to wielki bonus, bo umożliwi mi wreszcie normalne dumpowanie baz replikowanych slony'ym.
• agregaty wieloargumentowe – w tym agregaty statystyczne uwzględnione w standardzie sql2003
• porównywanie zmiennych “rekordowych". np. możliwośc zrobienia w triggerze: if row(new.*) is distinct from row(old.*)
• DROP … IF EXISTS
• poprawki w tsearch2 – wydajność, ale także funkcjonalność – tezaurus!
• nowy typ indeksów – gin. służy do indeksowania list odwrotnych – w szczególności powstał na potrzeby tsearch'a, ale można go używać niezależnie
• GRANT CONNECT ON DATABASE – dla wielu ludzi będzie to zbawienie, bo przestaną się martiwć, że ktoś obcy zobaczy nazwy ich tabelek 🙂
• funkcje w pl/pythonie obsługuja nazwane argumenty oraz mogą zwracać dane rekordowe.
• stats_command_string – pokazywanie aktualnie wykonywanego zapytania przez backend – do wersji 8.1.x wiązała się z pewnym (czasem dosyć sporym) obciążeniem serwera. w 8.2 overhead tego został praktycznie wyeliminowany i funkcja te jest włączona domyślnie 🙂

dodatkowo, w kolejce są jeszcze pewne patche, które nie zostały jeszcze zaakceptowane, ale ponieważ zostały zgłoszone przed ogłoszeniem feature-freez'a to mają szanse na włączenie do 8.2. są to między innymi:

• budowanie indeksów w locie, bez lockowania indeksowanej tablicy!
• update'owalne widoki. tzn. funkcjonalność do tego była od dawna, ale teraz ma to działać automatycznie. przynajmniej w relatywnie prostych sytuacjach.
• funkcjonalność INSERT/UPDATE RETURNING. czyli np. wydanie polecenia insert, tak aby został od razy zwrócony id nowo dodanego rekordu.

plus jeszcze jedna rzecz o której nie napisałem poprzednio (nie wiem czemu, wiedziałem, ale jakoś mi umknęło). w wersji 8.2 jest dużo poprawek tyczących się wydajności. naprawdę dużo. są ludzie którzy już teraz przesiadają się na 8.2 bo system “lata" zamiast “chodzić" czy “pełzać".

martijn van oosterhout zaimplementował interesujące rozszerzenie postgresa.

są to mianowicie typy danych z przechowywanymi w danych (i obsługiwanymi) jednostkami.

przykładem użycia może być typ danych do przechowywania kwot, wraz z walutą.

innym – wartości fizyczne wraz z jednostkami – np. ‘9.81 mps2′.

fajne w tym jest to, że te dane działają logicznie. to znaczy. mając typ finansowy, i wykonując dodawanie:

select '15 USD'::currency + ‘25.1 USD'::currency;

da oczekiwany, poprawny wynik. jednocześnie system jest na tyle sprytny, że przy próbie dodawania wartości z różnymi jednostkach zgłasza błąd.

oczywiście użyteczność tego sięga poza dodawanie 🙂

ogólnie – dzięki taggedtypes można sobie zdefiniować dowolne typy danych z dowolnymi tagami.

niestety – na chwilę obecną w systemie jest kilka ograniczeń. ale – dzięki temu, że całośc jest dostępna ze żródłami można sobie prosto dopisać dowolne dodatkowe funkcje. np. do typu walutowego przydatne byłoby automatyczne przeliczanie po aktualnym kursie by móc uzyskać np. coś takiego:

SELECT ‘1 PLN'::currency + ‘1 USD'::currency;

zwracające zsumowane kwoty “po kursie dnia".

albo po prostu tworzące wartość ‘1 PLN 1 USD' która nadal będzie prawidłowo działać 🙂

możliwości jest sporo. podwaliny już są. czy coś dalej z tego wyjdzie – to już zależy od użytkowników i zastosowań jakie sobie wymyślą 🙂

http://svana.org/kleptog/pgsql/taggedtypes.html

półtora roku temu (początek stycznia 2005) firma pervasive (znana jako producent komercyjnej bazy danych do zastosowań małych i średnich) ogłosiła, że będzie:

• oferować komercyjne wsparcie do postgresa
• oferować gotowy, “zabundlowany" pakiet – postgres (z ich łatkami) plus wsparcie
• pomagać developerom w rozwijaniu kodu

tydzień temu, w otwartym liście do “postgresql community", prezes pervasiva stwierdził, że rezygnują z tego “projektu".

nie postgresa tylko wsparcia i całej otoczki.

podobno przyczynami jest to, że nie docenili ilości i jakości wsparcia jakie użytkownicy mają już teraz dostępne na internecie (listy mailingowe, kanały irc). jak dla mnie oznacza to to, że mieli za mało klientów na te usługi. może nawet wcale nie mieli?

john farr (prezes pervasiva) stwierdził, że oczywiście będą supportować aktualnych klientów, ale nowych umów zawierać już nie będą.

do tej informacji mam mieszane uczucia. z jednej strony – szkoda trochę, pervasive jest trochę znany więc udany mariaż dałby większe publicity postgresowi, z drugiej strony – nie kojarzę co takiego cudownego ludzie z pervasive'a zrobili dla postgresa. w/g farra dali kod do dtrace'a, ale on będzie użyteczny (o ile wiem) jedynie pod solarisem. a to nie jest zbyt popularny system.

z trzeciej strony – wolę by rozwijały się i zyskiwały klientów firmy stricte postgresowe – command prompt, sra, greenplum.

więc chyba ogólnie jestem raczej zadowolony z takiego obrotu zdarzeń.

poniższa lista na pewno nie jest kompletna. na pewno będą tony bugfixów, ale tu postaram się tylko wyliczyć to co moim subiektywnym zdaniem najistotniejsze. za podstawę służy nowiutka, prosto z cvs'u, kopia pliku TODO informującego co jest do zrobienia, a co już jest gotowe.

• połączenie do postgresa będzie mogło wylistować wszystkie “prepared statements". jest to szczególnie istotne/pomocne w przypadku korzystania z mechanizmów do buforowania (poolowania) połączeń
• ponownie włączona zostaje obsługa opcji full_page_writes w konfiguracji – kod obsługujący został poprawiony, błędy usunięte. opcja ta ma służyć  do przyspieszenia obsługi wal'a.
• możliwość  przeglądania i kasowania logów serwera zdalnie poprzez wykonywanie odpowiednich zapytań sql
• sporo poprawek tyczących obsługi typów danych inet i cidr
• dodana została funkcja sleep() pozwalająca symulować działanie długich zapytań do testów  bazy pod kątem wielozadaniowości
• funkcje definiowane przez użytkownika, zdefiniowane jako zwracające typy domenowe (CREATE DOMAIN) będą miały (wreszcie!) obsługę constraint'ów domen.
• limit/offset oraz  fetch/move będą teraz używały int8 – dzięki czemu będzie można wygodniej obsługiwać naprawdę duże recordsety
• truncate dorobi się opcji cascade (no i restrict) dzięki czemu będzie kasować też tablice zależne. groźne, ale przydatne.
• prepare będzie się automatycznie domyślał typów parametrów poprzez analizę zapytania
• możliwośc dodawania komentarzy do wszystkich typów obiektów (w tym ról, tablespace'ów i baz)
• operatory pracujące na zmiennych typu rekordowego, np. (a,b) < (1,2) będą działać zgodnie ze specyfikacją sql'a.
• dodany widok systemowy pokazujący zawartośc free-space-map.

wydaje się być interesujące. nie widzę tu przełomów (choć dodanie prawidłowo działających operatorów rekordowych jest na pewno istotne) na miarę toast'a czy srf'ów, ale jest to zdecydowanie kawał dobrej roboty.

dowiedziałem się właśnie, że postgres 8.2 wszedł właśnie w fazę feature freeze.

konsekwencje:

• można się spodziewać relatywnie niedługo 8.2 stable.
• nowe super feature'y na serwerach produkcyjnych 🙂

co dokładnie będzie nowego – postaram się jeszcze dziś napisać.

teoretycznie napisanie polecenia update jest bardzo proste:

update tabelka set pole = ‘xxx' where id = yy;

tylko czy to na pewno słuszny sposób?

otóż nie.

problem polega na tym, że w postgresie działa cos co nazywa się mvcc. nie wnikając w szczegóły (jak kogoś z was bardzo interesują, odsyłam do dokumentacji postgresa) – powoduje to, że każdy update to tak naprawdę delete + insert.

efekty – tabelka rośnie. rosną też indeksy na polach które nie zostały zaktualizowane!

zostałem przez ten efekt dosyć boleśnie “ugryziony" gdy okazało się, że dosyć spora tabelka (1.5 miliona rekordów, 5 gigabajtów wielkości) rośnie mi jak na drożdżach, bo bardzo często robię w niej update jednego pola. a że na tabelce jest 15 indeksów to efekt się multiplikował.

rozwiązanie? wykonywanie update'a tylko gdy naprawdę jest konieczny:

update tabelka set pole = ‘xxx' where id = yy and pole <> ‘xxx';

sprawa się nieco komplikuje gdy pole może być null'em, ale na szczęście jest (mało znany) operator “is distinct from", i możemy zapisać zamiast:

update tabelka set pole = ‘xxx' where id = yy and (pole is null or pole <> ‘xxx');

to:

update tabelka set pole = ‘xxx' where id = yy and pole is distinct from ‘xxx';

co istotne – jeśli już modyfikujemy choć jedno pole – to modyfikacje kolejnych nie mają tego złego efektu.

więc nie musimy robić:

update tabelka set pole = ‘xxx' where id = yy and pole is distinct from ‘xxx';

update tabelka set pole_2 = ‘x2' where id = yy and pole_2 is distinct from ‘x2';

itd.

wystarczy (i zdecydowanie lepiej zadziała):

update tabelka set pole = ‘xxx', pole_2 = ‘x2' where id = yy and (pole is distinct from ‘xxx' or pole_2 is distinct from ‘x2');
dodam jeszcze, że o ile vacuum świetnie sobie radzi z odzyskiwaniem miejsca po update'ach w plikach tabel, o tyle z miejscem w plikach indeksów nie jest tak słodko 🙁
inną opcją (dodatkowo pomagającą) jest:

jesli macie dużą tabelkę (kilkanaście kolumn, sporo danych) i tylko jedno/kilka pól jest często aktualizowanych, przy czym reszta danych (w szczególności indeksy) nie jest dotykana – warto się zastanowić by te kolumny wydzielić do oddzielnej tablicy i połączyć relacją 1-do-1.

od razu ostrzegam: to nie zawsze da dobry rezultat. ale może warto spojrzeć. zmniejszone pliki to zmniejszone zapotrzebowanie na pamięć do cache'a dyskowego. a więc cały system zaczyna szybciej działać.

kilka dni temu odbyło się oficjalne zawiązanie nowej grupy użytkowników postgresql'a – portland postgresql users group. na inauguracyjne spotkanie został zaproszony w charakterze mówcy david wheeler – znany głównie jako osoba zaangażowana w rozwój bricolage – opartego na postgresie systemu klasy cms.

david na temat swojego wystąpienia wybrał sobie pl/pgsql.

przygotował sporą prezentację i wystąpił. wstęp został przyjęty bardzo dobrze. a slajdy zostały udostępnione na sieci. jeśli nie pisaliście nic w tym języku, lub nie czujecie się w nim do końca pewnie – zdecydowanie warto obejrzeć.

po udanym postawieniu serwera, przychodzi kolej na to by go nadzorować. spora część ludzi używa w tym celu softu który automatycznie robi wykresy różnych parametrów. przykładem (którego używam np. ja) jest mrtg. są też inne pakiety, lepsze czy gorsze. używam i lubię mrtg.

mrtg potrafi rysować wykresy nie tylko ruchu na interfejsach, ale praktycznie dowolnych danych podawanych w postaci liczbowej.

dzięki temu używam go do rysowania wykresów:

• loadu systemowego
• zużycia pamięci
• zużycia procesorów
• wolnego miejsca na dyskach
• ruchu na interfejsach sieciowych
• ruchu na swapie (in/out)

to monitoruję na każdym serwerze – niezależnie czy to webserwer, serwer aplikacyjny z jbossem, webproxy, firewall czy serwer bazodanowy.

ale na serwerach bazodanowych przydają się też inne parametry.

zanim przejdę do ich omówienia krótki wtręt.

mrtg potrafi pobierać dane poprzez uruchamianie programów które mu dane zwracają, lub poprzez odpytywanie zdalnego serwera snmp. wybrałem to drugie rozwiązanie, bo daje mi trochę więcej możliwości – np. mogę wykresy rysować na zupelnie innej maszynie niż ta której wykresy dotyczą.

do każdej znanej mi implementacji snmp jest możliwość podpięcia zewnętrznych skryptów/programów pod tzw. mib'y. dzięki temu mogę dowolnie rozszerzać funkcjonalność snmp i dodawać monitorowanie parametrów o których twórcy snmpd nie mieli pojęcia 🙂

pierwszym niestandardowym parametrem który monitoruję na serwerach bazodanowych jest ilość operacji i/o w podsystemie dyskowym.

postgresql jest mocno czuły na obciążenie dysków i tzw. iowaity powodują, że maszyna zachowuje się jakby ktoś jej podciął skrzydła. procesor jest wolny, wszystko działa ok, ale postgres się wlecze.

w używanym przeze mnie net-snmpd, odpowiednie wartości są dostępne pod mib'ami: ssIORawSent.0 i ssIORawReceived.0.

odpowiedni kawałek configu mrtg:

Target[db_sys_iorawsent]: ssIORawSent.0&ssIORawSent.0:public@db
Options[db_sys_iorawsent]: growright
MaxBytes[db_sys_iorawsent]: 100000000
Title[db_sys_iorawsent]: IORawSent at db
PageTop[db_sys_iorawsent]: IORawSent at db

(można umieścić oba wykresy na jednym obrazku, ale z przyczyn pozamerytorycznych wolę mieć każdy oddzielnie).

następnymi (i ostatnimi ogólno systemowymi) parametrami jakie monitoruję jest ilość context-switchy i przerwań w systemie.

dostępne jest to u mnie pod mibami: ssRawContexts.0 i ssRawInterrupts.0

i teraz przechodzimy do części najzabawniejszych.

co monitorować w samym postgresie.

przede wszystkim – jak? przygotowałem sobie prosty program który wykonuje jedno zadane polecenie w bazie danych i zwraca wynik na stdout. ten skrypt podłączam do snmp wykorzystując funkcję “exec" z snmpd.conf.

i lecimy z zapytaniami:

1. select pg_database_size(‘nazwa_bazy_której_wielkość_chcesz_monitorować');
   zwraca wielkość bazy w bajtach
2. select cast(extract(epoch from now() – query_start) * 1000 as int8) from pg_stat_activity where current_query !~ ‘stats_command_string w postgresql.conf
3. select pg_relation_size(‘nazwa_relacji');
   zwraca wielkość w bajtach dowolnej relacji – czyli tabeli czy indeksu. warto w ten sposób monitorować tabele czy indeksy które mają tendencje do puchnięcia (np. jakieś tabele z kolejkami zadań do wykonania).
4. select sum(xact_commit) + sum(xact_rollback) from pg_stat_database;
   zapytanie to pokazuje ile transakcji (od ostatniego restartu) postgres wykonał (niezależnie czy były one zatwierdzone czy wycofane).
   zbierając tę ilośc co jakiś czas można prosto policzyć ile transakcji na sekundę postgres robi. można też zmodyfikować to zapytanie tak aby liczyło nie transakcje w ogóle przetworzone przez postgresa tylko w konkretnej bazie – modyfikacje zapytania pozostawiam czytelnikom, jest trywialna 🙂

jeśli używacie replikacji, a do replikacji slony'ego, warto monitorować opóźnienie replikacyjne. opóźnienie to wyraża się w dwóch wartościach:

• lag_events
• lag_time

lag_events jest to ilość zdarzeń modyfikujących bazę które jeszcze nie zostały przesłane na bazy podrzędne. lag_time jest to opóźnienie replikacji wyrażone jako czas.

wyciągnąć obie wartości można tak:

select st_lag_num_events, st_lag_time from _slony.sl_status

przy czym aby móc to rysować lepiej jest przeliczyć lag_time na sekundy:

select st_lag_num_events, cast(extract(epoch from st_lag_time) * 1000 as int8) from _slony.sl_status

zwrócić należy uwagę, iż jeśli lag_events jest równy 0, to lag_time jest nieistotny.
na koniec coś na zewnątrz sql'a.

w postgresql.conf jest taka opcja: log_min_duration_statement.

pozwala ona na logowanie długich zapytań. tu uwaga – ta opcja jest zupełnie niezależna od log_statement czy log_duration!

wartość opcji log_min_duration_statement oznacza liczbę milisekund jaką zapytanie się wykonywało by zostało uznane za zbyt długie, i zalogowane. ja osobiście wpisuję tam wartość 1000 – co powoduje, że każde zapytanie trwające powyżej 1 sekundy zostanie zapisane w logach. np. tak:

[2006-07-14 00:00:04 CEST] [25989] LOG: duration: 1133.235 ms statement: SELECT id, file_extension FROM flickr_images WHERE id > ‘0' AND size_x_orig = 0 and status >= 0 ORDER BY id ASC

zwracam tylko uwagę na fakt iż to, że zapytanie się długo wykonuje niekoniecznie jest problemem zapytania. najczęstszą sytuacją jest to, że pojawia się zapytanie które wykonuje się dosyć długo, ale poza tym przyblokowuje ono zupełnie inne zapytania – które też nagle zaczynają się długo wykonywać.

tak więc nie należy patrzeć na listę “długich" zapytań jako listę rzeczy do poprawienia, tylko jako na wsad do analizy statystycznej jakie zapytania pojawiają się najczęściej. i dlaczego.

to zasadniczo kończy temat. monitorować można sporo więcej – choćby ilość procesów postgresa, czy ilości odczytów via seq-scan czy index-scan. natomiast wydaje mi się, że te parametry mogą być istotne “punktowo", a niekoniecznie muszą być od razu rysowane.