date:20100625

Dear Greg/Kevin/List ,

Many thanks for the comments regarding the params, I am however able to
change an
experiment on production in a certain time window , when that arrives i
shall post
my observations.

Rajesh Kumar Mallah.
Tradeindia.com - India's Largest B2B eMarketPlace.

Re: [PERFORM] Architecting a database

2010-06-25 Thread Bryan Hinton

Interesting point you made about the read to write ratio of 1 to 15.
How frequently will you be adding new entities or in the case of storing the
customers in one database table, how frequently will you be adding new
objects of a certain entity type. How many entity types do you foresee
existing? i.e. "Customer?" Will Customer have subtypes or is a Customer the
single entity in the database?
How frequent and for how long are write operations and are they heavily
transaction based?  Will you need to support complex reporting in the
future?   What is the max number of customers?  And how much data
(approximate) will a single customer record consume in bytes?   At what rate
does it grow? (in bytes)
Will your system need to support any type of complex reporting in the future
(despite it being write intensive)?

I'd take a look at memcached, plproxy, pgpool, and some of the other cool
stuff in the postgresql community.
At a minimum, it might help you architect the system in such a manner that
you don't box yourself in.
Last, KV stores for heavy write intensive operations in distributed
environments are certainly interesting - a hybrid solution could work.

Sounds like a fun project!

Bryan

On Fri, Jun 25, 2010 at 7:02 PM, Greg Smith  wrote:

> Kevin Grittner wrote:
>
>> A schema is a logical separation within a database.  Table
>> client1.account is a different table from client2.account.  While a
>> user can be limited to tables within a single schema, a user with
>> rights to all the tables can join between them as needed.  You could
>> put common reference data in a public schema which all users could
>> access in addition to their private schemas
>>
>
> My guess would be that this app will end up being best split by schema.  I
> wonder whether it *also* needs to be split by database, too.  2000 clusters
> is clearly a nightmare, and putting all the client data into one big table
> has both performance and security issues; that leaves database and schema as
> possible splits.  However, having 2000 databases in a cluster is probably
> too many; having 2000 schemas in a database might also be too many.  There
> are downsides to expanding either of those to such a high quantity.
>
> In order to keep both those in the domain where they perform well and are
> managable, it may be that what's needed is, say, 50 databases with 40
> schemas each, rather than 2000 of either.  Hard to say the ideal ratio.
>  However, I think that at the application design level, it would be wise to
> consider each client as having a database+schema pair unique to them, and
> with the assumption some shared data may need to be replicated to all the
> databases in the cluster.  Then it's possible to shift the trade-off around
> as needed once the app is built.  Building that level of flexibility in
> shouldn't be too hard if it's in the design from day one, but it would be
> painful bit of refactoring to do later.  Once there's a prototype, then some
> benchmark work running that app could be done to figure out the correct
> ratio between the two.  It might even make sense to consider full
> scalability from day one and make the unique client connection info
> host:port:database:schema.
>
> P.S. Very refreshing to get asked about this before rather than after a
> giant app that doesn't perform well is deployed.
>
> --
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> g...@2ndquadrant.com   www.2ndQuadrant.us
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] WAL+Os on a single disk

2010-06-25 Thread Robert Haas

On Thu, Jun 24, 2010 at 10:55 AM, Anj Adu  wrote:
> What would you recommend to do a quick test for this? (i.e WAL on
> internal disk vs WALon the 12 disk raid array )?

Maybe just pgbench?

http://archives.postgresql.org/pgsql-performance/2010-06/msg00223.php

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

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

Re: [PERFORM] Architecting a database


Kevin Grittner wrote:

A schema is a logical separation within a database.  Table
client1.account is a different table from client2.account.  While a
user can be limited to tables within a single schema, a user with
rights to all the tables can join between them as needed.  You could
put common reference data in a public schema which all users could
access in addition to their private schemas


My guess would be that this app will end up being best split by schema.  
I wonder whether it *also* needs to be split by database, too.  2000 
clusters is clearly a nightmare, and putting all the client data into 
one big table has both performance and security issues; that leaves 
database and schema as possible splits.  However, having 2000 databases 
in a cluster is probably too many; having 2000 schemas in a database 
might also be too many.  There are downsides to expanding either of 
those to such a high quantity.


In order to keep both those in the domain where they perform well and 
are managable, it may be that what's needed is, say, 50 databases with 
40 schemas each, rather than 2000 of either.  Hard to say the ideal 
ratio.  However, I think that at the application design level, it would 
be wise to consider each client as having a database+schema pair unique 
to them, and with the assumption some shared data may need to be 
replicated to all the databases in the cluster.  Then it's possible to 
shift the trade-off around as needed once the app is built.  Building 
that level of flexibility in shouldn't be too hard if it's in the design 
from day one, but it would be painful bit of refactoring to do later.  
Once there's a prototype, then some benchmark work running that app 
could be done to figure out the correct ratio between the two.  It might 
even make sense to consider full scalability from day one and make the 
unique client connection info host:port:database:schema.


