Newsgroup: it.comp.software.database
Titolo: Postgre: operazioni su tabella molto lente

Ho una tabella in PostgreSQL 7.4.8 contenente circa 52000 record, e
quando faccio le query di selezione mi sembra che sia molto lenta,
nonostatnte abbia messo un indice sul campo data_op (CREATE INDEX
data_op ON registro USING btree (data_op)).
Ad es., se faccio una query tramite phpPgAdmin del tipo :
SELECT * FROM registro WHERE data_op >= '01/01/2005' AND data_op <=
'01/08/2006'
addirittura non finisce mai di elaborare: tramite query analyze mi da
questo risultato:
QUERY PLAN
Index Scan using data_op on registro  (cost=0.00..367.81 rows=8886
width=102) (actual time=0.030..23.937 rows=5973 loops=1)
  Index Cond: ((data_op >= '2005-01-01'::date) AND (data_op <=
'2006-08-01'::date))
Total runtime: 27.053 ms
mentre se modifico la data da 2005 a 2006, quindi restringendo la
ricerca, ottengo :
QUERY PLAN
Index Scan using data_op on registro  (cost=0.00..128.87 rows=2092
width=102) (actual time=0.029..2.215 rows=501 loops=1)
  Index Cond: ((data_op >= '2006-01-01'::date) AND (data_op <=
'2006-08-01'::date))
Total runtime: 2.565 ms
E' normale!?!?! Come faccio a migliorare la situazione?!?!

Mentre cercavo di capire la differenza tra una select e un drop database,
xmaxfane ha scritto:

ciao,
guarda giusto per toglierci subito ogni dubbio, fai un
vacuum full analyze e riprova la query.
bye

 
Ho riprovato, ma non è cambiato nulla :-(

> ciao,

uhmm.... se provi a fare la select impostando un limit
cambia qualcosa??
Intendo:
select * FROM registro WHERE data_op >= '01/01/2005' AND data_op <=
'01/08/2006' limit 10;
se le cose cambiano sostanzialmente allora mi sa che il problema
potrebbe essere risolvibile usando un cursore esplicito. 
bye

 E' normale!?!?! Come faccio a migliorare la situazione?!?!
Non sono abituato a leggere i plan PostgreSQL, ma sembra che il rapporto
tra i tempi di esecuzione si avvicini molto al rapporto tra le righe
interessate, quindi ciò che rallenta la prima query lo fa anche per la
seconda.
C'è una grande differenza tra il costo stimato e il costo reale.
Prova a ricostruire l'indice.
Bye
-- 
Rudy
blog: 
http://rdbland.blogspot.com/
e-mail a 
email.it

 cambia qualcosa??
Ho provato ad eseguire la query, è velocissima; questo è l'explain
invece:
QUERY PLAN
Limit  (cost=0.00..0.39 rows=10 width=102) (actual time=0.030..0.087
rows=10 loops=1)
  ->  Index Scan using indice_data_op on registro  (cost=0.00..406.06
rows=10349 width=102) (actual time=0.027..0.072 rows=10 loops=1)
        Index Cond: ((data_op >= '2005-01-01'::date) AND (data_op <=
'2006-08-01'::date))
Quindi?!!? Cosa vuol dire questo? Che intendi con "usando un cursore
esplicito"?!?

 

Brevemente:
postgres prima di restituirti il resultset, aspetta di avere 
il risultato. Solo successivamente comincia a darti output.
Quindi, quando lanci la query, prima di avere un solo record
devi aspettare che l'elaborazione vada a termine. In questo caso
i limiti h/w del server sono un potenziale collo di bottiglia.
Perciò conviene usare una funzione in cui dichiari un cursore
e fai il fetch di un record per volta, oppure usi insieme limit e
offset per scorrere i record.
copy&paste paro paro dalla doc:
"Rather than executing a whole query at once, it is possible to set up a
cursor that encapsulates the query, and then read the query result a few
rows at a time. One reason for doing this is to avoid memory overrun when
the result contains a large number of rows. (However, PL/pgSQL users don't
normally need to worry about that, since FOR loops automatically use a
cursor internally to avoid memory problems.) A more interesting usage is
to return a reference to a cursor that it has created, allowing the caller
to read the rows. This provides an efficient way to return large row sets
from functions."
ciao

 offset per scorrere i record.
Quindi se ho capito bene: mi dovrei fare il count dei record della
tabella, dopodichè dovrei mettere dentro un ciclo la query modificando
di volta in volta il limit e l'offset fino ad arrivare al count della
tabella, e questo dovrebbe risolvere o almeno migliorare il mio
problema di lentezza.
E' giusto quello che ho riassunto, oppure non ci ho capito un
cavolo?!?!
Intanto grazie per l'aiuto.

 Intanto grazie per l'aiuto.
perchč il count??
non ti serve sapere il numero esatto:
prima select) select ..... limit 100;
seconda) select.....limit 100 offset 101;
ultima) select...limit 100 offset xxx
se non ci sono tutti i 100 record finali
ne riceverai di meno. Questo non č un problema,
esempio:
gulp=# select count(1) from stragulp ;
 count
