Re: [PERFORM] Making the most of memory?

2008-01-25 Thread Florian Weimer
* Chris Browne:

>> A dedicated RAID controller with battery-backed cache of ssuficient
>> size and two mirrored disks should not perform that bad, and has the
>> advantage of easy availability.
>
> That won't provide as "souped up" performance as "WAL on SSD," and
> it's from technical people wishing for things that some of those
> things actually emerge...

For WAL (I/O which is mostly sequential), the proposed approach isn't
that bad.  You can easily get more than 15,000 write transactions per
second, with a single thread.  Good luck finding a SSD NAS with a
60 usec round-trip time. 8->

Something which directly speaks SATA or PCI might offer comparable
performance, but SSD alone isn't sufficient.

-- 
Florian Weimer<[EMAIL PROTECTED]>
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

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

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


Re: [PERFORM] Making the most of memory?

2008-01-24 Thread Merlin Moncure
On Jan 24, 2008 1:01 PM, Merlin Moncure <[EMAIL PROTECTED]> > I'll
give you a little hint about postgresql.conf...tuning shared
> buffers rarely has a huge impact on performance...the o/s will

oops. i meant to say the o/s will cache the files just fine...the
setting that _does_ affect query performance is work_mem, but this
applies to queries on a case by case basis.

merlin

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

   http://archives.postgresql.org


Re: [PERFORM] Making the most of memory?

2008-01-24 Thread Merlin Moncure
On Jan 22, 2008 11:11 PM, Joshua Fielek <[EMAIL PROTECTED]> wrote:
> The database itself is not that large -- a db_dump of the sql file as
> text is only about 110MB. I haven't checked the exact size of the actual
> data base, but the entire data directory is smaller than the available
> memory at about 385MB including logs and config files. This is a single
> database with a relatively small number of client connections (50 or so)
> making a fair number of smaller queries. This is not a massive data
> effort by any means at this time, but it will be growing.
>
> We have available currently ~4GB (8GB total) for Postgres. We will be
> moving to a server that will have about 24GB (32GB total) available for
> the database, with the current server becoming a hot backup, probably
> with slony or something similar to keep the databases in sync.

The database is cached in RAM.  As soon as the database files are read
for the first time, they will stay cached in the o/s basically forever
(in either o/s file cache or postgresql buffer cache) as long as there
are no other demands on memory...not likely in your case.  This also
means extra ram is not likely to help performance much if at all.

I'll give you a little hint about postgresql.conf...tuning shared
buffers rarely has a huge impact on performance...the o/s will

possible issues you might be having:
*) sync issues: asking drives to sync more often they can handle.
possible solutions...faster/more drives or ask database to sync less
(fsync off, or better transaction management)
*) cpu bound issues: poorly designed queries, or poorly designed
tables, bad/no indexes, etc
*) unrealistic expectations of database performance
*) not maintaining database properly, vacuum, etc
*) mvcc issues

maybe post your transaction load, and/or some slow queries you are dealing with.

merlin

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


Re: [PERFORM] Making the most of memory?

2008-01-24 Thread Chris Browne
[EMAIL PROTECTED] (Florian Weimer) writes:
>> So, that web site seems to list products starting at about 32GB in a
>> separate rack-mounted box with redundant everything. I'd be more
>> interested in just putting the WAL on an SSD device, so 500MB or 1GB
>> would be quite sufficient. Can anyone point me towards such a device?
>
> A dedicated RAID controller with battery-backed cache of ssuficient
> size and two mirrored disks should not perform that bad, and has the
> advantage of easy availability.

That won't provide as "souped up" performance as "WAL on SSD," and
it's from technical people wishing for things that some of those
things actually emerge...

It appears that the SSD market place is going pretty "nuts" right now
as vendors are hawking flash-based SSD devices that are specifically
targeted at replacing disk drives for laptops.

