lunedì 4 febbraio 2008

Dalle tabelle ai file...

Come ogni buon RDBMS, PostgreSQL gestisce in modo trasparente i dati su supporto fisico (disco fisso), lasciando all'utente la cognizione del solo livello di gestione dei dati (relazioni, indici, ecc.). E' comunque possibile consultare i cataloghi interni di PostgreSQL per sapere dove e come vengano salvati i dati sul supporto fisico. In questo post verrà mostrato come trovare la corrispondenza fra dati e file su disco, e come calcolare lo spazio disco occupato da un database o da una tabella. Negli esempi che seguiranno si farà riferimento ad un database testdb che contiene la tabella clienti definita di seguito:

testdb=# \d clienti
Table "public.clienti"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
pk | integer | not null default nextval('clienti_pk_seq'::regclass)
name | text |
address | text |
Indexes:
"clienti_pkey" PRIMARY KEY, btree (pk)

Per recuperare le informazioni relative a dove i dati sono memorizzati occorre consultare il catalogo di sistema. In particolare le tabelle che maggiormente interessano sono:
  • pg_database contiene le informazioni generali sul database (nome, proprietario, tablespace, encoding, ecc.);
  • pg_tablespace contiene le informazioni specifiche sui vari tablespace definiti (locazione su disco, proprietario, ecc.);
  • pg_roles contiene gli utenti definiti nel sistema e il loro id numerico;
  • pg_class contiene le informazioni circa le tabelle definite in ogni database.
La prima cosa da fare, per trovare il proprio database su disco, è quella di interrogare il catalogo dei database:

select db.oid,      -- identificativo del db
db.datname, -- nome simbolico del db
db.datdba, -- amministratore del db
r.rolname, -- nome dell'amministratore
db.dattablespace, -- identificativo del tablespace
ts.spcname, -- nome simbolico del tablespace
ts.spcowner, -- identificativo proprietario tablespace
r2.rolname, -- nome simbolico del proprietario
ts.spclocation -- locazione su disco
from (((pg_database db join pg_roles r on db.datdba = r.oid)
left join pg_tablespace ts on db.dattablespace = ts.oid)
join pg_roles r2 on ts.spcowner = r2.oid);

oid | datname | datdba | rolname | dattablespace | spcname | spcowner | rolname | spclocation
------+----------------+--------+----------------+---------------+-------------------+----------+----------+-----------------------------------
17318 | testdb | 16388 | luca | 1663 | pg_default | 10 | postgres |

Le colonne interessanti in questo caso sono pg_database.oid, che indica l'id del database stesso. In particolare, su disco esisterà una directory con nome pari all'id del database (e quindi 17318). Si noti come il database risulti contenuto nel tablespace di default (spcname = pg_default) e per questi non sia indicata una location particolare. Questo significa che la directory che contiene i file del database si troverà sotto $PGDATA/base/17318. In effetti si ha che:

ls -lhd $PGDATA/base/17318/
drwx------ 2 postgres postgres 4,0K 2008-02-04 11:26 17318/

All'interno della directory 17318 sono contenuti una serie di file, generalmente uno per oggetto del database. Ad esempio, ogni tabella sarà identificata nei cataloghi tramite un OID numerico che troverà corrispondenza in un file su disco con nome pari allo stesso OID. Ad esempio, per la tabella clienti si ha che:

testdb=# select oid,relname, relpages, reltuples from pg_class where relname='clienti';
oid | relname | relpages | reltuples
------+---------+----------+-----------
17321 | clienti | 291 | 36864

La cosa interessante da notare è che la tabella clienti ha un OID 17321 e che occupa su disco 291 pagine per 36864 tuple (lo stesso dato si ottiene da un count sulla tabella).