-------
   897
(1 row)
gulp=# select * from stragulp limit 1000 offset 898;
 id   | nome | cognome
------+------+---------
(0 rows)
 
gulp=# select * from stragulp limit 1000 offset 897;
 id   | nome | cognome
------+------+---------
(0 rows)
 
gulp=# select * from stragulp limit 1000 offset 896;
 id   |   nome   |  cognome
------+----------+-----------
 0.00 | PIPPO    | PLUTO
(1 row)
Attenzione perň: quando usi limit & offset il risultato
non č univocamente prevedibile, devi forzare un order by
nella select.
Cmq da NON programmatore quale sono, direi che da pgpsql puoi
gestire sicuramente un ciclo che ti restituisca tot record per
volta anche senza limit & offset.
ciao

 non ti serve sapere il numero esatto
Il count lo faccio per vedere quanti record sono in totale, dopodichè
dentro un ciclo for che incremento per diciamo 1000 record alla volta
inserisco la query da ripetere con il limit 1000 e l'offset che
corrisponde all'incrementatore del ciclo. Es.:
$tot_count = nr record totali della tabella risultanti dalla query
for ($i=0; $count<=$tot_count; $i=$i+1000) {
query = select ... limit 1000 offset $i
...
}
Ho provato e sembra che funzioni, adesso però la testo meglio.
Se funziona mi hai risolto un grosso problema.
Se non funziona ti ringrazio lo stesso per il valido aiuto che mi hai
dato!

 Ho provato e sembra che funzioni, adesso però la testo meglio.
Ho preso un abbaglio, non funziona!
E' la stessa cosa della query per intero.
Ma è possibile che anche frazionando la query con l'offset ci si
impieghi lo stesso tempo!?

 xmaxfane ha scritto:

mumble mumble........
è veloce solo ai primi cicli?
hai provato con un cursore o stai usando un ciclo
all'interno di una applicazione?
Io ho un db con una tabella da più
di 3 milioni di record e una select con un range di
un anno impiega circa 3 minuti.
Le tue di preciso quanto impiegano?
Non vorrei che alla fin fine sul tuo server ci fossero limiti
h/w insormontabili e/o configurazioni che possono essere 
migliorate.
Piuttosto, scusa la frammentarietà dell'aiuto, ma così senza avere
tutte le info insieme viene difficle capire bene
quale possa essere realmente la causa della lentezza.
bye

 all'interno di una applicazione?
Sto usando un ciclo for all'interno di un'applicazione, come ti ho
postato prima.
Ma che intendi per cursore, scusa la mia ignoranza ma io non lo mica
capito cos'è e come si deve usare?!

 freCho ha scritto:

Un cursore è un oggetto del db che viene usato per
scorrere un recordset. Fondamentalmente è un puntatore
al record attuale/seguente.
Se devi smanettare pesantemente con postgres ti consiglio
vivamente gli archivi delle mailing lists.
Info sui cursori le trovi invece qui:
http://www.postgresql.org/docs/7.4/interactive/plpgsql-cursors.html
BTW, poichè il numero di record incriminati è 52000, che per un
db è nulla, direi che il problema è nell'H/W e/o configurazione
del server psql.
bye

 
http://www.postgresql.org/docs/7.4/interactive/plpgsql-cursors.html
Avevo già guardato quella pagina, ma non è che ci ho capito
granchè... mi sa che mi conviene mettere un post per i cursori
Nel frattempo...grazie

 E' normale!?!?! Come faccio a migliorare la situazione?!?!
No, un index scan così lento su 50K righe non è assolutamente
normale, a meno che non contengano tuple da 50K l'una :D. Sospetto che
ci sia qualche vizio di base sotto, così su due piedi mi vengono in
mente, grossolanamente in ordine inverso di probabilità:
- buffer insufficienti
- memoria/swap area insufficiente
- installazione bacata
- HW insufficiente
- HW bacato
Dicci di più sulle configurazioni di base...

 Dicci di più sulle configurazioni di base...
