Обсуждение: Performance on 8CPU's and 32GB of RAM

A client is moving their postgresql db to a brand new Windows 2003 x64
server with 2 quad cores and 32GB of RAM. It is a dedicated server to run
8.2.4.

The server typically will have less than 10 users. The primary use of this
server is to host a database that is continuously being updated by data
consolidation and matching software software that hits the server very hard.
There are typically eight such processes running at any one time. The
software extensively exploits postgresql native fuzzy string for data
matching. The SQL is dynamically generated by the software and consists of
large, complex joins. (the structure of the joins change as the software
adapts its matching strategies).

I would like to favour the needs of the data matching software, and the
server is almost exclusivly dedicated to PostgreSQL.

I have made some tentative modifications to the default postgres.config file
(see below), but I don't think I've scratched the surface of what this new
system is capable of. Can I ask - given my client's needs and this new,
powerful server and the fact that the server typically has a small number of
extremely busy processes, what numbers they would change, and what the
recommendations would be?

Thanks!

Carlo

max_connections = 100
shared_buffers = 100000
work_mem = 1000000
max_fsm_pages = 204800
max_fsm_relations = 1500
vacuum_cost_delay = 40
bgwriter_lru_maxpages = 100
bgwriter_all_maxpages = 100
checkpoint_segments = 64
checkpoint_warning = 290
effective_cache_size = 375000
stats_command_string = on
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1

On 9/4/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> A client is moving their postgresql db to a brand new Windows 2003 x64
> server with 2 quad cores and 32GB of RAM. It is a dedicated server to run
> 8.2.4.

And what does the drive subsystem look like?  All that horsepower
isn't going to help if all your data is sitting on an inferior drive
subsystem.

> The server typically will have less than 10 users. The primary use of this
> server is to host a database that is continuously being updated by data
> consolidation and matching software software that hits the server very hard.
> There are typically eight such processes running at any one time. The
> software extensively exploits postgresql native fuzzy string for data
> matching. The SQL is dynamically generated by the software and consists of
> large, complex joins. (the structure of the joins change as the software
> adapts its matching strategies).
>
> I would like to favour the needs of the data matching software, and the
> server is almost exclusivly dedicated to PostgreSQL.
>
> I have made some tentative modifications to the default postgres.config file
> (see below), but I don't think I've scratched the surface of what this new
> system is capable of. Can I ask - given my client's needs and this new,
> powerful server and the fact that the server typically has a small number of
> extremely busy processes, what numbers they would change, and what the
> recommendations would be?
>
> Thanks!
>
> Carlo
>
> max_connections = 100
> shared_buffers = 100000
> work_mem = 1000000

Even with only 10 users, 1 gig work_mem is extremely high.  (without a
unit, work_mem is set in k on 8.2.x) 10000 would be much more
reasonable.

OTOH, shared_buffers, at 100000 is only setting it to 100 meg.  that's
pretty small on a machine with 32 gig.  Also, I recommend setting
values more readable, like 500MB in postgresql.conf.  Much easier to
read than 100000...

> effective_cache_size = 375000

This seems low by an order of magnitude or two.

But the most important thing is what you've left out.  What kind of
I/O does this machine have.  It's really important for something that
sounds like an OLAP server.

Carlo Stonebanks wrote:
> A client is moving their postgresql db to a brand new Windows 2003 x64
> server with 2 quad cores and 32GB of RAM. It is a dedicated server to run
> 8.2.4.

Large shared_buffers and Windows do not mix.  Perhaps you should leave
the shmem config low, so that the kernel can cache the file pages.

> The server typically will have less than 10 users. The primary use of this
> server is to host a database that is continuously being updated by data
> consolidation and matching software software that hits the server very
> hard. There are typically eight such processes running at any one time. The
> software extensively exploits postgresql native fuzzy string for data
> matching. The SQL is dynamically generated by the software and consists of
> large, complex joins. (the structure of the joins change as the software
> adapts its matching strategies).

It sounds like you will need a huge lot of vacuuming effort to keep up.
Maybe you should lower autovac scale factors so that your tables are
visited more frequently.  A vacuum_delay of 40 sounds like too much
though.

Since you didn't describe your disk configuration, it is most likely not
really prepared to handle high I/O load.  Maybe you should fix that.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 9/4/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Carlo Stonebanks wrote:
> > A client is moving their postgresql db to a brand new Windows 2003 x64
> > server with 2 quad cores and 32GB of RAM. It is a dedicated server to run
> > 8.2.4.
>
> Large shared_buffers and Windows do not mix.  Perhaps you should leave
> the shmem config low, so that the kernel can cache the file pages.

Egads, I'd completely missed the word Windows up there.

I would highly recommend building the postgresql server on a unixish
OS.  Even with minimum tuning, I'd expect the same box running linux
or freebsd to stomp windows pretty heavily in the performance
department.

But yeah, the I/O, that's the big one.  If it's just a single or a
couple of IDE drives, it's not gonna be able to handle much load.

On 05.09.2007 01:15, Scott Marlowe wrote:
> On 9/4/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>> Carlo Stonebanks wrote:
>>> A client is moving their postgresql db to a brand new Windows 2003 x64
>>> server with 2 quad cores and 32GB of RAM. It is a dedicated server to run
>>> 8.2.4.
>> Large shared_buffers and Windows do not mix.  Perhaps you should leave
>> the shmem config low, so that the kernel can cache the file pages.
>
> But yeah, the I/O, that's the big one.  If it's just a single or a
> couple of IDE drives, it's not gonna be able to handle much load.

Right, additionally NTFS is really nothing to use on any serious disc array.


--
Regards,
Hannes Dorbath

Unfortunately, LINUX is not an option at this time. We looked into it; there
is no *NIX expertise in the enterprise. However, I have raised this issue in
various forums before, and when pressed no one was willing to say that "*NIX
*DEFINITELY* outperforms Windows" for what my client is doing (or if it did
outperform Windows, that it would outperform so significantly that it
merited the move).

Was this incorrect? Can my client DEFINITELY expect a significant
improvement in performance for what he is doing?

DISK subsystem reports: SCSI/RAID Smart Array E200 controller using RAID 1.





-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: September 4, 2007 7:15 PM
To: Alvaro Herrera
Cc: Carlo Stonebanks; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Performance on 8CPU's and 32GB of RAM

On 9/4/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Carlo Stonebanks wrote:
> > A client is moving their postgresql db to a brand new Windows 2003 x64
> > server with 2 quad cores and 32GB of RAM. It is a dedicated server to
run
> > 8.2.4.
>
> Large shared_buffers and Windows do not mix.  Perhaps you should leave
> the shmem config low, so that the kernel can cache the file pages.

Egads, I'd completely missed the word Windows up there.

I would highly recommend building the postgresql server on a unixish
OS.  Even with minimum tuning, I'd expect the same box running linux
or freebsd to stomp windows pretty heavily in the performance
department.

But yeah, the I/O, that's the big one.  If it's just a single or a
couple of IDE drives, it's not gonna be able to handle much load.

On 9/5/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> Unfortunately, LINUX is not an option at this time. We looked into it; there
> is no *NIX expertise in the enterprise. However, I have raised this issue in
> various forums before, and when pressed no one was willing to say that "*NIX
> *DEFINITELY* outperforms Windows" for what my client is doing (or if it did
> outperform Windows, that it would outperform so significantly that it
> merited the move).

Where unixes generally outperform windows is in starting up new
backends, better file systems, and handling very large shared_buffer
settings.

> Was this incorrect? Can my client DEFINITELY expect a significant
> improvement in performance for what he is doing?

Depends on what you mean by incorrect.  Windows can do ok.  But pgsql
is still much newer on windows than on unix / linux and there are
still some issues that pop up here and there that are being worked on.
 Plus there's still no real definitive set of guidelines to tune on
