Jun 27 2006
MySQL i PostgreSQL – testy wydajności
Filed under DB by Łukasz Dywicki
Witam, jakiś czas temu w pracy staneliśmy przed dylematem – którą bazę danych użyć? Dyskusja była długa, w końcu postanowiliśmy przetestować wydajność każdego rozwiązania. Niestety testy niejasności tylko jeszcze bardziej naświetliły.
Pierwszy test, jaki przeprowadziliśmy polegał na wstawieniu 3 milionów rekordów do tabeli, która składała się z 5 kolumn.
-- pgsql CREATE TABLE users ( user_id serial NOT NULL, user_name varchar(45), user_password varchar(32), user_first_name varchar(15), user_last_name varchar(15), CONSTRAINT users_pkey PRIMARY KEY (user_id) )
Identyfikator był numerem iteracji, nie wykorzystałem ani autoincrement w MySQL ani sekwencji w PgSQL. Niestety, troszkę się zagapiłem i nie mam czasów dla MySQL do 1.5 mln wstawień i dla PgSQL do 150 tys. Obrazek prezentujący wyniki po lewej. Czerwony – PgSQL, niebieski MySQL. Na dole powinna być ilość rekordów, które są już w bazie, ale ze względu, że zaczęły one na siebie zachodzić powstawiałem zera. W każdym razie im bliżej prawej tym mniej brakuje do 3 mln. Duże skoki w wykonywaniu insertów były spowodowane wykonaniem zapytania z SELECT COUNT(user_id) FROM users.
Jak widać MySQL wyraźnie wygrywa w tym teście z PgSQL. Średnio czas, który MySQL potrzebuje na wstawienie 10 tys rekordów to 12.2204 s. Gdybym dysponował czasami sprzed 1.5 mln rekordów czas ten mógłby ulce zmniejszeniu. PostgreSQL na wstawienie 10 tys rekordów potrzebuje średnio 14.7962 s.
To nie był koniec testów. Ciekawie zaczęło się robić, gdy trzeba było pobrać dane z tej tabeli. Oczywiście do tego celu napisałem algorytm, który losowo odpytuje tabelę z różnym limitem i offsetem oraz różnie sortuje wyniki. Ciekawi co się stało? Zarówno MySQL jak i PgSQL nie poradziły sobie z takim obciążeniem, Pg nie starczało 400 M wolnego miejsca na dysku, w przypadku MySQL nie było o czym gadać, serwer nawet nie zwrócił informacji o niepowodzeniu czy czymkolwiek tylko stanął. Tzn. pracował w dalszym ciągu stabilnie, ale zapytanie po prostu jakby pominął, nic nie zwrócił i dalej nic nie robił.
Jedynym lekarstwem na ten problem okazały się indeksy. Postanowiliśmy, że na początku zindeksujemy wszystkie kolumny (test bez indeksów w przygotowaniu). O ile dodanie indeksów w Pg to nie był problem, wystarczyło mu jakieś 40-60 minut na wszystkie kolumny o tyle w przypadku My problem już powstał. Serwer stworzył tabelę tymczasową i przez 2h nie dodał żadnego indeksu. Jedynym wyjściem było oczyszczenie tabeli. Niestety serwer po wycofaniu się z indeksów przestał odpowiadać. Po restarcie najpierw spróbowałem DELETE FROM users. Nie był to najlepszy pomysł. Restart. DELETE FROM users LIMIT 10000. Restart. DELETE FROM users LIMIT 1000. Restart. DROP TABLE users. Reset. Żadna z tych operacji nie dała skutku. Dopiero po kolejnym resecie udało mi się usunąć tabelę. Tabelę stworzyłem na nowo, dodałem indeksy i zacząłem dodawać dane na nowo. Tym razem czasy zmierzyłem od początku.
Wynik? Straszny. Czasy zaczęły lecieć na łeb, na szyję. O ile z początku jakoś to się trzymało, to później został przekroczony próg 30 s na 10 tys rekordów, następnie 50 aż doszliśmy do 85 s. Przy 1.5 mln rekordów przerwałem test.
Oprócz testów baz danych przetestowałem również na o wiele mniejszej tabeli (5 kolumn, 10 wierszy, pobranie wszystkich z losowym sortowaniem, 10 tys iteracji) AdoDB i Creole. AdoDB korzystało z PDO, Creole z natywnych funkcji. Nie bierzcie tego testu na poważnie.. Ciąg dalszy testów być może nastąpi. :) Dziękuję firmie Autoguard za pozwolenie na publikację testów.
A jakie wersje oprogramowania? Jaki system operacyjny? Jakie warunki sprzętowe?
A mógłbyś opisać te testy? Jakaś legenda na obrazkach czy coś
Dołączam sie do pytania o wersje, system i sprzet :D
Serwer stoi na Debianie, sprzęt – dual P3 1GHz, pamięć 1GB, dysk 40 GB
jacus wkoncu wstales po tym browarku ;)
Moje kilka uwag.
Spoko a w zapytaniu to serial co robi? Pozuje?
Nie napisales jaka wersja PG jak i MySQLa, czy dla MySQLa to bylo InnoDB bo jesli tak to mysle ze cos nie tak z PG jest czasy powinny byc bardziej zblizone. Kolejna rzecza indeksy na karzda kolumne to bardzo, bardzo zly pomysl.
Proponuje zrobic test na InnoDB przy wprowadzaniu z relacjami, jak i usuwniu i update.
MySQL 5.1.9-beta, tabelka InnoDB.
PgSQL 8.1.4.
Sekwencja jest zdefiniowana ale kolumna to dalej typ INT, to do niczego nie zobowiązuje, podobnie jak danie AUTO INCREMENT dla MySQL. Są one wykorzystywane jeśli nie zostanie przekazana wartość dla kolumny. W tym przypadku identyfikator zawsze był określany z góry a nie przez bazę.
Pisałem:
Dobrze wiem, że nie jest to bardzo zły pomysł. Klucze zostały założone ponieważ nie można było odpytać ani MySQL ani PgSQL o dane z tej tabeli. Bardzo mi zależy by sprawdzić jak zachowają się bazy przy takich ilościach danych.
Tak jak pisałem, dalsze testy są przygotowywane.
hm, 3mln rekordow bez indeksow?? po co testowac srodowisko, ktore nigdy nie zaistnieje w praktyce (nie zaistnieje, prawda??)?
na szybko:
mysql 3.x plus moocno zakurzony serwer (ale pod reka):
CREATE TABLE `users_en` (
`u_id` int(5) NOT NULL auto_increment,
`u_accepted` tinyint(1) NOT NULL default ‘0’,
`u_forename` varchar(150) NOT NULL default ”,
`u_surname` varchar(150) NOT NULL default ”,
`u_company` varchar(255) NOT NULL default ”,
`u_position` varchar(255) NOT NULL default ”,
`u_email` varchar(255) NOT NULL default ”,
`u_tel` varchar(255) NOT NULL default ”,
`u_pass` varchar(32) NOT NULL default ”,
`u_address` text NOT NULL,
`u_city` varchar(255) NOT NULL default ”,
`u_postcode` varchar(30) NOT NULL default ”,
`u_country` varchar(255) NOT NULL default ”,
PRIMARY KEY (`u_id`),
KEY `u_accepted` (`u_accepted`),
KEY `u_email` (`u_email`)
) TYPE=MyISAM AUTO_INCREMENT=1398543 ;
zapytanie – {$i} = koleny numer:
insert delayed into users set u_forename='{$i}’, u_surname='{$i}’, u_company=’245′, u_position=’45454′, u_email=’4343545655′;
dla 10k rekordow:
~1s
dla 100k rekordow:
~11s
dla 1mln rekordow:
~106s
dla 3mln juz mi sie nie chce…pozno.
prosze, nie robcie testow wydumanych srodowisk…brak indeksow i konkluzja, ze bazy nie wyrabiaja, selekty w trakcie trwania petli dodajacej inserty.
To środowisko może zaistnieć i to z niewiele mniejszą ilością insertów. Indeksy nie były ponieważ nie zawsze jest sens ich stosowania (dużo wstawień, mało pobrań). Zwróć uwagę na to, że stosowałeś MyISAM a nie InnoDB.
oczywiscie, typ tabeli ma tu znaczenie.
jednak proba stosowania selecta na duza tabele bez indeksow nie ma sensu.
SELECT na takiej tabeli nawet bez indeksów ma szanse zadziałać, problem zaczyna się gdy w grę wchodzi sortowanie bądź wyszukiwanie. Z punktu widzenia bazy select wyciąga tylko dane. Wierz mi, że na Postgresie na tej 3 milionowej tabeli select z limitem i offsetem przechodził bez problemu nawet przed wykonaniem VACUUM