I agree that there would be a considerable value for DBMS applications
in having availability of a device that combines the strengths of both
Flash (persistence) and DRAM (sheer volume of IOPs) to provide
something better than they offered alone.  I expect that the standard
size for this is more likely to be 32GB than 1GB, what with modern
shrinkage of physical sizing...
-- 
let name="cbbrowne" and tld="linuxfinances.info" in String.concat "@" 
[name;tld];;
http://www3.sympatico.ca/cbbrowne/spiritual.html
"When we write programs that "learn", it turns out that we do and they
don't." -- Alan J. Perlis


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [PERFORM] Making the most of memory?

2008-01-24 Thread Chris Browne
[EMAIL PROTECTED] ("Scott Marlowe") writes:
> On Jan 23, 2008 1:57 PM, Guy Rouillier <[EMAIL PROTECTED]> wrote:
>> Scott Marlowe wrote:
>> > I assume you're talking about solid state drives?  They have their
>> > uses, but for most use cases, having plenty of RAM in your server will
>> > be a better way to spend your money.  For certain high throughput,
>> > relatively small databases (i.e. transactional work) the SSD can be
>> > quite useful.
>>
>> Unless somebody has changes some physics recently, I'm not understanding
>> the recent discussions of SSD in the general press.  Flash has a limited
>> number of writes before it becomes unreliable.  On good quality consumer
>
> Actually, I was referring to all SSD systems, some of which are based
> on flash memory, some on DRAM, sometimes backed by hard drives.
>
> There's always a use case for a given piece of tech.

Yeah, I could see an SSD making use of a mixture of technologies...
 - Obviously, it needs a pile of RAM.
 - Then, have a battery that can keep the RAM backed up for [a while].
 - If power goes out, then contents of RAM get copied out to the "flash"
   memory.

In this context, "flash" has several merits over disk drives.
Notably, the absence of moving mechanical parts means:
  - Hopefully lower power consumption than a disk drive
  - Less fragility than a disk drive
  - Quite likely the "flash" will be smaller than a disk drive

The fact that the number of writes may be limited should only be an
important factor if power goes out *INCREDIBLY* frequently, as data
only gets written upon power loss.

The combination of RAM + battery + flash looks like a real winner,
when they are combined using a protocol that takes advantage of their
strengths, and which doesn't rest on their weaknesses.
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/advocacy.html
Roses are red
Violets are blue
Some poems rhyme
But this one doesn't. 

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


Re: [PERFORM] Making the most of memory?

2008-01-24 Thread Florian Weimer
> So, that web site seems to list products starting at about 32GB in a
> separate rack-mounted box with redundant everything. I'd be more
> interested in just putting the WAL on an SSD device, so 500MB or 1GB
> would be quite sufficient. Can anyone point me towards such a device?

A dedicated RAID controller with battery-backed cache of ssuficient
size and two mirrored disks should not perform that bad, and has the
advantage of easy availability.

-- 
Florian Weimer<[EMAIL PROTECTED]>
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [PERFORM] Making the most of memory?

2008-01-24 Thread Matthew

On Wed, 23 Jan 2008, Brian Hurt wrote:

I think the original poster was talking about drives like these:
http://www.texmemsys.com/

Basically, they're not using Flash, they're just big ol' hunks of 
battery-backed RAM.  Not unlike a 10GB battery backed buffer for your raid, 
except there is no raid.


So, that web site seems to list products starting at about 32GB in a 
separate rack-mounted box with redundant everything. I'd be more 
interested in just putting the WAL on an SSD device, so 500MB or 1GB would 
be quite sufficient. Can anyone point me towards such a device?


It'd preferably have a form-factor of a normal 3.5" hard drive, with a 
normal SATA or SAS connection, a gigabyte of RAM, a battery, and a 
gigabyte of flash. When the power cuts, the device would copy the RAM over 
to the flash and then power down. The device would therefore get the write 
performance of normal RAM, without wasting space, and could 
(theoretically) be pretty cheap, and would improve the transaction speed 
of Postgres significantly.


