From: Giovanni Mancuso <gmancuso@babel.it>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem with a table with 38928077 record
Date: Fri, 07 Oct 2011 17:12:06 +0200
Message-ID: <4E8F16C6.7070606@babel.it> (raw)
In-Reply-To: <m34nzlyu9a.fsf@mnc.ch>
References: <4E8ECEB7.4080800@babel.it>
<m34nzlyu9a.fsf@mnc.ch>
Il 07/10/2011 12:24, Guillaume Cottenceau ha scritto:
> Giovanni Mancuso <gmancuso 'at' babel.it> writes:
>
>> select count(*) from dm_object_perm;
>> count
>> ----------
>> 38'928'077
>> (1 row)
> [...]
>
>> If i run "explain analyze select * from dm_object_perm;" it goes on for many
>> hours.
> Almost 39 million records is not small, especially if you run on
> poor hardware[1], poor configuration[2], poor database optimization[3],
> bloat[4], or a combination of these.
>
> [1] you could tell what hardware you use
My Memory:
# cat /proc/meminfo
total: used: free: shared: buffers: cached:
Mem: 4022861824 2201972736 1820889088 0 8044544 1983741952
Swap: 8589926400 199303168 8390623232
MemTotal: 3928576 kB
MemFree: 1778212 kB
MemShared: 0 kB
Buffers: 7856 kB
Cached: 1897356 kB
SwapCached: 39892 kB
Active: 1330076 kB
ActiveAnon: 554472 kB
ActiveCache: 775604 kB
Inact_dirty: 539124 kB
Inact_laundry: 55348 kB
Inact_clean: 36504 kB
Inact_target: 392208 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 3928576 kB
LowFree: 1778212 kB
SwapTotal: 8388600 kB
SwapFree: 8193968 kB
CommitLimit: 10352888 kB
Committed_AS: 1713308 kB
HugePages_Total: 0
HugePages_Free: 0
Hugepagesize: 2048 kB
My CPU:
# egrep 'processor|model name|cpu MHz|cache size|flags' /proc/cpuinfo
processor : 0
model name : Dual Core AMD Opteron(tm) Processor 275
cpu MHz : 2193.798
cache size : 1024 KB
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
3dnowext 3dnow
processor : 1
model name : Dual Core AMD Opteron(tm) Processor 275
cpu MHz : 2193.798
cache size : 1024 KB
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
3dnowext 3dnow
processor : 2
model name : Dual Core AMD Opteron(tm) Processor 275
cpu MHz : 2193.798
cache size : 1024 KB
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
3dnowext 3dnow
processor : 3
model name : Dual Core AMD Opteron(tm) Processor 275
cpu MHz : 2193.798
cache size : 1024 KB
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext lm
3dnowext 3dnow
> [2] you could report if your DB configuration is tuned/good
max_connections = 50
shared_buffers = 512MB
temp_buffers = 128MB
max_prepared_transactions = 55
max_fsm_pages = 153600
vacuum_cost_delay = 0
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200
effective_cache_size = 256MB
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
vacuum_freeze_min_age = 100000000
> [3] you could report if the DB is regularly analyzed/vacuumed
> [4] you could try a VACUUM FULL or CLUSTER and/or REINDEX on your
> large table(s) if you suspect answer to [3] is "no" -
> warning, these block some/all DB operations while running,
> and they will probably run for long in your situation
I run VACUUM yesterday.
>
>> If i try to execute a left join: "SELECT dm_object.id FROM dm_object LEFT JOIN
>> dm_object_perm ON dm_object.id = dm_object_perm.object_id;" my db is unusable.
> EXPLAIN on this query would probably tell you PG has quite some
> work to do to produce the result.
>
>
>> how can I fix this?
> I'm wondering if your DB design (storing almost all "object x
> account" combinations in object_perm) is optimal.
>
--
*Giovanni Mancuso*
System Architect
Babel S.r.l. - http://www.babel.it <http://www.babel.it/;
*T:* 06.9826.9600 *M:* 3406580739 *F:* 06.9826.9680
P.zza S.Benedetto da Norcia, 33 - 00040 Pomezia (Roma)
------------------------------------------------------------------------
CONFIDENZIALE: Questo messaggio ed i suoi allegati sono di carattere
confidenziale per i destinatari in indirizzo.
E' vietato l'inoltro non autorizzato a destinatari diversi da quelli
indicati nel messaggio originale.
Se ricevuto per errore, l'uso del contenuto e' proibito; si prega di
comunicarlo al mittente e cancellarlo immediatamente.
Attachments:logo.gif (image/gif, 1.9K) downloadview imagegmancuso.vcf (text/x-vcard, 303B) download
begin:vcard
fn:Giovanni Mancuso
n:Mancuso;Giovanni
org:Babel S.r.l
adr:;;P.zza S.Benedetto da Norcia, 33;Pomezia;ROMA;00040;Italy
email;internet:gmancuso@babel.it
title:System Architect
tel;work:+39.06.91801075
tel;fax:+39.06.91612446
tel;cell:+393406580739
url:http://www.babel.it
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gmancuso@babel.it, gc@mnc.ch
Subject: Re: Performance problem with a table with 38928077 record
In-Reply-To: <4E8F16C6.7070606@babel.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox