Re: [PERFORM] Forcing index usage without 'enable_hashjoin = FALSE'

[Chris]

Dan Langille wrote:

I'm using PostgreSQL 8.1.4 and I'm trying to force the planner to use
an index.  With the index, I get executions times of 0.5 seconds. 
Without, it's closer to 2.5 seconds.


Compare these two sets of results (also provided at 
http://rafb.net/paste/results/ywcOZP66.html

should it appear poorly formatted below):

freshports.org=# \i test2.sql
 
QUERY PLAN

--
--
-
 Merge Join  (cost=24030.39..24091.43 rows=3028 width=206) (actual 
time=301.301..355.261 rows=3149 loops=1)

   Merge Cond: ("outer".id = "inner".category_id)
   ->  Sort  (cost=11.17..11.41 rows=97 width=4) (actual 
time=0.954..1.300 rows=95 loops=1)

 Sort Key: c.id
 ->  Seq Scan on categories c  (cost=0.00..7.97 rows=97 
width=4) (actual time=0.092..0.517 rows=97 loops=1)
   ->  Sort  (cost=24019.22..24026.79 rows=3028 width=206) (actual 
time=300.317..314.114 rows=3149 loops=1)

 Sort Key: p.category_id
 ->  Nested Loop  (cost=0.00..23844.14 rows=3028 width=206) 
(actual time=0.082..264.459 rows=3149 loops=1)
   ->  Seq Scan on ports p  (cost=0.00..6141.11 rows=3028 
width=206) (actual time=0.026..133.575 rows=3149 loops=1)

 Filter: (status = 'D'::bpchar)
   ->  Index Scan using element_pkey on element e  
(cost=0.00..5.83 rows=1 width=4) (actual time=0.022..0.026 rows=1 
loops=3149)

 Index Cond: ("outer".element_id = e.id)
 Total runtime: 369.869 ms
(13 rows)

freshports.org=# set enable_hashjoin = true;
SET
freshports.org=# \i test2.sql
   QUERY PLAN
--
--
 Hash Join  (cost=6156.90..13541.14 rows=3028 width=206) (actual 
time=154.741..2334.366 rows=3149 loops=1)

   Hash Cond: ("outer".category_id = "inner".id)
   ->  Hash Join  (cost=6148.68..13472.36 rows=3028 width=206) 
(actual time=153.801..2288.792 rows=3149 loops=1)

 Hash Cond: ("outer".id = "inner".element_id)
 ->  Seq Scan on element e  (cost=0.00..4766.70 rows=252670 
width=4) (actual time=0.022..1062.626 rows=252670 loops=1)
 ->  Hash  (cost=6141.11..6141.11 rows=3028 width=206) 
(actual time=151.105..151.105 rows=3149 loops=1)
   ->  Seq Scan on ports p  (cost=0.00..6141.11 rows=3028 
width=206) (actual time=0.027..131.072 rows=3149 loops=1)

 Filter: (status = 'D'::bpchar)
   ->  Hash  (cost=7.97..7.97 rows=97 width=4) (actual 
time=0.885..0.885 rows=97 loops=1)
 ->  Seq Scan on categories c  (cost=0.00..7.97 rows=97 
width=4) (actual time=0.076..0.476 rows=97 loops=1)

 Total runtime: 2346.877 ms
(11 rows)

freshports.org=#

Without leaving "enable_hashjoin = false", can you suggest a way to 
force the index usage?


FYI, the query is:

