how to insert data to database – as fast as possible

some time ago i wrote a piece on values(),() in postgresql 8.2, saying that multi-row inserts are very fast.

some people said that my benchmark is pretty useless as i didn't take into consideration transactions.

others asked me to translate the text to english.

so i decided to redo the test, with more test scenarios, and write it up in english. this is the summary.

at first what i used, what i tested and how.

i used a linux machine, with these things inside:

  • cpu: AMD Sempron(tm) Processor 2600+ (1.6ghz)
  • memory: 3gb
  • discs: 4 250gb hitachi sata discs (only one was used)

i tried to make the machine as predictable as possible, thus i stopped all daemons which were not neccessary. full ps auxwwf output is provided in results tar file. basically – there is postgresql, sshd, openvpn, dhclient and some gettys. no cron, atd, smtpd, httpd or anything like this.

then i wrote a small program which generated test files. i do not distribute test files themselves, as in total they use nearly 70gb!

then i wrote another small program – which basically ran all of the tests (3 times to get an average).

full set of results is downloadable as tar file, which contains 10598 files (tar file is 350k, unpacked directory takes 42megs).

one very important notice. all tests that i have performed inserted random data to table of this structure:

  • id int4 primary key,
  • some_text text,
  • bool_1 bool,
  • bool_2 bool,
  • some_date date,
  • some_timestamp timestamp

so results (especially “break-points" where there is no further gain) will be different when inserting to another tables. the only point of this benchmark is to show which approach can give which results. and what's really worth the trouble 🙂

Continue reading how to insert data to database – as fast as possible

looking for hosting

i'm looking for hosting provider. what i need is basically virtual server, on separate ip (i'd like to put there some non-web servers, like smtpd, and doing so without separate ip can/will be tricky).

other needs – up to 50g of disc space, bandwidth of up to 1mbit upload.

i will need to have root on the machine to be able to install new software, and run services on low-ports (ssh, smtpd, web) and some high-ports (not known at the moment).

can you suggest any company that will let me to setup such a environment? at low price, but not at the cost of service uptime.

drzewa w sql’u – ltree

uwaga – ta metoda jest tylko i wyłącznie dla postgresql'a, gdyż wykorzystuje niestandarodwy typ danych obecny (jako moduł w contribie) jedynie w postgresie.

jak ltree działa nie będę opisywał bo od tego jest manual do ltree.

baza do ltree jest trywialna, przykładowo, oryginalne, testowe drzewo:

zapisujemy tak:

# create table tree_ltree (
id int4 primary key,
path ltree
);

po wstawieniu naszego testowego drzewa uzyskujemy taką zawartość tabelki:

id path
1 sql
2 sql.postgresql
3 sql.oracle
4 sql.postgresql.linux
5 sql.oracle.solaris
6 sql.oracle.linux
7 sql.oracle.windows
8 sql.oracle.linux.glibc1
9 sql.oracle.linux.glibc2

ok. jak się pyta taką bazę?

1. pobranie listy elementów głównych (top-levelowych)

select * from tree_ltree where path ~ '*{1}'

2. pobranie elementu bezpośrednio “nad" podanym elementem:

dane wejściowe:

  • ID : id elementu
select  p.* from tree_ltree c join tree_ltree p on c.path <@ p.path where c.id = [ID] and c.path ~ cast(p.path::text || '.*{1}' as lquery)

zwracam uwagę, na to iż mając daną ścieżkę do elementu można mu po prostu wyciąć ostatni element (od kropki do końca) i w ten sposób uzyskać od razu ścieżkę do elementu nadrzędnego.

3. pobranie listy elementów bezpośrednio “pod" podanym elementem

dane wejściowe:

  • ID : id elementu
select c.* from tree_ltree c join tree_ltree p on c.path <@ p.path where p.id = [ID] and c.path ~ cast(p.path::text || '.*{1}' as lquery);

zwracam uwagę, na to iż mając daną ścieżkę do elementu można mu po prostu dokleić do niej .*{1} i wykonać zapytanie:

