Re: - Mailing list pgsql-performance

Joshua,

Actually that's a great idea!
I'll have to check if Solaris wants to play ball though.
We'll have to see as we don't have the new disks yet, ETA is next week.

Cheers,

Matt

-----Oorspronkelijk bericht-----
Van: Joshua D. Drake [mailto:jd@commandprompt.com]
Verzonden: donderdag 20 januari 2005 21:26
Aan: matt.casters@advalvas.be
CC: pgsql-performance@postgresql.org
Onderwerp: Re: [PERFORM]

Matt Casters wrote:
>
> Thanks Stephen,
>
> My main concern is to get as much read performance on the disks as
> possible on this given system.  CPU is rarely a problem on a typical
> data warehouse system, this one's not any different.
>
> We basically have 2 RAID5 disk sets (300Gb) and 150Gb) with a third
> one coming along.(around 350Gb)

Why not run two raid systems. A RAID 1 for your OS and a RAID 10 for your
database? Push all of your extra drives into the RAID 10.

Sincerely,

Joshua D. Drake




> I was kind of hoping that the new PGSQL tablespaces would allow me to
> create a storage container spanning multiple file-systems, but
> unfortunately, that seems to be not the case.  Is this correct?
>
> That tells me that I probably need to do a full reconfiguration of the
> disks on the Solaris level to get maximum performance out of the system.
> Mmmm. This is going to be a though one to crack.  Perhaps it will be
> possible to get some extra juice out of placing the indexes on the
> smaller disks (150G) and the data on the bigger ones?
>
> Thanks!
>
> Matt
>
> -----Oorspronkelijk bericht-----
> Van: Stephen Frost [mailto:sfrost@snowman.net]
> Verzonden: donderdag 20 januari 2005 15:26
> Aan: Matt Casters
> CC: pgsql-performance@postgresql.org
> Onderwerp: Re: [PERFORM]
>
> * Matt Casters (Matt.Casters@advalvas.be) wrote:
>
>>I have the go ahead of a customer to do some testing on Postgresql in
>>a couple of weeks as a replacement for Oracle.
>>The reason for the test is that the number of users of the warehouse
>>is going to increase and this will have a serious impact on licencing
>>costs. (I bet that sounds familiar)
>
>
> Rather familiar, yes... :)
>
>
>>We're running a medium sized data warehouse on a Solaris box (4CPU,
>>8Gb
>
> RAM) on Oracle.
>
>>Basically we have 2 large fact tables to deal with: one going for 400M
>>rows, the other will be hitting 1B rows soon.
>>(around 250Gb of data)
>
>
> Quite a bit of data.  There's one big thing to note here I think-
> Postgres will not take advantage of multiple CPUs for a given query,
Oracle will.
> So, it depends on your workload as to how that may impact you.
> Situations where this will be unlikely to affect you:
>
> Your main bottle-neck is IO/disk and not CPU.
> You run multiple queries in parallel frequently.
> There are other processes on the system which chew up CPU time anyway.
>
> Situations where you're likely to be affected would be:
>
> You periodically run one big query.
> You run a set of queries in sequential order.
>
>
>>My questions to the list are: has this sort of thing been attempted
>>before? If so, what where the performance results compared to Oracle?
>
>
> I'm pretty sure it's been attempted before but unfortunately I don't
> have any numbers on it myself.  My data sets aren't that large (couple
> million
> rows) but I've found PostgreSQL at least as fast as Oracle for what we
> do, and much easier to work with.
>
>
>>I've been reading up on partitioned tabes on pgsql, will the
>>performance benefit will be comparable to Oracle partitioned tables?
>
>
> In this case I would think so, except that PostgreSQL still won't use
> multiple CPUs for a given query, even against partitioned tables, aiui.
>
>
>>What are the gotchas?
>
>
> See above? :)  Other issues are things having to do w/ your specific
> SQL- Oracle's old join syntax isn't supported by PostgreSQL (what is
> it, something like select x,y from a,b where x=%y; to do a right-join,
iirc).
>
>
>>Should I be testing on 8 or the 7 version?
>
>
> Now that 8.0 is out I'd say probably test with that and just watch for
> 8.0.x releases before you go production, if you have time before you
> have to go into production with the new solution (sounds like you do-
> changing databases takes time anyway).
>
>
>>Thanks in advance for any help you may have, I'll do my best to keep
>>pgsql-performance up to date on the results.
>
>
> Hope that helps.  Others on here will correct me if I misspoke. :)
>
>     Stephen
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


--
Command Prompt, Inc., your source for PostgreSQL replication, professional
support, programming, managed services, shared and dedicated hosting. Home
of the Open Source Projects plPHP, plPerlNG, pgManage,  and pgPHPtoolkit.
Contact us now at: +1-503-667-4564 - http://www.commandprompt.com