Re: [PERFORM] TEXT field and Postgresql Perfomance

2005-01-07 Thread Alex Turner
, increasing insert time substantialy. Does it ever pay to use text and not CLOB unless your text is going to be short, in which case why not just varchar, leading to the thought that the text datatype is just bad? Alex Turner NetEconomist On Fri, 7 Jan 2005 22:03:23 -0600, Bruno Wolff III [EMAIL

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-10 Thread Alex Turner
, and over $100k/year for an Oracle DBA, and you need 60x60x24x7x365 uptime with recoverability, realtime replication and clustering - Oracle might be your best bet, otherwise - pick Postgresql ;) Alex Turner NetEconoimst On Mon, 10 Jan 2005 07:30:12 +, Gary Doades [EMAIL PROTECTED] wrote

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-10 Thread Alex Turner
You sir are correct! You can't use perl in MS-SQL or Oracle ;). Alex Turner NetEconomist On Mon, 10 Jan 2005 11:42:00 -0600, Frank Wiles [EMAIL PROTECTED] wrote: On Mon, 10 Jan 2005 18:33:07 +0100 Yann Michel [EMAIL PROTECTED] wrote: Hi, On Mon, Jan 10, 2005 at 11:07:55AM -0500, Alex

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-11 Thread Alex Turner
Connect to an external data system using a socket and propagate data changes using a trigger... I've had to do this, and it sucks to be stuck in Oracle! Alex Turner NetEconomist On Mon, 10 Jan 2005 17:29:52 -0600, Jim C. Nasby [EMAIL PROTECTED] wrote: On Mon, Jan 10, 2005 at 12:46:01PM -0500

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

2005-01-11 Thread Alex Turner
nuts, they have an 8U with 40xSATA backplane. Alex Turner NetEconomist On Tue, 11 Jan 2005 08:33:09 -0500, Merlin Moncure [EMAIL PROTECTED] wrote: Subject: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL? I'm sorry if there's a URL out there answering this, but I couldn't

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