If someone doesn't already make one, they should!

Matthew

---(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] Making the most of memory?

2008-01-24 Thread Merlin Moncure
On Jan 23, 2008 2:57 PM, Guy Rouillier <[EMAIL PROTECTED]> wrote:
> Unless somebody has changes some physics recently, I'm not understanding
> the recent discussions of SSD in the general press.  Flash has a limited
> number of writes before it becomes unreliable.  On good quality consumer
> grade, that's about 300,000 writes, while on industrial grade it's about
> 10 times that.  That's fine for mp3 players and cameras; even

wrong. at 1 million writes (which better flash drives can do)  wear
leveling, this can be disproved with a simple back of napkin
calculation...

the major problem with flash drives in the server space is actually
random write performance...if random write performed as well as random
read for flash ssd, you would be able to replace a stack of 15k sas
drives with a single flash ssd in terms of iops.

merlin

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


Re: [PERFORM] Making the most of memory?

2008-01-24 Thread Heikki Linnakangas

Joshua Fielek wrote:
We have an application that has been having some issues with performance 
within postgres 8.1.9 and later 8.2.5. The upgrade to 8.2.5 gained us a 
nice little performance increase just off the improved query 
optimization, but we are still having other performance issues.


What kind of performance issues are you having? A slow query?

What kind of transactions are you running? Read-only? A lot of updates? 
How many transactions per minute?


We have available currently ~4GB (8GB total) for Postgres. We will be 
moving to a server that will have about 24GB (32GB total) available for 
the database, with the current server becoming a hot backup, probably 
with slony or something similar to keep the databases in sync.


I've been monitoring the memory usage of postgres on the current system 
and it seems like none of the threads ever allocate more than about 
400MB total and about 80-90MB shared memory. It seems to me that since 
we have a very large chunk of memory relative to the database size we 
should be loading the entire database into memory. How can we be sure 
we're getting the most out of the memory we're allocating to postgres? 
What can we do to improve the memory usage, looking for performance 
first and foremost, on both the larger and smaller systems?


How are you measuring the amount of memory used? Which operating system 
are you using?


Those numbers don't seem unreasonable to me, though I would've expected 
a bit over ~300 MB of shared memory to be used given your shared_buffers 
setting.


On a database of ~400MB in size , I doubt you'll ever find use for more 
than 1-2 gigs of RAM.


Others have asked about your I/O system, but if the database stays in 
memory all the time, that shouldn't matter much. Except for one thing: 
fsyncs. Perhaps you're bottlenecked by the fact that each commit needs 
to flush the WAL to disk? A RAID array won't help with that, but a RAID 
controller with a battery-backed up cache will. You could try turning 
fsync=off to test that theory, but you don't want to do that in production.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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


Re: [PERFORM] Making the most of memory?

2008-01-24 Thread Steinar H. Gunderson
On Wed, Jan 23, 2008 at 07:54:24PM -0500, Greg Smith wrote:
> (a standard mechanical drive under heavy write load also wears out faster
> than one doing less work).

Wasn't this one of the myths that was dispelled in the Google disk paper a
while ago?

/* Steinar */
-- 
Homepage: http://www.sesse.net/

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

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


Re: [PERFORM] Making the most of memory?

2008-01-23 Thread Scott Marlowe
On Jan 23, 2008 1:57 PM, Guy Rouillier <[EMAIL PROTECTED]> wrote:
> Scott Marlowe wrote:
> > I assume you're talking about solid state drives?  They have their
> > uses, but for most use cases, having plenty of RAM in your server will
> > be a better way to spend your money.  For certain high throughput,
> > relatively small databases (i.e. transactional work) the SSD can be
> > quite useful.
>
> Unless somebody has changes some physics recently, I'm not understanding
> the recent discussions of SSD in the general press.  Flash has a limited
> number of writes before it becomes unreliable.  On good quality consumer

