[PERFORM] PostgreSQL data on a NAS device ?
Hi all, Does anyone have any experience with putting PostgreSQL data on a NAS device? I am asking this because a NAS device is much cheaper to set up than a couple of SCSI disks. I would like to use a relatively cheap NAS device which uses four IDE drives (7.200 rpm), like the Dell PowerVault 725N. The disks themselves would be much slower than SCSI disks, I know, but a NAS device can be equipped with 3 Gb of memory, so this would make a very large disk cache, right? If this NAS would be dedicated only to PostgreSQL, would this be slower/faster than a SCSI RAID-10 setup of 6 disks? It would be much cheaper... Any advice on this would be appreciated :) Kind regards, Alexander Priem. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] PostgreSQL data on a NAS device ?
On Mon, 20 Oct 2003 09:12:35 +0200 Alexander Priem [EMAIL PROTECTED] wrote: I am asking this because a NAS device is much cheaper to set up than a couple of SCSI disks. I would like to use a relatively cheap NAS device which uses four IDE drives (7.200 rpm), like the Dell PowerVault 725N. The disks themselves would be much slower than SCSI disks, I know, but a NAS device can be equipped with 3 Gb of memory, so this would make a very large disk cache, right? If this NAS would be dedicated only to PostgreSQL, would this be slower/faster than a SCSI RAID-10 setup of 6 disks? It would be much cheaper... The big concern would be the network connection, unless you are going fiber. You need to use _AT LEAST_ gigabit. _at least_.If you do go that route it'd be interesting to see bonnie results. And the other thing - remember that just because you are running NAS doesn't mean you can attach another machine running postgres and have a cluster. (See archives for more info about this). I suppose it all boils down to your budget (I usually get to work with a budget of $0). And I mentioned this in another post- If you don't mind refurb disks(or slightly used) check out ebay - you can get scsi disks by the truckload for cheap. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL data on a NAS device ?
Thanks for your reply, Jeff. If we are going to use a NAS device for storage, then it will be attached through a gigabit ethernet connection. Fiber will not be an option, since that would negate the savings we can make by using an IDE NAS device instead of SCSI-RAID, fiber's pretty expensive, right? Using a NAS device (that is used only by PostgreSQL, so it's dedicated) with 3Gb of RAM and four 7200 rpm IDE harddisks, connected using a gigabit ethernet connection to the PostgreSQL server, do you think it will be a match for a SCSI-RAID config using 4 or 6 15000rpm disks (RAID-10) through a SCSI-RAID controller having 128mb of writeback cache (battery-backed)? The SCSI-RAID config would be a lot more expensive. I can't purchase both configs and test which one wil be faster, but if the NAS solution would be (almost) as fast as the SCSI-RAID solution, it would be cheaper and easier to maintain... About clustering: I know this can't be done by hooking multiple postmasters to one and the same NAS. This would result in data corruption, i've read... Kind regards, Alexander. - Original Message - From: Jeff [EMAIL PROTECTED] To: Alexander Priem [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 20, 2003 2:20 PM Subject: Re: [PERFORM] PostgreSQL data on a NAS device ? On Mon, 20 Oct 2003 09:12:35 +0200 Alexander Priem [EMAIL PROTECTED] wrote: I am asking this because a NAS device is much cheaper to set up than a couple of SCSI disks. I would like to use a relatively cheap NAS device which uses four IDE drives (7.200 rpm), like the Dell PowerVault 725N. The disks themselves would be much slower than SCSI disks, I know, but a NAS device can be equipped with 3 Gb of memory, so this would make a very large disk cache, right? If this NAS would be dedicated only to PostgreSQL, would this be slower/faster than a SCSI RAID-10 setup of 6 disks? It would be much cheaper... The big concern would be the network connection, unless you are going fiber. You need to use _AT LEAST_ gigabit. _at least_.If you do go that route it'd be interesting to see bonnie results. And the other thing - remember that just because you are running NAS doesn't mean you can attach another machine running postgres and have a cluster. (See archives for more info about this). I suppose it all boils down to your budget (I usually get to work with a budget of $0). And I mentioned this in another post- If you don't mind refurb disks(or slightly used) check out ebay - you can get scsi disks by the truckload for cheap. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL data on a NAS device ?
Even better than the four-disk NAS I mentioned earlier is the following: Promise UltraTrak RM8000. This is a so-called SCSI-to-IDE RAID system. Basically it's a RAID setup of eight IDE disks, using a hardware RAID engine, that's connected to (in this case) the PostgreSQL server via a SCSI Ultra160 interface (!). So the server won't know any better than that there's a SCSI disk attached, but in reality it's a IDE RAID setup. It supports RAID levels 0, 1, 0+1, 5, 50 and JBOD and supports hot-swapping. Such a NAS config would cost around EUR 3700 (ex. VAT), using 8x40 Gb IDE disks (7200rpm). A SCSI RAID-10 setup using 6x18Gb (15000rpm) disks would cost around EUR 6000 (ex. VAT) so it's a big difference... Does anyone have experience with this NAS device or other SCSI-to-IDE RAID systems? Are they OK in terms of performance and reliability? Kind regards, Alexander. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL data on a NAS device ?
Alexander Priem kirjutas E, 20.10.2003 kell 16:04: Even better than the four-disk NAS I mentioned earlier is the following: Promise UltraTrak RM8000. This is a so-called SCSI-to-IDE RAID system. While you are at it, you could also check out http://www.3ware.com/ I guess one of these with 1 rpm 36GB SATA drivest would be pretty fast and possibly cheaper than SCSI raid. -- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL data on a NAS device ?
Hello Alexander, On Mon, 2003-10-20 at 06:04, Alexander Priem wrote: Even better than the four-disk NAS I mentioned earlier is the following: Promise UltraTrak RM8000. This is a so-called SCSI-to-IDE RAID system. Basically it's a RAID setup of eight IDE disks, using a hardware RAID engine, that's connected to (in this case) the PostgreSQL server via a SCSI Ultra160 interface (!). So the server won't know any better than that there's a SCSI disk attached, but in reality it's a IDE RAID setup. It supports RAID levels 0, 1, 0+1, 5, 50 and JBOD and supports hot-swapping. We have a Promise FasTrak 4000 in our development server connected to 120 Gig western digital 8mb cache drives. Basically the fastest drives we could get for an ide configuration. This system works well, however there are a few things you need to consider. The biggest is that you have very limited control over your devices with the Promise controllers. The bios of the raid controller doesn't have many options on it. You basically plug everything together, and just hope it works. It usually does, but there have been times in the past that really gave us a scare. And we had a situation that in a hard poweroff ( UPS died ) we suffered complete corruptions of 2 of our 4 drives. Performance wise it is =okay= but definitely not on par with either our Megaraid elite 1650 controller or a solution I'm going to suggest to you later in this mail. Your biggest hit is going to be multiple simultaneous accesses. The controller and drives just can't keep up to it. Realistically with my experiences I cannot recommend this solution for a production machine, even with the budget constraints you have put forth. Such a NAS config would cost around EUR 3700 (ex. VAT), using 8x40 Gb IDE disks (7200rpm). A SCSI RAID-10 setup using 6x18Gb (15000rpm) disks would cost around EUR 6000 (ex. VAT) so it's a big difference... I'm not sure where you have your figures, but I would like to propose the following solution for you. for your boot device use either a single ide drive and keep an exact duplicate of the drive in the event of a drive failure, or use 2 drives and use software raid to mirror the two. In this manner you can spend approx $100 USD for each drive and no additional cost for your controller as you will use the motherboards IDE controller. For your postgresql partition or even /var use software raid on an adaptec 29320-R SCSI controller. ( http://www.adaptec.com/worldwide/product/proddetail.html?sess=nolanguage=English+USprodkey=ASC-39320-Rcat=%2fTechnology%2fSCSI%2fUltra320+SCSI ) cost: $399 USD IF you bought it from adaptec Match this with 6 Seagate 10k 36G Cheetah U320 scsi drives: ( http://www.c-source.com/csource/newsite/ttechnote.asp?part_no=207024 ) for a cost of $189 USD per drive. If you have 6 of them it brings the total price for your drives to $1134 USD. Total cost for this would be approx $1633 before shipping costs. We use this configuration in our two file servers and have nothing but positive results. If you are totally unable to use software raid you could still buy 6 of those drives, and spend approx $900 USD on an LSI Megaraid 1650 controller. I really believe you'll find either of those options to be superior in terms of price for you. Sincerely, Will LaShell Does anyone have experience with this NAS device or other SCSI-to-IDE RAID systems? Are they OK in terms of performance and reliability? Kind regards, Alexander. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings signature.asc Description: This is a digitally signed message part
[PERFORM] Performance weirdness with/without vacuum analyze
It has been suggested to me that I resubmit this question to this list, rather than the GENERAL list it was originaly sent to. I asked earlier about ways of doing an UPDATE involving a left outer join and got some very useful feedback. This has thrown up a (to me) strange anomaly about the speed of such an update. The input to this query is a fairly large (the example I'm working with has 335,000 rows) set of records containing numbers to be looked up in the lookup table. This lookup table has 239 rows. I'm always reading the suggestion that doing a 'VACUUM ANALYZE' on a database is 'A Good Thing' as it helps the planner to do the best thing, so I arranged a vacuum analyze on the input records. Running the query takes about 13 mins or so. If, however I *don't* do an analyze, but leave the input table as it was when imported the run takes about 2.5 mins! Looking at the output from 'explain' I can see that the main difference in the way the planner does it is that it does a merge join in the non-analyze case, and a hash join in the analyze case. Unfortunately I don't really know what this is implying, hence the call for assistance. I have a file with all sorts of info about the problem (details of tables, output of 'explain' etc) but as it is about 5K in size, and wide as well, I didn't want to dump it in the list without any warning! However - it has been suggested that it should be OK to include this I have now done so - hopefully with this message. Regards, Harry. select version(); version - PostgreSQL 7.3.4 on i386-unknown-freebsd4.5, compiled by GCC 2.95.3 create table num_xlate (interim_num varchar(30) not null , num varchar(30) not null, starttime timestamp with time zone not null, endtime timestamp with time zone not null, constraint num_pos_dur check (endtime = starttime), primary key (interim_num, starttime)); create table unrated_cdrs (cdr_id bigserial unique, interim_cli varchar(30), interim_tli varchar(30), cli varchar(30), tli varchar(30)); CREATE TABLE copy unrated_cdrs (interim_cli, interim_tli) from '/data/swipe/bin/mt2.csv' with delimiter as ','; COPY explain update unrated_cdrs set cli = coalesce(b.num, unrated_cdrs.interim_cli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_cli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id; QUERY PLAN -- Merge Join (cost=286.99..358.99 rows=1000 width=393) Merge Cond: (outer.cdr_id = inner.cdr_id) - Index Scan using unrated_cdrs_cdr_id_key on unrated_cdrs (cost=0.00..52.00 rows=1000 width=262) - Sort (cost=286.99..289.49 rows=1000 width=131) Sort Key: un.cdr_id - Merge Join (cost=139.66..237.16 rows=1000 width=131) Merge Cond: (outer.interim_cli = inner.interim_num) Join Filter: ((outer.starttime = inner.starttime) AND (outer.starttime = inner.endtime)) - Sort (cost=69.83..72.33 rows=1000 width=49) Sort Key: un.interim_cli - Seq Scan on unrated_cdrs un (cost=0.00..20.00 rows=1000 width=49) - Sort (cost=69.83..72.33 rows=1000 width=82) Sort Key: num_xlate.interim_num - Seq Scan on num_xlate (cost=0.00..20.00 rows=1000 width=82) (14 rows) update unrated_cdrs set cli = coalesce(b.num, unrated_cdrs.interim_cli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_cli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id; UPDATE 335671 update unrated_cdrs set tli = coalesce(b.num, unrated_cdrs.interim_tli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_tli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id; UPDATE 335671 2m57.37s real 0.00s user 0.00s sys DROP TABLE create table unrated_cdrs (cdr_id bigserial unique, interim_cli varchar(30), interim_tli varchar(30), cli varchar(30), tli varchar(30)); CREATE TABLE copy unrated_cdrs (interim_cli, interim_tli) from '/data/swipe/bin/mt2.csv' with delimiter as ','; COPY vacuum analyze unrated_cdrs; VACUUM explain update unrated_cdrs set cli = coalesce(b.num, unrated_cdrs.interim_cli) from (unrated_cdrs as un left outer join num_xlate on (un.interim_cli = interim_num and un.starttime between num_xlate.starttime and num_xlate.endtime)) as b where unrated_cdrs.cdr_id = b.cdr_id;
Re: [PERFORM] Performance weirdness with/without vacuum analyze
Harry, It has been suggested to me that I resubmit this question to this list, rather than the GENERAL list it was originaly sent to. I asked earlier about ways of doing an UPDATE involving a left outer join and got some very useful feedback. The query you posted will always be somewhat slow due to the forced join order, which is unavodable with a left outer join. However, regarding your peculiar behaviour, please post: 1) Your random_page_cost and effective_cache_size settings 2) The EXPLAIN ANALYZE of each query instead of just the EXPLAIN Thanks! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL data on a NAS device ?
On Mon, 20 Oct 2003, Alexander Priem wrote: Hi all, Does anyone have any experience with putting PostgreSQL data on a NAS device? I am asking this because a NAS device is much cheaper to set up than a couple of SCSI disks. I would like to use a relatively cheap NAS device which uses four IDE drives (7.200 rpm), like the Dell PowerVault 725N. The disks themselves would be much slower than SCSI disks, I know, but a NAS device can be equipped with 3 Gb of memory, so this would make a very large disk cache, right? If this NAS would be dedicated only to PostgreSQL, would this be slower/faster than a SCSI RAID-10 setup of 6 disks? It would be much cheaper... Any advice on this would be appreciated :) How important is this data? With a local SCSI RAID controller and SCSI drives, you can pull the power cord out the back of the machine during 1000 transactions, and your database will come back up in a coherent state. If you need that kind of reliability, then you'll likely want to use local SCSI drives. Note that you should test your setup to be sure, i.e. pull the network cord and see how the machine recovers (if the machine recovers). Running storage on a NAS is a bit of a tightrope act with your data, as is using IDE drives with write cache enabled. But depending on your application, using NAS may be a good solution. So, what's this database gonna be used for? ---(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] index file bloating still in 7.4 ?
Hi Tom, Josh, We tried one more thing: with the table not being updated at all and we did vacuum. Each time a vacuum is done, the index file becomes bigger. This is probably what is contributing to the index file growing as well. Thanks. Gan At 11:04 am -0500 2003/10/20, Seum-Lim Gan wrote: Hi Josh, Tom, OK. As I understand it, vacuum does not release the space used by the index file. However, it should be able to reuse the space for indexing. I have observed that during initial updates of the table, the index file did not grow and was steady but it did not last long and keeps growing afterwards. Vacuum/vacuum analyze did not help. In all the update testing, vacuum analyze was done every 1 minute. Tom, something caught your attention the last time. Any insight so far ? Is it a bug ? Thanks. Gan Tom Lane wrote: Seum-Lim Gan [EMAIL PROTECTED] writes: vacuum verbose analyze dsperf_rda_or_key; INFO: vacuuming scncraft.dsperf_rda_or_key INFO: index dsperf242_1105 now contains 30 row versions in 12387 pages DETAIL: 3097702 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. Hm, interesting that you deleted 90% of the entries and still had no empty index pages at all. What was the pattern of your deletes and/or updates with respect to this index's key? However, when I check the disk space usage, it has not changed. It won't in any case. Plain VACUUM is designed for maintaining a steady-state level of free space in tables and indexes, not for returning major amounts of space to the OS. For that you need more-invasive operations like VACUUM FULL or REINDEX. regards, tom lane At 12:04 pm -0700 2003/10/19, Josh Berkus wrote: Gan, Oh, so in order to reclaim the disk space, we must run reindex or vacuum full ? This will lock out the table and we won't be able to do anything. Looks like this is a problem. It means we cannot use it for 24x7 operations without having to stop the process and do the vacuum full and reindex. Is there anything down the road that these operations will not lock out the table ? I doubt it; the amount of page-shuffling required to reclaim 90% of the space in an index for a table that has been mostly cleared is substantial, and would prevent concurrent access. Also, you seem to have set up an impossible situation for VACUUM. If I'm reading your statistics right, you have a large number of threads accessing most of the data 100% of the time, preventing VACUUM from cleaning up the pages.This is not, in my experience, a realistic test case ... there are peak and idle periods for all databases, even webservers that have been slashdotted. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- ++ | Seum-Lim GAN email : [EMAIL PROTECTED] | | Lucent Technologies| | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA.fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | ++ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- ++ | Seum-Lim GAN email : [EMAIL PROTECTED] | | Lucent Technologies| | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA.fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | ++ ---(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] index file bloating still in 7.4 ?
Seum-Lim Gan [EMAIL PROTECTED] writes: We tried one more thing: with the table not being updated at all and we did vacuum. Each time a vacuum is done, the index file becomes bigger. It is not possible for plain vacuum to make the index bigger. VACUUM FULL possibly could make the index bigger, since it has to transiently create duplicate index entries for every row it moves. If you want any really useful comments on your situation, you're going to have to offer considerably more detail than you have done so far --- preferably, a test case that lets someone else reproduce your results. So far, all we can do is guess on the basis of very incomplete information. When you aren't even bothering to mention whether a vacuum is FULL or not, I have to wonder whether I have any realistic picture of what's going on. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] index file bloating still in 7.4 ?
Seum-Lim Gan [EMAIL PROTECTED] writes: [ successive outputs from VACUUM ANALYZE ] FWIW, I don't think your problem is really index bloat at all, it's more like too-many-dead-rows bloat. Note that the number of dead row versions is climbing steadily from run to run: DETAIL: 101802 dead row versions cannot be removed yet. DETAIL: 110900 dead row versions cannot be removed yet. DETAIL: 753064 dead row versions cannot be removed yet. DETAIL: 765328 dead row versions cannot be removed yet. It's hardly the index's fault that it's growing, when it has to keep track of an ever-increasing number of rows. The real question is what you're doing that requires the system to keep hold of these dead rows instead of recycling them. I suspect you have a client process somewhere that is holding an open transaction for a long time ... probably not doing anything, just sitting there with an unclosed BEGIN ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] SRFs ... no performance penalty?
Folks, I'm working on the demo session for our upcoming presentation at PHPCon. As a side issue, we ended up comparing 3 versions of the same search screen: 1) All in PHP with views; 2) Using a function to build a query and count results but executing that query directly and sorting, paging in PHP; 3) Using a Set Returning function to handle row-returning, sorting, and paging. All three methods were executing a series moderately complex query against a medium-sized data set (only about 20,000 rows but it's on a laptop). The postgresql.conf was tuned like a webserver; e.g. low sort_mem, high max_connections. So far, on the average of several searches, we have: 1) 0.19687 seconds 2) 0.20667 seconds 3) 0.20594 seconds In our tests, using any kind of PL/pgSQL function seems to carry a 0.01 second penalty over using PHP to build the search query. I'm not sure if this is comparitive time for string-parsing or something else; the 0.01 seems to be consistent regardless of scale. The difference between using a PL/pgSQL function as a query-builder only (the 7.2.x method) and using SRFs was small enough not to be significant. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Low Insert/Update Performance
Rhaoni Chiu Pereira kirjutas E, 20.10.2003 kell 17:13: Hi List, I got a P4 1.7Ghz , 512MB RAM , HD 7200 RPM, on RED HAT 9 running PostgreSQL 7.3.2-3 Database. I have a Delphi aplication that updates the Oracle database using .dbf file's information ( converting the data from the old clipper aplication ) and it takes about 3min and 45 seconds to update Jan/2003 . Have you tried contrib/dbase to do the same ? How fast does this run My problem is that I must substitute this Oracle for a PostgreSQL database and this same Delphi aplication takes 45 min to update Jan/2003. All delphi routines are converted and optmized to work with PgSQL. Could it be that you try to run each insert in a separate transaction in PgSQL version ? Another possibility is that there is a primary key index created on empty tables which is not used in subsequent UNIQUE tests when tables start to fill and using index would be useful. An ANALYZE in a parallel backend could help here. Same can be true for foreign keys and unique constraints. --- Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] Low Insert/Update Performance
Hi List, I got a P4 1.7Ghz , 512MB RAM , HD 7200 RPM, on RED HAT 9 running PostgreSQL 7.3.2-3 Database. I have a Delphi aplication that updates the Oracle database using .dbf file's information ( converting the data from the old clipper aplication ) and it takes about 3min and 45 seconds to update Jan/2003 . My problem is that I must substitute this Oracle for a PostgreSQL database and this same Delphi aplication takes 45 min to update Jan/2003. All delphi routines are converted and optmized to work with PgSQL. Here follows my postgresql.conf: # # Connection Parameters # tcpip_socket = true #ssl = false max_connections = 10 #superuser_reserved_connections = 2 port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #virtual_host = '' #krb_server_keyfile = '' # # Shared Memory Size # shared_buffers = 1 # min max_connections*2 or 16, 8KB each max_fsm_relations = 2000# min 10, fsm is free space map, ~40 bytes max_fsm_pages = 2 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 64 # min 10 #wal_buffers = # min 4, typically 8KB each # # Non-shared Memory Sizes # sort_mem = 8000 # min 64, size in KB vacuum_mem = 16192 # min 1024, size in KB # # Write-ahead log (WAL) # checkpoint_segments = 9 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds # #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 # fsync = false #wal_sync_method = fsync# the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 # # Optimizer Parameters # enable_seqscan = false enable_indexscan = true enable_tidscan = true enable_sort = true enable_nestloop = true enable_mergejoin = true enable_hashjoin = true effective_cache_size = 16000# typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) default_statistics_target = 1000# range 1-1000 # # GEQO Optimizer Parameters # #geqo = true #geqo_selection_bias = 2.0 # range 1.5-2.0 #geqo_threshold = 11 #geqo_pool_size = 0 # default based on tables in statement, # range 128-1024 #geqo_effort = 1 #geqo_generations = 0 #geqo_random_seed = -1 # auto-compute seed # # Message display # #server_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error #silent_mode = false #log_connections = false #log_pid = false #log_statement = false #log_duration = false log_timestamp = true #log_min_error_statement = error # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false #explain_pretty_print = true # requires USE_ASSERT_CHECKING #debug_assertions = true # # Syslog # #syslog = 0 # range 0-2 #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # # Statistics # #show_parser_stats = false #show_planner_stats = false #show_executor_stats = false #show_statement_stats = false # requires BTREE_BUILD_STATS #show_btree_build_stats = false # # Access statistics collection # #stats_start_collector = true #stats_reset_on_server_start = true #stats_command_string = false #stats_row_level = false #stats_block_level = false # # Lock Tracing # #trace_notify = false # requires LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0 # # Misc # #autocommit = true #dynamic_library_path = '$libdir' search_path = 'vendas' #datestyle = 'iso, us' #timezone = unknown # actually, defaults to TZ environment setting #australian_timezones = false #client_encoding = sql_ascii# actually, defaults to database encoding #authentication_timeout = 60# 1-600, in seconds #deadlock_timeout = 1000# in milliseconds #default_transaction_isolation = 'read committed'
Re: [PERFORM] Low Insert/Update Performance
On Mon, 20 Oct 2003 12:13:26 -0200 Rhaoni Chiu Pereira [EMAIL PROTECTED] wrote: Hi List, I got a P4 1.7Ghz , 512MB RAM , HD 7200 RPM, on RED HAT 9 running PostgreSQL 7.3.2-3 Database. [clip] Please send schema queries or we will not be able to help you. Also, if you could provide explain analyze of each query it would be even more helpful! -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Low Insert/Update Performance
Rhaoni, My problem is that I must substitute this Oracle for a PostgreSQL database and this same Delphi aplication takes 45 min to update Jan/2003. All delphi routines are converted and optmized to work with PgSQL. Obviously not. How about posting the update queries? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