select * from tree_ltree where path ~ [ZMODYFIKOWANA_SCIEZKA_PARENTA]

4. pobranie listy wszystkich elementów “nad" danym elementem (wylosowanym)

dane wejściowe:

  • ID : id elementu
select  p.* from tree_ltree c join tree_ltree p on c.path <@ p.path where c.id = [ID] AND p.id <> [ID]

5. pobranie listy wszystkich elementów “pod" danym elementem (wylosowanym)

dane wejściowe:

  • ID : id elementu
select c.* from tree_ltree c join tree_ltree p on c.path <@ p.path where p.id = [ID] AND c.id <> [ID]

6. sprawdzenie czy dany element jest “liściem" (czy ma pod-elementy)

dane wejściowe:

  • ID : id elementu
select count(*) from tree_ltree c join tree_ltree p on c.path <@ p.path where p.id = [ID] AND c.id <> [ID]

jeśli zwróci 0 – to jest to liść. w innym przypadku zwróci ilość bezpośrednich “dzieci".

7. pobranie głównego elementu w tej gałęzi drzewa w której znajduje się dany (wylosowany) element

  • ID : id elementu
select  p.* from tree_ltree c join tree_ltree p on c.path <@ p.path where c.id = [ID] and p.path ~ '*{1}'

jeśli chodzi o zalety – najważniejszą jest szybkość pisania, intuicyjność zapytań, możliwości (indeksowane wyszukiwanie np. elementów 2 poziomy poniżej dowolnego elementu którego nazwa zaczyna się od “dep") i czytelność danych.

wada jest zasadniczo tylko jedna – przenośność. jeśli kiedykolwiek w przyszłości będziecie przenosić bazę na coś innego niż postgres, to macie problem. no tak. tylko po co przenosić bazę na coś innego niż postgres?

drzewa w sql’u – metoda pełnych ścieżek (metoda nr. 5 w/g starego tekstu)

oj. od ostatniego tekstu nt. drzew już trochę czasu minęło. czas więc dokończyć tę serię 🙂

metodę tę wymyśliliśmy ze znajomymi z firmy w której kiedyś pracowałem.
jest mocno prawdopodobne, że ktoś jeszcze wpadł na taki pomysł, natomiast wiem, że gdy ją wymyślaliśmy – nie korzystaliśmy z niczyich prac.

na czym ona polega? w skrócie na tym, że baza zna wszystkie powiązania między wszystkimi elementami drzewa które są ze sobą powiązaną na zasadzie “ojciec, ojciec ojca, ojciec ojca ojca, …".

w tym celu używamy 2 tabel – pierwszej aby przechowywać informacje o elementach drzewa, a w drugiej – o powiązaniach między nimi.

przykładowo, oryginalne, testowe drzewo:

tabelki:

# create table nodes_5 (
id int4 primary key,
name text
);
# create table tree_5 (
id int4 primary key,
parent_id int4 not null references nodes_5(id),
child_id int4 not null references nodes_5(id),
depth int4
);

wstawianie elementów polega na tym, że wstawiamy rekord do nodes_5, po czym dodajemy do tree_5 rekord którego child_id będzie takie jak aktualnego elementu, depth będzie równy 1, a parent_id będzie wskazywał na element nadrzędny.

następnie w tree_5 należy wstawić rekord którego depth będzie 0, a parent_id i child_id będą ustawione na id nowo wstawionego elementu.

na koniec należy skopiować wszystkie elementy gdzie child_id było takie jak aktualnie parent_id, podbijając depth o 1 i zmieniając child_id na id wstawionego elementu.

uff. skomplikowane?

tak się tylko wydaje. tabelki po wstawieniu danych wyglądają tak – prześledźcie je to zobaczycie, że sprawa jest prosta.

tabelka nodes_5:

id name
1 sql
2 postgresql
3 oracle
4 linux
5 solaris
6 linux
7 windows
8 glibc1
9 glibc2

