Re: [PERFORM] Making the most of memory?
* 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?
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?
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?
[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?
[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?
> 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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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