Riguardo all'hardware è una macchina nuova, un server ibm x206m con SO
centos 4.3, con configurazione di postgres di default.
Ci sono particolari settaggi da effettuare?!?!

 Ci sono particolari settaggi da effettuare?!?!
No, già con la configurazione standard dovrebbe andare molto più di
come dici.
Per il resto la macchina sembra rispondere bene? E' performante?

 Per il resto la macchina sembra rispondere bene? E' performante?
Fino ad 'ora ho sempre usato mysql e non ho mai avuto di questi
problemi.
Adesso con postgres mi sono sorti: vabbè che è più lento rispetto a
mysql, ma così è esagerato.
Possibile che facendo operazioni con tab di 52000 record, che fra
l'altro è la più grande, perchè le altre al max sono di 20000
record, possano sorgere questi problemi?!?!
Suggerimenti?!?!

 Suggerimenti?!?!
Intanto posta il file postgres.conf così vediamo se ci sono dei
dimensionamenti errati.
A livello di filesystem come sei messo? Se hai lasciato tutto come di
default sotto /var/lib/pgsql, com'è definita la partizione /var?
bye

 default sotto /var/lib/pgsql, com'è definita la partizione /var?
E' la partizione primaria: l'unico dubbio può essere che i dischi sono
in raid software con mdtools, però con la stessa cosa mysql funziona
benissimo, quindi sarebbe da escludere.
La conf è la seguente:
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.) White space may be used. Comments are
introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default
values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 100
        # note: increasing max_connections costs about 500 bytes of
shared
        # memory per connection slot, in addition to costs from
shared_buffers
        # and max_locks_per_transaction.
#superuser_reserved_connections = 2
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''              # what interface to listen on; defaults
to any
#rendezvous_name = ''           # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60    # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 1000          # min 16, at least max_connections*2,
8KB each
#sort_mem = 1024                # min 64, size in KB
#vacuum_mem = 8192              # min 1024, size in KB
# - Free Space Map -
#max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes
each
#max_fsm_relations = 1000       # min 100, ~50 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000   # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#authentication_timeout = 60    # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 100
        # note: increasing max_connections costs about 500 bytes of
shared
        # memory per connection slot, in addition to costs from
shared_buffers
        # and max_locks_per_transaction.
#superuser_reserved_connections = 2
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000        # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes
each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
#regex_flavor = advanced        # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
P.S.: scusate ma non sapevo come metterla tra le virgolette

 8KB each
Scusate se mi intrometto, ma non è un po' bassino? Corrisponderebbe a
meno di 8 MB. Io lo imposterei ad almeno 10 volte tanto, o no?
Bye
-- 
Rudy


 Bye
assolutamente si!
xmaxfrane, se il tuo server ospita solo il postgres,
settalo in modo da dargli il 70% della ram di sistema.
Mi sa che il tuo server perde il tempo a swappare
su disco.
ciao

 su disco.
Ho provato a settare il valore shared_buffer a 10000 ma così postgres
nn parte. Parte con valori <= di 2000. E' normale una cosa del genere?

Mentre cercavo di capire la differenza tra una select e un drop database,
xmaxfane ha scritto:

devi anche modificare i parametri del kernel in maniera
congrua. Se come presumo sei su linux verifica con
cat /proc/sys/kernel/shmmax qual'è il valore attuale.
Sarà quasi sicuramente più basso di quanto gli stai
configurando su psql.
Quindi modificalo con echo "valore" > /proc/sys/kernel/shmmax
vedrai che parte.
ovviamente devi mettere questa stringa in qualche
file di /etc/rc.?? che venga eseguito prima dello start
di postgres.
ciao

 meno di 8 MB. Io lo imposterei ad almeno 10 volte tanto, o no?
Sì, può essere aumentato, ma già così per un index scan su una
tabella di soli 50.000 record non dovrebbe avere problemi. Dev'esserci
qualcos'altro, ma sto cominciando a rimanere a corto di idee.
Com'e' la struttura della tabella? Cambia qualcosa se invece che fare
una "select *" fai la select solo sul campo contenuto nell'indice?

 configurando su psql.