P.S. Very refreshing to get asked about this before rather than after a 
giant app that doesn't perform well is deployed.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [PERFORM] Architecting a database

On 6/25/10 3:28 PM, Kevin Grittner wrote:

wrote:

With the PostgreSQL type tables I am not so certain how the data
is arranged within the one file. Does having the data all in one
database allow PostgreSQL to better utilize indexes and caches or
does having a number of smaller databases provide performance
increases? In case it is important, there are 2000 clients
involved, so that would be 2000 databases if I followed my current
FoxPro related structure.

The implications of putting multiple clients in a table, with a
client's rows identified by a client_id column, are probably fairly
obvious. If many of those 2,000 clients have tables with millions of
rows, performance could suffer without very careful indexing,
managing tables with billions of rows can become challenging, and
there could be concerns about how to ensure that data from one
client isn't accidentally shown to another.

You should also ask whether there are social (that is, nontechncal) reasons to
avoid multiple clients per table.

When a customer asks about security and you tell them, "You get your own database, nobody else
can log in," they tend to like that. If you tell them that their data is mixed with everyone
else's, but "we've done a really good job with our app software and we're pretty sure there
are no bugs that would let anyone see your data," that may not fly.

People will trust Postgres security (assuming you actually do it right) because
it's an open source, trusted product used by some really big companies. But
your own app? Do you even trust it?

Even if your application IS secure, it may not matter. It's what the customer
believes or worries about that can sell your product.

We've also found another really good reason for separate databases. It lets
you experiment without any impact on anything else. We have scripts that can
create a database in just a few minutes, load it up, and have it ready to demo
in just a few minutes. If we don't end up using it, we just blow it off and
its gone. No other database is impacted at all.

Craig

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

Re: [PERFORM] Architecting a database

 wrote:

> With the dBase and ISAM tables I have a good idea of how to handle
> them since I have been working with them since dBASE originally
> came out.

Ah, someone with whom I can reminisce about CP/M and WordStar?  :-)

> With the PostgreSQL type tables I am not so certain how the data
> is arranged within the one file. Does having the data all in one
> database allow PostgreSQL to better utilize indexes and caches or
> does having a number of smaller databases provide performance
> increases? In case it is important, there are 2000 clients
> involved, so that would be 2000 databases if I followed my current
> FoxPro related structure.

Well, there are many options here.  You could have:
 - one PostgreSQL cluster for each client,
 - one database for each client (all in one cluster),
 - one schema for each client (all in one database), or
 - a client_id column in each table to segregate data.

The first would probably be a maintenance nightmare; it's just
listed for completeness.  The cluster is the level at which you
start and stop the database engine, do real-time backups through the
database transaction logging, etc.  You probably don't want to do
that individually for each of 2,000 clients, I'm assuming.  Besides
that, each cluster has its own memory cache, which would probably be
a problem for you.  (The caching issues go away for all the
following options.)

The database is the level at which you can get a connection.  You
can see some cluster-level resources within all databases, like the
list of databases and the list of users, but for the most part, each
database is independent, even though they're running in the same
executable engine.  It would be relatively easy to keep the whole
cluster (all databases) backed up (especially after 9.0 is release
this summer), and you could have a cluster on another machine for
standby, if desired.  You are able to do dumps of individual
databases, but only as snapshots of a moment in time or through
external tools.  It's hard to efficiently join data from a table in
one database to a table in another.

A schema is a logical separation within a database.  Table
client1.account is a different table from client2.account.  While a
user can be limited to tables within a single schema, a user with
rights to all the tables can join between them as needed.  You could
put common reference data in a public schema which all users could
access in addition to their private schemas.

The implications of putting multiple clients in a table, with a
client's rows identified by a client_id column, are probably fairly
obvious. If many of those 2,000 clients have tables with millions of
rows, performance could suffer without very careful indexing,
managing tables with billions of rows can become challenging, and
there could be concerns about how to ensure that data from one
client isn't accidentally shown to another.

Hopefully that's enough to allow you to make a good choice.  If any
of that wasn't clear, please ask.

-Kevin

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

Re: [PERFORM] Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)

2010-06-25 Thread Tom Molesworth


On 25/06/10 20:00, Rajesh Kumar Mallah wrote:

Dear tom, we have autocommit off in dbi. Any commit or rollback from
the persistent modperl process immediately issues begin work; if the
modperl process is waiting for request the database backend remains in
idle in transaction state. Unless we modify data in a http request we
neighter issue a commit nor rollback.
   