tabelka tree_5:
# select * from tree_5;

id parent_id child_id depth
1 1 1 0
2 1 2 1
3 2 2 0
4 1 3 1
5 3 3 0
6 2 4 1
7 4 4 0
8 1 4 2
9 3 5 1
10 5 5 0
11 1 5 2
12 3 6 1
13 6 6 0
14 1 6 2
15 3 7 1
16 7 7 0
17 1 7 2
18 6 8 1
19 8 8 0
20 3 8 2
21 1 8 3
22 6 9 1
23 9 9 0
24 3 9 2
25 1 9 3

mam nadzieję, że teraz wygląda to bardziej oczywiście 🙂

ok. jak się pyta taką bazę?

1. pobranie listy elementów głównych (top-levelowych)

SELECT n.* from nodes_5 n left outer join tree_5 t on (n.id = t.child_id and t.depth = 1) where t.id is null

2. pobranie elementu bezpośrednio “nad" podanym elementem:

dane wejściowe:

  • ID : id elementu
select n.* from nodes_5 n join tree_5 t on n.id = t.parent_id where t.depth = 1 and t.child_id = [ID];

jeśli zapytanie nic nie zwróci – znaczy to, że dany element był “top-levelowy".

3. pobranie listy elementów bezpośrednio “pod" podanym elementem

dane wejściowe:

  • ID : id elementu
select n.* from nodes_5 n join tree_5 t on n.id = t.child_id where t.depth = 1 and t.parent_id = [ID];

4. pobranie listy wszystkich elementów “nad" danym elementem (wylosowanym)

dane wejściowe:

  • ID : id elementu
select n.* from nodes_5 n join tree_5 t on n.id = t.parent_id where t.child_id = [ID];

5. pobranie listy wszystkich elementów “pod" danym elementem (wylosowanym)

dane wejściowe:

  • ID : id elementu
select n.* from nodes_5 n join tree_5 t on n.id = t.child_id where t.parent_id = [ID];

6. sprawdzenie czy dany element jest “liściem" (czy ma pod-elementy)

dane wejściowe:

  • ID : id elementu
select count(*) from tree_5 where depth = 1 and parent_id = [ID]

jeśli zwróci 0 – to jest to liść. w innym przypadku zwróci ilość bezpośrednich “dzieci".

7. pobranie głównego elementu w tej gałęzi drzewa w której znajduje się dany (wylosowany) element

  • ID : id elementu
select n.* from nodes_5 n join tree_5 t on n.id = t.parent_id where t.child_id = [ID] order by depth desc limit 1;

podstawową zaletą tego rozwiązania jest to, że praktycznie wszystkie operacje można uzyskać prostym “where" po jednej tabeli (tree_5) – join do nodes_5 służy tylko do tego by móc zwrócić coś poza samym id.

wady – nieoczywiste przenoszenie elementów. pewna nadmiarowość informacji.

co do przenoszenia elementów – kiedyś o tym pisałem, ale aby było wszystko w jednym miejscu:

zakładając, że mamy drzewo i w nim element o id X przenosimy tak aby był bezpośrednio pod Y, zapytania które to zrealizują wyglądają tak:

DELETE FROM tree_5 WHERE id in (
                SELECT r2.id FROM tree_5 r1 join tree_5 r2 on r1.child_id = r2.child_id
                WHERE r1.parent_id = X AND r2.depth > r1.depth
                );
INSERT INTO tree_5 (parent_id, child_id, depth)
        SELECT r1.parent_id, r2.child_id, r1.depth + r2.depth + 1
        FROM
                tree_5 r1,
                tree_5 r2
        WHERE
                r1.child_id = Y AND
                r2.parent_id = X;

aha. oczywiście trzeba sprawdzić czy przenosiny elementu nie wygenerują pętli.

random text record identifiers

polish disclaimer begin;

w celu trenowania języka, oraz by poszerzyć teoretyczny zasięg bloga będę teraz starał się pisać po anglijsku. wytykanie błędów mile widziane.