Ho fatto come mi hai detto, da 33 Mb l'ho aumentato a 100 Mb, ed il
valore shared_buffer da 1000 a 10000, ho riavviato e questa volta è
ripartito senza problemi.
Ho provato ad effettuare la query precedente (sulla tab di 52000
record) da PhpPgAdmin, ossia :
SELECT * FROM registro WHERE data_op >= '01/01/2005' AND data_op <=
'01/08/2006'
e ci ha messo lo stesso un'eternità:
5973 riga(ghe)
Tempo di esecuzione totale: 155,552.241 ms
e l'explain:
QUERY PLAN
Index Scan using indice_data_op on registro  (cost=0.00..406.06
rows=10349 width=102) (actual time=0.027..23.821 rows=5973 loops=1)
  Index Cond: ((data_op >= '2005-01-01'::date) AND (data_op <=
'2006-08-01'::date))
Total runtime: 26.939 ms
Da cosa può dipendere ancora, io non so più che pesci pigliare?!?!?

>freCho wrote:

guarda, è veramente strano questo comportamento, bisognerebbe
essere li col server a disposizione e fare analisi un pochino
più profonde.
Ma, tagliamo la testa al toro, visto che nessuno sviluppatore
ti risponde sui cursori ti rispondo per quello che ne so:
Sono su un db in produzione (ergo sotto carico). La tabella in questione
è la stessa di qualche post fa: più di 3 milioni di record. Una select
su un range di un anno circa impiega tre minuti (senza cursore).
services=# \timing
Timing is on.
services=# begin;  <<== inizio una transazione: indispensabile per usare i
cursori in modo eplicito.
BEGIN
Time: 0.557 ms
services=# declare all_fax cursor for select * from nometabella where
sent_date >= '2006-01-01' and sent_date <= '2006-06-30';  <<==dichiaro il
cursore
DECLARE CURSOR
Time: 5.645 ms
services=# fetch 4 from all_fax;    <<== seleziono i primi 4 record
..... NON METTO I DATI PER OVVIE RAGIONI .............           
(4 rows)
 
Time: 7.187 ms    <<<========================= NOTA BENE!!!!!!!
services=# move forward 15000 in all_fax;  <<<==== MI sposto in avanti di
15000 records
MOVE 15000
Time: 2003.528 ms    <<<<============== ARRI-NOTA BENE  :)
services=# fetch 4 from all_fax;
........................ ..........................
(4 rows)
 
Time: 0.913 ms
services=# fetch next from all_fax;   <<== prendo il record sucessivo
................................................
(1 row)
 
Time: 0.733 ms
services=# close all_fax;  <<== chiudo il cursore
CLOSE CURSOR
Time: 0.395 ms
services=# commit;  <<== chiudo la transazione
COMMIT
Time: 2.162 ms
services=#
ti rimetto i comandi puliti dai commenti, altrimenti è troppo confuso:
services=# \timing
services=# begin;
services=# declare all_fax cursor for select * from nometabella where
sent_date >= '2006-01-01' and sent_date <= '2006-06-30';
services=# fetch 4 from all_fax;
services=# move forward 15000 in all_fax;
services=# fetch 4 from all_fax;
services=# fetch next from all_fax;
services=# close all_fax;  
CLOSE CURSOR
Time: 0.395 ms
services=# commit;
COMMIT
Time: 2.162 ms
services=#
spero sia abbastanza chiaro. Se non lo è chiedi :)
ciao

 una "select *" fai la select solo sul campo contenuto nell'indice?
Si, cambia tantissimo, anche se faccio la query su un campo che non è
l'indice ci mette poco, però mano a mano che aggiungo un campo le
prestazioni calano notevolmente (in tot. la tab è formata da 14
campi):
1 campo indice:       Tempo di esecuzione totale: 8,773.987 ms
1 campo non indice: Tempo di esecuzione totale: 12,105.440 ms
2 campi (1 indice):   Tempo di esecuzione totale: 20,899.132 ms
2 campi (0 indice):   Tempo di esecuzione totale: 26,631.965 ms
3 campi (1 indice):   Tempo di esecuzione totale: 35,192.652 ms
3 campi (0 indice):   Tempo di esecuzione totale: 39,456.217 ms
Cosa può risultare da questo, ossia che su un campo solo le
prestazioni sono nella norma, mentre su tutta la tabella degradano in
maniera esagerata!?!?
Ci sono forse altre impostazioni da modificare nel file di config.?!?
...nell'attesa vi ringrazio dell'aiuto che mi state fornendo

 maniera esagerata!?!?