The backend shouldn't go to 'idle in transaction' state until there is 
some activity within the transaction. I've attached an example script to 
demonstrate this - note that even SELECT queries will leave the handle 
as 'IDLE in transaction' unless you've changed the transaction isolation 
level from the default.


Any queries that are idle in transaction will block connection pooling 
and cause old versions of table rows to hang around, as described in 
other replies. Note that this is nothing to do with mod_perl, it's 
purely due to the way transactions are handled - a one-off script would 
also have this issue, but on exit issues an implicit rollback and 
disconnects.


Typically your database wrapper would handle this (I think DBIx::Class 
should take care of this automatically, although I haven't used it myself).


Tom



dbiPgConnectionHandling.pl
Description: Perl program

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

[PERFORM] Architecting a database

2010-06-25 Thread tony

I am in the process of moving a system that has been built around FoxPro
tables for the last 18 years into a PostgreSQL based system.

Over time I came up with decent strategies for making the FoxPro tables
work well with the workload that was placed on them, but we are getting to
the point that the locking mechanisms are causing problems when some of
the more used tables are being written to.

With the FoxPro tables I had one directory that contained the tables that
had global data that was common to all clients. Things like documents that
had been received and logged, checks that had been cut, etc. Then each
client had his own directory which housed tables that had information
relating to that specific client. Setting things up like this kept me from
having any tables that were too terribly large so record addition and
index creation were not very time consuming.

I am wondering how I should architect this in PostgreSQL. Should I follow
a similar strategy and have a separate database for each client and one
database that contains the global data? With the dBase and ISAM tables I
have a good idea of how to handle them since I have been working with them
since dBASE originally came out. With the PostgreSQL type tables I am not
so certain how the data is arranged within the one file. Does having the
data all in one database allow PostgreSQL to better utilize indexes and
caches or does having a number of smaller databases provide performance
increases? In case it is important, there are 2000 clients involved, so
that would be 2000 databases if I followed my current FoxPro related
structure. Of course, I suppose it is always possible to combine a number
of groups into a database if the number of databases is an issue.

Tables within the client specific databases are generally name and address
information as well as tables for 10 different types of accounts which
require different structures and those tables hold anywhere from 10,000
transactions a piece for some smaller groups and 1 million for larger
groups. I believe we have read to write ratio of about 1 to 15.

Thanks for any input.


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

Re: [PERFORM] pgbench results on a new server


Craig James wrote:

I've got a new server and want to make sure it's running well.


Any changes to the postgresql.conf file?  Generally you need at least a 
moderate shared_buffers (1GB or so at a minimum) and checkpoint_segments 
(32 or higher) in order for the standard pgbench test to give good results.




pgbench -c20 -t 5000 -U test
tps = 5789
pgbench -c30 -t  -U test
tps = 6961
pgbench -c40 -t 2500 -U test
tps = 2945


General numbers are OK, the major drop going from 30 to 40 clients is 
larger than it should be.  I'd suggest running the 40 client count one 
again to see if that's consistent.  If it is, that may just be pgbench 
itself running into a problem.  It doesn't handle high client counts 
very well unless you use the 9.0 version that supports multiple pgbench 
workers with the "-j" option.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [PERFORM] pgbench results on a new server

2010-06-25 Thread Scott Marlowe

On Fri, Jun 25, 2010 at 2:53 PM, Craig James  wrote:
> I've got a new server and want to make sure it's running well.  Are these
> pretty decent numbers?
>
> 8 cores (2x4 Intel Nehalem 2 GHz)
> 12 GB memory
> 12 x 7200 SATA 500 GB disks
> 3WARE 9650SE-12ML RAID controller with BBU
>  WAL on ext2, 2 disks: RAID1 500GB, blocksize=4096
>  Database on ext4, 8 disks: RAID10 2TB, stripe size 64K, blocksize=4096
> Ubuntu 10.04 LTS (Lucid)
> Postgres 8.4.4
>
> pgbench -i -s 100 -U test
> pgbench -c 5 -t 2 -U test
> tps = 4903
> pgbench -c 10 -t 1 -U test
> tps = 4070
> pgbench -c20 -t 5000 -U test
> tps = 5789
> pgbench -c30 -t  -U test
> tps = 6961
> pgbench -c40 -t 2500 -U test
> tps = 2945

Numbers are okay, but you likely need much longer tests to see how
they average out with the bgwriter / checkpoints happening, and keep
track of your IO numbers to see where your dips are.  I usually run
pgbench runs, once they seem to get decent numbers, for several hours
non-stop.  Sometimes days during burn in.  Note that running pgbench
on a machine other than the actual db is often a good idea so you're
not measuring how fast pgbench can run in contention with your own
database.

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

[PERFORM] Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)

Dear tom, we have autocommit off in dbi. Any commit or rollback from
the persistent modperl process immediately issues begin work; if the
modperl process is waiting for request the database backend remains in
idle in transaction state. Unless we modify data in a http request we
neighter issue a commit nor rollback.