explain analyse
SELECT P.id,
   P.category_id,
   P.version as version,
   P.revisionas revision,
   P.element_id,
   P.maintainer,
   P.short_description,
   to_char(P.date_added - SystemTimeAdjust(), 'DD Mon  
HH24:MI:SS') as date_added,

   P.last_commit_id  as last_change_log_id,
   P.package_exists,
   P.extract_suffix,
   P.homepage,
   P.status,
   P.broken,
   P.forbidden,
   P.ignore,
   P.restricted,
   P.deprecated,
   P.no_cdrom,
   P.expiration_date,
   P.latest_link
  FROM categories C, ports P JOIN element E on P.element_id = E.id
 WHERE P.status  = 'D'
   AND P.category_id = C.id;



I doubt it would make a difference but if you:

...
FROM categories C JOIN ports P on P.category_id=C.id JOIN element E on 
P.element_id = E.id

WHERE P.status  = 'D';

does it change anything?

--
Postgresql & php tutorials
http://www.designmagick.com/

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Query tuning

[Chris]

Subbiah, Stalin wrote:

Actually these servers will be upgraded to 8.1.4 in couple of months.


even so, you could get some bad data in there.
http://www.postgresql.org/docs/8.0/static/release.html . Go through the 
old release notes and you'll find various race conditions, crashes etc.



Here you go with explain analyze.

# explain analyze SELECT *
FROM EVENTLOG 
WHERE EVENTTIME>'07/23/06 16:00:00' AND  EVENTTIME<'08/22/06 16:00:00' 
AND  (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA' 
OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA' 
OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA') 
ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 500;
 
QUERY PLAN






-
 Limit  (cost=15583110.14..15583111.39 rows=500 width=327) (actual
time=427771.568..427772.904 rows=500 loops=1)
   ->  Sort  (cost=15583108.89..15618188.88 rows=14031998 width=327)
(actual time=427770.504..427771.894 rows=1000 loops=1)
 Sort Key: eventtime, sequencenum
 ->  Seq Scan on eventlog  (cost=0.00..2334535.17 rows=14031998
width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
   Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
without time zone) AND (((objdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
 Total runtime: 437884.134 ms
(6 rows)


If you analyze the table then run this again what plan does it come back 
with?


I can't read explain output properly but I suspect (and I'm sure I'll be 
corrected if need be) that the sort step is way out of whack and so is 
the seq scan because the stats aren't up to date enough.


Do you have an index on objdomainid, objid and userdomainid (one index 
per field) ? I wonder if that will help much.


--
Postgresql & php tutorials
http://www.designmagick.com/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Query tuning

[Subbiah, Stalin]

Actually these servers will be upgraded to 8.1.4 in couple of months.

Here you go with explain analyze.

# explain analyze SELECT *
FROM EVENTLOG 
WHERE EVENTTIME>'07/23/06 16:00:00' AND  EVENTTIME<'08/22/06 16:00:00' 
AND  (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA' 
OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA' 
OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA') 
ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 500;
 
QUERY PLAN





-
 Limit  (cost=15583110.14..15583111.39 rows=500 width=327) (actual
time=427771.568..427772.904 rows=500 loops=1)
   ->  Sort  (cost=15583108.89..15618188.88 rows=14031998 width=327)
(actual time=427770.504..427771.894 rows=1000 loops=1)
 Sort Key: eventtime, sequencenum
 ->  Seq Scan on eventlog  (cost=0.00..2334535.17 rows=14031998
width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
   Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
without time zone) AND (((objdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
 Total runtime: 437884.134 ms
(6 rows)

-Original Message-
From: Chris [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 6:37 PM
To: Subbiah, Stalin
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query tuning

Subbiah, Stalin wrote:
> Hello All,
> 
> This query runs forever and ever. Nature of this table being lots of 
> inserts/deletes/query, I vacuum it every half hour to keep the holes 
> reusable and nightly once vacuum analyze to update the optimizer. 
> We've got index on eventtime only. Running it for current day uses 
> index range scan and it runs within acceptable time. Below is the 
> explain of the query. Is the order by sequencenum desc prevents from 
> applying limit optimization?
> 
> explain SELECT *
> FROM EVENTLOG
> WHERE EVENTTIME>'07/23/06 16:00:00' 
> AND  EVENTTIME<'08/22/06 16:00:00' 
> AND  (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA' 
> OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA' 
> OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
> ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 0;
>  
> QUERY PLAN
> 
> --
> --
> --
> --
> --
> --
> --
> --
> -
>  Limit  (cost=15546930.29..15546931.54 rows=500 width=327)
>->  Sort  (cost=15546930.29..15581924.84 rows=13997819 width=327)
>  Sort Key: eventtime, sequencenum
>  ->  Seq Scan on eventlog  (cost=0.00..2332700.25 
> rows=13997819
> width=327)
>Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp 
> without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp 
> without time zone) AND (((objdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> (5 rows)
> 
> Thanks,
> Stalin
> Pg version 8.0.1, suse 64bit.

Firstly you should update to 8.0.8 - because it's in the same stream you
won't need to do a dump/initdb/reload like a major version change, it
should be a simple upgrade.

Can you send explain analyze instead of just explain?

It sounds like you're not analyz'ing enough - if you're doing lots of
updates/deletes/inserts then the statistics postgresql uses to choose
whether to do an index scan or something else will quickly be outdated
and so it'll have to go back to a full table scan every time..

Can you set up autovacuum to handle that for you more regularly?

--
Postgresql & php tutorials
http://www.designmagick.com/

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Moving a tablespace

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Tue, Aug 22, 2006 at 06:16:54PM -0700, Craig A. James wrote:
>> Is there a way to move a tablespace to a new location without a 
>> dump/restore?

> The last paragraph of the Tablespaces documentation might be helpful:
> http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html

> I just tested this and it appeared to work, but this hypothetical
> DBA might want to wait for others to comment before proceeding.

AFAIK it works fine.  Shut down postmaster, move tablespace's directory
tree somewhere else, fix the symbolic link in $PGDATA/pg_tblspc, start
postmaster, update the pg_tablespace entry.  There isn't anyplace else
in Postgres that knows where that link leads.  But if you are running
a hot PITR backup, see the caveats in TFM about what will happen on the
backup machine.

> He might also want to initdb and populate a test cluster and practice
> the procedure before doing it for real.

"Always mount a scratch monkey" ...

regards, tom lane

---(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: [PERFORM] Query tuning

[Chris]

Subbiah, Stalin wrote:

Hello All,

This query runs forever and ever. Nature of this table being lots of
inserts/deletes/query, I vacuum it every half hour to keep the holes
reusable and nightly once vacuum analyze to update the optimizer. We've
got index on eventtime only. Running it for current day uses index range
scan and it runs within acceptable time. Below is the explain of the
query. Is the order by sequencenum desc prevents from applying limit
optimization?

explain SELECT *
FROM EVENTLOG 
WHERE EVENTTIME>'07/23/06 16:00:00' 
AND  EVENTTIME<'08/22/06 16:00:00' 
AND  (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA' 
OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA' 
OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA') 
ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 0;
 
QUERY PLAN






-
 Limit  (cost=15546930.29..15546931.54 rows=500 width=327)
   ->  Sort  (cost=15546930.29..15581924.84 rows=13997819 width=327)
 Sort Key: eventtime, sequencenum
 ->  Seq Scan on eventlog  (cost=0.00..2332700.25 rows=13997819
width=327)
   Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
without time zone) AND (((objdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
(5 rows)

Thanks,
Stalin
Pg version 8.0.1, suse 64bit.


Firstly you should update to 8.0.8 - because it's in the same stream you 
won't need to do a dump/initdb/reload like a major version change, it 
should be a simple upgrade.


Can you send explain analyze instead of just explain?

It sounds like you're not analyz'ing enough - if you're doing lots of 
updates/deletes/inserts then the statistics postgresql uses to choose 
whether to do an index scan or something else will quickly be outdated 
and so it'll have to go back to a full table scan every time..


Can you set up autovacuum to handle that for you more regularly?

--
Postgresql & php tutorials
http://www.designmagick.com/

---(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: [PERFORM] Moving a tablespace

[Michael Fuhr]

On Tue, Aug 22, 2006 at 06:16:54PM -0700, Craig A. James wrote:
> Is there a way to move a tablespace to a new location without a 
> dump/restore?  I, er, this hypothetical guy, knows he can move it and put a 
> symbolic link in for /disk2, but this is somewhat unsatisfactory since 
> "/disk2" would have to exist forever.

The last paragraph of the Tablespaces documentation might be helpful:

http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html

"The directory $PGDATA/pg_tblspc contains symbolic links that point
to each of the non-built-in tablespaces defined in the cluster.
Although not recommended, it is possible to adjust the tablespace
layout by hand by redefining these links.  Two warnings: do not do
so while the postmaster is running; and after you restart the
postmaster, update the pg_tablespace catalog to show the new
locations.  (If you do not, pg_dump will continue to show the old
tablespace locations.)"

I just tested this and it appeared to work, but this hypothetical
DBA might want to wait for others to comment before proceeding.  He
might also want to initdb and populate a test cluster and practice
the procedure before doing it for real.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[PERFORM] Moving a tablespace

[Craig A. James]

Suppose, hypothetically of course, someone lacked foresight, and put a tablespace somewhere with a 
dumb name, like "/disk2", instead of using a symbolic link with a more descriptive name.  
 And then /disk2 needs to be renamed, say to "/postgres_data", and this (hypothetical) 
DBA realizes he has made a dumb mistake.

Is there a way to move a tablespace to a new location without a dump/restore?  I, er, 
this hypothetical guy, knows he can move it and put a symbolic link in for /disk2, but 
this is somewhat unsatisfactory since "/disk2" would have to exist forever.

Thanks,
Craig

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

[PERFORM] Query tuning

[Subbiah, Stalin]

Hello All,

This query runs forever and ever. Nature of this table being lots of
inserts/deletes/query, I vacuum it every half hour to keep the holes
reusable and nightly once vacuum analyze to update the optimizer. We've
got index on eventtime only. Running it for current day uses index range
scan and it runs within acceptable time. Below is the explain of the
query. Is the order by sequencenum desc prevents from applying limit
optimization?

explain SELECT *
FROM EVENTLOG 
WHERE EVENTTIME>'07/23/06 16:00:00' 
AND  EVENTTIME<'08/22/06 16:00:00' 
AND  (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA' 
OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA' 
OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA') 
ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 0;
 
QUERY PLAN





-
 Limit  (cost=15546930.29..15546931.54 rows=500 width=327)
   ->  Sort  (cost=15546930.29..15581924.84 rows=13997819 width=327)
 Sort Key: eventtime, sequencenum
 ->  Seq Scan on eventlog  (cost=0.00..2332700.25 rows=13997819
width=327)
   Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
without time zone) AND (((objdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
(5 rows)

Thanks,
Stalin
Pg version 8.0.1, suse 64bit.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] PowerEdge 2950 questions

[Jeff Davis]

On Tue, 2006-08-22 at 17:56 -0400, Bucky Jordan wrote:
> Hi Jeff,
> 
> My experience with the 2950 seemed to indicate that RAID10x6 disks did
> not perform as well as RAID5x6. I believe I posted some numbers to
> illustrate this in the post you mentioned. 
> 

Very interesting. I always hear that people avoid RAID 5 on database
servers, but I suppose it always depends. Is the parity calculation
something that may increase commit latency vs. a RAID 10? That's
normally the explanation that I get.

> If I remember correctly, the numbers were pretty close, but I was
> expecting RAID10 to significantly beat RAID5. However, with 6 disks,
> RAID5 starts performing a little better, and it also has good storage
> utilization (i.e. you're only loosing 1 disk's worth of storage, so with
> 6 drives, you still have 83% - 5/6 - of your storage available, as
> opposed to 50% with RAID10). 

Right, RAID 5 is certainly tempting since I get so much more storage.

> Keep in mind that with 6 disks, theoretically (your mileage may vary by
> raid controller implementation) you have more fault tolerance with
> RAID10 than with RAID5.

I'll also have the Slony system, so I think my degree of safety is still
quite high with RAID-5.

> Also, I don't think there's a lot of performance gain to going with the
> 15k drives over the 10k. Even dell only says a 10% boost. I've
> benchmarked a single drive configuration, 10k vs 15k rpm, and yes, the
> 15k had substantially better seek times, but raw io isn't much
> different, so again, it depends on your application's needs.

Do you think the seek time may affect transaction commit time though,
rather than just throughput? Or does it not make much difference since
we have writeback?

> Lastly, re your question on putting the WAL on the RAID10- I currently
> have the box setup as RAID5x6 with the WAL and PGDATA all on the same
> raidset. I haven't had the chance to do extensive tests, but from
> previous readings, I gather that if you have write-back enabled on the
> RAID, it should be ok (which it is in my case).

Ok, I won't worry about that then.

> As to how this compares with an Opteron system, if someone has some
> pgbench (or other test) suggestions and a box to compare with, I'd be
> happy to run the same on the 2950. (The 2950 is a 2-cpu dual core 3.0
> ghz box, 8GB ram with 6 disks, running FreeBSD 6.1 amd64 RELEASE if
> you're interested in picking a "fair" opteron equivalent ;)
> 

Based on your results, I think the Intels should be fine. Does each of
the cores have independent access to memory (therefore making memory
access more parallel)?

Thanks very much for the information!

Regards,
Jeff Davis


---(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: [PERFORM] VACUUM FULL needed sometimes to prevent transaction

[Jeff Davis]

On Tue, 2006-08-22 at 20:10 +0200, Marinos Yannikos wrote:
> Hello,
> 
> we're looking into the reason why we are getting warnings about 
> transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is 
> claiming that VACUUM without FULL cannot reassign XIDs properly when 
> max_fsm_pages was set too low (it says so here too, but this is rather 
> old: http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is 
> this true, or do we have a different issue here? We're using 8.1.3 with 
> a database generated on 8.1.3 (i.e. not migrated from 7.x or anything 
> like that).

Usually this is caused by either:
(1) You're not vacuuming as a superuser, so it's not able to vacuum
everything.
(2) You have a long-running transaction that never completed for some
strange reason.

Hope this helps,
Jeff Davis


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] PowerEdge 2950 questions

[Bucky Jordan]

Hi Jeff,

My experience with the 2950 seemed to indicate that RAID10x6 disks did
not perform as well as RAID5x6. I believe I posted some numbers to
illustrate this in the post you mentioned. 

If I remember correctly, the numbers were pretty close, but I was
expecting RAID10 to significantly beat RAID5. However, with 6 disks,
RAID5 starts performing a little better, and it also has good storage
utilization (i.e. you're only loosing 1 disk's worth of storage, so with
6 drives, you still have 83% - 5/6 - of your storage available, as
opposed to 50% with RAID10). 

Keep in mind that with 6 disks, theoretically (your mileage may vary by
raid controller implementation) you have more fault tolerance with
RAID10 than with RAID5.

Also, I don't think there's a lot of performance gain to going with the
15k drives over the 10k. Even dell only says a 10% boost. I've
benchmarked a single drive configuration, 10k vs 15k rpm, and yes, the
15k had substantially better seek times, but raw io isn't much
different, so again, it depends on your application's needs.

Lastly, re your question on putting the WAL on the RAID10- I currently
have the box setup as RAID5x6 with the WAL and PGDATA all on the same
raidset. I haven't had the chance to do extensive tests, but from
previous readings, I gather that if you have write-back enabled on the
RAID, it should be ok (which it is in my case).

As to how this compares with an Opteron system, if someone has some
pgbench (or other test) suggestions and a box to compare with, I'd be
happy to run the same on the 2950. (The 2950 is a 2-cpu dual core 3.0
ghz box, 8GB ram with 6 disks, running FreeBSD 6.1 amd64 RELEASE if
you're interested in picking a "fair" opteron equivalent ;)

Thanks,

Bucky




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jeff Davis
Sent: Tuesday, August 22, 2006 5:34 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] PowerEdge 2950 questions

This question is related to the thread:
http://archives.postgresql.org/pgsql-performance/2006-08/msg00152.php
but I had some questions.

I am looking at setting up two general-purpose database servers,
replicated with Slony. Each server I'm looking at has the following
specs:

Dell PowerEdge 2950
- 2 x Dual Core Intel(r) Xeon(r) 5130, 4MB Cache, 2.00GHz, 1333MHZ FSB
- 4GB RAM
- PERC 5/i, x6 Backplane, Integrated Controller Card (256MB battery-
backed cache)
- 6 x 73GB, SAS, 3.5-inch, 15K RPM Hard Drive arranged in RAID 10

These servers are reasonably priced and so they seem like a good choice
for the overall price, and the above thread indicated good performance.
However, I want to make sure that putting WAL in with PGDATA on the
RAID-10 is wise. And if there are any other suggestions that would be
great. Is the RAID controller good? Are the processors good for database
work or are Opterons significantly better?

I may go for more storage as well (i.e. getting 300GB disks), but I am
still determining the potential need for storage. I can get more RAM at
a later date if necessary also.

Regards,
Jeff Davis


---(end of broadcast)---
TIP 6: explain analyze is your friend

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

[PERFORM] PowerEdge 2950 questions

[Jeff Davis]

This question is related to the thread:
http://archives.postgresql.org/pgsql-performance/2006-08/msg00152.php
but I had some questions.

I am looking at setting up two general-purpose database servers,
replicated with Slony. Each server I'm looking at has the following
specs:

Dell PowerEdge 2950
- 2 x Dual Core Intel® Xeon® 5130, 4MB Cache, 2.00GHz, 1333MHZ FSB
- 4GB RAM
- PERC 5/i, x6 Backplane, Integrated Controller Card (256MB battery-
backed cache)
- 6 x 73GB, SAS, 3.5-inch, 15K RPM Hard Drive arranged in RAID 10

These servers are reasonably priced and so they seem like a good choice
for the overall price, and the above thread indicated good performance.
However, I want to make sure that putting WAL in with PGDATA on the
RAID-10 is wise. And if there are any other suggestions that would be
great. Is the RAID controller good? Are the processors good for database
work or are Opterons significantly better?

I may go for more storage as well (i.e. getting 300GB disks), but I am
still determining the potential need for storage. I can get more RAM at
a later date if necessary also.

Regards,
Jeff Davis


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

[Tom Lane]

Ulrich Habel <[EMAIL PROTECTED]> writes:
> Anythings speeks against this hack?

Only that it was done years ago.

As Alvaro mentions, if you are using a non-C locale then you need
non-default index opclasses to get it to work.  Non-C locales usually
have index sort orders that don't play nice with this conversion.

regards, tom lane

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

[PERFORM] Benchmarks

[Charles Sprickman]

Hi all,

I'm really glad to see all the test results people are posting here.  In 
fact, I used info from the archives to put together our first "big" 
database host:


-Tyan dual-core/dual-cpu mainboard (
-One Opteron 270 2.0GHz (although our vendor gave us two for some reason)
-Chenbro 3U case (RM31212B) - OK, but not very well thought-out
-8 Seagate SATA drives (yes, we stuck with our vendor of choice, WD 
Raptors may have been a better choice)

-3Ware 9550SX-12MI
-2GB RAM (we'll get more when we need it)

So this thing is sitting next to my desk and I'd like to see just how this 
compares to other hardware.  We already know that it will blow away our 
normal dual-xeon 1Us with just two U320 drives on Adaptec 2120s ZCR cards. 
We also know that for what this box will be doing (mailing list archives 
with msgs stored in Postgres) it's going to be more than enough for the 
next few years...


So what are people using to get a general feel for the bang/buck ratio? 
I've toyed with Bonnie, IOZone and simple "dd" writes.  I'd like to go a 
little further and actually hit Postgres to see how the entire system 
performs.  My reasons are, in no particular order:


-to learn something (general and pgsql tuning)
-to help guide future database server builds
-to take the benchmark data and share it somewhere

The first one is obvious.  Matching software to hardware is really hard 
and there aren't too many people that can do it well.


The second is a pretty big deal - we've been doing all 1U builds and 
currently spread our load amongst individual db servers that also do the 
web front end for mailing list management.  This has worked OK, but we may 
want to peel off the db section and start moving towards two large boxes 
like this with one replicating the other as a backup.


That last one is a stickler.  I've seen so much data posted on this list, 
is there any project in the works to collect this?  It seems like some 
RAID hardware just totally sucks (cough *Adaptec* cough).  Having a site 
that listed results for the more common benchmarks and sorting it out by 
hardware would help reduce the number of people that get burned by buying 
overpriced/underperforming RAID controllers/SANs.


Any thoughts on all this?

I'll be throwing in some quick stats on the box described above later 
today...  At first glance, the 3Ware controller is really looking like an 
excellent value.


Thanks,

Charles

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] How to get higher tps

[Marty Jia]

Here is, it's first time I got tps > 400

10 clients:

[EMAIL PROTECTED]:/pgsql/database]pgbench -c 10 -t 1 -v -d pgbench
2>/dev/null
pghost:  pgport: (null) nclients: 10 nxacts: 1 dbName: pgbench
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 10
number of transactions per client: 1
number of transactions actually processed: 10/10
tps = 413.022562 (including connections establishing)
tps = 413.125733 (excluding connections establishing)

20 clients:

[EMAIL PROTECTED]:/pgsql/database]pgbench -c 20 -t 1 -v -d pgbench
2>/dev/null
pghost:  pgport: (null) nclients: 20 nxacts: 1 dbName: pgbench
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 20
number of transactions per client: 1
number of transactions actually processed: 20/20
tps = 220.759983 (including connections establishing)
tps = 220.790077 (excluding connections establishing)
 

-Original Message-
From: Joshua D. Drake [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 3:38 PM
To: Marty Jia
Cc: Bucky Jordan; Alex Turner; Mark Lewis;
pgsql-performance@postgresql.org; DBAs; Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps

Marty Jia wrote:
> Bucky
> 
> My best result is around 380. I believe your hardware is more 
> efficient, because no matter how I change the conf parameters, no 
> improvement can be obtained. I even turned fsync off.

Do you stay constant if you use 40 clients versus 20?

> 
> What is your values for the following parameters?
> 
> shared_buffers = 8
> max_fsm_pages = 35
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 61
> random_page_cost = 3
> 
> Thanks
> Marty
> 
> -Original Message-
> From: Bucky Jordan [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 22, 2006 3:23 PM
> To: Joshua D. Drake; Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs; 
> Rich Wilson; Ernest Wurzbach
> Subject: RE: [PERFORM] How to get higher tps
> 
> Marty,
> 
> Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install

> on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
> cache/socket) with 6x300GB 10k SAS drives:
> 
> pgbench -c 10 -t 1 -d bench 2>/dev/null
> pghost:  pgport: (null) nclients: 10 nxacts: 1 dbName: bench 
> `transaction type: TPC-B (sort of) scaling factor: 20 number of
clients:
> 10 number of transactions per client: 1 number of transactions 
> actually processed: 10/10 tps = 561.056729 (including 
> connections establishing) tps = 561.127760 (excluding connections
> establishing)
> 
> Here's some iostat samples during the test:
>   tty   mfid0  da0  cd0
> cpu
>  tin tout  KB/t tps  MB/s   KB/t tps  MB/s   KB/t tps  MB/s  us ni sy
in
> id
>6   77 16.01 1642 25.67   0.00   0  0.00   0.00   0  0.00   3  0  8
> 2 87
>8  157 17.48 3541 60.43   0.00   0  0.00   0.00   0  0.00  24  0 28
> 4 43
>5  673 17.66 2287 39.44   0.00   0  0.00   0.00   0  0.00  10  0 13
> 2 75
>6 2818 16.37 2733 43.68   0.00   0  0.00   0.00   0  0.00  17  0 23
> 3 56
>1  765 18.05 2401 42.32   0.00   0  0.00   0.00   0  0.00  15  0 17
> 3 65
> 
> Note- the above was with no tuning to the kernel or postgresql.conf. 
> 
> Now for my question- it seems that I've still got quite a bit of 
> headroom on the hardware I'm running the above tests on, since I know 
> the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU 
> appears mostly idle. This would indicate I should be able to get some 
> significantly better numbers with postgresql.conf tweaks correct?
> 
> I guess the other problem is ensuring that we're not testing RAM 
> speeds, since most of the data is probably in memory (BSD io buffers)?

> Although, for the initial run, that doesn't seem to be the case, since

> subsequent runs without rebuilding the benchmark db are slightly not 
> believable (i.e. 1,200 going up to >2,500 tps over 5 back-to-back 
> runs). So, as long as I re-initialize the benchdb before each run, it 
> should be a realistic test, right?
> 
> Thanks,
> 
> Bucky
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Joshua D.
> Drake
> Sent: Tuesday, August 22, 2006 12:16 PM
> To: Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs; 
> Rich Wilson; Ernest Wurzbach
> Subject: Re: [PERFORM] How to get higher tps
> 
> Marty Jia wrote:
>> Here is iostat when running pgbench:
>>  
>> avg-cpu:  %user   %nice%sys %iowait   %idle
>>   26.170.008.25   23.17   42.42
> 
> You are are a little io bound and fairly cpu bound. I would be curious

> if your performance goes down if you increase the number of 
> connections you are using.
> 
> Joshua D. Drake
> 
> 
>>  
>> Device:tps   Blk_read/s   Blk_wrtn/s   Blk_read
Blk_wrtn
>> sda   0.00 0.00 0.00  0
0
>> sda1  

Re: [PERFORM] How to get higher tps

[Bucky Jordan]

As I mentioned, I haven't changed the defaults at all yet:
Fsync is still on... 

shared_buffers = 1000
max_fsm_pages = 2
max_connections = 40
work_mem = 1024
effective_cache_size = 1000
random_page_cost = 4

I'm not sure how much the dual core woodcrests and faster memory are
helping my system. Your hardware should *theoretically* have better IO
performance, assuming you're actually making use of the 2x2GB/s FC
interfaces and external RAID.

What do you get if you run the bench back-to-back without rebuilding the
test db? (Say pgbench -c 10 -t 1 -d bench 2>/dev/null run 5 times in
a row)? Maybe that would put more stress on RAM/CPU?

Seems to me your issue is with an underperforming IO subsystem- as
previously mentioned, you might want to check dd and bonnie++ (v 1.03)
numbers.

time bash -c "(dd if=/dev/zero of=bigfile count=125000 bs=8k && sync)"

I get ~255 mb/s from the above.

Bucky 

-Original Message-
From: Marty Jia [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 3:38 PM
To: Bucky Jordan; Joshua D. Drake
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: RE: [PERFORM] How to get higher tps

Bucky

My best result is around 380. I believe your hardware is more efficient,
because no matter how I change the conf parameters, no improvement can
be obtained. I even turned fsync off.

What is your values for the following parameters?

shared_buffers = 8
max_fsm_pages = 35
max_connections = 1000
work_mem = 65536
effective_cache_size = 61
random_page_cost = 3

Thanks
Marty

-Original Message-
From: Bucky Jordan [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 3:23 PM
To: Joshua D. Drake; Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: RE: [PERFORM] How to get higher tps

Marty,

Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
cache/socket) with 6x300GB 10k SAS drives:

pgbench -c 10 -t 1 -d bench 2>/dev/null
pghost:  pgport: (null) nclients: 10 nxacts: 1 dbName: bench
`transaction type: TPC-B (sort of) scaling factor: 20 number of clients:
10 number of transactions per client: 1 number of transactions
actually processed: 10/10 tps = 561.056729 (including
connections establishing) tps = 561.127760 (excluding connections
establishing)

Here's some iostat samples during the test:
  tty   mfid0  da0  cd0
cpu
 tin tout  KB/t tps  MB/s   KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in
id
   6   77 16.01 1642 25.67   0.00   0  0.00   0.00   0  0.00   3  0  8
2 87
   8  157 17.48 3541 60.43   0.00   0  0.00   0.00   0  0.00  24  0 28
4 43
   5  673 17.66 2287 39.44   0.00   0  0.00   0.00   0  0.00  10  0 13
2 75
   6 2818 16.37 2733 43.68   0.00   0  0.00   0.00   0  0.00  17  0 23
3 56
   1  765 18.05 2401 42.32   0.00   0  0.00   0.00   0  0.00  15  0 17
3 65

Note- the above was with no tuning to the kernel or postgresql.conf. 

Now for my question- it seems that I've still got quite a bit of
headroom on the hardware I'm running the above tests on, since I know
the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
appears mostly idle. This would indicate I should be able to get some
significantly better numbers with postgresql.conf tweaks correct?

I guess the other problem is ensuring that we're not testing RAM speeds,
since most of the data is probably in memory (BSD io buffers)? Although,
for the initial run, that doesn't seem to be the case, since subsequent
runs without rebuilding the benchmark db are slightly not believable
(i.e. 1,200 going up to >2,500 tps over 5 back-to-back runs). So, as
long as I re-initialize the benchdb before each run, it should be a
realistic test, right?

Thanks,

Bucky
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joshua D.
Drake
Sent: Tuesday, August 22, 2006 12:16 PM
To: Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps

Marty Jia wrote:
> Here is iostat when running pgbench:
>  
> avg-cpu:  %user   %nice%sys %iowait   %idle
>   26.170.008.25   23.17   42.42

You are are a little io bound and fairly cpu bound. I would be curious
if your performance goes down if you increase the number of connections
you are using.

Joshua D. Drake


>  
> Device:tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
> sda   0.00 0.00 0.00  0  0
> sda1  0.00 0.00 0.00  0  0
> sda2  0.00 0.00 0.00  0  0
> sda3  0.00 0.00 0.00  0  0
> sda4  0.00 0.00 0.00  0  0
> sda5   

Re: [PERFORM] How to get higher tps

[Joshua D. Drake]

Marty Jia wrote:

Bucky

My best result is around 380. I believe your hardware is more efficient,
because no matter how I change the conf parameters, no improvement can
be obtained. I even turned fsync off.


Do you stay constant if you use 40 clients versus 20?



What is your values for the following parameters?

shared_buffers = 8
max_fsm_pages = 35
max_connections = 1000
work_mem = 65536
effective_cache_size = 61
random_page_cost = 3

Thanks
Marty

-Original Message-
From: Bucky Jordan [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 3:23 PM

To: Joshua D. Drake; Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: RE: [PERFORM] How to get higher tps

Marty,

Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
cache/socket) with 6x300GB 10k SAS drives:

pgbench -c 10 -t 1 -d bench 2>/dev/null
pghost:  pgport: (null) nclients: 10 nxacts: 1 dbName: bench
`transaction type: TPC-B (sort of) scaling factor: 20 number of clients:
10 number of transactions per client: 1 number of transactions
actually processed: 10/10 tps = 561.056729 (including
connections establishing) tps = 561.127760 (excluding connections
establishing)

Here's some iostat samples during the test:
  tty   mfid0  da0  cd0
cpu
 tin tout  KB/t tps  MB/s   KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in
id
   6   77 16.01 1642 25.67   0.00   0  0.00   0.00   0  0.00   3  0  8
2 87
   8  157 17.48 3541 60.43   0.00   0  0.00   0.00   0  0.00  24  0 28
4 43
   5  673 17.66 2287 39.44   0.00   0  0.00   0.00   0  0.00  10  0 13
2 75
   6 2818 16.37 2733 43.68   0.00   0  0.00   0.00   0  0.00  17  0 23
3 56
   1  765 18.05 2401 42.32   0.00   0  0.00   0.00   0  0.00  15  0 17
3 65

Note- the above was with no tuning to the kernel or postgresql.conf. 


Now for my question- it seems that I've still got quite a bit of
headroom on the hardware I'm running the above tests on, since I know
the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
appears mostly idle. This would indicate I should be able to get some
significantly better numbers with postgresql.conf tweaks correct?

I guess the other problem is ensuring that we're not testing RAM speeds,
since most of the data is probably in memory (BSD io buffers)? Although,
for the initial run, that doesn't seem to be the case, since subsequent
runs without rebuilding the benchmark db are slightly not believable
(i.e. 1,200 going up to >2,500 tps over 5 back-to-back runs). So, as
long as I re-initialize the benchdb before each run, it should be a
realistic test, right?

Thanks,

Bucky
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joshua D.
Drake
Sent: Tuesday, August 22, 2006 12:16 PM
To: Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps

Marty Jia wrote:

Here is iostat when running pgbench:
 
avg-cpu:  %user   %nice%sys %iowait   %idle

  26.170.008.25   23.17   42.42


You are are a little io bound and fairly cpu bound. I would be curious
if your performance goes down if you increase the number of connections
you are using.

Joshua D. Drake


 
Device:tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn

sda   0.00 0.00 0.00  0  0
sda1  0.00 0.00 0.00  0  0
sda2  0.00 0.00 0.00  0  0
sda3  0.00 0.00 0.00  0  0
sda4  0.00 0.00 0.00  0  0
sda5  0.00 0.00 0.00  0  0
sda6  0.00 0.00 0.00  0  0
sda7  0.00 0.00 0.00  0  0
sdb   0.00 0.00 0.00  0  0
sdb1  0.00 0.00 0.00  0  0
sdb2  0.00 0.00 0.00  0  0
sdb3  0.00 0.00 0.00  0  0
sdb4  0.00 0.00 0.00  0  0
sdb5  0.00 0.00 0.00  0  0
sdb6  0.00 0.00 0.00  0  0
sdb7  0.00 0.00 0.00  0  0
sdc   0.00 0.00 0.00  0  0
sdd   0.00 0.00 0.00  0  0
sde   0.00 0.00 0.00  0  0
sdf   0.00 0.00 0.00  0  0
sdg   0.00 0.00 0.00  0  0
sdh

Re: [PERFORM] How to get higher tps

[Marty Jia]

Bucky

My best result is around 380. I believe your hardware is more efficient,
because no matter how I change the conf parameters, no improvement can
be obtained. I even turned fsync off.

What is your values for the following parameters?

shared_buffers = 8
max_fsm_pages = 35
max_connections = 1000
work_mem = 65536
effective_cache_size = 61
random_page_cost = 3

Thanks
Marty

-Original Message-
From: Bucky Jordan [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 3:23 PM
To: Joshua D. Drake; Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: RE: [PERFORM] How to get higher tps

Marty,

Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
cache/socket) with 6x300GB 10k SAS drives:

pgbench -c 10 -t 1 -d bench 2>/dev/null
pghost:  pgport: (null) nclients: 10 nxacts: 1 dbName: bench
`transaction type: TPC-B (sort of) scaling factor: 20 number of clients:
10 number of transactions per client: 1 number of transactions
actually processed: 10/10 tps = 561.056729 (including
connections establishing) tps = 561.127760 (excluding connections
establishing)

Here's some iostat samples during the test:
  tty   mfid0  da0  cd0
cpu
 tin tout  KB/t tps  MB/s   KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in
id
   6   77 16.01 1642 25.67   0.00   0  0.00   0.00   0  0.00   3  0  8
2 87
   8  157 17.48 3541 60.43   0.00   0  0.00   0.00   0  0.00  24  0 28
4 43
   5  673 17.66 2287 39.44   0.00   0  0.00   0.00   0  0.00  10  0 13
2 75
   6 2818 16.37 2733 43.68   0.00   0  0.00   0.00   0  0.00  17  0 23
3 56
   1  765 18.05 2401 42.32   0.00   0  0.00   0.00   0  0.00  15  0 17
3 65

Note- the above was with no tuning to the kernel or postgresql.conf. 

Now for my question- it seems that I've still got quite a bit of
headroom on the hardware I'm running the above tests on, since I know
the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
appears mostly idle. This would indicate I should be able to get some
significantly better numbers with postgresql.conf tweaks correct?

I guess the other problem is ensuring that we're not testing RAM speeds,
since most of the data is probably in memory (BSD io buffers)? Although,
for the initial run, that doesn't seem to be the case, since subsequent
runs without rebuilding the benchmark db are slightly not believable
(i.e. 1,200 going up to >2,500 tps over 5 back-to-back runs). So, as
long as I re-initialize the benchdb before each run, it should be a
realistic test, right?

Thanks,

Bucky
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joshua D.
Drake
Sent: Tuesday, August 22, 2006 12:16 PM
To: Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps

Marty Jia wrote:
> Here is iostat when running pgbench:
>  
> avg-cpu:  %user   %nice%sys %iowait   %idle
>   26.170.008.25   23.17   42.42

You are are a little io bound and fairly cpu bound. I would be curious
if your performance goes down if you increase the number of connections
you are using.

Joshua D. Drake


>  
> Device:tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
> sda   0.00 0.00 0.00  0  0
> sda1  0.00 0.00 0.00  0  0
> sda2  0.00 0.00 0.00  0  0
> sda3  0.00 0.00 0.00  0  0
> sda4  0.00 0.00 0.00  0  0
> sda5  0.00 0.00 0.00  0  0
> sda6  0.00 0.00 0.00  0  0
> sda7  0.00 0.00 0.00  0  0
> sdb   0.00 0.00 0.00  0  0
> sdb1  0.00 0.00 0.00  0  0
> sdb2  0.00 0.00 0.00  0  0
> sdb3  0.00 0.00 0.00  0  0
> sdb4  0.00 0.00 0.00  0  0
> sdb5  0.00 0.00 0.00  0  0
> sdb6  0.00 0.00 0.00  0  0
> sdb7  0.00 0.00 0.00  0  0
> sdc   0.00 0.00 0.00  0  0
> sdd   0.00 0.00 0.00  0  0
> sde   0.00 0.00 0.00  0  0
> sdf   0.00 0.00 0.00  0  0
> sdg   0.00 0.00 0.00  0  0
> sdh   0.00 0.00   

Re: [PERFORM] VACUUM FULL needed sometimes to prevent transaction ID wraparound?

[Dave Dutcher]

I would guess that you are not running vacuumdb as a user with permission to
vacuum the postgres or template1 databases.  Try telling vacuumdb to log in
as postgres or whatever your superuser account is called.


> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> Marinos Yannikos
> Sent: Tuesday, August 22, 2006 1:11 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] VACUUM FULL needed sometimes to prevent 
> transaction ID wraparound?
> 
> 
> Hello,
> 
> we're looking into the reason why we are getting warnings about 
> transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is 
> claiming that VACUUM without FULL cannot reassign XIDs properly when 
> max_fsm_pages was set too low (it says so here too, but this 
> is rather 
> old: 
> http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is 
> this true, or do we have a different issue here? We're using 
> 8.1.3 with 
> a database generated on 8.1.3 (i.e. not migrated from 7.x or anything 
> like that).
> 
> Thanks,
>   Marinos
> 
> ---(end of 
> broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] How to get higher tps

[Bucky Jordan]

Marty,

Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install
on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
cache/socket) with 6x300GB 10k SAS drives:

pgbench -c 10 -t 1 -d bench 2>/dev/null
pghost:  pgport: (null) nclients: 10 nxacts: 1 dbName: bench
`transaction type: TPC-B (sort of)
scaling factor: 20
number of clients: 10
number of transactions per client: 1
number of transactions actually processed: 10/10
tps = 561.056729 (including connections establishing)
tps = 561.127760 (excluding connections establishing)

Here's some iostat samples during the test:
  tty   mfid0  da0  cd0
cpu
 tin tout  KB/t tps  MB/s   KB/t tps  MB/s   KB/t tps  MB/s  us ni sy in
id
   6   77 16.01 1642 25.67   0.00   0  0.00   0.00   0  0.00   3  0  8
2 87
   8  157 17.48 3541 60.43   0.00   0  0.00   0.00   0  0.00  24  0 28
4 43
   5  673 17.66 2287 39.44   0.00   0  0.00   0.00   0  0.00  10  0 13
2 75
   6 2818 16.37 2733 43.68   0.00   0  0.00   0.00   0  0.00  17  0 23
3 56
   1  765 18.05 2401 42.32   0.00   0  0.00   0.00   0  0.00  15  0 17
3 65

Note- the above was with no tuning to the kernel or postgresql.conf. 

Now for my question- it seems that I've still got quite a bit of
headroom on the hardware I'm running the above tests on, since I know
the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
appears mostly idle. This would indicate I should be able to get some
significantly better numbers with postgresql.conf tweaks correct?

I guess the other problem is ensuring that we're not testing RAM speeds,
since most of the data is probably in memory (BSD io buffers)? Although,
for the initial run, that doesn't seem to be the case, since subsequent
runs without rebuilding the benchmark db are slightly not believable
(i.e. 1,200 going up to >2,500 tps over 5 back-to-back runs). So, as
long as I re-initialize the benchdb before each run, it should be a
realistic test, right?

Thanks,

Bucky
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joshua D.
Drake
Sent: Tuesday, August 22, 2006 12:16 PM
To: Marty Jia
Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps

Marty Jia wrote:
> Here is iostat when running pgbench:
>  
> avg-cpu:  %user   %nice%sys %iowait   %idle
>   26.170.008.25   23.17   42.42

You are are a little io bound and fairly cpu bound. I would be curious 
if your performance goes down if you increase the number of connections 
you are using.

Joshua D. Drake


>  
> Device:tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
> sda   0.00 0.00 0.00  0  0
> sda1  0.00 0.00 0.00  0  0
> sda2  0.00 0.00 0.00  0  0
> sda3  0.00 0.00 0.00  0  0
> sda4  0.00 0.00 0.00  0  0
> sda5  0.00 0.00 0.00  0  0
> sda6  0.00 0.00 0.00  0  0
> sda7  0.00 0.00 0.00  0  0
> sdb   0.00 0.00 0.00  0  0
> sdb1  0.00 0.00 0.00  0  0
> sdb2  0.00 0.00 0.00  0  0
> sdb3  0.00 0.00 0.00  0  0
> sdb4  0.00 0.00 0.00  0  0
> sdb5  0.00 0.00 0.00  0  0
> sdb6  0.00 0.00 0.00  0  0
> sdb7  0.00 0.00 0.00  0  0
> sdc   0.00 0.00 0.00  0  0
> sdd   0.00 0.00 0.00  0  0
> sde   0.00 0.00 0.00  0  0
> sdf   0.00 0.00 0.00  0  0
> sdg   0.00 0.00 0.00  0  0
> sdh   0.00 0.00 0.00  0  0
> sdi  40.33 0.00   413.33  0   1240
> sdj  34.33 0.00   394.67  0   1184
> sdk  36.00 0.00   410.67  0   1232
> sdl  37.00 0.00   429.33  0   1288
> sdm 375.00 0.00  3120.00  0   9360
> sdn 378.33 0.00  3120.00  0   9360
> 
> 
> 
> From: Alex Turner [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 22, 2006 11:27 AM
> To: Mark Lewis
> Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.o

Re: [PERFORM] VACUUM FULL needed sometimes to prevent transaction ID wraparound?

[Alvaro Herrera]

Marinos Yannikos wrote:
> Hello,
> 
> we're looking into the reason why we are getting warnings about 
> transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is 
> claiming that VACUUM without FULL cannot reassign XIDs properly when 
> max_fsm_pages was set too low (it says so here too, but this is rather 
> old: http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is 
> this true, or do we have a different issue here? We're using 8.1.3 with 
> a database generated on 8.1.3 (i.e. not migrated from 7.x or anything 
> like that).

It's not true.  Having shortage of FSM entries will make you lose space,
but it will be able to recycle Xids anyway.

I guess your problem is that you're not vacuuming all databases for some
reason.  I'd advise to lose the -q and make sure you're not redirecting
to somewhere you can't read the log; the read the log and make sure
everything is going fine.

What's the warning anyway?  Does it say that wraparound point is
nearing, or does it merely say that it is on Xid  and
you don't know how far that number actually is?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

[Alvaro Herrera]

Thomas Samson wrote:
> On 8/22/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> >Ulrich Habel wrote:
> >> Hello all,
> >> had an idea of optimizing a query that may work generally.
> >>
> >> In case a 'column' is indexed, following two alterations could be done
> >> I think:
> >>
> >> A)
> >>
> >>   select ... where column ~ '^Foo' -->  Seq Scan
> >
> >This is not true.  You can make this query use an index if you create it
> >with opclass varchar_pattern_ops or text_pattern_ops, as appropiate.
> >
> >Thus you don't need any hack here.
> >
> 
> And in the case of more general expression, like:
> select ... where column ~ 'something';
> 
> Is there a way to optimise this ? (in the case where 'something' is not
> a word, but a part of a word)

Not sure.  I'd try tsearch2 or pg_trgm (or pg_tgrm, whatever it's
called).  It's trigram indexing.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

[PERFORM] VACUUM FULL needed sometimes to prevent transaction ID wraparound?

[Marinos Yannikos]

Hello,

we're looking into the reason why we are getting warnings about 
transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is 
claiming that VACUUM without FULL cannot reassign XIDs properly when 
max_fsm_pages was set too low (it says so here too, but this is rather 
old: http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is 
this true, or do we have a different issue here? We're using 8.1.3 with 
a database generated on 8.1.3 (i.e. not migrated from 7.x or anything 
like that).


Thanks,
 Marinos

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

[Thomas Samson]

On 8/22/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote:

Ulrich Habel wrote:
> Hello all,
> had an idea of optimizing a query that may work generally.
>
> In case a 'column' is indexed, following two alterations could be done
> I think:
>
> A)
>
>   select ... where column ~ '^Foo' -->  Seq Scan

This is not true.  You can make this query use an index if you create it
with opclass varchar_pattern_ops or text_pattern_ops, as appropiate.

Thus you don't need any hack here.



And in the case of more general expression, like:
select ... where column ~ 'something';

Is there a way to optimise this ? (in the case where 'something' is not
a word, but a part of a word)

--
Thomas SAMSON

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

[Alvaro Herrera]

Ulrich Habel wrote:
> Hello all,
> had an idea of optimizing a query that may work generally.
> 
> In case a 'column' is indexed, following two alterations could be done
> I think:
> 
> A)
> 
>   select ... where column ~ '^Foo' -->  Seq Scan

This is not true.  You can make this query use an index if you create it
with opclass varchar_pattern_ops or text_pattern_ops, as appropiate.

Thus you don't need any hack here.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 6: explain analyze is your friend

[PERFORM] query planner: automatic rescribe of LIKE to BETWEEN ?

[Ulrich Habel]

Hello all,
had an idea of optimizing a query that may work generally.

In case a 'column' is indexed, following two alterations could be done
I think:

A)

  select ... where column ~ '^Foo' -->  Seq Scan

into that:

  select ... where column BETWEEN 'Foo' AND 'FooZ' -->  Index Scan

of course 'Z' should be the last possible character internally used of the 
DBMS.

That would work as long as there is no in-case-sensitive search being done.


another rescribtion:

B)

  select ... where  column ~ '^Foo$' -->  Seq Scan

into that:

  select ... where  column =  'Foo' -->  Bitmap Heap Scan

That speeds up things, too.



That would also apply to 'LIKE' and 'SIMILAR TO' operations, I think.

Is there any idea to make the "Query Planner" more intelligent to do these  
convertions automatically?

Anythings speeks against this hack?

Regards
  Uli Habel

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] How to get higher tps

[Joshua D. Drake]

Marty Jia wrote:

Here is iostat when running pgbench:
 
avg-cpu:  %user   %nice%sys %iowait   %idle

  26.170.008.25   23.17   42.42


You are are a little io bound and fairly cpu bound. I would be curious 
if your performance goes down if you increase the number of connections 
you are using.


Joshua D. Drake


 
Device:tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn

sda   0.00 0.00 0.00  0  0
sda1  0.00 0.00 0.00  0  0
sda2  0.00 0.00 0.00  0  0
sda3  0.00 0.00 0.00  0  0
sda4  0.00 0.00 0.00  0  0
sda5  0.00 0.00 0.00  0  0
sda6  0.00 0.00 0.00  0  0
sda7  0.00 0.00 0.00  0  0
sdb   0.00 0.00 0.00  0  0
sdb1  0.00 0.00 0.00  0  0
sdb2  0.00 0.00 0.00  0  0
sdb3  0.00 0.00 0.00  0  0
sdb4  0.00 0.00 0.00  0  0
sdb5  0.00 0.00 0.00  0  0
sdb6  0.00 0.00 0.00  0  0
sdb7  0.00 0.00 0.00  0  0
sdc   0.00 0.00 0.00  0  0
sdd   0.00 0.00 0.00  0  0
sde   0.00 0.00 0.00  0  0
sdf   0.00 0.00 0.00  0  0
sdg   0.00 0.00 0.00  0  0
sdh   0.00 0.00 0.00  0  0
sdi  40.33 0.00   413.33  0   1240
sdj  34.33 0.00   394.67  0   1184
sdk  36.00 0.00   410.67  0   1232
sdl  37.00 0.00   429.33  0   1288
sdm 375.00 0.00  3120.00  0   9360
sdn 378.33 0.00  3120.00  0   9360



From: Alex Turner [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 11:27 AM

To: Mark Lewis
Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org; DBAs;
Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps


Oh - and it's usefull to know if you are CPU bound, or IO bound.  Check
top or vmstat to get an idea of that

Alex


On 8/22/06, Alex Turner < [EMAIL PROTECTED]  >
wrote: 


First things first, run a bonnie++ benchmark, and post the
numbers.  That will give a good indication of raw IO performance, and is
often the first inidication of problems separate from the DB.  We have
seen pretty bad performance from SANs in the past.  How many FC lines do
you have running to your server, remember each line is limited to about
200MB/sec, to get good throughput, you will need multiple connections. 
	

When you run pgbench, run a iostat also and see what the numbers
say.


Alex.



On 8/22/06, Mark Lewis < [EMAIL PROTECTED]
 > wrote: 


Well, at least on my test machines running
gnome-terminal, my pgbench 
		runs tend to get throttled by gnome-terminal's lousy

performance to no
more than 300 tps or so.  Running with 2>/dev/null to
throw away all the
detailed logging gives me 2-3x improvement in scores.
Caveat: in my 
		case the db is on the local machine, so who knows what

all the
interactions are.

Also, when you initialized the pgbench db what scaling
factor did you
use?  And does running pgbench with -v improve
performance at all? 
		

-- Mark

On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> Joshua,
>
> Here is
>
> shared_buffers = 8
> fsync = on
> max_fsm_pages = 35
		> max_connections = 1000 
		> work_mem = 65536

> effective_cache_size = 61
> random_page_cost = 3
>
> Here is pgbench I used:
>
> pgbench -c 10 -t 1 -d HQDB
>
> Thanks
>
		> Marty 
		>

> -Original Message-
> From: Joshua D. Drake [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 21, 2006 6:09 PM

Re: [PERFORM] How to get higher tps

[Marty Jia]

Ron

Here is our hardware

Dual Intel Xeon 2.8GHz
6GB RAM
Linux 2.4 kernel
RedHat Enterprise Linux AS 3
200GB for PGDATA on 3Par, ext3
50GB for WAL on 3Par, ext3

RAID 10, using 3Par virtual volume technology across ~200 physical FC
disks.  4 virtual disks for PGDATA, striped with LVM into one volume, 2
virtual disks for WAL, also striped.  SAN attached with Qlogic SAN
surfer multipathing to load balance each LUN on two 2GBs paths.  HBAs
are Qlogic 2340's.  16GB host cache on 3Par.

shared_buffers = 8
max_fsm_pages = 35
max_connections = 1000
work_mem = 65536
effective_cache_size = 61
random_page_cost = 3

Thanks
 

-Original Message-
From: Ron [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 11:47 AM
To: Marty Jia
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to get higher tps

At 04:45 PM 8/21/2006, Marty Jia wrote:
>I'm exhausted to try all performance tuning ideas, like following 
>parameters
>
>shared_buffers
>fsync
>max_fsm_pages
>max_connections
>shared_buffers
>work_mem
>max_fsm_pages
>effective_cache_size
>random_page_cost

All of this comes =after= the Get the Correct HW (1) & OS (2) steps.
You are putting the cart before the horse.

>I believe all above have right size and values, but I just can not get 
>higher tps more than 300 testd by pgbench

300tps on what HW?  and under what pattern of IO load?
300tps of OLTP on a small number of non-Raptor 10K rpm HD's may actually
be decent performance.
300tps on a 24 HD RAID 10 based on Raptors or 15Krpm HDs and working
through a HW RAID controller w/ >= 1GB of BB cache is likely to be poor.

>Here is our hardware
>
>
>Dual Intel Xeon 2.8GHz
>6GB RAM

Modest CPU and RAM for a DB server now-a-days.   In particular, the 
more DB you can keep in RAM the better.
And you have said nothing about the most importance HW when talking
about tps:  What Does Your HD Subsystem Look Like?
.

>Linux 2.4 kernel
>RedHat Enterprise Linux AS 3
Upgrade to a 2.6 based kernel and examine your RHEL-AS3 install with a
close eye to trimming the fat you do not need from it.  Cent-OS ot Ca-Os
may be better distro choices.


>200GB for PGDATA on 3Par, ext3
>50GB for WAL on 3Par, ext3
Put WAL on ext2.  Experiment with ext3, jfs, reiserfs, and XFS for
pgdata.

Take a =close= look at the exact HW specs of your 3par.to make sure that
you are not attempting the impossible with that HW.
"3par" is marketing fluff.  We need HD specs and RAID subsystem config
data.

>With PostgreSql 8.1.4
>
>We don't have i/o bottle neck.
Prove it.  Where are the numbers that back up your assertion and how did
you get them?


>Whatelse I can try to better tps? Someone told me I can should get tps
>over 1500, it is hard to believe.
Did they claim your exact HW could get 1500tps?  Your exact HW+OS+pg 
version+app SW?  Some subset of those 4 variables?
Performance claims are easy to make.  =Valid= performance claims are 
tougher since they have to be much more constrained and descriptive.


Ron


---(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: [PERFORM] How to get higher tps

[Ron]

At 04:45 PM 8/21/2006, Marty Jia wrote:

I'm exhausted to try all performance tuning ideas, like following
parameters

shared_buffers
fsync
max_fsm_pages
max_connections
shared_buffers
work_mem
max_fsm_pages
effective_cache_size
random_page_cost


All of this comes =after= the Get the Correct HW (1) & OS (2) 
steps.  You are putting the cart before the horse.



I believe all above have right size and values, but I just can not get
higher tps more than 300 testd by pgbench


300tps on what HW?  and under what pattern of IO load?
300tps of OLTP on a small number of non-Raptor 10K rpm HD's may 
actually be decent performance.
300tps on a 24 HD RAID 10 based on Raptors or 15Krpm HDs and working 
through a HW RAID controller w/ >= 1GB of BB cache is likely to be poor.



Here is our hardware


Dual Intel Xeon 2.8GHz
6GB RAM


Modest CPU and RAM for a DB server now-a-days.   In particular, the 
more DB you can keep in RAM the better.
And you have said nothing about the most importance HW when talking 
about tps:  What Does Your HD Subsystem Look Like?

.


Linux 2.4 kernel
RedHat Enterprise Linux AS 3
Upgrade to a 2.6 based kernel and examine your RHEL-AS3 install with 
a close eye to trimming the fat you do not need from it.  Cent-OS ot 
Ca-Os may be better distro choices.




200GB for PGDATA on 3Par, ext3
50GB for WAL on 3Par, ext3

Put WAL on ext2.  Experiment with ext3, jfs, reiserfs, and XFS for pgdata.

Take a =close= look at the exact HW specs of your 3par.to make sure 
that you are not attempting the impossible with that HW.

"3par" is marketing fluff.  We need HD specs and RAID subsystem config data.


With PostgreSql 8.1.4

We don't have i/o bottle neck.
Prove it.  Where are the numbers that back up your assertion and how 
did you get them?




Whatelse I can try to better tps? Someone told me I can should get tps
over 1500, it is hard to believe.
Did they claim your exact HW could get 1500tps?  Your exact HW+OS+pg 
version+app SW?  Some subset of those 4 variables?
Performance claims are easy to make.  =Valid= performance claims are 
tougher since they have to be much more constrained and descriptive.



Ron


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] How to get higher tps

[Marty Jia]

Here is iostat when running pgbench:
 
avg-cpu:  %user   %nice    
%sys %iowait   
%idle  
26.17    0.00    8.25   
23.17   42.42
 
Device:    
tps   Blk_read/s   Blk_wrtn/s   
Blk_read   
Blk_wrtnsda   
0.00 
0.00 
0.00  
0  
0sda1  
0.00 
0.00 
0.00  
0  
0sda2  
0.00 
0.00 
0.00  
0  
0sda3  
0.00 
0.00 
0.00  
0  
0sda4  
0.00 
0.00 
0.00  
0  
0sda5  
0.00 
0.00 
0.00  
0  
0sda6  
0.00 
0.00 
0.00  
0  
0sda7  
0.00 
0.00 
0.00  
0  
0sdb   
0.00 
0.00 
0.00  
0  
0sdb1  
0.00 
0.00 
0.00  
0  
0sdb2  
0.00 
0.00 
0.00  
0  
0sdb3  
0.00 
0.00 
0.00  
0  
0sdb4  
0.00 
0.00 
0.00  
0  
0sdb5  
0.00 
0.00 
0.00  
0  
0sdb6  
0.00 
0.00 
0.00  
0  
0sdb7  
0.00 
0.00 
0.00  
0  
0sdc   
0.00 
0.00 
0.00  
0  
0sdd   
0.00 
0.00 
0.00  
0  
0sde   
0.00 
0.00 
0.00  
0  
0sdf   
0.00 
0.00 
0.00  
0  
0sdg   
0.00 
0.00 
0.00  
0  
0sdh   
0.00 
0.00 
0.00  
0  
0sdi  
40.33 
0.00   
413.33  
0   
1240sdj  
34.33 
0.00   
394.67  
0   
1184sdk  
36.00 
0.00   
410.67  
0   
1232sdl  
37.00 
0.00   
429.33  
0   
1288sdm 
375.00 
0.00  
3120.00  
0   
9360sdn 
378.33 
0.00  
3120.00  
0   9360


From: Alex Turner [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 11:27 AMTo: Mark 
LewisCc: Marty Jia; Joshua D. Drake; 
pgsql-performance@postgresql.org; DBAs; Rich Wilson; Ernest 
WurzbachSubject: Re: [PERFORM] How to get higher 
tps
Oh - and it's usefull to know if you are CPU bound, or IO 
bound.  Check top or vmstat to get an idea of thatAlex
On 8/22/06, Alex 
Turner < [EMAIL PROTECTED]> 
wrote:

  First things first, run a bonnie++ benchmark, and post the numbers.  
  That will give a good indication of raw IO performance, and is often the first 
  inidication of problems separate from the DB.  We have seen pretty bad 
  performance from SANs in the past.  How many FC lines do you have running 
  to your server, remember each line is limited to about 200MB/sec, to get good 
  throughput, you will need multiple connections. When you run pgbench, 
  run a iostat also and see what the numbers say.
  Alex.
  
  On 8/22/06, Mark 
  Lewis < [EMAIL PROTECTED]> 
  wrote:
  Well, 
at least on my test machines running gnome-terminal, my pgbench runs 
tend to get throttled by gnome-terminal's lousy performance to nomore 
than 300 tps or so.  Running with 2>/dev/null to throw away all 
thedetailed logging gives me 2-3x improvement in 
scores.  Caveat: in my case the db is on the local machine, so 
who knows what all theinteractions are.Also, when you 
initialized the pgbench db what scaling factor did 
youuse?  And does running pgbench with -v improve performance 
at all? -- MarkOn Tue, 2006-08-22 at 09:19 -0400, Marty Jia 
wrote:> Joshua,>> Here is>> 
shared_buffers = 8> fsync = on> max_fsm_pages = 
35> max_connections = 1000 > work_mem = 65536> 
effective_cache_size = 61> random_page_cost = 3>> 
Here is pgbench I used:>> pgbench -c 10 -t 1 -d 
HQDB>> Thanks>> Marty >> 
-Original Message-> From: Joshua D. Drake [mailto:[EMAIL PROTECTED]]> Sent: Monday, August 21, 
2006 6:09 PM> To: Marty Jia> Cc: pgsql-performance@postgresql.org> Subject: Re: 
[PERFORM] How to get higher tps>> Marty Jia wrote:> 
> I'm exhausted to try all performance tuning ideas, like following 
> > parameters> >> > shared_buffers> 
> fsync> > max_fsm_pages> > max_connections> 
> shared_buffers> > work_mem> > max_fsm_pages> 
> effective_cache_size> > random_page_cost> >> 
> I believe all above have right size and values, but I just can not 
get>> > higher tps more than 300 testd by pgbench 
>> What values did you use?>> >> > 
Here is our hardware> >> >> > Dual Intel Xeon 
2.8GHz> > 6GB RAM> > Linux 2.4 kernel> > 
RedHat En

Re: [PERFORM] How to get higher tps

[Marty Jia]

--- Here is vmstat
procs  
memory  
swap  
io 
system cpu r  
b   swpd   free   buff  cache   
si   so    bi    bo   
in    cs us sy id wa 0  1  15416  
18156  73372 4348488    1    
1 3 2    
4 1  2  1  2  2
 
 
--- Here  is iostat
 
avg-cpu:  
%user   %nice    %sys %iowait   
%idle  
11.59    0.00    6.13   
10.77   71.50
 
Device:    
tps   Blk_read/s   Blk_wrtn/s   
Blk_read   
Blk_wrtnsda   
2.76 
6.88    36.35   
16036474   
84688320sda1  
0.00 
0.01 
0.00  30100   
1056sda2  
0.27 
2.36 1.72    
5509296    
4017224sda3  
1.85 
0.78    21.99    
1819850   
51242800sda4  
0.00 
0.00 
0.00 
20  
0sda5  
0.15 
0.49 1.47    
1131624    
3425672sda6  
0.49 
3.14    11.12    
7320616   
25899088sda7  
0.01 
0.09 
0.04 219960 
102480sdb   
2.75 
6.78    36.35   
15803532   
84688320sdb1  
0.00 
0.01 
0.00  24322   
1056sdb2  
0.27 
2.31 1.72    
5391682    
4017224sdb3  
1.84 
0.79    21.99    
1836088   
51242800sdb4  
0.00 
0.00 
0.00 
20  
0sdb5  
0.15 
0.49 1.47    
1134546    
3425672sdb6  
0.49 
3.12    11.12    
7273816   
25899088sdb7  
0.01 
0.06 
0.04 138138 
102480sdc   
0.00 
0.00 
0.00    
632  
0sdd   
0.00 
0.00 
0.00 
80  
0sde   
0.00 
0.00 
0.00 
80  
0sdf   
0.00 
0.00 
0.00 
80  
0sdg   
0.00 
0.00 
0.00    
112  
0sdh   
0.00 
0.00 
0.00    
112  
0sdi 
139.89   
680.59   839.42 1585722266 
1955771032sdj 
139.72   
680.21   835.90 1584829368 
1947590800sdk 
139.82   
680.30   840.74 1585053608 
1958864880sdl 
139.86   
680.56   841.26 1585657408 
1960079576sdm  
54.80 
6.67   891.38   15547618 
2076836720sdn  
54.71 
6.66   891.35   15509096 
2076776352
 
 
 
 
 


From: Alex Turner [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 11:27 AMTo: Mark 
LewisCc: Marty Jia; Joshua D. Drake; 
pgsql-performance@postgresql.org; DBAs; Rich Wilson; Ernest 
WurzbachSubject: Re: [PERFORM] How to get higher 
tps
Oh - and it's usefull to know if you are CPU bound, or IO 
bound.  Check top or vmstat to get an idea of thatAlex
On 8/22/06, Alex 
Turner < [EMAIL PROTECTED]> 
wrote:

  First things first, run a bonnie++ benchmark, and post the numbers.  
  That will give a good indication of raw IO performance, and is often the first 
  inidication of problems separate from the DB.  We have seen pretty bad 
  performance from SANs in the past.  How many FC lines do you have running 
  to your server, remember each line is limited to about 200MB/sec, to get good 
  throughput, you will need multiple connections. When you run pgbench, 
  run a iostat also and see what the numbers say.
  Alex.
  
  On 8/22/06, Mark 
  Lewis < [EMAIL PROTECTED]> 
  wrote:
  Well, 
at least on my test machines running gnome-terminal, my pgbench runs 
tend to get throttled by gnome-terminal's lousy performance to nomore 
than 300 tps or so.  Running with 2>/dev/null to throw away all 
thedetailed logging gives me 2-3x improvement in 
scores.  Caveat: in my case the db is on the local machine, so 
who knows what all theinteractions are.Also, when you 
initialized the pgbench db what scaling factor did 
youuse?  And does running pgbench with -v improve performance 
at all? -- MarkOn Tue, 2006-08-22 at 09:19 -0400, Marty Jia 
wrote:> Joshua,>> Here is>> 
shared_buffers = 8> fsync = on> max_fsm_pages = 
35> max_connections = 1000 > work_mem = 65536> 
effective_cache_size = 61> random_page_cost = 3>> 
Here is pgbench I used:>> pgbench -c 10 -t 1 -d 
HQDB>> Thanks>> Marty >> 
-Original Message-> From: Joshua D. Drake [mailto:[EMAIL PROTECTED]]> Sent: Monday, August 21, 
2006 6:09 PM> To: Marty Jia> Cc: pgsql-performance@postgresql.org> Subject: Re: 
[PERFORM] How to get higher tps>> Marty Jia wrote:> 
> I'm exhausted to try all performance tuning ideas, like following 
> > parameters> >> > shared_buffers> 
> fsync> > max_fsm_pages> > max_connections> 
> shared_buffers> > work_mem> > max_fsm_pages> 
> effective_cache_size> > random_page_cost> >> 
> I believe all above

Re: [PERFORM] How to get higher tps

[Marty Jia]

The scaling factor is 20
I used -v and 2>/dev/null, now I got 

tps = 389.796376 (excluding connections establishing)

This is best so far I can get

Thanks 

-Original Message-
From: Mark Lewis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 22, 2006 10:32 AM
To: Marty Jia
Cc: Joshua D. Drake; pgsql-performance@postgresql.org; DBAs; Rich
Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps

Well, at least on my test machines running gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy performance to no
more than 300 tps or so.  Running with 2>/dev/null to throw away all the
detailed logging gives me 2-3x improvement in scores.  Caveat: in my
case the db is on the local machine, so who knows what all the
interactions are.

Also, when you initialized the pgbench db what scaling factor did you
use?  And does running pgbench with -v improve performance at all?

-- Mark

On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> Joshua,
> 
> Here is
> 
> shared_buffers = 8
> fsync = on
> max_fsm_pages = 35
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 61
> random_page_cost = 3
>  
> Here is pgbench I used:
> 
> pgbench -c 10 -t 1 -d HQDB
> 
> Thanks
> 
> Marty
> 
> -Original Message-
> From: Joshua D. Drake [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 21, 2006 6:09 PM
> To: Marty Jia
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to get higher tps
> 
> Marty Jia wrote:
> > I'm exhausted to try all performance tuning ideas, like following 
> > parameters
> > 
> > shared_buffers
> > fsync
> > max_fsm_pages
> > max_connections
> > shared_buffers
> > work_mem
> > max_fsm_pages
> > effective_cache_size
> > random_page_cost
> > 
> > I believe all above have right size and values, but I just can not 
> > get
> 
> > higher tps more than 300 testd by pgbench
> 
> What values did you use?
> 
> > 
> > Here is our hardware
> > 
> > 
> > Dual Intel Xeon 2.8GHz
> > 6GB RAM
> > Linux 2.4 kernel
> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3
> > 50GB for WAL on 3Par, ext3
> > 
> > With PostgreSql 8.1.4
> > 
> > We don't have i/o bottle neck. 
> 
> Are you sure? What does iostat say during a pgbench? What parameters 
> are you passing to pgbench?
> 
> Well in theory, upgrading to 2.6 kernel will help as well as making 
> your WAL ext2 instead of ext3.
> 
> > Whatelse I can try to better tps? Someone told me I can should get 
> > tps
> 
> > over 1500, it is hard to believe.
> 
> 1500? Hmmm... I don't know about that, I can get 470tps or so on my 
> measily dual core 3800 with 2gig of ram though.
> 
> Joshua D. Drake
> 
> 
> > 
> > Thanks
> > 
> > Marty
> > 
> > ---(end of
> > broadcast)---
> > TIP 2: Don't 'kill -9' the postmaster
> > 
> 
> 

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] How to get higher tps

[Alex Turner]

Oh - and it's usefull to know if you are CPU bound, or IO bound.  Check top or vmstat to get an idea of thatAlexOn 8/22/06, Alex Turner <
[EMAIL PROTECTED]> wrote:First things first, run a bonnie++ benchmark, and post the numbers.  That will give a good indication of raw IO performance, and is often the first inidication of problems separate from the DB.  We have seen pretty bad performance from SANs in the past.  How many FC lines do you have running to your server, remember each line is limited to about 200MB/sec, to get good throughput, you will need multiple connections.
When you run pgbench, run a iostat also and see what the numbers say.Alex.On 8/22/06, 
Mark Lewis <
[EMAIL PROTECTED]> wrote:Well, at least on my test machines running gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy performance to nomore than 300 tps or so.  Running with 2>/dev/null to throw away all thedetailed logging gives me 2-3x improvement in scores.  Caveat: in my
case the db is on the local machine, so who knows what all theinteractions are.Also, when you initialized the pgbench db what scaling factor did youuse?  And does running pgbench with -v improve performance at all?
-- MarkOn Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:> Joshua,>> Here is>> shared_buffers = 8> fsync = on> max_fsm_pages = 35> max_connections = 1000
> work_mem = 65536> effective_cache_size = 61> random_page_cost = 3>> Here is pgbench I used:>> pgbench -c 10 -t 1 -d HQDB>> Thanks>> Marty
>> -Original Message-> From: Joshua D. Drake [mailto:[EMAIL PROTECTED]]
> Sent: Monday, August 21, 2006 6:09 PM> To: Marty Jia> Cc: 
pgsql-performance@postgresql.org> Subject: Re: [PERFORM] How to get higher tps>
> Marty Jia wrote:> > I'm exhausted to try all performance tuning ideas, like following
> > parameters> >> > shared_buffers> > fsync> > max_fsm_pages> > max_connections> > shared_buffers> > work_mem> > max_fsm_pages

> > effective_cache_size> > random_page_cost> >> > I believe all above have right size and values, but I just can not get>> > higher tps more than 300 testd by pgbench

>> What values did you use?>> >> > Here is our hardware> >> >> > Dual Intel Xeon 2.8GHz> > 6GB RAM> > Linux 2.4 kernel> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3> > 50GB for WAL on 3Par, ext3> >> > With PostgreSql 8.1.4> >> > We don't have i/o bottle neck.>> Are you sure? What does iostat say during a pgbench? What parameters are
> you passing to pgbench?>> Well in theory, upgrading to 2.6 kernel will help as well as making your> WAL ext2 instead of ext3.>> > Whatelse I can try to better tps? Someone told me I can should get tps
>> > over 1500, it is hard to believe.>> 1500? Hmmm... I don't know about that, I can get 470tps or so on my> measily dual core 3800 with 2gig of ram though.>> Joshua D. Drake
>>> >> > Thanks> >> > Marty> >> > ---(end of> > broadcast)---> > TIP 2: Don't 'kill -9' the postmaster
> >>>---(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: [PERFORM] How to get higher tps

[Alex Turner]

First things first, run a bonnie++ benchmark, and post the numbers.  That will give a good indication of raw IO performance, and is often the first inidication of problems separate from the DB.  We have seen pretty bad performance from SANs in the past.  How many FC lines do you have running to your server, remember each line is limited to about 200MB/sec, to get good throughput, you will need multiple connections.
When you run pgbench, run a iostat also and see what the numbers say.Alex.On 8/22/06, Mark Lewis <
[EMAIL PROTECTED]> wrote:Well, at least on my test machines running gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy performance to nomore than 300 tps or so.  Running with 2>/dev/null to throw away all thedetailed logging gives me 2-3x improvement in scores.  Caveat: in my
case the db is on the local machine, so who knows what all theinteractions are.Also, when you initialized the pgbench db what scaling factor did youuse?  And does running pgbench with -v improve performance at all?
-- MarkOn Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:> Joshua,>> Here is>> shared_buffers = 8> fsync = on> max_fsm_pages = 35> max_connections = 1000
> work_mem = 65536> effective_cache_size = 61> random_page_cost = 3>> Here is pgbench I used:>> pgbench -c 10 -t 1 -d HQDB>> Thanks>> Marty
>> -Original Message-> From: Joshua D. Drake [mailto:[EMAIL PROTECTED]]> Sent: Monday, August 21, 2006 6:09 PM> To: Marty Jia> Cc: 
pgsql-performance@postgresql.org> Subject: Re: [PERFORM] How to get higher tps>> Marty Jia wrote:> > I'm exhausted to try all performance tuning ideas, like following
> > parameters> >> > shared_buffers> > fsync> > max_fsm_pages> > max_connections> > shared_buffers> > work_mem> > max_fsm_pages
> > effective_cache_size> > random_page_cost> >> > I believe all above have right size and values, but I just can not get>> > higher tps more than 300 testd by pgbench
>> What values did you use?>> >> > Here is our hardware> >> >> > Dual Intel Xeon 2.8GHz> > 6GB RAM> > Linux 2.4 kernel> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3> > 50GB for WAL on 3Par, ext3> >> > With PostgreSql 8.1.4> >> > We don't have i/o bottle neck.>> Are you sure? What does iostat say during a pgbench? What parameters are
> you passing to pgbench?>> Well in theory, upgrading to 2.6 kernel will help as well as making your> WAL ext2 instead of ext3.>> > Whatelse I can try to better tps? Someone told me I can should get tps
>> > over 1500, it is hard to believe.>> 1500? Hmmm... I don't know about that, I can get 470tps or so on my> measily dual core 3800 with 2gig of ram though.>> Joshua D. Drake
>>> >> > Thanks> >> > Marty> >> > ---(end of> > broadcast)---> > TIP 2: Don't 'kill -9' the postmaster
> >>>---(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: [PERFORM] How to get higher tps

[Scott Marlowe]

On Tue, 2006-08-22 at 08:16, Marty Jia wrote:
> Hi, Mark
> 
> Thanks, here is our hardware info:
> 
> RAID 10, using 3Par virtual volume technology across ~200 physical FC
> disks.  4 virtual disks for PGDATA, striped with LVM into one volume, 2
> virtual disks for WAL, also striped.  SAN attached with Qlogic SAN
> surfer multipathing to load balance each LUN on two 2GBs paths.  HBAs
> are Qlogic 2340's.  16GB host cache on 3Par.

A few points.  

Someone (Luke I think) posted that Linux's LVM has a throughput limit of
around 600 Megs/second.

Why are you using multiple virtual disks on an LPAR?  Did you try this
with just a single big virtual disk first to have something to compare
it to?  I think your disk subsystem is overthought for an LPAR.  If you
were running physical disks on a locally attached RAID card, it would be
a good idea.  But here you're just adding layers of complexity for no
gain, and in fact may be heading backwards.

I'd make two volumes on the LPAR, and let the LPAR do all the
virtualization for you.  Put a couple disks in a mirror set for the
pg_xlog, format it ext2, and mount it noatime.  Make another from a
dozen or so disks in an RAID 0 on top of RAID 1 (i.e. make a bunch of
mirror sets and stripe them into one big partition) and mount that for
PGDATA.  Simplify, and get a baseline.  Then, start mucking about to see
if you can get better performance.  change ONE THING at a time, and only
one thing, and test it well.

Got the latest and greatest drivers for the qlogic cards?

I would suggest some component testing to make sure everything is
working well.  bonnie++ and dd come to mind.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Vacuum not identifying rows for removal..

[Eamonn Kent]

Hi Joshua,

Thanks for the info...but, what I already have the backend id.  I was
trying to get the process id of the client application.  The client is
using libpq and running on the same workstation.  We have approximately
22 different clients running and it would help to isolate the client
program that is causing the problem. 

I was unable to locate the client using the backend server's process id
with lsof and netstat.  Really the information should be there...since,
each (I believe) each backend postgreSQL server will service a single
client via a unix socket (in the case where they are collocated on a
unix workstation).

Thanks

Ike



> Any ideas of how to identify the application process that is the
> postgres process (whose id I know).  Perhaps I need to turn on a
> different log flag?

select * from pg_stat_activity will give you the pid :)

Joshua D. Drake


> 
> 
> Thanks
> 
> Ike
> 
> 
> 
> 
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 21, 2006 2:06 PM
> To: Eamonn Kent
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Vacuum not identifying rows for removal.. 
> 
> "Eamonn Kent" <[EMAIL PROTECTED]> writes:
>> I am using PostgreSQL 8.1.4 for an embedded application.  For some
>> reason, vacuum is not able to identify rows that are candidates for
>> removal (i.e., mark space as available).
>> ...
>> We run auto vacuum and I can see from the logs that it is running
> quite
>> frequently. When I run vacuum full from the psql, I can see that
space
>> is not being recovered.  I have run vacuum full with the verbose flag
>> set, I can see that messages that indicate the existence of "dead row
>> versions that cannot be removed yet.
> 
> This means you've got an open transaction somewhere that could
> potentially still be able to see those rows.  Look around for
> applications sitting "idle in transaction".
> 
>   regards, tom lane
> 
> ---(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
> 


-- 

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
  http://www.commandprompt.com/



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] How to get higher tps

[Mark Lewis]

Well, at least on my test machines running gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy performance to no
more than 300 tps or so.  Running with 2>/dev/null to throw away all the
detailed logging gives me 2-3x improvement in scores.  Caveat: in my
case the db is on the local machine, so who knows what all the
interactions are.

Also, when you initialized the pgbench db what scaling factor did you
use?  And does running pgbench with -v improve performance at all?

-- Mark

On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> Joshua,
> 
> Here is 
> 
> shared_buffers = 8
> fsync = on
> max_fsm_pages = 35
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 61
> random_page_cost = 3
>  
> Here is pgbench I used:
> 
> pgbench -c 10 -t 1 -d HQDB
> 
> Thanks
> 
> Marty
> 
> -Original Message-
> From: Joshua D. Drake [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 21, 2006 6:09 PM
> To: Marty Jia
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to get higher tps
> 
> Marty Jia wrote:
> > I'm exhausted to try all performance tuning ideas, like following 
> > parameters
> > 
> > shared_buffers
> > fsync
> > max_fsm_pages
> > max_connections
> > shared_buffers
> > work_mem
> > max_fsm_pages
> > effective_cache_size
> > random_page_cost
> > 
> > I believe all above have right size and values, but I just can not get
> 
> > higher tps more than 300 testd by pgbench
> 
> What values did you use?
> 
> > 
> > Here is our hardware
> > 
> > 
> > Dual Intel Xeon 2.8GHz
> > 6GB RAM
> > Linux 2.4 kernel
> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3
> > 50GB for WAL on 3Par, ext3
> > 
> > With PostgreSql 8.1.4
> > 
> > We don't have i/o bottle neck. 
> 
> Are you sure? What does iostat say during a pgbench? What parameters are
> you passing to pgbench?
> 
> Well in theory, upgrading to 2.6 kernel will help as well as making your
> WAL ext2 instead of ext3.
> 
> > Whatelse I can try to better tps? Someone told me I can should get tps
> 
> > over 1500, it is hard to believe.
> 
> 1500? Hmmm... I don't know about that, I can get 470tps or so on my
> measily dual core 3800 with 2gig of ram though.
> 
> Joshua D. Drake
> 
> 
> > 
> > Thanks
> > 
> > Marty
> > 
> > ---(end of 
> > broadcast)---
> > TIP 2: Don't 'kill -9' the postmaster
> > 
> 
> 

---(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: [PERFORM] Postgre SQL 7.1 cygwin performance issue.

[Tom Lane]

"Ravindran G - TLS, Chennai." <[EMAIL PROTECTED]> writes:
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000 (2 GB Memory,
> P4).

Egad :-(

If you are worried about performance, get off 7.1.  Even if you are not
worried about performance, get off 7.1.  It *will* eat your data someday.

A native Windows build of PG 8.1 will blow the doors off 7.1/cygwin as
to both performance and reliability.

I know little about Windows versions, but I suspect people will tell you
that a newer version of Windows would be a good idea too.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] How to get higher tps

[Marty Jia]

Joshua,

Here is 

shared_buffers = 8
fsync = on
max_fsm_pages = 35
max_connections = 1000
work_mem = 65536
effective_cache_size = 61
random_page_cost = 3
 
Here is pgbench I used:

pgbench -c 10 -t 1 -d HQDB

Thanks

Marty

-Original Message-
From: Joshua D. Drake [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 21, 2006 6:09 PM
To: Marty Jia
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to get higher tps

Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following 
> parameters
> 
> shared_buffers
> fsync
> max_fsm_pages
> max_connections
> shared_buffers
> work_mem
> max_fsm_pages
> effective_cache_size
> random_page_cost
> 
> I believe all above have right size and values, but I just can not get

> higher tps more than 300 testd by pgbench

What values did you use?

> 
> Here is our hardware
> 
> 
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
> 
> With PostgreSql 8.1.4
> 
> We don't have i/o bottle neck. 

Are you sure? What does iostat say during a pgbench? What parameters are
you passing to pgbench?

Well in theory, upgrading to 2.6 kernel will help as well as making your
WAL ext2 instead of ext3.

> Whatelse I can try to better tps? Someone told me I can should get tps

> over 1500, it is hard to believe.

1500? Hmmm... I don't know about that, I can get 470tps or so on my
measily dual core 3800 with 2gig of ram though.

Joshua D. Drake


> 
> Thanks
> 
> Marty
> 
> ---(end of 
> broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 


-- 

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
  http://www.commandprompt.com/



---(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: [PERFORM] Postgre SQL 7.1 cygwin performance issue.

[Chris Hoover]

Is there a reason you are not upgrading to PostgreSQL 8.1?  it will run natively on Windoze, and will give you much better performance.  7.1 is way out of date, and has a lot of bad issues in it.Upgrading will most likely fix this issue.
ChrisOn 8/22/06, Ravindran G - TLS, Chennai. <[EMAIL PROTECTED]> wrote:
Hi,We are using PostgreSQL 7.1 cygwin installed on Windows 2000 (2 GB Memory,P4).We understand that the maximum connections that can be set is 64 inPostgresql 7.1 version.The performance is very slow and some time the database is not getting
connected from our application because of this.Please advise us on how to increase the performance by setting anyattributes in configuration files ?.Find enclosed the configuration file.Thanks and regards,
RaviTo post a message to the mailing list, send it to  pgsql-performance@postgresql.org-Original Message-From: 
[EMAIL PROTECTED][mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 22, 2006 5:32 PMTo: ravig3Subject: 7E88-5CD9-AD0E : CONFIRM from pgsql-performance (subscribe)
__The following request  "subscribe pgsql-performance ravig3 <[EMAIL PROTECTED]>"was sent toby ravig3 <
[EMAIL PROTECTED]>.To accept or reject this request, please do one of the following:1. If you have web browsing capability, visit<
http://mail.postgresql.org/mj/mj_confirm/domain=postgresql.org?t=7E88-5CD9-AD0E>   and follow the instructions there.2. Reply to [EMAIL PROTECTED]
   with one of the following two commands in the body of the message:acceptreject   (The number 7E88-5CD9-AD0E must be in the Subject header)3. Reply to 
[EMAIL PROTECTED]   with one of the following two commands in the body of the message:accept 7E88-5CD9-AD0Ereject 7E88-5CD9-AD0EYour confirmation is required for the following reason(s):
  The subscribe_policy rule says that the "subscribe" command  must be confirmed by the person affected by the command.If you do not respond within 4 days, a reminder will be sent.
If you do not respond within 7 days, this token will expire,and the request will not be completed.If you would like to communicate with a person,send mail to 
[EMAIL PROTECTED].DISCLAIMERThe contents of this e-mail and any attachment(s) are confidential and intended for thenamed recipient(s) only. It shall not attach any liability on the originator or HCL or its
affiliates. Any views or opinions presented in this email are solely those of the author andmay not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction,dissemination, copying, disclosure, modification, distribution and / or publication of this
message without the prior written consent of the author of this e-mail is strictlyprohibited. If you have received this email in error please delete it and notify the senderimmediately. Before opening any mail and attachments please check them for viruses and
defect.---(end of broadcast)---TIP 6: explain analyze is your friend

Re: [PERFORM] How to get higher tps

[Marty Jia]

Hi, Mark

Thanks, here is our hardware info:

RAID 10, using 3Par virtual volume technology across ~200 physical FC
disks.  4 virtual disks for PGDATA, striped with LVM into one volume, 2
virtual disks for WAL, also striped.  SAN attached with Qlogic SAN
surfer multipathing to load balance each LUN on two 2GBs paths.  HBAs
are Qlogic 2340's.  16GB host cache on 3Par.

Detailed major config values

shared_buffers = 8
fsync = on
max_fsm_pages = 35
max_connections = 1000
work_mem = 65536
effective_cache_size = 61
random_page_cost = 3


Marty
 

-Original Message-
From: Mark Lewis [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 21, 2006 5:47 PM
To: Marty Jia
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to get higher tps

Not much we can do unless you give us more info about how you're testing
(pgbench setup), and what you've done with the parameters you listed
below.  It would also be useful if you told us more about your drive
array than just "3Par".  We need to know the RAID level, number/speed of
disks, whether it's got a battery-backed write cache that's turned on,
things like this.

Like Jeff just said, it's likely that you're waiting for rotational
latency, which would limit your maximum tps for sequential jobs based on
the number of disks in your array.  For example, a 2-disk array of 10k
RPM disks is going to max out somewhere around 333 tps.  (2*1/60).

-- Mark Lewis

 

On Mon, 2006-08-21 at 16:45 -0400, Marty Jia wrote:
> I'm exhausted to try all performance tuning ideas, like following 
> parameters
> 
> shared_buffers
> fsync
> max_fsm_pages
> max_connections
> shared_buffers
> work_mem
> max_fsm_pages
> effective_cache_size
> random_page_cost
> 
> I believe all above have right size and values, but I just can not get

> higher tps more than 300 testd by pgbench
> 
> Here is our hardware
> 
> 
> Dual Intel Xeon 2.8GHz
> 6GB RAM
> Linux 2.4 kernel
> RedHat Enterprise Linux AS 3
> 200GB for PGDATA on 3Par, ext3
> 50GB for WAL on 3Par, ext3
> 
> With PostgreSql 8.1.4
> 
> We don't have i/o bottle neck. 
> 
> Whatelse I can try to better tps? Someone told me I can should get tps

> over 1500, it is hard to believe.
> 
> Thanks
> 
> Marty
> 
> ---(end of 
> broadcast)---
> TIP 2: Don't 'kill -9' the postmaster

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

[PERFORM] Postgre SQL 7.1 cygwin performance issue.

[Ravindran G - TLS, Chennai.]

Hi,

We are using PostgreSQL 7.1 cygwin installed on Windows 2000 (2 GB Memory,
P4). 

We understand that the maximum connections that can be set is 64 in
Postgresql 7.1 version. 

The performance is very slow and some time the database is not getting
connected from our application because of this. 

Please advise us on how to increase the performance by setting any
attributes in configuration files ?. 

Find enclosed the configuration file. 

Thanks and regards,
Ravi


To post a message to the mailing list, send it to
  pgsql-performance@postgresql.org


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 22, 2006 5:32 PM
To: ravig3
Subject: 7E88-5CD9-AD0E : CONFIRM from pgsql-performance (subscribe)


__ 
The following request

  "subscribe pgsql-performance ravig3 <[EMAIL PROTECTED]>"

was sent to  
by ravig3 <[EMAIL PROTECTED]>.

To accept or reject this request, please do one of the following:

1. If you have web browsing capability, visit
 

   and follow the instructions there.

2. Reply to [EMAIL PROTECTED] 
   with one of the following two commands in the body of the message:

accept
reject

   (The number 7E88-5CD9-AD0E must be in the Subject header)

3. Reply to [EMAIL PROTECTED] 
   with one of the following two commands in the body of the message:
   
accept 7E88-5CD9-AD0E
reject 7E88-5CD9-AD0E

Your confirmation is required for the following reason(s):

  The subscribe_policy rule says that the "subscribe" command 
  must be confirmed by the person affected by the command.
  

If you do not respond within 4 days, a reminder will be sent.

If you do not respond within 7 days, this token will expire,
and the request will not be completed.

If you would like to communicate with a person, 
send mail to [EMAIL PROTECTED]
DISCLAIMER 
The contents of this e-mail and any attachment(s) are confidential and intended 
for the 

named recipient(s) only. It shall not attach any liability on the originator or 
HCL or its 

affiliates. Any views or opinions presented in this email are solely those of 
the author and 

may not necessarily reflect the opinions of HCL or its affiliates. Any form of 
reproduction, 

dissemination, copying, disclosure, modification, distribution and / or 
publication of this 

message without the prior written consent of the author of this e-mail is 
strictly 

prohibited. If you have received this email in error please delete it and 
notify the sender 

immediately. Before opening any mail and attachments please check them for 
viruses and 

defect.
#
# PostgreSQL configuration file
# -
#
# This file consists of lines of the form
#
#   name = value
#
# (The `=' is optional.) White space is collapsed, comments are
# introduced by `#' anywhere on a line.  The complete list of option
# names and allowed values can be found in the PostgreSQL
# documentation.  The commented-out settings shown in this file
# represent the default values.

# Any option can also be given as a command line switch to the
# postmaster, e.g., 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.


#


#
#   Connection Parameters
#
tcpip_socket = true
#ssl = false

max_connections = 64

#port = 5432 
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777

#virtual_host = ''

#krb_server_keyfile = ''


#
#   Shared Memory Size
#
shared_buffers = 2# 2*max_connections, min 16
#max_fsm_relations = 100# min 10, fsm is free space map
max_fsm_pages = 2  # min 1000, fsm is free space map
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8# min 4

#
#   Non-shared Memory Sizes
#
#sort_mem = 512 # min 32
#vacuum_mem = 8192  # min 1024


#
#   Write-ahead log (WAL)
#
#wal_files = 0 # range 0-64
wal_sync_method = open_sync   # the default varies across platforms:
#  # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#commit_delay = 0  # range 0-10
#commit_siblings = 5   # range 1-1000
#checkpoint_segments = 3   # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300  # in seconds, range 30-3600
#fsync = true


#
#   Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

#ksqo = false

effective_cache_size = 5000  # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025


#
#   GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11

Re: [PERFORM] Storage Options

[Michael Stone]

On Mon, Aug 21, 2006 at 02:50:51PM -0700, Jeff Davis wrote:

the NetApp over NFS, so I am not sure what performance to expect. Any
suggestions about using network storage like this for the database?


Don't. Unless you're using a very small (toy-scale) database, the netapp 
storage is way too expensive for the kind of usage you see with a 
database application. You're much better off buying much cheaper storage 
twice and using a database replication solution than either choking a 
really expensive netapp or getting lousy performance from the same. The 
netapps have their niche, but database storage isn't it. (Peformance in 
general really isn't it--the advantages are managability, snapshotting, 
and cross-platform data exchange. It may be that those factors are 
important enough to make that a good solution for your particular 
situation, but they're generally not particularly relevant in the 
postgres space.)



Is there any consensus on whether to do WAL on a RAID-1 and PGDATA on a
RAID-10 versus everything on a RAID-10? How does the number of disks I
have affect this decision (I will probably have 4-8 disks per server).


You can't get a good answer without testing with your actual data. I'd 
suspect that with such a low number of disks you're better off with a 
single array, assuming that you have a good bbu raid controller and 
assuming that you're not doing write-mostly transaction work. But 
testing with your actual workload is the only way to really know.


Mike Stone

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

[PERFORM] Forcing index usage without 'enable_hashjoin = FALSE'

[Dan Langille]

I'm using PostgreSQL 8.1.4 and I'm trying to force the planner to use
an index.  With the index, I get executions times of 0.5 seconds. 
Without, it's closer to 2.5 seconds.

Compare these two sets of results (also provided at 
http://rafb.net/paste/results/ywcOZP66.html
should it appear poorly formatted below):

freshports.org=# \i test2.sql
 
QUERY PLAN
--
--
-
 Merge Join  (cost=24030.39..24091.43 rows=3028 width=206) (actual 
time=301.301..355.261 rows=3149 loops=1)
   Merge Cond: ("outer".id = "inner".category_id)
   ->  Sort  (cost=11.17..11.41 rows=97 width=4) (actual 
time=0.954..1.300 rows=95 loops=1)
 Sort Key: c.id
 ->  Seq Scan on categories c  (cost=0.00..7.97 rows=97 
width=4) (actual time=0.092..0.517 rows=97 loops=1)
   ->  Sort  (cost=24019.22..24026.79 rows=3028 width=206) (actual 
time=300.317..314.114 rows=3149 loops=1)
 Sort Key: p.category_id
 ->  Nested Loop  (cost=0.00..23844.14 rows=3028 width=206) 
(actual time=0.082..264.459 rows=3149 loops=1)
   ->  Seq Scan on ports p  (cost=0.00..6141.11 rows=3028 
width=206) (actual time=0.026..133.575 rows=3149 loops=1)
 Filter: (status = 'D'::bpchar)
   ->  Index Scan using element_pkey on element e  
(cost=0.00..5.83 rows=1 width=4) (actual time=0.022..0.026 rows=1 
loops=3149)
 Index Cond: ("outer".element_id = e.id)
 Total runtime: 369.869 ms
(13 rows)

freshports.org=# set enable_hashjoin = true;
SET
freshports.org=# \i test2.sql
   QUERY PLAN
--
--
 Hash Join  (cost=6156.90..13541.14 rows=3028 width=206) (actual 
time=154.741..2334.366 rows=3149 loops=1)
   Hash Cond: ("outer".category_id = "inner".id)
   ->  Hash Join  (cost=6148.68..13472.36 rows=3028 width=206) 
(actual time=153.801..2288.792 rows=3149 loops=1)
 Hash Cond: ("outer".id = "inner".element_id)
 ->  Seq Scan on element e  (cost=0.00..4766.70 rows=252670 
width=4) (actual time=0.022..1062.626 rows=252670 loops=1)
 ->  Hash  (cost=6141.11..6141.11 rows=3028 width=206) 
(actual time=151.105..151.105 rows=3149 loops=1)
   ->  Seq Scan on ports p  (cost=0.00..6141.11 rows=3028 
width=206) (actual time=0.027..131.072 rows=3149 loops=1)
 Filter: (status = 'D'::bpchar)
   ->  Hash  (cost=7.97..7.97 rows=97 width=4) (actual 
time=0.885..0.885 rows=97 loops=1)
 ->  Seq Scan on categories c  (cost=0.00..7.97 rows=97 
width=4) (actual time=0.076..0.476 rows=97 loops=1)
 Total runtime: 2346.877 ms
(11 rows)

freshports.org=#

Without leaving "enable_hashjoin = false", can you suggest a way to 
force the index usage?

FYI, the query is:

explain analyse
SELECT P.id,
   P.category_id,
   P.version as version,
   P.revisionas revision,
   P.element_id,
   P.maintainer,
   P.short_description,
   to_char(P.date_added - SystemTimeAdjust(), 'DD Mon  
HH24:MI:SS') as date_added,
   P.last_commit_id  as last_change_log_id,
   P.package_exists,
   P.extract_suffix,
   P.homepage,
   P.status,
   P.broken,
   P.forbidden,
   P.ignore,
   P.restricted,
   P.deprecated,
   P.no_cdrom,
   P.expiration_date,
   P.latest_link
  FROM categories C, ports P JOIN element E on P.element_id = E.id
 WHERE P.status  = 'D'
   AND P.category_id = C.id;

-- 
Dan Langille : Software Developer looking for work
my resume: http://www.freebsddiary.org/dan_langille.php



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster