Newsgroup: it.comp.software.database
Titolo: Partizionamento reale VS partizionamento fittizio

Ok, visto che non avevo una beneamata da fare, ho provato ad eseguire il
partizionamento logico. Gli strumenti (hardware e software) per provare
sono stati i seguenti:
Hardware:
- Pc desktop Intel Core2 Quad Q6600 con 2GB di RAM;
- Pc desktop AMD Athlon64 3200+ con 1.5GB di RAM
Software:
- Linux Debian a 64bit su tutti e due i pc;
- Firebird 2.0.3 sul desktop Intel;
- PostgreSQL 8.3.1 sul desktop AMD;
- Python 2.5.2 per la generazione dei dati.
La struttura del database e` stata utilizzando dieci tabelle della
aventi ciascuna quattro colonne (una sequenza, due colonne che riportano
la data e l'ora, na colonna di testo). Ogni tabella ha un indice sulla
colonna della data. La vista di raggruppamento e` stata eseguita
utilizzando la clausola UNION nella query. Per motivi pratici il
partizionamento dei dati e` stato fatto lato applicativo, e non lato
database.
I criteri per la generazione dei dati sono stati i seguenti: inserimento
di un record per ogni ora che intercorre dall'anno 1600 ad oggi. I
dati sono stati partizionati inserendo 40 anni in ogni tabella, fino al
riempimento di tutte le tabelle. C'e` comunque da fare una precisazione:
per motivi a me ignoti, il codice generato inserisce fino al 25/09/1999,
problema comunque secondario visto che comunque vengono inseriti un
numero significativo di dati (3.504.000 record, secondo la sequenza).
L'esecuzione di query, su PostgreSQL, ha i seguenti risultati:
- Eseguendo una query in cui si vuole estrarre la data massima
  inserita, il tempo di risposta e` di 13512 millisecondi.
- Eseguendo una query in cui si definisce un range di 100 anni, il
  tempo di risposta e` di 79877 millisecondi.
- Eseguendo una query in cui si prende in considerazione sempre un
  range di 100 anni, ma in cui vengono coinvolte almeno due tabelle, il
  tempo di risposta e` di 78830 millisecondi.
L'esecuzione di query, su Firebird, ha i seguenti risultati:
- Eseguendo una query in cui si vuole estrarre la data massima
  inserita, il tempo di risposta e` sui 3:09 minuti.
- Eseguendo una query in cui si definisce un range di 100 anni, il
  tempo di risposta e` di 54 secondi.
- Eseguendo una query in cui si prende in considerazione sempre un
  range di 100 anni, ma in cui vengono coinvolte almeno due tabelle, il
  tempo di risposta e` di 1:12 minuti.
