Re: [PERFORM] How to troubleshoot high mem usage by postgres?
Chris ctlaj...@gmail.com writes: @Tom Lane: As I mentioned above I am not doing everything in a single transaction. However I do want to try your suggestion regarding getting a memory context map. But I'm afraid I don't know how to do what you are describing. How can I set the ulimit of postmaster? Depends on the script you are using to start the postmaster. One way is to call ulimit in the startup script right before it invokes the postmaster. However, if you have something like su - postgres -c postmaster ... then I'd try putting it in the postgres user's ~/.profile or ~/.bash_profile instead; the su is likely to reset such things. And does the postmaster stderr output go to the postgres log file? Also depends. Look at the startup script and see where it redirects postmaster's stderr to. You might have to modify the script --- some are known to send stderr to /dev/null :-( Sorry to be so vague, but different packagers have different ideas about how to do this. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Estimation issue with partitioned tables
All, I'm seeing in a production database two problems with query rowcount estimation: (1) Estimates for the number of rows in an outer join do not take into account any constraint exclusion (CE) in operation. (2) Row estimates do not take into account if the unique indexes on the child partitions are different from the master partition (the append node). This is often true, because the key to the master is ( key, ce column) and for the children is just ( key ). The result is that if you do a series of outer joins using the CE criterion against partitioned tables, the row estimates you get will be several orders of magnitude too high ... and the subsequent query plan far too pessimistic. Anyone else seeing this? Do any of the 9.0 patches address the above issues? --Josh Berkus -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] partitioned tables query not using indexes
However the same query against the base table when specifying the check constraint key in the where clause produces sequential scans: Does the master table have the same indexes as the slave partitions? --Josh Berkus -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] partitioned tables query not using indexes
On Sun, Feb 28, 2010 at 12:29:14PM -0800, Josh Berkus wrote: However the same query against the base table when specifying the check constraint key in the where clause produces sequential scans: Does the master table have the same indexes as the slave partitions? --Josh Berkus Does this help? I have an empty base table without indexes and partitions underneath that do have the index. I did not think that an index on the parent table did anything. Cheers, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [HACKERS] full text search index scan query plan changed in 8.4.2?
Xufei,
List changed to psql-performance, which is where this discussion belongs.
I am testing the index used by full text search recently.
I have install 8.3.9 and 8.4.2 separately.
In 8.3.9, the query plan is like:
postgres=# explain SELECT s.name as source , t.name as target FROM element as
s, element as t WHERE to_tsvector('testcfg',s.name) @@
to_tsquery('testcfg',replace(t.name,':','|'));
QUERY PLAN
Nested Loop (cost=0.01..259.92 rows=491 width=18)
- Seq Scan on element t (cost=0.00..13.01 rows=701 width=9)
- Index Scan using element_ftsidx_test on element s (cost=0.01..0.33
rows=1 width=9)
Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@
to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text,
'|'::text)))
(4 rows)
I have index: element_ftsidx_test gin (to_tsvector('testcfg'::regconfig,
name::text))
The same index and query in 8.4.2:
postgres=# explain SELECT s.name as source , t.name as target FROM element as
s, element as t WHERE to_tsvector('testcfg',s.name) @@
to_tsquery('testcfg',replace(t.name,':','|')) ;
QUERY PLAN
--
Nested Loop (cost=0.32..3123.51 rows=2457 width=18)
- Seq Scan on element t (cost=0.00..13.01 rows=701 width=9)
- Bitmap Heap Scan on element s (cost=0.32..4.36 rows=4 width=9)
Recheck Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@
to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text,
'|'::text)))
- Bitmap Index Scan on element_ftsidx_test (cost=0.00..0.32 rows=4
width=0)
Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text)
@@ to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text,
'|'::text)))
(6 rows)
Why the query plans are different and why? Thanks!
Because the row estimates changed, since 8.4 improved row estimation for
TSearch. The 2nd query is probably actually faster, no? If not, you
may need to increase your stats collection. Or at least show us a
VACUUM ANALYZE.
--Josh Berkus
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] partitioned tables query not using indexes
On 2/28/10 1:51 PM, Kenneth Marshall wrote: On Sun, Feb 28, 2010 at 12:29:14PM -0800, Josh Berkus wrote: However the same query against the base table when specifying the check constraint key in the where clause produces sequential scans: Does the master table have the same indexes as the slave partitions? --Josh Berkus Does this help? I have an empty base table without indexes and partitions underneath that do have the index. I did not think that an index on the parent table did anything. I'm not sure that it does, but try it and see is easier than reading the planner code. --Josh Berkus -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [HACKERS] full text search index scan query plan changed in 8.4.2?
Josh Berkus wrote:
Xufei,
List changed to psql-performance, which is where this discussion belongs.
I am testing the index used by full text search recently.
I have install 8.3.9 and 8.4.2 separately.
In 8.3.9, the query plan is like:
postgres=# explain SELECT s.name as source , t.name as target FROM element as s, element as t WHERE to_tsvector('testcfg',s.name) @@ to_tsquery('testcfg',replace(t.name,':','|')); QUERY PLAN
Nested Loop (cost=0.01..259.92 rows=491 width=18)
- Seq Scan on element t (cost=0.00..13.01 rows=701 width=9)
- Index Scan using element_ftsidx_test on element s (cost=0.01..0.33 rows=1 width=9)
Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@ to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text)))
(4 rows)
I have index: element_ftsidx_test gin (to_tsvector('testcfg'::regconfig,
name::text))
The same index and query in 8.4.2:
postgres=# explain SELECT s.name as source , t.name as target FROM element as s, element as t WHERE to_tsvector('testcfg',s.name) @@ to_tsquery('testcfg',replace(t.name,':','|')) ;QUERY PLAN --
Nested Loop (cost=0.32..3123.51 rows=2457 width=18)
- Seq Scan on element t (cost=0.00..13.01 rows=701 width=9)
- Bitmap Heap Scan on element s (cost=0.32..4.36 rows=4 width=9) Recheck Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@ to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text)))
- Bitmap Index Scan on element_ftsidx_test (cost=0.00..0.32 rows=4 width=0)
Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@
to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text)))
(6 rows)
Why the query plans are different and why? Thanks!
Because the row estimates changed, since 8.4 improved row estimation for
TSearch. The 2nd query is probably actually faster, no? If not, you
may need to increase your stats collection. Or at least show us a
VACUUM ANALYZE.
I'm sure you mean explain analyze :)
--
Postgresql php tutorials
http://www.designmagick.com/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Autovacuum Tuning advice
I have a very busy system that takes about 9 million inserts per day and each record gets updated at least once after the insert (all for the one same table), there are other tables that get hit but not as severely. As suspected I am having a problem with table bloat. Any advice on how to be more aggressive with autovacuum? I am using 8.4.1. My machine has 4 Intel Xeon 3000 MHz Processors with 8 GB of Ram. Currently I am using only defaults for autovac. shared_buffers = 768MB # min 128kB work_mem = 1MB # min 64kB maintenance_work_mem = 384MB #-- # AUTOVACUUM PARAMETERS #-- #autovacuum = on #log_autovacuum_min_duration = -1 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #autovacuum_vacuum_threshold = 50 #autovacuum_analyze_threshold = 50 #autovacuum_vacuum_scale_factor = 0.2 #autovacuum_analyze_scale_factor = 0.1 #autovacuum_freeze_max_age = 2 #autovacuum_vacuum_cost_delay = 20ms #autovacuum_vacuum_cost_limit = -1 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Autovacuum Tuning advice
On Sun, Feb 28, 2010 at 8:09 PM, Plugge, Joe R. jrplu...@west.com wrote: I have a very busy system that takes about 9 million inserts per day and each record gets updated at least once after the insert (all for the one same table), there are other tables that get hit but not as severely. As suspected I am having a problem with table bloat. Any advice on how to be more aggressive with autovacuum? I am using 8.4.1. My machine has 4 Intel Xeon 3000 MHz Processors with 8 GB of Ram. What kind of drive system do you have? That's far more important than CPU and RAM. Let's look at a two pronged attack. 1: What can you maybe do to reduce the number of updates for each row. if you do something like: update row set field1='xyz' where id=4; update row set field2='www' where id=4; And you can combine those updates, that's a big savings. Can you benefit from HOT updates by removing some indexes? Updating indexed fields can cost a fair bit more than updating indexed ones IF you have a 100% fill factor and therefore free room in each page for a few extra rows. 2: Vacuum tuning. Currently I am using only defaults for autovac. This one: #autovacuum_vacuum_cost_delay = 20ms is very high for a busy system with a powerful io subsystem. I run my production servers with 1ms to 4ms so they can keep up. Lastly there are some settings you can make per table for autovac you can look into (i.e. set cost_delay to 0 for this table), or you can turn off autovac for this one table and then run a regular vac with no cost_delay on it every minute or two. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
