Обсуждение: Making the most of memory?

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 = 20000    # 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  : jfielek@concursive.com
http://www.concursive.com

On Jan 22, 2008 10:11 PM, Joshua Fielek <jfielek@centriccrm.com> 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 = 20000    # 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.

In response to Joshua Fielek <jfielek@centriccrm.com>:
>
> 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/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

On Jan 23, 2008 8:01 AM, mike long <mlong@concursive.com> 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.

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 <jfielek@centriccrm.com> 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 = 20000    # 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  : jfielek@concursive.com
> 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
>

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

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

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

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

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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Jan 23, 2008 1:57 PM, Guy Rouillier <guyr-ml1@burntmail.com> 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.

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/

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

On Jan 23, 2008 2:57 PM, Guy Rouillier <guyr-ml1@burntmail.com> 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

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

> 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                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

scott.marlowe@gmail.com ("Scott Marlowe") writes:
> On Jan 23, 2008 1:57 PM, Guy Rouillier <guyr-ml1@burntmail.com> 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.

fweimer@bfk.de (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

On Jan 22, 2008 11:11 PM, Joshua Fielek <jfielek@centriccrm.com> 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

On Jan 24, 2008 1:01 PM, Merlin Moncure <mmoncure@gmail.com> > 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

* 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                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99