Month: February 2007

case study: join, subselect, indeksy + pomysł na “aliasy” wyliczanych stałych

kolega pokazał mi pewną sytuację. miał taką tabelę:

                 TABLE "public.staty"
COLUMN  |              Type              | Modifiers
---------+--------------------------------+-----------
DATA    | timestamp(0) without time zone | NOT NULL
size    | integer                        | NOT NULL
proto   | integer                        | NOT NULL
macfrom | integer                        | NOT NULL
macto   | integer                        | NOT NULL
Indexes:
"blah123" PRIMARY KEY, btree (macto, DATA, proto, macfrom)
"dupa" btree (DATA)
"f1" btree (macfrom)
"f2" btree (macto)
"fs" btree (size)
CHECK constraints:
"proto check" CHECK (proto < 65536 AND proto > -1)
Foreign-KEY constraints:
"staty_fk" FOREIGN KEY (macto) REFERENCES macs(id)
"staty_fk1" FOREIGN KEY (macfrom) REFERENCES macs(id)

z 3 milionami rekordów. i na tym zapytanie:

SELECT a.i AS dupa, SUM(COALESCE(b.size, 0)) AS to_, SUM(COALESCE(f.size, 0)) AS FROM
FROM (SELECT
((current_timestamp-'1 month'::INTERVAL) + INTERVAL '1 second'*c.it) AS inter,
i
FROM(
SELECT i, i * ((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)
AS it FROM generate_series(0,599) i
) AS c
) AS a
LEFT JOIN
(SELECT  DATA, SIZE FROM staty WHERE macto='$mac' ) b
ON (
b.DATA > (a.inter-(((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)||' second')::INTERVAL )
AND
b.DATA < a.inter
)
LEFT JOIN
(SELECT  DATA, SIZE FROM staty WHERE macfrom='$mac' ) f
ON(
f.DATA > (a.inter-(((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)||' second')::INTERVAL )
AND
f.DATA < a.inter
)
GROUP BY a.i ORDER BY a.i

iiiś. poza “urodą" zapytania powalał czas: 120-200 sekund!

no muszę przyznać, że miałem bardzo duży problem by zrozumieć.

pierwsza rzecz – stwierdziłem, że przepiszę to zapytanie do postaci czytelnej.

i tu pojawił sie problem – w kodzie wielokrotnie występują pewne stałe wyliczane – now() – ‘1 month' czy to samo dzielone na 600.

jak będę je powtarzał za każdym razem to czytelność szlag trafi. na szczęście przypomniało mi się, że takie rzeczy można łatwo obejść aliasami.

chwila pisania i mamy zapytanie:

SELECT
g.i,
SUM(COALESCE(f.size, 0)) AS from_,
SUM(COALESCE(t.size, 0)) AS to_
FROM
generate_series(0,599) g(i),
staty f,
staty t,
(
SELECT
now() - '1 month'::INTERVAL AS START,
'1 second'::INTERVAL * EXTRACT(epoch FROM (now() - (now() - '1 month'::INTERVAL)))/600 AS div
) x
WHERE
f.macfrom = '$mac'
AND t.macto = '$mac'
AND f.DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
AND t.DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
GROUP BY
g.i

(tu zwracam uwagę na subselect () x – to on robi za aliasy do stałych wyliczanych.

odtrąbiłem sukces. ale przedwcześnie. zapytanie wykonywało się w zasadniczo tym samym czasie = 128 – 160 sekund.

powiedziałem koledze by pozakładał indeksy wielopolowe:

CREATE INDEX some_name ON staty (macfrom, DATA);
CREATE INDEX some_other_name ON staty (macto, DATA);

te dwa indeksy i vacuum później (oj, dużo później) mamy efekt. zerowy. moje zapytanie nadal jest wolne. co ciekawe – oryginalne zapytanie nagle przyspieszyło do około 3.7 sekundy.

zastanowiłem się czemu. i nagle – olśnienie. zapytanie (w całości) operuje na dokładnie wszystkich danych z tabeli – tzn. ten miesiąc dla którego agregujemy dane to więcej niż danych jest w bazie – podobno koło tygodnia.

co oznacza, że musimy wyselectować całość. a że maszyna ma mało ramu, to szybciej sobie radzi z liczeniem popaczkowanym w małe części niż całości na raz.

tak więc kolejny rewrite:

SELECT
g.i,
(
SELECT SUM(SIZE) FROM staty WHERE macfrom = '$mac' AND DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
) AS from_,
(
SELECT SUM(SIZE) FROM staty WHERE macto = '$mac' AND DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
) AS to_
FROM
generate_series(0,599) g(i),
(
SELECT
now() - '1 month'::INTERVAL AS START,
'1 second'::INTERVAL * EXTRACT(epoch FROM (now() - (now() - '1 month'::INTERVAL)))/600 AS div
) x

i mamy czas – 170ms.

refleksje na przyszłość:

  • joiny są szybkie, chyba, że operujemy na dużych zbiorach danych na maszynach z małą ilością ramu
  • subselecty nie zawsze są wolne. zazwyczaj tak. ale w tym przypadku – gdzie tak naprawdę nic nie łączy danych z dwóch kopii tabeli staty – działają super
  • subselect jako metoda aliasowania wartości – czad. upraszcza pisanie, trochę przyspiesza i ułatwia zrozumienie kodu 🙂

alternatywa dla wysokojakościowych radiowych łącz dla telewizji?

po prostu padłem.

zaczęło się od tego, że koleś chciał zestawić sobie tanio łącze radiowe do domu. a że mieszka w nowej zelandii, to musiał mieć spory zasięg. stwierdził, że zrobi to w oparciu o talerz anteny satelitarnej. ale to jest drogie

po kilku testach, dowodach matematycznych itd. udało mu się. zrobił antenę z … woka. takiej jakby patelni. za $10. działało i dawało sygnał na odległość do 20kilometrów!

o sprawie dowiedziała się jakaś mała lokalna stacja telewizyjna. potrzebowali metody przesyłu danych ze studia gdzieśtam (zakładam, że do centrum retransmisyjnego). koszt komercyjnego rozwiązania – $20,000. tyle kasy nie mieli. zestawili więc to łącze na wokach. i działa.

po prostu piękne. geniusz ludzki w najczystszej postaci.

(myśl w tle: heh, przy takiej cenie nie będzie problemu z zestawieniem nawet kilkudziesięciu łącz zapasowych).

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.

narzekanie

znowu się rozchorowałem.

na dodatek muszę pisać dokumentację do bazy. piszę w docbooku bo jest zasadniczo fajny. zasadniczo. ktoś wie jak w nim zrobić tabelkę z nagłówkiem pionowym a nie poziomym? nie chodzi mi o kierunek czcionek. po prostu chcę mieć “header-column" a nie “header-row".

a do tego nie mam na nic czasu. ilość nieprzeczytanych newsów w akregatorze przekroczyła 5000.

no nic. może w weekend się trochę “odkuję". jak tak, to zarzucę was lekko nieświeżymi newsami. ale za to w dużej ilości 🙂

drobny błąd

a dziś napiszę o tym jakim to łosiem można być. niechcący.

na potrzeby jednego z projektów napisałem własnego orm'a (object-relationship mapper). nie znacie? takie cos co pozwala widziec rekordy z tabel jako obiekty. ogólnie – każdy orm jest bez sensu. mój tym bardziej, ale służył do prostego celu – uproszczenia robienia eksportów.

działał.

do czasu.

ostatnio na jednej z maszyn eksporter zaczął zżerać cały ram. calutki. i wywalać maszynę. usiadłem do debugowania. i oto co ujrzałem:

...
sub _table { my $self = shift; RETURN $self->{ 'table_name' } }
sub _db    { my $self = shift; RETURN $self->{ 'db' } }
sub _log   { my $self = shift; RETURN $self->{ 'log' } }
sub _refresh {
my $self   = shift;
my $sql    = sprintf 'SELECT * FROM %s WHERE id = ?', $self->_table;
my $record = $self->_db->get_single_record( $sql, $self->{ 'data' }->{ 'id' } => 'INT8' );
IF ( $record ) {
$self->{ 'data' }    = $record;
$self->{ 'fetched' } = 1;
RETURN;
}
$self->log->critical(
"Cannot refresh record data from table " . $self->_table . " for id = " . $self->{ 'data' }->{ 'id' } );
croak( "DB Error" );
}
sub _get {
my $self = shift;
my ( $field ) = @_;
$self->_refresh unless $self->{ 'fetched' };
RETURN $self->{ 'data' }->{ $field };
}
sub AUTOLOAD {
my $self   = shift;
my $method = $AUTOLOAD;
$method =~ s/.*:://;
RETURN unless $method =~ m{ \A [a-z][a-z0-9_]* \z }xmso;
RETURN $self->_get( $method );
}

zasada działania bardzo prosta – jeśli mam obiekt klasy dziedziczącej z tego orm'a, i wykonam na nim metodę:

$obiekt->jakies_pole

(gdzie jakies_pole nie jest nazwa istniejacej metody), to request trafi do autoloada, który wywoła _get. _get sprawdzi czy obiekt załadował z bazy dane. jak tak – zwróci odpowiednie pole i po sprawie.

a co jeśli nie?

wtedy kod trafia na _refresh(). refresh odczytuje cały rekord z bazy w oparciu o id (które musi być). fajne.

jedno pytanie: co się stanie gdy rekordu w bazie nie będzie?

tradycyjna odpowiedź: kod zaloguje informacje o błędzie i wykona croak(). czyli taki die.

ale nie. niestety. złośliwy los i brak dobrych oczu spowodował, że napisałem $self->log->(), podczas gdy obiekt loggera jest dostępny przed $self->_log.

efekt? metody log nie ma. trafiamy na autoloada. autoload odsyła do _get'a, _get do _refresha. _refresh znowu nie znajduje rekordu, więc … i kółeczko się zamyka.

nieskończona rekurencja, 3 giga zużytej pamięci, kilka godzin pracy paru osób. przez brak jednego “_".

a czemu o tym piszę? abym pamiętał. i sprawdzał kod. i bym miał okazję się pochwalić jakie “fajne" błędy potrafię wygenerować, a potem wykryć. i pochwalić “perl -d" – debugger jest wkurzający, mało sympatyczny. i ratuje d… jak oczy zawiodą.

aha. i jak fajnie wygląda “T" pod debuggerem po np. 50 przebiegach tej rekurencji 🙂 (T == stack trace).