Actually, I was referring to all SSD systems, some of which are based
on flash memory, some on DRAM, sometimes backed by hard drives.

There's always a use case for a given piece of tech.

---(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] Making the most of memory?

2008-01-23 Thread Greg Smith

On Wed, 23 Jan 2008, Guy Rouillier wrote:

Flash has a limited number of writes before it becomes unreliable.  On 
good quality consumer grade, that's about 300,000 writes, while on 
industrial grade it's about 10 times that.


The main advance that's made SSD practical given the write cycle 
limitation is increasing sophisticated wear leveling: 
http://en.wikipedia.org/wiki/Wear_levelling


The best devices now use static wear levelling; overviews at 
http://en.wikipedia.org/wiki/Static_Wear_Leveling and 
http://www.storagesearch.com/siliconsys-art1.html


The basic idea is that the number of writes to each block is tracked, and 
as it approaches the limit that block gets swapped with one that has been 
more read-only.  So essentially the number of writes before failure 
approaches something closer to 1M x number of blocks.  This means that as 
the size of the device goes up, so does its longevity.  If you believe the 
hype, the combination in the increase in size of designs with these more 
sophisticated wear-levelling approaches has now crossed the line where 
it's more likely a standard moving-parts hard drive will fail first if you 
compare it to a similarly sized SDD doing the same job (a standard 
mechanical drive under heavy write load also wears out faster than one 
doing less work).


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

  http://archives.postgresql.org


Re: [PERFORM] Making the most of memory?

2008-01-23 Thread Craig James

Guy Rouillier wrote:

Scott Marlowe wrote:

I assume you're talking about solid state drives?  They have their
uses, but for most use cases, having plenty of RAM in your server will
be a better way to spend your money.  For certain high throughput,
relatively small databases (i.e. transactional work) the SSD can be
quite useful.


Unless somebody has changes some physics recently, I'm not understanding 
the recent discussions of SSD in the general press.  Flash has a limited 
number of writes before it becomes unreliable.  On good quality consumer 
grade, that's about 300,000 writes, while on industrial grade it's about 
10 times that.  That's fine for mp3 players and cameras; even 
professional photographers probably won't rewrite the same spot on a 
flash card that many times in a lifetime.  But for database 
applications, 300,000 writes is trivial. 3 million will go a lot longer, 
but in non-archival applications, I imagine even that mark won't take 
but a year or two to surpass.


One trick they use is to remap the physical Flash RAM to different logical 
addresses.  Typical apps update a small percentage of the data frequently, and 
the rest of the data rarely or never.  By shuffling the physical Flash RAM 
around, the media lasts a lot longer than a simple analysis might indicate.

Craig

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate


Re: [PERFORM] Making the most of memory?

2008-01-23 Thread A.M.


On Jan 23, 2008, at 2:57 PM, Guy Rouillier wrote:


Scott Marlowe wrote:

I assume you're talking about solid state drives?  They have their
uses, but for most use cases, having plenty of RAM in your server  
will

be a better way to spend your money.  For certain high throughput,
relatively small databases (i.e. transactional work) the SSD can be
quite useful.


Unless somebody has changes some physics recently, I'm not  
understanding the recent discussions of SSD in the general press.   
Flash has a limited number of writes before it becomes unreliable.   
On good quality consumer grade, that's about 300,000 writes, while  
on industrial grade it's about 10 times that.  That's fine for mp3  
players and cameras; even professional photographers probably won't  
rewrite the same spot on a flash card that many times in a  
lifetime.  But for database applications, 300,000 writes is  
trivial. 3 million will go a lot longer, but in non-archival  
applications, I imagine even that mark won't take but a year or two  
to surpass.


Please let outdated numbers rest in peace.
http://www.storagesearch.com/ssdmyths-endurance.html

