Thread

Re: good pc but bad performance,why?

huang yaqin <hyq@gthome.com> — 2004-04-07T08:56:56Z

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
　　　　　　　　hyq@gthome.com
　　　　　　　　　　2004-04-07

Re: good pc but bad performance,why?

Dennis Björklund <db@zigo.dhs.org> — 2004-04-07T09:53:59Z

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

Re: good pc but bad performance,why?

Steven Butler <stevenb@kjross.com.au> — 2004-04-07T10:39:25Z

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" <hyq@gthome.com>
To: "Richard Huxton" <dev@archonet.com>
Cc: <pgsql-performance@postgresql.org>
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

Re: good pc but bad performance,why?

Andrew McMillan <andrew@catalyst.net.nz> — 2004-04-07T11:29:42Z

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?
-------------------------------------------------------------------------

Re: good pc but bad performance,why?

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2004-04-07T11:51:43Z

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

Re: good pc but bad performance,why?

Shridhar Daithankar <shridhar@frodo.hserus.net> — 2004-04-07T11:54:41Z

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

Re: good pc but bad performance,why?

scott.marlowe <scott.marlowe@ihs.com> — 2004-04-07T19:52:35Z

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.

Re: good pc but bad performance,why?

Bruce Momjian <pgman@candle.pha.pa.us> — 2004-04-07T22:12:38Z

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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: good pc but bad performance,why?

Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-08T01:31:18Z

Bruce Momjian <pgman@candle.pha.pa.us> 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

Re: good pc but bad performance,why?

Bruce Momjian <pgman@candle.pha.pa.us> — 2004-04-08T01:33:34Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> 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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: good pc but bad performance,why?

Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-08T02:13:34Z

Bruce Momjian <pgman@candle.pha.pa.us> 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

Re: good pc but bad performance,why?

Bruce Momjian <pgman@candle.pha.pa.us> — 2004-04-08T03:12:07Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> 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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: good pc but bad performance,why?

Andrew McMillan <andrew@catalyst.net.nz> — 2004-04-08T08:54:39Z

On Thu, 2004-04-08 at 14:13, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> 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
-------------------------------------------------------------------------

Re: good pc but bad performance,why?

Geoffrey <esoteric@3times25.net> — 2004-04-08T11:56:53Z

Andrew McMillan wrote:
> On Thu, 2004-04-08 at 14:13, Tom Lane wrote:
> 
>> Bruce Momjian <pgman@candle.pha.pa.us> 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

Re: good pc but bad performance,why?

Bruce Momjian <pgman@candle.pha.pa.us> — 2004-04-08T14:56:05Z

Andrew McMillan wrote:
> On Thu, 2004-04-08 at 14:13, Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> 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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073