Re: FW: [PERFORM] how do functions affect query plan?
hi
i think the telegram_id's type should be integer.
please change telegram_id to numeric and try to run the the following sql.
the index should be used.
explain SELECT md.*
FROM measure_data md
where telegram_id in (trunc(66484.2),trunc(132362.1 ))
2014-05-15 17:28 GMT+09:00 changchao :
>
>
>
> > From: chang-c...@hotmail.com
> > To: pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] how do functions affect query plan?
> > Date: Thu, 15 May 2014 16:59:30 +0900
> >
> >
> >
> > Interestingly,adding type cast made postgresql wiser.
> > Anyone knows the reason?
> >
> > 1.no type cast
> > SELECT md.*
> > FROM measure_data md
> > where telegram_id in (trunc(66484.2),trunc(132362.1 ))
> >
> >
> > "Seq Scan on measure_data md (cost=0.00..459455.40 rows=205546
> width=28) (actual time=77.144..6458.870 rows=624 loops=1)"
> > " Filter: ((telegram_id)::numeric = ANY ('{66484,132362}'::numeric[]))"
> > " Rows Removed by Filter: 20553936"
> > "Total runtime: 6458.921 ms"
> >
> >
> > 2.type cast
> >
> > SELECT md.*
> > FROM measure_data md
> > where telegram_id in (trunc(66484.2)::int,trunc(132362.1 )::int)
> >
> > "Bitmap Heap Scan on measure_data md (cost=16.06..2618.86 rows=684
> width=28) (actual time=0.076..0.154 rows=624 loops=1)"
> > " Recheck Cond: (telegram_id = ANY ('{66484,132362}'::integer[]))"
> > " -> Bitmap Index Scan on index_measure_data_telegram_id
> (cost=0.00..15.88 rows=684 width=0) (actual time=0.065..0.065 rows=624
> loops=1)"
> > "Index Cond: (telegram_id = ANY ('{66484,132362}'::integer[]))"
> > "Total runtime: 0.187 ms"
> >
> >
> >
> >> From: chang-c...@hotmail.com
> >> To: david.g.johns...@gmail.com; pgsql-performance@postgresql.org
> >> Subject: Re: [PERFORM] how do functions affect query plan?
> >> Date: Thu, 15 May 2014 15:19:13 +0900
> >>
> >> Hi,David
> >>
> >> Seems that the root of evil is in the function(random,trunc),
> >> although I don't know why.
> >>
> >> Here is the comparison.
> >>
> >> 1.w/o function : index is wisely used.(Even without the limit 30 clause)
> >>
> >> explain analyze
> >> SELECT md.*
> >> FROM measure_data md
> >> where telegram_id in
> >> (
> >> SELECT 66484 + (132363-66484)/30 * i
> >> FROM generate_series(1,30) as s(i)
> >> limit 30
> >> )
> >> ;
> >>
> >> "Nested Loop (cost=10.01..39290.79 rows=10392 width=28) (actual
> time=0.079..3.490 rows=9360 loops=1)"
> >> " -> HashAggregate (cost=0.83..1.13 rows=30 width=4) (actual
> time=0.027..0.032 rows=30 loops=1)"
> >> " -> Limit (cost=0.00..0.45 rows=30 width=4) (actual time=0.013..0.020
> rows=30 loops=1)"
> >> " -> Function Scan on generate_series s (cost=0.00..15.00 rows=1000
> width=4) (actual time=0.011..0.016 rows=30 loops=1)"
> >> " -> Bitmap Heap Scan on measure_data md (cost=9.19..1306.20 rows=346
> width=28) (actual time=0.030..0.075 rows=312 loops=30)"
> >> " Recheck Cond: (telegram_id = ((66484 + (2195 * s.i"
> >> " -> Bitmap Index Scan on index_measure_data_telegram_id
> (cost=0.00..9.10 rows=346 width=0) (actual time=0.025..0.025 rows=312
> loops=30)"
> >> " Index Cond: (telegram_id = ((66484 + (2195 * s.i"
> >> "Total runtime: 3.714 ms"
> >>
> >>
> >> 2.when function is there: seq scan
> >>
> >> explain analyze
> >> SELECT md.*
> >> FROM measure_data md
> >> where telegram_id in
> >> (
> >> SELECT trunc((132363-66484) * random()) +66484
> >> FROM generate_series(1,30) as s(i)
> >> limit 30
> >> )
> >> ;
> >>
> >>
> >> "Hash Join (cost=1.65..490288.89 rows=10277280 width=28) (actual
> time=0.169..4894.847 rows=9360 loops=1)"
> >> " Hash Cond: ((md.telegram_id)::double precision =
> ((trunc((65879::double precision * random())) + 66484::double precision)))"
> >> " -> Seq Scan on measure_data md (cost=0.00..356682.60 rows=20554560
> width=28) (actual time=0.010..2076.932 rows=20554560 loops=1)"
> >> " -> Hash (cost=1.28..1.28 rows=30 width=8) (actual time=0.041..0.041
> rows=30 loops=1)"
> >> " Buckets: 1024 Batches: 1 Memory Usage: 2kB"
> >> " -> HashAggregate (cost=0.98..1.28 rows=30 width=8) (actual
> time=0.034..0.036 rows=30 loops=1)"
> >> " -> Limit (cost=0.00..0.60 rows=30 width=0) (actual time=0.016..0.026
> rows=30 loops=1)"
> >> " -> Function Scan on generate_series s (cost=0.00..20.00 rows=1000
> width=0) (actual time=0.015..0.023 rows=30 loops=1)"
> >> "Total runtime: 4895.239 ms"
> >>
> >>
> >>
> >>> Date: Wed, 14 May 2014 22:43:24 -0700
> >>> From: david.g.johns...@gmail.com
> >>> To: pgsql-performance@postgresql.org
> >>> Subject: Re: [PERFORM] how do functions affect query plan?
> >>>
> >>> 常超 wrote
> Hi,all
> I have a table to save received measure data.
>
>
> CREATE TABLE measure_data
> (
> id serial NOT NULL,
> telegram_id integer NOT NULL,
> measure_time timestamp without time zone NOT NULL,
> item_id integer NOT NULL,
> val double precision,
> CONSTRA
FW: [PERFORM] slow query on postgres 8.4
> explain analyze > select a.ID, a.provider, a.hostname, a.username, a.eventTimeStamp, > a.AIPGUID, a.submissionGUID, a.parentSubmissionGUID, a.sizeArchived, > a.addedContentString, a.addedContentSizesString, a.removedContentString, > a.removedContentSizesString, a.modifiedContentString, > a.modifiedContentSizesString, a.DISCRIMINATOR > from AIPModificationEvent a > where a.ID in (select MAX(b.ID) from AIPModificationEvent b where > b.parentSubmissionGUID > in >(select c.GUID from WorkflowProcessingEvent c where > c.DISCRIMINATOR='WorkflowCompleted' >and c.eventTimeStamp >= '2012-11-10 00:00:00' and > c.eventTimeStamp < '2012-11-11 00:00:00') >or b.submissionGUID in >(select c.GUID from WorkflowProcessingEvent c >where c.DISCRIMINATOR='WorkflowCompleted' and > c.eventTimeStamp >= '2012-11-10 00:00:00' and c.eventTimeStamp < > '2012-11-11 00:00:00') >group by b.AIPGUID) > limit 1000 offset 3000 Hi Maria, It appears to be doing a sort so that it can carry out the group by clause but the group by doesn't appear to be necessary as you're selecting the max(b.ID) after doing the group by. If you omit the group by then it will return more rows in that part of the query but the MAX(b.ID) will return 1 value regardless. Regards, Russell Keane. Registered name: In Practice Systems Ltd. Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ Registered Number: 1788577 Registered in England Visit our Internet Web site at www.inps.co.uk The information in this internet email is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is not authorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of INPS or any of its affiliates. If you are not the intended recipient please contact is.helpd...@inps.co.uk -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: Fw: [PERFORM] query total time im milliseconds
On 11/07/2011 2:26 AM, Radhya sahal wrote: long startTime = System.currentTimeMillis(); //execute query long executionTime = System.currentTimeMillis() - startTime; this executionTime is not an actual time for query , it includes time for access to postgresql server using JDBC The pg_stat_statements contrib module in PostgreSQL 8.4 and above might be able to do what you want. See the documentation here: http://www.postgresql.org/docs/9.0/static/pgstatstatements.html I don't think the core PostgreSQL server currently retains information about how long the last query executed ran for. I thought the PL/PgSQL "GET DIAGNOSTICS" statement might be able to find out how long the last query run within that PL/PgSQL function took, but it can't. So I think you're out of luck for now. PostgreSQL *CAN* log query durations to the server log, it just doesn't (AFAIK) offer any way to find out how long the last query took from SQL and doesn't keep that information after the statement finishes. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Fw: [PERFORM] query total time im milliseconds
- Forwarded Message From: Radhya sahal To: Samuel Gendler Sent: Sun, July 10, 2011 11:25:46 AM Subject: Re: [PERFORM] query total time im milliseconds Thank's long startTime = System.currentTimeMillis(); //execute query long executionTime = System.currentTimeMillis() - startTime; this executionTime is not an actual time for query , it includes time for access to postgresql server using JDBC From: Samuel Gendler To: Radhya sahal Cc: pgsql-performance group Sent: Sun, July 10, 2011 10:51:52 AM Subject: Re: [PERFORM] query total time im milliseconds On Sun, Jul 10, 2011 at 4:41 AM, Radhya sahal wrote: Dear all , >could any one help me? >when i use pgadmin to exceute a query it shows the total time for query .. >such as >(select * form table_name.)query total time is for example 100 ms >i want to know the command that can retentive the query total time >in millisecond > >if i connect with postgresql from java using JDBC >i need the query total time necessaryto use it in my project >i don't want run explian just query >thank's long startTime = System.currentTimeMillis(); //execute query long executionTime = System.currentTimeMillis() - startTime;
Fw: [PERFORM] Getting rid of a seq scan in query on a large table
- Forwarded Message -
>From: Denis de Bernardy
>To: Jens Hoffrichter
>Sent: Tuesday, June 28, 2011 12:59 AM
>Subject: Re: [PERFORM] Getting rid of a seq scan in query on a large table
>
>
>> Hash Cond: (posts.poster_id = posters.poster_id)
>
>> -> Seq Scan on posts (cost=0.00..11862.12 rows=112312
>> width=24) (actual time=0.019..60.092 rows=112312 loops=1)
>
>
>Unless I am mistaking, you've very few poster ids in there (since the two rows
>arguments are equal). The Postgres planner will identify this and just seq
>scan the whole thing instead of bothering to randomly access the rows one by
>one using the index. This looks like a case where you actually do not want it
>to use an index scan -- doing so will be slower.
>
>
>D
>
>
>
>
>
>
>
>>
>>From: Jens Hoffrichter
>>To: pgsql-performance@postgresql.org
>>Sent: Monday, June 27, 2011 2:46 PM
>>Subject: [PERFORM] Getting rid of a seq scan in query on a large table
>>
>>
>>Hi everyone,
>>
>>
>>I'm having trouble getting rid of a sequential scan on a table with roughly
>>120k entries it. Creation of an index on that particular column which
>>triggers the sequential scan doesn't do anything, VACUUM and ANALYZE has been
>>done on the table.
>>
>>
>>The table in question has the following definition:
>>
>>
>> Column | Type |
>>Modifiers
>>+--+--
>> post_id | bigint | not null default
>>nextval('posts_post_id_seq'::regclass)
>> forum_id | bigint | not null
>> threadlink | character varying(255) | not null
>> timestamp | timestamp with time zone | not null
>> poster_id | bigint |
>> thread_id | bigint | not null
>> subject | text | not null
>> text | text | not null
>> postername | character varying(255) |
>> internal_post_id | bigint | not null default
>>nextval('posts_internal_post_id_seq'::regclass)
>> internal_thread_id | bigint |
>>Indexes:
>> "posts_pkey" PRIMARY KEY, btree (internal_post_id)
>> "posts_forum_id_key" UNIQUE, btree (forum_id, post_id)
>> "idx_internal_thread_id" btree (internal_thread_id)
>> "idx_posts_poster_id" btree (poster_id)
>>Foreign-key constraints:
>> "posts_forum_id_fkey" FOREIGN KEY (forum_id) REFERENCES forums(forum_id)
>> "posts_internal_thread_id_fkey" FOREIGN KEY (internal_thread_id)
>>REFERENCES threads(internal_thread_id)
>> "posts_poster_id_fkey" FOREIGN KEY (poster_id) REFERENCES
>>posters(poster_id)
>>
>>
>>The query is this:
>>
>>
>>SELECT threads.internal_thread_id AS threads_internal_thread_id,
>>threads.forum_id AS threads_forum_id, threads.thread_id AS threads_thread_id,
>>threads.title AS threads_title, threads.poster_id AS threads_poster_id,
>>threads.postername AS threads_postername, threads.category AS
>>threads_category, threads.posttype AS threads_posttype
>>
>> FROM threads JOIN posts
>>ON threads.internal_thread_id = posts.internal_thread_id JOIN posters ON
>>posts.poster_id = posters.poster_id JOIN posters_groups AS posters_groups_1
>>ON posters.poster_id = posters_groups_1.poster_id JOIN groups ON
>>groups.group_id = posters_groups_1.group_id WHERE groups.group_id = 4 ORDER
>>BY posts.timestamp DESC;
>>
>>
>>The query plan (with an explain analyze) gives me the following:
>>
>>
>>
>>QUERY PLAN
>>--
>> Sort (cost=13995.93..14006.63 rows=4279 width=108) (actual
>>time=79.927..79.947 rows=165 loops=1)
>> Sort Key: posts."timestamp"
>> Sort Method: quicksort Memory: 50kB
>> -> Nested Loop (cost=6.97..13737.84 rows=4279 width=108) (actual
>>time=0.605..79.693 rows=165 loops=1)
>> -> Seq Scan on groups (cost=0.00..1.05 rows=1 width=8) (actual
>>time=0.013..0.014 rows=1 loops=1)
>> Filter: (group_id = 4)
>> -> Nested Loop (cost=6.97..13694.00 rows=4279 width=116) (actual
>>time=0.587..79.616 rows=165 loops=1)
>> -> Hash Join (cost=6.97..12343.10 rows=4279 width=24)
>>(actual time=0.568..78.230 rows=165 loops=1)
>> Hash Cond: (posts.poster_id = posters.poster_id)
>> -> Seq Scan on posts (cost=0.00..11862.12 rows=112312
>>width=24) (actual time=0.019..60.092 rows=112312 loops=1)
>> -> Hash
Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
My problem is, that in fact I don't know which tag to index since I'm running a web admin application where users can enter arbitrary queries. For a tag cloud, try this : - table tags ( tag_id, tag_name ) - table articles ( article_id ) - table articles_to_tags( article_id, tag_id ) now this is the classical approach, which doesn't work so well when you want to get an article that has several tags (tag intersection). So, materialize the list of tag_ids for each article in an INTEGER[] array in the articles table, kept up to date with triggers. Create a gist index on that, and use indexed array vs array operators. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Hi all Thank you to all who answered: That worked: CREATE INDEX planet_osm_point_tags_amenity ON planet_osm_point ((tags->'amenity')) WHERE (tags->'amenity') IS NOT NULL; My problem is, that in fact I don't know which tag to index since I'm running a web admin application where users can enter arbitrary queries. Yours, Stefan 2011/5/25 Pierre C : >> You wrote >>> >>> Try to create a btree index on "(bench_hstore->bench_id) WHERE >>> (bench_hstore->bench_id) IS NOT NULL". >> >> What do you mean exactly? >> => CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps) WHERE >> ??? IS NOT NULL; >> >> My table's def is: >>> >>> CREATE TABLE myhstore ( id bigint PRIMARY KEY, kvps hstore NOT NULL ); >> >> So I'm doing something like: >> CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps); > > Hello ; > > I meant a plain old btree index like this : > > CREATE INDEX foo ON myhstore((kvps->'yourkeyname')) WHERE > (kvps->'yourkeyname') IS NOT NULL; > > The idea is that : > > - The reason to use hstore is to have an arbitrary number of keys and use > the keys you want, not have a fixed set of columns like in a table > - Therefore, no hstore key is present in all rows (if it was, you'd make > it a table column, and maybe index it) > - You'll probably only want to index some of the keys/values (avoiding to > index values that contain serialized data or other stuff that never > appears in a WHERE clause) > > So, for each key that corresponds to a searchable attribute, I'd use a > conditional index on that key, which only indexes the relevant rows. For > keys that never appear in a WHERE, no index is needed. > > gist is good if you want the intersecton of a hstore with another one (for > instance), btree is good if you want simple search or range search. > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
On Wed, May 25, 2011 at 11:59 AM, Pierre C wrote: >> You wrote >>> >>> Try to create a btree index on "(bench_hstore->bench_id) WHERE >>> (bench_hstore->bench_id) IS NOT NULL". >> >> What do you mean exactly? >> => CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps) WHERE >> ??? IS NOT NULL; >> >> My table's def is: >>> >>> CREATE TABLE myhstore ( id bigint PRIMARY KEY, kvps hstore NOT NULL ); >> >> So I'm doing something like: >> CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps); > > Hello ; > > I meant a plain old btree index like this : > > CREATE INDEX foo ON myhstore((kvps->'yourkeyname')) WHERE > (kvps->'yourkeyname') IS NOT NULL; > > The idea is that : > > - The reason to use hstore is to have an arbitrary number of keys and use > the keys you want, not have a fixed set of columns like in a table > - Therefore, no hstore key is present in all rows (if it was, you'd make > it a table column, and maybe index it) > - You'll probably only want to index some of the keys/values (avoiding to > index values that contain serialized data or other stuff that never > appears in a WHERE clause) > > So, for each key that corresponds to a searchable attribute, I'd use a > conditional index on that key, which only indexes the relevant rows. For > keys that never appear in a WHERE, no index is needed. > > gist is good if you want the intersecton of a hstore with another one (for > instance), btree is good if you want simple search or range search. +1 on this approach. it works really well (unless, of course, you need 50 indexes...) merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
You wrote Try to create a btree index on "(bench_hstore->bench_id) WHERE (bench_hstore->bench_id) IS NOT NULL". What do you mean exactly? => CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps) WHERE ??? IS NOT NULL; My table's def is: CREATE TABLE myhstore ( id bigint PRIMARY KEY, kvps hstore NOT NULL ); So I'm doing something like: CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps); Hello ; I meant a plain old btree index like this : CREATE INDEX foo ON myhstore((kvps->'yourkeyname')) WHERE (kvps->'yourkeyname') IS NOT NULL; The idea is that : - The reason to use hstore is to have an arbitrary number of keys and use the keys you want, not have a fixed set of columns like in a table - Therefore, no hstore key is present in all rows (if it was, you'd make it a table column, and maybe index it) - You'll probably only want to index some of the keys/values (avoiding to index values that contain serialized data or other stuff that never appears in a WHERE clause) So, for each key that corresponds to a searchable attribute, I'd use a conditional index on that key, which only indexes the relevant rows. For keys that never appear in a WHERE, no index is needed. gist is good if you want the intersecton of a hstore with another one (for instance), btree is good if you want simple search or range search. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Salut Pierre You wrote > Try to create a btree index on "(bench_hstore->bench_id) WHERE > (bench_hstore->bench_id) IS NOT NULL". What do you mean exactly? => CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps) WHERE ??? IS NOT NULL; My table's def is: > CREATE TABLE myhstore ( id bigint PRIMARY KEY, kvps hstore NOT NULL ); So I'm doing something like: CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps); Stefan 2011/5/23 Pierre C : > >> Hi Merlin >> >> The analyze command gave the following result: >> >> On the KVP table: >> Index Scan using kvpidx on bench_kvp (cost=0.00..8.53 rows=1 width=180) >> (actual time=0.037..0.038 rows=1 loops=1) >> Index Cond: (bench_id = '20_20'::text) >> Total runtime: 0.057 ms >> >> And on the Hstore table: >> Bitmap Heap Scan on bench_hstore (cost=32.22..3507.54 rows=1000 width=265) >> (actual time=145.040..256.173 rows=1 loops=1) >> Recheck Cond: (bench_hstore @> '"bench_id"=>"20_20"'::hstore) >> -> Bitmap Index Scan on hidx (cost=0.00..31.97 rows=1000 width=0) (actual >> time=114.748..114.748 rows=30605 loops=1) >> Index Cond: (bench_hstore @> '"bench_id"=>"20_20"'::hstore) >> Total runtime: 256.211 ms >> >> For Hstore I'm using a GIST index. >> > > Try to create a btree index on "(bench_hstore->bench_id) WHERE > (bench_hstore->bench_id) IS NOT NULL". > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
On Tue, May 17, 2011 at 11:10 AM, wrote: > For Hstore I'm using a GIST index. I would have thought that GIN would be a better choice for this workload. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Hi Merlin The analyze command gave the following result: On the KVP table: Index Scan using kvpidx on bench_kvp (cost=0.00..8.53 rows=1 width=180) (actual time=0.037..0.038 rows=1 loops=1) Index Cond: (bench_id = '20_20'::text) Total runtime: 0.057 ms And on the Hstore table: Bitmap Heap Scan on bench_hstore (cost=32.22..3507.54 rows=1000 width=265) (actual time=145.040..256.173 rows=1 loops=1) Recheck Cond: (bench_hstore @> '"bench_id"=>"20_20"'::hstore) -> Bitmap Index Scan on hidx (cost=0.00..31.97 rows=1000 width=0) (actual time=114.748..114.748 rows=30605 loops=1) Index Cond: (bench_hstore @> '"bench_id"=>"20_20"'::hstore) Total runtime: 256.211 ms For Hstore I'm using a GIST index. Try to create a btree index on "(bench_hstore->bench_id) WHERE (bench_hstore->bench_id) IS NOT NULL". -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Hi Merlin The analyze command gave the following result: On the KVP table: Index Scan using kvpidx on bench_kvp (cost=0.00..8.53 rows=1 width=180) (actual time=0.037..0.038 rows=1 loops=1) Index Cond: (bench_id = '20_20'::text) Total runtime: 0.057 ms And on the Hstore table: Bitmap Heap Scan on bench_hstore (cost=32.22..3507.54 rows=1000 width=265) (actual time=145.040..256.173 rows=1 loops=1) Recheck Cond: (bench_hstore @> '"bench_id"=>"20_20"'::hstore) -> Bitmap Index Scan on hidx (cost=0.00..31.97 rows=1000 width=0) (actual time=114.748..114.748 rows=30605 loops=1) Index Cond: (bench_hstore @> '"bench_id"=>"20_20"'::hstore) Total runtime: 256.211 ms For Hstore I'm using a GIST index. Table analysis returned no message. Michel Von: Merlin Moncure mmonc...@gmail.com An: Stefan Keller cc: pgsql-performance@postgresql.org Datum: 16. Mai 2011 15:47 Betreff: Re: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation) Merlin Moncure hstore is not really designed for large-ish sets like that. merlin 2011/5/16 Stefan Keller : > Hoi Michel > > Hast du die EXPLAIN ANALYZE information? > > LG, Stefan > > > -- Forwarded message -- > From: Craig Ringer > Date: 2011/5/16 > Subject: Re: [PERFORM] KVP table vs. hstore - hstore performance (Was: > Postgres NoSQL emulation) > To: Stefan Keller > Cc: pgsql-performance@postgresql.org > > > On 14/05/11 18:10, Stefan Keller wrote: >> Hi, >> >> I am conducting a benchmark to compare KVP table vs. hstore and got >> bad hstore performance results when the no. of records is greater than >> about 500'000. >> >> CREATE TABLE kvp ( id SERIAL PRIMARY KEY, key text NOT NULL, value text ); >> -- with index on key >> CREATE TABLE myhstore ( id SERIAL PRIMARY KEY, obj hstore NOT NULL ); >> -- with GIST index on obj >> >> Does anyone have experience with that? > > What are your queries? > > What does EXPLAIN ANALYZE report on those queries? > > Did you ANALYZE your tables after populating them? > > -- > Craig Ringer > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] Queries becoming slow under heavy load
Excellent! And you learned a bit more about how to monitor your server while you were at it. Win win! On Thu, Feb 3, 2011 at 10:40 AM, Anne Rosset wrote: > Thanks to all of you who replied and pointed NFS as a potential > culprit. > Our issue was that pgsql's temp dir (pgsql_tmp) was set to the default > value ( $PSQL_DIR/base/pgsql_tmp/) which was located in NFS. > Moving the temp dir to local disk got us a huge improvement. > > Anne > > -Original Message- > From: Shaun Thomas [mailto:stho...@peak6.com] > Sent: Friday, January 28, 2011 7:31 AM > To: Anne Rosset > Cc: pgsql-performance@postgresql.org > Subject: Re: FW: [PERFORM] Queries becoming slow under heavy load > > On 01/27/2011 11:12 PM, Anne Rosset wrote: > >> Thanks for your response. >> We are over NFS for our storage ... > > NFS? I'm not sure you know this, but NFS has major locking issues that > would make it a terrible candidate for hosting a database. > >> and it's not until around the 221 second mark that we see catch it > consuming CPU: >> >> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND >> 7090 root 25 0 689m 399m 10m R 93.4 5.0 3872:07 java >> 28312 postgres 16 0 396m 225m 204m R 5.7 2.8 0:51.52 > postmaster<- here >> 3391 root 15 0 29056 2348 1424 R 1.9 0.0 0:00.01 top >> 4297 root 16 0 10228 740 632 D 0.0 0.0 12:53.66 > hald-addon-stor >> 26885 httpd 15 0 2263m 1.5g 16m R 0.0 19.0 0:00.01 java >> >> Note that the load average is fine during this timeframe, ~4 out of 8, > so plenty of CPU. > > Please listen to us. We asked you to use sar, or iostat, to tell us how > much the disk IO is being utilized. From your other screenshots, there > were at least two other PG processes that were running and could have > been thrashing the disk or locking tables your "slow" query needed. If > it's waiting for disk IO, the CPU will remain low until it gets what it > needs. > > Not everything is about the CPU. Especially now that we know your DB is > running on top of NFS. > >> Further, or worse yet, this same behavior expands out to multiple >> processes, producing a true "back up". It can look something like >> this. Notice the 0% cpu consumption: >> >> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND >> 7090 root 22 0 689m 399m 10m R 91.1 5.0 3874:32 java >> 4139 root 15 0 29080 2344 1424 R 1.9 0.0 0:00.01 top >> 1555 postgres 16 0 474m 258m 162m D 0.0 3.2 0:17.32 > postmaster >> 1846 postgres 16 0 474m 285m 189m D 0.0 3.6 0:47.43 > postmaster >> 2713 postgres 16 0 404m 202m 179m D 0.0 2.5 0:33.54 > postmaster >> 2801 postgres 16 0 391m 146m 131m D 0.0 1.8 0:04.48 > postmaster >> 2804 postgres 16 0 419m 172m 133m D 0.0 2.2 0:09.41 > postmaster >> 2825 postgres 16 0 473m 142m 49m D 0.0 1.8 0:04.12 > postmaster > > Yes. And they could all be waiting for IO. Or NFS locking is blocking > the reads. Or... what is that Java app doing? We don't know the state of > your IO, and when you have 0% or very low CPU usage, you either have > locking contention or you're being IO starved. > > And what queries are these connections performing? You can check it by > getting the contents of the pg_stat_activity system view. If they're > selecting and still "slow", compare that against the iostat or sar > results. For instance, here's an IOSTAT of our system: > > iostat -dmx dm-9 1 > > Linux 2.6.18-92.el5 (oslchi6pedb1) 01/28/2011 > > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz > avgqu-sz await svctm %util > dm-9 0.00 0.00 125.46 227.78 4.95 0.89 33.88 > 0.08 0.19 0.08 2.91 > > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz > avgqu-sz await svctm %util > dm-9 0.00 0.00 5.00 0.00 0.04 0.00 14.40 > 0.05 10.60 10.60 5.30 > > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz > avgqu-sz await svctm %util > dm-9 0.00 0.00 2.00 0.00 0.02 0.00 16.00 > 0.01 7.00 7.00 1.40 > > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz > avgqu-sz await svctm %util > dm-9 0.00 0.00 4.00 1184.00 0.04 4.62 8.04 > 27.23 11.73 0.06 6.80 > > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz > avgqu-sz await svctm %util > dm-9 0.00
Re: FW: [PERFORM] Queries becoming slow under heavy load
Thanks to all of you who replied and pointed NFS as a potential culprit. Our issue was that pgsql's temp dir (pgsql_tmp) was set to the default value ( $PSQL_DIR/base/pgsql_tmp/) which was located in NFS. Moving the temp dir to local disk got us a huge improvement. Anne -Original Message- From: Shaun Thomas [mailto:stho...@peak6.com] Sent: Friday, January 28, 2011 7:31 AM To: Anne Rosset Cc: pgsql-performance@postgresql.org Subject: Re: FW: [PERFORM] Queries becoming slow under heavy load On 01/27/2011 11:12 PM, Anne Rosset wrote: > Thanks for your response. > We are over NFS for our storage ... NFS? I'm not sure you know this, but NFS has major locking issues that would make it a terrible candidate for hosting a database. > and it's not until around the 221 second mark that we see catch it consuming CPU: > >PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND > 7090 root 25 0 689m 399m 10m R 93.4 5.0 3872:07 java > 28312 postgres 16 0 396m 225m 204m R 5.7 2.8 0:51.52 postmaster<- here > 3391 root 15 0 29056 2348 1424 R 1.9 0.0 0:00.01 top > 4297 root 16 0 10228 740 632 D 0.0 0.0 12:53.66 hald-addon-stor > 26885 httpd 15 0 2263m 1.5g 16m R 0.0 19.0 0:00.01 java > > Note that the load average is fine during this timeframe, ~4 out of 8, so plenty of CPU. Please listen to us. We asked you to use sar, or iostat, to tell us how much the disk IO is being utilized. From your other screenshots, there were at least two other PG processes that were running and could have been thrashing the disk or locking tables your "slow" query needed. If it's waiting for disk IO, the CPU will remain low until it gets what it needs. Not everything is about the CPU. Especially now that we know your DB is running on top of NFS. > Further, or worse yet, this same behavior expands out to multiple > processes, producing a true "back up". It can look something like > this. Notice the 0% cpu consumption: > >PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND > 7090 root 22 0 689m 399m 10m R 91.1 5.0 3874:32 java > 4139 root 15 0 29080 2344 1424 R 1.9 0.0 0:00.01 top > 1555 postgres 16 0 474m 258m 162m D 0.0 3.2 0:17.32 postmaster > 1846 postgres 16 0 474m 285m 189m D 0.0 3.6 0:47.43 postmaster > 2713 postgres 16 0 404m 202m 179m D 0.0 2.5 0:33.54 postmaster > 2801 postgres 16 0 391m 146m 131m D 0.0 1.8 0:04.48 postmaster > 2804 postgres 16 0 419m 172m 133m D 0.0 2.2 0:09.41 postmaster > 2825 postgres 16 0 473m 142m 49m D 0.0 1.8 0:04.12 postmaster Yes. And they could all be waiting for IO. Or NFS locking is blocking the reads. Or... what is that Java app doing? We don't know the state of your IO, and when you have 0% or very low CPU usage, you either have locking contention or you're being IO starved. And what queries are these connections performing? You can check it by getting the contents of the pg_stat_activity system view. If they're selecting and still "slow", compare that against the iostat or sar results. For instance, here's an IOSTAT of our system: iostat -dmx dm-9 1 Linux 2.6.18-92.el5 (oslchi6pedb1) 01/28/2011 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 125.46 227.78 4.95 0.8933.88 0.080.19 0.08 2.91 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 5.00 0.00 0.04 0.0014.40 0.05 10.60 10.60 5.30 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 2.00 0.00 0.02 0.0016.00 0.017.00 7.00 1.40 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 4.00 1184.00 0.04 4.62 8.04 27.23 11.73 0.06 6.80 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 11.00 847.00 0.09 3.31 8.10 29.31 49.65 0.79 67.90 That last column, %util, effectively tells us how saturated the controller is. If the percentage is high, it's really working hard to supply the data we're asking for, or trying to write. If it's low, we're probably working from memory cache, or getting less requests. There have been times our queries are "slow" and when we check this stat, it's often at or above 90%, sometimes for minutes at a time. That's almost always a clear indicator you have IO contention. Queries can't work without the data they need
Re: FW: [PERFORM] Queries becoming slow under heavy load
Shaun Thomas wrote: On 01/27/2011 11:12 PM, Anne Rosset wrote: Thanks for your response. We are over NFS for our storage ... NFS? I'm not sure you know this, but NFS has major locking issues that would make it a terrible candidate for hosting a database. That depends on the implementation. Vendor supported NAS, running NFS3 or NFS4 should be OK. There are other databases that can use it, too. Some databases even have a built-in NFS client. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] Queries becoming slow under heavy load
On 01/27/2011 11:12 PM, Anne Rosset wrote: > Thanks for your response. > We are over NFS for our storage ... NFS? I'm not sure you know this, but NFS has major locking issues that would make it a terrible candidate for hosting a database. > and it's not until around the 221 second mark that we see catch it consuming > CPU: > >PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND > 7090 root 25 0 689m 399m 10m R 93.4 5.0 3872:07 java > 28312 postgres 16 0 396m 225m 204m R 5.7 2.8 0:51.52 postmaster<- > here > 3391 root 15 0 29056 2348 1424 R 1.9 0.0 0:00.01 top > 4297 root 16 0 10228 740 632 D 0.0 0.0 12:53.66 hald-addon-stor > 26885 httpd 15 0 2263m 1.5g 16m R 0.0 19.0 0:00.01 java > > Note that the load average is fine during this timeframe, ~4 out of 8, so > plenty of CPU. Please listen to us. We asked you to use sar, or iostat, to tell us how much the disk IO is being utilized. From your other screenshots, there were at least two other PG processes that were running and could have been thrashing the disk or locking tables your "slow" query needed. If it's waiting for disk IO, the CPU will remain low until it gets what it needs. Not everything is about the CPU. Especially now that we know your DB is running on top of NFS. > Further, or worse yet, this same behavior expands out to multiple processes, > producing a true "back up". It can look > something like this. Notice the 0% cpu consumption: > >PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND > 7090 root 22 0 689m 399m 10m R 91.1 5.0 3874:32 java > 4139 root 15 0 29080 2344 1424 R 1.9 0.0 0:00.01 top > 1555 postgres 16 0 474m 258m 162m D 0.0 3.2 0:17.32 postmaster > 1846 postgres 16 0 474m 285m 189m D 0.0 3.6 0:47.43 postmaster > 2713 postgres 16 0 404m 202m 179m D 0.0 2.5 0:33.54 postmaster > 2801 postgres 16 0 391m 146m 131m D 0.0 1.8 0:04.48 postmaster > 2804 postgres 16 0 419m 172m 133m D 0.0 2.2 0:09.41 postmaster > 2825 postgres 16 0 473m 142m 49m D 0.0 1.8 0:04.12 postmaster Yes. And they could all be waiting for IO. Or NFS locking is blocking the reads. Or... what is that Java app doing? We don't know the state of your IO, and when you have 0% or very low CPU usage, you either have locking contention or you're being IO starved. And what queries are these connections performing? You can check it by getting the contents of the pg_stat_activity system view. If they're selecting and still "slow", compare that against the iostat or sar results. For instance, here's an IOSTAT of our system: iostat -dmx dm-9 1 Linux 2.6.18-92.el5 (oslchi6pedb1) 01/28/2011 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 125.46 227.78 4.95 0.8933.88 0.080.19 0.08 2.91 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 5.00 0.00 0.04 0.0014.40 0.05 10.60 10.60 5.30 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 2.00 0.00 0.02 0.0016.00 0.01 7.00 7.00 1.40 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 4.00 1184.00 0.04 4.62 8.04 27.23 11.73 0.06 6.80 Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await svctm %util dm-9 0.00 0.00 11.00 847.00 0.09 3.31 8.10 29.31 49.65 0.79 67.90 That last column, %util, effectively tells us how saturated the controller is. If the percentage is high, it's really working hard to supply the data we're asking for, or trying to write. If it's low, we're probably working from memory cache, or getting less requests. There have been times our queries are "slow" and when we check this stat, it's often at or above 90%, sometimes for minutes at a time. That's almost always a clear indicator you have IO contention. Queries can't work without the data they need to return your results. Sending us more CPU charts isn't going to help us in helping you. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] Queries becoming slow under heavy load
Scott, Thanks for your response. We are over NFS for our storage ... Here is what we see during our performance testing: This is about 7 seconds after the query was sent to postgres: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 7090 root 25 0 689m 399m 10m R 89.9 5.0 3868:44 java 1846 postgres 16 0 474m 198m 103m R 75.2 2.5 0:28.69 postmaster 2170 postgres 15 0 391m 203m 188m R 44.0 2.6 0:17.63 postmaster 2555 httpd 18 0 298m 15m 4808 R 22.0 0.2 0:00.12 httpd 2558 root 15 0 29056 2324 1424 R 1.8 0.0 0:00.01 top 1207 httpd 15 0 337m 20m 7064 R 0.0 0.3 0:00.69 httpd 28312 postgres 16 0 396m 183m 162m D 0.0 2.3 0:50.82 postmaster < this is the query here Notice the 0% CPU, also, notice the 183m RES memory. Ten seconds later: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 7090 root 25 0 689m 399m 10m R 92.9 5.0 3868:53 java 2657 root 15 0 29056 2328 1424 R 1.9 0.0 0:00.01 top 28312 postgres 16 0 396m 184m 162m D 0.0 2.3 0:50.84 postmaster < here Ten seconds after that: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 7090 root 25 0 689m 399m 10m R 88.7 5.0 3869:02 java 1845 postgres 16 0 473m 223m 127m D 22.6 2.8 0:26.39 postmaster 2412 httpd 15 0 2245m 1.4g 16m R 18.9 17.8 0:02.48 java 966 postgres 15 0 395m 242m 221m D 0.0 3.0 1:02.31 postmaster 2680 root 15 0 29056 2336 1424 R 0.0 0.0 0:00.01 top 28312 postgres 16 0 396m 184m 163m D 0.0 2.3 0:50.85 postmaster <--- here etc and it's not until around the 221 second mark that we see catch it consuming CPU: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 7090 root 25 0 689m 399m 10m R 93.4 5.0 3872:07 java 28312 postgres 16 0 396m 225m 204m R 5.7 2.8 0:51.52 postmaster <- here 3391 root 15 0 29056 2348 1424 R 1.9 0.0 0:00.01 top 4297 root 16 0 10228 740 632 D 0.0 0.0 12:53.66 hald-addon-stor 26885 httpd 15 0 2263m 1.5g 16m R 0.0 19.0 0:00.01 java Note that the load average is fine during this timeframe, ~4 out of 8, so plenty of CPU. Looks like this is true "halting". Further, or worse yet, this same behavior expands out to multiple processes, producing a true "back up". It can look something like this. Notice the 0% cpu consumption: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 7090 root 22 0 689m 399m 10m R 91.1 5.0 3874:32 java 4139 root 15 0 29080 2344 1424 R 1.9 0.0 0:00.01 top 1555 postgres 16 0 474m 258m 162m D 0.0 3.2 0:17.32 postmaster 1846 postgres 16 0 474m 285m 189m D 0.0 3.6 0:47.43 postmaster 2713 postgres 16 0 404m 202m 179m D 0.0 2.5 0:33.54 postmaster 2801 postgres 16 0 391m 146m 131m D 0.0 1.8 0:04.48 postmaster 2804 postgres 16 0 419m 172m 133m D 0.0 2.2 0:09.41 postmaster 2825 postgres 16 0 473m 142m 49m D 0.0 1.8 0:04.12 postmaster Thanks for any additional explanation/advice, Anne -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Wednesday, January 26, 2011 8:19 PM To: Anne Rosset Cc: pgsql-performance@postgresql.org Subject: Re: FW: [PERFORM] Queries becoming slow under heavy load On Wed, Jan 26, 2011 at 9:04 AM, Anne Rosset wrote: PLEASE post just the settings you changed. I'm not searching through a list that big for the interesting bits. > Today we did more analysis and observed postgress processes that > continually reported status 'D' in top. Full stop. The most likely problem here is that the query is now hitting the disks and waiting. If you have 1 disk and two users, the access speed will drop by factors, usually much higher than 2. To put it very simply, you need as many mirror pairs in your RAID-10 or as many disks in your RAID5 or RAID 6 as you have users reading the disk drives. If you're writing you need more and more disks too. Mediating this issue we find things like SSD cache in ZFS or battery backed RAID controllers. They allow the reads and writes to be streamlined quite a bit to the spinning disks, making it appear the RAID array underneath it was much faster, had better access, and all the sectors were near each other. To an extent. If you have the answer to the previous poster's question "can you tell us what sort of IO you have (sata, scsi, raid, # of disks, etc)." you should provi
Re: FW: [PERFORM] Queries becoming slow under heavy load
On Wed, Jan 26, 2011 at 10:16 AM, Shaun Thomas wrote: > Worse however, is your checkpoints. Lord. Increase checkpoint_segments to > *at least* 20, and increase your checkpoint_completion_target to 0.7 or 0.8. > Check your logs for checkpoint warnings, and I'll bet it's constantly > complaining about increasing your checkpoint segments. Every checkpoint not > started by the scheduled system risks a checkpoint spike, which can flood > your system with IO regardless of which queries are running. That kind of IO > storm will ruin your performance, and with only 3 checkpoint segments on a > busy database, are probably happening constantly. To Shaun: Unless she's not write bound but read bound. We can't tell because we haven't seen the queries. We haven't seen the output of iostat or vmstat. To Anne: Another tool to recommend is sar. it's part of the sysstat package on debian / ubuntu / rhel. you have to enable it in various ways, it'll tell you when you try to run it after installing it. It allows you to look back over the last 7 days, 5 minutes at a time, to see the trends on your servers. Very useful stuff and easy to graph in a spreadsheet or web page. Or just read it. For instance, here's the output of sar on the data drive of a slave (read only) server under slony replication. sar -d -f sa25|grep "02:[01234].:.. AM.\+dev251-1" Linux 2.6.32-27-server () 01/25/2011 _x86_64_(16 CPU) 12:00:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 09:45:01 AM dev251-1481.21 6981.74 1745.82 18.14 4.86 10.10 1.57 75.65 09:55:01 AM dev251-1620.16 28539.52 2135.67 49.46 5.25 8.47 1.22 75.42 10:05:01 AM dev251-1 1497.16 29283.95 1898.94 20.83 13.89 9.28 0.64 96.52 10:15:01 AM dev251-1 1040.47 17123.89 2286.10 18.66 8.89 8.54 0.87 90.49 10:25:01 AM dev251-1562.97 8802.77 1515.50 18.33 4.84 8.60 1.41 79.57 Let me interpret for ya, in case it's not obvious. IO Utilization runs from about 50% to about 90%. when it's at 90% we are running 700 to 1000 tps, reading at a maximum of 15MB a second and writing at a paltry 1M or so a second. Average wait stays around 10ms. If we use sar -u from the same time period, we cna match up iowait to this chart and see if we were really waiting on IO or not. 12:00:01 AM CPU %user %nice %system %iowait%steal %idle 09:45:01 AM all 47.44 0.00 5.20 4.94 0.00 42.42 09:55:01 AM all 46.42 0.00 5.63 5.77 0.00 42.18 10:05:01 AM all 48.64 0.00 6.35 11.87 0.00 33.15 10:15:01 AM all 46.94 0.00 5.79 8.81 0.00 38.46 10:25:01 AM all 48.68 0.00 5.58 5.42 0.00 40.32 We can see that we have at peak, 11% of our CPU power is waiting behind IO. We have 16 CPUs, so each one is 100/16 or 6.25% of the total. So at 11% we have two cores on hold the whole time basically. In real life on this machine we have ALL cpus waiting about 11% of the time across the board. But the math comes out the same. We're waiting on IO. Here's a heavy db server, lots of ram, same time period. sdh is one of a large number of disks in a RAID-10 array. md17 is that RAID-10 array (actually the RAID0 at the top of a bunch of RAID-1s I still don't trust linux's RAID-10 implementation). 12:00:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 09:45:01 sdh 5.86 5.65158.87 28.08 0.21 35.07 3.36 1.97 09:45:01 md17253.78168.69 1987.64 8.50 0.00 0.00 0.00 0.00 09:55:01 sdh 5.48 5.65134.99 25.68 0.16 30.00 3.31 1.81 09:55:01 md17215.13157.56 1679.67 8.54 0.00 0.00 0.00 0.00 10:05:01 sdh 4.37 5.39106.53 25.58 0.09 21.61 3.57 1.56 10:05:01 md17170.81133.76 1334.43 8.60 0.00 0.00 0.00 0.00 10:15:01 sdh 6.16 5.37177.25 29.64 0.25 40.95 3.38 2.08 10:15:01 md17280.63137.88 2206.95 8.36 0.00 0.00 0.00 0.00 10:25:01 sdh 4.52 3.72116.41 26.56 0.09 20.64 3.58 1.62 10:25:01 md17187.65107.59 1470.88 8.41 0.00 0.00 0.00 0.00 (Note that md devices do not show values for %util, svctm or await hence the need for sdh) This db fits the data set in ram, the other machine doesn't. It had a RAID controller, but that caught fire, and burned down. The next caught fire, burned down, and fell into the swamp. It now has a straight up SAS controller with no caching. Numbers were even better when it had a caching RAID controller, but I got tired of
Re: FW: [PERFORM] Queries becoming slow under heavy load
On Wed, Jan 26, 2011 at 9:04 AM, Anne Rosset wrote: PLEASE post just the settings you changed. I'm not searching through a list that big for the interesting bits. > Today we did more analysis and observed postgress processes that > continually reported status 'D' in top. Full stop. The most likely problem here is that the query is now hitting the disks and waiting. If you have 1 disk and two users, the access speed will drop by factors, usually much higher than 2. To put it very simply, you need as many mirror pairs in your RAID-10 or as many disks in your RAID5 or RAID 6 as you have users reading the disk drives. If you're writing you need more and more disks too. Mediating this issue we find things like SSD cache in ZFS or battery backed RAID controllers. They allow the reads and writes to be streamlined quite a bit to the spinning disks, making it appear the RAID array underneath it was much faster, had better access, and all the sectors were near each other. To an extent. If you have the answer to the previous poster's question "can you tell us what sort of IO you have (sata, scsi, raid, # of disks, etc)." you should provide it. If you've got a pair of 5k RPM SATA drives in a RAID-1 you might need more hardware. So, instead of just settings, show us a few carefully selected lines of output from vmstat or iostat while this is happening. Don't tell us what you see, show us. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: FW: [PERFORM] Queries becoming slow under heavy load
On 01/26/2011 10:04 AM, Anne Rosset wrote: We've been able to match long running database queries to such processes. This occurs under relatively low load average (say 4 out of 8) and can involve as little as 1 single sql query. The b state means the process is blocking, waiting for... something. One thing you need to consider is far more than your CPU usage. If you have the 'sar' utility, run it as 'sar 1 100' just to see how your system is working. What you want to watch for is iowait. If even one query is churning your disks, every single other query that has to take even one block from disk instead of cache, is going to stall. If you see an iowait of anything greater than 5%, you'll want to check further on the device that contains your database with iostat. My favorite use of this is 'iostat -dmx [device] 1' where [device] is the block device where your data files are, if your WAL is somewhere else. And yeah, your shared_buffers are kinda on the lowish side. Your effective_cache_size is good, but you have a lot more room to increase PG-specific memory. Worse however, is your checkpoints. Lord. Increase checkpoint_segments to *at least* 20, and increase your checkpoint_completion_target to 0.7 or 0.8. Check your logs for checkpoint warnings, and I'll bet it's constantly complaining about increasing your checkpoint segments. Every checkpoint not started by the scheduled system risks a checkpoint spike, which can flood your system with IO regardless of which queries are running. That kind of IO storm will ruin your performance, and with only 3 checkpoint segments on a busy database, are probably happening constantly. Unfortunately we still need to know more. This is just based on your PG settings, and that's not really enough to know how "busy" your DB is. One way to check is to log the contents of pg_stat_database, especially the xact_commit and xact_rollback columns. Grab those with a timestamp. If you get a snapshot of that every minute, you can figure out how many queries you're processing per minute or per second pretty easily. We've hit 8600 TPS before and don't have nearly the trouble you've been reporting. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
FW: [PERFORM] Queries becoming slow under heavy load
Sorry it seems like the postgres configuration didn't come thru the first time. name| setting - + -- add_missing_from| off allow_system_table_mods | off archive_command | (disabled) archive_mode| off archive_timeout | 0 array_nulls | on authentication_timeout | 1min autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold| 250 autovacuum_freeze_max_age | 2 autovacuum_max_workers | 3 autovacuum_naptime | 5min autovacuum_vacuum_cost_delay| 20ms autovacuum_vacuum_cost_limit| -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 500 backslash_quote | safe_encoding bgwriter_delay | 200ms bgwriter_lru_maxpages | 100 bgwriter_lru_multiplier | 2 block_size | 8192 bonjour_name| check_function_bodies | on checkpoint_completion_target| 0.5 checkpoint_segments | 3 checkpoint_timeout | 5min checkpoint_warning | 30s client_encoding | UTF8 client_min_messages | notice commit_delay| 250 commit_siblings | 10 constraint_exclusion| off cpu_index_tuple_cost| 0.005 cpu_operator_cost | 0.0025 cpu_tuple_cost | 0.01 custom_variable_classes | DateStyle | ISO, MDY db_user_namespace | off deadlock_timeout| 1s debug_assertions| off debug_pretty_print | off debug_print_parse | off debug_print_plan| off debug_print_rewritten | off default_statistics_target | 10 default_tablespace | default_text_search_config | pg_catalog.simple default_transaction_isolation | read committed default_transaction_read_only | off default_with_oids | off effective_cache_size| 400kB enable_bitmapscan | on enable_hashagg | on enable_hashjoin | on enable_indexscan| on enable_mergejoin| off enable_nestloop | on enable_seqscan | on enable_sort | on enable_tidscan | on escape_string_warning | on explain_pretty_print| on extra_float_digits | 0 from_collapse_limit | 8 fsync | on full_page_writes| on geqo| off geqo_effort | 5 geqo_generations| 0 geqo_pool_size | 0 geqo_selection_bias | 2 geqo_threshold | 12 gin_fuzzy_search_limit | 0 ignore_system_indexes | off integer_datetimes | off join_collapse_limit | 8 krb_caseins_users | off krb_server_hostname | krb_srvname | postgres lc_collate | en_US.UTF-8 lc_ctype| en_US.UTF-8 lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8 listen_addresses| 127.0.0.1,208.75.198.149 local_preload_libraries | log_autovacuum_min_duration | -1 log_checkpoints | off log_connections | off log_destination | stderr log_disconnections | off log_duration| off log_error_verbosity | default log_executor_stats | off log_hostname| off log_line_prefix | log_lock_waits | off log_min_duration_statement | -1 log_min_error_statement | error log_min_messages| notice log_parser_stats| off log_planner_stats | off log_rotation_age| 0 log_rotation_size | 0 log_statement | none log_statement_stats | off log_temp_files | -1 log_timezone| Asia/Kolkata log_truncate_on_rotation| off logging_collector | on maintenance_work_mem| 256MB max_connections | 100 max_files_per_process | 1000 max_fsm_pages | 50 max_fsm_relations | 500 max_function_args | 100 max_identifier_length | 63 max_index_keys | 32 max_locks_per_transaction | 64 max_prepared_transactions | 5 max_stack_depth | 5MB password_encryption | on port| 5432 post_auth_delay | 0 pre_auth_delay | 0 random_page_cost| 4 regex_flavor| advanced search_path | "$user",public seq_page_cost | 1 server_encoding | UTF8 server_version | 8.3.8 server_version_num | 80308 session_replication_role| origin shared_buffers | 240MB silent_mode | off sql_inheritance | on ssl | off standard_conforming_strings | off statement_timeou
Re: FW: [PERFORM] Performance 8.4.0
On Sun, Aug 2, 2009 at 10:04 PM, Chris Dunn wrote: > The database is 8gb currently. Use to be a lot bigger but we removed all > large objects out and developed a file server storage for it, and using > default page costs for 8.4, I did have it changed in 8.1.4 You might want to play with lowering them. The default page costs make page accesses expensive relative to per-tuple operations, which is appropriate if you are I/O-bound but not so much if you are CPU bound, and especially if the whole database is memory resident. I'd try something like random_page_cost = seq_page_cost = 0.1 for starters, or whatever values were working for you in 8.1, but the sweet spot may be higher or lower. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
FW: [PERFORM] Performance 8.4.0
The database is 8gb currently. Use to be a lot bigger but we removed all large objects out and developed a file server storage for it, and using default page costs for 8.4, I did have it changed in 8.1.4 -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Sunday, 2 August 2009 11:26 PM To: Chris Dunn Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Performance 8.4.0 On Fri, Jul 31, 2009 at 12:22 AM, Chris Dunn wrote: > constraint_exclusion = on This is critical if you need it, but a waste of CPU time if you don't. Other than that your paramaters look good. Are you using the default page cost settings? I see you have 12 GB RAM; how big is your database? ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
FW: [PERFORM] optimize query with a maximum(date) extraction
bad address kep his from going to the list on my first try ... apologies to the moderators. -Original Message- From: Gregory Williamson Sent: Wed 9/5/2007 4:59 AM To: JS Ubei; pgsql-performance@postgresql.org Subject: RE: [PERFORM] optimize query with a maximum(date) extraction In order to help others help you, you might provide the following: table description (columns, types, indexes) (\d tablename from psql does nicely) the same query run as "EXPLAIN ANALYZE ;" Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.) -Original Message- From: [EMAIL PROTECTED] on behalf of JS Ubei Sent: Wed 9/5/2007 3:53 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] optimize query with a maximum(date) extraction Hi all, I need to improve a query like : SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id; Stupidly, I create a B-tree index on my_table(the_date), witch is logically not used in my query, because it's not with a constant ? isn't it ? I know that I can't create a function index with an aggregative function. How I can do ? thanks, jsubei _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: FW: [PERFORM]
<[EMAIL PROTECTED]> writes: > with a standard 7200 rpm drive ~150 transactions/sec sounds about right > > to really speed things up you want to get a disk controller with a battery > backed cache so that the writes don't need to hit the disk to be safe. Note that this is only if you're counting transactions/sec in a single session. You can get much more if you have many sessions since they can all commit together in a single disk i/o. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: FW: [PERFORM]
On Tue, 8 May 2007, Orhan Aglagul wrote: No, it is one transaction per insert. -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 5:38 PM To: Orhan Aglagul Subject: RE: [PERFORM] On Tue, 2007-05-08 at 19:36, Orhan Aglagul wrote: But 10,000 records in 65 sec comes to ~153 records per second. On a dual 3.06 Xeon What range is acceptable? If you're doing that in one big transaction, that's horrible. Because it shouldn't be waiting for each insert to fsync, but the whole transaction. with a standard 7200 rpm drive ~150 transactions/sec sounds about right to really speed things up you want to get a disk controller with a battery backed cache so that the writes don't need to hit the disk to be safe. that should get your speeds up to (and possibly above) what you got by turning fsync off. David Lang ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
FW: [PERFORM]
No, it is one transaction per insert. -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 5:38 PM To: Orhan Aglagul Subject: RE: [PERFORM] On Tue, 2007-05-08 at 19:36, Orhan Aglagul wrote: > But 10,000 records in 65 sec comes to ~153 records per second. On a dual > 3.06 Xeon > What range is acceptable? If you're doing that in one big transaction, that's horrible. Because it shouldn't be waiting for each insert to fsync, but the whole transaction. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
FW: [PERFORM]
-Original Message- From: Orhan Aglagul Sent: Tuesday, May 08, 2007 5:37 PM To: 'Scott Marlowe' Subject: RE: [PERFORM] But 10,000 records in 65 sec comes to ~153 records per second. On a dual 3.06 Xeon What range is acceptable? -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 5:31 PM To: Orhan Aglagul Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] On Tue, 2007-05-08 at 17:59, Orhan Aglagul wrote: > Hi Everybody, > > I was trying to see how many inserts per seconds my application could > handle on various machines. > > Here is the data: > > > > Time for 1 inserts > > Fsync=on > > Fsync=off > > Pentium M 1.7 > > ~17 sec > > ~6 sec > > Pentium 4 2.4 > > ~13 sec > > ~11 sec > > Dual Xeon > > ~65 sec > > ~1.9 sec > > > In addition to my previous post, if you see that big a change between fsync on and off, you likely have a drive subsystem that is actually reporting fsync properly. The other two machines are lying. Or they have a battery backed caching raid controller ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
FW: [PERFORM]
Yes, I did not do it in one transaction. All 3 machines are configured with the same OS and same version postgres. No kernel tweaking and no postgres tweaking done (except the fsync)... -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 5:23 PM To: Orhan Aglagul Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] On Tue, 2007-05-08 at 17:59, Orhan Aglagul wrote: > Hi Everybody, > > I was trying to see how many inserts per seconds my application could > handle on various machines. > > Those are the machines I used to run my app: > > > > 1) Pentium M 1.7Ghz > > 2) Pentium 4 2.4 Ghz > > 3) DMP Xeon 3Ghz > > > > Sure, I was expecting the dual Zeon to outperform the Pentium M and 4. > But the data showed the opposite. > > So, I wrote a simple program (in C) using the libpq.so.5 which opens a > connection to the database (DB in localhost), > > Creates a Prepared statement for the insert and does a 10,000 insert. > The result did not change. > > > > Only after setting fsync to off in the config file, the amount of time > to insert 10,000 records was acceptable. > > > > Here is the data: > > > > Time for 1 inserts > > Fsync=on > > Fsync=off > > Pentium M 1.7 > > ~17 sec > > ~6 sec > > Pentium 4 2.4 > > ~13 sec > > ~11 sec > > Dual Xeon > > ~65 sec > > ~1.9 sec > > > > > I read that postgres does have issues with MP Xeon (costly context > switching). But I still think that with fsync=on 65 seconds is > ridiculous. > > > > Can anybody direct me to some improved/acceptable performance with > fsync=on? I'm guessing you didn't do the inserts inside a single transaction, which means that each insert was it's own transaction. Try doing them all in a transaction. I ran this simple php script: and it finished in 3.5 seconds on my workstation (nothing special) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: FW: [PERFORM] Simple join optimized badly?
Jim C. Nasby wrote: Ok, now that I've actually looked at the release notes, I take that back and apologize. But while there's a lot of improvements that have been made, there's still some seriously tough problems that have been talked about for a long time and there's still no "light at the end of the tunnel", like how to handle multi-column statistics. Yeah - multi-column stats and cost/stats for functions look the the next feature additions we need to get going on Cheers Mark ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] Simple join optimized badly?
Tom Lane wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> writes: If someone's going to commit to putting effort into improving the planner then that's wonderful. But I can't recall any significant planner improvements since min/max (which I'd argue was more of a bug fix than an improvement). Hmph. Apparently I've wasted most of the last five years. In my opinion your on-going well thought out planner improvements are *exactly* the approach we need to keep doing... Cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: FW: [PERFORM] Simple join optimized badly?
On Thu, 2006-10-12 at 09:44, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > If someone's going to commit to putting effort into improving the > > planner then that's wonderful. But I can't recall any significant > > planner improvements since min/max (which I'd argue was more of a bug > > fix than an improvement). > > Hmph. Apparently I've wasted most of the last five years. I appreciate the work, and trust me, I've noticed the changes in the query planner over time. Thanks for the hard work, and I'm sure there are plenty of other thankful people too. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] Simple join optimized badly?
On Thu, Oct 12, 2006 at 10:44:20AM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > If someone's going to commit to putting effort into improving the > > planner then that's wonderful. But I can't recall any significant > > planner improvements since min/max (which I'd argue was more of a bug > > fix than an improvement). > > Hmph. Apparently I've wasted most of the last five years. Ok, now that I've actually looked at the release notes, I take that back and apologize. But while there's a lot of improvements that have been made, there's still some seriously tough problems that have been talked about for a long time and there's still no "light at the end of the tunnel", like how to handle multi-column statistics. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] Simple join optimized badly?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > If someone's going to commit to putting effort into improving the > planner then that's wonderful. But I can't recall any significant > planner improvements since min/max (which I'd argue was more of a bug > fix than an improvement). Hmph. Apparently I've wasted most of the last five years. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: FW: [PERFORM] Simple join optimized badly?
On Thu, Oct 12, 2006 at 10:59:23PM +1300, Mark Kirkwood wrote: > H.J. Sanders wrote: > > > why not just like in some other (commercial) databases: > > > > a statement to say: use index > > > > I know this is against all though but if even the big ones can not resist > > the pressure of their users, why not? > > > > Yeah - some could not (e.g. Oracle), but some did (e.g. DB2), and it > seemed (to me anyway) significant DB2's optimizer worked much better > than Oracle's last time I used both of them (Oracle 8/9 and DB2 7/8). If someone's going to commit to putting effort into improving the planner then that's wonderful. But I can't recall any significant planner improvements since min/max (which I'd argue was more of a bug fix than an improvement). In fact, IIRC it took at least 2 major versions to get min/max fixed, and that was a case where it was very clear-cut what had to be done. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: FW: [PERFORM] Simple join optimized badly?
H.J. Sanders wrote: why not just like in some other (commercial) databases: a statement to say: use index I know this is against all though but if even the big ones can not resist the pressure of their users, why not? Yeah - some could not (e.g. Oracle), but some did (e.g. DB2), and it seemed (to me anyway) significant DB2's optimizer worked much better than Oracle's last time I used both of them (Oracle 8/9 and DB2 7/8). cheers Mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
FW: [PERFORM] Simple join optimized badly?
Hello. Simply jumping on the bandwagon, just my 2 cents: why not just like in some other (commercial) databases: a statement to say: use index I know this is against all though but if even the big ones can not resist the pressure of their users, why not? Henk Sanders > > -Oorspronkelijk bericht- > > Van: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] Bucky Jordan > > Verzonden: woensdag 11 oktober 2006 16:27 > > Aan: Tom Lane; Brian Herlihy > > CC: Postgresql Performance > > Onderwerp: Re: [PERFORM] Simple join optimized badly? > > > > > > > Brian Herlihy <[EMAIL PROTECTED]> writes: > > > > What would it take for hints to be added to postgres? > > > > > > A *whole lot* more thought and effort than has been expended on the > > > subject to date. > > > > > > Personally I have no use for the idea of "force the planner to do > > > exactly X given a query of exactly Y". You don't have exactly Y > > > today, tomorrow, and the day after (if you do, you don't need a > > > hint mechanism at all, you need a mysql-style query cache). > > > IMHO most of the planner mistakes we see that could be fixed via > > > hinting are really statistical estimation errors, and so the right > > > level to be fixing them at is hints about how to estimate the number > > > of rows produced for given conditions. Mind you that's still a plenty > > > hard problem, but you could at least hope that a hint of that form > > > would be useful for more than one query. > > > > > > > Do I understand correctly that you're suggesting it might not be a bad > > idea to allow users to provide statistics? > > > > Is this along the lines of "I'm loading a big table and touching every > > row of data, so I may as well collect some stats along the way" and "I > > know my data contains these statistical properties, but the analyzer > > wasn't able to figure that out (or maybe can't figure it out efficiently > > enough)"? > > > > While it seems like this would require more knowledge from the user > > (e.g. more about their data, how the planner works, and how it uses > > statistics) this would actually be helpful/required for those who really > > care about performance. I guess it's the difference between a tool > > advanced users can get long term benefit from, or a quick fix that will > > probably come back to bite you. I've been pleased with Postgres' > > thoughtful design; recently I've been doing some work with MySQL, and > > can't say I feel the same way. > > > > Also, I'm guessing this has already come up at some point, but what > > about allowing PG to do some stat collection during queries? If you're > > touching a lot of data (such as an import process) wouldn't it be more > > efficient (and perhaps more accurate) to collect stats then, rather than > > having to re-scan? It would be nice to be able to turn this on/off on a > > per query basis, seeing as it could have pretty negative impacts on OLTP > > performance... > > > > - Bucky > > > > ---(end of broadcast)--- > > TIP 4: Have you searched our list archives? > > > >http://archives.postgresql.org > > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
FW: [PERFORM] XFS filessystem for Datawarehousing -2
Sorry, forgot to ask: What is the recommended/best PG block size for DWH database? 16k, 32k, 64k ? What hsould be the relation between XFS/RAID stripe size and PG block size ? Best Regards. Milen Kulev -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Milen Kulev Sent: Tuesday, August 01, 2006 11:50 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] XFS filessystem for Datawarehousing I intend to test Postgres/Bizgres for DWH use. I want to use XFS filesystem to get the best possible performance at FS level(correct me if I am wrong !). Is anyone using XFS for storing/retrieving relatively large amount of data (~ 200GB)? If yes, what about the performance and stability of XFS. I am especially interested in recommendations about XFS mount options and mkfs.xfs options. My setup will be roughly this: 1) 4 SCSI HDD , 128GB each, 2) RAID 0 on the four SCSI HDD disks using LVM (software RAID) There are two other SATA HDD in the server. Server has 2 physical CPUs (XEON at 3 GHz), 4 Logical CPUs, 8 GB RAM, OS = SLES9 SP3 My questions: 1) Should I place external XFS journal on separate device ? 2) What should be the journal buffer size (logbsize) ? 3) How many journal buffers (logbufs) should I configure ? 4) How many allocations groups (for mkfs.xfs) should I configure 5) Is it wortj settion noatime ? 6) What I/O scheduler(elevators) should I use (massive sequencial reads) 7) What is the ideal stripe unit and width (for a RAID device) ? I will appreciate any options, suggestions, pointers. Best Regards. Milen Kulev ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
FW: [PERFORM] pg_xlog on data partition with BBU RAID
Forwarding to -performance From: Alan Hodgson [mailto:[EMAIL PROTECTED] On Friday 09 June 2006 12:41, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > Has anyone actually done any testing on this? Specifically, I'm > wondering if the benefit of adding 2 more drives to a RAID10 outweighs > whatever penalties there are to having pg_xlog on that RAID10 with all > the rest of the data. I have an external array with 1GB of write-back cache, and testing on it before deployment showed no difference under any workload I could generate between having pg_xlog on a separate RAID-1 or having it share a RAID-10 with the default tablespace. I left it on the RAID-10, and it has been fine there. We have a very write-heavy workload. -- "If a nation expects to be ignorant and free, in a state of civilization, it expects what never was and never will be." -- Thomas Jefferson ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
FW: [PERFORM] x206-x226
Hello list. Reading my own e-mail I notice I made a very important mistake. The X206 has 1 x ATA 7200 RPM The X226 has 2 x SCSI RAID1 1RPM I corrected it below. Sorry . Henk Sanders -Oorspronkelijk bericht-Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Namens H.J. SandersVerzonden: vrijdag 10 maart 2006 10:50Aan: pgsql-performance@postgresql.orgOnderwerp: [PERFORM] x206-x225 Hello list. We have compared 2 IBM x servers: IBM X206 IBM X226 -- --- processor Pentium 4 3.2 Ghz Xeon 3.0 Ghz main memory 1.25 GB 4 GB discs 1 x ATA 7200 RPM 2 x SCSI RAID1 1RPM LINUX 2.6 (SUSE 9) same PGSQL 7.4 same postgresql.conf attached same We have bij means of an informix-4GL program done the following test: create table : name char(18) adres char(20) key integer create index on (key) Time at X206 Time at X226 -- insert record (key goes from 1 to 1) 6 sec. 41 sec. select record (key goes from 1 to 1) 4 4 delete record (key goes from 1 to 1) 6 41 This is ofcourse a totally unexpected results (you should think off the opposite). Funny is that the select time is the same for both machines. Does anybody has any any idea what can cause this strange results or where we can start our investigations? Regards Henk Sanders postgresql.conf-74 Description: Binary data ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
FW: [PERFORM] Used Memory
Here are the configuration of our database server: port = 5432 max_connections = 300 superuser_reserved_connections = 10 authentication_timeout = 60 shared_buffers = 48000 sort_mem = 32168 sync = false Do you think this is enough? Or can you recommend a better configuration for my server? The server is also running PHP and Apache but wer'e not using it extensively. For development purpose only. The database slow down is occurring most of the time (when the memory free is low) I don't think it has something to do with vacuum. We only have a full server vacuum once a day. -Original Message- From: Mark Kirkwood [mailto:[EMAIL PROTECTED] Sent: Monday, October 24, 2005 3:14 AM To: Christian Paul B. Cosinas Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory > > > I just noticed that as long as the free memory in the first row (which > is 55036 as of now) became low, the slower is the response of the > database server. > Also, how about posting your postgresql.conf (or just the non-default parameters) to this list? Some other stuff that could be relevant: - Is the machine just a database server, or does it run (say) Apache + Php? - When the slowdown is noticed, does this coincide with certain activities - e.g, backup , daily maintenance, data load(!) etc. regards Mark > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html Nope, not me either. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
FW: [PERFORM] Used Memory
Here are the configuration of our database server: port = 5432 max_connections = 300 superuser_reserved_connections = 10 authentication_timeout = 60 shared_buffers = 48000 sort_mem = 32168 sync = false Do you think this is enough? Or can you recommend a better configuration for my server? The server is also running PHP and Apache but wer'e not using it extensively. For development purpose only. The database slow down is occurring most of the time (when the memory free is low) I don't think it has something to do with vacuum. We only have a full server vacuum once a day. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] Deadlock Issue with PostgreSQL
Anu, > Thanks a lot for your quick response. Which version do you think is the > more stable one that we should upgrade to? 8.0.3 > Please provide us with the Upgrade instructions/documentation to be > followed for both red hat and PostgreSQL. See the PostgreSQL documentation for upgrade instructions. Given how old your version is, you might need to go through an intermediate version, like 7.3. Red Hat upgrades are between you and Red Hat. They sell support for a reason ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
FW: [PERFORM] Deadlock Issue with PostgreSQL
Hello Tom, Thanks a lot for your quick response. Which version do you think is the more stable one that we should upgrade to? Please provide us with the Upgrade instructions/documentation to be followed for both red hat and PostgreSQL. Thanks and Best Regards, Anu -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 21, 2005 12:15 PM To: Anu Kucharlapati Cc: pgsql-performance@postgresql.org; Owen Blizzard Subject: Re: [PERFORM] Deadlock Issue with PostgreSQL "Anu Kucharlapati" <[EMAIL PROTECTED]> writes: > Red Hat Linux release 7.3 > Apache 1.3.20 > PostgreSQL 7.1.3 I'm not sure about Apache, but both the RHL and Postgres versions you are using are stone age --- *please* update. Red Hat stopped supporting that release years ago, and the PG community isn't supporting 7.1.* anymore either. There are too many known problems in 7.1.* that are unfixable without a major-version upgrade. regards, tom lane
Fw: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
- Original Message - From: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]> To: Sent: Thursday, September 22, 2005 6:37 PM Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0 How do I produce an "Index scan plan" ? - Original Message - From: "Josh Berkus" To: Cc: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>; "John Arbash Meinel" <[EMAIL PROTECTED]> Sent: Thursday, September 22, 2005 6:19 PM Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0 Jean-Pierre, First off, you're on Windows? " -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4) (actual time=0.004..1143.720 rows=581475 loops=1)" Well, this is your pain point. Can we see the index scan plan on 8.1? Given that it's *expecting* only one row, I can't understand why it's using a seq scan ... "Nested Loop Left Join (cost=0.00..11.02 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)" " -> Nested Loop Left Join (cost=0.00..5.48 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)" "Total runtime: 0.000 ms" Feh, this looks like the "windows does not report times" bug, which makes it hard to compare ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: FW: [PERFORM] speed of querry?
On Mon, 18 Apr 2005, Joel Fradkin wrote: > Another odd thing is when I tried turning off merge joins on the XP desktop > It took 32 secs to run compared to the 6 secs it was taking. > On the Linux (4proc box) it is now running in 3 secs with the mergejoins > turned off. > > Unfortunately it takes over 2 minutes to actually return the 160,000+ rows. > I am guessing that is either network (I have gig cards on a LAN) or perhaps > the ODBC driver (using PGADMIN III to do the select). > > I tried to run on psql on the server but it was putting it out to more. > If I do it and use > test.txt will it run it all out so I can get a time? > Does it display the time anywhere like in pgadminIII? Redirecting should turn the pager off. \timing will add a timing number after queries. If you want to not be bothered by the pager, you can turn if off with \pset pager off. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
FW: [PERFORM] speed of querry?
Sorry if this posts twice I posted and did not see it hit the list. What are the statistics for tbljobtitle.id and tbljobtitle.clientnum I added default_statistics_target = 250 to the config and re-loaded the data base. If that is what you mean? --- how many distinct values of each, tbljobtitle.id 6764 for all clients 1018 for SAKS tbljobtitle.clientnum 237 distinct clientnums just 1 for SAKS and are the distributions skewed to a few popular values? There are 3903 distinct values for jobtitle Not sure if I answered the questions, let me know if you need more info. It appears there are 1018 job titles in the table for saks and 6764 for all the clients. There can be more values as presentation layer can have more then one value for an id. SAKS is not using presentation layer yet as there are only 1018 distinct values 1 for each id. Joel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
FW: [PERFORM] speed of querry?
What are the statistics for tbljobtitle.id and tbljobtitle.clientnum I added default_statistics_target = 250 to the config and re-loaded the data base. If that is what you mean? --- how many distinct values of each, tbljobtitle.id 6764 for all clients 1018 for SAKS tbljobtitle.clientnum 237 distinct clientnums just 1 for SAKS and are the distributions skewed to a few popular values? There are 3903 distinct values for jobtitle Not sure if I answered the questions, let me know if you need more info. It appears there are 1018 job titles in the table for saks and 6764 for all the clients. There can be more values as presentation layer can have more then one value for an id. SAKS is not using presentation layer yet as there are only 1018 distinct values 1 for each id. Joel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
FW: [PERFORM] speed of querry?
Another odd thing is when I tried turning off merge joins on the XP desktop It took 32 secs to run compared to the 6 secs it was taking. On the Linux (4proc box) it is now running in 3 secs with the mergejoins turned off. Unfortunately it takes over 2 minutes to actually return the 160,000+ rows. I am guessing that is either network (I have gig cards on a LAN) or perhaps the ODBC driver (using PGADMIN III to do the select). I tried to run on psql on the server but it was putting it out to more. If I do it and use > test.txt will it run it all out so I can get a time? Does it display the time anywhere like in pgadminIII? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] FW: Index usage
BBI Edwin Punzalan wrote: Hi. 1) chatlogs rows increases every now and then (its in a live environment) and currently have 538,696 rows OK, so as a rule of thumb I'd say if you were fetching less than 5000 rows it's bound to use an index. If more than 50,000 always use a seqscan, otherwise it'll depend on configuration settings. It looks like you settings are suggesting the cost of an index-scan vs seq-scan are greater than they are. 2) this is the only problem we experienced. So far, all our other indexes are being used correctly. Good. 3) I don't remember tuning any post-installation configuration of our postgreSQL except setting fsync to false. So long as you know why this can cause data loss. It won't affect this problem. Read that performance article I linked to in the last message, it's written by two people who know what they're talking about. The standard configuration settings are designed to work on any machine, not provide good performance. Work through the basics there and we can look at random_page_cost etc. if it's still causing you problems. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: FW: [PERFORM] FW: Index usage
Hi. 1) chatlogs rows increases every now and then (its in a live environment) and currently have 538,696 rows 2) this is the only problem we experienced. So far, all our other indexes are being used correctly. 3) I don't remember tuning any post-installation configuration of our postgreSQL except setting fsync to false. Thanks for taking a look at our problem. :D -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 6:17 PM To: BBI Edwin Punzalan Cc: [EMAIL PROTECTED] Subject: Re: FW: [PERFORM] FW: Index usage BBI Edwin Punzalan wrote: > Thanks, Tim. > > I tried adding an upper limit and its still the same as follows: > > == > db=# explain analyze select date from chatlogs where date>='11/24/04'; > NOTICE: QUERY PLAN: > > Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..145.72 > rows=37 > width=4) (actual time=0.18..239.69 rows=10737 loops=1) > Total runtime: 246.22 msec > > EXPLAIN > db=# explain analyze select date from chatlogs where date>='11/23/04' > and date<'11/24/04'; > NOTICE: QUERY PLAN: > > Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual > time=0.44..4447.01 rows=13029 loops=1) Total runtime: 4455.56 msec We have two issues here 1. In the first example it only picks an index because it thinks it is going to get 37 rows, it actually gets 10737 2. It's taking 4455ms to run a seq-scan but only 246ms to run an index-scan over 10737 rows (and then fetch the rows too). Questions: 1. How many rows do you have in chatlogs? 2. Is this the only problem you are experiencing, or just one from many? 3. Have you tuned any configuration settings? e.g. as suggested in: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] FW: Index usage
BBI Edwin Punzalan wrote: Thanks, Tim. I tried adding an upper limit and its still the same as follows: == db=# explain analyze select date from chatlogs where date>='11/24/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..145.72 rows=37 width=4) (actual time=0.18..239.69 rows=10737 loops=1) Total runtime: 246.22 msec EXPLAIN db=# explain analyze select date from chatlogs where date>='11/23/04' and date<'11/24/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual time=0.44..4447.01 rows=13029 loops=1) Total runtime: 4455.56 msec We have two issues here 1. In the first example it only picks an index because it thinks it is going to get 37 rows, it actually gets 10737 2. It's taking 4455ms to run a seq-scan but only 246ms to run an index-scan over 10737 rows (and then fetch the rows too). Questions: 1. How many rows do you have in chatlogs? 2. Is this the only problem you are experiencing, or just one from many? 3. Have you tuned any configuration settings? e.g. as suggested in: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
FW: [PERFORM] FW: Index usage
Thanks, Tim. I tried adding an upper limit and its still the same as follows: == db=# explain analyze select date from chatlogs where date>='11/24/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..145.72 rows=37 width=4) (actual time=0.18..239.69 rows=10737 loops=1) Total runtime: 246.22 msec EXPLAIN db=# explain analyze select date from chatlogs where date>='11/23/04' and date<'11/24/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual time=0.44..4447.01 rows=13029 loops=1) Total runtime: 4455.56 msec EXPLAIN db=# explain analyze select date from chatlogs where date>='11/23/04' and date<'11/25/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual time=0.45..4268.00 rows=23787 loops=1) Total runtime: 4282.81 msec == How come a query on the current date filter uses an index and the others does not? This makes indexing to speed up queries quite difficult. -Original Message- From: Leeuw van der, Tim [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 3:35 PM To: BBI Edwin Punzalan; [EMAIL PROTECTED] Subject: RE: [PERFORM] FW: Index usage Well you just selected a whole lot more rows... What's the total number of rows in the table? In general, what I remember from reading on the list, is that when there's no upper bound on a query like this, the planner is more likely to choose a seq. scan than an index scan. Try to give your query an upper bound like: select date from chatlogs where date>='11/23/04' and date < '12/31/99'; select date from chatlogs where date>='10/23/04' and date < '12/31/99'; This should make it easier for the planner to give a proper estimate of the number of rows returned. If it doesn't help yet, please post 'explain analyze' output rather than 'explain' output, for it allows much better investigation into why the planner chooses what it chooses. cheers, --Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of BBI Edwin Punzalan Sent: Wednesday, November 24, 2004 7:52 AM To: [EMAIL PROTECTED] Subject: [PERFORM] FW: Index usage Hi everyone, Can anyone please explain postgres' behavior on our index. I did the following query tests on our database: db=# create index chatlogs_date_idx on chatlogs (date); CREATE db=# explain select date from chatlogs where date>='11/23/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..144.11 rows=36 width=4) EXPLAIN db=# explain select date from chatlogs where date>='10/23/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..23938.06 rows=253442 width=4) EXPLAIN Date's datatype is date. Its just odd that I just change the actual date of search and the index is not being used anymore. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
FW: [PERFORM] can't handle large number of INSERT/UPDATEs
>>Eliminate that contention point, and you will have solved your problem. I agree, If your updates are slow then you will get a queue building up. Make sure that:- 1) all your indexing is optimised. 2) you are doing regular vacuuming (bloated tables will cause a slow down due to swapping). 3) your max_fsm_pages setting is large enough - it needs to be big enough to hold all the transactions between vacuums (+ some spare for good measure). 4) do a full vacuum - do one to start and then do one after you have had 2&3 (above) in place for a while - if the full vacuum handles lots of dead tuples then your max_fsm_pages setting is too low. 5) Also try reindexing or drop/recreate the indexes in question as... "PostgreSQL is unable to reuse B-tree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only be reused by rows with similar values. For example, if indexed rows are deleted and newly inserted/updated rows have much higher values, the new rows can't use the index space made available by the deleted rows. Instead, such new rows must be placed on new index pages. In such cases, disk space used by the index will grow indefinitely, even if VACUUM is run frequently. " Are your updates directly executed or do you use stored procs? We had a recent problem with stored procs as they store a "one size fits all" query plan when compiled - this can be less than optimum in some cases. We have a similar sounding app to yours and if tackled correctly then all the above will make a massive difference in performance. Rod -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rod Taylor Sent: 25 October 2004 22:19 To: Anjan Dave Cc: Postgresql Performance Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs On Mon, 2004-10-25 at 16:53, Anjan Dave wrote: > Hi, > > > > I am dealing with an app here that uses pg to handle a few thousand > concurrent web users. It seems that under heavy load, the INSERT and > UPDATE statements to one or two specific tables keep queuing up, to > the count of 150+ (one table has about 432K rows, other has about > 2.6Million rows), resulting in ?wait?s for other queries, and then This isn't an index issue, it's a locking issue. Sounds like you have a bunch of inserts and updates hitting the same rows over and over again. Eliminate that contention point, and you will have solved your problem. Free free to describe the processes involved, and we can help you do that. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fw: [PERFORM] Query performance issue with 8.0.0beta1
- Original Message - From: "Stefano Bonnin" <[EMAIL PROTECTED]> To: "Josh Berkus" <[EMAIL PROTECTED]> Sent: Monday, August 30, 2004 4:13 PM Subject: Re: [PERFORM] Query performance issue with 8.0.0beta1 > This is my postgres.conf, I have changed only the work_mem and > shared_buffers parameters. > > >DID you > > configure it for the 8.0 database? > > What does it mean? Is in 8.0 some important NEW configation parameter ? > > # pgdata = '/usr/local/pgsql/data' # use data in another > directory > # hba_conf = '/etc/pgsql/pg_hba.conf' # use hba info in another > directory > # ident_conf = '/etc/pgsql/pg_ident.conf' # use ident info in another > directory > # external_pidfile= '/var/run/postgresql.pid' # write an extra pid file > #listen_addresses = 'localhost' # what IP interface(s) to listen on; > # defaults to localhost, '*' = any > #port = 5432 > max_connections = 100 > #superuser_reserved_connections = 2 > #unix_socket_directory = '' > #unix_socket_group = '' > #unix_socket_permissions = 0777 # octal > #rendezvous_name = '' # defaults to the computer name > #authentication_timeout = 60# 1-600, in seconds > #ssl = false > #password_encryption = true > #krb_server_keyfile = '' > #db_user_namespace = false > > shared_buffers = 2048 # min 16, at least max_connections*2, 8KB > each > work_mem = 2048 # min 64, size in KB > #maintenance_work_mem = 16384 # min 1024, size in KB > #max_stack_depth = 2048 # min 100, size in KB > > #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each > #max_fsm_relations = 1000 # min 100, ~50 bytes each > > #max_files_per_process = 1000 # min 25 > #preload_libraries = '' > > #vacuum_cost_delay = 0 # 0-1000 milliseconds > #vacuum_cost_page_hit = 1 # 0-1 credits > #vacuum_cost_page_miss = 10 # 0-1 credits > #vacuum_cost_page_dirty = 20# 0-1 credits > #vacuum_cost_limit = 200# 0-1 credits > > #bgwriter_delay = 200 # 10-5000 milliseconds > #bgwriter_percent = 1 # 1-100% of dirty buffers > #bgwriter_maxpages = 100# 1-1000 buffers max at once > > #fsync = true # turns forced synchronization on or off > #wal_sync_method = fsync# the default varies across platforms: > # fsync, fdatasync, open_sync, or > open_datasync > #wal_buffers = 8# min 4, 8KB each > #commit_delay = 0 # range 0-10, in microseconds > #commit_siblings = 5# range 1-100 > #checkpoint_segments = 3# in logfile segments, min 1, 16MB each > #checkpoint_timeout = 300 # range 30-3600, in seconds > #checkpoint_warning = 30# 0 is off, in seconds > > #archive_command = '' # command to use to archive a logfile > segment > > #enable_hashagg = true > #enable_hashjoin = true > #enable_indexscan = true > #enable_mergejoin = true > #enable_nestloop = true > #enable_seqscan = true > #enable_sort = true > #enable_tidscan = true > > #effective_cache_size = 1000# typically 8KB each > #random_page_cost = 4 # units are one sequential page fetch cost > #cpu_tuple_cost = 0.01 # (same) > #cpu_index_tuple_cost = 0.001 # (same) > #cpu_operator_cost = 0.0025 # (same) > > #geqo = true > #geqo_threshold = 12 > #geqo_effort = 5# range 1-10 > #geqo_pool_size = 0 # selects default based on effort > #geqo_generations = 0 # selects default based on effort > #geqo_selection_bias = 2.0 # range 1.5-2.0 > > default_statistics_target = 20 # range 1-1000 > #from_collapse_limit = 8 > #join_collapse_limit = 8# 1 disables collapsing of explicit JOINs > > #log_destination = 'stderr' # Valid values are combinations of stderr, > # syslog and eventlog, depending on > # platform. > > # This is relevant when logging to stderr: > #redirect_stderr = false# Enable capturing of stderr into log files. > # These are only relevant if redirect_stderr is true: > #log_directory = 'pg_log' # Directory where logfiles are written. > # May be specified absolute or relative to > PGDATA > #log_filename_prefix = 'postgresql_' # Prefix for logfile names. > #log_rotation_age = 1440# Automatic rotation of logfiles will happen > after > # so many minutes. 0 to disable. > #log_rotation_size = 10240 # Automatic rotation of logfiles will happen > after > # so many kilobytes of log output. 0 to > disable. > > # These are relevant when logging to syslog: > #syslog_facility = 'LOCAL0' > #syslog_ident = 'postgres' > > > # - When to Log - > > #client_min_messages = notice # Values, in order of decreasing detail: > # debug5, debug4, debug3, debug2, debug1, >
FW: [PERFORM] Tuning queries on large database
[forwarded to performance] > The result is that for "short queries" (Q1 and Q2) it runs in a few > seconds on both Oracle and PG. The difference becomes important with > Q3 : 8 seconds with oracle > 80 sec with PG > and too much with Q4 : 28s with oracle >17m20s with PG ! > > Of course when I run 100 or 1000 parallel queries such as Q3 or Q4, > it becomes a disaster ! > I can't understand these results. The way to execute queries is the > same I think. I've read recommended articles on the PG site. > I tried with a table containing 30 millions rows, results are similar. I don't trust the Oracle #s. Lets look at Q4: returns 3 million rows. Using your #s of 160 fields and 256 bytes, your are asking for a result set of 160 * 256 * 3M = 12 GB! This data has to be gathered by the disk, assembled, and sent over the network. I don't know Oracle, but it probably has some 'smart' result set that uses a cursor behind the scenes to do the fetching. With a 3M row result set, you need to strongly consider using cursors. Try experimenting with the same query (Q4), declared as a cursor, and fetch the data in 10k blocks in a loop (fetch 1), and watch the #s fly. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: FW: [PERFORM] Version 7 question
The best way to set it is to let the machine run under normal load for a while, then look at the cache / buffer usage using top (or some other program, top works fine for this). My server with 1.5 gig ram shows 862328K cache right now. So, just divide by page size (usually 8192) and I get ~ 100,000 blocks. On Tue, 1 Jul 2003, Howard Oblowitz wrote: > What would be the best value range for effective_cache_size > on Postgres 7.3.2, assuming say 1.5 GB of RAM and > shared_buffers set to 8192, and shmmax set to 750mb? > > And what are the most important factors one should take > into account in determining the value? > > > > > -Original Message- > > From: scott.marlowe [SMTP:[EMAIL PROTECTED] > > Sent: 01 July 2003 02:56 > > To: Michael Mattox > > Cc: Hilary Forbes; [EMAIL PROTECTED] > > Subject:Re: [PERFORM] Version 7 question > > > > 8192 is only 64 megs of RAM, not much, but a good number. Keep in mind > > that the kernel tends to be better at buffering huge amounts of disk, > > while postgresql is better left to use buffers that are large enough for > > the current working set (i.e. not your whole database, just the largest > > amount of data you're slinging about on a regular basis in one query.) > > > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768 > > (256 megs of ram) to run well, but anything over that doesn't help. Of > > course, we don't toss around more than a hundred meg or so at a time. If > > > > our result sets were in the gigabyte range, I'd A: want more memory and B: > > > > Give more of it to postgresql. > > > > The original poster was, I believe running 7.0.x, which is way old, so no, > > > > I don't think there was an equivalent of effective_cache_size in that > > version. Upgrading would be far easier than performance tuning 7.0. since > > > > the query planner was much simpler (i.e. more prone to make bad decisions) > > > > in 7.0. > > > > On Tue, 1 Jul 2003, Michael Mattox wrote: > > > > > I have my shared buffers at 8192 and my effective cache at 64000 (which > > is > > > 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and > > > I've been asking my boss for another 512megs for over a month now. I > > have > > > no idea if my buffers are too high/low. > > > > > > Michael > > > > > > > -Original Message- > > > > From: [EMAIL PROTECTED] > > > > [mailto:[EMAIL PROTECTED] Behalf Of Hilary > > > > Forbes > > > > Sent: Tuesday, July 01, 2003 2:10 PM > > > > To: [EMAIL PROTECTED] > > > > Subject: [PERFORM] Version 7 question > > > > > > > > > > > > I'm just trying to improve performance on version 7 before doing > > > > some tests and hopefully upgrading to 7.3. > > > > > > > > At the moment we have > > > > B=64 (no of shared buffers) > > > > N=32 (no of connections) > > > > in postmaster.opt which I take it is the equivalent of the new > > > > postgresql.conf file. > > > > > > > > From all that is being written about later versions I suspect > > > > that this is far too low. Would I be fairly safe in making the > > > > no of shared buffers larger? Also is there an equivalent of > > > > effective_cache_size that I can set for version 7? > > > > > > > > Many thanks in advance > > > > Hilary > > > > > > > > > > > > > > > > > > > > Hilary Forbes > > > > - > > > > DMR Computer Limited: http://www.dmr.co.uk/ > > > > Direct line: 01689 889950 > > > > Switchboard: (44) 1689 86 Fax: (44) 1689 860330 > > > > E-mail: [EMAIL PROTECTED] > > > > > > > > ** > > > > > > > > > > > > ---(end of > > broadcast)--- > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > > > > ---(end of broadcast)--- > > > TIP 5: Have you checked our extensive FAQ? > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
FW: [PERFORM] Version 7 question
What would be the best value range for effective_cache_size on Postgres 7.3.2, assuming say 1.5 GB of RAM and shared_buffers set to 8192, and shmmax set to 750mb? And what are the most important factors one should take into account in determining the value? > -Original Message- > From: scott.marlowe [SMTP:[EMAIL PROTECTED] > Sent: 01 July 2003 02:56 > To: Michael Mattox > Cc: Hilary Forbes; [EMAIL PROTECTED] > Subject: Re: [PERFORM] Version 7 question > > 8192 is only 64 megs of RAM, not much, but a good number. Keep in mind > that the kernel tends to be better at buffering huge amounts of disk, > while postgresql is better left to use buffers that are large enough for > the current working set (i.e. not your whole database, just the largest > amount of data you're slinging about on a regular basis in one query.) > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768 > (256 megs of ram) to run well, but anything over that doesn't help. Of > course, we don't toss around more than a hundred meg or so at a time. If > > our result sets were in the gigabyte range, I'd A: want more memory and B: > > Give more of it to postgresql. > > The original poster was, I believe running 7.0.x, which is way old, so no, > > I don't think there was an equivalent of effective_cache_size in that > version. Upgrading would be far easier than performance tuning 7.0. since > > the query planner was much simpler (i.e. more prone to make bad decisions) > > in 7.0. > > On Tue, 1 Jul 2003, Michael Mattox wrote: > > > I have my shared buffers at 8192 and my effective cache at 64000 (which > is > > 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and > > I've been asking my boss for another 512megs for over a month now. I > have > > no idea if my buffers are too high/low. > > > > Michael > > > > > -Original Message- > > > From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] Behalf Of Hilary > > > Forbes > > > Sent: Tuesday, July 01, 2003 2:10 PM > > > To: [EMAIL PROTECTED] > > > Subject: [PERFORM] Version 7 question > > > > > > > > > I'm just trying to improve performance on version 7 before doing > > > some tests and hopefully upgrading to 7.3. > > > > > > At the moment we have > > > B=64 (no of shared buffers) > > > N=32 (no of connections) > > > in postmaster.opt which I take it is the equivalent of the new > > > postgresql.conf file. > > > > > > From all that is being written about later versions I suspect > > > that this is far too low. Would I be fairly safe in making the > > > no of shared buffers larger? Also is there an equivalent of > > > effective_cache_size that I can set for version 7? > > > > > > Many thanks in advance > > > Hilary > > > > > > > > > > > > > > > Hilary Forbes > > > - > > > DMR Computer Limited: http://www.dmr.co.uk/ > > > Direct line: 01689 889950 > > > Switchboard: (44) 1689 86 Fax: (44) 1689 860330 > > > E-mail: [EMAIL PROTECTED] > > > > > > ** > > > > > > > > > ---(end of > broadcast)--- > > > TIP 5: Have you checked our extensive FAQ? > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