Conclusion:
"With current technologies write endurance is not a factor you should  
be worrying about when deploying flash SSDs for server acceleration  
applications - even in a university or other analytics intensive  
environment. "


That said, postgresql is likely making assumptions about non-volatile  
storage that will need to be shattered once SSDs become more widely  
deployed. Perhaps SSDs will replace RAID BBUs and then the HDs  
themselves?


Cheers,
M

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


Re: [PERFORM] Making the most of memory?

2008-01-23 Thread Brian Hurt

Guy Rouillier wrote:


Scott Marlowe wrote:


I assume you're talking about solid state drives?  They have their
uses, but for most use cases, having plenty of RAM in your server will
be a better way to spend your money.  For certain high throughput,
relatively small databases (i.e. transactional work) the SSD can be
quite useful.



Unless somebody has changes some physics recently, I'm not 
understanding the recent discussions of SSD in the general press.  
Flash has a limited number of writes before it becomes unreliable.  On 
good quality consumer grade, that's about 300,000 writes, while on 
industrial grade it's about 10 times that.  That's fine for mp3 
players and cameras; even professional photographers probably won't 
rewrite the same spot on a flash card that many times in a lifetime.  
But for database applications, 300,000 writes is trivial. 3 million 
will go a lot longer, but in non-archival applications, I imagine even 
that mark won't take but a year or two to surpass.



I think the original poster was talking about drives like these:
http://www.texmemsys.com/

Basically, they're not using Flash, they're just big ol' hunks of 
battery-backed RAM.  Not unlike a 10GB battery backed buffer for your 
raid, except there is no raid.


Brian


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


Re: [PERFORM] Making the most of memory?

2008-01-23 Thread Guy Rouillier

Scott Marlowe wrote:

I assume you're talking about solid state drives?  They have their
uses, but for most use cases, having plenty of RAM in your server will
be a better way to spend your money.  For certain high throughput,
relatively small databases (i.e. transactional work) the SSD can be
quite useful.


Unless somebody has changes some physics recently, I'm not understanding 
the recent discussions of SSD in the general press.  Flash has a limited 
number of writes before it becomes unreliable.  On good quality consumer 
grade, that's about 300,000 writes, while on industrial grade it's about 
10 times that.  That's fine for mp3 players and cameras; even 
professional photographers probably won't rewrite the same spot on a 
flash card that many times in a lifetime.  But for database 
applications, 300,000 writes is trivial. 3 million will go a lot longer, 
but in non-archival applications, I imagine even that mark won't take 
but a year or two to surpass.


--
Guy Rouillier

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


Re: [PERFORM] Making the most of memory?

2008-01-23 Thread Rich
Josh what about the rest of your system?  What operating system?  Your
hardware setup. Drives? Raids?  What indices do you have setup for
these queries?  There are other reasons that could cause bad queries
performance.