On 6/25/10, Tom Molesworth  wrote:
> On 25/06/10 16:59, Rajesh Kumar Mallah wrote:
>> when i reduce max_connections i start getting errors, i will see again
>> concurrent connections
>> during business hours. lot of our connections are in > transaction state> during business
>> this peculiar  behavior of  mod_perl servers have been discussed in
>> past i think. dont' remember
>> if there was any resolution.
>
> If connections spend any significant amount of time in  transaction> state, that might indicate you're not committing/rolling
> back after running queries - can you show an example of the code you're
> using?
>
> e.g. something like my $dbh = DBI->connect(...); my $sth =
> $dbh->prepare(q{select ... }); $sth->fetchall_arrayref; $sth->rollback;
>
> Tom
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

-- 
Sent from Gmail for mobile | mobile.google.com

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

[PERFORM] pgbench results on a new server


I've got a new server and want to make sure it's running well.  Are these 
pretty decent numbers?

8 cores (2x4 Intel Nehalem 2 GHz)
12 GB memory
12 x 7200 SATA 500 GB disks
3WARE 9650SE-12ML RAID controller with BBU
  WAL on ext2, 2 disks: RAID1 500GB, blocksize=4096
  Database on ext4, 8 disks: RAID10 2TB, stripe size 64K, blocksize=4096
Ubuntu 10.04 LTS (Lucid)
Postgres 8.4.4

pgbench -i -s 100 -U test
pgbench -c 5 -t 2 -U test
tps = 4903
pgbench -c 10 -t 1 -U test
tps = 4070
pgbench -c20 -t 5000 -U test
tps = 5789
pgbench -c30 -t  -U test
tps = 6961
pgbench -c40 -t 2500 -U test
tps = 2945

Thanks,
Craig



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

[PERFORM] Any recent AMD purchases?

I'm trying to find someone who has a system with an AMD "Magny Cours" 
6100 series processor in it, like the Opteron 6174 or 6176 SE, who'd be 
willing to run a short test for me during an idle period to collect some 
performance data about it.  Can't be running Windows, probably easiest 
to compile the test programs under Linux.  If you have one of those 
processors and would be willing to help me out, please drop me an 
off-list note and I'll tell you what I'm looking for.  Will need 
permission to publish the results to the community.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [PERFORM] Write performance

2010-06-25 Thread Scott Carey

On Jun 24, 2010, at 6:16 AM, Janning wrote:

> On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote:
>> On Thu, 24 Jun 2010, Janning wrote:
>>> We have a 12 GB RAM machine with intel i7-975 and using
>>> 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)"
>> 
>> Those discs are 1.5TB, not 1.5GB.
> 
> sorry, my fault.
> 
>>> One disk for the system and WAL etc. and one SW RAID-0 with two disks for
>>> postgresql data. Our database is about 24GB.
>> 
>> Beware of RAID-0 - make sure you can recover the data when (not if) a disc
>> fails.
> 
> oh sorry again, its a raid-1 of course. shame on me.

If your WAL is not on RAID but your data is, you will lose data if the WAL log 
drive dies.  You will then have a difficult time recovering data from the data 
drives even though they are RAID protected.  Most likely indexes and some data 
will be corrupted since the last checkpoint.   I have lost a WAL before, and 
the result was a lot of corrupted system indexes that had to be rebuilt in 
single user mode, and one system table (stats related) that had to be purged 
and regenerated from scratch.  This was not fun.  Most of the data was fine, 
but the cleanup is messy if you lose WAL, and there is no guarantee that your 
data is safe if you don't have the WAL available.

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

Re: [PERFORM] Occasional giant spikes in CPU load


On 6/25/10 9:41 AM, Kevin Grittner wrote:

Craig James  wrote:


I always just assumed that lots of backends that would be harmless
if each one was doing very little.


Even if each is doing very little, if a large number of them happen
to make a request at the same time, you can have problems.  This is
exactly where a connection pool can massively improve both
throughput and response time.  If you can arrange it, you want a
connection pool which will put a limit on active database
transactions and queue requests to start a new transaction until one
of the pending ones finishes.


No, that's doesn't seem to be the case.  There is no external activity that 
triggers this huge spike in usage.  It even happens to our backup server when 
only one of us is using it to do a single query.  This problem seems to be 
triggered by Postgres itself, not by anything external.

Per Tom's suggestion, I think upgrading to 8.4.4 is the answer.  I'll learn 
more when our new hardware comes into use with a shiny new 8.4.4 installation.

Craig

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

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)


Rajesh Kumar Mallah wrote:
 default_statistics_target = 50 # pgtune wizard 2010-06-25  
