Re: [PERFORM] Suspected Postgres Datacorruption

Scott Marlowe writes: > I would upgrade to either 8.2 or 9.0 and here's my reasons. with 8.2 > you still have implicit casts, which your application may depend upon. > Most other changes between 7.4 and 8.2 were pretty small, so if > you've got a lot of implicit casts in your SQL, 8.2 will be th

Re: [PERFORM] Suspected Postgres Datacorruption

On Thu, Aug 4, 2011 at 8:40 PM, Scott Marlowe wrote: > then you should really do so.  The changes to things like autovacuum > getting multi-threaded (8.3) HOT updates (8.3) on disk tracking of Wait, multithreaded autovac may have been put in place in 8.2 . Anyway, my points still stand, just migh

Re: [PERFORM] Suspected Postgres Datacorruption

On Thu, Aug 4, 2011 at 8:33 PM, Sumeet Jauhar wrote: > >    [ Sumeet ] ok so i agree we need to move ahead and shift to a higher > version . But how do we decide that . Which one would you say is the > stablest version of Postgres [ still supported version ] out in the market > below beacuse Brad

Re: [PERFORM] table size is bigger than expected

On 08/04/2011 11:56 AM, Jian Shi wrote: Hey, I'm a new user of PostgreSQL. I found one of my tables is taking unexpectedly large space:... I did vaccum, reindex, the size is still the same. Is there anything else that I can do? Did you try CLUSTER? A basic vacuum only identifies space

Re: [PERFORM] Need to tune for Heavy Write

On 05/08/11 05:40, Samuel Gendler wrote: On Wed, Aug 3, 2011 at 9:56 PM, Adarsh Sharma mailto:adarsh.sha...@orkash.com>> wrote: Dear all, From the last few days, I researched a lot on Postgresql Performance Tuning due to slow speed of my server. My application selects data f

Re: [PERFORM] Suspected Postgres Datacorruption

> -Original Message- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] On Behalf Of Nicholson, Brad (Toronto, ON, CA) > Sent: Thursday, August 04, 2011 5:47 PM > To: Scott Marlowe; Sumeet Jauhar > Cc: pgsql-performance@postgresql.org > Subject

Re: [PERFORM] Postgres 8.4 memory related parameters

On 08/04/2011 04:54 PM, Kevin Grittner wrote: it peaked at 2x physical cores, where it ended up being 60% faster than true cores. Not sure I understand the terminology here -- "physical cores" is counting HT or not? No. So with a dual X5675, that's 12 cores. My numbers peaked at 24-concurre

Re: [PERFORM] Suspected Postgres Datacorruption

> -Original Message- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] On Behalf Of Scott Marlowe > Sent: Thursday, August 04, 2011 5:22 PM > To: Sumeet Jauhar > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Suspected Postgres

Re: [PERFORM] Postgres 8.4 memory related parameters

Shaun Thomas wrote: > it peaked at 2x physical cores, where it ended up being 60% > faster than true cores. Not sure I understand the terminology here -- "physical cores" is counting HT or not? Thanks, -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)

Re: [PERFORM] Postgres 8.4 memory related parameters

On 08/04/2011 04:36 PM, Kevin Grittner wrote: Anyway, I'm always willing to take advantage of the benchmarking work of others, so I'm very curious about where performance topped out for you with HT enabled, and whether disk waits were part of the mix. Hah. Well, it peaked at 2x physical cores,

Re: [PERFORM] Postgres 8.4 memory related parameters

Shaun Thomas wrote: > On 08/04/2011 03:38 PM, Kevin Grittner wrote: > >> You're probably going to get better performance by setting that >> to 2 to 3 times the number of actual cores (don't county >> hyperthreading for this purpose), and using a connection pooler >> to funnel the 600 user connect

Re: [PERFORM] Suspected Postgres Datacorruption

On Wed, Aug 3, 2011 at 1:35 AM, Sumeet Jauhar wrote: > > > Our application is running on Postgres 7.4.X . I agree that this is a very > old version of Postgres and we should have upgraded . The issue that we > faced is that Wow, that is a very old version. It has been out of maintenance for a lo

Re: [PERFORM] Tsearch2 - bad performance with concatenated ts-vectors

Gavin Flower wrote: > I am experimenting with formatting styles, especially relating to > joins. Because I have poor eyesight: visual clues are important, > so that I can focus on key points. Hence the use of > abbreviations, naming conventions, and careful indenting. > So I would like peo

Re: [PERFORM] Postgres 8.4 memory related parameters

On Thu, Aug 4, 2011 at 2:38 PM, Kevin Grittner wrote: > Claire Chang wrote: > >> hi, We recently bought a 4 8core 128G memory database server and I >> am setting it up to replace our old 4 4cores 128G memory database >> server as a master.  The memory related settings that we use on >> the old ma

Re: [PERFORM] Postgres 8.4 memory related parameters