On Jan 22, 2008 11:11 PM, Joshua Fielek <[EMAIL PROTECTED]> wrote:
>
> Hey folks --
>
> For starters, I am fairly new to database tuning and I'm still learning
> the ropes. I understand the concepts but I'm still learning the real
> world impact of some of the configuration options for postgres.
>
> We have an application that has been having some issues with performance
> within postgres 8.1.9 and later 8.2.5. The upgrade to 8.2.5 gained us a
> nice little performance increase just off the improved query
> optimization, but we are still having other performance issues.
>
> The database itself is not that large -- a db_dump of the sql file as
> text is only about 110MB. I haven't checked the exact size of the actual
> data base, but the entire data directory is smaller than the available
> memory at about 385MB including logs and config files. This is a single
> database with a relatively small number of client connections (50 or so)
> making a fair number of smaller queries. This is not a massive data
> effort by any means at this time, but it will be growing.
>
> We have available currently ~4GB (8GB total) for Postgres. We will be
> moving to a server that will have about 24GB (32GB total) available for
> the database, with the current server becoming a hot backup, probably
> with slony or something similar to keep the databases in sync.
>
> I've been monitoring the memory usage of postgres on the current system
> and it seems like none of the threads ever allocate more than about
> 400MB total and about 80-90MB shared memory. It seems to me that since
> we have a very large chunk of memory relative to the database size we
> should be loading the entire database into memory. How can we be sure
> we're getting the most out of the memory we're allocating to postgres?
> What can we do to improve the memory usage, looking for performance
> first and foremost, on both the larger and smaller systems?
>
> Here's the salient config items for the 8GB system:
>
> max_connections = 200# realistically we expect 50-150 open
> shared_buffers = 38000
> sort_mem = 1048576
> work_mem = 32000
> maintenance_work_mem = 32000
> max_fsm_pages = 480001# probably too large for the max_fsm_*
> max_fsm_relations = 2# items; one Db with ~400 tables.
> effective_cache_size = 212016# ~2GB, could probably double this
>
>
> Thanks,
> J
> --
> Joshua J. Fielek
> Sr. Software Engineer
> Concursive Corporation
> 223 East City Hall Ave., Suite 212
> Norfolk, VA 23510
> Phone  : (757) 627-3002x6656
> Mobile : (757) 754-4462
> Fax: (757) 627-8773
> Email  : [EMAIL PROTECTED]
> http://www.concursive.com
>
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

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

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


Re: [PERFORM] Making the most of memory?

2008-01-23 Thread Scott Marlowe
On Jan 23, 2008 8:01 AM, mike long <[EMAIL PROTECTED]> wrote:
> Scott,
>
> What are your thoughts on using one of those big RAM appliances for
> storing a Postgres database?

I assume you're talking about solid state drives?  They have their
uses, but for most use cases, having plenty of RAM in your server will
be a better way to spend your money.  For certain high throughput,
relatively small databases (i.e. transactional work) the SSD can be
quite useful.  For large reporting databases running into the terabyte
range they're prohibitively expensive.

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


Re: [PERFORM] Making the most of memory?

2008-01-23 Thread Bill Moran
In response to Joshua Fielek <[EMAIL PROTECTED]>:
> 
> Hey folks --
> 
> For starters, I am fairly new to database tuning and I'm still learning 
> the ropes. I understand the concepts but I'm still learning the real 
> world impact of some of the configuration options for postgres.
> 
> We have an application that has been having some issues with performance 
> within postgres 8.1.9 and later 8.2.5. The upgrade to 8.2.5 gained us a 
> nice little performance increase just off the improved query 
> optimization, but we are still having other performance issues.
> 
> The database itself is not that large -- a db_dump of the sql file as 
> text is only about 110MB. I haven't checked the exact size of the actual 
> data base, but the entire data directory is smaller than the available 
> memory at about 385MB including logs and config files. This is a single 
> database with a relatively small number of client connections (50 or so) 
> making a fair number of smaller queries. This is not a massive data 
> effort by any means at this time, but it will be growing.
> 
> We have available currently ~4GB (8GB total) for Postgres. We will be 
> moving to a server that will have about 24GB (32GB total) available for 
> the database, with the current server becoming a hot backup, probably 
> with slony or something similar to keep the databases in sync.
> 
> I've been monitoring the memory usage of postgres on the current system 
> and it seems like none of the threads ever allocate more than about 
> 400MB total and about 80-90MB shared memory. It seems to me that since 
> we have a very large chunk of memory relative to the database size we 
> should be loading the entire database into memory. How can we be sure 
> we're getting the most out of the memory we're allocating to postgres? 
> What can we do to improve the memory usage, looking for performance 
> first and foremost, on both the larger and smaller systems?

Every system is a little different.  I recommend you do some profiling.

First off, Install the pg_buffercache add-on.  This gives you an easy
view to see how much of your shared_buffers are being used, with a
query like:
select count(*) from pg_buffercache where reldatabase is not null;