polish disclaimer commit;

ok, so you're trying to build something that needs random-text record identifiers. perhaps a new tinyurl-kind-of-service.

and you are thinking about a way to implement random text generation in a way that:

  • there will be no direct information which record was added just after given one (knowing it's textual id). i mean – the text id's cannot be sequential like a, b, c, d, …
  • the code should be as small as possible. we do not want to start with 40-characters behemoths just to make sure no-one can know which ones were before another.
  • it should be as simple as possible.

requirements 1 and 2 are almost contrary, but we can manage.

first – let's assume we will generate random id's out of these characters: a-z, A-Z, 0-9. this gives us 62 different characters.

now. let's do it that way:

  1. assume current_length to be 1
  2. generate random string of length = current_length
  3. if this id is already taken, increment current_length and repear from step 2
  4. voila. new id generated.

it matches both first and second requirement from list.

as for simplicity.

let's try to implement:

first, let's create a test table with unique constraint on text-id field (i keep numerical id “just in case"):

CREATE TABLE test_table (
id          BIGSERIAL,
random_code TEXT  NOT NULL DEFAULT '',
something   TEXT ,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_test_table_random_code ON test_table (random_code);

now, let's create function for random string generation:

CREATE OR REPLACE FUNCTION get_random_string(string_length INT4)
RETURNS TEXT
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
possible_chars TEXT = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
output TEXT = '';
i INT4;
pos INT4;
BEGIN
FOR i IN 1..string_length LOOP
pos := 1 + cast( random() * ( length(possible_chars) - 1) as INT4 );
output := output || substr(possible_chars, pos, 1);
END LOOP;
RETURN output;
END;
$BODY$;

code looks simply i guess. in case it doesn't – ask question in comments area.

now. the master-code, which is a trigger:

CREATE OR REPLACE FUNCTION trg_test_table_get_random_code()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
temprec RECORD;
new_string TEXT;
BEGIN
LOOP
new_string := get_random_string(string_length);
SELECT count(*) INTO temprec FROM test_table WHERE random_code = new_string;
IF temprec.count = 0 THEN
NEW.random_code := new_string;
EXIT;
END IF;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
RETURN NEW;
END;
$BODY$;
CREATE TRIGGER trg_test_table_get_random_code BEFORE INSERT ON test_table FOR EACH ROW EXECUTE PROCEDURE trg_test_table_get_random_code();

basically this trigger is implementation of algorithm i wrote couple of lines above.

is it done? basically yes.

after 10 inserts i got this content of table:

# select * from test_table;
id | random_code | something
----+-------------+-----------
1 | x           | x
2 | B           | x
3 | w           | x
4 | U           | x
5 | b           | x
6 | OE          | x
7 | N           | x
8 | zn          | x
9 | Y           | x
10 | JY          | x
(10 rows)

if you know your database-things you will see that the code has one serious (or not serious, depending on your view) problem.

if two inserts will happen at the same time, it is possible that one of them will raise exception of unique violation.

this is because there is a race condition between select count(*) and actual insert which takes place only after trigger finishes.

is there no hope? there is. but we have to modify the way we insert data to test table.

until now, i was able to simply: insert into test_table (something) values (‘x'); and it called my trigger code which set the random_code to whatever ‘s appropriate.

but if i want this to be a more fault-proof, i need to throw away the trigger, and force client to do inserts using select's.

like this:

CREATE OR REPLACE FUNCTION smart_insert(IN in_something TEXT, OUT new_key TEXT)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
BEGIN
LOOP
new_key := get_random_string(string_length);
BEGIN
INSERT INTO test_table(something, random_code) VALUES (in_something, new_key);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
END;
$BODY$;

and now, i can:

select smart_insert('x');

done.

or is it?

what if you'd like to be able to generate these text keys in more than one table? (for simplicity sake let's assume all of them have the same fields).

in such a case we would modify the function to be:

CREATE OR REPLACE FUNCTION smart_insert(IN table_name TEXT, IN in_something TEXT, OUT new_key TEXT)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
use_sql TEXT;
BEGIN
LOOP
new_key := get_random_string(string_length);
BEGIN
use_sql := 'INSERT INTO ' || quote_ident( table_name ) || ' (something, random_code) VALUES (' || quote_literal(in_something) || ', ' || quote_literal(new_key) || ')';
EXECUTE use_sql;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
END;
$BODY$;

and then i could do:

select smart_insert('test_table', 'xxx');

print “read manual” while each %bug;

taak.

trafił mnie dziś bardzo “fajny" błąd.

w sofcie który pisałem, piszę i się zajmuję mam taki kawałek kodu:

for my $object ( @objects ) {
next unless $self->validate_object( $object );
$self->save_object_to_database( $object );
}

kod ten pobiera z podanej listy obiekty (tak naprawdę to nie obiekty tylko struktury (hasze haszy). potem waliduje zawartość i jeśli walidacja się udała – wpisuje do bazy.

trywiał.

jednym z elementów walidacji jest podmiana pewnych wartości na wartości słownikowe.

powiedzmy, że mamy w “obiekcie" element “region" i ma on wartość “WARSZAWA". w odpowiednim słowniku sprawdzam czy wartość WARSZAWA jest dopuszczalna. jak nie – walidacja się nie udała. jak tak, zamiast stringu “WARSZAWA" wstawiam numeryczny identyfikator z bazy. np. 15.

kod który to robi:

sub validate_object {
my $self = shift;
my $object = shift;
while (my ($param, $dictionary) = each %{ $self->validation_rules }) {
next unless defined $object->{ $param };
my $object_value = $object->{ $param };
if ( $self->dictionaries->{ $dictionary }->{ $object_value } ) {
$object->{ $param } = $self->dictionaries->{ $dictionary }->{ $object_value };
next;
}
$self->log("error at validation ...");
return;
}
return 1;
}

może nie jest to najbardziej czytelne, ale po kolei:

hash $self->validation_rules ma pary klucz/wartość, gdzie klucz jest nazwą klucza (elementu) z obiektu, a wartość jest nazwą słownika którym mamy dany element walidować.

przykładowo hash ten może zawierać:

"region" => 'REGION_LIST'

reguł jest standardowo około 10.

słowniki są zwracane z metody $self->dictionaries(). zwracana struktura to hashref, mający jako klucz nazwę słownika, a jako wartość – hashref z parami – tekstowa wartość => numeryczny identyfikator.

przykładowo:

{
'REGION_LIST' => { 'WARSZAWA' => 1, 'KRAKÓW' => 2, ...},
'CATEGORIES_LIST' => { 'MOTO' => 15, 'AGD' => 21, ... },
...
}

proste.

czy widzicie błąd w kodzie funkcji validate_object() ?

nie?

podpowiem. objaw który do mnie trafił, to to, że metoda save_object_to_database() zwracała bład sql'a, który mówił, że wartość ‘WARSZAWA' nie jest prawidłowa dla pola numerycznego.

nadal nie wiecie?

otóż metoda each(). zapamiętuje ona w haszu ostatnio zwrócony element. tak aby przy następnym wywołaniu zwrócić kolejny.

co się więc stanie gdy któryś z obiektów sie nie zwaliduje?

załóżmy, że mamy 10 reguł. od 1 do 10. przy obiekcie “a" zwalidowały się reguły 1, 2, 3, 4, a przy regule 5 pojawił się błąd. został zalogowany ($self->log), metoda validate_object się skończyła pustym returnem. więc w głównej metodzie został pobrany kolejny obiekt – “b".

przy walidowaniu obiektu “b", wywołujemy each(), który zwraca którą regułę? 6! potem 7, 8, 9, 10 i na tym skończy. czyli reguły 1-5 w ogóle nie są sprawdzone. i nawet jeśli obiekt jest poprawny – wartości odpowiednich pól nie zostają zamienione na id'y. i stąd błąd przy insercie.

czemu o tym piszę?

dwa powody.

po pierwsze: może się to komuś przyda.

po drugie: może to spowoduje, że o tym nie zapomnę. i następnym razem zamiast bawić się each() użyję po prostu keys.

tsearch – instalacja, testy, rozszerzanie

jak część z was wie byłem ostatnio na pgcon'ie.

tu od razu informacyjnie – byłem dzięki mojemu pracodawcy – firmie eo networks, któremu niniejszym publicznie bardzo dziękuję za umożliwienie mi wzięcia udziału w tej imprezie – jeśli szukacie pracy i znacie się na javie, bazach danych (głównie postgres, ale slyszałem też o jakichś projektach na innych bazach) – warto się odezwać.

wracając do meritum.

byłem tam na prezentacji olega bartunova nt. nowego tsearcha. nowego – nie znaczy, że będzie tsearch3. nowego – czyli tsearch2 zintegrowanego z samym postgresem.

jak się uda wszystko co zaplanowali to będzie tak w 8.3, ale jak się nie uda – no cóż. zobaczymy.

na prezentacji podpatrzyłem jedną rzecz którą wam tu teraz pokażę: wyszukiwanie pełnotekstowe z “poprawianiem" literówek (fuzzy-full text search).

Continue reading tsearch – instalacja, testy, rozszerzanie

parsowanie rss’ów

prace nad dnews postępują powoli. bardzo powoli.

pisałem dziś kawałek kodu do czytania rss'ów/atom'ów/rdf'ów.

prosty test na rss'ach planety ubuntu i wynik:

undefined entity at line 527, column 19, byte 70093 at /usr/lib/perl5/XML/Parser.pm line 187

wrrrrrr.

a co jest w tej linii?

=> head -n 527 planet.ubuntulinux.org.xml | tail -n 1
<title>Sebastian K&uuml;gler: We are out of real life</title>

jeśli nie zgadłeś – problemem jest ten &uuml;.

co z tym zrobić? popytałem na ircu, poszukałem na googlu. w końcu trafiłem na stronę która mówi o przyczynach i jak zapobiec.

w związku z tym musiałem zastosować taki kawałek hacka:

$content =~ s{
\A
(
\s*
<\?.*\?>
\s*
)
<
([A-Z0-9:_-]+)
}{$1 . get_doctype($2) . "<" . $2}eixms;

gdzie funkcja get_doctype wygląda:

sub get_doctype {
my $tag = shift;
return <<__DOCTYPE__;
<!DOCTYPE $tag [
<!ENTITY % HTMLlat1 PUBLIC "-//W3C//ENTITIES Latin 1 for XHTML//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml-lat1.ent"> %HTMLlat1;
<!ENTITY % HTMLspecial PUBLIC "-//W3C//ENTITIES Special for XHTML//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml-special.ent"> %HTMLspecial;
<!ENTITY % HTMLsymbol PUBLIC "-//W3C//ENTITIES Symbols for XHTML//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml-symbol.ent"> %HTMLsymbol;
]>
__DOCTYPE__
}

co to powoduje?

wstawia przed pierwszy tag w xml'u deklarację używania entity z html'a.

po tym – xml::feed działa mi już poprawnie nie marudząc o nic 🙂

wniosek? część (zgaduję, że większość) feedów rdf/rss/atom jest zwalona jeśli chodzi o poprawność xml'a.

dnews

projekt dnews doczekał się wznowienia.

na razie jest site z layoutem który nic nie robi (i nie ma wszystkich elementów), ale będę po kolei dokładał kolejne cegiełki funkcjonalnościowe.

jeśli macie jakieś sugestie/pomysły/idee – proszę o komentarze do tego wpisu.

—- UPDATE z 2007.04.25 —-

projekt (a dokładniej to co w nim jest, czyli na razie niewiele) można ściągnąć:

svn co http://svn.depesz.com/svn/dNews/trunk/ dNews

dostęp anonimowy jest read only.