Wpisy z October, 2007

Benchmark procedur w PostgreSQL

Napisano October 9, 2007 * Komentarze (1)

Podczas pisania jednej z naszych aplikacji webowych chcieliśmy mieć możliwość dostępu do numeru IP oraz ID użytkownika w każdej z funkcji PostgreSQL. Aby uniknąć przekazywania tych wartości do każdej z funkcji korzystając z listy argumentów (mamy tych funkcji około 110) skorzystaliśmy z tabel tymczasowych w PostgreSQL. Przy każdym połączeniu z bazą danych, uruchamiana była następująca procedura:

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE OR REPLACE FUNCTION set_connection_data_old(_ip text, _user_id integer)
RETURNS BOOLEAN AS
$BODY$
BEGIN
EXECUTE 'CREATE TEMP TABLE user_connection_data (ip text, user_id integer)';
INSERT INTO user_connection_data (ip, user_id) VALUES (_ip, _user_id);
IF FOUND THEN
RETURN true;
ELSE RETURN false;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Zainstalowany skrypt do analizowania logów PostgreSQL wykazał, iż procedura ta jest najczęściej wywoływaną instrukcją (co oczywiście nie jest odkrywcze), jednak przeciętny czas jej wywołania (60-70ms) wydawał się nam zbyt duży. W przypadku nieposprzątanej bazy danych czas potrafił się wydłużyć do kilku – kilkunastu sekund!

Oczywiście, można przekazywać te dwie informacje (IP oraz ID użytkownika) do każdej funkcji osobno, jednak zależało nam na bardziej eleganckim rozwiązaniu. Po zaakceptowaniu straty wynikającej z konieczności uruchamiania dodatkowego zapytania, rozpoczęliśmy poszukiwania innych rozwiązań dla naszego głównego problemu, czyli przekazywania wspólnych (globalnych) zmiennych pomiędzy wywołaniami funkcji. Możliwości z którymi się spotkaliśmy są następujące:

  • tworzenie tabeli tymczasowej oraz
  • stworzenie zmiennej globalnej.

Niestety, pl/pgsql nie dysponuje natywnie możliwością tworzenia zmiennych globalnych. Możliwości takie posiada jednak zarówno pl/tcl, pl/perl oraz pl/python.

Oto przeprowadzone testy:

1. Pierwsze wywołanie pustej funkcji:

  • pl/pgsql (~4ms)
  • pl/tcl (~20ms)
  • pl/pyhon (~65ms)
  • pl/perl (~120ms)

Wyniki mówią same za siebie – pl/pgsql jest wyraźnym zwycięzcą. 5x szybszy od pl/tcl, ponad 16x szybszy od pl/pythona oraz 30x (!) szybszy od pl/perla. Kolejne wywołania funkcji w każdym przypadku były oczywiście szybsze.

wykres1

2. Wywołanie 5x pod rząd tej samej pustej funkcji wraz z przykładowymi czasami wywołania

  • pl/pgsql
LOG:  duration: 3.870 ms  statement: SELECT test_pgsql( )
LOG:  duration: 0.412 ms  statement: SELECT test_pgsql( )
LOG:  duration: 0.272 ms  statement: SELECT test_pgsql( )
LOG:  duration: 0.267 ms  statement: SELECT test_pgsql( )
LOG:  duration: 0.264 ms  statement: SELECT test_pgsql( )
  • pl/tcl
LOG:  duration: 25.939 ms  statement: SELECT test_tcl( )
LOG:  duration: 0.440 ms  statement: SELECT test_tcl( )
LOG:  duration: 0.336 ms  statement: SELECT test_tcl( )
LOG:  duration: 0.330 ms  statement: SELECT test_tcl( )
LOG:  duration: 0.330 ms  statement: SELECT test_tcl( )
  • pl/python:
LOG:  duration: 66.549 ms  statement: SELECT test_python( )
LOG:  duration: 0.497 ms  statement: SELECT test_python( )
LOG:  duration: 0.312 ms  statement: SELECT test_python( )
LOG:  duration: 0.307 ms  statement: SELECT test_python( )
LOG:  duration: 0.307 ms  statement: SELECT test_python( )
  • pl/perl
LOG:  duration: 122.523 ms  statement: SELECT test_perl( )
LOG:  duration: 0.428 ms  statement: SELECT test_perl( )
LOG:  duration: 0.278 ms  statement: SELECT test_perl( )
LOG:  duration: 0.274 ms  statement: SELECT test_perl( )
LOG:  duration: 0.274 ms  statement: SELECT test_perl( )

Zgodnie z tym czego mogliśmy się spodziewać najbardziej kosztowne jest pierwsze wywołanie funkcji (w tym przypadku rodzaj języka ma znaczenie), natomiast kolejne wywołania (niezależnie od języka) to zazwyczaj 0,2 – 0,4 ms.

3. Pięciokrotne, pojedyncze funkcji zapisującej/odczytującej w zależności od języka dwie zmienne – do tablicy tymczasowej (pl/pgsql) lub zapis jako zmienne globalne (pl/tcl, pl/perl, pl/python)

  • pl/tcl (zapis, średni czas ~26,7ms)
LOG:  duration: 26.209 ms  statement: SELECT test_store_tcl( '127.0.0.1', 234 )
LOG:  duration: 26.366 ms  statement: SELECT test_store_tcl( '127.0.0.1', 234 )
LOG:  duration: 26.108 ms  statement: SELECT test_store_tcl( '127.0.0.1', 234 )
LOG:  duration: 29.043 ms  statement: SELECT test_store_tcl( '127.0.0.1', 234 )
LOG:  duration: 26.030 ms  statement: SELECT test_store_tcl( '127.0.0.1', 234 )
  • pl/tcl (odczyt, średni czas ~0,86 ms)