(current 100 via default)
(*) effective_cache_size = 22GB # pgtune wizard 2010-06-25 (18GB , 
specified)

 checkpoint_segments = 16 # pgtune wizard 2010-06-25 (30 , specified)


You probably want to keep your existing values for all of these.  Your 
effective_cache_size setting may be a little low, but I wouldn't worry 
about changing that right now--you have bigger problems right now.


(*) maintenance_work_mem = 1GB # pgtune wizard 2010-06-25 (16MB via 
default)

(*) wal_buffers = 8MB # pgtune wizard 2010-06-25 ( 64kb , via default)
 checkpoint_completion_target = 0.9 # pgtune wizard 2010-06-25 
(0.5 via default)
 shared_buffers = 7680MB # pgtune wizard 2010-06-25 ( 4096 MB , 
specified)


These are all potentially better for your system, but you'll use more 
RAM if you make these changes.  For example, if you're having swap 
trouble, you definitely don't want to increase maintenance_work_mem.


I suspect that 8GB of shared_buffers is probably the most you want to 
use.  Most systems stop gaining any more benefit from that somewhere 
between 8GB and 10GB, and instead performance gets worse; it's better to 
be on the low side of that drop.  You can probably support 8GB just fine 
if you sort out the work_mem issues.


(*) max_connections = 80 # pgtune wizard 2010-06-25 ( 300 , ;-)  
specified )

 work_mem = 192MB # pgtune wizard 2010-06-25  (256MB , specified)


pgtune makes a guess at how many connections you'll have based on 
specified workload.  If you know you have more connections than that, 
you should specify that on the command line:


pgtune -c 300 ...

It will then re-compute the work_mem figure more accurately using that 
higher connection count.  Right now, it's guessing 192MB based on 80 
connections, which is on the high side of reasonable.  192MB with *300* 
connections is way oversized.  My rough computation says that if you 
tell it the number of connections correctly, pgtune will suggest to you 
around 50MB for work_mem.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [PERFORM] Occasional giant spikes in CPU load

2010-06-25 Thread Tom Lane

Craig James  writes:
> On 6/25/10 7:47 AM, Tom Lane wrote:
>> Any chance of going to 8.4?  If this is what I suspect, you really need
>> this 8.4 fix:
>> http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php
>> which eliminated the thundering-herd behavior that previous releases
>> exhibit when the sinval queue overflows.

> Yes, there is a chance of upgrading to 8.4.4.  I just bought a new server and 
> it has 8.4.4 on it, but it won't be online for a while so I can't compare 
> yet.  This may motivate me to upgrade the current servers to 8.4.4 too.  I 
> was pleased to see that 8.4 has a new upgrade-in-place feature that means we 
> don't have to dump/restore.  That really helps a lot.

I wouldn't put a lot of faith in pg_migrator for an 8.3 to 8.4
conversion ... it might work, but test it on a copy of your DB first.
Possibly it'll actually be recommendable in 9.0.

> A question about 8.4.4: I've been having problems with bloat.  I thought I'd 
> adjusted the FSM parameters correctly based on advice I got here, but 
> apparently not.  8.4.4 has removed the configurable FSM parameters 
> completely, which is very cool.  But ... if I upgrade a bloated database 
> using the upgrade-in-place feature, will 8.4.4 recover the bloat and return 
> it to the OS, or do I still have to recover the space manually (like 
> vacuum-full/reindex, or cluster, or copy/drop a table)?

No, an in-place upgrade to 8.4 isn't magically going to fix that.  This
might actually be sufficient reason to stick with the tried&true dump
and reload method, since you're going to have to do something fairly
expensive anyway to clean out the bloat.

regards, tom lane

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

Re: [PERFORM] Occasional giant spikes in CPU load


Craig James wrote:
if I upgrade a bloated database using the upgrade-in-place feature, 
will 8.4.4 recover the bloat and return it to the OS, or do I still 
have to recover the space manually (like vacuum-full/reindex, or 
cluster, or copy/drop a table)?


There's no way for an upgrade in place to do anything about bloat.  The 
changes in 8.4 reduce the potential sources for new bloat (like running 
out of a FSM pages), and the overhead of running VACUUM drops some due 
to things like the "Partial VACUUM" changes.  But existing bloated 
tables and indexes are moved forward to the new version without any change.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)

Rajesh Kumar Mallah  wrote:

> pgtune suggests the following:
> (current value are in braces via reason) , (*) indicates
> significant difference from current value.

Different people have come to different conclusions on some of these
settings.  I believe that's probably because differences in hardware
and workloads actually make the best choice different in different
environments, and it's not always clear how to characterize that to
make the best choice.  If yo get conflicting advice on particular
settings, I would strongly recommend testing to establish what works
best for your actual workload on your hardware and OS.

That said, my experience suggests...