No, non sono nella norma neanche per un campo solo: OTTO Secondi per
una normale select su un campo indice su 50000 record è roba da
pentium 100 con 128 Mb di ram...
Il fatto che i tempi aumentino linearmente all'aumentare dei campi fa
pensare ad una lentezza esagerata nel leggere i dati da disco, ma se
fosse così dovresti comunque avvertire una certa lentezza anche in
altre operazioni della macchina.
Per togliere anche questo dubbio... quanto ti dà l'hdparm sulla
partizione root?

 pentium 100 con 128 Mb di ram...
Per darti un termine di paragone, ho fatto ora una prova e per tirare
fuori un campo da ca. 30000 righe su 80000 con un'index scan, su una
macchina PIII da 1 Ghz con 768 Mb di Ram e dischi da ca 40 Mb/sec, ci
mette poco più di 400 ms. Con i buffer a 1000 esattamente come di
default.
Per questo dico che ci deve essere un baco da qualche parte...

 partizione root?
Ho eseguito il comando hdparm -tT /dev/sdb e questo è il risultato:
/dev/sdb:
 Timing cached reads:   3328 MB in  2.00 seconds = 1662.59 MB/sec
 Timing buffered disk reads:  180 MB in  3.00 seconds =  59.91 MB/sec
Che significa tutto ciò!?!?

 Che significa tutto ciò!?!?
Alcune cose:
1) probabilmente hai un controller con almeno 8 Mb di cache a bordo,
altrimenti non si spiega il valore di troughput astronomico delle
cached read, che è evidentemente fuori scala per un HD :D
2) che in linea di massima il disco è abbastanza veloce, quindi non ci
sono problemi neanche lì...
Bohf. Forse una qualche forma di corruzione dei dati nel DB?
Fai anche questa prova:
1) dumpa tutto il contenuto del db 'incriminato' con pg_dump
2) restora il tutto (con psql -i) dentro un db diverso creato ex novo
3) dagli un bel vacuum analyze (alla fine dell'import forse lo fa da
solo ma abbondare non guasta)
4) riprova i timing sul db 'pulito'
e vediamo...

 4) riprova i timing sul db 'pulito'
Fatto, ho restorato tutto con il cmd restore perchè psql -i non me lo
prende, ho provato a rifare la query ma è tutto uguale:
5973 riga(ghe)
Tempo di esecuzione totale: 154,343.676 ms
Può essere che ci siano problemi con i dati perchè ho importato i
dati da access a postgres tramite uno scriptino che ho fatto io?!?!

xmaxfane ha scritto:

Scusa, puoi provare ad eseguire la query dal frontend nativo ovvero
psql e vedere che tempi hai?

 E' normale!?!?! Come faccio a migliorare la situazione?!?!
Normale no. Sicuramente c'è qualche cosa che rallenta, perché se ti
dico i tempi del mio e su che macchina gira, ti arrabbi :D

 Quanta GB di ram hai? La partizione di swap è attiva?
1 GB di ram su ibm x206m e partizione attiva
Per le altre prove tento domani e faccio sapere.
Thanks

 dati da access a postgres tramite uno scriptino che ho fatto io?!?!
Mah, normalmente no, non vedo motivi per cui il modo in cui hai
importato i dati possa inficiare in qualche modo le prestazioni delle
query successive. A meno che tu nella procedura di input non abbia
portato con te dei tipi un po' indigesti come BLOB o CLOB...
Le dimensioni in K di un record quanto sono? Non mi ricordo se nella
versione che usi te  postgres aveva ancora la vecchia limitazione 1
record = 1 blocco...
bye!

 record = 1 blocco...
La dimensione in K non so calcolarla, ti posso dire però che è un
record normalissimo: è una tab di 14 campi di cui 6 integer, 2 date, 1
timestamp, 2 numeric(10,2), 1 text e un character(1), quindi non mi
sembra che ci sia niente di "impegnativo"!

 sembra che ci sia niente di "impegnativo"!
Non sembrerebbe proprio, a meno che dentro il campo text non ci siano
campi da più di 8Kb, ma non mi pare che neanche Access li supporti e
quindi non la vedo probabile.
Sono veramente a corto di idee... a questo punto restano le soluzioni
bovine: upgrada kernel e versione di postgresql se puoi...

 bovine: upgrada kernel e versione di postgresql se puoi...
Si, credo che farò così, ma ho paura di avere problemi di
compatibilità con l'upgrade, spero che non ci siano grosse difficoltà

 compatibilità con l'upgrade, spero che non ci siano grosse difficoltà
fai prima un pg_dumpall in modalità SQL. Così alle brutte puoi
ripartire da zero e reimportare quello.
In bocca al lupo...