LOG:  duration: 0.864 ms  statement: SELECT test_get_tcl( )
LOG:  duration: 0.867 ms  statement: SELECT test_get_tcl( )
LOG:  duration: 0.863 ms  statement: SELECT test_get_tcl( )
LOG:  duration: 0.864 ms  statement: SELECT test_get_tcl( )
LOG:  duration: 0.862 ms  statement: SELECT test_get_tcl( )
  • pl/perl (zapis, średni czas ~122,1 ms)
LOG:  duration: 121.790 ms  statement: SELECT test_store_perl( '127.0.0.1', 234 )
LOG:  duration: 122.007 ms  statement: SELECT test_store_perl( '127.0.0.1', 234 )
LOG:  duration: 122.095 ms  statement: SELECT test_store_perl( '127.0.0.1', 234 )
LOG:  duration: 122.428 ms  statement: SELECT test_store_perl( '127.0.0.1', 234 )
LOG:  duration: 122.211 ms  statement: SELECT test_store_perl( '127.0.0.1', 234 )
  • pl/perl (odczyt, średni czas ~1,66 ms)
LOG:  duration: 1.642 ms  statement: SELECT test_get_perl( )
LOG:  duration: 1.688 ms  statement: SELECT test_get_perl( )
LOG:  duration: 1.662 ms  statement: SELECT test_get_perl( )
LOG:  duration: 1.670 ms  statement: SELECT test_get_perl( )
LOG:  duration: 1.654 ms  statement: SELECT test_get_perl( )
  • pl/python (zapis, średni czas ~66 ms)
LOG:  duration: 66.196 ms  statement: SELECT test_store_python( '127.0.0.1', 234 )
LOG:  duration: 66.026 ms  statement: SELECT test_store_python( '127.0.0.1', 234 )
LOG:  duration: 66.012 ms  statement: SELECT test_store_python( '127.0.0.1', 234 )
LOG:  duration: 65.915 ms  statement: SELECT test_store_python( '127.0.0.1', 234 )
LOG:  duration: 66.228 ms  statement: SELECT test_store_python( '127.0.0.1', 234 )
  • pl/python (odczyt, średni czas ~1,52 ms)
LOG:  duration: 1.474 ms  statement: SELECT test_get_python( )
LOG:  duration: 1.718 ms  statement: SELECT test_get_python( )
LOG:  duration: 1.446 ms  statement: SELECT test_get_python( )
LOG:  duration: 1.477 ms  statement: SELECT test_get_python( )
LOG:  duration: 1.475 ms  statement: SELECT test_get_python( )
  • pl/pgsql (zapis, średni czas ~55,8 ms)
LOG:  duration: 57.036 ms  statement: SELECT test_store_pgsql( '127.0.0.1', 234 )
LOG:  duration: 65.635 ms  statement: SELECT test_store_pgsql( '127.0.0.1', 234 )
LOG:  duration: 52.147 ms  statement: SELECT test_store_pgsql( '127.0.0.1', 234 )
LOG:  duration: 57.006 ms  statement: SELECT test_store_pgsql( '127.0.0.1', 234 )
LOG:  duration: 47.622 ms  statement: SELECT test_store_pgsql( '127.0.0.1', 234 )
  • pl/pgsql (odczyt, średni czas ~3,34 ms)
LOG:  duration: 2.980 ms  statement: SELECT test_get_pgsql( )
LOG:  duration: 4.691 ms  statement: SELECT test_get_pgsql( )
LOG:  duration: 3.018 ms  statement: SELECT test_get_pgsql( )
LOG:  duration: 3.022 ms  statement: SELECT test_get_pgsql( )
LOG:  duration: 3.002 ms  statement: SELECT test_get_pgsql( )

Wyniki zapisu: (im mniej ms tym lepiej)

1. pl/tcl
2. pl/pgsql
3. pl/python
4. pl/perl

picture2

Wyniki odczytu: (im mniej ms tym lepiej)

1. pl/tcl
2. pl/python
3. pl/perl
4. pl/pgsql

picture3

Jak widać, w naszym konkretnym przypadku, najszybszy zarówno w zapisie, jak i odczycie danych okazał się pl/tcl.

W efekcie nasza funkcja została przyspieszona o 100% – średni czas wykonania został skrócony z ~60ms do ~30ms.

Oczywiście, benchmark dotyczył specyficznej funkcjonalności, stąd nie zawsze nasze wynika sprawdzą się w innym kontekście. Zapewne też najszybsze będą funkcje napisane w C, jednak w standardowej dystrybucji PostgreSQL język ten nie jest umieszczony (przynajmniej do wersji 8.2). Oto krótka specyfikacja naszego piwnicznego serwera deweloperskiego:

  • 2x Pentium III Katmai
  • 512 MB RAM
  • 2x SCSI Storage Controller (Adaptec + IBM) RAID 5
  • Slackware 10.2.0
  • PostgreSQL 8.1
  • PHP 5.2.0
  • Apche 2.0.59

Historyczny wpis na blogu ;)

Napisano October 8, 2007 * Komentarze (0)

Witaj na naszym blogu. Tematyka będzie oscylować wokół tematów powiązanych z szeroko rozumianym pojęciem “web development” – języki skryptowe, bazy danych, metodyki wytwarzania oprogramowania. Jestem przekonany, iż blog będzie tematycznie ewoluował, stąd zamiast rozpisywać się o prawdopodobnej tematyce zapraszam do przeczytania pierwszego wpisu dotyczącego benchmarku procedur w PostgreSQL.