On 08/04/2011 03:38 PM, Kevin Grittner wrote: You're probably going to get better performance by setting that to 2 to 3 times the number of actual cores (don't county hyperthreading for this purpose), and using a connection pooler to funnel the 600 user connections down to a smaller number of da

Re: [PERFORM] Re: GROUP BY with reasonable timings in PLAN but unreasonable execution time

On 08/03/2011 06:29 AM, Robert Haas wrote: b. the Merge Join cost estimator did a poor job with the data it was given: In function eqjoinsel_inner there are two cases (1) ANALYZE data is available for both sides of the join and (2) ANALYZE data is missing for one or both sides. Due to the GROUP

Re: [PERFORM] Performance die when COPYing to table with bigint PK

All you are saying disproves following: in experiment I replaces bigint index: CREATE INDEX ix_t_big ON test.t USING btree (id_big) TABLESPACE tblsp_ix; with 4 (!) other indexes: CREATE INDEX ix_t2 ON test.t USING btree (ip) TABLESPACE tblsp_ix; CREATE INDEX ix_t3 ON test.t USING btree (id_smal

Re: [PERFORM] table size is bigger than expected

On Thu, Aug 4, 2011 at 2:56 PM, Jian Shi wrote: > Hey, > >   I’m a new user of PostgreSQL. I found one of my tables is taking > unexpectedly large space: > > select > pg_size_pretty(pg_relation_size('archive_files')); > >  pg_size_pretty > > > > 1113 MB > > > the field “fname” sto

Re: [PERFORM] Performance die when COPYing to table with bigint PK

If you look at the rest of my mail - you would notice 50 times difference in performance. What you would say? On Thu, Aug 4, 2011 at 7:11 PM, Vitalii Tymchyshyn wrote: > 04.08.11 18:59, Kevin Grittner написав(ла): >> >> Robert Ayrapetyan  wrote: >>> >>> Kevin Grittner  wrote: >> >>> [regarding te

Re: [PERFORM] Performance die when COPYing to table with bigint PK

Hi. Timings for your test: foo=# create table bi (big bigint not null, medium int not null); CREATE TABLE foo=# insert into bi with x(n) as (select generate_series(1, 100)) foo-# select n + 50, n from x; INSERT 0 100 foo=# \timing on Timing is on. foo=# truncate table bi; insert i

Re: [PERFORM] Performance penalty when using WITH

Robert， I've built an index on this expression firstname || ' ' || substring(lastname,1,1). I believe this is the best index for this particular query. Correct me if I am wrong. Li On Aug 3, 2011, at 3:18 AM, Robert Klemme wrote: > On Tue, Aug 2, 2011 at 11:48 PM, Merlin Moncure wrote: >> On

Re: [PERFORM] Parameters for PostgreSQL

On 04/08/11 11:42, Jayadevan M wrote: > Hello, > > >The most important spec has been omitted. What's the storage subsystem? > We have storage on SAN, RAID 5. RAID 5? That's *really* not ideal for database workloads, either Pg or Oracle, unless your RAID 5 storage backend has enough battery-backed

[PERFORM] Suspected Postgres Datacorruption

Hi All , Can you please help me out with the following questions . Our application is running on Postgres 7.4.X . I agree that this is a very old version of Postgres and we should have upgraded . The issue that we faced is that 1 . There was a system crash due to a hardware failure . 2 .

Re: [PERFORM] Tsearch2 - bad performance with concatenated ts-vectors

On 02/08/11 18:22, Jan Wielgus wrote: select participant.participant_id from participant participant join person person on person.person_participant_id = participant.participant_id left join registration registration on registration.registration_registered_participant_id = participant.participan

Re: [PERFORM] Postgres 8.4 memory related parameters

Claire Chang wrote: > hi, We recently bought a 4 8core 128G memory database server and I > am setting it up to replace our old 4 4cores 128G memory database > server as a master. The memory related settings that we use on > the old machine seem a bit wrong according to the experts on IRC: > m

[PERFORM] Postgres 8.4 memory related parameters

hi, We recently bought a 4 8core 128G memory database server and I am setting it up to replace our old 4 4cores 128G memory database server as a master. The memory related settings that we use on the old machine seem a bit wrong according to the experts on IRC: max_connections = 600shared_buffer

[PERFORM] table size is bigger than expected

Hey, I'm a new user of PostgreSQL. I found one of my tables is taking unexpectedly large space: select pg_size_pretty(pg_relation_size('archive_files')); pg_size_pretty 1113 MB The structure of this table is like: Column | Type| Modifiers +-

Re: [PERFORM] Need to tune for Heavy Write

On Wed, Aug 3, 2011 at 9:56 PM, Adarsh Sharma wrote: > Dear all, > > From the last few days, I researched a lot on Postgresql Performance Tuning > due to slow speed of my server. > My application selects data from mysql database about 10 rows , process > it & insert into postgres 2 tables by m

Re: [PERFORM] Performance die when COPYing to table with bigint PK

Robert Ayrapetyan wrote: > If you look at the rest of my mail - you would notice 50 times > difference in performance. > What you would say? That accessing a page from RAM is more than 50 times as fast as a random access of that page from disk. -Kevin -- Sent via pgsql-performance mailing

Re: [PERFORM] Need to tune for Heavy Write

On Thu, Aug 4, 2011 at 6:41 AM, Nicholson, Brad (Toronto, ON, CA) wrote: >> -Original Message- >> From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- >> ow...@postgresql.org] On Behalf Of Scott Marlowe >> Sent: Thursday, August 04, 2011 4:46 AM >> To: Willy-Bas Loos >>

Re: [PERFORM] Performance die when COPYing to table with bigint PK

04.08.11 18:59, Kevin Grittner написав(ла): Robert Ayrapetyan wrote: Kevin Grittner wrote: [regarding tests which do show the problem] tried same with 2 columns (bigint and int) - it didn't produced such effect probably because data volume has critical effect. Based on what you're showing

Re: [PERFORM] Performance die when COPYing to table with bigint PK

Robert Ayrapetyan wrote: > Kevin Grittner wrote: >> What timings do you get for the insert statements if you run the >> following in your environment? >> Here's what I get: >> >> Time: 1629.141 ms >> Time: 1638.060 ms >> Time: 1711.833 ms >> >> Time: 4151.953 ms >> Time: 4602.679 ms >> Time:

Re: [PERFORM] Seq Scan vs. Index Scan

Nassib Nassar wrote: > In this example it looks to me like the planner is choosing a Seq > Scan resulting in 18x running time compared to running it with > enable_seqscan = 'off'. I would try these settings: random_page_cost = 2 cpu_tuple_cost = 0.02 Based on your estimated cost versus ac

Re: [PERFORM] Need to tune for Heavy Write

On Thu, Aug 4, 2011 at 7:57 AM, Kevin Grittner wrote: >> RAM : 16 GB > >> effective_cache_size = 4096MB > > That should probably be more like 12GB to 15GB.  It probably won't > affect the load time here, but could affect other queries. Actually on a heavily written database a large effective cac

Re: [PERFORM] Parameters for PostgreSQL

On 8/3/2011 11:03 PM, Craig Ringer wrote: great gobs of battery backed write cache DRAM. Now I know what I'm asking Santa for Christmas this year! -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [PERFORM] Need to tune for Heavy Write

Adarsh Sharma wrote: > Postgres : 8.4.2 You should definitely update to a more recent bug patch level: http://www.postgresql.org/support/versioning > RAM : 16 GB > effective_cache_size = 4096MB That should probably be more like 12GB to 15GB. It probably won't affect the load time here

Re: [PERFORM] Need to tune for Heavy Write

On 4/08/2011 12:56 PM, Adarsh Sharma wrote: Dear all, From the last few days, I researched a lot on Postgresql Performance Tuning due to slow speed of my server. My application selects data from mysql database about 10 rows , process it & insert into postgres 2 tables by making about 45 con

[PERFORM] Seq Scan vs. Index Scan

In this example it looks to me like the planner is choosing a Seq Scan resulting in 18x running time compared to running it with enable_seqscan = 'off'. Adding more indexes to public.gene (please see below) seemed to make things worse. I definitely have run VACUUM ANALYZE on everything, manual

Re: [PERFORM] Need to tune for Heavy Write

> -Original Message- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] On Behalf Of Scott Marlowe > Sent: Thursday, August 04, 2011 4:46 AM > To: Willy-Bas Loos > Cc: Adarsh Sharma; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Nee

