Re: [PERFORM] good pc but bad performance,why?
On Wednesday 07 April 2004 16:59, Andrew McMillan wrote: > One thing I recommend is to use ext2 (or almost anything but ext3). > There is no real need (or benefit) from having the database on a > journalled filesystem - the journalling is only trying to give similar > sorts of guarantees to what the fsync in PostgreSQL is doing. That is not correct assumption. A journalling file system ensures file system consistency even at a cost of loss of some data. And postgresql can not guarantee recovery if WAL logs are corrupt. Some months back, there was a case reported where ext2 corrupted WAL and database. BAckup is only solution then.. Journalling file systems are usually very close to ext2 in performance, many a times lot better. With ext2, you are buying a huge risk. Unless there are good reason, I would not put a database on ext2. Performance isn't one ofthem.. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] good pc but bad performance,why?
Andrew McMillan wrote: > On Thu, 2004-04-08 at 14:13, Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > Tom Lane wrote: > > >> But it should be okay to set the filesystem to journal only its own > > >> metadata. There's no need for it to journal file contents. > > > > > Can you set ext2 to journal metadata? I didn't know it could do that. > > > > No, ext2 has no journal at all AFAIK. But I believe ext3 has an option > > to journal or not journal file contents, and at least on a Postgres-only > > volume you'd want to turn that off. > > No, it certainly doesn't. > > To be honest I was not aware that PostgreSQL was susceptible to failure > on non[metadata] journalled filesystems - I was [somewhat vaguely] of > the understanding that it would work fine on any filesystem. We expect the filesystem to come back intact. If it doesn't from an ext2 crash, we can't WAL recover in all cases. > And obviously, from my original post, we can see that I believed > metadata journalling was wasted on it. No. UFS file systems don't do journaling, but do metadata fsync, which is all we need. > Is the 'noatime' option worthwhile? Are you saying that PostgreSQL noatime might help, not sure, but my guess is that most inode fsync's are modifications of mtime, which can't be turned off with amount option. > should always be run on a metadata journalled filesystem then, and that > VFAT, ext2, etc are ++ungood? Yep. Not sure about VFAT but we do need the filesystem to return after a crash, obviously, or we can't even get to the xlog directory or the /data files to do WAL recovery. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
Re: [PERFORM] good pc but bad performance,why?
Andrew McMillan wrote: On Thu, 2004-04-08 at 14:13, Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: Tom Lane wrote: But it should be okay to set the filesystem to journal only its own metadata. There's no need for it to journal file contents. Can you set ext2 to journal metadata? I didn't know it could do that. No, ext2 has no journal at all AFAIK. But I believe ext3 has an option to journal or not journal file contents, and at least on a Postgres-only volume you'd want to turn that off. No, it certainly doesn't. You can mount ext3 filesystems as ext2 and they will function just as ext2. -- Until later, Geoffrey Registered Linux User #108567 Building secure systems in spite of Microsoft ---(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] good pc but bad performance,why?
On Thu, 2004-04-08 at 14:13, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> But it should be okay to set the filesystem to journal only its own > >> metadata. There's no need for it to journal file contents. > > > Can you set ext2 to journal metadata? I didn't know it could do that. > > No, ext2 has no journal at all AFAIK. But I believe ext3 has an option > to journal or not journal file contents, and at least on a Postgres-only > volume you'd want to turn that off. No, it certainly doesn't. To be honest I was not aware that PostgreSQL was susceptible to failure on non[metadata] journalled filesystems - I was [somewhat vaguely] of the understanding that it would work fine on any filesystem. And obviously, from my original post, we can see that I believed metadata journalling was wasted on it. Is the 'noatime' option worthwhile? Are you saying that PostgreSQL should always be run on a metadata journalled filesystem then, and that VFAT, ext2, etc are ++ungood? Thanks, Andrew McMillan. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 A foolish consistency is the hobgoblin of little minds - Shaw - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] good pc but bad performance,why?
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> But it should be okay to set the filesystem to journal only its own > >> metadata. There's no need for it to journal file contents. > > > Can you set ext2 to journal metadata? I didn't know it could do that. > > No, ext2 has no journal at all AFAIK. But I believe ext3 has an option > to journal or not journal file contents, and at least on a Postgres-only > volume you'd want to turn that off. Right, ext3 has that option. I don't think XFS needs it (it does meta-data only by default). -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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] good pc but bad performance,why?
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> But it should be okay to set the filesystem to journal only its own >> metadata. There's no need for it to journal file contents. > Can you set ext2 to journal metadata? I didn't know it could do that. No, ext2 has no journal at all AFAIK. But I believe ext3 has an option to journal or not journal file contents, and at least on a Postgres-only volume you'd want to turn that off. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] good pc but bad performance,why?
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > scott.marlowe wrote: > >>> There is no real need (or benefit) from having the database on a > >>> journalled filesystem - the journalling is only trying to give similar > >>> sorts of guarantees to what the fsync in PostgreSQL is doing. > >> > >> Is this true? I was under the impression that without at least meta-data > >> journaling postgresql could still be corrupted by power failure. > > > It is false. ext2 isn't crash-safe, and PostgreSQL needs an intact file > > system for WAL recovery. > > But it should be okay to set the filesystem to journal only its own > metadata. There's no need for it to journal file contents. Can you set ext2 to journal metadata? I didn't know it could do that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] good pc but bad performance,why?
Bruce Momjian <[EMAIL PROTECTED]> writes: > scott.marlowe wrote: >>> There is no real need (or benefit) from having the database on a >>> journalled filesystem - the journalling is only trying to give similar >>> sorts of guarantees to what the fsync in PostgreSQL is doing. >> >> Is this true? I was under the impression that without at least meta-data >> journaling postgresql could still be corrupted by power failure. > It is false. ext2 isn't crash-safe, and PostgreSQL needs an intact file > system for WAL recovery. But it should be okay to set the filesystem to journal only its own metadata. There's no need for it to journal file contents. regards, tom lane ---(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] good pc but bad performance,why?
scott.marlowe wrote: > > One thing I recommend is to use ext2 (or almost anything but ext3). > > There is no real need (or benefit) from having the database on a > > journalled filesystem - the journalling is only trying to give similar > > sorts of guarantees to what the fsync in PostgreSQL is doing. > > Is this true? I was under the impression that without at least meta-data > journaling postgresql could still be corrupted by power failure. It is false. ext2 isn't crash-safe, and PostgreSQL needs an intact file system for WAL recovery. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] good pc but bad performance,why?
On Wed, 7 Apr 2004, Andrew McMillan wrote: > On Wed, 2004-04-07 at 20:56, huang yaqin wrote: > > Hello, Richard Huxton, > > > > You said turning fsync off may cause losing data, that's terrible. > > I use SCSI disk, and file system is ext3. I tune postgresql.conf and can't get > > any improvement. So what can I do? > > Does SCSI disk and IDE disk have difference? > > Yes, turning off fsync means that the database is not guaranteeing > consistency of writes to disk any longer. On the other hand your IDE > system probably never was, because IDE drives just typically turn on > write caching in hardware without telling anyone. > > SCSI typically doesn't turn on write caching in the physical drive by > default, as Tom Lane pointed out earlier. Good SCSI has a battery > backed up cache, and then it is OK to turn on write caching, because the > controller has enough battery to complete all writes in the event of a > power failure. Actually, almost all SCSI drives turn on write caching by default, they just don't lie about fsync, so you still have a one update per revolution limit, but other things can be happening while that write is being commited due to the multi-threaded nature of both the SCSI interface and the kernel drivers associated with them It would appear the linux kernel hackers are trying to implement the multi-threaded features of the latest ATA spec, so that, at some future date, you could have IDE drives that cache AND tell the truth of their sync AND can do more than one thing at a time. > One thing I recommend is to use ext2 (or almost anything but ext3). > There is no real need (or benefit) from having the database on a > journalled filesystem - the journalling is only trying to give similar > sorts of guarantees to what the fsync in PostgreSQL is doing. Is this true? I was under the impression that without at least meta-data journaling postgresql could still be corrupted by power failure. > The suggestion someone else made regarding use of software raid is > probably also a good one if you are trying to use the on-board RAID at > the moment. Some onboard RAID controllers are fairly good (dell's 2600 series have an adaptec on board that can have battery backed cache that is ok, the lsi megaraid based one is faster under linux though.) But some of them are pretty poor performers. > Finally, I would say that because you are seeing poor performance on one > box and great performance on another, you should look at the hardware, > or at the hardware drivers, for the problem - not so much at PostgreSQL. More than likely, the biggest issue is that the SCSI drives are performing proper fsync, while the IDE drives are lying. Definitely a time to look at a good caching RAID controller. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] good pc but bad performance,why?
fsync I'm thinking 50 inserts, if autocommiting is 50TPS = ~100 IO per second (50 WAL + checkpoint IO) = roughly the I/O rate of a single drive. Huang - Are you using a single drive for pg? If so, there is a safety problem of both the data and logs used for recovery on the same drive. If the drive crashes, there is nothing left for recovery. Also, there is a big contention issue, since the log is a fast sequential write, and checkpointing is random. If the log is on a separate drive, you'll probably see insert speed at disk sequential write speed, since the other drive(s) should hopefully be able to keep up when checkpointing. If they share the same drive, you'll see an initial burst of inserts, then a order of magnitude performance drop-off as soon as you checkpoint - because the disk is interleaving the log and data writes. fsync off is only appropriate for externally recoverable processes, such as loading an empty server from a file. /Aaron - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "huang yaqin" <[EMAIL PROTECTED]>; "Tom Lane" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, April 07, 2004 4:33 AM Subject: Re: [PERFORM] good pc but bad performance,why? On Wednesday 07 April 2004 05:00, huang yaqin wrote: > hello！ > > Thanks, you are right. > I use "postmaster -o "-F" " to start my PG，and performance improved > greatly. I don't think Tom was recommending turning fsync off. If you have a system crash/power glitch then the database can become corrupted. If you are happy the possibility if losing your data, write performance will improve noticably. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] good pc but bad performance,why?
Sending again bacuse of MUA error.. Chose a wrong address in From..:-( Shridhar On Wednesday 07 April 2004 17:21, Shridhar Daithankar wrote: > On Wednesday 07 April 2004 16:59, Andrew McMillan wrote: > > One thing I recommend is to use ext2 (or almost anything but ext3). > > There is no real need (or benefit) from having the database on a > > journalled filesystem - the journalling is only trying to give similar > > sorts of guarantees to what the fsync in PostgreSQL is doing. > > That is not correct assumption. A journalling file system ensures file > system consistency even at a cost of loss of some data. And postgresql can > not guarantee recovery if WAL logs are corrupt. Some months back, there was > a case reported where ext2 corrupted WAL and database. BAckup is only > solution then.. > > Journalling file systems are usually very close to ext2 in performance, > many a times lot better. With ext2, you are buying a huge risk. > > Unless there are good reason, I would not put a database on ext2. > Performance isn't one ofthem.. > > Shridhar ---(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] good pc but bad performance,why?
On Wed, 2004-04-07 at 20:56, huang yaqin wrote: > Hello, Richard Huxton, > > You said turning fsync off may cause losing data, that's terrible. > I use SCSI disk, and file system is ext3. I tune postgresql.conf and can't get > any improvement. So what can I do? > Does SCSI disk and IDE disk have difference? Yes, turning off fsync means that the database is not guaranteeing consistency of writes to disk any longer. On the other hand your IDE system probably never was, because IDE drives just typically turn on write caching in hardware without telling anyone. SCSI typically doesn't turn on write caching in the physical drive by default, as Tom Lane pointed out earlier. Good SCSI has a battery backed up cache, and then it is OK to turn on write caching, because the controller has enough battery to complete all writes in the event of a power failure. One thing I recommend is to use ext2 (or almost anything but ext3). There is no real need (or benefit) from having the database on a journalled filesystem - the journalling is only trying to give similar sorts of guarantees to what the fsync in PostgreSQL is doing. The suggestion someone else made regarding use of software raid is probably also a good one if you are trying to use the on-board RAID at the moment. Finally, I would say that because you are seeing poor performance on one box and great performance on another, you should look at the hardware, or at the hardware drivers, for the problem - not so much at PostgreSQL. Of course if it is application performance you want to achieve, we _can_ help here, but you will need to provide more details of what you are trying to do in your application, including; - confirmation that you have done a VACUUM and ANALYZE of all tables before you start - output from EXPLAIN ANALYZE for slow queries - anything else you think is useful. without that sort of detail we can only give vague suggestions, like "wrap everything in a transaction" - excellent advice, certainly, but you can read that in the manual. There are no magic bullets, but I am sure most of the people on this list have systems that regularly do way more than 50 inserts / second on server hardware. Regards, Andrew McMillan - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 http://survey.net.nz/ - any questions? - ---(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] good pc but bad performance,why?
It sounds almost like you're doing one insert per transaction. Try wrapping multiple inserts into a single transaction and see if that helps. This may not be appropriate for your application, but it does guarantee that committed transactions will not be lost. My apologies if you are already doing this. :) BEGIN; insert ... insert ... insert ... COMMIT; Regards, Steve Butler - Original Message - From: "huang yaqin" <[EMAIL PROTECTED]> To: "Richard Huxton" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, April 07, 2004 6:56 PM Subject: Re: [PERFORM] good pc but bad performance,why? Hello, Richard Huxton, You said turning fsync off may cause losing data, that's terrible. I use SCSI disk, and file system is ext3. I tune postgresql.conf and can't get any improvement. So what can I do? Does SCSI disk and IDE disk have difference? Regards, Huang yaqin ---(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] good pc but bad performance,why?
On Wed, 7 Apr 2004, huang yaqin wrote: > You said turning fsync off may cause losing data, that's terrible. I use > SCSI disk, and file system is ext3. I tune postgresql.conf and can't get > any improvement. So what can I do? Make sure you do as much as possible inside one transaction. If you want to do 1000 inserts, then do BEGIN; insert ; insert; ... ; COMMIT; -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] good pc but bad performance,why?
Hello, Richard Huxton, You said turning fsync off may cause losing data, that's terrible. I use SCSI disk, and file system is ext3. I tune postgresql.conf and can't get any improvement. So what can I do? Does SCSI disk and IDE disk have difference? Regards, Huang yaqin === 2004-04-07 09:33:00 === >On Wednesday 07 April 2004 05:00, huang yaqin wrote: >> hello！ >> >> Thanks, you are right. >> I use "postmaster -o "-F" " to start my PG，and performance improved >> greatly. > >I don't think Tom was recommending turning fsync off. If you have a system >crash/power glitch then the database can become corrupted. > >If you are happy the possibility if losing your data, write performance will >improve noticably. > >-- > Richard Huxton > Archonet Ltd > > > > >Powered by MessageSoft SMG >SPAM, virus-free and secure email >http://www.messagesoft.com > >. = = = = = = = = = = = = = = = = = = = = 致 礼！ huang yaqin [EMAIL PROTECTED] 2004-04-07 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] good pc but bad performance,why?
On Wednesday 07 April 2004 05:00, huang yaqin wrote: > hello！ > > Thanks, you are right. >I use "postmaster -o "-F" " to start my PG，and performance improved > greatly. I don't think Tom was recommending turning fsync off. If you have a system crash/power glitch then the database can become corrupted. If you are happy the possibility if losing your data, write performance will improve noticably. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] good pc but bad performance,why?
hello！ Thanks, you are right. I use "postmaster -o "-F" " to start my PG，and performance improved greatly. Best regards, huang yaqin >huang yaqin <[EMAIL PROTECTED]> writes: >> I have some question when I use postgresql 7.4.1 on redhat adv server 2.1 . >> I use IBM335 as server, it has 4 cpus, 1G RAM. but I got very bad performance. >> I can only do about 50 inserts per sencond. Event worse than my pc(PIII 800,256M >> RAM), can anyone give me some advice? > >If the cheap machine appears to be able to commit more transactions >per second than the better one, it's very likely because the cheap >machine has a disk that lies about write completion. Is the server >using SCSI disks by any chance? To a first approximation, IDE drives >lie by default, SCSI drives don't. > > regards, tom lane > > > > >Powered by MessageSoft SMG >SPAM, virus-free and secure email >http://www.messagesoft.com = = = = = = = = = = = = = = = = = = = = 致 礼！ huang yaqin [EMAIL PROTECTED] 2004-04-07 ---(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] good pc but bad performance,why?
huang yaqin wrote: hello, I have some question when I use postgresql 7.4.1 on redhat adv server 2.1 . I use IBM335 as server, it has 4 cpus, 1G RAM. but I got very bad performance. This is most likely a dual processor Xeon machine with HT, because the x335 is limited to two physical cpus. I can only do about 50 inserts per sencond. Event worse than my pc(PIII 800,256M RAM), can anyone give me some advice? any chance you are using the onboard MPT-Fusion "Raid"controller with a RAID1 - we have seen absolutely horrible performance from these controllers here. Using them as a normal SCSI-Controller with Softwareraid on top fixed this for us ... stefan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] good pc but bad performance,why?
huang yaqin <[EMAIL PROTECTED]> writes: > I have some question when I use postgresql 7.4.1 on redhat adv server 2.1 . > I use IBM335 as server, it has 4 cpus, 1G RAM. but I got very bad performance. > I can only do about 50 inserts per sencond. Event worse than my pc(PIII 800,256M > RAM), can anyone give me some advice? If the cheap machine appears to be able to commit more transactions per second than the better one, it's very likely because the cheap machine has a disk that lies about write completion. Is the server using SCSI disks by any chance? To a first approximation, IDE drives lie by default, SCSI drives don't. regards, tom lane ---(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] good pc but bad performance,why?
huang yaqin wrote: > hello, > > I have some question when I use postgresql 7.4.1 on redhat adv server 2.1 . > I use IBM335 as server, it has 4 cpus, 1G RAM. but I got very bad performance. > I can only do about 50 inserts per sencond. Event worse than my pc(PIII 800,256M > RAM), can anyone give me some advice? Have you referenced this document?: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Bill Moran Potential Technologies http://www.potentialtech.com ---(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] good pc but bad performance,why?
On Tuesday 06 April 2004 09:01, huang yaqin wrote: > hello, > > I have some question when I use postgresql 7.4.1 on redhat adv server 2.1 > . I use IBM335 as server, it has 4 cpus, 1G RAM. but I got very bad > performance. I can only do about 50 inserts per sencond. Event worse than > my pc(PIII 800,256M RAM), can anyone give me some advice? How have you tuned your postgresql.conf file? What disk systems do you have? What does vmstat/iostat show as the bottleneck in the system? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
