Il 07/10/2011 12:24, Guillaume Cottenceau ha scritto:
My Memory: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 # 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 max_connections = 50[2] you could report if your DB configuration is tuned/good 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
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 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. |
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@xxxxxxxx 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
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
