Re: [PERFORM] Migrating to Postgresql and new hardware

Lars wrote: Below is a quote from the Pliant datasheet: "No Write Cache: Pliant EFDs deliver outstanding write performance without any dependence on write cache and thus does not use battery/supercap." I liked the article The Register wrote about them, with the headline "Pliant's SSDs are a

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/28/2011 11:44 AM, Scott Carey wrote: On 1/27/11 4:11 PM, "Alan Hodgson" wrote: On January 27,

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/28/2011 11:14 AM, Stephen Frost wrote: It got lost from the original post but my database (9.0.0) is currently on my Windows XP 64-bit workstation in my office on a 16 drive Seagate 15k.5 RAID5, no comments needed, I know, I'm moving it :-). I'm moving it to my server whic

Re: [PERFORM] postgres 9 query performance

On Friday, January 28, 2011 06:30:19 PM yazan suleiman wrote: > I am evaluating postgres 9 to migrate away from Oracle. The following > query runs too slow, also please find the explain plan: First: explain analyze SELECT DISTINCT EVENT.ID ,ORIGIN.ID AS ORIGINID ,EVENT.PREFERRED_ORIGI

Re: [PERFORM] postgres 9 query performance

On Fri, Jan 28, 2011 at 09:30:19AM -0800, yazan suleiman wrote: > I am evaluating postgres 9 to migrate away from Oracle. The following query > runs too slow, also please find the explain plan: > > > explain analyze select DISTINCT

Re: [PERFORM] postgres 9 query performance

On Fri, Jan 28, 2011 at 10:30 AM, yazan suleiman wrote: > I am evaluating postgres 9 to migrate away from Oracle.  The following query > runs too slow, also please find the explain plan: > > explain analyze select DISTINCT EVENT.ID,

[PERFORM] postgres 9 query performance

I am evaluating postgres 9 to migrate away from Oracle. The following query runs too slow, also please find the explain plan: explain analyze select DISTINCT EVENT.ID, ORIGIN.ID AS ORIGINID,EVENT.PREFERRED_ORIGIN_ID AS PREFERRED_ORI

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On Fri, Jan 28, 2011 at 10:44 AM, Scott Carey wrote: > If you go this route, I suggest two equally sized RAID 10's on different > controllers fir index + data, with software raid-0 on top of that.  RAID 10 > will max out a controller after 6 to 10 drives, usually.  Using the OS RAID > 0 to aggrega

Re: FW: [PERFORM] Queries becoming slow under heavy load

Shaun Thomas wrote: On 01/27/2011 11:12 PM, Anne Rosset wrote: Thanks for your response. We are over NFS for our storage ... NFS? I'm not sure you know this, but NFS has major locking issues that would make it a terrible candidate for hosting a database. That depends on the implem

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/28/11 9:28 AM, "Stephen Frost" wrote: >* Scott Marlowe (scott.marl...@gmail.com) wrote: >> There's nothing wrong with whole table updates as part of an import >> process, you just have to know to "clean up" after you're done, and >> regular vacuum can't fix this issue, only vacuum full or

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/28/11 9:00 AM, "Scott Marlowe" wrote: >On Fri, Jan 28, 2011 at 9:39 AM, Robert Schnabel >wrote: >> I can't do outside the database. So yes, once the upload is done I run >> queries that update every row for certain columns, not every column. >>After >> I'm done with a table I run a VACUU

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/27/11 4:11 PM, "Alan Hodgson" mailto:ahodg...@simkin.ca>> wrote: On January 27, 2011, Robert Schnabel mailto:schnab...@missouri.edu>> wrote: > So my questions are 1) am I'm crazy for doing this, 2) would you change > anything and 3) is it acceptable to put the xlog & wal (and perhaps t

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

* Scott Marlowe (scott.marl...@gmail.com) wrote: > There's nothing wrong with whole table updates as part of an import > process, you just have to know to "clean up" after you're done, and > regular vacuum can't fix this issue, only vacuum full or reindex or > cluster. Just to share my experiences

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

Robert, * Robert Schnabel (schnab...@missouri.edu) wrote: >Depends on what you mean by that.  The tables that I'm concerned with look >something like bigint x2, char var x13, int x24, real x8, smallint x4 by >about 65M rows, each.  I only do the updates on one table at a time.  The >

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/28/2011 11:00 AM, Scott Marlowe wrote: On Fri, Jan 28, 2011 at 9:39 AM, Robert Schnabel wrote: I can't do outside the database. So yes, once the upload is done I run queries that update every row for certain columns, not every column. After I'm done with a table I run a VACUUM ANALYZE.

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On Fri, Jan 28, 2011 at 9:39 AM, Robert Schnabel wrote: > I can't do outside the database.  So yes, once the upload is done I run > queries that update every row for certain columns, not every column.  After > I'm done with a table I run a VACUUM ANALYZE.  I'm really not worried about > what my ta

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/28/2011 7:14 AM, Stephen Frost wrote: Robert, * Robert Schnabel (schnab...@missouri.edu) wrote: Once the bulk data is inserted into the tables I generally do some updates on columns to set values which characterize the data. Please

Re: FW: [PERFORM] Queries becoming slow under heavy load

On 01/27/2011 11:12 PM, Anne Rosset wrote: > Thanks for your response. > We are over NFS for our storage ... NFS? I'm not sure you know this, but NFS has major locking issues that would make it a terrible candidate for hosting a database. > and it's not until around the 221 second mark that we s

Re: [PERFORM] High load,

On 27/01/2011 11:31, Michael Kohl wrote: Hi all, we are running a fairly big Ruby on Rails application on Postgres 8.4. Our traffic grew quite a bit lately, and since then we are facing DB performance issues. System load occasionally explodes (around 170 yesterday on a 16 core system), which see

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

Putting the WAL on a second controller does help, if you're write-heavy. I tried separating indexes and data once on one server and didn't really notice that it helped much. Managing the space was problematic. I would suggest putting those together on a single RAID-10 of all the 300GB drives

Re: [PERFORM] High load,

Michael Kohl wrote: We are already doing the logging part, we are just a bit behind on the "explain analyze" part of things. One day soon... There is, of course, the auto_explain module which will do that for you. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

Robert, * Robert Schnabel (schnab...@missouri.edu) wrote: > Once the bulk data is inserted into the tables I generally > do some updates on columns to set values which characterize the > data. Please tell me you're not running actual full-table UPDATE statements... You would be *much* better of

Re: [PERFORM] High load,

On Thu, Jan 27, 2011 at 6:05 PM, Scott Marlowe wrote: > A good method to start is to log long running queries and then explain > analyze just them. We are already doing the logging part, we are just a bit behind on the "explain analyze" part of things. One day soon... Thanks, Michael -- Sent v