Thread: Performance problem with a table with 38928077 record

Performance problem with a table with 38928077 record

From

Giovanni Mancuso

Date:

07 October 2011, 10:12:25

Hi,

I have a problem with my postgres 8.2.

I Have an application that write ojbect (file, folder, ecc.) and another table that have account. This to tables are likend eith another tablenthat have a permissions foreach objects + accounts.

My structure is:

TABLE WITH USERS
# \d auth_accounts
                                Table "public.auth_accounts"
   Column   | Type   |                              Modifiers
------------+---------+----------------------------------------------------------------------
id         | integer | not null default nextval(('"auth_accounts_id_seq"'::text)::regclass)
login      | text    | not null
password   | text    | not null
first_name | text    |
last_name | text    |
email      | text    |
phone      | text    |
Indexes:
    "auth_accounts_pkey" PRIMARY KEY, btree (id)
    "auth_accounts_id_key" UNIQUE, btree (id)

TABLE WITH OBJECTS:
\d dm_object
                                           Table "public.dm_object"
    Column    |            Type             |                            Modifiers
--------------+-----------------------------+------------------------------------------------------------------
id           | integer                     | not null default nextval(('"dm_object_id_seq"'::text)::regclass)
name         | text                        | not null
summary      | text                        |
object_type | text                        |
create_date | timestamp without time zone |
object_owner | integer                     |
status       | smallint                    | not null
status_date | timestamp without time zone |
status_owner | integer                     |
version      | integer                     | not null default 1
reindex      | smallint                    | default 0
filesize     | numeric                     |
token        | text                        |
delete_date | date                        |
Indexes:
    "dm_object_id_key" UNIQUE, btree (id)
    "delete_date_index" btree (delete_date)
    "dm_object_object_type_idx" btree (object_type)
    "dm_object_search_key" btree (name, summary)
    "filesize_index" btree (filesize)
    "id_index" btree (id)
    "name_index" btree (name)
    "object_type_index" btree (object_type)
    "summary_index" btree (summary)

TABLE WITH PERMISSIONS:
docmgr=# \d dm_object_perm
   Table "public.dm_object_perm"
   Column   |   Type   | Modifiers
