Re: [PERFORM] XFS filessystem for Datawarehousing
Milen, XFS, EXT3, JFS For what reason are you planning to use a journaling FS? I think using WAL, fsyncing every transaction and using a journaling FS is tautologous. And if you have problems using EXT2 you can just add the journal later without loosing data. My tests using EXT2 showed a performance boost up to 50% on INSERTs. Christian I am pretty exited whether XFS will clearly outpertform ETX3 (no default setups for both are planned !). I am not sure whether is it worth to include JFS in comparison too ... Best Regards, Milen Kulev ** The information contained in, or attached to, this e-mail, may contain confidential information and is intended solely for the use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of the company. The recipient should check this e-mail and any attachments for the presence of viruses. The company accepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. ** ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] XFS filessystem for Datawarehousing
* Christian Koth: For what reason are you planning to use a journaling FS? I think using WAL, fsyncing every transaction and using a journaling FS is tautologous. The journal is absolutely required to preserve the integrity of the file system's own on-disk data structures after a crash. Even if you've got a trustworthy file system checker (there are surprisingly few of them, especially for advanced file systems without fixed data structure locations), running it after a crash usually leads to unacceptably high downtime. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] XFS filessystem for Datawarehousing
On Thu, Aug 03, 2006 at 01:10:39AM -0600, Koth, Christian (DWBI) wrote: For what reason are you planning to use a journaling FS? I think using WAL, fsyncing every transaction and using a journaling FS is tautologous. And if you have problems using EXT2 you can just add the journal later without loosing data. My tests using EXT2 showed a performance boost up to 50% on INSERTs. The requirements for the WAL filesystem and for the data filesystem are different. Having the WAL on a small ext2 filesystem makes sense and is good for performance. Having the data on a huge ext2 filesystem is a horrible idea, because you'll fsck forever if there's a crash, and because ext2 isn't a great performer for large filesystems. I typically have a couple-gig ext2 WAL paired with a couple of couple-hundred-gig xfs data index partitions. Note that the guarantees of a journaling fs like xfs have nothing to do with the kind of journaling done by the WAL, and each has its place on a postgres system. Mike Stone ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] unsubscribe
unsubscribe -- Wade Klaver Wavefire Technologies Corporation GPG Public Key at http://archeron.wavefire.com /\ ASCII Ribbon Campaign . \ / - NO HTML/RTF in e-mail . X - NO Word docs in e-mail . / \ - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Strange behaviour
Richard Rowell [EMAIL PROTECTED] writes: We are using a BI tool that generates some rather ugly queries. One of the ugly queries is taking much longer to return thin I think it should. (http://www.bowmansystems.com/~richard/full.analyze) Can anyone shed any light on what is going on here? Seems like you have some bad rowcount estimates leading to poor plan selection. Most of the problem looks to be coming from the FunctionScan nodes, wherein the planner doesn't have any real way to estimate how many rows come out. You might look into whether you can replace those functions with views, so that the planner isn't dealing with black boxes. regards, tom lane ---(end of broadcast)--- TIP 1: 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
[PERFORM]
I'm at a client who's an ASP; they've written their app such that each customer gets their own database. Rigth now they're at nearly 200 databases, and were thinking that they must be the largest PostgreSQL install in the world. :) After taking them down a notch or two, I started wondering how many sites could beat 200 databases in a single cluster. I'm sure there's any number that can, though 200 databases in a cluster certainly isn't mainstream. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM]
On Thu, Aug 03, 2006 at 01:33:35PM -0500, Jim Nasby wrote: I'm at a client who's an ASP; they've written their app such that each customer gets their own database. Rigth now they're at nearly 200 databases, and were thinking that they must be the largest PostgreSQL install in the world. :) After taking them down a notch or two, I started wondering how many sites could beat 200 databases in a single cluster. I'm sure there's any number that can, though 200 databases in a cluster certainly isn't mainstream. cassarossa:~ psql -h sql -l | grep 'rows)' (137 rows) That's our measly student society. :-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM]
I've got 226 customer databases in one cluster. Works like a champ with 8.1.3. I have 3 additional PostgreSQL servers with our largest customers on them. They have between 10 and 30 databases. The smallest of my servers has 261GB's worth of db's in the cluster, and the largest is 400GB's. BTW, our application is an asp application also.Just some fun numbers for you.ChrisP.S.Thanks to all of the PostgreSQL developers for the great work and for providing the awesome support. On 8/3/06, Jim Nasby [EMAIL PROTECTED] wrote: I'm at a client who's an ASP; they've written their app such that eachcustomer gets their own database. Rigth now they're at nearly 200databases, and were thinking that they must be the largest PostgreSQL install in the world. :) After taking them down a notch or two, Istarted wondering how many sites could beat 200 databases in a singlecluster. I'm sure there's any number that can, though 200 databases in a cluster certainly isn't mainstream.--Jim C. Nasby, Sr. Engineering Consultant[EMAIL PROTECTED]Pervasive Softwarehttp://pervasive.com work: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] XFS filessystem for Datawarehousing
Hi Luke, That is ~ 50% increase !! Amazing... How many reader processes did you have to get this results ? Regards. Milen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Luke Lonergan Sent: Thursday, August 03, 2006 6:05 AM To: Michael Stone; pgsql-performance@postgresql.org Subject: Re: [PERFORM] XFS filessystem for Datawarehousing Again - the performance difference increases as the disk speed increases. Our experience is that we went from 300MB/s to 475MB/s when moving from ext3 to xfs. - Luke On 8/2/06 4:33 PM, Michael Stone [EMAIL PROTECTED] wrote: On Wed, Aug 02, 2006 at 02:26:39PM -0700, Steve Poe wrote: For the past year, I have been running odbc-bench on a dual-opteron with 4GB of RAM using a 8GB sample data. I found the performance difference between EXT3, JFS, and XFS is +/- 5-8%. That's not surprising when your db is only 2x your RAM. You'll find that filesystem performance is much more important when your database is 10x+ your RAM (which is often the case once your database heads toward a TB). Testing newer kernels and read-ahead patches may benefit you as well. I've been really impressed by the adaptive readahead patches with postgres. Mike Stone ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: 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] XFS filessystem for Datawarehousing -2
Title: Nachricht Hi Dennis, I am just cusrios to try PGwith different block sizes ;) I don't knowhow much performancethe bigger block size will bring (I mean 32k or 64k , for example, for DWH applikations). I am surprised tohear that OCFS2.0 (or any her FS usind direct I/O) performs well with PG.A month ago I haveperformed asimple test with VeritasFS, with and than without cache (e.g. direct I/O).I have started1 , then 2,, then3, then 4 parallel INSERT processes. Veritas FS WITH FS cacheoutperformed the direct I/O version by factor 2-2.5! I haven't tested woth OCFS2.0 though. I am not sure that OCFS2.0 is the good choicefor PG data and index filesystems. For WAL - perhaps. Best Regards. Milen -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Denis LussierSent: Thursday, August 03, 2006 7:36 AMTo: Luke LonerganCc: Milen Kulev; pgsql-performance@postgresql.orgSubject: Re: [PERFORM] XFS filessystem for Datawarehousing -2I was kinda thinking that making the Block Size configurable at InitDB time would be a nice simple enhancement for PG 8.3. My own personal rule of thumb for sizing is 8k for OLTP, 16k for mixed use, 32k for DWH. I have no personal experience with XFS, but, I've seen numerous internal edb-postgres test results that show that of all file systems... OCFS 2.0 seems to be quite good for PG update intensive apps (especially on 64 bit machines). On 8/1/06, Luke Lonergan [EMAIL PROTECTED] wrote: Milen,On 8/1/06 3:19 PM, "Milen Kulev" [EMAIL PROTECTED] wrote: Sorry, forgot to ask: What is the recommended/bestPG block size for DWHdatabase?16k, 32k, 64k ? What hsould be the relationbetween XFS/RAID stripe size and PG block size ?We have found that the page size in PG starts to matter only at very highdisk performance levels around 1000MB/s.Other posters have talked about maintenance tasks improving in performance, but I haven't seen it.- Luke---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM]
Just curious, is this a production server? Also, how large is the total cluster on disk?On 8/3/06, Ian Westmacott [EMAIL PROTECTED] wrote:is that all? psql -l | grep 'rows)'(2016 rows) On Thu, 2006-08-03 at 21:15 +0200, Steinar H. Gunderson wrote: On Thu, Aug 03, 2006 at 01:33:35PM -0500, Jim Nasby wrote: I'm at a client who's an ASP; they've written their app such that each customer gets their own database. Rigth now they're at nearly 200 databases, and were thinking that they must be the largest PostgreSQL install in the world. :) After taking them down a notch or two, I started wondering how many sites could beat 200 databases in a single cluster. I'm sure there's any number that can, though 200 databases in a cluster certainly isn't mainstream. cassarossa:~ psql -h sql -l | grep 'rows)' (137 rows) That's our measly student society. :-) /* Steinar */---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] RAID stripe size question
On 8/3/06, Luke Lonergan [EMAIL PROTECTED] wrote: Merlin, moving a gigabyte around/sec on the server, attached or no, is pretty heavy lifting on x86 hardware. Maybe so, but we're doing 2GB/s plus on Sun/Thumper with software RAID and 36 disks and 1GB/s on a HW RAID with 16 disks, all SATA. that is pretty amazing, that works out to 55 mb/sec/drive, close to theoretical maximums. are you using pci-e sata controller and raptors im guessing? this is doubly impressive if we are talking raid 5 here. do you find that software raid is generally better than hardware at the highend? how much does this tax the cpu? WRT seek performance, we're doing 2500 seeks per second on the Sun/Thumper on 36 disks. You might do better with 15K RPM disks and great controllers, but I haven't seen it reported yet. thats pretty amazing too. only a highly optimized raid system can pull this off. BTW - I'm curious about the HP P600 SAS host based RAID controller - it has very good specs, but is the Linux driver solid? have no clue. i sure hope i dont go through the same headaches as with ibm scsi drivers (rebranded adaptec btw). sas looks really promising however. the adaptec sas gear is so cheap it might be worth it to just buy some and see what it can do. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM]
No, this is a test server used for regression testing. Relatively small (hundreds of GB) and quiet (dozen connections) in the Postgres universe. On Thu, 2006-08-03 at 16:31 -0400, Chris Hoover wrote: Just curious, is this a production server? Also, how large is the total cluster on disk? On 8/3/06, Ian Westmacott [EMAIL PROTECTED] wrote: is that all? psql -l | grep 'rows)' (2016 rows) On Thu, 2006-08-03 at 21:15 +0200, Steinar H. Gunderson wrote: On Thu, Aug 03, 2006 at 01:33:35PM -0500, Jim Nasby wrote: I'm at a client who's an ASP; they've written their app such that each customer gets their own database. Rigth now they're at nearly 200 databases, and were thinking that they must be the largest PostgreSQL install in the world. :) After taking them down a notch or two, I started wondering how many sites could beat 200 databases in a single cluster. I'm sure there's any number that can, though 200 databases in a cluster certainly isn't mainstream. cassarossa:~ psql -h sql -l | grep 'rows)' (137 rows) That's our measly student society. :-) /* Steinar */ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] XFS filessystem for Datawarehousing -2
[EMAIL PROTECTED] (Denis Lussier) writes: I have no personal experience with XFS, but, I've seen numerous internal edb-postgres test results that show that of all file systems... OCFS 2.0 seems to be quite good for PG update intensive apps (especially on 64 bit machines). I have been curious about OCFS for some time; it sounded like a case where there could possibly be some useful semantic changes to filesystem functionality, notably that: - atime is pretty irrelevant; - it might try to work with pretty fixed block sizes (8K, perhaps?) rather than try to be efficient at handling tiny files It sounds like it ought to be able to be a good fit. Of course, with a big warning sticker of what is required for Oracle to work properly is implemented, anything more is not a guarantee on it, who's going to trust it? -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://www.ntlug.org/~cbbrowne/oses.html There isn't any reason why Linux can't be implemented as an enterprise computing solution. Find out what you've been missing while you've been rebooting Windows NT. - Infoworld ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] XFS filessystem for Datawarehousing
Milen, On 8/3/06 12:44 PM, Milen Kulev [EMAIL PROTECTED] wrote: Hi Luke, That is ~ 50% increase !! Amazing... How many reader processes did you have to get this results ? Just one - I'll refresh the results sometime and post. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
