Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpvMfqORWRv7.pgp Description: PGP signature
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/7/07, Florian Weimer [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
Scott, 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. without touching the file-concurrency issues caused by virus scanners: a LOT of the Postgres - VirusScanner problems on Windows were caused during the postgres spawns a new process and communicates with that process via ipstack Many Virus Scanners seam to have dealt with the TCP/IP stack in a not compatible manner... Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
* 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[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 2007-09-05 Scott Marlowe wrote: On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] wrote: On 2007-09-05 Scott Marlowe wrote: On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/6/07, James Mansion [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
Wow - it's nice to hear someone say that... out loud. Thanks, you gave me hope! -Original Message- From: James Mansion [mailto:[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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? ;-) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 05.09.2007 01:15, Scott Marlowe wrote: On 9/4/07, Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Trevor Talbot [EMAIL PROTECTED] wrote: On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Trevor Talbot [EMAIL PROTECTED] wrote: On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 9/5/07, Trevor Talbot [EMAIL PROTECTED] wrote: On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On Wed, 2007-09-05 at 14:36 -0500, Scott Marlowe wrote: On 9/5/07, Trevor Talbot [EMAIL PROTECTED] wrote: On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 2007-09-05 Scott Marlowe wrote: On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] wrote: On 2007-09-05 Scott Marlowe wrote: On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
-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- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/4/07, Carlo Stonebanks [EMAIL PROTECTED] 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 = 10 work_mem = 100 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) 1 would be much more reasonable. OTOH, shared_buffers, at 10 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 10... 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/4/07, Alvaro Herrera [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