Posto che attualmente il ciclo di sviluppo di Firebird ancora non
predilige la velocita` di risposta, ma l'affidabilita` e la
scalabilita`, e` comunque da notare il comportamento tenuto dagli
optimizer dei due RDBMS: nell'esecuzione della query Firebird ha un
comportamento uguale per tutte le tabelle coinvolte nella JOIN.
PostgreSQL, invece, nella configurazione predefinita utilizza gli
indici solo quando lo ritiene opportuno. Purtroppo, per motivi pratici,
tecnici e motivazionali (non ho voglia di impelagarmi nell'installazione
di Oracle sul mio pc, e utilizzare Oracle XE su di una macchina
virtuale falserebbe le prove), non ho modo di provare sia questo
approccio che l'utilizzo di funzioni native per la gestione di tabelle
partizionate. Se qualcuno vuole provare, riporto sia il codice python da
me utilizzato per generare i dati, sia il codice per creare la struttura
del database. Ovviamente, il tutto potrebbe essere scritto meglio, ma
visto quanto ci ho perso a fare queste prove (due mezze serate), direi
che e` scritto anche troppo bene :)
Enrico
####################
-- Struttura SQL del database
-- CREATE GENERATOR di fatto crea una sequenza (su Firebird per motivi
-- storici hanno un nome differente)
CREATE GENERATOR TEST_SEQ;
CREATE TABLE TEST1
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST10
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST2
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST3
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST4
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST5
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST6
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST7
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST8
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE TABLE TEST9
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30)
);
CREATE VIEW TEST (INS, DATE_INS, HOUR_INS, TXT)
AS   
select ins, date_ins, hour_ins, txt from TEST1
union
select ins, date_ins, hour_ins, txt from TEST2
union
select ins, date_ins, hour_ins, txt from TEST3
union
select ins, date_ins, hour_ins, txt from TEST4
union
select ins, date_ins, hour_ins, txt from TEST5
union
select ins, date_ins, hour_ins, txt from TEST6
union
select ins, date_ins, hour_ins, txt from TEST7
union
select ins, date_ins, hour_ins, txt from TEST8
union
select ins, date_ins, hour_ins, txt from TEST9
union
select ins, date_ins, hour_ins, txt from TEST10;
CREATE INDEX IDX_TEST_1 ON TEST1 (DATE_INS);
CREATE INDEX IDX_TEST_10 ON TEST10 (DATE_INS);
CREATE INDEX IDX_TEST_2 ON TEST2 (DATE_INS);
CREATE INDEX IDX_TEST_3 ON TEST3 (DATE_INS);
CREATE INDEX IDX_TEST_4 ON TEST4 (DATE_INS);
CREATE INDEX IDX_TEST_5 ON TEST5 (DATE_INS);
CREATE INDEX IDX_TEST_6 ON TEST6 (DATE_INS);
CREATE INDEX IDX_TEST_7 ON TEST7 (DATE_INS);
CREATE INDEX IDX_TEST_8 ON TEST8 (DATE_INS);
CREATE INDEX IDX_TEST_9 ON TEST9 (DATE_INS);
####################
####################
#!/usr/bin/python
"""
Script Python.
Per motivi tecnici, viene riportato l'adattamento per la creazione di
dati su Firebird. Per poterlo utilizzare su PostgreSQL (ma penso anche
su altri RDBMS), basta cambiare la libreria del database (su Firebird la
libreria e` KinterbasDB), i dati di connessione, e il carattere di
passaggio dei dati nella query (da ? a %s )
"""
import kinterbasdb as db
import datetime
cur_time = datetime.datetime(1600, 1, 1, 0, 0, 0)
end_time = datetime.datetime(2008, 12, 31, 23, 0, 0)
con = db.connect(host="localhost",
                 database="/home/ebianchi/tmp/db/test.fdb",
                 user="sysdba",
                 password="masterkey")
con = db.connect(host="192.168.1.2",
                 database="test",
                 user="ebianchi",
                 password="ebianchi")
cur = con.cursor()
run = True
table = 1
years = 0
count_insert = 0
while run:
    cur.execute("INSERT INTO test" + str(table) +
                    "(ins, date_ins, hour_ins, txt) " +
                    "VALUES(nextval('test_seq'), ?, ?, ?)",
                    ["".join([str(cur_time.year),
                              str(cur_time.month).zfill(2),
                              str(cur_time.day).zfill(2)]),
                     "".join([str(cur_time.hour).zfill(2),
                              str(cur_time.minute).zfill(2),
                              str(cur_time.second).zfill(2)]),
                     " ".join(["Testo data",
                               "/".join([str(cur_time.day).zfill(2),
                                        str(cur_time.month).zfill(2),
                                        str(cur_time.year)]),
                               "ore",
                               str(cur_time.hour)])])
    cur_time = cur_time + datetime.timedelta(hours=1)
    count_insert = count_insert + 1
    if years == 40:
        years = 0
        table += 1
        print "Inserimento nella tabella " + str(table)
    if count_insert == 8760:
        print "Inserimento anno " + str(cur_time.year)
        years = years + 1
        count_insert = 0
        con.commit()
 
    if table > 10:
        run = False
    if cur_time == end_time:
        print "Data corrente uguale a data di fine"
        run = False
cur.close()
con.close()
####################

  tempo di risposta e` di 54 secondi.
3:09 contro 54; la max() si scansiona tutto insomma.
Se vuoi prova a vedere cosa cambia mettendo i CHECK.
ciao 

 3:09 contro 54; la max() si scansiona tutto insomma.
Come ho detto, lo sviluppo di Firebird e` piu` incentrato sulla
stabilita` e sulla scalabilita`, che sulle perfomance vere e proprie. Su
dei test che circolano su internet ed eseguiti su Firebird 1.5, si vede
chiaramente che il numero di query servite contemporaneamente,
nonostante sia piu` basso rispetto a PostgreSQL, aumenta in maniera
costante. Di contro, PostgreSQL 8.1 riesce a servire piu` query
contemporaneamente, ma il suo andamento e` molto piu` altalenante
Enrico

 selezione
Non solo durante gli inserimenti: il query optimizer puo' guardare
le clausole CHECK per trarre alcune conclusioni; ad esempio,
puo' sapere in anticipo se la colonna contiene o no un certo dato.
Nel ns. caso, se in una tabella c'e` un CHECK Date between '1600'
and '1640' su una colonna, e la query e` where Date = 1800, il
dbms sa che non c'e` neppure bisogno di leggersi la tabella perche`
il dato non puo` stare li`, "per definizione".
Ciao 

 base ultra stabile
Prendi in considerazione Dell. Se hai la possibilita` di acquistarlo via
PMI, un Optiplex e` una manna, altrimenti vedi un XPS della serie 420
(sugli Inspiron non mi pronuncio, in quanto non li ho mai provati). In
ufficio, ho un Optiplex 745 da due anni, e non ho mai avuto alcun
problema, senza considerare che l'unica parte che fa rumore e` il
masterizzatore DVD quando gli inserisci un supporto da leggere o da
scrivere. Per ritornare IT, sul discorso dei CHECK mi hai convinto,
provo e poi posto i risultati (con query sensate, stavolta :) )
Enrico

 contemporaneamente, ma il suo andamento e` molto piu` altalenante
Comunque io con i cammpi DATA in firebird ho avuto qualche strano
comportamento.
Del tipo indicizzo una tabella proprio per quel campo, e una where del tipo
WHERE ID BETWEEN (1 and x) AND DATA BETWEEN (datai AND dataf)
Con l'indice era mooolto lenta, senza andava meglio. Non ho testato se è un
comportamento comune. I ogni caso uso FB 1.5, quindi va preso con le pinze.

 comportamento.
Boh, dall'explain che mi presenta Flamerobin gli indici vengono sempre
utilizzati. Tieni pero` presente  che io sto "giocando sporco", ovvero
DATA_INS e` di tipo INTEGER
Enrico

 partizionamento logico.
Tutto molto interessante, ma... non ho capito cosa volevi testare :^)
Cioè, a parte l'oggetto del test, che tipo di valutazione ti proponi
di fare? Se vale la pena passare ad un DB che fa partizionamento vero
o no?
bye!

 di fare? 
Nessuna, la il test era solo per semplice scazzo :)
La cosa e` nata in Message-ID: <g0slbd$70c$1...@abraham.hellfish.home> , e
visto che non avevo nulla di importante da fare...
Enrico

 partizionamento logico
Altro giro, altra corsa. Stavolta, non pago, ho provato in ufficio su di
un server acquistato all'uopo per far girare tutti i RDBMS che
servono[1]. In teoria volevo provare anche il partizionamento fisico su
Oracle, ma per motivi di tempo non ho potuto. Il server e` cosi`
composto:
Hardware:
- Intel Xeon E5420  @ 2.50GHz (due CPU fisiche, 8 core in totale)
- 8GB RAM
- 6 HDD SAS 15k
Software:
- Debian stable + backports a 64 bit
- Firebird 2.0.3
- PostgreSQL 8.2.5
La struttura e` simile a quella precedente, con la differenza di
implementare i CHECK. Il criterio di generazione dei dati e` rimasto lo
stesso dell'altra volta, con la differenza che non c'e` alcun limite,
ovvero l'ultima tabella viene riempita fino alla data di fine. I
dati su cui sono stati eseguiti i test sono quindi 3.585.239
Ecco i risultati:
Firebird:
- Esecuzione di una query di estrazione della data massima inserita:
  01:17 minuti
- Esecuzione di una query che estrae un range di anni pari a  20: 00:04
  minuti
- Esecuzione di una query che estrae un range di anni pari a 100: 00:20
  minuti
- Esecuzione di una query che estrae un range di anni pari a 200: 00:33
  minuti
PostgreSQL:
- Esecuzione di una query di estrazione della data massima inserita:
  6086 millisecondi
- Esecuzione di una query che estrae un range di anni pari a  20: 16642
  millisecondi
- Esecuzione di una query che estrae un range di anni pari a 100: 79002
  millisecondi
- Esecuzione di una query che estrae un range di anni pari a 200: 157904
  millisecondi
Devo dire che il comportamento di PostgreSQL 8.2 mi ha stupito un po':
rispetto alla 8.3, viene fatto sempre lo scan di tutti gli indici delle
tabelle presenti nella vista. Un comportamento simile a quello di
Firebird, per capirci. Come l'altra volta, di seguito c'e` il codice sia
della struttura del database, sia quello dell'applicazione scritta per
generare i dati
Enrico
[1] si, lo so, il tuning in una macchina del genere va a farsi benedire,
ma sinceramente non me ne importa piu` di tanto, in quanto e` una
macchina dedicata allo sviluppo (e quindi delle prestazioni non ce ne
frega molto)
################
-- Struttura del database
CREATE GENERATOR TEST_SEQ;
CREATE TABLE TEST1
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test1_check CHECK(date_ins <= 16391231)
);
CREATE TABLE TEST10
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test10_check CHECK(date_ins >= 19800101)
);
CREATE TABLE TEST2
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test2_check CHECK(date_ins >= 16400101 AND date_ins <= 16791231)
);
CREATE TABLE TEST3
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test3_check CHECK(date_ins >= 16800101 AND date_ins <= 17191231)
);
CREATE TABLE TEST4
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test4_check CHECK(date_ins >= 17200101 AND date_ins <= 17591231)
);
CREATE TABLE TEST5
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test5_check CHECK(date_ins >= 17600101 AND date_ins <= 17991231)
);
CREATE TABLE TEST6
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test6_check CHECK(date_ins >= 18000101 AND date_ins <= 18391231)
);
CREATE TABLE TEST7
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test7_check CHECK(date_ins >= 18400101 AND date_ins <= 18791231)
);
CREATE TABLE TEST8
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test8_check CHECK(date_ins >= 18800101 AND date_ins <= 19391231)
);
CREATE TABLE TEST9
(
  INS Integer,
  DATE_INS Integer,
  HOUR_INS Integer,
  TXT Varchar(30),
  CONSTRAINT test9_check CHECK(date_ins >= 19400101 AND date_ins <= 19791231)
);


################
#!/usr/bin/python
"""
Script Python.
Lo script riporta sia i dati per la connessione a Firebird che a
PostgreSQL. Per utilizzarlo su uno o sull'altro, basta decommentare le
righe del database interessato e commentare le altre
"""
# Firebird
#import kinterbasdb as db
# PostgreSQL
import psycopg2 as db
import datetime
cur_time = datetime.datetime(1600, 1, 1, 0, 0, 0)
end_time = datetime.datetime(2008, 12, 31, 23, 0, 0)
# PostgreSQL
"""
Per ovvi motivi, non riporto i dati relativi all'utente e alla
password del database PostgreSQL
"""
con = db.connect(host="192.168.70.212",
                 database="test",
                 user="user",
                 password="password")
# Firebird
"""
con = db.connect(host="192.168.70.212",
                 database="~/2.0/data/test.fdb",
                 user="sysdba",
                 password="masterkey")