Windows just yet.

> DISK subsystem reports: SCSI/RAID Smart Array E200 controller using RAID 1.

So, just two disks?  for the load you mentioned before, you should
probably be looking at at least 4 maybe 6 or 8 disks in a RAID-10.
And a battery backed cache.  I've seen reports on this list of the
E300 being a pretty mediocre performer.  A better controller might be
worth looking into as well.

> Right, additionally NTFS is really nothing to use on any serious disc
> array.

Do you mean that I will not see any big improvement if I upgrade the disk
subsystem because the client is using NTFS (i.e. Windows)

On 9/5/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> > Right, additionally NTFS is really nothing to use on any serious disc
> > array.
>
> Do you mean that I will not see any big improvement if I upgrade the disk
> subsystem because the client is using NTFS (i.e. Windows)

No, I think he's referring more to the lack of reliability of NTFS
compared to UFS / ZFS / JFS / XFS on unixen.

A faster disk subsystem will likely be a real help.

>> Large shared_buffers and Windows do not mix.  Perhaps you should leave
the shmem config low, so that the kernel can cache the file pages.
<<

Is there a problem BESIDES the one that used to cause windows to fail to
allocate memory in blocks larger than 1.5GB?

The symptom of this problem was that postgresql would just refuse to
restart. Microsoft released a patch for this problem and we can now start
postgresql with larger shared buffers. If this is indeed the problem that
you refer to - and it has indeed been solved by Microsoft - is there a down
side to this?


>> It sounds like you will need a huge lot of vacuuming effort to keep up.
Maybe you should lower autovac scale factors so that your tables are
visited more frequently.  A vacuum_delay of 40 sounds like too much
though.
<<

Does autovacuum not impede performance while it is vacuuming a table?

Carlo Stonebanks wrote:

> >> It sounds like you will need a huge lot of vacuuming effort to keep up.
> Maybe you should lower autovac scale factors so that your tables are
> visited more frequently.  A vacuum_delay of 40 sounds like too much
> though.
> <<
>
> Does autovacuum not impede performance while it is vacuuming a table?

It causes I/O.  Not sure what else you have in mind.  vacuum_delay
throttles the I/O usage, at the expense of longer vacuum times.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 9/5/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> >> Large shared_buffers and Windows do not mix.  Perhaps you should leave
> the shmem config low, so that the kernel can cache the file pages.
> <<
>
> Is there a problem BESIDES the one that used to cause windows to fail to
> allocate memory in blocks larger than 1.5GB?
>
> The symptom of this problem was that postgresql would just refuse to
> restart. Microsoft released a patch for this problem and we can now start
> postgresql with larger shared buffers. If this is indeed the problem that
> you refer to - and it has indeed been solved by Microsoft - is there a down
> side to this?

There have been some reports that performance-wise large shared buffer
settings don't work as well on windows as they do on linux / unix.
Don't know myself.  Just what I've read.

> >> It sounds like you will need a huge lot of vacuuming effort to keep up.
> Maybe you should lower autovac scale factors so that your tables are
> visited more frequently.  A vacuum_delay of 40 sounds like too much
> though.
> <<
>
> Does autovacuum not impede performance while it is vacuuming a table?

Of course vacuum impedes performance.  Depends on your I/O subsystem.
By adjusting your vacuum parameters in postgresql.conf, the impact can
be made pretty small.  But not vacuuming has a slow but sure
deteriorating effect over time.  So, it's generally better to let
autovacuum take care of things and run vacuum with a reasonable set of
parameters so it doesn't eat all your I/O bandwidth.

On 9/5/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On 9/5/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> > > Right, additionally NTFS is really nothing to use on any serious disc
> > > array.
> >
> > Do you mean that I will not see any big improvement if I upgrade the disk
> > subsystem because the client is using NTFS (i.e. Windows)
>
> No, I think he's referring more to the lack of reliability of NTFS
> compared to UFS / ZFS / JFS / XFS on unixen.