There is also a lot of interesting information in the pg_stat_database
table, i.e.:
select sum(blks_hit) from pg_stat_database;
Which gives you the # of reads that were satisfied from shared_buffers,
or
select sum(blks_read) from pg_stat_database;
which gives you the # of reads that had to go to disk.

There are lots of other stats you can graph, but those are some that I
find particularly telling as to how things are being used.

>From there, I recommend that you graph those #s and any others that you
find interesting.  We use MRTG, but there are certainly other options.
Add that to stats collecting that you should be doing on machine data,
such as overall IO and CPU usage, and you start to get a pretty clear
view of what your machine is doing.

Note that you have to flip some stats collecting switches on in your
postgresql.conf file, and overall this can put some additional load on
your machine.  My opinion is that you're _FAR_ better off sizing your
hardware up a bit so that you can gather this data on a continual basis
than if you don't know what's going on.

Another thing to do is turn on statement timing.  This will create huge
log files and increase your IO traffic considerably, but the data involved
is priceless.  Run it through pgFouine periodically (possibly on a schedule
via a cron job) to isolate problematic queries and address them
individually.

Note that it can be tempting to configure Postgres to "only log queries
that take longer than 500ms" in an attempt to "only catch the slow and
problematic queries without creating unmanageable amounts of IO"  The
danger in this is that you may have some relatively fast queries that
are used so often that they constitute a serious performance problem.
Optimizing a query from 25ms to 22ms doesn't seem like it's worth the
effort, but if it's run 1x10^25 times a day it is.  If the IO load of
logging all queries presents too much of a slowdown, I recommend selecting
data collection periods and do it for perhaps an hour, then turn it
back off.  Maybe once a week or so.

Hope this helps.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

[EMAIL PROTECTED]
Phone: 412-422-3463x4023

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


Re: [PERFORM] Making the most of memory?

2008-01-22 Thread Scott Marlowe
On Jan 22, 2008 10:11 PM, Joshua Fielek <[EMAIL PROTECTED]> wrote:
>
> Hey folks --
>
> For starters, I am fairly new to database tuning and I'm still learning
> the ropes. I understand the concepts but I'm still learning the real
> world impact of some of the configuration options for postgres.
>
> We have an application that has been having some issues with performance
> within postgres 8.1.9 and later 8.2.5. The upgrade to 8.2.5 gained us a
> nice little performance increase just off the improved query
> optimization, but we are still having other performance issues.
>
> The database itself is not that large -- a db_dump of the sql file as
> text is only about 110MB. I haven't checked the exact size of the actual
> data base, but the entire data directory is smaller than the available
> memory at about 385MB including logs and config files. This is a single
> database with a relatively small number of client connections (50 or so)
> making a fair number of smaller queries. This is not a massive data
> effort by any means at this time, but it will be growing.
>
> We have available currently ~4GB (8GB total) for Postgres.

How are you "allocating" this memory to postgresql?  VM, ulimit?  Or
are you just saying that you want to tune pgsql to use about 4Gig of
ram?

> We will be
> moving to a server that will have about 24GB (32GB total) available for
> the database, with the current server becoming a hot backup, probably
> with slony or something similar to keep the databases in sync.
>
> I've been monitoring the memory usage of postgres on the current system
> and it seems like none of the threads ever allocate more than about
> 400MB total and about 80-90MB shared memory. It seems to me that since
> we have a very large chunk of memory relative to the database size we
> should be loading the entire database into memory.

You'd think so.  But you might be wrong.  The OS itself will naturally
cache all of the data in memory anyway.  Having PostgreSQL cache it
might as well might make things faster, might make them slower,
depending on your usage patterns.

However, it's far more important that PostgreSQL be able to allocate
memory for individual backends for things like sorts and maintenance
than to use it all to hold mostly static data that may or may not be
accessed all that often.

> How can we be sure
> we're getting the most out of the memory we're allocating to postgres?

