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.
2. Wywołanie 5x pod rząd tej samej pustej funkcji wraz z przykładowymi czasami wywołania
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( )
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( )
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( )
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
Wyniki odczytu: (im mniej ms tym lepiej)
1. pl/tcl
2. pl/python
3. pl/perl
4. pl/pgsql
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
Komentarze (1) 
