Re: Performance problem with a table with 38928077 record

Поиск
Список
Период
Сортировка
От Giovanni Mancuso
Тема Re: Performance problem with a table with 38928077 record
Дата
Msg-id 4E90668E.9010602@babel.it
обсуждение исходный текст
Ответ на Re: Performance problem with a table with 38928077 record  (Giovanni Mancuso <gmancuso@babel.it>)
Список pgsql-performance
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
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
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.
Вложения

В списке pgsql-performance по дате отправления:

Предыдущее
От: Ben Ciceron
Дата:
Сообщение: Re: PostgreSQL-9.0 Monitoring System to improve performance
Следующее
От: Leonardo Francalanci
Дата:
Сообщение: Re: : Performance Improvement Strategy