Lack of reliability compared to _UFS_?  Can you elaborate on this?

Trevor Talbot wrote:
>
> Lack of reliability compared to _UFS_?  Can you elaborate on this?

What elaboration's needed?   UFS seems to have one of the longest
histories of support from major vendors of any file system supported
on any OS (Solaris, HP-UX, SVR4, Tru64 Unix all use it).

Can you elaborate on your question?

On 9/5/07, Trevor Talbot <quension@gmail.com> wrote:
> On 9/5/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > On 9/5/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> > > > Right, additionally NTFS is really nothing to use on any serious disc
> > > > array.
> > >
> > > Do you mean that I will not see any big improvement if I upgrade the disk
> > > subsystem because the client is using NTFS (i.e. Windows)
> >
> > No, I think he's referring more to the lack of reliability of NTFS
> > compared to UFS / ZFS / JFS / XFS on unixen.
>
> Lack of reliability compared to _UFS_?  Can you elaborate on this?

Not a lot.  Back when I was an NT 4.0 sysadmin, I had many many
occasions where NTFS simply corrupted for no apparent reason.  No
system crash, no obvious problems with the drive, and bang suddenly a
file goes corrupted.  About that time I gave up on Windows and started
supporting Linux and Solaris.  Neither is perfect, but I've never had
either of them just corrupt a file on good hardware for no reason.
Keep in mind, the machine that was corrupting files for no reason went
on to be our development / staging linux server, handling quite a
heavy load of developers on it, and never once had a corrupted file on
it.

With the newer journalling file systems on linux, solaris and BSD, you
get both good performance and very reliable behaviour.  Maybe NTFS has
gotten better since then, but I don't personally know.

On 9/5/07, Trevor Talbot <quension@gmail.com> wrote:
> On 9/5/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > On 9/5/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> > > > Right, additionally NTFS is really nothing to use on any serious disc
> > > > array.
> > >
> > > Do you mean that I will not see any big improvement if I upgrade the disk
> > > subsystem because the client is using NTFS (i.e. Windows)
> >
> > No, I think he's referring more to the lack of reliability of NTFS
> > compared to UFS / ZFS / JFS / XFS on unixen.
>
> Lack of reliability compared to _UFS_?  Can you elaborate on this?

Oh, the other issue that NTFS still seems to suffer from that most
unix file systems have overcome is fragmentation.  Since you can't
defrag a live system, you have to plan time to take down the db should
the NTFS partition for your db get overly fragmented.

And there's the issue that with windows / NTFS that when one process
opens a file for read, it locks it for all other users.  This means
that things like virus scanners can cause odd, unpredictable failures
of your database.

On 9/5/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On 9/5/07, Trevor Talbot <quension@gmail.com> wrote:
> > On 9/5/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > > On 9/5/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> > > > > Right, additionally NTFS is really nothing to use on any serious disc
> > > > > array.
> > > >
> > > > Do you mean that I will not see any big improvement if I upgrade the disk
> > > > subsystem because the client is using NTFS (i.e. Windows)
> > >
> > > No, I think he's referring more to the lack of reliability of NTFS
> > > compared to UFS / ZFS / JFS / XFS on unixen.
> >
> > Lack of reliability compared to _UFS_?  Can you elaborate on this?

> Not a lot.  Back when I was an NT 4.0 sysadmin, I had many many
> occasions where NTFS simply corrupted for no apparent reason.  No
> system crash, no obvious problems with the drive, and bang suddenly a
> file goes corrupted.  About that time I gave up on Windows and started
> supporting Linux and Solaris.  Neither is perfect, but I've never had
> either of them just corrupt a file on good hardware for no reason.

Anecdotal then.  That's fine, but needs to be qualified as such, not
presented as a general case that everyone with experience agrees is
true.

I mean, I've got a box running OpenBSD UFS that's lost files on me,
while my NTFS boxes have been fine other than catastrophic drive
failure.  But that anecdote doesn't actually mean anything, since it's
useless in the general case.  (The issues on that one UFS box have a
known cause anyway, related to power failures.)

> With the newer journalling file systems on linux, solaris and BSD, you
> get both good performance and very reliable behaviour.  Maybe NTFS has
> gotten better since then, but I don't personally know.

The thing is, most UFS implementations I'm familiar with don't
journal; that's what prompted my question in the first place, since I
figured you were thinking along those lines.  NTFS is
metadata-journaling, like most of the others, and has continued to
improve over time.

I took the original comment to be about performance, actually.  NTFS's
journaling method tends to get bashed in that department compared to
some of the more modern filesystems.  I don't have any experience with
intensive I/O on large arrays to know.

Hopefully he'll clarify what he meant.

> Oh, the other issue that NTFS still seems to suffer from that most
> unix file systems have overcome is fragmentation.  Since you can't
> defrag a live system, you have to plan time to take down the db should
> the NTFS partition for your db get overly fragmented.

Live defragmentation has been supported since NT4, although Microsoft
never included tools or publicly documented it until 2000.  The NTFS
implementation in Windows doesn't make much effort to avoid
fragmentation, but that varies among implementations of the other
filesystems too.  Modern ones tend to be better at it.

> And there's the issue that with windows / NTFS that when one process
> opens a file for read, it locks it for all other users.  This means
> that things like virus scanners can cause odd, unpredictable failures
> of your database.

It's simply a Windows platform default for file I/O; there's no hard
limitation there, and it's not about a particular filesystem.  In the
case of antivirus vs database, it's more of an administrative issue:
configure the AV to ignore the database files, harass the AV vendor to
get programmers with clue, find another AV vendor, or just don't run
AV on your dedicated database server.

On 2007-09-05 Scott Marlowe wrote:
> And there's the issue that with windows / NTFS that when one process
> opens a file for read, it locks it for all other users.  This means
> that things like virus scanners can cause odd, unpredictable failures
> of your database.

Uh... what? Locking isn't done by the filesystem but by applications
(which certainly can decide to not lock a file when opening it). And no
one in his right mind would ever have a virus scanner access the files
of a running database, regardless of operating system or filesystem.

Regards
Angar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html

On 9/5/07, Ansgar -59cobalt- Wiechers <lists@planetcobalt.net> wrote:
> On 2007-09-05 Scott Marlowe wrote:
> > And there's the issue that with windows / NTFS that when one process
> > opens a file for read, it locks it for all other users.  This means
> > that things like virus scanners can cause odd, unpredictable failures
> > of your database.
>
> Uh... what? Locking isn't done by the filesystem but by applications
> (which certainly can decide to not lock a file when opening it). And no
> one in his right mind would ever have a virus scanner access the files
> of a running database, regardless of operating system or filesystem.

Exactly, the default is to lock the file.  The application has to
explicitly NOT lock it.  It's the opposite of linux.

And be careful, you're insulting a LOT of people who have come on this
list with the exact problem of having their anti-virus scramble the
brain of their postgresql installation.  It's a far more common
problem than it should be.

On Wed, 2007-09-05 at 14:36 -0500, Scott Marlowe wrote:
> On 9/5/07, Trevor Talbot <quension@gmail.com> wrote:
> > On 9/5/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > > On 9/5/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> > > > > Right, additionally NTFS is really nothing to use on any serious disc
> > > > > array.
> > > > Do you mean that I will not see any big improvement if I upgrade the disk
> > > > subsystem because the client is using NTFS (i.e. Windows)

I haven't had a corrupt NTFS filesystem is ages; even with hardware
failures.  If NTFS was inherently unstable there wouldn't be hundreds of
thousands of large M$-SQL and Exchange instances.

> And there's the issue that with windows / NTFS that when one process
> opens a file for read, it locks it for all other users.

