Re: [PERFORM] VERY slow queries at random
On 07.06.2007, at 22:42, Greg Smith wrote: On Thu, 7 Jun 2007, Gunther Mayer wrote: wal checkpoint config is on pg defaults everywhere, all relevant config options are commented out. I'm no expert in wal stuff but I don't see how that could cause the problem? Checkpoints are very resource intensive and can cause other processes (including your selects) to hang for a considerable period of time while they are processing. With the default parameters, they can happen very frequently. Normally checkpoint_segments and checkpoint_timeout are increased in order to keep this from happening. This would normally be an issue only if you're writing a substantial amount of data to your tables. If there are a lot of writes going on, you might get some improvement by adjusting those parameters upward; the defaults are pretty low. Make sure you read http://www.postgresql.org/docs/8.2/static/wal-configuration.html first so you know what you're playing with, there are some recovery implications invoved. I remember us having problems with 8.0 background writer, you might want to try turning it off. Not sure if it behaves as badly in 8.2. increasing wal buffers might be a good idea also. Kristo ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] [ADMIN] reclaiming disk space after major updates
Andrew Sullivan wrote: On Thu, Jun 07, 2007 at 03:26:56PM -0600, Dan Harris wrote: They don't always have to be in a single transaction, that's a good idea to break it up and vacuum in between, I'll consider that. Thanks If you can do it this way, it helps _a lot_. I've had to do this sort of thing, and breaking into groups of a couple thousand or so really made the difference. A One more point in my original post.. For my own education, why does VACUUM FULL prevent reads to a table when running (I'm sure there's a good reason)? I can certainly understand blocking writes, but if I could still read from it, I'd have no problems at all! -Dan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [ADMIN] reclaiming disk space after major updates
On Fri, Jun 08, 2007 at 08:29:24AM -0600, Dan Harris wrote: One more point in my original post.. For my own education, why does VACUUM FULL prevent reads to a table when running (I'm sure there's a good reason)? I can certainly understand blocking writes, but if I could still read from it, I'd have no problems at all! It has to take an exclusive lock, because it actually moves the bits around on disk. Since your SELECT query could be asking for data that is actually in-flight, you lose. This is conceptually similar to the way defrag works on old FAT-type filesystems: if you used one, you'll remember that when you were defragging your disk, if you did anything else on that disk the defrag would keep restarting. This was because the OS was trying to move bits around, and when you did stuff, you screwed up its optimization. The database works differently, by taking an exclusive lock, but the basic conceptual problem is the same. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Please help me understand these numbers
I need some help. I have started taking snapshots of performance of my databases with concerns to io. I created a view on each cluster defined as: SELECT pg_database.datname AS database_name, pg_stat_get_db_blocks_fetched(pg_database.oid) AS blocks_fetched, pg_stat_get_db_blocks_hit(pg_database.oid) AS blocks_hit, pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) AS physical_reads FROM pg_database WHERE pg_stat_get_db_blocks_fetched(pg_database.oid) 0 ORDER BY pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) DESC; I am taking 5 minute snapshots of this view. When I look at my data, I am getting row like this: database_name: xxx blocks_fetched: 2396915583 blocks_hit: 1733190669 physical_reads: 663724914 snapshot_timestamp: 2007-06-08 09:20:01.396079 database_name: xxx blocks_fetched: 2409671770 blocks_hit: 1733627788 physical_reads: 676043982 snapshot_timestamp: 2007-06-08 09:25:01.512911 Subtracting these 2 lines gives me a 5 minute number of blocks_fetched: 12756187 blocks_hit: 437119 physical_reads: 12319068 If I am interpreting these number correctly, for this 5 minute interval I ended up hitting only 3.43% of the requested data in my shared_buffer, and ended up requesting 12,319,068 blocks from the os? Since a postgres block is 8KB, that's 98,553,544 KB (~94GB)! Are my assumptions correct in this? I am just having a hard time fathoming this. For this particular db, that is almost 1/2 of the total database (it is a 200GB+ db) requested in just 5 minutes! Thanks for any clarification on this. Chris 12756187 12756187
Re: [PERFORM] Please help me understand these numbers
In response to Chris Hoover [EMAIL PROTECTED]: I need some help. I have started taking snapshots of performance of my databases with concerns to io. I created a view on each cluster defined as: SELECT pg_database.datname AS database_name, pg_stat_get_db_blocks_fetched(pg_database.oid) AS blocks_fetched, pg_stat_get_db_blocks_hit(pg_database.oid) AS blocks_hit, pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) AS physical_reads FROM pg_database WHERE pg_stat_get_db_blocks_fetched(pg_database.oid) 0 ORDER BY pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) DESC; I am taking 5 minute snapshots of this view. When I look at my data, I am getting row like this: database_name: xxx blocks_fetched: 2396915583 blocks_hit: 1733190669 physical_reads: 663724914 snapshot_timestamp: 2007-06-08 09:20:01.396079 database_name: xxx blocks_fetched: 2409671770 blocks_hit: 1733627788 physical_reads: 676043982 snapshot_timestamp: 2007-06-08 09:25:01.512911 Subtracting these 2 lines gives me a 5 minute number of blocks_fetched: 12756187 blocks_hit: 437119 physical_reads: 12319068 If I am interpreting these number correctly, for this 5 minute interval I ended up hitting only 3.43% of the requested data in my shared_buffer, and ended up requesting 12,319,068 blocks from the os? Since a postgres block is 8KB, that's 98,553,544 KB (~94GB)! Are my assumptions correct in this? It certainly seems possible. I am just having a hard time fathoming this. For this particular db, that is almost 1/2 of the total database (it is a 200GB+ db) requested in just 5 minutes! What are your share_buffers setting and the total RAM available to the OS? My guess would be that you have plenty of RAM in the system (8G+ ?) but that you haven't allocated very much of it to shared_buffers (only a few 100 meg?). As a result, PostgreSQL is constantly asking the OS for disk blocks that it doesn't have cached, but the OS has those disk blocks cached in RAM. If my guess is right, you'll probably see improved performance by allocating more shared memory to PostgreSQL, thus avoiding having to move data from one area in memory to another before it can be used. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] How much ram is too much
Is it possible that providing 128G of ram is too much ? Will other systems in the server bottleneck ? Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] How much ram is too much
What is your expected data size and usage pattern? What are the other components in the system? On Fri, 8 Jun 2007, Dave Cramer wrote: Is it possible that providing 128G of ram is too much ? Will other systems in the server bottleneck ? Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] How much ram is too much
Dave Cramer wrote: Is it possible that providing 128G of ram is too much ? Will other systems in the server bottleneck ? What CPU and OS are you considering? -- Guy Rouillier ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Best way to delete unreferenced rows?
Craig James wrote: Tyrrill, Ed wrote: I have a table, let's call it A, whose primary key, a_id, is referenced in a second table, let's call it B. For each unique A.a_id there are generally many rows in B with the same a_id. My problem is that I want to delete a row in A when the last row in B that references it is deleted. Right now I just query for rows in A that aren't referenced by B, and that worked great when the tables were small, but it takes over an hour now that the tables have grown larger (over 200 million rows in B and 14 million in A). The delete has to do a sequential scan of both tables since I'm looking for what's not in the indexes. I was going to try creating a trigger after delete on B for each row to check for more rows in B with the same a_id, and delete the row in A if none found. In general I will be deleting 10's of millions of rows from B and 100's of thousands of rows from A on a daily basis. What do you think? Does anyone have any other suggestions on different ways to approach this? Essentially what you're doing is taking the one-hour job and spreading out in little chunks over thousands of queries. If you have 10^7 rows in B and 10^5 rows in A, then on average you have 100 references from B to A. That means that 99% of the time, your trigger will scan B and find that there's nothing to do. This could add a lot of overhead to your ordinary transactions, costing a lot more in the long run than just doing the once-a-day big cleanout. You didn't send the specifics of the query you're using, along with an EXPLAIN ANALYZE of it in operation. It also be that your SQL is not optimal, and that somebody could suggest a more efficient query. It's also possible that it's not the sequential scans that are the problem, but rather that it just takes a long time to delete 100,000 rows from table A because you have a lot of indexes. Or it could be a combination of performance problems. You haven't given us enough information to really analyze your problem. Send more details! Craig Ok. Yes, there are a bunch of indexes on A that may slow down the delete, but if I just run the select part of the delete statement through explain analyze then that is the majority of the time. The complete sql statement for the delete is: delete from backupobjects where record_id in (select backupobjects.record_id from backupobjects left outer join backup_location using(record_id) where backup_location.record_id is null ) What I've referred to as A is backupobjects, and B is backup_location. Here is explain analyze of just the select: mdsdb=# explain analyze select backupobjects.record_id from backupobjects left outer join backup_location using(record_id) where backup_location.record_id is null; QUERY PLAN --- Merge Left Join (cost=38725295.93..42505394.70 rows=13799645 width=8) (actual time=6503583.342..8220629.311 rows=93524 loops=1) Merge Cond: (outer.record_id = inner.record_id) Filter: (inner.record_id IS NULL) - Index Scan using backupobjects_pkey on backupobjects (cost=0.00..521525.10 rows=13799645 width=8) (actual time=15.955..357813.621 rows=13799645 loops=1) - Sort (cost=38725295.93..39262641.69 rows=214938304 width=8) (actual time=6503265.293..7713657.750 rows=214938308 loops=1) Sort Key: backup_location.record_id - Seq Scan on backup_location (cost=0.00..3311212.04 rows=214938304 width=8) (actual time=11.175..1881179.825 rows=214938308 loops=1) Total runtime: 8229178.269 ms (8 rows) I ran vacuum analyze after the last time any inserts, deletes, or updates were done, and before I ran the query above. I've attached my postgresql.conf. The machine has 4 GB of RAM. Thanks, Ed postgresql.conf Description: postgresql.conf ---(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] dbt2 NOTPM numbers
On 6/4/07, Markus Schiltknecht [EMAIL PROTECTED] wrote: Thanks, that's exactly the one simple and very raw comparison value I've been looking for. (Since most of the results pages of (former?) OSDL are down). Yeah, those results pages are gone for good. :( Regards, Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Please help me understand these numbers
On 6/8/07, Bill Moran [EMAIL PROTECTED] wrote: In response to Chris Hoover [EMAIL PROTECTED]: I need some help. I have started taking snapshots of performance of my databases with concerns to io. I created a view on each cluster defined as: SELECT pg_database.datname AS database_name, pg_stat_get_db_blocks_fetched(pg_database.oid) AS blocks_fetched, pg_stat_get_db_blocks_hit(pg_database.oid) AS blocks_hit, pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) AS physical_reads FROM pg_database WHERE pg_stat_get_db_blocks_fetched(pg_database.oid) 0 ORDER BY pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) DESC; I am taking 5 minute snapshots of this view. When I look at my data, I am getting row like this: database_name: xxx blocks_fetched: 2396915583 blocks_hit: 1733190669 physical_reads: 663724914 snapshot_timestamp: 2007-06-08 09:20:01.396079 database_name: xxx blocks_fetched: 2409671770 blocks_hit: 1733627788 physical_reads: 676043982 snapshot_timestamp: 2007-06-08 09:25:01.512911 Subtracting these 2 lines gives me a 5 minute number of blocks_fetched: 12756187 blocks_hit: 437119 physical_reads: 12319068 If I am interpreting these number correctly, for this 5 minute interval I ended up hitting only 3.43% of the requested data in my shared_buffer, and ended up requesting 12,319,068 blocks from the os? Since a postgres block is 8KB, that's 98,553,544 KB (~94GB)! Are my assumptions correct in this? It certainly seems possible. I am just having a hard time fathoming this. For this particular db, that is almost 1/2 of the total database (it is a 200GB+ db) requested in just 5 minutes! What are your share_buffers setting and the total RAM available to the OS? My guess would be that you have plenty of RAM in the system (8G+ ?) but that you haven't allocated very much of it to shared_buffers (only a few 100 meg?). As a result, PostgreSQL is constantly asking the OS for disk blocks that it doesn't have cached, but the OS has those disk blocks cached in RAM. If my guess is right, you'll probably see improved performance by allocating more shared memory to PostgreSQL, thus avoiding having to move data from one area in memory to another before it can be used. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 Wow, that's amazing. You pretty much hit my config on the head. 9GB ram with 256MB shared_buffers. I have just started playing with my shared_buffers config on another server that tends to be my main problem server. I just ran across these informational functions the other day, and they are opening up some great territory for me that I have been wanting to know about for a while. I was starting to bump my shared_buffers up slowly. Would it be more advisable to just push them to 25% of my ram and start there or work up slowly. I was going slowly since it takes a database restart to change the parameter. Any advise would be welcome. Chris
Re: [PERFORM] How much ram is too much
It's an IBM x3850 using linux redhat 4.0 On 8-Jun-07, at 12:46 PM, Guy Rouillier wrote: Dave Cramer wrote: Is it possible that providing 128G of ram is too much ? Will other systems in the server bottleneck ? What CPU and OS are you considering? -- Guy Rouillier ---(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 5: don't forget to increase your free space map settings
Re: [PERFORM] How much ram is too much
On Fri, 8 Jun 2007, Dave Cramer wrote: Is it possible that providing 128G of ram is too much ? Will other systems in the server bottleneck ? the only way 128G of ram would be too much is if your total database size (including indexes) is smaller then this. now it may not gain you as much of an advantage going from 64G to 128G as it does going from 32G to 64G, but that depends on many variables as others have been asking. David Lang ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[OT] Re: [PERFORM] How much ram is too much
Dave Cramer írta: It's an IBM x3850 using linux redhat 4.0 Isn't that a bit old? I have a RedHat 4.2 somewhere that was bundled with Applixware 3. :-) -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] How much ram is too much
[EMAIL PROTECTED] wrote: On Fri, 8 Jun 2007, Dave Cramer wrote: Is it possible that providing 128G of ram is too much ? Will other systems in the server bottleneck ? the only way 128G of ram would be too much is if your total database size (including indexes) is smaller then this. now it may not gain you as much of an advantage going from 64G to 128G as it does going from 32G to 64G, but that depends on many variables as others have been asking. I don't know about the IBM but I know some of the HPs require slower ram to actually get to 128G. Joshua D. Drake David Lang ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Please help me understand these numbers
On 6/8/07, Bill Moran [EMAIL PROTECTED] wrote: In response to Chris Hoover [EMAIL PROTECTED]: On 6/8/07, Bill Moran [EMAIL PROTECTED] wrote: In response to Chris Hoover [EMAIL PROTECTED]: I need some help. I have started taking snapshots of performance of my databases with concerns to io. I created a view on each cluster defined as: SELECT pg_database.datname AS database_name, pg_stat_get_db_blocks_fetched(pg_database.oid) AS blocks_fetched, pg_stat_get_db_blocks_hit(pg_database.oid) AS blocks_hit, pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) AS physical_reads FROM pg_database WHERE pg_stat_get_db_blocks_fetched(pg_database.oid) 0 ORDER BY pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) DESC; I am taking 5 minute snapshots of this view. When I look at my data, I am getting row like this: database_name: xxx blocks_fetched: 2396915583 blocks_hit: 1733190669 physical_reads: 663724914 snapshot_timestamp: 2007-06-08 09:20:01.396079 database_name: xxx blocks_fetched: 2409671770 blocks_hit: 1733627788 physical_reads: 676043982 snapshot_timestamp: 2007-06-08 09:25:01.512911 Subtracting these 2 lines gives me a 5 minute number of blocks_fetched: 12756187 blocks_hit: 437119 physical_reads: 12319068 If I am interpreting these number correctly, for this 5 minute interval I ended up hitting only 3.43% of the requested data in my shared_buffer, and ended up requesting 12,319,068 blocks from the os? Since a postgres block is 8KB, that's 98,553,544 KB (~94GB)! Are my assumptions correct in this? It certainly seems possible. I am just having a hard time fathoming this. For this particular db, that is almost 1/2 of the total database (it is a 200GB+ db) requested in just 5 minutes! What are your share_buffers setting and the total RAM available to the OS? My guess would be that you have plenty of RAM in the system (8G+ ?) but that you haven't allocated very much of it to shared_buffers (only a few 100 meg?). As a result, PostgreSQL is constantly asking the OS for disk blocks that it doesn't have cached, but the OS has those disk blocks cached in RAM. If my guess is right, you'll probably see improved performance by allocating more shared memory to PostgreSQL, thus avoiding having to move data from one area in memory to another before it can be used. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 Wow, that's amazing. You pretty much hit my config on the head. 9GB ram with 256MB shared_buffers. Some days are better than others :) I have just started playing with my shared_buffers config on another server that tends to be my main problem server. I just ran across these informational functions the other day, and they are opening up some great territory for me that I have been wanting to know about for a while. Have a look at the pg_buffercache module, which can be pretty useful for figuring out what data is being accessed. I was starting to bump my shared_buffers up slowly. Would it be more advisable to just push them to 25% of my ram and start there or work up slowly. I was going slowly since it takes a database restart to change the parameter. I looked back through and couldn't find which version of PostgreSQL you were using. If it's 8.X, the current wisdom is to start with 25 - 30% of your unused RAM for shared buffers (by unused, it's meant to take into account any other applications running on the same machine and their RAM requirements) and then tune down or up as seems to help. So, my recommendation would be to bump shared_buffers up to around 2G and go from there. Another thing that I realized wasn't in your original email is if you're having any sort of problems? If there are slow queries or other performance issues, do before/after tests to see if you're adjusting values in the right direction. If you don't have any performance issues outstanding, it can be easy to waste a lot of time/energy tweaking settings that don't really help anything. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 Sorry, I am on 8.1.3 (move to 8.1.9 is being started). I do have some performance issues but they are sporadic. I am trying to make sure my servers are all running well. I believe that they are ok most of the time, but we are walking on the edge. They can easily be pushed over and have my customers complaining of slowness. So, I am trying to look at tuning back away from the edge. Thanks for your help, Chris
Re: [PERFORM] Please help me understand these numbers
In response to Chris Hoover [EMAIL PROTECTED]: On 6/8/07, Bill Moran [EMAIL PROTECTED] wrote: In response to Chris Hoover [EMAIL PROTECTED]: I need some help. I have started taking snapshots of performance of my databases with concerns to io. I created a view on each cluster defined as: SELECT pg_database.datname AS database_name, pg_stat_get_db_blocks_fetched(pg_database.oid) AS blocks_fetched, pg_stat_get_db_blocks_hit(pg_database.oid) AS blocks_hit, pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) AS physical_reads FROM pg_database WHERE pg_stat_get_db_blocks_fetched(pg_database.oid) 0 ORDER BY pg_stat_get_db_blocks_fetched(pg_database.oid) - pg_stat_get_db_blocks_hit(pg_database.oid) DESC; I am taking 5 minute snapshots of this view. When I look at my data, I am getting row like this: database_name: xxx blocks_fetched: 2396915583 blocks_hit: 1733190669 physical_reads: 663724914 snapshot_timestamp: 2007-06-08 09:20:01.396079 database_name: xxx blocks_fetched: 2409671770 blocks_hit: 1733627788 physical_reads: 676043982 snapshot_timestamp: 2007-06-08 09:25:01.512911 Subtracting these 2 lines gives me a 5 minute number of blocks_fetched: 12756187 blocks_hit: 437119 physical_reads: 12319068 If I am interpreting these number correctly, for this 5 minute interval I ended up hitting only 3.43% of the requested data in my shared_buffer, and ended up requesting 12,319,068 blocks from the os? Since a postgres block is 8KB, that's 98,553,544 KB (~94GB)! Are my assumptions correct in this? It certainly seems possible. I am just having a hard time fathoming this. For this particular db, that is almost 1/2 of the total database (it is a 200GB+ db) requested in just 5 minutes! What are your share_buffers setting and the total RAM available to the OS? My guess would be that you have plenty of RAM in the system (8G+ ?) but that you haven't allocated very much of it to shared_buffers (only a few 100 meg?). As a result, PostgreSQL is constantly asking the OS for disk blocks that it doesn't have cached, but the OS has those disk blocks cached in RAM. If my guess is right, you'll probably see improved performance by allocating more shared memory to PostgreSQL, thus avoiding having to move data from one area in memory to another before it can be used. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 Wow, that's amazing. You pretty much hit my config on the head. 9GB ram with 256MB shared_buffers. Some days are better than others :) I have just started playing with my shared_buffers config on another server that tends to be my main problem server. I just ran across these informational functions the other day, and they are opening up some great territory for me that I have been wanting to know about for a while. Have a look at the pg_buffercache module, which can be pretty useful for figuring out what data is being accessed. I was starting to bump my shared_buffers up slowly. Would it be more advisable to just push them to 25% of my ram and start there or work up slowly. I was going slowly since it takes a database restart to change the parameter. I looked back through and couldn't find which version of PostgreSQL you were using. If it's 8.X, the current wisdom is to start with 25 - 30% of your unused RAM for shared buffers (by unused, it's meant to take into account any other applications running on the same machine and their RAM requirements) and then tune down or up as seems to help. So, my recommendation would be to bump shared_buffers up to around 2G and go from there. Another thing that I realized wasn't in your original email is if you're having any sort of problems? If there are slow queries or other performance issues, do before/after tests to see if you're adjusting values in the right direction. If you don't have any performance issues outstanding, it can be easy to waste a lot of time/energy tweaking settings that don't really help anything. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [OT] Re: [PERFORM] How much ram is too much
Zoltan Boszormenyi wrote: Dave Cramer írta: It's an IBM x3850 using linux redhat 4.0 Isn't that a bit old? I have a RedHat 4.2 somewhere that was bundled with Applixware 3. :-) He means redhat ES/AS 4 I assume. J -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [OT] Re: [PERFORM] How much ram is too much
On 8-Jun-07, at 2:10 PM, Joshua D. Drake wrote: Zoltan Boszormenyi wrote: Dave Cramer írta: It's an IBM x3850 using linux redhat 4.0 Isn't that a bit old? I have a RedHat 4.2 somewhere that was bundled with Applixware 3. :-) He means redhat ES/AS 4 I assume. Yes AS4 J -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/ donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [OT] Re: [PERFORM] How much ram is too much
Joshua D. Drake írta: Zoltan Boszormenyi wrote: Dave Cramer írta: It's an IBM x3850 using linux redhat 4.0 Isn't that a bit old? I have a RedHat 4.2 somewhere that was bundled with Applixware 3. :-) He means redhat ES/AS 4 I assume. J I guessed that, hence the smiley. But it's very unfortunate that version numbers are reused - it can cause confusion. There was a RH 4.0 already a long ago, when the commercial and the community version were the same. I think Microsoft will avoid reusing its versions when year 2095 comes. :-) -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [OT] Re: [PERFORM] How much ram is too much
On Fri, Jun 08, 2007 at 08:54:39PM +0200, Zoltan Boszormenyi wrote: Joshua D. Drake írta: Zoltan Boszormenyi wrote: Dave Cramer írta: It's an IBM x3850 using linux redhat 4.0 Isn't that a bit old? I have a RedHat 4.2 somewhere that was bundled with Applixware 3. :-) He means redhat ES/AS 4 I assume. I guessed that, hence the smiley. But it's very unfortunate that version numbers are reused - it can cause confusion. There was a RH 4.0 already a long ago, when the commercial and the community version were the same. I think Microsoft will avoid reusing its versions when year 2095 comes. :-) He should have written RHEL 4.0. RH 4.0 is long enough ago, though, that I think few would assume it meant the much older release. You'll find a similar thing with products like CuteFTP 7.0 or CuteFTP Pro 3.0. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [OT] Re: [PERFORM] How much ram is too much
[EMAIL PROTECTED] írta: On Fri, Jun 08, 2007 at 08:54:39PM +0200, Zoltan Boszormenyi wrote: Joshua D. Drake írta: Zoltan Boszormenyi wrote: Dave Cramer írta: It's an IBM x3850 using linux redhat 4.0 Isn't that a bit old? I have a RedHat 4.2 somewhere that was bundled with Applixware 3. :-) He means redhat ES/AS 4 I assume. I guessed that, hence the smiley. But it's very unfortunate that version numbers are reused - it can cause confusion. There was a RH 4.0 already a long ago, when the commercial and the community version were the same. I think Microsoft will avoid reusing its versions when year 2095 comes. :-) He should have written RHEL 4.0. RH 4.0 is long enough ago, though, that I think few would assume it meant the much older release. Yes. But up until RHEL 8.0/9.0 ( or plain 9 without decimals ;-) ) I can make cheap jokes telling that I can give you a free upgrade. :-) You'll find a similar thing with products like CuteFTP 7.0 or CuteFTP Pro 3.0. I am sure there are others, too. But enough of this OT, I am really interested in the main thread's topic. Best regards, -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] How much ram is too much
Dave Cramer wrote: It's an IBM x3850 using linux redhat 4.0 I had to look that up, web site says it is a 4-processor, dual-core (so 8 cores) Intel Xeon system. It also says Up to 64GB DDR II ECC memory, so are you sure you can even get 128 GB RAM? If you could, I'd expect diminishing returns from the Xeon northbridge memory access. If you are willing to spend that kind of money on memory, you'd be better off with Opteron or Sparc. -- Guy Rouillier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [OT] Re: [PERFORM] How much ram is too much
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Zoltan Boszormenyi wrote: Joshua D. Drake írta: Zoltan Boszormenyi wrote: Dave Cramer írta: It's an IBM x3850 using linux redhat 4.0 Isn't that a bit old? I have a RedHat 4.2 somewhere that was bundled with Applixware 3. :-) He means redhat ES/AS 4 I assume. J I guessed that, hence the smiley. But it's very unfortunate that version numbers are reused - it can cause confusion. There was a RH 4.0 already a long ago, when the commercial and the community version were the same. I think Microsoft will avoid reusing its versions when year 2095 comes. :-) Well, RedHat Linux, and RedHat Linux Enterprise Server/Advanced Servers are clearly different products :-P And yes, I even owned Applix :) Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGac2FHJdudm4KnO0RAkpcAJwI+RTIJgAc5Db1bnsu7tRNiU9vzACeIGvl LP0CSxc5dML0BMerI+u1xYc= =qiye -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
