Re: [PERFORM] Postgres 8 vs Postgres 7.4/cygwin
Scott Goldstein [EMAIL PROTECTED] writes: I'm currently trying to make a decision on whether to use the Cygwin port of Postgres 7.4 or Postgres 8.0 for a windows installation. Can someone provide some comparison info from a performance point of view? I was thinking that the Cygwin port has the overhead of the translation layer, but 8.0 is a newer product and may still have performance issue. Can anyone comment on this? Well, the performance issues of the cygwin-based releases are the stuff of legend ;-). New product or no, this is really a no-brainer. regards, tom lane ---(end of broadcast)--- TIP 3: 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
[PERFORM] Speed with offset clause
Hi again all, My queries are now optimised. They all use the indexes like they should. However, there's still a slight problem when I issue the offset clause. We have a table that contains 600.000 records We display them by 25 in the webpage. So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 This take a long time to run, about 5-10 seconds whereas offset below 100 take less than a second. Can I speed this up ? Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Speed with offset clause
On 6/24/05, Yves Vindevogel [EMAIL PROTECTED] wrote: So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 improving this is hard, but not impossible. if you have right index created, try to reverse the order and fetch first adverts, and then resort it (just the 25 adverts) in correct order. it will be faster. depesz ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] max_connections / shared_buffers / effective_cache_size questions
Hello, I'm a Sun Solaris sys admin for a start-up company. I've got the UNIX background, but now I'm having to learn PostgreSQL to support it on our servers :) Server Background: Solaris 10 x86 PostgreSQL 8.0.3 Dell PowerEdge 2650 w/4gb ram. This is running JBoss/Apache as well (I KNOW the bad juju of running it all on one box, but it's all we have currently for this project). I'm dedicating 1gb for PostgreSQL alone. So, far I LOVE it compared to MySQL it's solid. The only things I'm kind of confused about (and I've been searching for answers on lot of good perf docs, but not too clear to me) are the following: 1.) shared_buffers I see lot of reference to making this the size of available ram (for the DB). However, I also read to make it the size of pgdata directory. I notice when I load postgres each daemon is using the amount of shared memory (shared_buffers). Our current dataset (pgdata) is 85mb in size. So, I'm curious should this size reflect the pgdata or the 'actual' memory given? I currently have this at 128mb 2.) effective_cache_size - from what I read this is the 'total' allowed memory for postgresql to use correct? So, if I am willing to allow 1GB of memory should I make this 1GB? 3.) max_connections, been trying to figure 'how' to determine this #. I've read this is buffer_size+500k per a connection. ie. 128mb(buffer) + 500kb = 128.5mb per connection? I was curious about 'sort_mem' I can't find reference of it in the 8.0.3 documentation, has it been removed? work_mem and max_stack_depth set to 4096 maintenance_work_mem set to 64mb Thanks for any help on this. I'm sure bombardment of newbies gets old :) -William Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] max_connections / shared_buffers / effective_cache_size
Puddle wrote: Hello, I'm a Sun Solaris sys admin for a start-up company. I've got the UNIX background, but now I'm having to learn PostgreSQL to support it on our servers :) Server Background: Solaris 10 x86 PostgreSQL 8.0.3 Dell PowerEdge 2650 w/4gb ram. This is running JBoss/Apache as well (I KNOW the bad juju of running it all on one box, but it's all we have currently for this project). I'm dedicating 1gb for PostgreSQL alone. So, far I LOVE it compared to MySQL it's solid. The only things I'm kind of confused about (and I've been searching for answers on lot of good perf docs, but not too clear to me) are the following: 1.) shared_buffers I see lot of reference to making this the size of available ram (for the DB). However, I also read to make it the size of pgdata directory. I notice when I load postgres each daemon is using the amount of shared memory (shared_buffers). Our current dataset (pgdata) is 85mb in size. So, I'm curious should this size reflect the pgdata or the 'actual' memory given? I currently have this at 128mb You generally want shared_buffers to be no more than 10% of available ram. Postgres expects the OS to do it's own caching. 128M/4G = 3% seems reasonable to me. I would certainly never set it to 100% of ram. 2.) effective_cache_size - from what I read this is the 'total' allowed memory for postgresql to use correct? So, if I am willing to allow 1GB of memory should I make this 1GB? This is the effective amount of caching between the actual postgres buffers, and the OS buffers. If you are dedicating this machine to postgres, I would set it to something like 3.5G. If it is a mixed machine, then you have to think about it. This does not change how postgres uses RAM, it changes how postgres estimates whether an Index scan will be cheaper than a Sequential scan, based on the likelihood that the data you want will already be cached in Ram. If you dataset is only 85MB, and you don't think it will grow, you really don't have to worry about this much. You have a very small database. 3.) max_connections, been trying to figure 'how' to determine this #. I've read this is buffer_size+500k per a connection. ie. 128mb(buffer) + 500kb = 128.5mb per connection? Max connections is just how many concurrent connections you want to allow. If you can get away with lower, do so. Mostly this is to prevent connections * work_mem to get bigger than your real working memory and causing you to swap. I was curious about 'sort_mem' I can't find reference of it in the 8.0.3 documentation, has it been removed? sort_mem changed to work_mem in 8.0, same thing with vacuum_mem - maintenance_work_mem. work_mem and max_stack_depth set to 4096 maintenance_work_mem set to 64mb Depends how much space you want to give per connection. 4M is pretty small for a machine with 4G of RAM, but if your DB is only 85M it might be plenty. work_mem is how much memory a sort/hash/etc will use before it spills to disk. So look at your queries. If you tend to sort most of your 85M db in a single query, you might want to make it a little bit more. But if all of your queries are very selective, 4M could be plenty. I would make maintenance_work_mem more like 512M. It is only used for CREATE INDEX, VACUUM, etc. Things that are not generally done by more than one process at a time. And it's nice for them to have plenty of room to run fast. Thanks for any help on this. I'm sure bombardment of newbies gets old :) -William Good luck, John =:- signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Speed with offset clause
Yves Vindevogel wrote: Hi again all, My queries are now optimised. They all use the indexes like they should. However, there's still a slight problem when I issue the offset clause. We have a table that contains 600.000 records We display them by 25 in the webpage. So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 This take a long time to run, about 5-10 seconds whereas offset below 100 take less than a second. Can I speed this up ? Met vriendelijke groeten, Bien à vous, Kind regards, *Yves Vindevogel* *Implements* Postgres has the optimization that it will plan a query, and once it reaches the limit, it can stop even though there is more data available. The problem you are having is that it has to go through offset rows first, before it can apply the limit. If you can, (as mentioned in the other post), try to refine your index so that you can reverse it for the second half of the data. This is probably tricky, as you may not know how many rows you have (or the amount might be changing). A potentially better thing, is if you have an index you are using, you could use a subselect so that the only portion that needs to have 60k rows is a single column. Maybe an example: Instead of saying: SELECT * FROM table1, table2 WHERE table1.id = table2.id ORDER BY table1.date OFFSET x LIMIT 25; You could do: SELECT * FROM (SELECT id FROM table1 OFFSET x LIMIT 25) as subselect JOIN table1 ON subselect.id = table1.id , table2 WHERE table1.id = table2.id; That means that the culling process is done on only a few rows of one table, and the rest of the real merging work is done on only a few rows. It really depends on you query, though, as what rows you are sorting on has a big influence on how well this will work. John =:- signature.asc Description: OpenPGP digital signature
Re: [PERFORM] max_connections / shared_buffers /
1.) shared_buffers I see lot of reference to making this the size of available ram (for the DB). However, I also read to make it the size of pgdata directory. 2.) effective_cache_size - from what I read this is the 'total' allowed memory for postgresql to use correct? So, if I am willing to allow 1GB of memory should I make this 1GB? shared_buffers in your case should be about 1. It is not taken on a per connection basis, but is global for that cluster. Perhaps your memory analysis tool is fooling with you? effective_cache_size is what you want to set to the amount of ram that you expect the kernel to use for caching the database information in memory. PostgreSQL will not allocate this memory, but it will make adjustments to the query execution methods (plan) chosen. 3.) max_connections, been trying to figure 'how' to determine this #. I've read this is buffer_size+500k per a connection. ie. 128mb(buffer) + 500kb = 128.5mb per connection? Max connections is the number of connections to the database you intend to allow. Shared_buffers must be of a certain minimum size to have that number of connections, but the 10k number above should cover any reasonable configurations. work_mem and max_stack_depth set to 4096 maintenance_work_mem set to 64mb Sort_mem and vacuum_mem became work_mem and maintenance_work_mem as those terms better indicate what they really do. Thanks for any help on this. I'm sure bombardment of newbies gets old :) That's alright. We only request that once you have things figured out that you, at your leisure, help out a few others. -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] Performance - moving from oracle to postgresql
Hi: I'm beginning the push at our company to look at running postgreSQL in production here. We have a dual CPU 2.8 GHZ Xeon Box running oracle. Typical CPU load runs between 20% and 90%. Raw DB size is about 200GB. We hit the disk at roughly 15MB/s read volume and 3MB/s write. At any given time we have from 2 to 70 sessions running on the instance. Sessions often persist for 24 hours or more. Total FreeFree Mb Mb % IDXS_EXT10 2000290 14.5 DATA_EXT1001 3200 32 SYSTEM 220 95.2 43.3 IDXS_EXT1002 9600 48 DATA_EXT10 6000 2990 49.8 UNDOB4000 2561.1 64 TEMP 8000 5802.9 72.5 DATA_LOB_EXT20 2000 1560 78 IDXS_EXT1 500 40180.2 DATA_EXT1 4000 3758 94 Total Instance 56720 30258.2 53.3 There are some immediate questions from our engineers about performance - Oracle has one particular performance enhancement that Postgres is missing. If you do a select that returns 100,000 rows in a given order, and all you want are rows 99101 to 99200, then Oracle can do that very efficiently. With Postgres, it has to read the first 99200 rows and then discard the first 99100. But... If we really want to look at performance, then we ought to put together a set of benchmarks of some typical tasks. Is this accurate: accoring to http://www.postgresql.org/docs/8.0/interactive/queries-limit.html -- The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET can be inefficient. What are the key performance areas I should be looking at? Where is psql not appropriate to replace Oracle? Thanks in advance, apologies if this occurs as spam, please send Replies to me off-list. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] max_connections / shared_buffers / effective_cache_size questions
Thanks for the feedback guys. The database will grow in size. This first client years worth of data was 85mb (test to proof of concept). The 05 datasets I expect to be much larger. I think I may increase the work_mem and maintenance_work_mem a bit more as suggested to. I'm a bit still confused with max_connections. I've been keeping the max_connections to the # of Apache connections. Since, this is all currently one one box and it's a web-based application. I wanted to make sure it stuck with the same # of connections. However, is there a formula or way to determine if a current setup with memory etc to allow such connections? Exactly how is max_connections determined or is a guess? Again thanks for your help and Mr. Taylors. Look forward to providing help when I got more a grasp on things to !:) -William --- John A Meinel [EMAIL PROTECTED] wrote: Puddle wrote: Hello, I'm a Sun Solaris sys admin for a start-up company. I've got the UNIX background, but now I'm having to learn PostgreSQL to support it on our servers :) Server Background: Solaris 10 x86 PostgreSQL 8.0.3 Dell PowerEdge 2650 w/4gb ram. This is running JBoss/Apache as well (I KNOW the bad juju of running it all on one box, but it's all we have currently for this project). I'm dedicating 1gb for PostgreSQL alone. So, far I LOVE it compared to MySQL it's solid. The only things I'm kind of confused about (and I've been searching for answers on lot of good perf docs, but not too clear to me) are the following: 1.) shared_buffers I see lot of reference to making this the size of available ram (for the DB). However, I also read to make it the size of pgdata directory. I notice when I load postgres each daemon is using the amount of shared memory (shared_buffers). Our current dataset (pgdata) is 85mb in size. So, I'm curious should this size reflect the pgdata or the 'actual' memory given? I currently have this at 128mb You generally want shared_buffers to be no more than 10% of available ram. Postgres expects the OS to do it's own caching. 128M/4G = 3% seems reasonable to me. I would certainly never set it to 100% of ram. 2.) effective_cache_size - from what I read this is the 'total' allowed memory for postgresql to use correct? So, if I am willing to allow 1GB of memory should I make this 1GB? This is the effective amount of caching between the actual postgres buffers, and the OS buffers. If you are dedicating this machine to postgres, I would set it to something like 3.5G. If it is a mixed machine, then you have to think about it. This does not change how postgres uses RAM, it changes how postgres estimates whether an Index scan will be cheaper than a Sequential scan, based on the likelihood that the data you want will already be cached in Ram. If you dataset is only 85MB, and you don't think it will grow, you really don't have to worry about this much. You have a very small database. 3.) max_connections, been trying to figure 'how' to determine this #. I've read this is buffer_size+500k per a connection. ie. 128mb(buffer) + 500kb = 128.5mb per connection? Max connections is just how many concurrent connections you want to allow. If you can get away with lower, do so. Mostly this is to prevent connections * work_mem to get bigger than your real working memory and causing you to swap. I was curious about 'sort_mem' I can't find reference of it in the 8.0.3 documentation, has it been removed? sort_mem changed to work_mem in 8.0, same thing with vacuum_mem - maintenance_work_mem. work_mem and max_stack_depth set to 4096 maintenance_work_mem set to 64mb Depends how much space you want to give per connection. 4M is pretty small for a machine with 4G of RAM, but if your DB is only 85M it might be plenty. work_mem is how much memory a sort/hash/etc will use before it spills to disk. So look at your queries. If you tend to sort most of your 85M db in a single query, you might want to make it a little bit more. But if all of your queries are very selective, 4M could be plenty. I would make maintenance_work_mem more like 512M. It is only used for CREATE INDEX, VACUUM, etc. Things that are not generally done by more than one process at a time. And it's nice for them to have plenty of room to run fast. Thanks for any help on this. I'm sure bombardment of newbies gets old :) -William Good luck, John =:- Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com ---(end of broadcast)--- TIP 3: 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
Fwd: [PERFORM] Speed with offset clause
Hmm, I can't do this, i'm afraid. Or it would be rather difficult My query is executed through a webpage (link to the page in a navigation bar) I do not know how many records there are (data is changing, and currently is 600k records) The only thing I could do, is doing this in a function where I first get the page, and then decide whether to use the normal sort order or the reversed order That would put my weak point right in the middle, which is not that bad, but I would like to find an easier way, if that is possible Huge memory would help ? On 24 Jun 2005, at 20:54, hubert depesz lubaczewski wrote: On 6/24/05, Yves Vindevogel [EMAIL PROTECTED]> wrote: So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 improving this is hard, but not impossible. if you have right index created, try to reverse the order and fetch first adverts, and then resort it (just the 25 adverts) in correct order. it will be faster. depesz Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smallerMet vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Speed with offset clause
Hi, Indeed, I would have to do it through a function, where I check the number of pages, It puts my weakest point in the middle then. I could simply rewrite my query like you state, just to check. I think all my queries are on one table only. (I report in a website on one table, that has been denormalized into other smaller tables for speed) But the problem is on the big table. I'm currently looking at another possibility, and that is generating XML files based upon my database. This would increase disk space enormously, but limit my problems with the database. Since I am using Cocoon for the website, this is not such a problematic decision, disks are cheap and I need only a few modifications to my code. On 24 Jun 2005, at 21:22, John A Meinel wrote: Yves Vindevogel wrote: Hi again all, My queries are now optimised. They all use the indexes like they should. However, there's still a slight problem when I issue the offset clause. We have a table that contains 600.000 records We display them by 25 in the webpage. So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 This take a long time to run, about 5-10 seconds whereas offset below 100 take less than a second. Can I speed this up ? Met vriendelijke groeten, Bien à vous, Kind regards, *Yves Vindevogel* *Implements* Postgres has the optimization that it will plan a query, and once it reaches the limit, it can stop even though there is more data available. The problem you are having is that it has to go through offset rows first, before it can apply the limit. If you can, (as mentioned in the other post), try to refine your index so that you can reverse it for the second half of the data. This is probably tricky, as you may not know how many rows you have (or the amount might be changing). A potentially better thing, is if you have an index you are using, you could use a subselect so that the only portion that needs to have 60k rows is a single column. Maybe an example: Instead of saying: SELECT * FROM table1, table2 WHERE table1.id = table2.id ORDER BY table1.date OFFSET x LIMIT 25; You could do: SELECT * FROM (SELECT id FROM table1 OFFSET x LIMIT 25) as subselect JOIN table1 ON subselect.id = table1.id , table2 WHERE table1.id = table2.id; That means that the culling process is done on only a few rows of one table, and the rest of the real merging work is done on only a few rows. It really depends on you query, though, as what rows you are sorting on has a big influence on how well this will work. John =:-> Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Speed with offset clause
I just ran this query select p.* from tblPrintjobs p , (select oid from tblPrintjobs limit 25 offset 622825) as subset where p.oid = subset.oid And it seems to be a bit faster than without the subselect, probably because I'm only getting one column. The speed gain is not that high though On 24 Jun 2005, at 22:19, Yves Vindevogel wrote: Hmm, I can't do this, i'm afraid. Or it would be rather difficult My query is executed through a webpage (link to the page in a navigation bar) I do not know how many records there are (data is changing, and currently is 600k records) The only thing I could do, is doing this in a function where I first get the page, and then decide whether to use the normal sort order or the reversed order That would put my weak point right in the middle, which is not that bad, but I would like to find an easier way, if that is possible Huge memory would help ? On 24 Jun 2005, at 20:54, hubert depesz lubaczewski wrote: On 6/24/05, Yves Vindevogel [EMAIL PROTECTED]> wrote: So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 improving this is hard, but not impossible. if you have right index created, try to reverse the order and fetch first adverts, and then resort it (just the 25 adverts) in correct order. it will be faster. depesz Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Pasted Graphic 2.tiff> Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smallerMet vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Pasted Graphic 2.tiff> Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Needed: Simplified guide to optimal memory configuration
For those who provided some guidance, I say thank you. You comments helped out a lot. All of our customers who are using the older release are now very pleased with the performance of the database now that we were able to give them meaningful configuration settings. I'm also pleased to see that Frank WIles has taken upon himself the effort to write this guidance down for folks like me. Kudos to you all. Thanks again. Todd On Jun 15, 2005, at 2:06 AM, Todd Landfried wrote: I deeply apologize if this has been covered with some similar topic before, but I need a little guidance in the optimization department. We use Postgres as our database and we're having some issues dealing with customers who are, shall we say, thrifty when it comes to buying RAM. We tell them to buy at least 1GB, but there's always the bargain chaser who thinks 256MB of RAM is more than enough. So here's what I need--in layman's terms 'cause I'll need to forward this message on to them to prove what I'm saying (don't ya love customers?). 1. Our database has a total of 35 tables and maybe 300 variables 2. There are five primary tables and only two of these are written to every minute, sometimes up to a menial 1500 transactions per minute. 3. Our customers usually buy RAM in 256MB, 512MB, 1GB or 2GB. We've tried to come up with a optimization scheme based on what we've been able to discern from lists like this, but we don't have a lot of confidence. Using the default settings seems to work best with 1GB, but we need help with the other RAM sizes. What's the problem? The sucker gets s-l-o-w on relatively simple queries. For example, simply listing all of the users online at one time takes 30-45 seconds if we're talking about 800 users. We've adjusted the time period for vacuuming the tables to the point where it occurs once an hour, but we're getting only a 25% performance gain from that. We're looking at the system settings now to see how those can be tweaked. So, what I need is to be pointed to (or told) what are the best settings for our database given these memory configurations. What should we do? Thanks Todd Don't know if this will help, but here's the result of show all: NOTICE: enable_seqscan is on NOTICE: enable_indexscan is on NOTICE: enable_tidscan is on NOTICE: enable_sort is on NOTICE: enable_nestloop is on NOTICE: enable_mergejoin is on NOTICE: enable_hashjoin is on NOTICE: ksqo is off NOTICE: geqo is on NOTICE: tcpip_socket is on NOTICE: ssl is off NOTICE: fsync is on NOTICE: silent_mode is off NOTICE: log_connections is off NOTICE: log_timestamp is off NOTICE: log_pid is off NOTICE: debug_print_query is off NOTICE: debug_print_parse is off NOTICE: debug_print_rewritten is off NOTICE: debug_print_plan is off NOTICE: debug_pretty_print is off NOTICE: show_parser_stats is off NOTICE: show_planner_stats is off NOTICE: show_executor_stats is off NOTICE: show_query_stats is off NOTICE: stats_start_collector is on NOTICE: stats_reset_on_server_start is on NOTICE: stats_command_string is off NOTICE: stats_row_level is off NOTICE: stats_block_level is off NOTICE: trace_notify is off NOTICE: hostname_lookup is off NOTICE: show_source_port is off NOTICE: sql_inheritance is on NOTICE: australian_timezones is off NOTICE: fixbtree is on NOTICE: password_encryption is off NOTICE: transform_null_equals is off NOTICE: geqo_threshold is 20 NOTICE: geqo_pool_size is 0 NOTICE: geqo_effort is 1 NOTICE: geqo_generations is 0 NOTICE: geqo_random_seed is -1 NOTICE: deadlock_timeout is 1000 NOTICE: syslog is 0 NOTICE: max_connections is 64 NOTICE: shared_buffers is 256 NOTICE: port is 5432 NOTICE: unix_socket_permissions is 511 NOTICE: sort_mem is 2048 NOTICE: vacuum_mem is 126622 NOTICE: max_files_per_process is 1000 NOTICE: debug_level is 0 NOTICE: max_expr_depth is 1 NOTICE: max_fsm_relations is 500 NOTICE: max_fsm_pages is 1 NOTICE: max_locks_per_transaction is 64 NOTICE: authentication_timeout is 60 NOTICE: pre_auth_delay is 0 NOTICE: checkpoint_segments is 3 NOTICE: checkpoint_timeout is 300 NOTICE: wal_buffers is 8 NOTICE: wal_files is 0 NOTICE: wal_debug is 0 NOTICE: commit_delay is 0 NOTICE: commit_siblings is 5 NOTICE: effective_cache_size is 79350 NOTICE: random_page_cost is 2 NOTICE: cpu_tuple_cost is 0.01 NOTICE: cpu_index_tuple_cost is 0.001 NOTICE: cpu_operator_cost is 0.0025 NOTICE: geqo_selection_bias is 2 NOTICE: default_transaction_isolation is read committed NOTICE: dynamic_library_path is $libdir NOTICE: krb_server_keyfile is FILE:/etc/pgsql/krb5.keytab NOTICE: syslog_facility is LOCAL0 NOTICE: syslog_ident is postgres NOTICE: unix_socket_group is unset NOTICE: unix_socket_directory is unset NOTICE: virtual_host is unset NOTICE: wal_sync_method is fdatasync NOTICE: DateStyle is ISO with US (NonEuropean) conventions NOTICE: Time zone is unset NOTICE: TRANSACTION
Re: [PERFORM] Performance Tuning Article
Hi, The article seems to dismiss RAID5 a little too quickly. For many application types, using fast striped mirrors for the index space and RAID5 for the data can offer quite good performance (provided a sufficient number of spindles for the RAID5 - 5 or 6 disks or more). In fact, random read (ie most webapps) performance of RAID5 isn't necessarily worse than that of RAID10, and can in fact be better in some circumstances. And, using the cheaper RAID5 might allow you to do that separation between index and data in the first place. Just thought I'd mention it, Dmitri -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Frank Wiles Sent: Wednesday, June 22, 2005 10:52 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Performance Tuning Article Hi Everyone, I've put together a short article and posted it online regarding performance tuning PostgreSQL in general. I believe it helps to bring together the info in a easy to digest manner. I would appreciate any feedback, comments, and especially any technical corrections. The article can be found here: http://www.revsys.com/writings/postgresql-performance.html Thanks! - Frank Wiles [EMAIL PROTECTED] http://www.wiles.org - ---(end of broadcast)--- TIP 3: 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 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance - moving from oracle to postgresql
There are some immediate questions from our engineers about performance - Oracle has one particular performance enhancement that Postgres is missing. If you do a select that returns 100,000 rows in a given order, and all you want are rows 99101 to 99200, then Oracle can do that very efficiently. With Postgres, it has to read the first 99200 rows and then discard the first 99100. But... If we really want to look at performance, then we ought to put together a set of benchmarks of some typical tasks. Is this accurate: accoring to http://www.postgresql.org/docs/8.0/interactive/queries-limit.html -- The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET can be inefficient. Yes. That's accurate. First you need to determine whether PostgreSQLs method is fast enough for that specific query, and if the performance gains for other queries (inserts, updates, delete) from reduced index management evens out your concern. All performance gains through design changes either increase complexity dramatically or have a performance trade-off elsewhere. I find it rather odd that anyone would issue a single one-off select for 0.1% of the data about 99.1% of the way through, without doing anything with the rest. Perhaps you want to take a look at using a CURSOR? Where is psql not appropriate to replace Oracle? Anything involving reporting using complex aggregates or very long running selects which Oracle can divide amongst multiple CPUs. Well, PostgreSQL can do it if you give it enough time to run the query, but a CUBE in PostgreSQL on a TB sized table would likely take significantly longer to complete. It's mostly just that the Pg developers haven't implemented those features optimally, or at all, yet. -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] parameterized LIKE does not use index
On Thu, Jun 23, 2005 at 11:55:35AM -0700, Josh Berkus wrote: Bruno, I remember some discussion about delaying planning until the first actual query so that planning could use actual parameters to do the planning. If you really want to have it check the parameters every time, I think you will need to replan every time. I don't know if there is a way to save some of the prepare working while doing this. That wouldn't help much in Kurt's case.Nor in most real cases, which is why I think the idea never went anywhere. I suspect the only way to do this and have it work well would be to cache plans based on the relevant statistics of the parameters passed in. Basically, as part of parsing (which could always be cached, btw, so long as schema changes clear the cache), you store what fields in what tables/indexes each parameter corresponds to. When you go to execute you look up the stats relevant to each parameter; you can then cache plans according to the stats each parameter has. Of course caching all that is a non-trivial amount of work, so you'd only want to do it for pretty complex queries. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Configurator project launched
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sounds a little similar to what's in pgAdmin CVS right now. The configuration editor can retrieve the config file and display configured and active setting concurrently, together with explanations taken from pg_settings (when not run against a pgsql server but a file current settings are missing, comments are taken from a pg_settings csv dump). There's the infrastructure to give hints about all settings, with very few currently implemented. I wonder if this could be combined with the configurator somehow. Currently, integration won't work with Perl, so maybe C for the core and Perl for the interactive part would be better. Probably so. Seems there is a bit of convergent evolution going on. When I get a moment of free time, I'll check out the pgAdmin code. Can someone shoot me a URL to the files in question? (assuming a web cvs interface). - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506242107 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCvK6AvJuQZxSWSsgRApFcAKDVQ5OdVgVc2PmY/p719teJ3BqNjQCgrgyx +w+w8GCGXUFO+5dxi5RPwKo= =eG7M -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