------------+----------+-----------
object_id | integer | not null
account_id | integer |
group_id   | integer |
bitset     | smallint |
Indexes:
    "account_id_index" btree (account_id)
    "bitset_index" btree (bitset)
    "dm_object_perm_group_id" btree (group_id)
    "dm_object_perm_id_key" btree (object_id)
    "idx_dm_object_perm_nulls" btree (bitset) WHERE bitset IS NULL
    "object_id_index" btree (object_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (object_id) REFERENCES dm_object(id)

If i count the records foreach tables i have:
select count(*) from dm_object;
count
-------
9778
(1 row)

select count(*) from auth_accounts;
count
-------
4334

select count(*) from dm_object_perm;
count
----------
38928077
(1 row)

The dm_object_perm have 38928077 of record.

If i run the "EXPLAIN ANALYZE" of "select *" in auth_accounts and dm_object i have good time:
docmgr=# explain analyze select * from auth_accounts;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on auth_accounts (cost=0.00..131.33 rows=4333 width=196) (actual time=20.000..200.000 rows=4334 loops=1)
Total runtime: 200.000 ms
(2 rows)

docmgr=# explain analyze select * from dm_object;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on dm_object (cost=0.00..615.78 rows=9778 width=411) (actual time=0.000..10.000 rows=9778 loops=1)
Total runtime: 10.000 ms
(2 rows)

If i run "explain analyze select * from dm_object_perm;" it goes on for many hours.

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.

how can I fix this?

Thanks

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.

Attachment

Re: Performance problem with a table with 38928077 record

From

Guillaume Cottenceau

Date:

07 October 2011, 10:24:27

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
[2] you could report if your DB configuration is tuned/good
[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

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

--
Guillaume Cottenceau

Re: Performance problem with a table with 38928077 record

From

Gregg Jaskiewicz

Date:

07 October 2011, 10:29:35

Do you need left join ?
Can you further normalize the tables? (to lower the I/O)
Can you upgrade to at least 8.3 ? It has huuge performance
improvements over 8.3.

Re: Performance problem with a table with 38928077 record

From

Cédric Villemain

Date:

07 October 2011, 12:59:56

2011/10/7 Giovanni Mancuso <gmancuso@babel.it>

Hi,

I have a problem with my postgres 8.2.

I Have an application that write ojbect (file, folder, ecc.) and another table that have account. This to tables are likend eith another tablenthat have a permissions foreach objects + accounts.

My structure is:

TABLE WITH USERS
# \d auth_accounts
                                Table "public.auth_accounts"
   Column   | Type   |                              Modifiers
------------+---------+----------------------------------------------------------------------
id         | integer | not null default nextval(('"auth_accounts_id_seq"'::text)::regclass)
login      | text    | not null
password   | text    | not null
first_name | text    |
last_name | text    |
email      | text    |
phone      | text    |
Indexes:
    "auth_accounts_pkey" PRIMARY KEY, btree (id)
    "auth_accounts_id_key" UNIQUE, btree (id)

TABLE WITH OBJECTS:
\d dm_object
                                           Table "public.dm_object"
    Column    |            Type             |                            Modifiers
--------------+-----------------------------+------------------------------------------------------------------
id           | integer                     | not null default nextval(('"dm_object_id_seq"'::text)::regclass)
name         | text                        | not null
summary      | text                        |
object_type | text                        |
create_date | timestamp without time zone |
object_owner | integer                     |
status       | smallint                    | not null
status_date | timestamp without time zone |
status_owner | integer                     |
version      | integer                     | not null default 1
reindex      | smallint                    | default 0
filesize     | numeric                     |
token        | text                        |
delete_date | date                        |
Indexes:
    "dm_object_id_key" UNIQUE, btree (id)
    "delete_date_index" btree (delete_date)
    "dm_object_object_type_idx" btree (object_type)
    "dm_object_search_key" btree (name, summary)
    "filesize_index" btree (filesize)
    "id_index" btree (id)
    "name_index" btree (name)
    "object_type_index" btree (object_type)
    "summary_index" btree (summary)

TABLE WITH PERMISSIONS:
docmgr=# \d dm_object_perm
   Table "public.dm_object_perm"
   Column   |   Type   | Modifiers
------------+----------+-----------
object_id | integer | not null
account_id | integer |
group_id   | integer |
bitset     | smallint |
Indexes:
    "account_id_index" btree (account_id)
    "bitset_index" btree (bitset)
    "dm_object_perm_group_id" btree (group_id)
    "dm_object_perm_id_key" btree (object_id)
    "idx_dm_object_perm_nulls" btree (bitset) WHERE bitset IS NULL
    "object_id_index" btree (object_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (object_id) REFERENCES dm_object(id)

If i count the records foreach tables i have:
select count(*) from dm_object;
count
-------
9778
(1 row)

select count(*) from auth_accounts;
count
-------
4334

select count(*) from dm_object_perm;
count
----------
38928077
(1 row)

The dm_object_perm have 38928077 of record.

If i run the "EXPLAIN ANALYZE" of "select *" in auth_accounts and dm_object i have good time:
docmgr=# explain analyze select * from auth_accounts;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on auth_accounts (cost=0.00..131.33 rows=4333 width=196) (actual time=20.000..200.000 rows=4334 loops=1)
Total runtime: 200.000 ms
(2 rows)

docmgr=# explain analyze select * from dm_object;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on dm_object (cost=0.00..615.78 rows=9778 width=411) (actual time=0.000..10.000 rows=9778 loops=1)
Total runtime: 10.000 ms
(2 rows)

If i run "explain analyze select * from dm_object_perm;" it goes on for many hours.

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.

how can I fix this?

once you've provided more informations as required by other people it should be easier to help you. What's duration do you expect your hardware to take to read 1GB ? (or 10GB ?)

Even without this 'slow' (really?) query Your must review your indexes usages: duplicate indexes are useless and reduce overall performance.
The first task here is to remove the duplicates.

Thanks
--

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.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Attachment

logo.gif

Re: Performance problem with a table with 38928077 record

From

Giovanni Mancuso

Date:

07 October 2011, 15:12:20

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)

Attachment

Re: Performance problem with a table with 38928077 record

From

Giovanni Mancuso

Date:

08 October 2011, 15:05:06

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)

Attachment

Re: Performance problem with a table with 38928077 record

From

Robert Haas

Date:

27 October 2011, 19:13:21

What is a bit strange about this is that you can do this:

On Fri, Oct 7, 2011 at 6:04 AM, Giovanni Mancuso <gmancuso@babel.it> wrote:

select count(*) from dm_object_perm;
count
----------
38928077
(1 row)

But not this:

If i run "explain analyze select * from dm_object_perm;" it goes on for many hours.

If I had to guess, I'd bet that the second one is trying to spool the resultset in memory someplace and that's driving the machine into swap. But that's just a shot in the dark. You might want to use tools like top, vmstat, iostat, free, etc. to see what the system is actually doing while this is running. I'd start the query up, let it run for 10 minutes or so, and then see whether the machine is CPU-bound or I/O-bound, and whether the amount of swap in use is growing.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Performance problem with a table with 38928077 record

From

Shaun Thomas

Date:

27 October 2011, 21:25:50

On 10/27/2011 02:13 PM, Robert Haas wrote:

> If I had to guess, I'd bet that the second one is trying to spool the
> resultset in memory someplace and that's driving the machine into
> swap.

That would be my guess too. SELECT * on a 40-million row table is a
*lot* different than getting the count, which throws away the rows once
it verifies they're valid.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email

Re: Performance problem with a table with 38928077 record

From

Tom Lane

Date:

27 October 2011, 22:19:59

Shaun Thomas <sthomas@peak6.com> writes:
> On 10/27/2011 02:13 PM, Robert Haas wrote:
>> If I had to guess, I'd bet that the second one is trying to spool the
>> resultset in memory someplace and that's driving the machine into
>> swap.

> That would be my guess too. SELECT * on a 40-million row table is a
> *lot* different than getting the count, which throws away the rows once
> it verifies they're valid.

But EXPLAIN ANALYZE throws away the rows too.  There's something odd
going on there, but the information provided is insufficient to tell
what.

            regards, tom lane