> default_statistics_target = 50 # pgtune wizard 2010-06-25
> (current 100 via default)

Higher values add a little bit to the planning time of complex
queries, but reduce the risk of choosing a bad plan.  I would
recommend leaving this at 100 unless you notice problems with long
plan times.

> (*) maintenance_work_mem = 1GB # pgtune wizard 2010-06-25
> (16MB via default)

Yeah, I'd boost this to 1GB.

> checkpoint_completion_target = 0.9 # pgtune wizard 2010-06-25
> (0.5 via default)

I'd change this one by itself, and probably after some of the other
tuning is done, so you can get a good sense of "before" and "after".
I'm guessing that 0.9 would be better, but I would test it.

> (*) effective_cache_size = 22GB # pgtune wizard 2010-06-25
> (18GB , specified)

Unless you're running other processes on the box which consume a lot
of RAM, 18GB is probably lower than ideal, although this setting
isn't too critical -- it doesn't affect actual RAM allocation; it
just gives the optimizer a hint about how much might get cached.  A
higher setting encourages index use; a lower setting encourages
table scans.

> work_mem = 192MB # pgtune wizard 2010-06-25
> (256MB , specified)

With 300 connections, I think that either of these could lead you to
experience intermittent bursts of extreme swapping.  I'd drop it to
somewhere in the 16MB to 32MB range until I had a connection pool
configured such that it was actually keeping the number of active
connections much lower.

> (*) wal_buffers = 8MB # pgtune wizard 2010-06-25
> (64kb , via default)

Sure, I'd boost this.

> checkpoint_segments = 16 # pgtune wizard 2010-06-25
> (30 , specified)

If you have the disk space for the 30 segments, I wouldn't reduce
it.

> shared_buffers = 7680MB # pgtune wizard 2010-06-25
> (4096 MB , specified)

This one is perhaps the most sensitive to workload.  Anywhere
between 1GB and 8GB might be best for you.  Greg Smith has some
great advice on how to tune this for your workload.

> (*) max_connections = 80 # pgtune wizard 2010-06-25
> (300 , ;-) specified)
> 
> when i reduce max_connections i start getting errors, i will see
> again concurrent connections during business hours.

That's probably a good number to get to, but you have to reduce the
number of actual connections before you set the limit that low.

> lot of our connections are in  in transaction state

If any of these stay in that state for more than a minute or two,
you need to address that if you want to get your connection count
under control.  If any of them persist for hours or days, you need
to fix it to avoid bloat which can kill performance.

-Kevin

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

Re: [PERFORM] Occasional giant spikes in CPU load

Craig James  wrote:

> I always just assumed that lots of backends that would be harmless
> if each one was doing very little.

Even if each is doing very little, if a large number of them happen
to make a request at the same time, you can have problems.  This is
exactly where a connection pool can massively improve both
throughput and response time.  If you can arrange it, you want a
connection pool which will put a limit on active database
transactions and queue requests to start a new transaction until one
of the pending ones finishes.

-Kevin

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

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)

2010-06-25 Thread Tom Molesworth


On 25/06/10 16:59, Rajesh Kumar Mallah wrote:
when i reduce max_connections i start getting errors, i will see again 
concurrent connections
during business hours. lot of our connections are in transaction state> during business
this peculiar  behavior of  mod_perl servers have been discussed in 
past i think. dont' remember

if there was any resolution.


If connections spend any significant amount of time in transaction> state, that might indicate you're not committing/rolling 
back after running queries - can you show an example of the code you're 
using?


e.g. something like my $dbh = DBI->connect(...); my $sth = 
$dbh->prepare(q{select ... }); $sth->fetchall_arrayref; $sth->rollback;


Tom


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

Re: [PERFORM] Occasional giant spikes in CPU load


On 6/25/10 7:47 AM, Tom Lane wrote:

Craig James  writes:

On 6/24/10 9:04 PM, Tom Lane wrote:

sinval queue overflow comes to mind ... although that really shouldn't
happen if there's "no real load" on the server.  What PG version is
this?



8.3.10.  Upgraded based on your advice when I first asked this question.


Any chance of going to 8.4?  If this is what I suspect, you really need
this 8.4 fix:
http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php
which eliminated the thundering-herd behavior that previous releases
exhibit when the sinval queue overflows.


Yes, there is a chance of upgrading to 8.4.4.  I just bought a new server and 
it has 8.4.4 on it, but it won't be online for a while so I can't compare yet.  
This may motivate me to upgrade the current servers to 8.4.4 too.  I was 
pleased to see that 8.4 has a new upgrade-in-place feature that means we don't 
have to dump/restore.  That really helps a lot.