"""
cur = con.cursor()
run = True
table = ""
years = 0
count_insert = 0
while run:
    if cur_time.year <= 1639:
       table = "test1"
    elif cur_time.year >= 1640 and cur_time.year <= 1679:
       table = "test2"
    elif cur_time.year >= 1680 and cur_time.year <= 1719:
       table = "test3"
    elif cur_time.year >= 1720 and cur_time.year <= 1759:
       table = "test4"
    elif cur_time.year >= 1760 and cur_time.year <= 1799:
       table = "test5"
    elif cur_time.year >= 1800 and cur_time.year <= 1839:
       table = "test6"
    elif cur_time.year >= 1840 and cur_time.year <= 1879:
       table = "test7"
    elif cur_time.year >= 1880 and cur_time.year <= 1939:
       table = "test8"
    elif cur_time.year >= 1940 and cur_time.year <= 1979:
       table = "test9"
    elif cur_time.year >= 1980:
       table = "test10"
    cur.execute("INSERT INTO " + table +
                "(ins, date_ins, hour_ins, txt) " +
                #"VALUES(gen_id(test_seq, 1), ?, ?, ?)",    # Firebird
                "VALUES(nextval('test_seq'), %s, %s, %s)", # PostgreSQL
                ["".join([str(cur_time.year),
                          str(cur_time.month).zfill(2),
                          str(cur_time.day).zfill(2)]),
                 "".join([str(cur_time.hour).zfill(2),
                          str(cur_time.minute).zfill(2),
                          str(cur_time.second).zfill(2)]),
                 " ".join(["Testo data",
                           "/".join([str(cur_time.day).zfill(2),
                                     str(cur_time.month).zfill(2),
                                     str(cur_time.year)]),
                           "ore",
                           str(cur_time.hour)])])
    cur_time = cur_time + datetime.timedelta(hours=1)
    count_insert = count_insert + 1
    if count_insert == 8760:
        print "Inserito anno " + str(cur_time.year)
        count_insert = 0
        con.commit()
    if cur_time == end_time:


 Altro giro, altra corsa.
Infine, come si dice, non c'e` due senza tre. Questo e` il risultato dei
test sui miei pc di casa, con i CHECK sulle tabelle impostati:
Firebird:
- Esecuzione di una query di estrazione della data massima inserita:
  3:17 minuti.
- Esecuzione di una query di estrazione di un range di 20 anni: 00:03
  minuti.
- Esecuzione di una query di estrazione di un range di 100 anni: 00:58
  minuti.
- Esecuzione di una query di estrazione di un range di 200 anni: 01:25
  minuti.
PostgreSQL:
- Esecuzione di una query di estrazione della data massima inserita: 29989
  millisecondi.
- Esecuzione di una query di estrazione di un range di 20 anni: 17807
  millisecondi.
- Esecuzione di una query di estrazione di un range di 100 anni: 81709
  millisecondi.
- Esecuzione di una query di estrazione di un range di 200 anni: 164114
  millisecondi.
Personalmente devo dire che queste prove mi hanno colpito positivamente.
Sinceramente, in un contesto del genere, mi aspettavo dei risultati ben
peggiori, in quanto stavo ricorrendo ad un barbatrucco per ottimizzare
una situazione critica. Infine, se consideriamo che i database non hanno
avuto tuning per supportare la gestione dei dati tramite un
partizionamento fittizio, direi che posso essere piu` che soddisfatto
dei risultati
Enrico
P.S. Comunque, se devo essere pgnolo, mancherebbe da testare uno
scenario in cui il database supporta realmente le funzionalita` di
partizionamento dei dati. Purtroppo, efettuare una prova in questo
senso, mi richiede molto piu` tempo, in quanto un Oracle funzionante
l'ho disponibile in ufficio e, tra l'altro, la sua licenza mi
impedirebbe di postare dei test comparativi senza prima essere
autorizzato da Oracle stessa (non sto scherzando)