Re: [PERFORM] Delete query takes exorbitant amount of time
On Sun, 2005-03-27 at 07:05 -0800, Stephan Szabo wrote: > On Sat, 26 Mar 2005, Karim Nassar wrote: > > Some improvement. Even better once it's cached. Row estimate didn't > > change. Is this the best I can expect? Is there any other optimizations > > I am missing? > > I'm not sure, really. Running a seq scan for each removed row in the > referenced table doesn't seem like a particularly good plan in general > though, especially if the average number of rows being referenced isn't > on the order of 500k per value. I don't know what to look at next though. > Karim, please... run the EXPLAIN after doing SET enable_seqscan = off Thanks, Best Regards, Simon Riggs ---(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
[PERFORM] NFS RAID 0/5
Hi, I have a web app using PostgreSQL which indexes, searches and streams/downloads online movies. I think I have a problem with NFS and RAID, it is not strictly PostgreSQL but closely linked and I know many people on this list are experienced with this technology. Apologies if it is off topic. Sometimes it is hard to not be the Developer, Database and System Administrator all rolled into one. I have a FreeBSD box with 1TB disk space RAID 5, 800GB is used. This is mount via NFS onto Debian Linux running Apache/PHP/PostgreSQL. I have a script which loads the directory structure etc. into the database. As users surf the site web pages are generated by selecting from the database as per a standard web app. The server is on a 100mbit link and has reached up to 80mbits/s in the past not using NFS or RAID. The problem is when users start to stream/download the content the load averages go through the roof. Sometimes as high as 300. I can only see mostly Apache processes running, up to 2000 is the max. limit. Even after 200 Apache connections the load avg. is over 10. Could it be that using RAID 5 and NFS is causing the high load avg. on the Linux web servers? I have a machine with RAID 0 but not ready for a day or so. I will soon need to move the databases onto the NFS partition and am concerned it will increase my problem. Any advise much appreciated. Thank you. Regards, Rudi ---(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] Configuration/Tuning of server/DB
Reid, There are a few very valuable tuning documents that are part of the established PostgreSQL-related literature. You don't mention which version of postgres you'll be running, but here are the documents you'll find useful: postgresql.conf 7.4: http://www.varlena.com/varlena/GeneralBits/Tidbits/ annotated_conf_e.html 8.0: http://www.powerpostgresql.com/Downloads/annotated_conf_80.html general tuning http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source — Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 24, 2005, at 1:46 PM, Reid Thompson wrote: Using information found on the web, I've come up with some configuration and tuning parameters for a server/db that we will be implementing. I was wondering if I could generate some feedback as to configuration and tuning so that I could compare my estimations with those of others. Host is AIX 5.1 with 4 cpu's and 4 GB ram. Postgresql will be sharing this machine with other processes. Storage is an EMC storage array. The DB itself is very simple. Two tables, one with 40-45 columns ( largest column will likely contain no more than 32 chars of data ), the other with less than 5 columns ( largest column will contain no more than 20 chars data ). Expected transactions will be along the order of ~600K +- 100K inserts and ~600K +-200K updates per week. Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Delete query takes exorbitant amount of time
On Mon, 28 Mar 2005, Karim A Nassar wrote: > On Mon, 28 Mar 2005, Simon Riggs wrote: > > run the EXPLAIN after doing > > SET enable_seqscan = off > > The results I previously supplied were searching for a non-existent > value, so I have provided output for both cases. > > *** > *** Searching for non-existent value > *** > > orfs=# PREPARE test2(int) AS SELECT 1 from measurement where > orfs-# id_int_sensor_meas_type = $1 FOR UPDATE; > PREPARE > orfs=# EXPLAIN ANALYZE EXECUTE TEST2(1); > QUERY PLAN > -- > Seq Scan on measurement > (cost=0.00..164559.16 rows=509478 width=6) > (actual time=6421.849..6421.849 rows=0 loops=1) >Filter: (id_int_sensor_meas_type = $1) > Total runtime: 6421.917 ms > (3 rows) > > orfs=# SET enable_seqscan = off; I think you have to prepare with enable_seqscan=off, because it effects how the query is planned and prepared. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Delete query takes exorbitant amount of time
Tom Lane Wrote: > Hmm ... I wonder how hard it would be to teach EXPLAIN ANALYZE to show > the runtime expended in each trigger when the statement is of a kind > that has triggers. We couldn't break down the time *within* the > triggers, but even this info would help a lot in terms of finger > pointing ... > > Seq Scan on ... (nn.nnn ms) > Trigger foo: nn.mmm ms > Trigger bar: nn.mmm ms > Total time: nn.mmm ms So I got the latest from CVS on Friday night to see how hard it would be to implement this, but it turns out that Tom has already committed the improvement, so I'm in Tom's fan club today. I imported my test dataset and was almost immediately able to track down the cause of my performance problem. Thanks! Mark Lewis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Sluggish server performance
Pg: 7.4.5 RH 7.3 Raid 0+1 (200G 15k RPM) Quad Xeon 8G ram 95% Read-only 5% - read-write I'm experiencing extreme load issues on my machine anytime I have more than 40 users connected to the database. The majority of the users appear to be in an idle state according TOP, but if more than3 or more queries are ran the system slows to a crawl. The queries don't appear to the root cause because they run fine when the load drops. I also doing routine vacuuming on the tables. Is there some place I need to start looking for the issues bogging down the server? Here are some of my settings. I can provide more as needed: cat /proc/sys/kernel/shmmax 175013888 max_connections = 100 #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 2000 # min 16, at least max_connections*2, 8KB each sort_mem = 12288# min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB # - Free Space Map - max_fsm_pages = 300 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 500 # min 100, ~50 bytes each #--- # WRITE AHEAD LOG #--- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync wal_buffers = 32# min 4, 8KB each # - Checkpoints - checkpoint_segments = 50# in logfile segments, min 1, 16MB each checkpoint_timeout = 1800 # range 30-3600, in seconds # - Planner Cost Constants - effective_cache_size = 262144 # typically 8KB each #effective_cache_size = 625000 # typically 8KB each random_page_cost = 2# 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) Patrick Hatcher ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Sluggish server performance
Hi, At 20:20 28/03/2005, Patrick Hatcher wrote: I'm experiencing extreme load issues on my machine anytime I have more than 40 users connected to the database. The majority of the users appear to be in an idle state according TOP, but if more than3 or more queries are ran the system slows to a crawl. The queries don't appear to the root cause because they run fine when the load drops. I also doing routine vacuuming on the tables. Is there some place I need to start looking for the issues bogging down the server? Check that your queries use optimal plans, which usually (but not always) means they should use indexes rather than sequential scans. You can check for this by using EXPLAIN or EXPLAIN ANALYZE . You can also check the pg_stat_* and pg_statio_* tables to get a feel of what kind of accesses are done. You also might want to find out if your system is limited by IO or by the CPU. Most probably the former. You can also check the "performance tips" section of the manual. Also you shared_buffers setting seems to be pretty low given your configuration. Hope that helps, Jacques. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sluggish server performance
On Mon, 2005-03-28 at 10:20 -0800, Patrick Hatcher wrote: > > > > Pg: 7.4.5 > RH 7.3 > Raid 0+1 (200G 15k RPM) > Quad Xeon > 8G ram > > 95% Read-only > 5% - read-write > > I'm experiencing extreme load issues on my machine anytime I have more than > 40 users connected to the database. The majority of the users appear to be > in an idle state according TOP, but if more than3 or more queries are ran > the system slows to a crawl. The queries don't appear to the root cause > because they run fine when the load drops. I also doing routine vacuuming > on the tables. > > Is there some place I need to start looking for the issues bogging down the > server? Well your shared buffers seems a little low but beyond that you may have a couple of queries that run fine until you get into a highly concurrent situation. I would turn on statement, duration and pid logging. See if there is a query that takes say 400ms, if that query needs to be executed before a bunch of other queries then you will get immediately slow down in a highly concurrent environment. Also I didn't see your statistics target listed... What level is that at? Lastly you may be able to get away with a lower random_page_cost. Sincerely, Joshua D. Drake > > > Here are some of my settings. I can provide more as needed: > > > cat /proc/sys/kernel/shmmax > 175013888 > > max_connections = 100 > > #--- > # RESOURCE USAGE (except WAL) > #--- > > # - Memory - > > shared_buffers = 2000 # min 16, at least max_connections*2, 8KB > each > sort_mem = 12288# min 64, size in KB > #vacuum_mem = 8192 # min 1024, size in KB > > # - Free Space Map - > > max_fsm_pages = 300 # min max_fsm_relations*16, 6 bytes each > max_fsm_relations = 500 # min 100, ~50 bytes each > > > #--- > # WRITE AHEAD LOG > #--- > > # - Settings - > > #fsync = true # turns forced synchronization on or off > #wal_sync_method = fsync# the default varies across platforms: > # fsync, fdatasync, open_sync, or > open_datasync > wal_buffers = 32# min 4, 8KB each > > # - Checkpoints - > > checkpoint_segments = 50# in logfile segments, min 1, 16MB each > checkpoint_timeout = 1800 # range 30-3600, in seconds > > > # - Planner Cost Constants - > > effective_cache_size = 262144 # typically 8KB each > #effective_cache_size = 625000 # typically 8KB each > random_page_cost = 2# 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) > > > Patrick Hatcher > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564 Custom programming, 24x7 support, managed services, and hosting Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG Reliable replication, Mammoth Replicator - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Delete query takes exorbitant amount of time
On Mon, 2005-03-28 at 09:37 -0700, Karim A Nassar wrote: > On Mon, 28 Mar 2005, Stephan Szabo wrote: > > > On Mon, 28 Mar 2005, Simon Riggs wrote: > > > > run the EXPLAIN after doing > > > > SET enable_seqscan = off > > ... > > > I think you have to prepare with enable_seqscan=off, because it > > effects how the query is planned and prepared. > > orfs=# SET enable_seqscan = off; > SET > orfs=# PREPARE test2(int) AS SELECT 1 from measurement where > orfs-# id_int_sensor_meas_type = $1 FOR UPDATE; > PREPARE > orfs=# EXPLAIN ANALYZE EXECUTE TEST2(1); -- non-existent > > QUERY PLAN > - > Index Scan using measurement__id_int_sensor_meas_type_idx on measurement > (cost=0.00..883881.49 rows=509478 width=6) > (actual time=29.207..29.207 rows=0 loops=1) >Index Cond: (id_int_sensor_meas_type = $1) > Total runtime: 29.277 ms > (3 rows) > > orfs=# EXPLAIN ANALYZE EXECUTE TEST2(197); -- existing value > > QUERY PLAN > - > Index Scan using measurement__id_int_sensor_meas_type_idx on measurement > (cost=0.00..883881.49 rows=509478 width=6) > (actual time=12.903..37478.167 rows=509478 loops=1) >Index Cond: (id_int_sensor_meas_type = $1) > Total runtime: 38113.338 ms > (3 rows) > "That process starts upon the supposition that when you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth." - Sherlock Holmes Well, based upon the evidence so far, the Optimizer got it right: Normal SeqScan, value=1elapsed= 6.4s cost=164559 SeqScan, value=197 elapsed=28.1s cost=164559 SeqScan=off IndexScan, value=1 elapsed= 29ms cost=883881 IndexScan, value=197elapsed=38.1s cost=883881 With SeqScan=off the index is used, proving that it has been correctly defined for use in queries. The FK CASCADE delete onto measurement will only be triggered by the deletion of a real row, so the actual value will be the time taken. This is longer than a SeqScan, so the Optimizer is correct. My guess is that Measurement has a greatly non-uniform distribution of values and that 197 is one of the main values. Other values exist in the lookup table, but are very infrequently occurring in the larger table. Karim, Please do: select id_int_sensor_meas_type, count(*) from measurement group by id_int_sensor_meas_type order by count(*) desc; Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Query Optimizer Failure / Possible Bug
Hannes, > The query and the corresponding EXPLAIN is at > > http://hannes.imos.net/query.txt The problem is that you're using a complex corellated sub-select in the SELECT clause: SELECT d.delivery_id, da.article_no, da.amount, ( SELECT COUNT(*) FROM serials s INNER JOIN rma_ticket_serials rts ON ( s.serial_id = rts.serial_id ) WHERE s.article_no = da.article_no AND s.delivery_id = d.delivery_id AND rts.replace = FALSE ) AS replaced_serials This means that the planner pretty much has to iterate over the subquery, running it once for each row in the result set. If you want the optimizer to use a JOIN structure instead, put the subselect in the FROM clause. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Alex wrote: > > Without starting too much controvesy I hope, I would seriously > > recommend you evaluate the AMCC Escalade 9500S SATA controller. ... > At the risk of shaming myself with another 'me too' post, I'd like to > say that my experiences back this up 100%. The Escalade controllers are > excellent and the Raptor drives are fast and reliable (so far). ... I assume AMCC == 3ware now? Has anyone verified that fsync is safe on these controllers? Ie, that they aren't caching writes and "lying" about the write completing like IDE drives often do by default? -- greg ---(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] which dual-CPU hardware/OS is fastest for PostgreSQL?
Greg Stark wrote: "Merlin Moncure" <[EMAIL PROTECTED]> writes: Alex wrote: Without starting too much controvesy I hope, I would seriously recommend you evaluate the AMCC Escalade 9500S SATA controller. . At the risk of shaming myself with another 'me too' post, I'd like to say that my experiences back this up 100%. The Escalade controllers are excellent and the Raptor drives are fast and reliable (so far). . I assume AMCC == 3ware now? Has anyone verified that fsync is safe on these controllers? Ie, that they aren't caching writes and "lying" about the write completing like IDE drives oft For those who speak highly of the Escalade controllers and/Raptor SATA drives, how is the database being utilized, OLTP or primarily read access? This is good information I am learning, but I also see the need to understand the context of how the hardware is being used. Steve Poe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
> I assume AMCC == 3ware now? > > Has anyone verified that fsync is safe on these controllers? Ie, that they > aren't caching writes and "lying" about the write completing like IDE > drives often do by default? The higher end AMCC/3ware controllers actually warn you about using write-cache. You have to explicitly turn it on within the controller bios. They also have optional battery backed cache. Sincerely, Joshua D. Drake > -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564 Custom programming, 24x7 support, managed services, and hosting Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG Reliable replication, Mammoth Replicator - http://www.commandprompt.com/ ---(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
Re: [PERFORM] Preventing query from hogging server
On Thu, Mar 24, 2005 at 01:07:39PM -0600, Matthew Nuzum wrote: > I've tried `nice psql` in the past and I don't think that had much impact, > but I haven't tried it on this query. On linux, nice will only help if the query is CPU-bound. On FreeBSD, nice affects I/O scheduling, as well as CPU, so it's a more effective means of limiting the impact of large queries. I don't know how other OS's handle this. -- 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 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
Re: [PERFORM] Delete query takes exorbitant amount of time
On Mon, 2005-03-28 at 13:03 -0700, Karim A Nassar wrote: > > Well, based upon the evidence so far, the Optimizer got it right: > > Agreed. So, this means that the answer to my original question is "that > delete gonna take a long time"? > > Seems that there is still something wrong. From what I can tell from > everyones questions, the FK constraint on measurement is causing multiple > seq scans for each value deleted from int_sensor_meas_type. However, when > deleting a single value, the FK check should use the index, so my ~190 > deletes *should* be fast, no? No. > > IndexScan, value=1 elapsed= 29ms cost=883881 > > 190 * 29ms is much less than 40 minutes. What am I missing here? It all depends upon your data. There are *no* values in *your* table that take 29ms to delete... > > Karim, > > Please do: > > > > select id_int_sensor_meas_type, count(*) > > from measurement > > group by id_int_sensor_meas_type > > order by count(*) desc; > > id_int_sensor_meas_type | count > -+ > 31 | 509478 > 30 | 509478 > 206 | 509478 > 205 | 509478 > 204 | 509478 > 40 | 509478 > 39 | 509478 > 197 | 509478 > 35 | 509478 > 34 | 509478 > 33 | 509478 > 32 | 509478 > 41 | 509477 > > This sample dataset has 13 measurements from a weather station over 3 > years, hence the even distribution. Each value has 1/13th of the table, which is too many rows per value to make an IndexScan an efficient way of deleting rows from the table. Thats it. If you have more values when measurement is bigger, the delete will eventually switch plans (if you reconnect) and use the index. But not yet. There's a few ways to (re)design around it, but the distribution of your data is not *currently* conducive to the using an index. Best Regards, Simon Riggs ---(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
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > I assume AMCC == 3ware now? > > > > Has anyone verified that fsync is safe on these controllers? Ie, that they > > aren't caching writes and "lying" about the write completing like IDE > > drives often do by default? > > The higher end AMCC/3ware controllers actually warn you about using > write-cache. You have to explicitly turn it on within the controller > bios. Well that's a good sign. But if they're using SATA drives my concern is that the drives themselves may be doing some caching on their own. Has anyone verified that the controllers are disabling the drive cache or issuing flushes or doing something else to be sure to block the drives from caching writes? -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] How to improve db performance with $7K?
Have you already considered application/database tuning? Adding indexes? shared_buffers large enough? etc. Your database doesn't seem that large for the hardware you've already got. I'd hate to spend $7k and end up back in the same boat. :) On Sat, 2005-03-26 at 13:04 +, Steve Poe wrote: > >Steve, can we clarify that you are not currently having any performance > >issues, you're just worried about failure? Recommendations should be based > >on whether improving applicaiton speed is a requirement ... > > Josh, > > The priorities are: 1)improve safety/failure-prevention, 2) improve > performance. > > The owner of the company wants greater performance (and, I concure to > certain degree), but the owner's vote is only 1/7 of the managment team. > And, the rest of the management team is not as focused on performance. > They all agree in safety/failure-prevention. > > Steve > > > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Delete query takes exorbitant amount of time
On Fri, 2005-03-25 at 09:38 -0800, Josh Berkus wrote: > > I guess I was wondering if there is other general tuning advice for such > > large table indexes such as increasing statistics, etc. > > If you're going with the drop/load/recreate option, then I'd suggest > increasing work_mem for the duration. Hmmm ... or maintenance_work_mem? > What gets used for FK checks? Simon? > In 8.0, maintenance_work_mem is used for index creation, vacuum and initial check of FK checks at time of creation. Everything else uses work_mem as the limit. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] JDBC best practice
I'm using a Postgres table as the data source for a JTable
in a Java app. As a first approximation, I'm implementing
AbstractTableModel.getValueAt() like so:
public Object getValueAt(int row, int col)
{
try
{
rs_.absolute(row + 1);
return rs_.getObject(col + 1);
}
catch (Exception e)
{
...
}
return null;
}
Where rs_ is a RecordSet object. What I'm wondering is
whether it's better to call absolute() or relative() or
next()/previous(). If absolute() is the slowest call,
then I can cache the last row fetched and move relative
to that.
My suspicion is that next()/previous() is much faster
than absolute() when the record to be fetched is very near
the last record fetched. I haven't actually tried it, but
I'd like some insight if others can already answer this
question based on knowledge of the server side and/or the
JDBC driver.
__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East, Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
Greg Stark wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > > > I assume AMCC == 3ware now? > > > > > > Has anyone verified that fsync is safe on these controllers? Ie, that they > > > aren't caching writes and "lying" about the write completing like IDE > > > drives often do by default? > > > > The higher end AMCC/3ware controllers actually warn you about using > > write-cache. You have to explicitly turn it on within the controller > > bios. > > Well that's a good sign. > > But if they're using SATA drives my concern is that the drives themselves may > be doing some caching on their own. Has anyone verified that the controllers > are disabling the drive cache or issuing flushes or doing something else to be > sure to block the drives from caching writes? I asked 3ware this at the Linuxworld Boston show and they said their controller keeps the information in cache until they are sure it is on the platters and not just in the disk cache, but that is far from a 100% reliable report. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
Anyone using power5 platform? something like an ibm eserver p5 520 running red hat linux. (http://www-1.ibm.com/servers/eserver/pseries/hardware/entry/520.html)? klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
Bruce Momjian writes: > I asked 3ware this at the Linuxworld Boston show and they said their > controller keeps the information in cache until they are sure it is on > the platters and not just in the disk cache, but that is far from a 100% > reliable report. Hm. Well, keeping it in cache is one thing. But what it needs to do is not confirm the write to the host OS. Unless they want to sell their battery backed unit which is an expensive add-on... -- greg ---(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] JDBC best practice
On Mon, 28 Mar 2005, Dave Held wrote: > I'm using a Postgres table as the data source for a JTable in a Java > app. Where rs_ is a RecordSet object. What I'm wondering is whether > it's better to call absolute() or relative() or next()/previous(). If > absolute() is the slowest call, then I can cache the last row fetched > and move relative to that. > > My suspicion is that next()/previous() is much faster than absolute() > when the record to be fetched is very near the last record fetched. I > haven't actually tried it, but I'd like some insight if others can > already answer this question based on knowledge of the server side > and/or the JDBC driver. There are two types of ResultSets that can be returned by the JDBC driver. One is backed by a cursor and can only be used for TYPE_FORWARD_ONLY ResultSets so it is not really applicable to you. The other method retrieves all results at once and stashes them in a Vector. This makes next, absolute, and relative positioning all equal cost. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] How to improve db performance with $7K?
Cott Lang wrote: Have you already considered application/database tuning? Adding indexes? shared_buffers large enough? etc. Your database doesn't seem that large for the hardware you've already got. I'd hate to spend $7k and end up back in the same boat. :) Cott, I agree with you. Unfortunately, I am not the developer of the application. The vendor uses ProIV which connects via ODBC. The vendor could certain do some tuning and create more indexes where applicable. I am encouraging the vendor to take a more active role and we work together on this. With hardware tuning, I am sure we can do better than 35Mb per sec. Also moving the top 3 or 5 tables and indexes to their own slice of a RAID10 and moving pg_xlog to its own drive will help too. Since you asked about tuned settings, here's what we're using: kernel.shmmax = 1073741824 shared_buffers = 1 sort_mem = 8192 vacuum_mem = 65536 effective_cache_size = 65536 Steve Poe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