2005-01-12 Thread Alex Turner
to talk about seek time. anything below 90% is not going to keep even the best disc hardware saturated. I know that our dataset is 99% cached, and therefore better CPUs/better RAM has a huge impact on overall performance. Alex Turner NetEconomist On 11 Jan 2005 10:39:01 -0500, Greg Stark [EMAIL

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

2005-01-12 Thread Alex Turner
is acheivable. Cache hit ratio is just one small indication of performance. Relating to that - How to extract this kind of information from postgresql? Is there a way to get the cache hti ratio, or determine the worst 10 queries in a database? Alex Turner NetEconomist On 12 Jan 2005 12:25:23 -0500

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-13 Thread Alex Turner
. Alex Turner NetEconomist -- Remember, what most consider 'convential wisdom' is neither wise nor the convention. Don't speculate, educate. On Wed, 12 Jan 2005 22:51:24 -0800, Joe Conway [EMAIL PROTECTED] wrote: Greg Sabino Mullane wrote: Don't forget your support contract cost, as well

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-13 Thread Alex Turner
poorly. Alex Turner NetEconomist On Thu, 13 Jan 2005 06:56:52 -0800, Joe Conway [EMAIL PROTECTED] wrote: Alex Turner wrote: I'm not advocating that people switch to Oracle at all, It's still much more expensive than Postgresql, and for most small and medium applications Postgresql is much

Re: [PERFORM] Best filesystem for PostgreSQL Database Cluster under Linux

2005-01-14 Thread Alex Turner
write on a Bonnie++ benchmark. This is virtualy the slowest system in our datacenter, but has a modern controler and 10k disks, whilst our PATA systems manage much better throughput. (Yes I know that MB/sec is not the only speed measure, it also does badly on IO/sec). Alex Turner NetEconomist

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

2005-01-14 Thread Alex Turner
://plexq.com/~aturner/3ware.pdf for their 4 way, 8 way and 12 way RAID benchmarks including RAID 0, RAID 5 and RAID 10. If others have similar data, I would be very interested to see how it stacks up against other RAID controllers. Alex Turner NetEconomist On Fri, 14 Jan 2005 16:52:42 +1100

Re: [PERFORM] Disk configuration

2005-01-19 Thread Alex Turner
queries returning sub second. Hope this helps at least a little Alex Turner NetEconomist On Wed, 19 Jan 2005 09:03:44 +1100, Benjamin Wragg [EMAIL PROTECTED] wrote: I just wanted to bounce off the list the best way to configure disks for a postgresql server. My gut feeling is as follows

Re: [PERFORM] Disk configuration

2005-01-20 Thread Alex Turner
be the same I would imagine because you still have to write all data to all drives. Thats my best guess. Alex Turner NetEconomist On Thu, 20 Jan 2005 11:55:37 +1100, Benjamin Wragg [EMAIL PROTECTED] wrote: Thanks. That sorts out all my questions regarding disk configuration. One more regarding

Re: [PERFORM]

2005-01-20 Thread Alex Turner
I am curious - I wasn't aware that postgresql supported partitioned tables, Could someone point me to the docs on this. Thanks, Alex Turner NetEconomist On Thu, 20 Jan 2005 09:26:03 -0500, Stephen Frost [EMAIL PROTECTED] wrote: * Matt Casters ([EMAIL PROTECTED]) wrote: I have the go ahead

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Alex Turner
are often very heavy, and the reason why one would want to use a cursor. Alex Turner NetEconomist On Thu, 20 Jan 2005 15:20:59 +, Richard Huxton dev@archonet.com wrote: Andrei Bintintan wrote: If you're using this to provide pages of results, could you use a cursor? What do you mean

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Alex Turner
, because it's hard to make it work well in Java (most JMS implementations suck, look at MQueue or a custom queue impl, forget XML it's too slow to serialize and deserialize requests). Alex Turner NetEconomist On Thu, 20 Jan 2005 11:13:25 -0500, Stephen Frost [EMAIL PROTECTED] wrote: * [EMAIL PROTECTED

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Alex Turner
How do you create a temporary view that has only a small subset of the data from the DB init? (Links to docs are fine - I can read ;). My query isn't all that complex, and my number of records might be from 10 to 2k depending on how I implement it. Alex Turner NetEconomist On Thu, 20 Jan 2005

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-26 Thread Alex Turner
. Alex Turner NetEconomist On Wed, 26 Jan 2005 12:11:49 +0200, Andrei Bintintan [EMAIL PROTECTED] wrote: The problems still stays open. The thing is that I have about 20 - 30 clients that are using that SQL query where the offset and limit are involved. So, I cannot create a temp table, because

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-26 Thread Alex Turner
this will be the impression that will stick with them. I guess we could experiment and see how much extra time creating a cache table will take... Alex Turner NetEconomist On Wed, 26 Jan 2005 13:58:18 +0100, PFC [EMAIL PROTECTED] wrote: Supposing your searches display results which are rows coming

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-28 Thread Alex Turner
per second. We don't need more RAM with this config. The disks are fast enough. 2500 transaction/second is pretty damn fast. Alex Turner On Fri, 28 Jan 2005 10:31:38 -0500, Andrew Sullivan [EMAIL PROTECTED] wrote: On Thu, Jan 20, 2005 at 10:40:02PM -0200, Bruno Almeida do Lago wrote: I

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-28 Thread Alex Turner
On Fri, 28 Jan 2005 11:54:57 -0500, Christopher Weimann [EMAIL PROTECTED] wrote: On 01/28/2005-10:59AM, Alex Turner wrote: At this point I will interject a couple of benchmark numbers based on a new system we just configured as food for thought. System A (old system): Compaq Proliant

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-31 Thread Alex Turner
fsync on. Alex Turner NetEconomist On Fri, 28 Jan 2005 11:19:44 -0500, Merlin Moncure [EMAIL PROTECTED] wrote: With the right configuration you can get very serious throughput. The new system is processing over 2500 insert transactions per second. We don't need more RAM with this config

Re: [PERFORM] High end server and storage for a PostgreSQL OLTP system

2005-02-01 Thread Alex Turner
be interested in at least some bonnie++ benchmarks, and perhaps other if people would like. Alex Turner NetEconomist On Tue, 1 Feb 2005 05:27:27 -0600, Jim C. Nasby [EMAIL PROTECTED] wrote: On Tue, Feb 01, 2005 at 07:35:35AM +0100, Cosimo Streppone wrote: You might look at Opteron's, which

Re: [PERFORM] High end server and storage for a PostgreSQL OLTP system

2005-02-01 Thread Alex Turner
None - but I'll definately take a look.. Alex Turner NetEconomist On Tue, 01 Feb 2005 22:11:30 +0100, Cosimo Streppone [EMAIL PROTECTED] wrote: Alex Turner wrote: To be honest I've used compaq, dell and LSI SCSI RAID controllers and got pretty pathetic benchmarks from all of them. I

Re: [PERFORM] multi billion row tables: possible or insane?

2005-03-04 Thread Alex Turner
Not true - with fsync on I get nearly 500 tx/s, with it off I'm as high as 1600/sec with dual opteron and 14xSATA drives and 4GB RAM on a 3ware Escalade. Database has 3 million rows. As long as queries use indexes, multi billion row shouldn't be too bad. Full table scan will suck though. Alex

Re: [PERFORM] Postgres on RAID5

2005-03-11 Thread Alex Turner
to recommend against a 14 drive RAID 5! This is statisticaly as likely to fail as a 7 drive RAID 0 (not counting the spare, but rebuiling a spare is very hard on existing drives). Alex Turner netEconomist On Fri, 11 Mar 2005 16:13:05 -0500, Arshavir Grigorian [EMAIL PROTECTED] wrote: Hi, I have

Re: [PERFORM] Postgres on RAID5

2005-03-14 Thread Alex Turner
on a fourth. Unsurprisingly this looks alot like the Oracle recommended minimum config. Also a note for interest is that this is _software_ raid... Alex Turner netEconomist On 13 Mar 2005 23:36:13 -0500, Greg Stark [EMAIL PROTECTED] wrote: Arshavir Grigorian [EMAIL PROTECTED] writes: Hi, I

Re: [PERFORM] Postgres on RAID5

2005-03-14 Thread Alex Turner
performance is less than stellar for a 14 drive stripe, and CPU usage for writes is very high. Even so - this should be enough through put to get over 100 rows/sec assuming you have virtualy no stored procs (I have noticed that stored procs in plpgsql REALLY slow pg_sql down). Alex Turner netEconomist

Re: [PERFORM] Postgres on RAID5

2005-03-14 Thread Alex Turner
He doesn't have a RAID controller, it's software RAID... Alex Turner netEconomis On Mon, 14 Mar 2005 16:18:00 -0500, Merlin Moncure [EMAIL PROTECTED] wrote: Alex Turner wrote: 35 Trans/sec is pretty slow, particularly if they are only one row at a time. I typicaly get 200-400/sec on our

[PERFORM] Planner issue

2005-03-22 Thread Alex Turner
see anything pertaining to index selectivity. Thanks, Alex Turner netEconomist trendmls=# explain analyze select listnum from propmain where listprice=30 and listprice=22; QUERY PLAN

Re: [PERFORM] Planner issue

2005-03-22 Thread Alex Turner
= 20::numeric)) Total runtime: 30.193 ms (3 rows) trendmls=# On Tue, 22 Mar 2005 08:22:59 -0800, Joshua D. Drake [EMAIL PROTECTED] wrote: Alex Turner wrote: I get the following output from explain analyze on a certain subset of a large query I'm doing. Try increases

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-01 Thread Alex Turner
RAID 10, database is on RAID 10. Data is very spread out because database turnover time is very high, so our performance is about double this with a fresh DB. (the data half life is probably measurable in days or weeks). Alex Turner netEconomist On Apr 1, 2005 1:06 PM, Marc G. Fournier [EMAIL

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-01 Thread Alex Turner
Oh - this is with a seperate transaction per command. fsync is on. Alex Turner netEconomist On Apr 1, 2005 4:17 PM, Alex Turner [EMAIL PROTECTED] wrote: 1250/sec with record size average is 26 bytes 800/sec with record size average is 48 bytes. 250/sec with record size average is 618 bytes

Re: [PERFORM] How to improve db performance with $7K?

2005-04-04 Thread Alex Turner
would be greatly interested. Alex Turner netEconomist On Mar 29, 2005 8:17 AM, Dave Cramer [EMAIL PROTECTED] wrote: Yeah, 35Mb per sec is slow for a raid controller, the 3ware mirrored is about 50Mb/sec, and striped is about 100 Dave PFC wrote: With hardware tuning, I am sure we can do

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Alex Turner
Yup, Battery backed, cache enabled. 6 drive RAID 10, and 4 drive RAID 10, and 2xRAID 1. It's a 3ware 9500S-8MI - not bad for $450 plus BBU. Alex Turner netEconomist On Apr 1, 2005 6:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Alex Turner [EMAIL PROTECTED] writes: On Apr 1, 2005 4:17 PM, Alex

Re: [PERFORM] How to improve db performance with $7K?

2005-04-04 Thread Alex Turner
with SCSI. If anyone has a usefull link on that, it would be greatly appreciated. More drives will give more throughput/sec, but not necesarily more transactions/sec. For that you will need more RAM on the controler, and defaintely a BBU to keep your data safe. Alex Turner netEconomist On Apr 4, 2005

Re: [PERFORM] How to improve db performance with $7K?

2005-04-04 Thread Alex Turner
in some, SATA wins, or draws. I'm trying to find something more apples to apples. 10k to 10k. Alex Turner netEconomist On Apr 4, 2005 3:23 PM, Vivek Khera [EMAIL PROTECTED] wrote: On Apr 4, 2005, at 3:12 PM, Alex Turner wrote: Our system is mostly read during the day, but we do a full

Re: [PERFORM] How to improve db performance with $7K?

2005-04-06 Thread Alex Turner
test database to run, I would be happy to run it on our hardware here. Alex Turner On Apr 6, 2005 3:30 AM, William Yu [EMAIL PROTECTED] wrote: Alex Turner wrote: I'm no drive expert, but it seems to me that our write performance is excellent. I think what most are concerned about is OLTP where

Re: RE : RE: [PERFORM] Postgresql vs SQLserver for this application ?

2005-04-06 Thread Alex Turner
I think everyone was scared off by the 5000 inserts per second number. I've never seen even Oracle do this on a top end Dell system with copious SCSI attached storage. Alex Turner netEconomist On Apr 6, 2005 3:17 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Unfortunately. But we

Re: RE : RE: [PERFORM] Postgresql vs SQLserver for thisapplication ?

2005-04-06 Thread Alex Turner
think the underlying code to do this has to be not-too-complex. I'd say we're there. -Original Message- From: Alex Turner [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 06, 2005 11:38 AM To: [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org; Mohan, Ross Subject: Re

Re: [PERFORM] Réf

2005-04-06 Thread Alex Turner
I think his point was that 9 * 4 != 2400 Alex Turner netEconomist On Apr 6, 2005 2:23 PM, Rod Taylor [EMAIL PROTECTED] wrote: On Wed, 2005-04-06 at 19:42 +0200, Steinar H. Gunderson wrote: On Wed, Apr 06, 2005 at 01:18:29PM -0400, Rod Taylor wrote: Yeah, I think that can be done provided

Re: [PERFORM] How to improve db performance with $7K?

2005-04-06 Thread Alex Turner
such tests, we'd all be delighted with to see the results so we have another option for building servers. Alex Turner wrote: It's hardly the same money, the drives are twice as much. It's all about the controller baby with any kind of dive. A bad SCSI controller will give sucky performance

Re: [PERFORM] How to improve db performance with $7K?

2005-04-06 Thread Alex Turner
have to share in SCSI. A SATA controller typicaly can do 3Gb/sec (384MB/sec) per drive, but SCSI can only do 320MB/sec across the entire array. What am I missing here? Alex Turner netEconomist On Apr 6, 2005 5:41 PM, Jim C. Nasby [EMAIL PROTECTED] wrote: Sorry if I'm pointing out the obvious here

Re: [PERFORM] How to improve db performance with $7K?

2005-04-06 Thread Alex Turner
Ok - so I found this fairly good online review of various SATA cards out there, with 3ware not doing too hot on RAID 5, but ok on RAID 10. http://www.tweakers.net/reviews/557/ Very interesting stuff. Alex Turner netEconomist On Apr 6, 2005 7:32 PM, Alex Turner [EMAIL PROTECTED] wrote: I guess

Re: [PERFORM] How to improve db performance with $7K?

2005-04-06 Thread Alex Turner
Ok - I take it back - I'm reading through this now, and realising that the reviews are pretty clueless in several places... On Apr 6, 2005 8:12 PM, Alex Turner [EMAIL PROTECTED] wrote: Ok - so I found this fairly good online review of various SATA cards out there, with 3ware not doing too hot

Re: [PERFORM] How to improve db performance with $7K?

2005-04-06 Thread Alex Turner
on the controller and to the drive. *shrug* This of course is all supposed to go away with SATA II which as NCQ, Native Command Queueing. Of course the 3ware controllers don't support SATA II, but a few other do, and I'm sure 3ware will come out with a controller that does. Alex Turner netEconomist On 06 Apr

Re: [PERFORM] How to improve db performance with $7K?

2005-04-07 Thread Alex Turner
, thereby generating a cost increase (at least thats what the manufactures tell us). I know if you ever held a 15k drive in your hand, you can notice a considerable weight difference between it and a 7200RPM IDE drive. Alex Turner netEconomist On Apr 7, 2005 11:37 AM, [EMAIL PROTECTED] [EMAIL PROTECTED

Re: [PERFORM] Intel SRCS16 SATA raid?

2005-04-14 Thread Alex Turner
I have read a large chunk of this, and I would highly recommend it to anyone who has been participating in the drive discussions. It is most informative!! Alex Turner netEconomist On 4/14/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Greg, I posted this link under a different thread

Re: [PERFORM] Intel SRCS16 SATA raid?

2005-04-14 Thread Alex Turner
=40devID_2=259devID_3=267devID_4=261devID_5=248devCnt=6 It does illustrate some of the weaknesses of SATA drives, but all in all the Raptor drives put on a good show. Alex Turner netEconomist On 4/14/05, Alex Turner [EMAIL PROTECTED] wrote: I have read a large chunk of this, and I would highly

Re: [PERFORM] Intel SRCS16 SATA raid?

2005-04-14 Thread Alex Turner
Just to clarify these are tests from http://www.storagereview.com, not my own. I guess they couldn't get number for those parts. I think everyone understands that a 0ms seek time impossible, and indicates a missing data point. Thanks, Alex Turner netEconomist On 4/14/05, Dave Held [EMAIL

Re: [PERFORM] Intel SRCS16 SATA raid?

2005-04-14 Thread Alex Turner
as NCQ on the drive). Alex Turner netEconomist On 4/14/05, Dave Held [EMAIL PROTECTED] wrote: -Original Message- From: Alex Turner [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 12:14 PM To: [EMAIL PROTECTED] Cc: Greg Stark; pgsql-performance@postgresql.org; [EMAIL

Re: [PERFORM] How to improve db performance with $7K?

2005-04-14 Thread Alex Turner
linear increment. Alex Turner netEconomist On 4/14/05, Kevin Brown [EMAIL PROTECTED] wrote: Tom Lane wrote: Kevin Brown [EMAIL PROTECTED] writes: I really don't see how this is any different between a system that has tagged queueing to the disks and one that doesn't. The only

Re: [PERFORM] Intel SRCS16 SATA raid?

2005-04-15 Thread Alex Turner
. The 3ware trounces the Areca in all IO/sec test. Alex Turner netEconomist On 4/15/05, Marinos Yannikos [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: Well I have never even heard of it. 3ware is the defacto authority of reasonable SATA RAID. no! 3ware was rather early in this business

Re: [PERFORM] Intel SRCS16 SATA raid?

2005-04-15 Thread Alex Turner
. Our biggest hit is reads, so we can buy 3xSATA machines and load balance. It's all about the application, and buying what is appropriate. I don't buy a Corvette if all I need is a malibu. Alex Turner netEconomist On 4/15/05, Dave Held [EMAIL PROTECTED] wrote: -Original Message- From

Re: [PERFORM] Intel SRCS16 SATA raid?

2005-04-15 Thread Alex Turner
I stand corrected! Maybe I should re-evaluate our own config! Alex T (The dell PERC controllers do pretty much suck on linux) On 4/15/05, Vivek Khera [EMAIL PROTECTED] wrote: On Apr 15, 2005, at 11:01 AM, Alex Turner wrote: You can't fit a 15k RPM SCSI solution into $7K ;) Some of us

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
having to factor in the cost of a bigger chassis to hold more drives, which can be big bucks. Alex Turner netEconomist On 18 Apr 2005 10:59:05 -0400, Greg Stark [EMAIL PROTECTED] wrote: William Yu [EMAIL PROTECTED] writes: Using the above prices for a fixed budget for RAID-10, you could get

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
multiple tablespaces on seperate paritions. My assertion therefore is that simply adding more drives to an already competent* configuration is about as likely to increase your database effectiveness as swiss cheese is to make your car run faster. Alex Turner netEconomist *Assertion here

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
). Alex Turner netEconomist On 4/18/05, John A Meinel [EMAIL PROTECTED] wrote: Alex Turner wrote: [snip] Adding drives will not let you get lower response times than the average seek time on your drives*. But it will let you reach that response time more often. [snip] I

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
I think the add more disks thing is really from the point of view that one disk isn't enough ever. You should really have at least four drives configured into two RAID 1s. Most DBAs will know this, but most average Joes won't. Alex Turner netEconomist On 4/18/05, Steve Poe [EMAIL PROTECTED

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
only need to read from one disk. So my assertion that adding more drives doesn't help is pretty wrong... particularly with OLTP because it's always dealing with blocks that are smaller that the stripe size. Alex Turner netEconomist On 4/18/05, Jacques Caron [EMAIL PROTECTED] wrote: Hi, At 18:56

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
of alot since subscribing. Alex Turner netEconomist On 4/18/05, Alex Turner [EMAIL PROTECTED] wrote: Ok - well - I am partially wrong... If you're stripe size is 64Kb, and you are reading 256k worth of data, it will be spread across four drives, so you will need to read from four devices to get

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
Mistype.. I meant 0+1 in the second instance :( On 4/18/05, Joshua D. Drake [EMAIL PROTECTED] wrote: Alex Turner wrote: Not true - the recommended RAID level is RAID 10, not RAID 0+1 (at least I would never recommend 1+0 for anything). Uhmm I was under the impression that 1+0 was RAID 10

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
On 4/18/05, Jacques Caron [EMAIL PROTECTED] wrote: Hi, At 20:21 18/04/2005, Alex Turner wrote: So I wonder if one could take this stripe size thing further and say that a larger stripe size is more likely to result in requests getting served parallized across disks which would lead

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alex Turner
Does it really matter at which end of the cable the queueing is done (Assuming both ends know as much about drive geometry etc..)? Alex Turner netEconomist On 4/18/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Kevin Brown wrote: Greg Stark wrote: I think you're being misled

Re: [PERFORM] How to improve db performance with $7K?

2005-04-20 Thread Alex Turner
I wonder if thats something to think about adding to Postgresql? A setting for multiblock read count like Oracle (Although having said that I believe that Oracle natively caches pages much more aggressively that postgresql, which allows the OS to do the file caching). Alex Turner netEconomist

Re: [PERFORM] How to improve db performance with $7K?

2005-04-20 Thread Alex Turner
in an array, which would yield better performance to cost ratio. Therefore I would suggest it is something that should be investigated. After all, why implemented TCQ on each drive, if it can be handled more effeciently at the other end by the controller for less money?! Alex Turner netEconomist On 4/19

Re: [PERFORM] Index bloat problem?

2005-04-21 Thread Alex Turner
Is: REINDEX DATABASE blah supposed to rebuild all indices in the database, or must you specify each table individualy? (I'm asking because I just tried it and it only did system tables) Alex Turner netEconomist On 4/21/05, Josh Berkus josh@agliodbs.com wrote: Bill, What about if an out

Re: [PERFORM] Index bloat problem?

2005-04-21 Thread Alex Turner
forces the system to physically re-allocate all that data space, and now you have just 2499 entries, that use 625 blocks. I'm not sure that 'blocks' is the correct term in postgres, it's segments in Oracle, but the concept remains the same. Alex Turner netEconomist On 4/21/05, Bill Chandler [EMAIL

Re: [PERFORM] Partitioning / Clustering

2005-05-12 Thread Alex Turner
website I've managed will ever see. Why solve the complicated clustered sessions problem, when you don't really need to? Alex Turner netEconomist On 5/11/05, PFC [EMAIL PROTECTED] wrote: However, memcached (and for us, pg_memcached) is an excellent way to improve horizontal scalability

Re: [PERFORM] Partitioning / Clustering

2005-05-12 Thread Alex Turner
size RAID arrays on 10k discs with fsync on for small transactions. I'm sure that could easily be bettered with a few more dollars. Maybe my number are off, but somehow it doesn't seem like that many people need a highly complex session solution to me. Alex Turner netEconomist On 5/12/05, Alex

Re: [PERFORM] Select performance vs. mssql

2005-05-24 Thread Alex Turner
Until you start worrying about MVC - we have had problems with the MSSQL implementation of read consistency because of this 'feature'. Alex Turner NetEconomistOn 5/24/05, Bruno Wolff III [EMAIL PROTECTED] wrote: On Tue, May 24, 2005 at 08:36:36 -0700,mark durrant [EMAIL PROTECTED] wrote

Re: [PERFORM] Filesystem

2005-06-03 Thread Alex Turner
to default after that problem (that partition is not on a DB server though). Alex Turner netEconomistOn 6/3/05, Martin Fandel [EMAIL PROTECTED] wrote: Hi @ all,i have only a little question. Which filesystem is preferred forpostgresql? I'm plan to use xfs (before i used reiserfs). The reasonis

Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster

2005-06-18 Thread Alex Turner
and will read from independant halves, but gives worse redundancy. Alex Turner NetEconomistOn 6/18/05, Jacques Caron [EMAIL PROTECTED] wrote: Hi,At 18:00 18/06/2005, PFC wrote: I don't know what I'm talking about, but wouldn't mirorring be fasterthan striping for random reads like you often get

Re: [PERFORM] Cheap RAM disk?

2005-07-26 Thread Alex Turner
Also seems pretty silly to put it on a regular SATA connection, when all that can manage is 150MB/sec. If you made it connection directly to 66/64-bit PCI then it could actualy _use_ the speed of the RAM, not to mention PCI-X. Alex Turner NetEconomist On 7/26/05, John A Meinel [EMAIL PROTECTED

Re: [PERFORM] Cheap RAM disk?

2005-07-26 Thread Alex Turner
ol 2.5 Reg ECC. Alex Turner NetEconomist On 7/26/05, PFC [EMAIL PROTECTED] wrote: I'm a little leary as it is definitely a version 1.0 product (it is still using an FPGA as the controller, so they were obviously pushing to get the card into production). Not necessarily. FPGA's

Re: [PERFORM] Need for speed

2005-08-16 Thread Alex Turner
Are you calculating aggregates, and if so, how are you doing it (I ask the question from experience of a similar application where I found that my aggregating PGPLSQL triggers were bogging the system down, and changed them so scheduled jobs instead). Alex Turner NetEconomist On 8/16/05, Ulrich

Re: [PERFORM] sustained update load of 1-2k/sec

2005-08-19 Thread Alex Turner
. I have two independant controlers on two independant PCI buses to give max throughput. on with a 6 drive RAID 10 and the other with two 4 drive RAID 10s. Alex Turner NetEconomist On 8/19/05, Mark Cotner [EMAIL PROTECTED] wrote: Hi all, I bet you get tired of the same ole questions over and over

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

2005-09-12 Thread Alex Turner
don't co-operate with linux well. Alex Turner NetEconomist On 9/12/05, Brandon Black [EMAIL PROTECTED] wrote: I'm in the process of developing an application which uses PostgreSQL for data storage. Our database traffic is very atypical, and as a result it has been rather challenging to figure out how

Re: [PERFORM] RAID Stripe size

2005-09-20 Thread Alex Turner
that lower stripe sizes impacted performance badly as did overly large stripe sizes. Alex Turner NetEconomistOn 16 Sep 2005 04:51:43 -0700, bmmbn [EMAIL PROTECTED] wrote: Hi EveryoneThe machine is IBM x345 with ServeRAID 6i 128mb cache and 6 SCSI 15kdisks.2 disks are in RAID1 and hold the OS, SWAP

Re: [PERFORM] RAID Stripe size

2005-09-20 Thread Alex Turner
100% usage, they get all kinds of unhappy, we haven't had the same problem with JFS. Alex Turner NetEconomistOn 9/20/05, Welty, Richard [EMAIL PROTECTED] wrote: Alex Turnerwrote: I would also recommend looking at file system.For us JFS worked signifcantlyfaster than resier for large read loads

Re: [PERFORM] Need for speed 2

2005-09-20 Thread Alex Turner
I have found that while the OS may flush to the controller fast with fsync=true, the controller does as it pleases (it has BBU, so I'm not too worried), so you get great performance because your controller is determine read/write sequence outside of what is being demanded by an fsync. Alex Turner

Re: [PERFORM] Indexes on ramdisk

2005-10-04 Thread Alex Turner
people without resorting to SSD. Alex Turner NetEconomistOn 10/4/05, Emil Briggs [EMAIL PROTECTED] wrote: I have an application that has a table that is both read and write intensive.Data from iostat indicates that the write speed of the system is the factorthat is limiting performance. The table

Re: [PERFORM] Performance on SUSE w/ reiserfs

2005-10-11 Thread Alex Turner
disk pages. It looks to me more like either a Java problem, or a kernel problem... Alex Turner NetEconomistOn 10/10/05, Jon Brisbin [EMAIL PROTECTED] wrote: Tom Lane wrote: Are you sure it's not cached data pages, rather than cached inodes? If so, the above behavior is *good*. People often have

Re: [PERFORM] Performance on SUSE w/ reiserfs

2005-10-11 Thread Alex Turner
Well - to each his own I guess - we did extensive testing on 1.4, and it refused to allocate much past 1gig on both Linux x86/x86-64 and Windows. AlexOn 10/11/05, Alan Stange [EMAIL PROTECTED] wrote: Alex Turner wrote: Perhaps this is true for 1.5 on x86-32 (I've only used it on x86-64) but I

Re: [PERFORM] What gets cached?

2005-10-21 Thread Alex Turner
Oracle uses LRU caching algorithm also, not LFU. AlexOn 10/21/05, Martin Nickel [EMAIL PROTECTED] wrote: I was reading a comment in another posting and it started me thinkingabout this.Let's say I startup an Oracle server.All my queries are alittle bit (sometimes a lot bit) slow until it gets its

Re: [PERFORM] Used Memory

2005-10-21 Thread Alex Turner
[snip]to the second processor in my dual Xeon eServer) has got me to thepoint that the perpetually high memory usage doesn't affect my application server. I'm curious - how does the high memory usage affect your application server? Alex

Re: [PERFORM] What gets cached?

2005-10-24 Thread Alex Turner
Just to play devils advocate here for as second, but if we have an algorithm that is substational better than just plain old LRU, which is what I believe the kernel is going to use to cache pages (I'm no kernel hacker), then why don't we apply that and have a significantly larger page cache a la

Re: [PERFORM] Is There Any Way ....

2005-10-24 Thread Alex Turner
This is possible with Oracle utilizing the keep pool alter table t_name storage ( buffer_pool keep); If Postgres were to implement it's own caching system, this seems like it would be easily to implement (beyond the initial caching effort). Alex On 10/24/05, Craig A. James [EMAIL PROTECTED]

Re: [PERFORM] browsing table with 2 million records

2005-10-26 Thread Alex Turner
b.order_val=25 and b.order_val50 and a.primary_key_id=b.primary_key_id If the data updates alot then this won't work as well though as the index table will require frequent updates to potentialy large number of records (although a small number of pages so it still won't be horrible). Alex Turner

Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??

2005-11-07 Thread Alex Turner
and 2xRAID 1. Make sure you get the firmware update if you have these controllers though. Alex Turner NetEconomist On 11/6/05, Joost Kraaijeveld [EMAIL PROTECTED] wrote: Hi, I am experiencing very long update queries and I want to know if it reasonable to expect them to perform better

Re: [PERFORM] Sort performance on large tables

2005-11-10 Thread Alex Turner
We use this memory in all our servers (well - the 512 sticks). 0 problems to date: http://www.newegg.com/Product/Product.asp?Item=N82E16820145513 $163 for 1GB. This stuff is probably better than the Samsung RAM dell is selling you for 3 times the price. Alex On 11/10/05, Ron Peacetree [EMAIL

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-15 Thread Alex Turner
On 11/15/05, Luke Lonergan [EMAIL PROTECTED] wrote: Adam, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Claus Guttesen Sent: Tuesday, November 15, 2005 12:29 AM To: Adam Weisberg Cc: pgsql-performance@postgresql.org Subject: Re:

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-15 Thread Alex Turner
Not at random access in RAID 10 they aren't, and anyone with their head screwed on right is using RAID 10. The 9500S will still beat the Areca cards at RAID 10 database access patern. Alex. On 11/15/05, Dave Cramer [EMAIL PROTECTED] wrote: Luke, Have you tried the areca cards, they are

Re: [PERFORM] Hardware/OS recommendations for large databases

2005-11-16 Thread Alex Turner
). It should also be noted that 64 drive chassis' are going to become possible once 2.5 10Krpm SATA II and FC HDs become the standard next year (48's are the TOTL now). We need controller technology to keep up. Ron At 12:16 AM 11/16/2005, Alex Turner wrote: Not at random access in RAID 10

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-17 Thread Alex Turner
On 11/16/05, William Yu [EMAIL PROTECTED] wrote: Alex Turner wrote: Spend a fortune on dual core CPUs and then buy crappy disks... I bet for most applications this system will be IO bound, and you will see a nice lot of drive failures in the first year of operation with consumer grade

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-17 Thread Alex Turner
Just pick up a SCSI drive and a consumer ATA drive. Feel their weight. You don't have to look inside to tell the difference. Alex On 11/16/05, David Boreham [EMAIL PROTECTED] wrote: I suggest you read this on the difference between enterprise/SCSI and desktop/IDE drives:

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-17 Thread Alex Turner
On 11/16/05, Joshua D. Drake [EMAIL PROTECTED] wrote: The only questions would be: (1) Do you need a SMP server at all? I'd claim yes -- you always need 2+ cores whether it's DC or 2P to avoid IO interrupts blocking other processes from running. I would back this up. Even for smaller

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alex Turner
Ok - so I ran the same test on my system and get a total speed of 113MB/sec. Why is this? Why is the system so limited to around just 110MB/sec? I tuned read ahead up a bit, and my results improve a bit.. Alex On 11/18/05, Luke Lonergan [EMAIL PROTECTED] wrote: Dave, On 11/18/05 5:00

Re: [PERFORM] LVM and Postgres

2005-12-06 Thread Alex Turner
I would argue that almost certainly won't by doing that as you will create a new place even further away for the disk head to seek to instead of just another file on the same FS that is probably closer to the current head position. Alex On 12/6/05, Michael Stone [EMAIL PROTECTED] wrote: On Tue,

Re: [PERFORM] Is RAID10 the best choice?

2005-12-11 Thread Alex Turner
Personaly I would split into two RAID 1s. One for pg_xlog, one for the rest. This gives probably the best performance/reliability combination. Alex. On 12/10/05, Carlos Benkendorf [EMAIL PROTECTED] wrote: Hello, I would like to know which is the best configuration to use 4 scsi drives with

  1   2   >