A question about 8.4.4: I've been having problems with bloat.  I thought I'd 
adjusted the FSM parameters correctly based on advice I got here, but 
apparently not.  8.4.4 has removed the configurable FSM parameters completely, 
which is very cool.  But ... if I upgrade a bloated database using the 
upgrade-in-place feature, will 8.4.4 recover the bloat and return it to the OS, 
or do I still have to recover the space manually (like vacuum-full/reindex, or 
cluster, or copy/drop a table)?


Or you could look at using connection pooling so you don't have quite
so many backends ...


I always just assumed that lots of backends that would be harmless if each one 
was doing very little.  If I understand your explanation, it sounds like that's 
not entirely true in pre-8.4.4 releases due to the sinval queue problems.

Thanks,
Craig

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

Re: [PERFORM] Occasional giant spikes in CPU load

Dear Criag,

also check for the possibility of installing sysstat in our system.
it goes a long way in collecting the system stats. you may
consider increasing the frequency of data collection by
changing the interval of cron job manually in /etc/cron.d/
normally its */10 , you may make it */2 for time being.
the software automatically maintains historical records
of data for 1 month.

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)

Dear List,

pgtune suggests the following:
(current value are in braces via reason) , (*) indicates significant
difference from current value.

 default_statistics_target = 50 # pgtune wizard 2010-06-25  (current 100
via default)
(*) maintenance_work_mem = 1GB # pgtune wizard 2010-06-25 (16MB via default)
 checkpoint_completion_target = 0.9 # pgtune wizard 2010-06-25 (0.5 via
default)
(*) effective_cache_size = 22GB # pgtune wizard 2010-06-25 (18GB ,
specified)
 work_mem = 192MB # pgtune wizard 2010-06-25  (256MB , specified)
(*) wal_buffers = 8MB # pgtune wizard 2010-06-25 ( 64kb , via default)
 checkpoint_segments = 16 # pgtune wizard 2010-06-25 (30 , specified)
 shared_buffers = 7680MB # pgtune wizard 2010-06-25 ( 4096 MB ,
specified)
(*) max_connections = 80 # pgtune wizard 2010-06-25 ( 300 , ;-)  specified )

when i reduce max_connections i start getting errors, i will see again
concurrent connections
during business hours. lot of our connections are in  during business
this peculiar  behavior of  mod_perl servers have been discussed in past i
think. dont' remember
if there was any resolution.

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)

I changed shared_buffers from 10G to 4G ,
swap usage has almost become nil.

# free
 total   used   free sharedbuffers cached
Mem:  32871276   245758248295452  0  11064   22167324
-/+ buffers/cache:2397436   30473840
Swap:  41929123524192560

I also observed that there was a huge IO wait and load spike initially
which gradually reduced to normal levels. Now things seems to be
fine. but real test shall be during business hours.

vmstat output:
http://pastebin.com/ygu8gUhS

the iowait now is very respectable < 10% and CPU is idling most of
the time.

# vmstat 10
procs ---memory-- ---swap-- -io --system--
-cpu--
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id wa st
 2  1352 8482444  11336 2229910010   450   20800
50  6 39  5  0
 4  0352 8393840  11352 2230448400   480   163 9260 12717
32  4 62  3  0
 5  1352 8474788  11360 2230898000   304   445 8295 12358
28  4 67  2  0
 3  0352 8370672  11376 2231667600   648   158 8760 13214
38  4 55  3  0
11  0352 8193824  11392 2232357200   621   577 8800 13163
37  4 56  3  0
 2  0352 8229012  11408 2232666400   169   405 9588 13696
34  4 61  1  0
 6  1352 8319176  11424 2233314400   559   170 8830 12929
32  4 61  3  0

I shall also try pgtune in a while.

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

Re: [PERFORM] Occasional giant spikes in CPU load

2010-06-25 Thread Tom Lane

Craig James  writes:
> On 6/24/10 9:04 PM, Tom Lane wrote:
>> sinval queue overflow comes to mind ... although that really shouldn't
>> happen if there's "no real load" on the server.  What PG version is
>> this?

> 8.3.10.  Upgraded based on your advice when I first asked this question.

Any chance of going to 8.4?  If this is what I suspect, you really need
this 8.4 fix:
http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php
which eliminated the thundering-herd behavior that previous releases
exhibit when the sinval queue overflows.

If you're stuck on 8.3 then you are going to have to modify your
application's behavior to eliminate sinval overflows.  If the overall
system load isn't high then I would have to guess that the problem is
some individual sessions sitting "idle in transaction" for long periods,
long enough that a number of DDL operations happen elsewhere.

You could also consider throwing memory at the problem by raising the
sinval queue size.  That'd require a custom build since it's not exposed
as a configurable parameter, but it'd be a one-line patch I think.

Or you could look at using connection pooling so you don't have quite
so many backends ...

regards, tom lane

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

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)

Rajesh Kumar Mallah  wrote:

