Database ottimizzazioni / fine tuning

Sintonizzare la vostra base di dati dei parametri per estrarre il massimo fuori di esso!

Ciao,

Queste sono alcune delle mie esperienze, mentre i stava costruendo un motore di ricerca e il database PostgreSQL optmising verso sonic-velocità!

La nostra configurazione per il server è stato Postgresql:
RedHat 7.2
PIV 2,00 Ghz Sistema
1024MB di RAM

Una delle prime cose che ho notato, dopo aver acceso il Servlet programma, è stato che, anche se le query sono stati restituiti quasi veloce come da precedente sistema basato su MySQL, il carico sul server è stato molto più elevato. Poi ho cominciato a scendere in dettagli profonda delle cose. Ho avuto ottimizzato da MySQL prima di aumentare notevolmente le dimensioni del buffer cache e da buttare e ram più verso il problema. L'unica cosa più grande che uno ha a che fare prima di eseguire PostgreSQL, è quello di fornire abbastanza spazio condiviso tampone. Ma allora,
Quanto è sufficiente?
Vi è un acceso dibattito su di esso, tra le persone che affermano che l'intera logica di RAM potrebbe essere destinato, come nei confronti di coloro che dicono che gettare più di RAM, dopo un certo limite, non ha alcun uso. Il più comune di buffer cache hai, maggiore è la percentuale del database che né cause leggere () 's memoria né la copia del sistema operativo del buffer cache.But globale, si cache un numero inferiore di blocchi, perché vi sarà il doppio buffering loro . Quando si copia un blocco del sistema operativo del buffer di memoria condivisa, esiste ancora la copia del sistema operativo del buffer. Quindi è ora che bloccano tamponato due volte. Un singolo disco I / O è di gran lunga più costoso di centinaia di copie tra il sistema operativo del buffer cache e postgres' la memoria condivisa. Anche prendere in considerazione tutte le altre cose che si sta facendo sulla macchina - solo piccole cose, come cron e tali. Tutto quello che tiene memoria. Pertanto, è pericoloso non lasciare il sistema operativo di gestire una buona porzione di memoria.
Si verifica che queste due fattori potrebbero essere tracciate e rendere un po 'di una linea. L'ideale sarebbe il punto in cui esse hanno attraversato.

Inoltre ho anche ottimizzato SQL specificamente per il mio scopo. Un grave inconveniente in PostgreSQL risiede nella realizzazione della valutazione delle domande contenenti 'in' e 'ESISTE'. Supponiamo:
Query 1. SELECT * FROM db1 ID DOVE IN ((SELECT id DA DOVE db2 parola = 'qualunque')) LIMITE DI 20;
Query 2. SELECT * FROM db1 DOVE IN ID (1234,2345,1242,1256,1245,1567,2222,22,345,234,567,456,35,56);

(dove ID è la chiave primaria)

La query viene digitalizzato in seguito utilizzando l'indice su ID, mentre il primo viene eseguito in una scansione sequenziale. Penso che questo si chiama "errore pilota" in cui il database viene eseguito il sottoquery per ogni riga della query esterna. Invece, se si usa ADESIONE esplicito (come di seguito) e quindi si potrebbe forzare il database da usare invece un indice di scansione.
Domanda finale:
SELECT * FROM db1, db2 a, b db2
dove id = a.id e a.word = 'word1'
e id = b.id e b.word = 'word2'
etc

NOTA: Si può anche eseguire una scansione sequenziale, invece di un atteso indice scansione, se il numero di tuple da sottoporre a scansione sono più di 30-40% del totale delle tuple della tabella. Anche se questo può essere variata modificando i pesi assegnati ai random_page_cost, cpu_tuple_cost, cpu_index_cost e cpu_operator_cost utilizzati dalla ottimizzatore per fare questi decesions.

Ho anche deciso di lanciare più RAM per lo scopo. I assegnati 64 MB di RAM condivisa verso il tampone spazio. Il file / var / lib / pgsql / data / postgresql.conf contiene le impostazioni per il server di database. Postgresql sistema utilizza la memoria condivisa come un buffer. Su un sistema Linux, è possibile visualizzare la quantità di memoria condivisa è stato assegnato dal vostro sistema, eseguendo il comando:
cat / proc / sys / kernel / shmmax
Per visualizzare e utilizzare la memoria condivisa del sistema:
IPCS
Il risultato sarà in byte. Per impostazione predefinita RedHat 7.2 stanzia 32 MB di memoria condivisa, che potrebbe non essere sufficiente per PostgreSQL. Ho aumentato il limite a 64 MB facendo il comando:
echo 67108864> / proc / sys / kernel / shmmax

È necessario inserire questa linea nel vostro file di avvio postgresql, o modificando il file / etc / rc.d / rc.local file per una più permanente setting.Then nella nostra postgresql.conf impostare shared_buffers al 8192.I anche impostare il nostro sort_mem a 16384 (16Megs una sorta di area di memoria). Dal momento che la connessione è stata messa in vigore, mi max_connections fissato a 50.
Ed è stato anche fsync a false.

shared_buffers = 8192
sort_mem = 16384
max_connections = 50
fsync = false

Un intoppo che ho trovato è stato inizialmente che il sistema ha dovuto costruire e abbattere uno postgresql con ogni richiesta di connessione. Questo è intollerabile, così ho iniziato a utilizzare la connessione messa caratteristiche previste dalla resina (http://caucho.com).

-----
Varun

Ringraziamenti: Curt, Bruce, Andrea e tutti i miei dubbi di compensazione!