Re: [PERFORM] Hardware recommendations
-Original Message- From: Greg Smith [mailto:g...@2ndquadrant.com] Sent: Saturday, December 11, 2010 2:18 AM To: Benjamin Krajmalnik Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Hardware recommendations What sort of total read/write rates are you seeing when iostat is showing the system 85% busy? That's a useful number to note as an estimate of just how random the workload is. I did a vacuum full of the highly bloated, constantly accessed tables, which has improved the situation significantly. I am not seeing over 75% busy right now, but these are some values for the high busy presently: 71% 344 w/s 7644 kw/s 81% 392 w/s 8880 kw/s 79% 393 w/s 9526 kw/s 75% 443 w/s 10245 kw/s 80% 436 w/s 10157 kw/s 76% 392 w/s 8438 kw/s Have you increased checkpoint parameters like checkpoint_segments? You need to avoid having checkpoints too often if you're going to try and use 4GB of memory for shared_buffers. Yes, I have it configured at 1024 checkpoint_segments, 5min timeout,0.9 compiostat -x 5letion_target It's nice to put the logs onto a separate disk because it lets you measure exactly how much I/O is going to them, relative to the database. It's not really necessary though; with 14 disks you'll be at the range where you can mix them together and things should still be fine. Thx. I will place them in their own RAID1 (or mirror if I end up going to ZFS) On the processor front, are there advantages to going to X series processors as opposed to the E series (especially since I am I/O bound)? Is anyone running this type of hardware, specially on FreeBSD? Any opinions, especially concerning the Areca controllers which they use? It sounds like you should be saving your hardware dollars for more RAM and disks, not getting faster procesors. The Areca controllers are fast and pretty reliable under Linux. I'm not aware of anyone using them for PostgreSQL in production on FreeBSD. Aberdeen may have enough customers doing that to give you a good opinion on how stable that is likely to be; they're pretty straight as vendors go. You'd want to make sure to stress test that hardware/software combo as early as possible regardless, it's generally a good idea and you wouldn't be running a really popular combination. Thx. That was my overall plan - that's why I am opting for drives, cache on the controller, and memory. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Supportwww.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Benjamin Krajmalnik Sent: Monday, December 13, 2010 1:45 PM To: Greg Smith Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Hardware recommendations -Original Message- From: Greg Smith [mailto:g...@2ndquadrant.com] Sent: Saturday, December 11, 2010 2:18 AM To: Benjamin Krajmalnik Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Hardware recommendations Have you increased checkpoint parameters like checkpoint_segments? You need to avoid having checkpoints too often if you're going to try and use 4GB of memory for shared_buffers. Yes, I have it configured at 1024 checkpoint_segments, 5min timeout,0.9 compiostat -x 5letion_target I would consider bumping that checkpoint timeout duration to a bit longer and see if that helps any if you are still looking for knobs to fiddle with. YMMV. -Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
John W Strange wrote: http://www.fusionio.com/products/iodrive/ - BEST in slot currently IMHO. http://www.intel.com/design/flash/nand/extreme/index.htm?wapkw=(X25-E) - not a bad alternative. The FusionIO drives are OK, so long as you don't mind the possibility that your system will be down for 15 minutes after any unexpected crash. They can do a pretty time consuming verification process on the next boot if you didn't shut the server down properly before mounting. Intel's drives have been so bad about respecting OS cache flush calls that I can't recommend them for any PostgreSQL use, due to their tendency for the database to get corrupted in the same sort of post-crash situation. See http://wiki.postgresql.org/wiki/Reliable_Writes for more background. If you get one of the models that allows being setup for reliability, those are so slow that's not even worth the trouble. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Supportwww.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
Benjamin Krajmalnik wrote: I am already having serious I/O bottlenecks with iostat -x showing extended periods where the disk subsystem on the data partition (the one with all the random i/o) at over 85% busy. The system is running FreeBSD 7.2 amd64 and PostgreSQL 8.4.4 on amd64-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit. Currently I have about 4GB of shared memory allocated to PostgreSQL. Database is currently about 80GB, with about 60GB being in partitioned tables which get rotated nightly to purge old data (sort of like a circular buffer of historic data). What sort of total read/write rates are you seeing when iostat is showing the system 85% busy? That's a useful number to note as an estimate of just how random the workload is. Have you increased checkpoint parameters like checkpoint_segments? You need to avoid having checkpoints too often if you're going to try and use 4GB of memory for shared_buffers. I was looking at one of the machines which Aberdeen has (the X438), and was planning on something along the lines of 96GB RAM with 16 SAS drives (15K). If I create a RAID 10 (stripe of mirrors), leaving 2 hot spares, should I still place the logs in a separate RAID-1 mirror, or can they be left on the same RAID-10 container? It's nice to put the logs onto a separate disk because it lets you measure exactly how much I/O is going to them, relative to the database. It's not really necessary though; with 14 disks you'll be at the range where you can mix them together and things should still be fine. On the processor front, are there advantages to going to X series processors as opposed to the E series (especially since I am I/O bound)? Is anyone running this type of hardware, specially on FreeBSD? Any opinions, especially concerning the Areca controllers which they use? It sounds like you should be saving your hardware dollars for more RAM and disks, not getting faster procesors. The Areca controllers are fast and pretty reliable under Linux. I'm not aware of anyone using them for PostgreSQL in production on FreeBSD. Aberdeen may have enough customers doing that to give you a good opinion on how stable that is likely to be; they're pretty straight as vendors go. You'd want to make sure to stress test that hardware/software combo as early as possible regardless, it's generally a good idea and you wouldn't be running a really popular combination. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Supportwww.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
We use ZFS and use SSDs for both the log device and L2ARC. All disks and SSDs are behind a 3ware with BBU in single disk mode. Out of curiosity why do you put your log on SSD? Log is all sequential IOs, an area in which SSD is not any faster than HDD. So I'd think putting log on SSD wouldn't give you any performance boost. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
On 10-12-2010 14:58 Andy wrote: We use ZFS and use SSDs for both the log device and L2ARC. All disks and SSDs are behind a 3ware with BBU in single disk mode. Out of curiosity why do you put your log on SSD? Log is all sequential IOs, an area in which SSD is not any faster than HDD. So I'd think putting log on SSD wouldn't give you any performance boost. The common knowledge you based that comment on, may actually not be very up-to-date anymore. Current consumer-grade SSD's can achieve up to 200MB/sec when writing sequentially and they can probably do that a lot more consistent than a hard disk. Have a look here: http://www.anandtech.com/show/2829/21 The sequential writes-graphs consistently put several SSD's at twice the performance of the VelociRaptor 300GB 10k rpm disk and that's a test from over a year old, current SSD's have increased in performance, whereas I'm not so sure there was much improvement in platter based disks lately? Apart from that, I'd guess that log-devices benefit from reduced latencies. Its actually the recommended approach from Sun to add a pair of (small SLC-based) ssd log devices to increase performance (especially for nfs-scenario's where a lot of synchonous writes occur) and they offer it as an option for most of their Unified Storage appliances. Best regards, Arjen -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
On 10-12-2010 18:57 Arjen van der Meijden wrote: Have a look here: http://www.anandtech.com/show/2829/21 The sequential writes-graphs consistently put several SSD's at twice the performance of the VelociRaptor 300GB 10k rpm disk and that's a test from over a year old, current SSD's have increased in performance, whereas I'm not so sure there was much improvement in platter based disks lately? Here's a more recent test: http://www.anandtech.com/show/4020/ocz-vertex-plus-preview-introducing-the-indilinx-martini/3 That shows several consumer grade SSD's and a 600GB VelociRaptor, its 200+ vs 140MB/sec. I'm not sure how recent 15k rpm sas disks would do, nor do I know how recent server grade SSD's would behave. But if we assume similar gains for both, its still in favor of SSD's :-) Best regards, Arjen -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
On Fri, Dec 10, 2010 at 11:05 AM, Arjen van der Meijden acmmail...@tweakers.net wrote: On 10-12-2010 18:57 Arjen van der Meijden wrote: Have a look here: http://www.anandtech.com/show/2829/21 The sequential writes-graphs consistently put several SSD's at twice the performance of the VelociRaptor 300GB 10k rpm disk and that's a test from over a year old, current SSD's have increased in performance, whereas I'm not so sure there was much improvement in platter based disks lately? Here's a more recent test: http://www.anandtech.com/show/4020/ocz-vertex-plus-preview-introducing-the-indilinx-martini/3 That shows several consumer grade SSD's and a 600GB VelociRaptor, its 200+ vs 140MB/sec. I'm not sure how recent 15k rpm sas disks would do, nor do I know how recent server grade SSD's would behave. But if we assume similar gains for both, its still in favor of SSD's :-) The latest Seagate Cheetahs (15k.7) can do 122 to 204 depending on what part of the drive you're writing to. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
The common knowledge you based that comment on, may actually not be very up-to-date anymore. Current consumer-grade SSD's can achieve up to 200MB/sec when writing sequentially and they can probably do that a lot more consistent than a hard disk. Have a look here: http://www.anandtech.com/show/2829/21 The sequential writes-graphs consistently put several SSD's at twice the performance of the VelociRaptor 300GB 10k rpm disk and that's a test from over a year old, current SSD's have increased in performance, whereas I'm not so sure there was much improvement in platter based disks lately? The sequential IO performance of SSD may be twice faster than HDD, but the random IO performance of SSD is at least an order of magnitude faster. I'd think it'd make more sense to take advantage of SSD's greatest strength, which is random IO. The same website you linked, anandtech, also benchmarked various configurations of utilizing SSD: http://www.anandtech.com/show/2739/11 According to their benchmarks putting logs on SSD results in no performance improvements, while putting data on SSD leads to massive improvement. They used MySQL for the benchmarks. So perhaps Postgresql is different in this regard? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
On Thu, Dec 9, 2010 at 04:28, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Dec 8, 2010 at 5:03 PM, Benjamin Krajmalnik k...@servoyant.com wrote: My biggest concern with SSD drives is their life expectancy, Generally that's not a big issue, especially as the SSDs get larger. Being able to survive a power loss without corruption is more of an issue, so if you go SSD get ones with a supercapacitor that can write out the data before power down. I agree with Benjamin here. Even if you put multiple SSD drives into a RAID array, all the drives get approximately the same write load and thus will likely wear out and fail at the same time! As for the Areca controllers, I haven't tested them with the latest drivers or firmware, but we would routinely get 180 to 460 days of uptime between lockups That sucks! But does a BBU even help with SSDs? The flash eraseblock is larger than the RAID cache unit size anyway, so as far as I can tell, it might not save you in the case of a power loss. Any thoughts whether software RAID on SSD is a good idea? Regards, Marti -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
If you are worried about wearing out the SSD's long term get a larger SSD and create the partition smaller than the disk, this will reduce the write amplification and extend the life of the drive. TRIM support also helps lower write amplification issues by not requiring as many pages to do the writes, and improve performance as well! As a test I bought 4 cheap 40GB drives in a raid0 software stripe, I have run it for almost a year now with a lot of random IO. I portioned them as 30GB drives, leaving an extra 25% spare area to reduce the write amplification, I can still get over 600MB/sec on these for a whopping cost of $400 and a little of my time. SSD's can be very useful, but you have to be aware of the shortcomings and how to overcome them. - John -Original Message- From: Marti Raudsepp [mailto:ma...@juffo.org] Sent: Thursday, December 09, 2010 6:09 AM To: Scott Marlowe Cc: Benjamin Krajmalnik; John W Strange; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Hardware recommendations On Thu, Dec 9, 2010 at 04:28, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Dec 8, 2010 at 5:03 PM, Benjamin Krajmalnik k...@servoyant.com wrote: My biggest concern with SSD drives is their life expectancy, Generally that's not a big issue, especially as the SSDs get larger. Being able to survive a power loss without corruption is more of an issue, so if you go SSD get ones with a supercapacitor that can write out the data before power down. I agree with Benjamin here. Even if you put multiple SSD drives into a RAID array, all the drives get approximately the same write load and thus will likely wear out and fail at the same time! As for the Areca controllers, I haven't tested them with the latest drivers or firmware, but we would routinely get 180 to 460 days of uptime between lockups That sucks! But does a BBU even help with SSDs? The flash eraseblock is larger than the RAID cache unit size anyway, so as far as I can tell, it might not save you in the case of a power loss. Any thoughts whether software RAID on SSD is a good idea? Regards, Marti This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice. Any comments or statements made herein do not necessarily reflect those of JPMorgan Chase Co., its subsidiaries and affiliates. This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. Although this transmission and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by JPMorgan Chase Co., its subsidiaries and affiliates, as applicable, for any loss or damage arising in any way from its use. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. Please refer to http://www.jpmorgan.com/pages/disclosures for disclosures relating to European legal entities. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
On Wed, Dec 8, 2010 at 3:03 PM, Benjamin Krajmalnik k...@servoyant.com wrote: I need to build a new high performance server to replace our current production database server. We run FreeBSD 8.1 with PG 8.4 (soon to upgrade to PG 9). Hardware is: Supermicro 2u 6026T-NTR+ 2x Intel Xeon E5520 Nehalem 2.26GHz Quad-Core (8 cores total), 48GB RAM We use ZFS and use SSDs for both the log device and L2ARC. All disks and SSDs are behind a 3ware with BBU in single disk mode. This has given us the capacity of the spinning disks with (mostly) the performance of the SSDs. The main issue we've had is that if the server is rebooted performance is horrible for a few minutes until the various memory and ZFS caches are warmed up. Luckily, that doesn't happen very often. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Hardware recommendations
I need to build a new high performance server to replace our current production database server. The current server is a SuperMicro 1U with 2 RAID-1 containers (one for data, one for log, SAS - data is 600GB, Logs 144GB), 16GB of RAM, running 2 quad core processors (E5405 @ 2GHz), Adaptec 5405 Controller with BBU. I am already having serious I/O bottlenecks with iostat -x showing extended periods where the disk subsystem on the data partition (the one with all the random i/o) at over 85% busy. The system is running FreeBSD 7.2 amd64 and PostgreSQL 8.4.4 on amd64-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit. Currently I have about 4GB of shared memory allocated to PostgreSQL. Database is currently about 80GB, with about 60GB being in partitioned tables which get rotated nightly to purge old data (sort of like a circular buffer of historic data). I was looking at one of the machines which Aberdeen has (the X438), and was planning on something along the lines of 96GB RAM with 16 SAS drives (15K). If I create a RAID 10 (stripe of mirrors), leaving 2 hot spares, should I still place the logs in a separate RAID-1 mirror, or can they be left on the same RAID-10 container? On the processor front, are there advantages to going to X series processors as opposed to the E series (especially since I am I/O bound)? Is anyone running this type of hardware, specially on FreeBSD? Any opinions, especially concerning the Areca controllers which they use? The new box would ideally be built with the latest released version of FreeBSD, PG 9.x. Also, is anyone running the 8.x series of FreeBSD with PG 9 in a high throughput production environment? I will be upgrading one of our test servers in one week to this same configuration to test out, but just wanted to make sure there aren't any caveats others have experienced, especially as it pertains with the autovacuum not launching worker processes which I have experienced. Best regards, Benjamin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
If you are IO-bound, you might want to consider using SSD. A single SSD could easily give you more IOPS than 16 15k SAS in RAID 10. --- On Wed, 12/8/10, Benjamin Krajmalnik k...@servoyant.com wrote: From: Benjamin Krajmalnik k...@servoyant.com Subject: [PERFORM] Hardware recommendations To: pgsql-performance@postgresql.org Date: Wednesday, December 8, 2010, 6:03 PM I need to build a new high performance server to replace our current production database server. The current server is a SuperMicro 1U with 2 RAID-1 containers (one for data, one for log, SAS - data is 600GB, Logs 144GB), 16GB of RAM, running 2 quad core processors (E5405 @ 2GHz), Adaptec 5405 Controller with BBU. I am already having serious I/O bottlenecks with iostat -x showing extended periods where the disk subsystem on the data partition (the one with all the random i/o) at over 85% busy. The system is running FreeBSD 7.2 amd64 and PostgreSQL 8.4.4 on amd64-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit. Currently I have about 4GB of shared memory allocated to PostgreSQL. Database is currently about 80GB, with about 60GB being in partitioned tables which get rotated nightly to purge old data (sort of like a circular buffer of historic data). I was looking at one of the machines which Aberdeen has (the X438), and was planning on something along the lines of 96GB RAM with 16 SAS drives (15K). If I create a RAID 10 (stripe of mirrors), leaving 2 hot spares, should I still place the logs in a separate RAID-1 mirror, or can they be left on the same RAID-10 container? On the processor front, are there advantages to going to X series processors as opposed to the E series (especially since I am I/O bound)? Is anyone running this type of hardware, specially on FreeBSD? Any opinions, especially concerning the Areca controllers which they use? The new box would ideally be built with the latest released version of FreeBSD, PG 9.x. Also, is anyone running the 8.x series of FreeBSD with PG 9 in a high throughput production environment? I will be upgrading one of our test servers in one week to this same configuration to test out, but just wanted to make sure there aren't any caveats others have experienced, especially as it pertains with the autovacuum not launching worker processes which I have experienced. Best regards, Benjamin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
Sent from my android device. -Original Message- From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org Sent: Wed, 08 Dec 2010 17:14 Subject: [PERFORM] Hardware recommendations Received: from mx2.hub.org [200.46.204.254] by mail.pengdows.com with SMTP (EHLO mx2.hub.org) (ArGoSoft Mail Server Pro for WinNT/2000/XP, Version 1.8 (1.8.9.4)); Wed, 8 Dec 2010 23:14:07 Received: from postgresql.org (mail.postgresql.org [200.46.204.86]) by mx2.hub.org (Postfix) with ESMTP id C1EAD3EAD610; Wed, 8 Dec 2010 19:16:09 -0400 (AST) Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BEF461337B83 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 19:16:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69961-09 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 23:15:55 + (UTC) X-Greylist: delayed 00:12:11.193596 by SQLgrey-1.7.6 Received: from mail.illumen.com (unknown [64.207.29.137]) by mail.postgresql.org (Postfix) with ESMTP id 69A021337B8C for pgsql-performance@postgresql.org; Wed, 8 Dec 2010 19:15:55 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Subject: [PERFORM] Hardware recommendations Date: Wed, 8 Dec 2010 16:03:43 -0700 Message-ID: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local In-Reply-To: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Hardware recommendations Thread-Index: AcuXJy2x5aJ1UxfPTAK6bTXXH/raOgAABuAQ References: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits.107 tagged_above0 required=5 testsºYES_00.9, RDNS_NONE=0.793 X-Spam-Level: X-Mailing-List: pgsql-performance List-Archive: http://archives.postgresql.org/pgsql-performance List-Help: mailto:majord...@postgresql.org?body=help List-ID: pgsql-performance.postgresql.org List-Owner: mailto:pgsql-performance-ow...@postgresql.org List-Post: mailto:pgsql-per
Re: [PERFORM] Hardware recommendations
John, The platform is a network monitoring system, so we have quite a lot of inserts/updates (every data point has at least one record insert as well as at least 3 record updates). The management GUI has a lot of selects. We are refactoring the database to some degree to aid in the performance, since the performance degradations are correlated to the number of users viewing the system GUI. My biggest concern with SSD drives is their life expectancy, as well as our need for relatively high capacity. From a purely scalability perspective, this setup will need to support terabytes of data. I suppose I could use table spaces to use the most accessed data in SSD drives and the rest on regular drives. As I stated, I am moving to RAID 10, and was just wondering if the logs should still be moved off to different spindles, or will leaving them on the RAID10 be fine and not affect performance. -Original Message- From: John W Strange [mailto:john.w.stra...@jpmchase.com] Sent: Wednesday, December 08, 2010 4:32 PM To: Benjamin Krajmalnik; pgsql-performance@postgresql.org Subject: RE: Hardware recommendations Ben, It would help if you could tell us a bit more about the read/write mix and transaction requirements. *IF* you are heavy writes I would suggest moving off the RAID1 configuration to a RAID10 setup. I would highly suggest looking at SLC based solid state drives or if your budget has legs, look at fusionIO drives. We currently have several setups with two FusionIO Duo cards that produce 2GB second reads, and over 1GB/sec writes. They are pricey but, long term cheaper for me than putting SAN in place that can meet that sort of performance. It all really depends on your workload: http://www.fusionio.com/products/iodrive/ - BEST in slot currently IMHO. http://www.intel.com/design/flash/nand/extreme/index.htm?wapkw=(X25-E) - not a bad alternative. There are other SSD controllers on the market but I have experience with both so I can recommend both as well. - John -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Benjamin Krajmalnik Sent: Wednesday, December 08, 2010 5:04 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Hardware recommendations I need to build a new high performance server to replace our current production database server. The current server is a SuperMicro 1U with 2 RAID-1 containers (one for data, one for log, SAS - data is 600GB, Logs 144GB), 16GB of RAM, running 2 quad core processors (E5405 @ 2GHz), Adaptec 5405 Controller with BBU. I am already having serious I/O bottlenecks with iostat -x showing extended periods where the disk subsystem on the data partition (the one with all the random i/o) at over 85% busy. The system is running FreeBSD 7.2 amd64 and PostgreSQL 8.4.4 on amd64-portbld- freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit. Currently I have about 4GB of shared memory allocated to PostgreSQL. Database is currently about 80GB, with about 60GB being in partitioned tables which get rotated nightly to purge old data (sort of like a circular buffer of historic data). I was looking at one of the machines which Aberdeen has (the X438), and was planning on something along the lines of 96GB RAM with 16 SAS drives (15K). If I create a RAID 10 (stripe of mirrors), leaving 2 hot spares, should I still place the logs in a separate RAID-1 mirror, or can they be left on the same RAID-10 container? On the processor front, are there advantages to going to X series processors as opposed to the E series (especially since I am I/O bound)? Is anyone running this type of hardware, specially on FreeBSD? Any opinions, especially concerning the Areca controllers which they use? The new box would ideally be built with the latest released version of FreeBSD, PG 9.x. Also, is anyone running the 8.x series of FreeBSD with PG 9 in a high throughput production environment? I will be upgrading one of our test servers in one week to this same configuration to test out, but just wanted to make sure there aren't any caveats others have experienced, especially as it pertains with the autovacuum not launching worker processes which I have experienced. Best regards, Benjamin -- Sent via pgsql-performance mailing list (pgsql- performa...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice. Any comments or statements made herein do not necessarily reflect those of JPMorgan Chase
Re: [PERFORM] Hardware recommendations
Ben, It would help if you could tell us a bit more about the read/write mix and transaction requirements. *IF* you are heavy writes I would suggest moving off the RAID1 configuration to a RAID10 setup. I would highly suggest looking at SLC based solid state drives or if your budget has legs, look at fusionIO drives. We currently have several setups with two FusionIO Duo cards that produce 2GB second reads, and over 1GB/sec writes. They are pricey but, long term cheaper for me than putting SAN in place that can meet that sort of performance. It all really depends on your workload: http://www.fusionio.com/products/iodrive/ - BEST in slot currently IMHO. http://www.intel.com/design/flash/nand/extreme/index.htm?wapkw=(X25-E) - not a bad alternative. There are other SSD controllers on the market but I have experience with both so I can recommend both as well. - John -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Benjamin Krajmalnik Sent: Wednesday, December 08, 2010 5:04 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Hardware recommendations I need to build a new high performance server to replace our current production database server. The current server is a SuperMicro 1U with 2 RAID-1 containers (one for data, one for log, SAS - data is 600GB, Logs 144GB), 16GB of RAM, running 2 quad core processors (E5405 @ 2GHz), Adaptec 5405 Controller with BBU. I am already having serious I/O bottlenecks with iostat -x showing extended periods where the disk subsystem on the data partition (the one with all the random i/o) at over 85% busy. The system is running FreeBSD 7.2 amd64 and PostgreSQL 8.4.4 on amd64-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit. Currently I have about 4GB of shared memory allocated to PostgreSQL. Database is currently about 80GB, with about 60GB being in partitioned tables which get rotated nightly to purge old data (sort of like a circular buffer of historic data). I was looking at one of the machines which Aberdeen has (the X438), and was planning on something along the lines of 96GB RAM with 16 SAS drives (15K). If I create a RAID 10 (stripe of mirrors), leaving 2 hot spares, should I still place the logs in a separate RAID-1 mirror, or can they be left on the same RAID-10 container? On the processor front, are there advantages to going to X series processors as opposed to the E series (especially since I am I/O bound)? Is anyone running this type of hardware, specially on FreeBSD? Any opinions, especially concerning the Areca controllers which they use? The new box would ideally be built with the latest released version of FreeBSD, PG 9.x. Also, is anyone running the 8.x series of FreeBSD with PG 9 in a high throughput production environment? I will be upgrading one of our test servers in one week to this same configuration to test out, but just wanted to make sure there aren't any caveats others have experienced, especially as it pertains with the autovacuum not launching worker processes which I have experienced. Best regards, Benjamin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice. Any comments or statements made herein do not necessarily reflect those of JPMorgan Chase Co., its subsidiaries and affiliates. This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. Although this transmission and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by JPMorgan Chase Co., its subsidiaries and affiliates, as applicable, for any loss or damage arising in any way from its use. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. Please refer to http://www.jpmorgan.com/pages/disclosures for disclosures relating to European legal entities. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes
Re: [PERFORM] Hardware recommendations
On Thu, Dec 9, 2010 at 01:26, Andy angelf...@yahoo.com wrote: If you are IO-bound, you might want to consider using SSD. A single SSD could easily give you more IOPS than 16 15k SAS in RAID 10. Are there any that don't risk your data on power loss, AND are cheaper than SAS RAID 10? Regards, Marti -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
Sent from my android device. -Original Message- From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org Sent: Wed, 08 Dec 2010 17:14 Subject: [PERFORM] Hardware recommendations Received: from mx2.hub.org [200.46.204.254] by mail.pengdows.com with SMTP (EHLO mx2.hub.org) (ArGoSoft Mail Server Pro for WinNT/2000/XP, Version 1.8 (1.8.9.4)); Wed, 8 Dec 2010 23:14:07 Received: from postgresql.org (mail.postgresql.org [200.46.204.86]) by mx2.hub.org (Postfix) with ESMTP id C1EAD3EAD610; Wed, 8 Dec 2010 19:16:09 -0400 (AST) Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BEF461337B83 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 19:16:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69961-09 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 23:15:55 + (UTC) X-Greylist: delayed 00:12:11.193596 by SQLgrey-1.7.6 Received: from mail.illumen.com (unknown [64.207.29.137]) by mail.postgresql.org (Postfix) with ESMTP id 69A021337B8C for pgsql-performance@postgresql.org; Wed, 8 Dec 2010 19:15:55 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Subject: [PERFORM] Hardware recommendations Date: Wed, 8 Dec 2010 16:03:43 -0700 Message-ID: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local In-Reply-To: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Hardware recommendations Thread-Index: AcuXJy2x5aJ1UxfPTAK6bTXXH/raOgAABuAQ References: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits.107 tagged_above0 required=5 testsºYES_00.9, RDNS_NONE=0.793 X-Spam-Level: X-Mailing-List: pgsql-performance List-Archive: http://archives.postgresql.org/pgsql-performance List-Help: mailto:majord...@postgresql.org?body=help List-ID: pgsql-performance.postgresql.org List-Owner: mailto:pgsql-performance-ow...@postgresql.org List-Post: mailto:pgsql-per
Re: [PERFORM] Hardware recommendations
If you are IO-bound, you might want to consider using SSD. A single SSD could easily give you more IOPS than 16 15k SAS in RAID 10. Are there any that don't risk your data on power loss, AND are cheaper than SAS RAID 10? Vertex 2 Pro has a built-in supercapacitor to save data on power loss. It's spec'd at 50K IOPS and a 200GB one costs around $1,000. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
Sent from my android device. -Original Message- From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org Sent: Wed, 08 Dec 2010 17:14 Subject: [PERFORM] Hardware recommendations Received: from mx2.hub.org [200.46.204.254] by mail.pengdows.com with SMTP (EHLO mx2.hub.org) (ArGoSoft Mail Server Pro for WinNT/2000/XP, Version 1.8 (1.8.9.4)); Wed, 8 Dec 2010 23:14:07 Received: from postgresql.org (mail.postgresql.org [200.46.204.86]) by mx2.hub.org (Postfix) with ESMTP id C1EAD3EAD610; Wed, 8 Dec 2010 19:16:09 -0400 (AST) Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BEF461337B83 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 19:16:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69961-09 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 23:15:55 + (UTC) X-Greylist: delayed 00:12:11.193596 by SQLgrey-1.7.6 Received: from mail.illumen.com (unknown [64.207.29.137]) by mail.postgresql.org (Postfix) with ESMTP id 69A021337B8C for pgsql-performance@postgresql.org; Wed, 8 Dec 2010 19:15:55 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Subject: [PERFORM] Hardware recommendations Date: Wed, 8 Dec 2010 16:03:43 -0700 Message-ID: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local In-Reply-To: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Hardware recommendations Thread-Index: AcuXJy2x5aJ1UxfPTAK6bTXXH/raOgAABuAQ References: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits.107 tagged_above0 required=5 testsºYES_00.9, RDNS_NONE=0.793 X-Spam-Level: X-Mailing-List: pgsql-performance List-Archive: http://archives.postgresql.org/pgsql-performance List-Help: mailto:majord...@postgresql.org?body=help List-ID: pgsql-performance.postgresql.org List-Owner: mailto:pgsql-performance-ow...@postgresql.org List-Post: mailto:pgsql-per
Re: [PERFORM] Hardware recommendations
Sent from my android device. -Original Message- From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org Sent: Wed, 08 Dec 2010 17:14 Subject: [PERFORM] Hardware recommendations Received: from mx2.hub.org [200.46.204.254] by mail.pengdows.com with SMTP (EHLO mx2.hub.org) (ArGoSoft Mail Server Pro for WinNT/2000/XP, Version 1.8 (1.8.9.4)); Wed, 8 Dec 2010 23:14:07 Received: from postgresql.org (mail.postgresql.org [200.46.204.86]) by mx2.hub.org (Postfix) with ESMTP id C1EAD3EAD610; Wed, 8 Dec 2010 19:16:09 -0400 (AST) Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BEF461337B83 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 19:16:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69961-09 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 23:15:55 + (UTC) X-Greylist: delayed 00:12:11.193596 by SQLgrey-1.7.6 Received: from mail.illumen.com (unknown [64.207.29.137]) by mail.postgresql.org (Postfix) with ESMTP id 69A021337B8C for pgsql-performance@postgresql.org; Wed, 8 Dec 2010 19:15:55 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Subject: [PERFORM] Hardware recommendations Date: Wed, 8 Dec 2010 16:03:43 -0700 Message-ID: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local In-Reply-To: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Hardware recommendations Thread-Index: AcuXJy2x5aJ1UxfPTAK6bTXXH/raOgAABuAQ References: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits.107 tagged_above0 required=5 testsºYES_00.9, RDNS_NONE=0.793 X-Spam-Level: X-Mailing-List: pgsql-performance List-Archive: http://archives.postgresql.org/pgsql-performance List-Help: mailto:majord...@postgresql.org?body=help List-ID: pgsql-performance.postgresql.org List-Owner: mailto:pgsql-performance-ow...@postgresql.org List-Post: mailto:pgsql-per
Re: [PERFORM] Hardware recommendations
Sent from my android device. -Original Message- From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org Sent: Wed, 08 Dec 2010 17:14 Subject: [PERFORM] Hardware recommendations Received: from mx2.hub.org [200.46.204.254] by mail.pengdows.com with SMTP (EHLO mx2.hub.org) (ArGoSoft Mail Server Pro for WinNT/2000/XP, Version 1.8 (1.8.9.4)); Wed, 8 Dec 2010 23:14:07 Received: from postgresql.org (mail.postgresql.org [200.46.204.86]) by mx2.hub.org (Postfix) with ESMTP id C1EAD3EAD610; Wed, 8 Dec 2010 19:16:09 -0400 (AST) Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BEF461337B83 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 19:16:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69961-09 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 23:15:55 + (UTC) X-Greylist: delayed 00:12:11.193596 by SQLgrey-1.7.6 Received: from mail.illumen.com (unknown [64.207.29.137]) by mail.postgresql.org (Postfix) with ESMTP id 69A021337B8C for pgsql-performance@postgresql.org; Wed, 8 Dec 2010 19:15:55 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Subject: [PERFORM] Hardware recommendations Date: Wed, 8 Dec 2010 16:03:43 -0700 Message-ID: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local In-Reply-To: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Hardware recommendations Thread-Index: AcuXJy2x5aJ1UxfPTAK6bTXXH/raOgAABuAQ References: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits.107 tagged_above0 required=5 testsºYES_00.9, RDNS_NONE=0.793 X-Spam-Level: X-Mailing-List: pgsql-performance List-Archive: http://archives.postgresql.org/pgsql-performance List-Help: mailto:majord...@postgresql.org?body=help List-ID: pgsql-performance.postgresql.org List-Owner: mailto:pgsql-performance-ow...@postgresql.org List-Post: mailto:pgsql-per
Re: [PERFORM] Hardware recommendations
Sent from my android device. -Original Message- From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org Sent: Wed, 08 Dec 2010 17:14 Subject: [PERFORM] Hardware recommendations Received: from mx2.hub.org [200.46.204.254] by mail.pengdows.com with SMTP (EHLO mx2.hub.org) (ArGoSoft Mail Server Pro for WinNT/2000/XP, Version 1.8 (1.8.9.4)); Wed, 8 Dec 2010 23:14:07 Received: from postgresql.org (mail.postgresql.org [200.46.204.86]) by mx2.hub.org (Postfix) with ESMTP id C1EAD3EAD610; Wed, 8 Dec 2010 19:16:09 -0400 (AST) Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BEF461337B83 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 19:16:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69961-09 for pgsql-performance-postgresql@mail.postgresql.org; Wed, 8 Dec 2010 23:15:55 + (UTC) X-Greylist: delayed 00:12:11.193596 by SQLgrey-1.7.6 Received: from mail.illumen.com (unknown [64.207.29.137]) by mail.postgresql.org (Postfix) with ESMTP id 69A021337B8C for pgsql-performance@postgresql.org; Wed, 8 Dec 2010 19:15:55 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Subject: [PERFORM] Hardware recommendations Date: Wed, 8 Dec 2010 16:03:43 -0700 Message-ID: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local In-Reply-To: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Hardware recommendations Thread-Index: AcuXJy2x5aJ1UxfPTAK6bTXXH/raOgAABuAQ References: f4e6a2751a2823418a21d4a160b689887b0...@fletch.stackdump.local From: Benjamin Krajmalnik k...@servoyant.com To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits.107 tagged_above0 required=5 testsºYES_00.9, RDNS_NONE=0.793 X-Spam-Level: X-Mailing-List: pgsql-performance List-Archive: http://archives.postgresql.org/pgsql-performance List-Help: mailto:majord...@postgresql.org?body=help List-ID: pgsql-performance.postgresql.org List-Owner: mailto:pgsql-performance-ow...@postgresql.org List-Post: mailto:pgsql-per
Re: [PERFORM] Hardware recommendations
On Wed, Dec 8, 2010 at 5:03 PM, Benjamin Krajmalnik k...@servoyant.com wrote: John, The platform is a network monitoring system, so we have quite a lot of inserts/updates (every data point has at least one record insert as well as at least 3 record updates). The management GUI has a lot of selects. We are refactoring the database to some degree to aid in the performance, since the performance degradations are correlated to the number of users viewing the system GUI. Scalability here may be better addressed by having something like hot read only slaves for the users who want to view data. My biggest concern with SSD drives is their life expectancy, Generally that's not a big issue, especially as the SSDs get larger. Being able to survive a power loss without corruption is more of an issue, so if you go SSD get ones with a supercapacitor that can write out the data before power down. as well as our need for relatively high capacity. Ahhh, capacity is where SSDs start to lose out quickly. Cheap 10k SAS drives and less so 15k drives are way less per gigabyte than SSDs, and you can only fit so many SSDs onto a single controller / in a single cage before you're broke. From a purely scalability perspective, this setup will need to support terabytes of data. I suppose I could use table spaces to use the most accessed data in SSD drives and the rest on regular drives. As I stated, I am moving to RAID 10, and was just wondering if the logs should still be moved off to different spindles, or will leaving them on the RAID10 be fine and not affect performance. With a battery backed caching RAID controller, it's more important that you have the pg_xlog files on a different partition than on a differen RAID set. I.e. you can have one big RAID set, and set aside the first 100G or so for pg_xlog. This has to do with fsync behaviour. In linux this is a known issue, I'm not sure how much so it would be in BSD. But you should test for fsync contention. As for the Areca controllers, I haven't tested them with the latest drivers or firmware, but we would routinely get 180 to 460 days of uptime between lockups on our 1680s we installed 2.5 or so years ago. Of the two brand new LSI controllers we installed this summer, we've had one fail already. However, the database didn't get corrupted so not too bad. My preference still leans towards the Areca, but no RAID controller is perfect and infallible. Performance wise the Areca is still faster than the LSI , and the newer faster LSI just didn't work with out quad 12 core AMD mobo. Note that all of that hardware was brand new, so things may have improved by now. I have to say Aberdeen took great care of us in getting the systems up and running. As for CPUs, almost any modern CPU will do fine. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Andy Sent: Wednesday, December 08, 2010 5:24 PM To: Marti Raudsepp Cc: pgsql-performance@postgresql.org; Benjamin Krajmalnik Subject: Re: [PERFORM] Hardware recommendations If you are IO-bound, you might want to consider using SSD. A single SSD could easily give you more IOPS than 16 15k SAS in RAID 10. Are there any that don't risk your data on power loss, AND are cheaper than SAS RAID 10? Vertex 2 Pro has a built-in supercapacitor to save data on power loss. It's spec'd at 50K IOPS and a 200GB one costs around $1,000. Viking offers 6Gbps SAS physical connector SSD drives as well - with a super capacitor. I have not seen any official pricing yet, but I would suspect it would be in the same ballpark. I am currently begging to get some for eval. I will let everyone know if I swing that and can post numbers. -mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware recommendations to scale to silly load
Nitpicking -- Perhaps the 4th data line is meant to be: Inserts in separate transactions 2500 inserts/second ^^^ ?? Greg Williamson -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tue 9/9/2003 8:25 PM To: Matt Clark Cc: Ron Johnson; PgSQL Performance ML Subject:Re: [PERFORM] Hardware recommendations to scale to silly load Matt Clark wrote: Just a data point, but on my Dual Xeon 2.4Gig machine with a 10k SCSI drive I can do 4k inserts/second if I turn fsync off. If you have a battery-backed controller, you should be able to do the same. (You will not need to turn fsync off --- fsync will just be fast because of the disk drive RAM). Am I missing something? I think Ron asked this, but I will too, is that 4k inserts in one transaction or 4k transactions each with one insert? fsync is very much faster (as are all random writes) with the write-back cache, but I'd hazard a guess that it's still not nearly as fast as turning fsync off altogether. I'll do a test perhaps... Sorry to be replying late. Here is what I found. fsync on Inserts all in one transaction 3700 inserts/second Inserts in separate transactions870 inserts/second fsync off Inserts all in one transaction 3700 inserts/second Inserts all in one transaction 2500 inserts/second ECPG test program attached. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Hardware recommendations to scale to silly load
AS == Andrew Sullivan [EMAIL PROTECTED] writes: AS On Fri, Aug 29, 2003 at 12:05:03AM -0700, William Yu wrote: We should see a boost when we move to 64-bit Linux and hopefully another one when NUMA for Linux is production-stable. AS According to the people who've worked with SGIs, NUMA actually seems AS to make things worse. It has something to do with how the shared AS memory is handled. You'll want to dig through the -general or AS -hackers archives from somewhere between 9 and 14 months ago, IIRC. I knew my PhD research would one day be good for *something* ... The basic premise of NUMA is that you can isolate which data belongs to which processor and put that on memory pages that are local/closer to it. In practice, this is harder than it sounds as it requires very detailed knowledge of the application's data access patterns, and how memory is allocated by the OS and standard libraries. Often you end up with pages that have data that should be local to two different processors, and that data keeps being migrated (if your NUMA OS supports page migration) between the two processors or one of them just gets slow access. I can't imagine it benefiting postgres given its globally shared buffers. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Hardware recommendations to scale to silly load
MC == Matt Clark [EMAIL PROTECTED] writes: MC And concurrency is very high, because it's a web app, and each MC httpd has one connection to PG, and there can be hundreds of MC active httpd processes. Some kind of connection pooling scheme MC might be in order when there are that many active clients. Any One thing you really should do (don't know if you already do it...) is have your web split into a front-end proxy and a back-end application server. There are lots of docs on how to do this for mod_perl, but it can apply to just about any backend technology that is pooling the connections. With a setup like this, my front-end web server typically has about 100 to 150 connections, and the backend doing the dynamic work (and accessing the database) has peaked at 60 or so. Usually the backend numbers at about 25. The front-end small processes get to deal with your dialup customers trickling down the data since it buffers your backend for you. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Hardware recommendations to scale to silly load
Vivek Khera [EMAIL PROTECTED] writes: The front-end small processes get to deal with your dialup customers trickling down the data since it buffers your backend for you. Huh. Well, I used to think this. But I think I was wrong. I used to have apache proxy servers running in front of the mod_perl apache servers. The proxy servers handled image and static html requests, and proxied any dynamic content to the mod_perl servers. In fact most web pages are only a few kilobytes, and you can easily configure the kernel buffers on the sockets to be 32kb or more. So the proxies would only come into play when there was a really large dynamic document, something that should probably never happen on a high volume web site anyways. I think the main source of the benefit people see from this setup is the static content. For that you get a bigger kick out of separating the static content onto entirely separate servers, preferably something slim like thttpd and just exposing the mod_perl/php/whatever servers directly. The one thing I worry about exposing the dynamic servers directly is susceptibility to dos or ddos attacks. Since all someone has to do to tie up your precious heavyweight apache slot is make a connection, one machine could easily tie up your whole web site. That would be a bit harder if you had hundreds of slots available. Of course even so it's not hard. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Hardware recommendations to scale to silly load
matt wrote: Are you *sure* about that 3K updates/inserts per second xlates to 10,800,000 per hour. That, my friend, is a WHOLE HECK OF A LOT! Yup, I know! Just a data point, but on my Dual Xeon 2.4Gig machine with a 10k SCSI drive I can do 4k inserts/second if I turn fsync off. If you have a battery-backed controller, you should be able to do the same. (You will not need to turn fsync off --- fsync will just be fast because of the disk drive RAM). Am I missing something? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Hardware recommendations to scale to silly load
My question is: is that current peak rate (300 inserts/updates *or* 2500 selects) based upon 1 connection, or many connections? With 4 CPUs, and a 4 disk RAID10, I wouldn't be surprised if 4 con- current connections gives the optimum speed. Optimum number of active workers is probably between 10 and 16. 4 doing math, 4 doing a dma transfer of data, and 4 to be available the instant one of the other 8 completes. On FreeBSD it seems to work that way when there is a mix of activity with the database. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Hardware recommendations to scale to silly load
Shridhar Daithankar wrote: Be careful here, we've seen that with the P4 Xeon's that are hyper-threaded and a system that has very high disk I/O causes the system to be sluggish and slow. But after disabling the hyper-threading itself, our system flew.. Anybody has opteron working? Hows' the performance? Yes. I'm using an 2x 1.8GHz Opteron system w/ 8GB of RAM. Right now, I'm still using 32-bit Linux -- I'm letting others be the 64-bit guinea pigs. :) I probably will get a cheapie 1x Opteron machine first and test the 64-bit kernel/libraries thoroughly before rolling it out to production. As for performance, the scaling is magnificient -- even when just using PAE instead of 64-bit addressing. At low transaction counts, it's only ~75% faster than the 2x Athlon 1800+ MP it replaced. But once the transactions start coming in, the gap is as high as 5x. My w-a-g: since each CPU has an integrated memory controller, you avoid memory bus contention which is probably the major bottleneck as transaction load increases. (I've seen Opteron several vs Xeon comparisons where single-connection tests are par for both CPUs but heavy-load tests favor the Opteron by a wide margin.) I suspect the 4X comparisons would tilt even more towards AMD's favor. We should see a boost when we move to 64-bit Linux and hopefully another one when NUMA for Linux is production-stable. ---(end of broadcast)--- TIP 3: 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] Hardware recommendations to scale to silly load
We should see a boost when we move to 64-bit Linux and hopefully another one when NUMA for Linux is production-stable. Assuming SCO doesn't make them remove it :P Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Hardware recommendations to scale to silly load
On 29 Aug 2003 at 0:05, William Yu wrote: Shridhar Daithankar wrote: Be careful here, we've seen that with the P4 Xeon's that are hyper-threaded and a system that has very high disk I/O causes the system to be sluggish and slow. But after disabling the hyper-threading itself, our system flew.. Anybody has opteron working? Hows' the performance? Yes. I'm using an 2x 1.8GHz Opteron system w/ 8GB of RAM. Right now, I'm still using 32-bit Linux -- I'm letting others be the 64-bit guinea pigs. :) I probably will get a cheapie 1x Opteron machine first and test the 64-bit kernel/libraries thoroughly before rolling it out to production. Just a guess here but does a precompiled postgresql for x86 and a x86-64 optimized one makes difference? Opteron is one place on earth you can watch difference between 32/64 bit on same machine. Can be handy at times.. As for performance, the scaling is magnificient -- even when just using PAE instead of 64-bit addressing. At low transaction counts, it's only ~75% faster than the 2x Athlon 1800+ MP it replaced. But once the transactions start coming in, the gap is as high as 5x. My w-a-g: since each CPU has an integrated memory controller, you avoid memory bus contention which is probably the major bottleneck as transaction load increases. (I've seen Opteron several vs Xeon comparisons where single-connection tests are par for both CPUs but heavy-load tests favor the Opteron by a wide margin.) I suspect the 4X comparisons would tilt even more towards AMD's favor. I am sure. But is 64 bit environment, Xeon is not the compitition. It's PA-RSC- 8700, ultraSparcs, Power series and if possible itanium. I would still expect AMD to compete comfortably given high clock speed. But chipset need to be competent as well.. I still remember the product I work on, a single CPU PA-RISC 8700 with single SCSI disc, edged out a quad CPU Xeon with SCSI RAID controller running windows in terms of scalability while running oracle. I am not sure if it was windows v/s HP-UX issue but at the end HP machine was lot better than windows machine. Windows machine shooted ahead for light load and drooeed dead equally fast with rise in load.. We should see a boost when we move to 64-bit Linux and hopefully another one when NUMA for Linux is production-stable. Getting a 2.6 running now is the answer to make it stable fast..:-) Of course if you have spare hardware.. Bye Shridhar -- briefcase, n: A trial where the jury gets together and forms a lynching party. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Hardware recommendations to scale to silly load
On Fri, 2003-08-29 at 03:18, Shridhar Daithankar wrote: On 29 Aug 2003 at 0:05, William Yu wrote: Shridhar Daithankar wrote: [snip] As for performance, the scaling is magnificient -- even when just using PAE instead of 64-bit addressing. At low transaction counts, it's only ~75% faster than the 2x Athlon 1800+ MP it replaced. But once the transactions start coming in, the gap is as high as 5x. My w-a-g: since each CPU has an integrated memory controller, you avoid memory bus contention which is probably the major bottleneck as transaction load increases. (I've seen Opteron several vs Xeon comparisons where single-connection tests are par for both CPUs but heavy-load tests favor the Opteron by a wide margin.) I suspect the 4X comparisons would tilt even more towards AMD's favor. I am sure. But is 64 bit environment, Xeon is not the compitition. It's PA-RSC- 8700, ultraSparcs, Power series and if possible itanium. IMO, Opti will compete in *both* markets. -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA Adventure is a sign of incompetence Stephanson, great polar explorer ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware recommendations to scale to silly load
On Fri, Aug 29, 2003 at 12:05:03AM -0700, William Yu wrote: We should see a boost when we move to 64-bit Linux and hopefully another one when NUMA for Linux is production-stable. According to the people who've worked with SGIs, NUMA actually seems to make things worse. It has something to do with how the shared memory is handled. You'll want to dig through the -general or -hackers archives from somewhere between 9 and 14 months ago, IIRC. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware recommendations to scale to silly load
Shridhar Daithankar wrote: Just a guess here but does a precompiled postgresql for x86 and a x86-64 optimized one makes difference? Opteron is one place on earth you can watch difference between 32/64 bit on same machine. Can be handy at times.. I don't know yet. I tried building a 64-bit kernel and my eyes glazed over trying to figure out how to create the cross-platform GCC compiler that's first needed to build the kernel. Then I read all the libraries drivers also needed to be 64-bit compiled and at that point gave up the ghost. I'll wait until a 64-bit Redhat distro is available before I test the 64-bit capabilities. The preview SuSE 64-bit Linux used in most of the Opteron rollout tests has MySql precompiled as 64-bit and under that DB, 64-bit added an extra ~25% performance (compared to a 32-bit SuSE install). My guess is half of the performance comes from eliminating the PAE swapping. I am sure. But is 64 bit environment, Xeon is not the compitition. It's PA-RSC- 8700, ultraSparcs, Power series and if possible itanium. Well, just because the Opteron is 64-bit doesn't mean it's direct competition for the high-end RISC chips. Yes, if you're looking at the discrete CPU itself, it appears they could compete -- the SpecINT scores places the Opteron near the top of the list. But big companies also need the infrastructure, management tools and top-end scalability. If you just have to have the million dollar machines (128x Itanium2 servers or whatever), AMD is nowhere close to competing unless Beowulf clusters fit your needs. In terms of infrastructure, scalability, mindshare and pricing, Xeon is most certainly Opteron's main competition. We're talking $10K servers versus $50K+ servers (assuming you actually want performance instead of having a single pokey UltraSparc CPU in a box). And yes, just because Opteron is a better performing server platform than Xeon doesn't mean a corporate fuddy-duddy still won't buy Xeon due to the $1B spent by Intel on marketting. We should see a boost when we move to 64-bit Linux and hopefully another one when NUMA for Linux is production-stable. Getting a 2.6 running now is the answer to make it stable fast..:-) Of course if you have spare hardware.. My office is a pigsty of spare hardware lying around. :) We're like pigs rolling around in the mud. ---(end of broadcast)--- TIP 3: 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] Hardware recommendations to scale to silly load
On Fri, 2003-08-29 at 11:33, William Yu wrote: Shridhar Daithankar wrote: [snip] I am sure. But is 64 bit environment, Xeon is not the compitition. It's PA-RSC- 8700, ultraSparcs, Power series and if possible itanium. Well, just because the Opteron is 64-bit doesn't mean it's direct competition for the high-end RISC chips. Yes, if you're looking at the discrete CPU itself, it appears they could compete -- the SpecINT scores places the Opteron near the top of the list. But big companies also need the infrastructure, management tools and top-end scalability. If you just have to have the million dollar machines (128x Itanium2 servers or whatever), AMD is nowhere close to competing unless Beowulf clusters fit your needs. With the proper motherboards and chipsets, it can definitely compete. What's so special about Itanic-2 that it can be engineered to be put in 128x boxes and run VMS and high-end Unix , but Opti can't? Nothing. If a company with enough engineering talent wants to do it, it can happen. -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA For me and windows it became a matter of easy to start with, and becoming increasingly difficult to be productive as time went on, and if something went wrong very difficult to fix, compared to linux's large over head setting up and learning the system with ease of use and the increase in productivity becoming larger the longer I use the system. Rohan Nicholls , The Netherlands ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Hardware recommendations to scale to silly load
You probably, more than anything, should look at some kind of superfast, external storage array Yeah, I think that's going to be a given. Low end EMC FibreChannel boxes can do around 20,000 IOs/sec, which is probably close to good enough. You mentioned using multiple RAID controllers as a boost - presumably the trick here is to split the various elements (WAL, tables, indexes) across different controllers using symlinks or suchlike? Can I feasibly split the DB tables across 5 or more controllers? Also, and importantly, the load comes but one hour per week, so buying a Starfire isn't a real option, as it'd just sit idle the rest of the time. I'm particularly interested in keeping the cost down, as I'm a shareholder in the company! Interesting. If you can't spread the load out, can you batch some parts of it? Or is the whole thing interactive therefore needing to all be done in real time at once? All interactive I'm afraid. It's a micropayment system that's going to be used here in the UK to do online voting for a popular TV programme. The phone voting system has a hard limit of [redacted] million votes per hour, and the producers would like to be able to tell people to vote online if the phone lines are busy. They can vote online anyway, but we expect the average viewer to have to make 10 calls just to get through during peak times, so the attraction is obvious. whether you like it or not, you're gonna need heavy iron if you need to do this all in one hour once a week. Yeah, I need to rent a Starfire for a month later this year, anybody got one lying around? Near London? Actually, I've seen stuff like that going on Ebay pretty cheap lately. I saw a 64 CPU E10k (366 MHz CPUs) with 64 gigs ram and 20 hard drives going for $24,000 a month ago. Put Linux or BSD on it and Postgresql should fly. Jeez, and I thought I was joking about the Starfire. Even Slowaris would be OK on one of them. The financial issue is that there's just not that much money in the micropayments game for bursty sales. If I was doing these loads *continuously* then I wouldn't be working, I'd be in the Maldives :-) I'm also looking at renting equipment, or even trying out IBM/HP's 'on-demand' offerings. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Hardware recommendations to scale to silly load
On 27 Aug 2003, matt wrote: I'm wondering if the good people out there could perhaps give me some pointers on suitable hardware to solve an upcoming performance issue. I've never really dealt with these kinds of loads before, so any experience you guys have would be invaluable. Apologies in advance for the amount of info below... My app is likely to come under some serious load in the next 6 months, but the increase will be broadly predictable, so there is time to throw hardware at the problem. Currently I have a ~1GB DB, with the largest (and most commonly accessed and updated) two tables having 150,000 and 50,000 rows. A typical user interaction with the system involves about 15 single-table selects, 5 selects with joins or subqueries, 3 inserts, and 3 updates. The current hardware probably (based on benchmarking and profiling) tops out at about 300 inserts/updates *or* 2500 selects per second. There are multiple indexes on each table that updates inserts happen on. These indexes are necessary to provide adequate select performance. Current hardware/software: Quad 700MHz PIII Xeon/1MB cache 3GB RAM RAID 10 over 4 18GB/10,000rpm drives 128MB battery backed controller cache with write-back enabled Redhat 7.3, kernel 2.4.20 Postgres 7.2.3 (stock redhat issue) I need to increase the overall performance by a factor of 10, while at the same time the DB size increases by a factor of 50. e.g. 3000 inserts/updates or 25,000 selects per second, over a 25GB database with most used tables of 5,000,000 and 1,000,000 rows. It will likely take a combination of optimizing your database structure / methods and increasing your hardware / OS performance. You probably, more than anything, should look at some kind of superfast, external storage array that has dozens of drives, and a large battery backed cache. You may be able to approximate this yourself with just a few dual channel Ultra 320 SCSI cards and a couple dozen hard drives. The more spindles you throw at a database, generally speaking, the more parallel load it can handle. You may find that once you get to 10 or 20 drives, RAID 5 or 5+0 or 0+5 will be outrunning 1+0/0+1 due to fewer writes. You likely want to look at the fastest CPUs with the fastest memory you can afford. those 700MHz xeons are likely using PC133 memory, which is painfully slow compared to the stuff pumping data out at 4 to 8 times the rate of the older stuff. Maybe an SGI Altix could do this? Have you looked at them? They're not cheap, but they do look to be quite fast, and can scale to 64 CPUs if need be. They're interbox communication fabric is faster than most CPU's front side busses. Notably, the data is very time-sensitive, so the active dataset at any hour is almost certainly going to be more on the order of 5GB than 25GB (plus I'll want all the indexes in RAM of course). Also, and importantly, the load comes but one hour per week, so buying a Starfire isn't a real option, as it'd just sit idle the rest of the time. I'm particularly interested in keeping the cost down, as I'm a shareholder in the company! Interesting. If you can't spread the load out, can you batch some parts of it? Or is the whole thing interactive therefore needing to all be done in real time at once? So what do I need? whether you like it or not, you're gonna need heavy iron if you need to do this all in one hour once a week. Can anyone who has (or has ever had) that kind of load in production offer any pointers, anecdotes, etc? Any theoretical musings also more than welcome. Comments upon my sanity will be referred to my doctor. If the best price/performance option is a second hand 32-cpu Alpha running VMS I'd be happy to go that way ;-) Actually, I've seen stuff like that going on Ebay pretty cheap lately. I saw a 64 CPU E10k (366 MHz CPUs) with 64 gigs ram and 20 hard drives going for $24,000 a month ago. Put Linux or BSD on it and Postgresql should fly. ---(end of broadcast)--- TIP 3: 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] Hardware recommendations to scale to silly load
Don't know how cheap they are. I have an app that does large batch updates. I found that if I dropped the indexes, did the updates and recreated the indexes, it was faster than doing the updates while the indexes were intact. Yeah, unfortunately it's not batch work, but real time financial work. If I drop all the indexes my select performance goes through the floor, as you'd expect. Does noatime make much difference on a PostgreSQL database? I haven't tested that yet. Yup, it does. In fact it should probably be in the standard install documentation (unless someone has a reason why it shouldn't). Who *cares* when PG last looked at the tables? If 'nomtime' was available that would probably be a good thing too. Can you split it onto multiple boxes? Some database layouts lend themselves to this, others don't. Obviously you can't do joins from one server to another, so you may lose more in multiple queries than you gain by having multiple servers. It's worth looking into though. I'm considering that. There are some tables which I might be able to split out. There amy even be some things I can pull from the DB altogether (session info in particular, so long as I can reliably send a given user's requests to the same app server each time, bearing in mind I can't see the cookies too easily because 50% of the requests are over SSL) I know my answers aren't quite the ones you were looking for, but my experience is that many people try to solve poor application design by simply throwing bigger hardware at the problem. It appears as though you've already done your homework, though. Well, I *hope* that's the case! The core issue is simply that we have to deal with an insane load for 1 hour a week, and there's just no avoiding it. Maybe I can get Sun/HP/IBM to lend some gear (it's a pretty high-profile site). ---(end of broadcast)--- TIP 3: 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] Hardware recommendations to scale to silly load
Martha Stewart called it a Good Thing [EMAIL PROTECTED] (matt)wrote: I'm also looking at renting equipment, or even trying out IBM/HP's 'on-demand' offerings. You're assuming that this is likely to lead to REAL savings, and that seems unlikely. During the recent power outage in the NorthEast, people looking for generators and fuel were paying _premium_ prices, not discounted prices. If your hardware requirement leads to someone having to buy hardware to support your peak load, then _someone_ has to pay the capital cost, and that someone is unlikely to be IBM or HP. Peak demand equipment is likely to attract pretty peaked prices. If you can find someone who needs the hardware during the day, but who _never_ needs it during your needful hours, then there might be an arrangement to be had, assuming the someone else trusts you to use what's, at other times, their hardware, and assuming you trust them with the financial information you're managing. -- select 'cbbrowne' || '@' || 'ntlug.org'; http://www3.sympatico.ca/cbbrowne/linux.html Rules of the Evil Overlord #170. I will be an equal-opportunity despot and make sure that terror and oppression is distributed fairly, not just against one particular group that will form the core of a rebellion. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Hardware recommendations to scale to silly load
Are you sure? Have you tested the overall application to see if possibly you gain more on insert performance than you lose on select performanc? Unfortunately dropping any of the indexes results in much worse select performance that is not remotely clawed back by the improvement in insert performance. Actually there doesn't really seem to *be* that much improvement in insert performance when going from 3 indexes to 2. I guess indexes must be fairly cheap for PG to maintain? It's possible that compiling Postgres manually with proper optimizations could yield some improvements, as well as building a custom kernel in Redhat. Also, you don't mention which filesystem you're using: http://www.potentialtech.com/wmoran/postgresql.php Yeah, I can imagine getting 5% extra from a slim kernel and super-optimised PG. The FS is ext3, metadata journaling (the default), mounted noatime. But if you're in the situation where you have more time than money, you may find that an overall audit of your app is worthwhile. Consider taking parts that are in perl (for example) and recoding them into C (that is, unless you've already identified that all the bottlenecks are at the PostgreSQL server) I can pretty cheaply add more CPU horsepower for the app servers, as they scale horizontally, so I can chuck in a couple (or 3, or 4, or ...) more dual-cpu boxen with a gig of ram and tell the load balancer about them. The problem with the DB is that that approach simply won't work - the box just has to get bigger! I doubt if the suggestions I've made are going to get you 10x, but they may get you 2x, and then you only need the hardware to do 5x. It all helps :-) A few percent here, a few percent there, pretty soon you're talking serious improvements... Thanks Matt ---(end of broadcast)--- TIP 3: 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] Hardware recommendations to scale to silly load
On Wed, Aug 27, 2003 at 02:35:13AM +0100, matt wrote: I need to increase the overall performance by a factor of 10, while at the same time the DB size increases by a factor of 50. e.g. 3000 inserts/updates or 25,000 selects per second, over a 25GB database with most used tables of 5,000,000 and 1,000,000 rows. Your problem is mostly going to be disk related. You can only get in there as many tuples in a second as your disk rotates per second. I suspect what you need is really expensive disk hardware (sorry to tell you that) set up as RAID 1+0 on fibre channel or something. 3000 write transactions per second is probably too much to ask for any standard hardware. But given that you are batching this once a week, and trying to avoid big expenses, are you use this is the right approach? Perhaps you should consider a redesign using COPY and such? A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 3: 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] Hardware recommendations to scale to silly load
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (scott.marlowe) belched out... :-): whether you like it or not, you're gonna need heavy iron if you need to do this all in one hour once a week. The other thing worth considering is trying to see if there is a way of partitioning the workload across multiple hosts. At the point that you start going past hardware that is over-the-counter commodity stuff, the premiums start getting pretty high. Dual-CPU Intel boxes are pretty cheap compared to buncha-CPU Sparc boxes. If some sort of segmentation of the workload can be done, whether by area code, postal code, or perhaps the last couple digits of the caller's phone number, or even a round robin, it's likely to be a lot cheaper to get an array of 4 Dual-Xeon boxes with 8 disk drives apiece than a Sun/HP/IBM box with 16 CPUs. -- let name=cbbrowne and tld=ntlug.org in name ^ @ ^ tld;; http://cbbrowne.com/info/linuxxian.html Show me... show me... show me... COMPUTERS! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Hardware recommendations to scale to silly load
Christopher Browne wrote: Martha Stewart called it a Good Thing [EMAIL PROTECTED] (matt)wrote: I'm also looking at renting equipment, or even trying out IBM/HP's 'on-demand' offerings. You're assuming that this is likely to lead to REAL savings, and that seems unlikely. During the recent power outage in the NorthEast, people looking for generators and fuel were paying _premium_ prices, not discounted prices. If your hardware requirement leads to someone having to buy hardware to support your peak load, then _someone_ has to pay the capital cost, and that someone is unlikely to be IBM or HP. Peak demand equipment is likely to attract pretty peaked prices. If you can find someone who needs the hardware during the day, but who _never_ needs it during your needful hours, then there might be an arrangement to be had, assuming the someone else trusts you to use what's, at other times, their hardware, and assuming you trust them with the financial information you're managing. I hadn't considered this, but that's not a bad idea. With FreeBSD, you have jails, which allow multiple users to share hardware without having to worry about user A looking at user B's stuff. Does such a paradigm exist on any heavy iron? I have no idea where you'd go to find this kind of co-op server leasing, but it sure sounds like it could work. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware recommendations to scale to silly load
On Tue, 2003-08-26 at 20:35, matt wrote: I'm wondering if the good people out there could perhaps give me some pointers on suitable hardware to solve an upcoming performance issue. I've never really dealt with these kinds of loads before, so any experience you guys have would be invaluable. Apologies in advance for the amount of info below... My app is likely to come under some serious load in the next 6 months, but the increase will be broadly predictable, so there is time to throw hardware at the problem. Currently I have a ~1GB DB, with the largest (and most commonly accessed and updated) two tables having 150,000 and 50,000 rows. A typical user interaction with the system involves about 15 single-table selects, 5 selects with joins or subqueries, 3 inserts, and 3 updates. The current hardware probably (based on benchmarking and profiling) tops out at about 300 inserts/updates *or* 2500 selects per second. There are multiple indexes on each table that updates inserts happen on. These indexes are necessary to provide adequate select performance. Current hardware/software: Quad 700MHz PIII Xeon/1MB cache 3GB RAM RAID 10 over 4 18GB/10,000rpm drives 128MB battery backed controller cache with write-back enabled Much more cache needed. Say 512MB per controller? Redhat 7.3, kernel 2.4.20 Postgres 7.2.3 (stock redhat issue) Upgrade to Pg 7.3.4! I need to increase the overall performance by a factor of 10, while at the same time the DB size increases by a factor of 50. e.g. 3000 Are you *sure* about that 3K updates/inserts per second xlates to 10,800,000 per hour. That, my friend, is a WHOLE HECK OF A LOT! inserts/updates or 25,000 selects per second, over a 25GB database with Likewise: 90,000,000 selects per hour. most used tables of 5,000,000 and 1,000,000 rows. Notably, the data is very time-sensitive, so the active dataset at any During the 1 hour surge, will SELECTs at 10 minutes after the hour depend on INSERTs at 5 minutes after the hour? If not, maybe you could pump the INSERT/UPDATE records into flat files, to be processed after the 1-hour surge is complete. That may reduce the h/w requirements. hour is almost certainly going to be more on the order of 5GB than 25GB (plus I'll want all the indexes in RAM of course). Also, and importantly, the load comes but one hour per week, so buying a Only one hour out of 168? May I ask what kind of app it is? Starfire isn't a real option, as it'd just sit idle the rest of the time. I'm particularly interested in keeping the cost down, as I'm a shareholder in the company! What a fun exercises. Ok, lets see: Postgres 7.3.4 RH AS 2.1 12GB RAM motherboard with 64 bit 66MHz PCI slots 4 - Xenon 3.0GHz (1MB cache) CPUs 8 - 36GB 15K RPM as RAID10 on a 64 bit 66MHz U320 controller having 512MB cache (for database) 2 - 36GB 15K RPM as RAID1 on a 64 bit 66MHz U320 controller having 512MB cache (for OS, swap, WAL files) 1 - library tape drive plugged into the OS' SCSI controller. I prefer DLT, but that's my DEC bias. 1 - 1000 volt UPS. If you know when the flood will be coming, you could perform SELECT * FROM ... WHERE statements on an indexed field, to pull the relevant data into Linux's buffers. Yes, the 8 disks is capacity-overkill, but the 8 high-speed spindles is what you're looking for. So what do I need? Can anyone who has (or has ever had) that kind of load in production offer any pointers, anecdotes, etc? Any theoretical musings also more than welcome. Comments upon my sanity will be referred to my doctor. If the best price/performance option is a second hand 32-cpu Alpha running VMS I'd be happy to go that way ;-) I'd love to work on a GS320! You may even pick one up for a million or 2. The license costs for VMS Rdb would eat you, though. Rdb *does* have ways, though, using large buffers and hashed indexes, with the table tuples stored on the same page as the hashed index keys, to make such accesses *blazingly* fast. Many thanks for reading this far. -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA A C program is like a fast dance on a newly waxed dance floor by people carrying razors. Waldi Ravens ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware recommendations to scale to silly load
matt wrote: I'm wondering if the good people out there could perhaps give me some pointers on suitable hardware to solve an upcoming performance issue. I've never really dealt with these kinds of loads before, so any experience you guys have would be invaluable. Apologies in advance for the amount of info below... My app is likely to come under some serious load in the next 6 months, but the increase will be broadly predictable, so there is time to throw hardware at the problem. Currently I have a ~1GB DB, with the largest (and most commonly accessed and updated) two tables having 150,000 and 50,000 rows. A typical user interaction with the system involves about 15 single-table selects, 5 selects with joins or subqueries, 3 inserts, and 3 updates. The current hardware probably (based on benchmarking and profiling) tops out at about 300 inserts/updates *or* 2500 selects per second. There are multiple indexes on each table that updates inserts happen on. These indexes are necessary to provide adequate select performance. Are you sure? Have you tested the overall application to see if possibly you gain more on insert performance than you lose on select performanc? (Hey, you asked for musings ...) Current hardware/software: Quad 700MHz PIII Xeon/1MB cache 3GB RAM RAID 10 over 4 18GB/10,000rpm drives 128MB battery backed controller cache with write-back enabled Redhat 7.3, kernel 2.4.20 Postgres 7.2.3 (stock redhat issue) It's possible that compiling Postgres manually with proper optimizations could yield some improvements, as well as building a custom kernel in Redhat. Also, you don't mention which filesystem you're using: http://www.potentialtech.com/wmoran/postgresql.php I need to increase the overall performance by a factor of 10, while at the same time the DB size increases by a factor of 50. e.g. 3000 inserts/updates or 25,000 selects per second, over a 25GB database with most used tables of 5,000,000 and 1,000,000 rows. Notably, the data is very time-sensitive, so the active dataset at any hour is almost certainly going to be more on the order of 5GB than 25GB (plus I'll want all the indexes in RAM of course). Also, and importantly, the load comes but one hour per week, so buying a Starfire isn't a real option, as it'd just sit idle the rest of the time. I'm particularly interested in keeping the cost down, as I'm a shareholder in the company! I can't say for sure without looking at your application overall, but many applications I've seen could be optimized. It's usually a few seconds here and there that take hours to find and tweak. But if you're in the situation where you have more time than money, you may find that an overall audit of your app is worthwhile. Consider taking parts that are in perl (for example) and recoding them into C (that is, unless you've already identified that all the bottlenecks are at the PostgreSQL server) I doubt if the suggestions I've made are going to get you 10x, but they may get you 2x, and then you only need the hardware to do 5x. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Hardware recommendations to scale to silly load
On Wed, 2003-08-27 at 21:26, Bill Moran wrote: Christopher Browne wrote: Martha Stewart called it a Good Thing [EMAIL PROTECTED] (matt)wrote: [snip] With FreeBSD, you have jails, which allow multiple users to share hardware without having to worry about user A looking at user B's stuff. Does such a paradigm exist on any heavy iron? I have no IBM invented the idea (or maybe stole it) back in the '70s. The VM hypervisor was designed as a conversion tool, to let customers run both OS/MVS and DOS/VSE, to aid in converting from VSE to MVS. Customers, the cheap, uncooperative beasts, liked VSE, but also liked VM, since it let them have, for example, a dev, test, and production systems all on the same piece of h/w, thus saving them oodles of money in h/w costs and maintenance fees. Yes, yes, the modern term for this is server consolidation, and VMware does the same thing, 30 years after dinosaur customers had it on boxen that academics, analysts and young whippersnappers said were supposed to be extinct 20 years ago. -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA Knowledge should be free for all. Harcourt Fenton Mudd, Star Trek:TOS, I, Mudd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Hardware recommendations to scale to silly load
2003-08-27 ragyog napjn matt ezt zente: Yeah, I can imagine getting 5% extra from a slim kernel and super-optimised PG. Hm, about 20%, but only for the correctness - 20% not help you also :( The FS is ext3, metadata journaling (the default), mounted noatime. Worst fs under linux :) Try xfs. -- Tomka Gergely S most - vajon barbrok nlkl mi lesz velnk? k mgiscsak megolds voltak valahogy... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Hardware recommendations to scale to silly load
Are you *sure* about that 3K updates/inserts per second xlates to 10,800,000 per hour. That, my friend, is a WHOLE HECK OF A LOT! Yup, I know! During the 1 hour surge, will SELECTs at 10 minutes after the hour depend on INSERTs at 5 minutes after the hour? Yes, they do. It's a payments system, so things like account balances and purchase histories have to be updated in real time. Only one hour out of 168? May I ask what kind of app it is? Online voting for an unnamed TV show... If the best price/performance option is a second hand 32-cpu Alpha running VMS I'd be happy to go that way ;-) I'd love to work on a GS320! You may even pick one up for a million or 2. The license costs for VMS Rdb would eat you, though. You'd be amazed how little they do go for actually :-) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware recommendations to scale to silly load
On Tue, 2003-08-26 at 23:59, Ron Johnson wrote: What a fun exercises. Ok, lets see: Postgres 7.3.4 RH AS 2.1 12GB RAM motherboard with 64 bit 66MHz PCI slots 4 - Xenon 3.0GHz (1MB cache) CPUs 8 - 36GB 15K RPM as RAID10 on a 64 bit 66MHz U320 controller having 512MB cache (for database) 2 - 36GB 15K RPM as RAID1 on a 64 bit 66MHz U320 controller having 512MB cache (for OS, swap, WAL files) 1 - library tape drive plugged into the OS' SCSI controller. I prefer DLT, but that's my DEC bias. 1 - 1000 volt UPS. Be careful here, we've seen that with the P4 Xeon's that are hyper-threaded and a system that has very high disk I/O causes the system to be sluggish and slow. But after disabling the hyper-threading itself, our system flew.. -- Chris Bowlby [EMAIL PROTECTED] Hub.Org Networking Services ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Hardware recommendations to scale to silly load
On 28 Aug 2003 at 11:05, Chris Bowlby wrote: On Tue, 2003-08-26 at 23:59, Ron Johnson wrote: What a fun exercises. Ok, lets see: Postgres 7.3.4 RH AS 2.1 12GB RAM motherboard with 64 bit 66MHz PCI slots 4 - Xenon 3.0GHz (1MB cache) CPUs 8 - 36GB 15K RPM as RAID10 on a 64 bit 66MHz U320 controller having 512MB cache (for database) 2 - 36GB 15K RPM as RAID1 on a 64 bit 66MHz U320 controller having 512MB cache (for OS, swap, WAL files) 1 - library tape drive plugged into the OS' SCSI controller. I prefer DLT, but that's my DEC bias. 1 - 1000 volt UPS. Be careful here, we've seen that with the P4 Xeon's that are hyper-threaded and a system that has very high disk I/O causes the system to be sluggish and slow. But after disabling the hyper-threading itself, our system flew.. Anybody has opteron working? Hows' the performance? Bye Shridhar -- A father doesn't destroy his children. -- Lt. Carolyn Palamas, Who Mourns for Adonais?, stardate 3468.1. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware recommendations to scale to silly load
sm On 27 Aug 2003, matt wrote: My app is likely to come under some serious load in the next 6 months, but the increase will be broadly predictable, so there is time to throw hardware at the problem. Currently I have a ~1GB DB, with the largest (and most commonly accessed and updated) two tables having 150,000 and 50,000 rows. Just how big do you expect your DB to grow? For a 1GB disk-space database, I'd probably just splurge for an SSD hooked up either via SCSI or FibreChannel. Heck, up to about 5Gb or so it is not that expensive (about $25k) and adding another 5Gb should set you back probably another $20k. I use an SSD from Imperial Technology ( http://www.imperialtech.com/ ) for mail spools. My database is way to big for my budget to put in SSD. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 3: 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] Hardware recommendations to scale to silly load
I need to increase the overall performance by a factor of 10, while at the same time the DB size increases by a factor of 50. e.g. 3000 inserts/updates or 25,000 selects per second, over a 25GB database with most used tables of 5,000,000 and 1,000,000 rows. Ok.. I would be surprised if you needed much more actual CPU power. I suspect they're mostly idle waiting on data -- especially with a Quad Xeon (shared memory bus is it not?). I'd be looking to get your hands on a large pSeries machine from IBM or perhaps an 8-way Opteron (not that hard to come by today, should be easy in the near future). The key is low latency ram tied to a chip rather than a centralized bus -- a 3800 SunFire would do too ;). Write performance won't matter very much. 3000 inserts/second isn't high -- some additional battery backed write cache may be useful but not overly important with enough ram to hold the complete dataset. I suspect those are slow due to things like foreign keys -- which of course are selects. Notably, the data is very time-sensitive, so the active dataset at any hour is almost certainly going to be more on the order of 5GB than 25GB (plus I'll want all the indexes in RAM of course). Very good. Find yourself 8GB to 12GB ram and you should be fine. In this case, additional ram will keep the system from hitting the disk for writes as well. You may want to play around with checkpoints. Prevention of a checkpoint during this hour will help prevent peaks. Be warned though, WAL will grow very large, and recovery time should a crash occur could be painful. You say the data is very time sensitive -- how time sensitive? Are the selects all based on this weeks data? A copy of the database on a second machine (say your Quad Xeon) for static per client data would be very useful to reduce needless load. I assume the application servers have already cached any static global data by this point. Finally, upgrade to 7.4. Do use prepared statements. Do limit the number of connections any given application server is allowed (especially for short transactions). 3 PostgreSQL processes per CPU (where the box limit is not Disk) seems to be about right -- your OS may vary. Pre-calculate anything you can. Are the $ amounts for a transaction generally the the same? Do you tend to have repeat clients? Great -- make your current clients transactions a day in advance. Now you have a pair of selects and 1 update (mark it with the time the client actually approved it). If the client doesn't approve of the pre-calculated transaction, throw it away at some later time. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Hardware recommendations to scale to silly load
On 27 Aug 2003, matt wrote: You probably, more than anything, should look at some kind of superfast, external storage array Yeah, I think that's going to be a given. Low end EMC FibreChannel boxes can do around 20,000 IOs/sec, which is probably close to good enough. You mentioned using multiple RAID controllers as a boost - presumably the trick here is to split the various elements (WAL, tables, indexes) across different controllers using symlinks or suchlike? Can I feasibly split the DB tables across 5 or more controllers? I'm not sure I'd split the tables by hand right up front. Try getting as many hard drives as you can afford hooked up at once, and then try different ways of partitioning them. I'm guessing that making two fairly good sized 1+0 sets, one for data and one for WAL might be the best answer. Actually, I've seen stuff like that going on Ebay pretty cheap lately. I saw a 64 CPU E10k (366 MHz CPUs) with 64 gigs ram and 20 hard drives going for $24,000 a month ago. Put Linux or BSD on it and Postgresql should fly. Jeez, and I thought I was joking about the Starfire. Even Slowaris would be OK on one of them. The financial issue is that there's just not that much money in the micropayments game for bursty sales. If I was doing these loads *continuously* then I wouldn't be working, I'd be in the Maldives :-) $24,000 isn't that much for a server really, and if you can leverage this one sale to get more, then it would likely pay for itself over time. If you have problems keeping up with load, it will be harder to get more customers, so you kinda wanna do this as well as possible the first time. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware recommendations to scale to silly load
Just how big do you expect your DB to grow? For a 1GB disk-space database, I'd probably just splurge for an SSD hooked up either via SCSI or FibreChannel. Heck, up to about 5Gb or so it is not that expensive (about $25k) and adding another 5Gb should set you back probably another $20k. I use an SSD from Imperial Technology ( http://www.imperialtech.com/ ) for mail spools. My database is way to big for my budget to put in SSD. I may well be able to split some tables that aren't used in joins into a separate DB, and could well use an SSD for those. In fact two of the inserts per user interaction could be split off, and they're not financially important tables, so fsync=false could be enabled for those, in which case an SSD might be overkill... The whole thing will definitely *not* fit in an SSD for a sensible price, but the WAL might well! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Hardware recommendations to scale to silly load
Ok.. I would be surprised if you needed much more actual CPU power. I suspect they're mostly idle waiting on data -- especially with a Quad Xeon (shared memory bus is it not?). In reality the CPUs get pegged: about 65% PG and 35% system. But I agree that memory throughput and latency is an issue. Write performance won't matter very much. 3000 inserts/second isn't high -- some additional battery backed write cache may be useful but not overly important with enough ram to hold the complete dataset. I suspect those are slow due to things like foreign keys -- which of course are selects. 3000 inserts/sec isn't high when they're inside one transaction, but if each is inside its own transaction then that's 3000 commits/second. case, additional ram will keep the system from hitting the disk for writes as well. How does that work? You may want to play around with checkpoints. Prevention of a checkpoint during this hour will help prevent peaks. Be warned though, WAL will grow very large, and recovery time should a crash occur could be painful. Good point. I'll have a think about that. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Hardware recommendations to scale to silly load
On Thu, 2003-08-28 at 12:37, Matt Clark wrote: Ok.. I would be surprised if you needed much more actual CPU power. I suspect they're mostly idle waiting on data -- especially with a Quad Xeon (shared memory bus is it not?). In reality the CPUs get pegged: about 65% PG and 35% system. But I agree that memory throughput and latency is an issue. system in this case is dealing with disk activity or process switches? Usually the 65% includes the CPU waiting on a request for data from main memory. Since you will be moving a lot of data through the CPU, the L1 / L2 cache doesn't help too much (even large cache), but low latency high bandwidth memory will make a significant difference. CPUs not having to wait on other CPUs doing a memory fetch will make an even larger difference (dedicated memory bus per CPU). Good memory is the big ticket item. Sun CPUs are not better than Intel CPUs, for simple DB interaction. It's the additional memory bandwidth that makes them shine. Incidentally, Suns are quite slow with PG for calculation intensive work on a small dataset. Write performance won't matter very much. 3000 inserts/second isn't high -- some additional battery backed write cache may be useful but not overly important with enough ram to hold the complete dataset. I suspect those are slow due to things like foreign keys -- which of course are selects. 3000 inserts/sec isn't high when they're inside one transaction, but if each is inside its own transaction then that's 3000 commits/second. Still not anything to concern yourself with. WAL on battery backed write cache (with a good controller) will more than suffice -- boils down to the same as if fsync was disabled. You might want to try putting it onto it's own controller, but I don't think you will see much of a change. 20k WAL operations / sec would be something to worry about. case, additional ram will keep the system from hitting the disk for writes as well. How does that work? Simple. Your OS will buffer writes in memory until they are required to hit disk (fsync or similar). Modify the appropriate sysctl to inform the OS it can use more than 10% (10% is the FreeBSD default I believe) of the memory for writes. Buffering 4GB of work in memory (WAL logs will ensure this is crash safe) will nearly eliminate I/O. When the OS is no longer busy, it will filter the writes from ram back to disk. Visibly, there is no change to the user aside from a speed increase. You may want to play around with checkpoints. Prevention of a checkpoint during this hour will help prevent peaks. Be warned though, WAL will grow very large, and recovery time should a crash occur could be painful. Good point. I'll have a think about that. This is more important with a larger buffer. A checkpoint informs the OS to dump the buffer to disk so it can guarantee it hit hardware (thus allowing PG to remove / recycle WAL files). I do think your best bet is to segregate the DB. Read / write, by user location, first 4 digits of the credit card, anything will make a much better system. Keep a master with all of the data that can take the full week to process it. signature.asc Description: This is a digitally signed message part
[PERFORM] Hardware recommendations to scale to silly load
I'm wondering if the good people out there could perhaps give me some pointers on suitable hardware to solve an upcoming performance issue. I've never really dealt with these kinds of loads before, so any experience you guys have would be invaluable. Apologies in advance for the amount of info below... My app is likely to come under some serious load in the next 6 months, but the increase will be broadly predictable, so there is time to throw hardware at the problem. Currently I have a ~1GB DB, with the largest (and most commonly accessed and updated) two tables having 150,000 and 50,000 rows. A typical user interaction with the system involves about 15 single-table selects, 5 selects with joins or subqueries, 3 inserts, and 3 updates. The current hardware probably (based on benchmarking and profiling) tops out at about 300 inserts/updates *or* 2500 selects per second. There are multiple indexes on each table that updates inserts happen on. These indexes are necessary to provide adequate select performance. Current hardware/software: Quad 700MHz PIII Xeon/1MB cache 3GB RAM RAID 10 over 4 18GB/10,000rpm drives 128MB battery backed controller cache with write-back enabled Redhat 7.3, kernel 2.4.20 Postgres 7.2.3 (stock redhat issue) I need to increase the overall performance by a factor of 10, while at the same time the DB size increases by a factor of 50. e.g. 3000 inserts/updates or 25,000 selects per second, over a 25GB database with most used tables of 5,000,000 and 1,000,000 rows. Notably, the data is very time-sensitive, so the active dataset at any hour is almost certainly going to be more on the order of 5GB than 25GB (plus I'll want all the indexes in RAM of course). Also, and importantly, the load comes but one hour per week, so buying a Starfire isn't a real option, as it'd just sit idle the rest of the time. I'm particularly interested in keeping the cost down, as I'm a shareholder in the company! So what do I need? Can anyone who has (or has ever had) that kind of load in production offer any pointers, anecdotes, etc? Any theoretical musings also more than welcome. Comments upon my sanity will be referred to my doctor. If the best price/performance option is a second hand 32-cpu Alpha running VMS I'd be happy to go that way ;-) Many thanks for reading this far. Matt ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware recommendations to scale to silly load
matt wrote: Are you sure? Have you tested the overall application to see if possibly you gain more on insert performance than you lose on select performanc? Unfortunately dropping any of the indexes results in much worse select performance that is not remotely clawed back by the improvement in insert performance. Bummer. It was just a thought: never assume dropping indexes will hurt performance. But, since you've obviously tested ... Actually there doesn't really seem to *be* that much improvement in insert performance when going from 3 indexes to 2. I guess indexes must be fairly cheap for PG to maintain? Don't know how cheap they are. I have an app that does large batch updates. I found that if I dropped the indexes, did the updates and recreated the indexes, it was faster than doing the updates while the indexes were intact. It doesn't sound like your app can use that approach, but I thought I'd throw it out there. It's possible that compiling Postgres manually with proper optimizations could yield some improvements, as well as building a custom kernel in Redhat. Also, you don't mention which filesystem you're using: http://www.potentialtech.com/wmoran/postgresql.php Yeah, I can imagine getting 5% extra from a slim kernel and super-optimised PG. The FS is ext3, metadata journaling (the default), mounted noatime. ext3 is more reliable than ext2, but it's 1.1x slower. You can squeeze a little performance by using Reiser or JFS, if you're not willing to take the risk of ext2, either way, it's a pretty minor improvement. Does noatime make much difference on a PostgreSQL database? I haven't tested that yet. But if you're in the situation where you have more time than money, you may find that an overall audit of your app is worthwhile. Consider taking parts that are in perl (for example) and recoding them into C (that is, unless you've already identified that all the bottlenecks are at the PostgreSQL server) I can pretty cheaply add more CPU horsepower for the app servers, as they scale horizontally, so I can chuck in a couple (or 3, or 4, or ...) more dual-cpu boxen with a gig of ram and tell the load balancer about them. The problem with the DB is that that approach simply won't work - the box just has to get bigger! Can you split it onto multiple boxes? Some database layouts lend themselves to this, others don't. Obviously you can't do joins from one server to another, so you may lose more in multiple queries than you gain by having multiple servers. It's worth looking into though. I know my answers aren't quite the ones you were looking for, but my experience is that many people try to solve poor application design by simply throwing bigger hardware at the problem. It appears as though you've already done your homework, though. Hope this has been _some_ help. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