> its now non business hours and
> SELECT procpid,current_query   from pg_stat_activity where
> current_query not ilike '%idle%' ;
> is just 5-10, i am yet to measure it during business hours.

Be careful about ' in transaction' status.  Those are a
problem if the transaction remains active for very long, because
vacuum (autovacuum or otherwise) can't free space for dead rows
which could still be visible to the ' in transaction'
connection.  It's normal to see this status briefly between
statements in a transaction, but it's a problem if a connection just
sits there in this status.

-Kevin

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

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)

Dear List,

Hmmm , lemme test efficacy of pg_tune.
I would reduce shared buffers also.

regarding concurrent queries:

its now non business hours and
SELECT procpid,current_query   from pg_stat_activity where
current_query not ilike '%idle%' ;
is just 5-10, i am yet to measure it during business hours.

Warm Regds
Rajesh Kumar Mallah.

On Fri, Jun 25, 2010 at 4:58 PM, Yeb Havinga  wrote:
> Rajesh Kumar Mallah wrote:
>>
>> A scary phenomenon is being exhibited by the server , which is the server
>> is slurping all the swap suddenly
>>   8  1 4192912 906164   6100 2787364000  2277   858 13440 16235
>> 63  8 19 10  0
>>
>> I reduced work_mem from 4GB to 2GB to 512MB (now). I clearly remember that
>> this
>> abnormal consumption of swap was NOT there even when work_mem was 4GB.
>> eg during happier times swap utilisation was: http://pastebin.com/bnE1pFZ9
>>  the question is whats making postgres slurp the swap? i am posting my
>> current postgresql.conf
>> once again.
>>
>> # cat postgresql.conf  | grep -v "^\s*#" | grep -v "^\s*$"
>> listen_addresses = '*'  # what IP address(es) to listen on;
>> port = 5432 # (change requires restart)
>> max_connections = 300   # (change requires restart)
>>
>
> Hello Rajesh,
>
> In constrast with e.g. shared_buffers and effective_cache_size, work_mem is
> amount of memory per 'thing' (e.g. order/group by) that wants some working
> memory, so even a single backend can use several pieces of work_mem memory.
>
> Looking at your postgresql.conf, other memory values seem a bit too high as
> well for a 32GB ram server. It is probably a good idea to use pgtune (on
> pgfoundry) to get some reasonable ball park settings for your hardware.
>
> regards,
> Yeb Havinga
>
>

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

Re: [PERFORM] Occasional giant spikes in CPU load


On 6/24/10 9:04 PM, Tom Lane wrote:

Craig James  writes:

So what is it that will cause every single Postgres backend to come to life at 
the same moment, when there's no real load on the server?  Maybe if a backend 
crashes?  Some other problem?


sinval queue overflow comes to mind ... although that really shouldn't
happen if there's "no real load" on the server.  What PG version is
this?


8.3.10.  Upgraded based on your advice when I first asked this question.


  Also, the pg_stat_activity view contents when this happens would
probably be more useful to look at than "top" output.


I'll try.  It's hard to discover anything because the whole machine is 
overwhelmed when this happens.  The only way I got the top(1) output was by 
running it high priority as root using nice(1).  I can't do that with a 
Postgres backend, but I'll see what I can do.

Craig

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

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)

2010-06-25 Thread Yeb Havinga


Rajesh Kumar Mallah wrote:

A scary phenomenon is being exhibited by the server , which is the server
is slurping all the swap suddenly
  
 8  1 4192912 906164   6100 2787364000  2277   858 13440 16235

63  8 19 10  0

I reduced work_mem from 4GB to 2GB to 512MB (now). I clearly remember that this
abnormal consumption of swap was NOT there even when work_mem was 4GB.
eg during happier times swap utilisation was: http://pastebin.com/bnE1pFZ9
  
the question is whats making postgres slurp the swap? i am posting my

current postgresql.conf
once again.

# cat postgresql.conf  | grep -v "^\s*#" | grep -v "^\s*$"
listen_addresses = '*'  # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 300   # (change requires restart)
  

Hello Rajesh,

In constrast with e.g. shared_buffers and effective_cache_size, work_mem 
is amount of memory per 'thing' (e.g. order/group by) that wants some 
working memory, so even a single backend can use several pieces of 
work_mem memory.


Looking at your postgresql.conf, other memory values seem a bit too high 
as well for a 32GB ram server. It is probably a good idea to use pgtune 
(on pgfoundry) to get some reasonable ball park settings for your hardware.


regards,
Yeb Havinga


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

Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)

2010-06-25 Thread Devrim GÜNDÜZ

On Fri, 2010-06-25 at 15:25 +0530, Rajesh Kumar Mallah wrote:
> shared_buffers = 10GB   # min 128kB
> work_mem = 512MB# min 64kB 

These are still pretty high IMHO. How many *concurrent* connections do
you have?
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

[PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)