This isn't true;  the mode of a file open is up to the application.
Possibly lots of Windows applications are stupid or sloppy in how they
manage files but that isn't a flaw in NTFS.

--
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org

On 2007-09-05 Scott Marlowe wrote:
> On 9/5/07, Ansgar -59cobalt- Wiechers <lists@planetcobalt.net> wrote:
>> On 2007-09-05 Scott Marlowe wrote:
>>> And there's the issue that with windows / NTFS that when one process
>>> opens a file for read, it locks it for all other users.  This means
>>> that things like virus scanners can cause odd, unpredictable
>>> failures of your database.
>>
>> Uh... what? Locking isn't done by the filesystem but by applications
>> (which certainly can decide to not lock a file when opening it). And
>> no one in his right mind would ever have a virus scanner access the
>> files of a running database, regardless of operating system or
>> filesystem.
>
> Exactly, the default is to lock the file.  The application has to
> explicitly NOT lock it.  It's the opposite of linux.

Yes. So? It's still up to the application, and it still has nothing at
all to do with the filesystem.

> And be careful, you're insulting a LOT of people who have come on this
> list with the exact problem of having their anti-virus scramble the
> brain of their postgresql installation.  It's a far more common
> problem than it should be.

How does that make it any less stup^Wintellectually challenged?

Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html

On 9/5/07, Ansgar -59cobalt- Wiechers <lists@planetcobalt.net> wrote:
> On 2007-09-05 Scott Marlowe wrote:
> > On 9/5/07, Ansgar -59cobalt- Wiechers <lists@planetcobalt.net> wrote:
> >> On 2007-09-05 Scott Marlowe wrote:
> >>> And there's the issue that with windows / NTFS that when one process
> >>> opens a file for read, it locks it for all other users.  This means
> >>> that things like virus scanners can cause odd, unpredictable
> >>> failures of your database.
> >>
> >> Uh... what? Locking isn't done by the filesystem but by applications
> >> (which certainly can decide to not lock a file when opening it). And
> >> no one in his right mind would ever have a virus scanner access the
> >> files of a running database, regardless of operating system or
> >> filesystem.
> >
> > Exactly, the default is to lock the file.  The application has to
> > explicitly NOT lock it.  It's the opposite of linux.
>
> Yes. So? It's still up to the application, and it still has nothing at
> all to do with the filesystem.

And if you look at my original reply, you'll see that I said WINDOWS /
NTFS.  not just NTFS.  i.e. it's a windowsism.

>
> > And be careful, you're insulting a LOT of people who have come on this
> > list with the exact problem of having their anti-virus scramble the
> > brain of their postgresql installation.  It's a far more common
> > problem than it should be.
>
> How does that make it any less stup^Wintellectually challenged?

It doesn't.  It's just not necessary to insult people to make a point.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Carlo Stonebanks wrote:
> Unfortunately, LINUX is not an option at this time. We looked into it; there
> is no *NIX expertise in the enterprise. However, I have raised this issue in
> various forums before, and when pressed no one was willing to say that "*NIX
> *DEFINITELY* outperforms Windows" for what my client is doing (or if it did
> outperform Windows, that it would outperform so significantly that it
> merited the move).
>
> Was this incorrect? Can my client DEFINITELY expect a significant
> improvement in performance for what he is doing?
>
> DISK subsystem reports: SCSI/RAID Smart Array E200 controller using RAID 1.
>

Based on the hardware config you mention, it sounds to me as if you put
all your money in the wrong basket (e.g; way too much ram and cpu / not
enough IO).

Sincerely,

Joshua D. Drake



- --

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG32r5ATb/zqfZUUQRAkAgAJ97aaOJZBbf8PobFjWs2v2fPh67PQCfeDVF
mU6DA7mb3XfWDlpRsOfLi0U=
=t7b9
-----END PGP SIGNATURE-----

On 2007-09-05 Scott Marlowe wrote:
> On 9/5/07, Ansgar -59cobalt- Wiechers <lists@planetcobalt.net> wrote:
>> On 2007-09-05 Scott Marlowe wrote:
>>> On 9/5/07, Ansgar -59cobalt- Wiechers <lists@planetcobalt.net> wrote:
>>>> On 2007-09-05 Scott Marlowe wrote:
>>>>> And there's the issue that with windows / NTFS that when one
>>>>> process opens a file for read, it locks it for all other users.
>>>>> This means that things like virus scanners can cause odd,
>>>>> unpredictable failures of your database.
>>>>
>>>> Uh... what? Locking isn't done by the filesystem but by
>>>> applications (which certainly can decide to not lock a file when
>>>> opening it). And no one in his right mind would ever have a virus
>>>> scanner access the files of a running database, regardless of
>>>> operating system or filesystem.
>>>
>>> Exactly, the default is to lock the file.  The application has to
>>> explicitly NOT lock it.  It's the opposite of linux.
>>
>> Yes. So? It's still up to the application, and it still has nothing
>> at all to do with the filesystem.
>
> And if you look at my original reply, you'll see that I said WINDOWS /
> NTFS.  not just NTFS.  i.e. it's a windowsism.

I am aware of what you wrote. However, since the locking behaviour is
exactly the same with Windows/FAT32 or Windows/%ANY_OTHER_FILESYSTEM%
your statement is still wrong.

Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html

Scott Marlowe wrote:
> And there's the issue that with windows / NTFS that when one process
> opens a file for read, it locks it for all other users.  This means
> that things like virus scanners can cause odd, unpredictable failures
> of your database.
>
>
Can you provide some justification for this?

James

Scott Marlowe wrote:
> Where unixes generally outperform windows is in starting up new
> backends, better file systems, and handling very large shared_buffer
> settings.
>

Why do you think that UNIX systems are better at handling large shared
buffers than Wndows?
32 bit Windows systems can suffer from fragmented address space, to be
sure, but if the
performance of the operating-system supplied mutex or semaphore isn't
good enough, you can
just use the raw atomic ops.

If what you mean is that pg has a design that's heavily oriented towards
things that tend to
be cheap on POSIX and doesn't use the core Win32 features effectively,
then let's track
that as an optimisation opportunity for the Win32 port.

On 9/6/07, James Mansion <james@mansionfamily.plus.com> wrote:
> Scott Marlowe wrote:
> > And there's the issue that with windows / NTFS that when one process
> > opens a file for read, it locks it for all other users.  This means
> > that things like virus scanners can cause odd, unpredictable failures
> > of your database.
> >
> >
> Can you provide some justification for this?

Seeing as I didn't write Windows or any of the plethora of anti-virus
software, no I really can't. It's unforgivable behaviour.

Can I provide evidence that it happens?  Just read the archives of
this list for the evidence.  I've seen it often enough to know that
most anti-virus software seems to open files in exclusive mode and
cause problems for postgresql, among other apps.


> Why do you think that UNIX systems are better at handling large shared
> buffers than Wndows?

Because we've seen lots of problems with large shared buffers on windows here.

Now, maybe for a windows specific app it's all fine and dandy.  but
for the way pgsql works, windows and large shared buffers don't seem
to get along.

I'm done.  Use windows all you want.  I'll stick to unix.  It seems to
just work for pgsql.

James Mansion escribió:

> If what you mean is that pg has a design that's heavily oriented
> towards things that tend to be cheap on POSIX and doesn't use the core
> Win32 features effectively, then let's track that as an optimisation
> opportunity for the Win32 port.

Already done for 8.3 (actual performance improvements still to be
reported), but that doesn't help those poor users still on 8.2.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Los dioses no protegen a los insensatos.  Éstos reciben protección de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)

Wow - it's nice to hear someone say that... out loud.

Thanks, you gave me hope!

-----Original Message-----
From: James Mansion [mailto:james@mansionfamily.plus.com]
Sent: September 6, 2007 4:55 PM
To: Carlo Stonebanks
Cc: Scott Marlowe; Alvaro Herrera; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Performance on 8CPU's and 32GB of RAM

