Thread: good pc but bad performance,why?

good pc but bad performance,why?

From
huang yaqin
Date:
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?
  

        huang yaqin
        hyq@gthome.com
          2004-04-06



Re: good pc but bad performance,why?

From
Richard Huxton
Date:
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

Re: good pc but bad performance,why?

From
Bill Moran
Date:
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


Re: good pc but bad performance,why?

From
Tom Lane
Date:
huang yaqin <hyq@gthome.com> 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

Re: good pc but bad performance,why?

From
Stefan Kaltenbrunner
Date:
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

Re: good pc but bad performance,why?

From
huang yaqin
Date:
hello!

    Thanks, you are right.
     I use "postmaster -o "-F" " to start my PG,and performance improved greatly.

           Best   regards,
             huang yaqin

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




Re: good pc but bad performance,why?

From
Richard Huxton
Date:
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

Re: good pc but bad performance,why?

From
huang yaqin
Date:
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?

From
Dennis Bjorklund
Date:
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?

From
"Steven Butler"
Date:
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?

From
Andrew McMillan
Date:
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?

From
Shridhar Daithankar
Date:
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?

From
"Aaron Werman"
Date:
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" <dev@archonet.com>
To: "huang yaqin" <hyq@gthome.com>; "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
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

Re: good pc but bad performance,why?

From
"scott.marlowe"
Date:
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?

From
Bruce Momjian
Date:
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?

From
Tom Lane
Date:
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?

From
Bruce Momjian
Date:
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?

From
Tom Lane
Date:
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?

From
Bruce Momjian
Date:
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?

From
Andrew McMillan
Date:
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?

From
Geoffrey
Date:
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?

From
Bruce Momjian
Date:
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

Re: good pc but bad performance,why?

From
Shridhar Daithankar
Date:
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