Re: [PERFORM] Need to tune for Heavy Write

Scott is right. His answer solves the problem in the long run. Even if your write load increases, it will perform fast enough. For now try increasing checkpoint_segments size, restart Postgres for new settings to take effect and try again with your write load. If you are not satisfied with write

Re: [PERFORM] Need to tune for Heavy Write

On Thu, Aug 4, 2011 at 2:34 AM, Willy-Bas Loos wrote: > On Thu, Aug 4, 2011 at 6:56 AM, Adarsh Sharma > wrote: >> After this I change my pg_xlog directory to a separate directory other than >> data directory by symlinking. >>(...) >> Please let me know if I missing any other important configurat

Re: [PERFORM] Need to tune for Heavy Write

To put it simple, you need to set checkpoint_segments way higher than your current value! Link: wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server On Aug 4, 2011 6:57 AM, "Adarsh Sharma" wrote:

Re: [PERFORM] Need to tune for Heavy Write

On Thu, Aug 4, 2011 at 6:56 AM, Adarsh Sharma wrote: > After this I change my pg_xlog directory to a separate directory other than > data directory by symlinking. >(...) > Please let me know if I missing any other important configuration. Moving the pg_xlog to a different directory only helps whe

Re: [PERFORM] Need to tune for Heavy Write

Hi Adarsh, Have you set checkpoint_segments and checkpoint_completion_target the right way? Tuning these parameters are a MUST if you want good write performance. See this link for more information: http://www.postgresql.org/docs/current/static/runtime-config-wal.html