Обсуждение: good pc but bad performance,why?
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
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
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
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
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
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
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
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
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
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
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?
-------------------------------------------------------------------------
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
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
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.
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
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
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
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
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
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
-------------------------------------------------------------------------
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
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
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