Carlo Stonebanks wrote:
> Isn't it just easier to assume that Windows Server can't do anything
right?
> ;-)
>
>
Well, avoiding the ;-) - people do, and its remarkably foolish of them.  Its
a long-standing whinge that many people with a UNIX-background seem to
just assume that Windows sucks, but you could run 40,000 sockets from a
single Win32 process for a while and some well-known UNIX systems would
still struggle to do this, libevent or no.  Admitedly, the way a Win32
app is architected would be rather different from a typical POSIX one.

Windows has been a cheap target bt its remarkably adequate and the
TPC results speak for themselves.

<<
If what you mean is that pg has a design that's heavily oriented towards
things that tend to
be cheap on POSIX and doesn't use the core Win32 features effectively,
then let's track
that as an optimisation opportunity for the Win32 port.
>>

Isn't it just easier to assume that Windows Server can't do anything right?
;-)

Carlo Stonebanks wrote:
> Isn't it just easier to assume that Windows Server can't do anything right?
> ;-)
>
>
Well, avoiding the ;-) - people do, and its remarkably foolish of them.  Its
a long-standing whinge that many people with a UNIX-background seem to
just assume that Windows sucks, but you could run 40,000 sockets from a
single Win32 process for a while and some well-known UNIX systems would
still struggle to do this, libevent or no.  Admitedly, the way a Win32
app is architected would be rather different from a typical POSIX one.

Windows has been a cheap target bt its remarkably adequate and the
TPC results speak for themselves.

* Scott Marlowe:

> And there's the issue that with windows / NTFS that when one process
> opens a file for read, it locks it for all other users.  This means
> that things like virus scanners can cause odd, unpredictable failures
> of your database.

I think most of them open the file in shared/backup mode.  The only
lock that is created by that guards deletion and renaming.  It can
still lead to obscure failures, but it's not a wholly-eclusive lock.

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

On 9/7/07, Florian Weimer <fweimer@bfk.de> wrote:
> * Scott Marlowe:
>
> > And there's the issue that with windows / NTFS that when one process
> > opens a file for read, it locks it for all other users.  This means
> > that things like virus scanners can cause odd, unpredictable failures
> > of your database.
>
> I think most of them open the file in shared/backup mode.  The only
> lock that is created by that guards deletion and renaming.  It can
> still lead to obscure failures, but it's not a wholly-eclusive lock.

Well, there've been a lot of issues with anti-virus and postgresql not
getting along.  I wonder if pgsql takes out a stronger lock, and when
it can't get it then the failure happens.  Not familiar enough with
windows to do more than speculate.

On Wed, Sep 05, 2007 at 11:06:03AM -0400, Carlo Stonebanks wrote:
> Unfortunately, LINUX is not an option at this time. We looked into it; there
> is no *NIX expertise in the enterprise. However, I have raised this issue in
> various forums before, and when pressed no one was willing to say that "*NIX
> *DEFINITELY* outperforms Windows" for what my client is doing (or if it did
> outperform Windows, that it would outperform so significantly that it
> merited the move).
>
> Was this incorrect? Can my client DEFINITELY expect a significant
> improvement in performance for what he is doing?

Since we don't know your actual workload, there's no way to predict
this. That's what benchmarking is for. If you haven't already bought the
hardware, I'd strongly recommend benchmarking this before buying
anything, so that you have a better idea of what your workload looks
like. Is it I/O-bound? CPU-bound? Memory?

One of the fastest ways to non-performance in PostgreSQL is not
vacuuming frequently enough. Vacuum more, not less, and control IO
impact via vacuum_cost_delay. Make sure the FSM is big enough, too.

Unless your database is small enough to fit in-memory, your IO subsystem
is almost certainly going to kill you. Even if it does fit in memory, if
you're doing much writing at all you're going to be in big trouble.
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)