I'd suggest not worrying too much about it.  Using 100% of your memory
is much more dangerous than not.  Since when you run out the machine
will start swapping and slow to a crawl.

> What can we do to improve the memory usage, looking for performance
> first and foremost, on both the larger and smaller systems?
>
> Here's the salient config items for the 8GB system:
>
> max_connections = 200# realistically we expect 50-150 open
> shared_buffers = 38000

That's a good number for the size database you're currently running.
Having shared_buffers be larger than your data set doesn't really
help.  Depending on your workload, having it be smaller can help (i.e.
lots of small transactions).

> sort_mem = 1048576

This setting doesn't exist in 8.1 and 8.2 anymore, it was replaced
with this one:

> work_mem = 32000

Which, by the way, is a pretty reasonable number, except if you're
commonly handling 200 actual connections in which case you could be
allocating 32M*200 = 6.4Gig max if each connection is running a sort
at the same time.  If most won't be using that much, you might be
safe.

> maintenance_work_mem = 32000
> max_fsm_pages = 480001# probably too large for the max_fsm_*

That's ok.  it's better to allocate a few hundred thousand extra fsm
pages than not.  Since you have to restart to change it, it's better
to be prepared.

> max_fsm_relations = 2# items; one Db with ~400 tables.
> effective_cache_size = 212016# ~2GB, could probably double this

Since effective cache size doesn't allocate anything, but rather acts
as a big round knob telling pgsql about how much memory the OS is
caching postgresql stuff in, you can approximate it.

I'd worry more about what kind of drive subsystem you have in this
system.  In a database server the I/O subsystem is often the most
important part of planning for good performance.

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

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


[PERFORM] Making the most of memory?

2008-01-22 Thread Joshua Fielek


Hey folks --

For starters, I am fairly new to database tuning and I'm still learning 
the ropes. I understand the concepts but I'm still learning the real 
world impact of some of the configuration options for postgres.


We have an application that has been having some issues with performance 
within postgres 8.1.9 and later 8.2.5. The upgrade to 8.2.5 gained us a 
nice little performance increase just off the improved query 
optimization, but we are still having other performance issues.


The database itself is not that large -- a db_dump of the sql file as 
text is only about 110MB. I haven't checked the exact size of the actual 
data base, but the entire data directory is smaller than the available 
memory at about 385MB including logs and config files. This is a single 
database with a relatively small number of client connections (50 or so) 
making a fair number of smaller queries. This is not a massive data 
effort by any means at this time, but it will be growing.


We have available currently ~4GB (8GB total) for Postgres. We will be 
moving to a server that will have about 24GB (32GB total) available for 
the database, with the current server becoming a hot backup, probably 
with slony or something similar to keep the databases in sync.


I've been monitoring the memory usage of postgres on the current system 
and it seems like none of the threads ever allocate more than about 
400MB total and about 80-90MB shared memory. It seems to me that since 
we have a very large chunk of memory relative to the database size we 
should be loading the entire database into memory. How can we be sure 
we're getting the most out of the memory we're allocating to postgres? 
What can we do to improve the memory usage, looking for performance 
first and foremost, on both the larger and smaller systems?


Here's the salient config items for the 8GB system:

max_connections = 200# realistically we expect 50-150 open
shared_buffers = 38000
sort_mem = 1048576
work_mem = 32000
maintenance_work_mem = 32000
max_fsm_pages = 480001# probably too large for the max_fsm_* 
max_fsm_relations = 2# items; one Db with ~400 tables.

effective_cache_size = 212016# ~2GB, could probably double this


Thanks,
J
--
Joshua J. Fielek
Sr. Software Engineer
Concursive Corporation
223 East City Hall Ave., Suite 212
Norfolk, VA 23510
Phone  : (757) 627-3002x6656
Mobile : (757) 754-4462
Fax: (757) 627-8773
Email  : [EMAIL PROTECTED]
http://www.concursive.com

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate