wydajność PostgreSQL
Chciałem sprawdzić jak ma się wydajność Postgresa w pewnym interesującym mnie przypadku. Założenie było takie: tabela z 10 milionami wpisów o 3 kolumnach, w tym jedna indeksowana. Na początek mały skrypcik do wygenerowania tabeli:
#!/bin/bash
TXT="Lorem ipsum dolor sit amet"
echo "CREATE TABLE alarms(time INTEGER, usr TEXT, msg TEXT);" > "test.sql"
I=1
while [ $I -le 10000000 ]; do
U=$[ $I % 1000000 ]
T=$[ (100000*$RANDOM+$RANDOM)%2147483647 ]
echo "INSERT INTO alarms values('$T', '$U', '$TXT');" >> "test.sql"
I=$[ $I + 1 ]
done
Generowanie pliku trwało ponad 18 minut i w efekcie zajął on 766 MB
oort@gentoo ~/src/sql $ time ./generate.sh
real 18m25.219s
user 16m8.341s
sys 1m43.007s
Wersją PostgreSQL, którą poddałem testom była 8.0.15. Ponad 90 minut zajęło wstawienie danych do bazy. Najdłużej trwającą operacją zgodnie z oczekiwaniami okazał się select zawierający relację typu ‘<' (select * from alarms where time<1000). Ponieważ kolumna time nie była indeksowna, należało przejrzeć wszystkie 10 milionów wpisów. Po dodaniu indeksu typu btree nowe pomiary nieco mnie zaskoczyły gdyż czas takiego zapytania znacząco się nie zmienił. Zupełnie jakby indeksowanie nie działało. A jednak operacje typu select z relacją '=' czy delete wykonywały się szybciej. Takiego zachowania można się było spodziewać po indeksie typu hash, ale w przypadku btree również zapytania z '<' czy '>‘ powinny wykonywać się szybciej.
Z ciekawości sprawdziłem więc jak będzie to wyglądać w przypadku MySQL (wersja 5.0.44). Tu wszystko działało prawidłowo i zgodnie z oczekiwaniami. Trzeba jednak zauważyć, że MySQL wykorzystuje więcej zasobów. W przypadku dodawania wpisów do bazy użycie procesora sięgało 70% (PostgreSQL - 20%).
Wniosek był więc taki, że z Postgresem jest coś nie tak 
Uaktualniłem go do wersji 8.2.6 i powtórzyłem badania. Teraz wyniki prezentowały się dużo lepiej i indeksowanie dawało naprawdę dużo.
Poniżej znajduje się tabelka prezentująca wyniki. Jeśli nie podano inaczej jednostkami są sekundy. Kolumna “create table” zawiera czas stworzenia tabeli oraz wstawienia 10 milionów wierszy. Oczywiście w przypadku MySQLa i operacji insert czas nie wynosi 0, ale właśnie z taką, niezbyt dużą, dokładnością podaje on czasy operacji.
| DBMS | indeks |
create table |
create index |
insert | delete |
select (=) |
select (<) |
|
PostgreSQL 8.0.15 |
- | 92 m | - | 0,0001 | 4,5 | 4,5 | 4,5 |
| btree | - | 1 m | 0,0008 | 0,0008 | 0,0003 | 4,5 | |
| hash | - | 7 h | 0,0008 | 0,0008 | 0,0003 | 4,5 | |
| MySQL 5.0.44 | - | 11 m | - | 0 | 4,1 | 3,8 | 4 |
| btree | - | 78 s | 0 | 0,03 | 0,03 | 0,03 | |
|
PostgreSQL 8.2.6 |
- | 92 m | - | 0,0007 | 2,8 | 2,8 | 2,8 |
| btree | - | 46 s | 0,0008 | 0,0008 | 0,0003 | 0,0003 | |
| hash | - | 10 h | 0,0008 | 0,0008 | 0,0003 | 2,8 |
Jak więc widać dla selectów zawierających relację ‘<’ w przypadku PostgreSQL 8.0.15 indeksowanie typu btree nie dało nic, natomiast w przypadku PostgreSQL 8.2.6 zmniejszyło ich wykonywanie z 2,8 s do 0,0003 s. Indeksowanie daje więc tutaj niemal 10000 krotny wzrost prędkości.
Na koniec mała uwaga. Operacje INSERT, DELETE oraz SELECT wykonwałem po co najmniej 10 razy; wyników nie uśredniałem, ale podane w tabelce nie odbiegają zbytnio od średniej. Środowisko testowe: Athlon 64 X2 4200+ (2 x 2.2GHz), 2 GB RAM 800 MHz, Linux 2.6.22, system plików ext3.