effective finding queries to optimize

let's imagine simple situation – you have postgresql server. configuration was fine-tuned, hardware is ok. yet the system is not really as fast as it should.

most common problem – slow queries.

second most common problem – fast queries, but too many of them. for example – i once saw a system which did something like this:

  • select id from table;
  • for every id do:
  • select * from table where id = ?

reason? very “interesting" orm.

now i'll show you how i deal with these kind of situations 🙂

Continue reading effective finding queries to optimize

Log Buffer #57: a Carnival of the Vanities for DBAs

A couple of days ago I got email from Dave Edwards of Pythian. Dave invited me to be guest editor of upcoming Log Buffer edition.

After some hesitation (just take a look at list of guys which were editors of previous editions, and you'll understand my shyness) I decided to give it a try. so, here it goes – 57th edition, written by depesz 🙂

First of all, we'll start with some posts about my favorite database – PostgreSQL.

Lewis Cunningham on his An Expert's Guide to Oracle Technology blog writes about new product for EnterpriseDB (commercial DB based on PostgreSQL) – GridSQL, which looks like Oracle's RAC. Check also his writeup on second blog: EnterpriseDB News.

What's interesting is that EnterpriseDB and their PR agency got lately quite controversial, which was mentioned by Joshua Drake on his blog (he also links to mailing list archives with discussions about the situation), and by Peter Eisentraut on his personal blog. this entry is very important as it goes into details about EnterpriseDB's “PostgreSQL distribution for Linux".

zillablog – blog by Robert Treat, has very interesting entry about conforming to standards, and using them as a excuse not to add new functionality.

And as a final note from our (PostgreSQL) area – Dave Page wrote new entry on his blog about setting up SSL access on Windows – apparently there were some issues with pre-built binaries of OpenSSL.

Now, let's enter the territory of “the other" opensource database – MySQL:

Biggest news of the week was change in policies and availability of MySQL products – community server and enterprise server. it all started with Kaj Arnö's post on his “MySQL AB VP Community Whereabouts in a Nutshell".

It generated a lot of followups, including Mike Kruckenberg on his blog, Greg's Postgres Stuff by Greg Sabino Mullane, Lukas Kahwe Smith's Poo-Tee-Weet and Peter Zaitsev on Mysql Performance Blog.

Of course Kaj Arnö had to respond, so there went another post on his blog.

Journal of Patrick Galbraith informs us about his new addition to mysqldump – options specifically designed to simplify dumping replicated databases.

Charlie Cahoon's MySQL – Summer of Code tells us how to use global variables in MySQL Proxy. To be honest I haven't heard about it (MySQL Proxy) before, but now, after reading his post I read more about it, and the software looks great. Something like pgpool (for those of you who know PostgreSQL), but with more functionality.

On the the possible functionalities of Mysql Proxy is using this (together with Charlie's code) to transform it into automatic regression tests generator. Details are shown on Giuseppe Maxia's data charmer – it looks really cool.

Johan Andersson and Jimmy Guerrero both tell us about web seminar (and white papers) about MySQL Cluster. Unfortunately if you're in America – it's too late, your webinar was on 8th, but you can still catch webinar directed at EMEA users on 15th.

While we're at learning – Jay Pipes presents materials from his workshop about tuning mysql queries – a must read for every MySQL DBA.

On his diary, Erik Wetterberg, wrote about generating XML content directly from MySQL. Check also comment to this post, as it contains link to valuable resource .

Sergey Zhuravlev on Xaprb wrote about new extension for MySQL replication – slave delay. Interesting idea with some obvious, and some not-so-obvious uses.

At the end of MySQL section, I couldn't help but write about two posts highlighting 2 interesting bugs in MySQL code.

First is Vadim Tkachenko on MySQL performance blog which writes about issues with query cache while dealing with column-based privileges.

The other post highlights what looks to me as quite serious bug. It's Parvesh Garg's post on Optim MySQL, and it's about InnoDB engine repeating (in some situations) auto_increment values.

Oracle is not the database I use, but 2 posts got my attention:

Mark Brunelli in Eye on Oracle: a searchoracle.com blog let us know that white papers for newest Oracle (11g) are available for download.

Second post made me sad that we don't have such a thing in PostgreSQL. It's on Coskan's Approach to Oracle by Coskan Gundogar and tells about permanent (guaranteed) restore points. Great feature.

At the end one more post – not technically database related, but it deals with the human side of databases and (DB) conferences: Mark Atwood's Journal entry about what to do when you're going to speak at a conference.

That would be all in this edition of Log Buffer. I hope you liked my choice of posts, and if not, don't worry, in a week there will be another Log Buffer, this time hosted by Jay Pipes

postgresql tips & tricks

mage_ from #postgresql had interesting problem today.

he has a table with 2 date fields, and he wants to have list of all years from both fields. together. as one list.

his approach:

SELECT date_part('year', date1) FROM test
UNION
SELECT date_part('year', date2) FROM test;

is hardly satisfactory – it takes too long.

any way to speed it up?
Continue reading postgresql tips & tricks

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.

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.

5 rzeczy których nie chcieliście wiedzieć i nigdy o nie nie pytaliście.

co najmniej 3 osoby mnie naznaczyły (elf, ata i tmarc), więc lecimy:

  1. w 4 czy 5 klasie podstawówki przyniosłem do szkoły penthouse'a. koledzy mieli radochę, ja opierdziel.
  2. wyrzucono mnie ze szkoły (liceum) za to że namalowałem grafitti na korytarzu. tyle, że tego nie zrobiłem.
  3. mam kilka świetnych pomysłów na zarabianie kasy. i jedynie nie potrafię się zebrać w sobie i zabrać za to by je zrealizować. i tak od kilku lat. a pomysłów przybywa.
  4. nie mam w ogóle pamięci do twarzy czy nazwisk. jestem w stanie całkowicie bezstresowo nie zauważyć bądź nie poznać na ulicy osoby z którą pracuję od lat. co gorsza – nie robię tego celowo.
  5. nie czytam książek informatycznych. żadnych. niby jako programista/bazodanowiec powinienem, ale nudzą mnie. uważam, że jest z nimi jak ze świnkami morskimi – ani to świnka ani morska.

co do kolejnych ofiar:

  1. ads
  2. arim
  3. illi
  4. jarek zabiełło
  5. smoku

witajcie inne planety

jak może wiecie naukowcy mają technologiczne możliwości by na podstawie obserwowania światła gwiazd powiedzieć sporo o ich składzie.

jak do tej pory – nie działało to dla planet. po prostu dlatego, że “świecą" zbyt słabo.

ostatnio udało się przeprowadzić jednakże analizę światła z dwóch planet i wyciągnąć na tej podstawie wnioski nt. składu chemicznego atmosfery na nich.

wynik mało spektakularny – nie wykryto niczego co mogłoby sugerować życie (a przynajmniej takie życie jakie jesteśmy w stanie rozpoznać), ale istotnym wynikiem jest empiryczny dowód na to, że jesteśmy w stanie “powąchać" atmosferę planet z odległości kilkudziesięciu lat świetlnych.

co to dla nas oznacza? prostsze, szybsze i tańsze sprawdzanie czy daleka planeta “wygląda" na zamieszkałą lub zdatną do zamieszkania. a to jest coś bardzo ważnego. nie dla codziennego zjadacza chleba. ale dla codziennego zjadacza chleba za 50 lat – już tak.

nagroda turinga za rok 2006 – po raz pierwszy dla kobiety

począwszy od 1966 roku, corocznie przyznawana jest nagroda dla osoby lub osób które odznaczyły się wkładem z szeroko pojętą informatykę.

nagroda ta, traktowana jako informatyczny nobel, była wręczana już 40 razy i za każdym razem lauretem byli faceci. znani. świeni fachowcy – z bardziej znanych nazwisk trzeba wymienić knutha, dijkstrę, edgara codda, niklausa wirtha, kena thompsona i dennisa ritchiego, team rsa (rivest, shamir, adleman) czy niedawno panów cerfa i kahna – twórców internetu jako takiego – protokołów i pomysłów.

w 2006 roku po raz pierwszy uhonorowana została kobieta – frances allen. frances pracowała od 1957 roku w ibm'ie i brała udziała w wielu niesamowitych projektach jak choćby pisanie pierwszych kompilatorów (fortran), tworzenie oprogramowania na blue gene'a czy softu wywiadowczego dla nsa.

mimo, że od 2002 roku jest na emeryturze nadal aktywnie się udziela i zachęca młode pokolenia kobiet do wybrania swojej ścieżki kariery.

dla zainteresowanych – nagroda poza wielkim “szacunem" i “joł" ze środowiska obejmuje $100,000.