Re: Performance problem with a table with 38928077 record

I clean all unused data, run VACUUM FULL and run REINDEX d,_object_perm.

Un my table, now i have:
----------
24089952

But the problem is the same.

Thanks

Il 07/10/2011 17:12, Giovanni Mancuso ha scritto:
> 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.

--

*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.