Ipotizzando una dimensione delle pagine di 8kB (il valore dipende dall'installazione di PostgreSQL), si può usare il numero di pagine per capire la dimensione del file/tabella su disco:

testdb=# select relname,
(relpages * 8 * 1024) as size_bytes,
( (relpages * 8 * 1024) / (1024 * 1024) ) as size_MB
from pg_class where relname='clienti';

relname | size_bytes | size_mb
--------+------------+---------
clienti | 2383872 | 2

Come si nota, ci si aspetta una dimensione di 2,3 MB su disco per la tabella 17321, e in effetti si ha che:

ls 17318/17321 -lh
-rw------- 1 postgres postgres 2,3M 2008-02-04 11:32 17318/17321

Riassumendo quindi si ha che:
  1. ad ogni database viene associato un OID numerico, memorizzato nel catalogo di sistema. Tale OID corrisponde ad una directory su disco (all'interno di $PGDATA/base se non si ha un tablespace particolare, altrimenti la directory è contenuta nel tablespace);
  2. ogni oggetto di database, ad esempio una tabella, ha a sua volta un OID che corrisponde ad un file su disco con nome pari all'OID.
Esistono anche funzioni appositamente studiate per visualizzare la dimensione di un database o di una tabella, in particolare:
  • pg_database_size('database_name') mostra la dimensione in bytes del database specificato;
  • pg_relation_size('relation_name') mostra la dimensione dei soli dati della relazione specificata;
  • pg_total_relation_size('relation_name') mostra la dimensione complessiva di una relazione, inclusi i dati e gli indici;
  • pg_size_pretty(value) formatta un valore in un formato umanamente leggibile.
Quindi, per sapere la dimensione del database si può eseguire:

testdb=# select pg_database_size('testdb'),
pg_size_pretty(pg_database_size('testdb'));

pg_database_size | pg_size_pretty
-----------------+----------------
6915800 | 6754 kB
Il primo valore è in bytes, il secondo è in kB formattato automaticamente tramite la pg_size_pretty. Analogamente, per sapere la dimensione di una relazione si ha:

testdb=# select pg_size_pretty( pg_relation_size('clienti') ),
pg_size_pretty( pg_total_relation_size('clienti') );
pg_size_pretty | pg_size_pretty
----------------+----------------
2328 kB | 3000 kB

Si noti come la dimensione dei soli dati corrisponde a 2,3 MB come già evidenziato, mentre si considerano anche gli indici si ha una dimensione di 3 MB.

Si tenga presente che, per ragioni di praticità ed efficienza, PostgreSQL divide i dati di una relazione in gruppi da 1GB. Questo significa che se una relazione aumenta oltre 1GB, il relativo file su disco viene diviso anch'esso in blocchi da 1GB l'uno. Si supponga di aver aumentato la tabella clienti in modo da superare 1 GB di soli dati:


testdb=# select pg_size_pretty( pg_relation_size('clienti') ),
pg_size_pretty( pg_total_relation_size('clienti') );
pg_size_pretty | pg_size_pretty
---------------+----------------
1161 MB | 1484 MB

Su disco quello che è successo è che il file 17321 è stato diviso in due, nello specifico un 17321 (che contiene il primo GB di dati) e un 17321.1 (che contiene il secondo GB di dati). Qualora anche il secondo file raggiunga il suo limite, PostgreSQL provvederà a costruire un file 17321.2 e così via. A livello di catalogo di sistema questa cosa non viene mostrata, ovvero non esiste nella tabella pg_class un'entry per ogni file creato su disco, ma resta solo l'entry della relazione 17321 con il numero di pagine e tuple chiaramente aumentato.

ls -lh 17318/17321*
-rw------- 1 postgres postgres 1,0G 2008-02-04 13:51 17318/17321
-rw------- 1 postgres postgres 138M 2008-02-04 13:52 17318/17321.1

Una considerazione finale d'obbligo: sebbene si sia mostrato come risalire alla locazione fisica dei dati gestiti dal database si ricorda come manipolare manualmente tali dati possa risultare pericoloso per la stabilità e la coerenza dei dati stessi. E' quindi bene lasciare al database la gestione della corrispondenza fra lo spazio fisico e quello logico.

Nessun commento: