Re: [PERFORM] Comments requested on IO performance : new db server
On 09/03/12, Merlin Moncure (mmonc...@gmail.com) wrote: On Fri, Mar 9, 2012 at 5:15 AM, Rory Campbell-Lange r...@campbell-lange.net wrote: I've taken the liberty of reposting this message as my addendum to a long thread that I started on the subject of adding a new db server to our existing 4-year old workhorse got lost in discussion. Our workload is several small databases totalling less than 40GB of disk space. The proposed system has 48GB RAM, 2 * quad core E5620 @ 2.40GHz and 4 WD Raptors behind an LSI SAS card. Our supplier has just run a set of tests on the machine we intend to buy. The test rig had the following setup: LSI MegaRAID SAS 9260-8i Firmware: 12.12.0-0090 Kernel: 2.6.39.4 Hard disks: 4x WD6000BLHX Test done on 256GB volume BS = blocksize in bytes The test tool is fio. I'd be grateful to know if the results below are considered acceptable. An ancillary question is whether a 4096 block size is a good idea. I suppose we will be using XFS which I understand has a default block size of 4096 bytes. RAID 10 -- ... -- Random write BS MB/s IOPs 512 .53 001103.60 1024 0001.15 001179.20 4096 0004.43 001135.30 16384 0017.61 001127.56 65536 0061.39 000982.39 131072 0079.27 000634.16 -- since your RAM is larger than the database size, read performance is essentially a non-issue.your major gating factors are going to be cpu bound queries and random writes -- 1000 IOPS essentially puts an upper bound on your write TPS, especially if your writes are frequent and randomly distributed, the case that is more or less simulated by pgbench with large scaling factors. Now, 1000 write tps is quite alot (3.6 mil transactions/hour) and your workload will drive the hardware consideration. Thanks for your comments, Merlin. With regard to the gating factors I believe the following is pertinent: CPU My current server has 2 * quad Xeon E5420 @ 2.50GHz. The server occasionally reaches 20% sutained utilisation according to sar. This cpu has a passmark of 7,730. http://www.cpubenchmark.net/cpu_lookup.php?cpu=[Dual+CPU]+Intel+Xeon+E5420+%40+2.50GHz My proposed CPU is an E5620 @ 2.40GHz with CPU passmark of 9,620 http://www.cpubenchmark.net/cpu_lookup.php?cpu=[Dual+CPU]+Intel+Xeon+E5620+%40+2.40GHz Since the workload will be very similar I'm hoping for about 20% better CPU performance from the new server, which should drop max CPU load by 5% or so. Random Writes I'll have to test this. My current server (R10 4*15K SCSI) produced the following pgbench stats while running its normal workload: -c -t TPS 5 2 446 10 1 542 20 5000 601 30 647 I'd be grateful to know what parameters I should use for a large scaling factor pgbench test. Many thanks Rory -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- 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] Comments requested on IO performance : new db server
Is a block size of 4096 a good idea both for the filesystem and postgresql? The analysis here: http://www.fuzzy.cz/en/articles/benchmark-results-hdd-read-write-pgbench/ appears to suggest that at least for database block sizes of 4096 read/write performance is much higher than for smaller block sizes. Rory On 09/03/12, Rory Campbell-Lange (r...@campbell-lange.net) wrote: ...An ancillary question is whether a 4096 block size is a good idea. I suppose we will be using XFS which I understand has a default block size of 4096 bytes. RAID 10 -- Read sequential BS MB/s IOPs 5120129.26264730.80 10240229.75235273.40 40960363.14092965.50 163840475.02030401.50 655360472.79007564.65 1310720428.15003425.20 -- Write sequential BS MB/s IOPs 5120036.08073908.00 10240065.61067192.60 40960170.15043560.40 163840219.80014067.57 655360240.05003840.91 1310720243.96001951.74 -- Random read BS MB/s IOPs 5120001.50003077.20 10240002.91002981.40 40960011.59002968.30 163840044.50002848.28 655360156.96002511.41 1310720170.65001365.25 -- Random write BS MB/s IOPs 512.53001103.60 10240001.15001179.20 40960004.43001135.30 163840017.61001127.56 655360061.39000982.39 1310720079.27000634.16 -- -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Comments requested on IO performance : new db server
I've taken the liberty of reposting this message as my addendum to a long thread that I started on the subject of adding a new db server to our existing 4-year old workhorse got lost in discussion. Our workload is several small databases totalling less than 40GB of disk space. The proposed system has 48GB RAM, 2 * quad core E5620 @ 2.40GHz and 4 WD Raptors behind an LSI SAS card. Our supplier has just run a set of tests on the machine we intend to buy. The test rig had the following setup: LSI MegaRAID SAS 9260-8i Firmware: 12.12.0-0090 Kernel: 2.6.39.4 Hard disks: 4x WD6000BLHX Test done on 256GB volume BS = blocksize in bytes The test tool is fio. I'd be grateful to know if the results below are considered acceptable. An ancillary question is whether a 4096 block size is a good idea. I suppose we will be using XFS which I understand has a default block size of 4096 bytes. RAID 10 -- Read sequential BS MB/s IOPs 5120129.26264730.80 10240229.75235273.40 40960363.14092965.50 163840475.02030401.50 655360472.79007564.65 1310720428.15003425.20 -- Write sequential BS MB/s IOPs 5120036.08073908.00 10240065.61067192.60 40960170.15043560.40 163840219.80014067.57 655360240.05003840.91 1310720243.96001951.74 -- Random read BS MB/s IOPs 5120001.50003077.20 10240002.91002981.40 40960011.59002968.30 163840044.50002848.28 655360156.96002511.41 1310720170.65001365.25 -- Random write BS MB/s IOPs 512.53001103.60 10240001.15001179.20 40960004.43001135.30 163840017.61001127.56 655360061.39000982.39 1310720079.27000634.16 -- -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- 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] Advice sought : new database server
On 08/03/12, Ants Aasma (ants.aa...@eesti.ee) wrote: So regardless if the benchmark is a good representation of the target workload or not, it definitely isn't benchmarking the IO system. At the risk of hijacking the thread I started, I'd be grateful for comments on the following system IO results. Rather than using pgbench (which Ants responded about above), this uses fio. Our workload is several small databases totalling less than 40GB of disk space. The proposed system has 48GB RAM, 2 * quad core E5620 @ 2.40GHz and 4 WD Raptors behind an LSI SAS card. Is this IO respectable? LSI MegaRAID SAS 9260-8i Firmware: 12.12.0-0090 Kernel: 2.6.39.4 Hard disks: 4x WD6000BLHX Test done on 256GB volume BS = blocksize in bytes RAID 10 -- Read sequential BS MB/s IOPs 5120129.26264730.80 10240229.75235273.40 40960363.14092965.50 163840475.02030401.50 655360472.79007564.65 1310720428.15003425.20 -- Write sequential BS MB/s IOPs 5120036.08073908.00 10240065.61067192.60 40960170.15043560.40 163840219.80014067.57 655360240.05003840.91 1310720243.96001951.74 -- Random read BS MB/s IOPs 5120001.50003077.20 10240002.91002981.40 40960011.59002968.30 163840044.50002848.28 655360156.96002511.41 1310720170.65001365.25 -- Random write BS MB/s IOPs 512.53001103.60 10240001.15001179.20 40960004.43001135.30 163840017.61001127.56 655360061.39000982.39 1310720079.27000634.16 -- -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- 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] Advice sought : new database server
On 04/03/12, Rory Campbell-Lange (r...@campbell-lange.net) wrote: I'd be grateful for advice on specifying a new server ... The existing server is a 2 x Quad core E5420 Xeon (2.5GHz) with 8GB of RAM with an LSI battery-backed RAID 10 array of 4no 10K SCSI disks, providing about 230GB of usable storage, 150GB of which is on an LV providing reconfigurable space for the databases which are served off an XFS formatted volume. In conversation on the list I've established that our current server (while fine for our needs) isn't performing terribly well. It could do with more RAM and the disk IO seems slow. That said, I'm keen to buy a new server to improve on the current performance, so I've taken the liberty of replying here to my initial mail to ask specifically about new server recommendations. The initial plan was to share some of the load between the current and new server, and to buy something along the following lines: 1U chassis with 8 2.5 disk bays 2x Intel Xeon E5630 Quad-Core / 4x 2.53GHz / 12MB cache 8 channel Areca ARC-1880i (PCI Express x8 card) presumably with BBU (can't see it listed at present) 2 x 300GB SAS 2.5 disks for operating system (Possibly also 300GB SATA VelociRaptor/10K RPM/32MB cache RAID 1 4 x 300GB SAS 2.5 storage disks RAID 10 48.0GB DDR3 1333MHz registered ECC (12x 4.0GB modules) However, after comments on the list, I realise I could get two servers with the following specs for the same price as the above: 2x Intel Xeon E5620 Quad-Core / 4x 2.40GHz / 12MB cache 48.0GB DDR3 1066MHz registered ECC 4 channel Areca ARC-1212 (PCI Express x4 card) + BBU 4 x WD Raptors in RAID 10 (in 3.5 adapters) In other words, for GBP 5k I can get two servers that may better meet between them my requirements (lots of memory, reasonably fast disks) than a single server. A salient point is that individual databases are currently less than 1GB in size but will grow perhaps to be 2GB over the coming 18 months. The aim would be to contain all of the databases in memory on each server. I'd be very grateful for comments on this strategy. Rory -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- 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] Advice sought : new database server
On 04/03/12, Scott Marlowe (scott.marl...@gmail.com) wrote: On Sun, Mar 4, 2012 at 11:36 AM, Rory Campbell-Lange r...@campbell-lange.net wrote: On 04/03/12, Scott Marlowe (scott.marl...@gmail.com) wrote: ... [Description of system with 2 * 4 core Xeons, 8GB RAM, LSI card with 4*15K SCSI drives in R10. We are looking for a new server to partner with this one.] ... We do have complex transactions, but I haven't benchmarked the performance so I can't describe it. Yeah try to get a measurement of how many transactions per second you're running at peak load, and if you're currently IO bound or CPU bound. Our existing server rarely goes above 7% sustained IO according to SAR. Similarly, CPU at peak times is at 5-7% on the SAR average (across all 8 cores). I'm not clear on how to read the memory stats, but the average kbcommit value for this morning's work is 12420282 which (assuming it means about 12GB memory) is 4GB more than physical RAM. However the system never swaps, probably due to our rather parsimonious postgres memory settings. Few of the databases are at the many million row size at the moment, and we are moving to an agressive scheme of archiving old data, so we hope to keep things fast. The key here is that your whole db can fit into memory. 48G is cutting it close if you're figuring on being at 40G in a year. I'd spec it out with 96G to start. That way if you want to set work_mem to 8 or 16M you can without worrying about running the machine out of memory / scramming your OS file system cache with a few large queries etc. Thanks for this excellent point. However I thought 15k disks were a pre-requisite for a fast database system, if one can afford them? The heads have to seek, settle and THEN you ahve to wait for the platters to rotate under the head i.e. latency. I assume if all else is equal the 1880 controller will run 20-40% faster with 15k disks in a write-heavy application. Also I would be grateful to learn if there is a good reason not to use 2.5 SATA disks. The 10k 2.5 seagate drives have combined seek and latency figures of about 7ms, while the15k 2.5 seagate drives have a combined time of about 5ms. Even the fastest 3.5 seagates average 6ms average seek time, but with short stroking can get down to 4 or 5. Now all of this becomes moot if you compare them to SSDs, where the seek settle time is measured in microseconds or lower. The fastest spinning drive will look like a garbage truck next to the formula one car that is the SSD. Until recently incompatabilites with RAID controllers and firmware bugs kept most SSDs out of the hosting center, or made the ones you could get horrifically expensive. The newest generations of SSDs though seem to be working pretty well. From your comments it appears there are 3 options: 1. Card + BBU + SAS disks (10K/15K doesnt matter) + lots of RAM 2. Card + BBU + Raptors + lots of RAM 3. SSDs + lots of RAM Is this correct? If my databases are unlikely to be IO bound might it not be better to go for cheaper drive subsystems (i.e. option 2) + lots of RAM, or alternatively SSDs based on the fact that we don't require much storage space? I am unclear of what the options are on the highly-reliable SSD front, and how to RAID SSD systems. An ancillary point is that our systems are designed to have more rather than fewer databases so that we can scale easily horizontally. -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- 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] Advice sought : new database server
On 05/03/12, Craig James (cja...@emolecules.com) wrote: On Sun, Mar 4, 2012 at 10:36 AM, Rory Campbell-Lange r...@campbell-lange.net wrote: We do have complex transactions, but I haven't benchmarked the performance so I can't describe it. Few of the databases are at the many million row size at the moment, and we are moving to an agressive scheme of archiving old data, so we hope to keep things fast. However I thought 15k disks were a pre-requisite for a fast database system, if one can afford them? I assume if all else is equal the 1880 controller will run 20-40% faster with 15k disks in a write-heavy application. Also I would be grateful to learn if there is a good reason not to use 2.5 SATA disks. Without those benchmarks, you can't really say what fast means. There are many bottlenecks that will limit your database's performance; the disk's spinning rate is just one of them. Memory size, memory bandwidth, CPU, CPU cache size and speed, the disk I/O bandwidth in and out, the disk RPM, the presence of a BBU controller ... any of these can be the bottleneck. If you focus on the disk's RPM, you may be fixing a bottleneck that you'll never reach. We 12 inexpensive 7K SATA drives with an LSI/3Ware 9650SE and a BBU, and have been very impressed by the performance. 8 drives in RAID10, two in RAID1 for the WAL, one for Linux and one spare. This is on an 8-core system with 12 GB memory: pgbench -i -s 100 -U test pgbench -U test -c ... -t ... -c -t TPS 5 2 3777 10 1 2622 20 5000 3759 30 5712 40 2500 5953 50 2000 6141 Thanks for this quick guide to using pgbenc. My 4-year old SCSI server with 4 RAID10 disks behind an LSI card achieved the following on a contended system: -c -t TPS 5 2 446 10 1 542 20 5000 601 30 647 These results seem pretty lousy in comparison to yours. Interesting. -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Advice sought : new database server
I'd be grateful for advice on specifying the new server We presently have one main database server which is performing well. As our services expand we are thinking of bringing another database server to work with it, and back each up via Postgres 9.1 streaming replication each to a VM server -- at present we are doing pg_dumps twice a day and using Postgres 8.4. The existing server is a 2 x Quad core E5420 Xeon (2.5GHz) with 8GB of RAM with an LSI battery-backed RAID 10 array of 4no 10K SCSI disks, providing about 230GB of usable storage, 150GB of which is on an LV providing reconfigurable space for the databases which are served off an XFS formatted volume. We presently have 90 databases using around 20GB of disk storage. However the larger databases are approaching 1GB in size, so in a year I imagine the disk requirement will have gone up to 40GB for the same number of databases. The server also serves some web content. Performance is generally good, although we have a few slow running queries due to poor plpgsql design. We would get faster performance, I believe, by providing more RAM. Sorry -- I should have some pg_bench output to share here. I believe our existing server together with the new server should be able to serve 200--300 databases of our existing type, with around 100 databases on our existing server and perhaps 150 on the new one. After that we would be looking to get a third database server. I'm presently looking at the following kit: 1U chassis with 8 2.5 disk bays 2x Intel Xeon E5630 Quad-Core / 4x 2.53GHz / 12MB cache 8 channel Areca ARC-1880i (PCI Express x8 card) presumably with BBU (can't see it listed at present) 2 x 300GB SAS 2.5 disks for operating system (Possibly also 300GB SATA VelociRaptor/10K RPM/32MB cache RAID 1 4 x 300GB SAS 2.5 storage disks RAID 10 48.0GB DDR3 1333MHz registered ECC (12x 4.0GB modules) My major question about this chassis, which is 1U, is that it only takes 2.5 disks, and presently the supplier does not show 15K SAS disk options. Assuming that I can get the BBU for the Areca card, and that 15K SAS disks are available, I'd be grateful for comments on this configuration. Regards Rory -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- 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] Advice sought : new database server
On 04/03/12, Scott Marlowe (scott.marl...@gmail.com) wrote: On Sun, Mar 4, 2012 at 2:58 AM, Rory Campbell-Lange r...@campbell-lange.net wrote: [About existing server...] We would get faster performance, I believe, by providing more RAM. Sorry -- I should have some pg_bench output to share here. RAM is always a good thing, and it's cheap enough that you can throw 32 or 64G at a machine like this pretty cheaply. Thanks for your note. 1U chassis with 8 2.5 disk bays 2x Intel Xeon E5630 Quad-Core / 4x 2.53GHz / 12MB cache 8 channel Areca ARC-1880i (PCI Express x8 card) presumably with BBU (can't see it listed at present) 2 x 300GB SAS 2.5 disks for operating system (Possibly also 300GB SATA VelociRaptor/10K RPM/32MB cache RAID 1 4 x 300GB SAS 2.5 storage disks RAID 10 48.0GB DDR3 1333MHz registered ECC (12x 4.0GB modules) My major question about this chassis, which is 1U, is that it only takes 2.5 disks, and presently the supplier does not show 15K SAS disk options. Assuming that I can get the BBU for the Areca card, and that 15K SAS disks are available, I'd be grateful for comments on this configuration. The 15k RPM disks aren't that big of a deal unless you're pushing the bleeding edge on a transactional system. I'm gonna take a wild guess that you're not doing heavy transactions, in which case, the BBU on the areca is the single most important thing for you to get for good performance. The areca 1880 is a great controller and is much much easier to configure than the LSI. Performance wise it's one of the fastest DAS controllers made. We do have complex transactions, but I haven't benchmarked the performance so I can't describe it. Few of the databases are at the many million row size at the moment, and we are moving to an agressive scheme of archiving old data, so we hope to keep things fast. However I thought 15k disks were a pre-requisite for a fast database system, if one can afford them? I assume if all else is equal the 1880 controller will run 20-40% faster with 15k disks in a write-heavy application. Also I would be grateful to learn if there is a good reason not to use 2.5 SATA disks. If the guys you're looking at getting this from can't do custom orders, find a white box dealer who can, like www.aberdeeninc.com. It might not be on their site, but they can build dang near anything you want. Thanks for the note about Aberdeen. I've seen the advertisements, but not tried them yet. Thanks for your comments Rory -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Small DB Server Advice
We have a web application for which we intend to run the database on a dedicated server. We hope by the end of 2008 to have 10 companies accessing 10 instances of the database for this application. The dump file of each database is likely to be less than 100MB at the end of the year. The databases are read-heavy. I'm thinking of something along the following lines: (https://secure.dnuk.com/systems/r325hs-1u.php?configuration=7766) 4 x 147GB 15000 rpm SCSI in RAID 10 with 320-1 RAID CARD + 64MB cache BBU 2x Intel Xeon E5405 / 4x 2.00GHz / 1333MHz FSB / 12MB cache 6GB RAM Cost around 2320 GBP -- it would be great to get it under 2000 Needs to be in the UK. I would be grateful for any comments. I'm particularly out of date about the best processors to go for. DNUK also have Opteron as an option. Rory ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] LVM and Postgres
On 06/12/05, Michael Stone ([EMAIL PROTECTED]) wrote: On Tue, Dec 06, 2005 at 07:52:25PM -0500, Alex Turner wrote: I would argue that almost certainly won't by doing that as you will create a new place even further away for the disk head to seek to instead of just another file on the same FS that is probably closer to the current head position. I would argue that you should benchmark it instead of speculating. Is there a good way of benchmarking? We don't have much in the way of test data at present. Regards, Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Disk tests for a new database server
We are testing disk I/O on our new server (referred to in my recent questions about LVM and XFS on this list) and have run bonnie++ on the xfs partition destined for postgres; results noted below. I haven't been able to find many benchmarks showing desirable IO stats. As far as I can tell the sequential input (around 11K/sec) looks good while the sequential output (around 5K/sec) looks fairly average. Advice and comments gratefully received. Suggested Parameters for running pg_bench would be great! Thanks, Rory The server has a dual core AMD Opteron 270 chip (2000MHz), 6GB of RAM and an LSI 320-1 card running 4x147GB disks running in a RAID10 configuration. The server has a freshly compiled 2.6.14.3 linux kernel. partial df output: FilesystemSize Used Avail Use% Mounted on ... /dev/mapper/masvg-masdata 99G 33M 94G 1% /masdata /dev/mapper/masvg-postgres 40G 92M 40G 1% /postgres partial fstab config: ... /dev/mapper/masvg-masdata /masdataext3defaults0 2 /dev/mapper/masvg-postgres /postgres xfs defaults0 2 Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP mas 13000M 52655 99 52951 10 32246 7 41441 72 113438 12 590.0 0 mas 13000M 49306 83 51967 9 32269 7 42442 70 115427 12 590.5 1 mas 13000M 53449 89 51982 10 32625 7 42819 71 111829 11 638.3 0 mas 13000M 51818 88 51639 9 33127 7 42377 70 108585 11 556.5 0 mas 13000M 48930 90 51750 9 32854 7 41220 71 109813 11 566.2 0 mas 13000M 52148 88 47393 9 35343 7 42871 70 109775 12 582.0 0 mas 13000M 52427 88 53040 10 32315 7 42813 71 112045 12 596.7 0 mas 13000M 51967 87 54004 10 30429 7 46180 76 110973 11 625.1 0 mas 13000M 48690 89 46416 9 35678 7 41429 72 111612 11 627.2 0 mas 13000M 52641 88 52807 10 31115 7 43476 72 110694 11 568.2 0 mas 13000M 52186 88 47385 9 35341 7 42959 72 110963 11 558.7 0 mas 13000M 52092 87 53111 10 32135 7 42636 69 110560 11 562.0 1 mas 13000M 49445 90 47378 9 34410 7 41191 72 110736 11 610.3 0 mas 13000M 51704 88 47699 9 35436 7 42413 69 110446 11 612.0 0 mas 13000M 52434 88 53331 10 32479 7 43229 71 109385 11 620.6 0 mas 13000M 52074 89 53291 10 32095 7 43593 72 109541 11 628.0 0 mas 13000M 48084 88 52624 10 32301 7 40975 72 110548 11 594.0 0 mas 13000M 53019 90 52441 10 32411 7 42574 68 111321 11 578.0 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files:max:min/sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP mas 16 7970 36 + +++ 7534 34 7918 36 + +++ 4482 22 mas 16 7945 33 + +++ 7483 30 7918 42 + +++ 4438 20 mas 16 8481 48 + +++ 7468 31 7870 39 + +++ 4385 23 mas 16 7915 36 + +++ 7498 33 7930 41 + +++ 4619 23 mas 16 8553 35 + +++ 8312 38 8613 37 + +++ 4513 24 mas 16 8498 40 + +++ 8215 33 8570 43 + +++ 4858 26 mas 16 7892 39 + +++ 7624 30 5341 28 + +++ 4762 22 mas 16 5408 27 + +++ 9378 37 8573 41 + +++ 4385 21 mas 16 5063 27 + +++ 8656 38 5159 27 + +++ 4705 24 mas 16 4917 25 + +++ 8682 39 5282 28 + +++ 4723 22 mas 16 5027 28 + +++ 8538 36 5173 29 + +++ 4719 23 mas 16 5449 27 + +++ 8630 36 5266 28 + +++ 5463 27 mas 16 5373 27 + +++ 8658 37 5264 26 + +++ 4731 22 mas 16 4959 24 + +++ 9126 46 5160 26 + +++ 4717 24 mas 16 5379 27 + +++ 8620 40 5014 27 + +++ 4701 21 mas 16 5312 29 + +++ 8642 36 7862 42 + +++ 4869 24 mas 16 5057 26 + +++ 8566 36 5120 28 + +++ 4681 21 mas 16 5225 27 + +++ 8740 37 5205 28 + +++ 4744 21 -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Disk tests for a new database server
Hi Steve On 08/12/05, Steve Poe ([EMAIL PROTECTED]) wrote: Rory, While I don't have my specific stats with my from my tests with XFS and bonnie for our company's db server, I do recall vividly that seq. output did not increase dramatically until I had 8+ discs in a RAID10 configuration on an LSI card. I was not using LVM. If I had less than 8 discs, seq. output was about equal regardless of file system being uses (EXT3,JFS,or XFS). Thanks for the information. I certainly had not appreciated this fact. Regards, Rory On Thu, 2005-12-08 at 12:12 +, Rory Campbell-Lange wrote: We are testing disk I/O on our new server (referred to in my recent questions about LVM and XFS on this list) and have run bonnie++ on the xfs partition destined for postgres; results noted below. I haven't been able to find many benchmarks showing desirable IO stats. As far as I can tell the sequential input (around 11K/sec) looks good while the sequential output (around 5K/sec) looks fairly average. Advice and comments gratefully received. Suggested Parameters for running pg_bench would be great! Thanks, Rory ... Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP mas 13000M 52655 99 52951 10 32246 7 41441 72 113438 12 590.0 0 mas 13000M 49306 83 51967 9 32269 7 42442 70 115427 12 590.5 1 mas 13000M 53449 89 51982 10 32625 7 42819 71 111829 11 638.3 0 mas 13000M 51818 88 51639 9 33127 7 42377 70 108585 11 556.5 0 ... --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files:max:min/sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP mas 16 7970 36 + +++ 7534 34 7918 36 + +++ 4482 22 mas 16 7945 33 + +++ 7483 30 7918 42 + +++ 4438 20 mas 16 8481 48 + +++ 7468 31 7870 39 + +++ 4385 23 mas 16 7915 36 + +++ 7498 33 7930 41 + +++ 4619 23 mas 16 8553 35 + +++ 8312 38 8613 37 + +++ 4513 24 mas 16 8498 40 + +++ 8215 33 8570 43 + +++ 4858 26 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] LVM and Postgres
I need to slice up a web server's disk space to provide space for postgres and storing binaries such as images and sound files. I'm thinking of using logical volume management (LVM) to help manage the amount of space I use between postgres and the data volumes. The server has a 250GB RAID10 (LSI 320-I + BBU) volume which I am thinking of slicing up in the following way (Linux 2.6 kernel): / : ext3 : 47GB (root, home etc) /boot : ext3 : 1GB /tmp : ext2 : 2GB /usr : ext3 : 4GB /var : ext3 : 6GB --- 60GB VG:190GB approx --- Initially divided so: /data : ext3 : 90GB /postgres : xfs : 40GB This gives me left over space of roughly 60GB to extend into on the volume group, which I can balance between the /data and /postgres logical volumes as needed. Are there any major pitfalls to this approach? Thanks, Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] LVM and Postgres
Hi August. Thanks very much for your mail. On 06/12/05, August Zajonc ([EMAIL PROTECTED]) wrote: Rory Campbell-Lange wrote: The server has a 250GB RAID10 (LSI 320-I + BBU) volume which I am thinking of slicing up in the following way (Linux 2.6 kernel): / : ext3 : 47GB (root, home etc) /boot : ext3 : 1GB /tmp : ext2 : 2GB /usr : ext3 : 4GB /var : ext3 : 6GB --- 60GB VG:190GB approx --- Initially divided so: /data : ext3 : 90GB /postgres : xfs : 40GB This gives me left over space of roughly 60GB to extend into on the volume group, which I can balance between the /data and /postgres logical volumes as needed. It looks like you are using fast disks and xfs for filesystem on the /postgresql partition. That's nice. How many disks in the array? Four. One thing you miss is sticking a bunch of sequential log writes on a separate spindle as far as I can see with this? WAL / XFS (i think) both have this pattern. If you've got a fast disk and can do BBU write caching your WAL writes will hustle. Yes, we don't have any spare disks unfortunately. We have enabled the BBU write, so we are hoping for good performance. I'd be grateful for some advice on dd/bonnie++ tests for checking this. Others can probably speak a bit better on any potential speedups. I'd better test extending the Logical Volumes too! Many thanks Rory ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Dividing up a single 250GB RAID10 server for postgres
Hi. We have a server provided for a test of a web application with the following specifications: 1 Dual core 1.8GHz Opteron chip 6 GB RAM approx 250GB of RAID10 storage (LSI card + BBU, 4 x 15000 RPM,16MB Cache SCSI disks) The database itself is very unlikely to use up more than 50GB of storage -- however we are going to be storing pictures and movies etc etc on the server. I understand that it is better to run pg_xlog on a separate spindle but we don't have that option available at present. Normally I use ext3. I wondered if I should make my normal partitions and then make a +/- 200GB LVM VG and then slice that initially into a 100GB ext3 data directory and a 50GB xfs postgres data area, giving 100GB to use between these as they grow. I haven't used LVM with xfs before, however. Advice gratefully received. Rory ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Help specifying new web server/database machine
Hi All. Thanks very much for Joshua, William, Bjoern and Matthew's replies. I've now looked at the famous Server for 7K thread. In my case we are looking for a server for around 3000 pounds (UK); the server is to be an all-purpose web and database server. Processor: First of all I noted that we were intending to use Opteron processors. I guess this isn't a straightforward choice because I believe Debian (our Linux of choice) doesn't have a stable AMD64 port. However some users on this list suggest that Opterons work very well even in a 32 bit environment. Some have suggested that a single dual core processor is the way to go. The RAM needs to fit the CPU arrangement too; William points out that one needs 2 DIMMS per CPU. Disks: I'm somewhat confused here. I've followed the various notes about SATA vs SCSI and it seems that SCSI is the way to go. On a four-slot 1U server, would one do a single RAID10 over 4 disks 1rpm U320 disks? I would run the database in its own partition, separate from the rest of the OS, possible on LVM. An LSI-Megaraid-2 appears to be the card of choice. The following (without RAID card) breaks my budget by about 200 pounds: System: Armari Opteron AM-2138-A8 1U Base PCI-X (BEI) Case Accessories : IPMI 2.0 module for AM Series Opteron Servers CPU : AMD Opteron 265 - Dual Core 1.8GHz CPU (940pin) Memory: 2GB 400MHz DDR SDRAM (4 x 512MB (PC3200) ECC REG.s) Hard drive: Maxtor Atlas 10K V 147.1GB 10K U320/SCA - 8D147J0 Additional Drives : 3 x Maxtor Atlas 10K V 147.1GB 10K U320/SCA - 8D147J0 CD/DVD Drive : AM series Server 8x Slimline DVD-ROM Warranty : 3 Year Return to base Warranty (Opteron Server) Carriage : PC System Carriage (UK only) for 1U Server Thanks for any further comments, Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Help specifying new web server/database machine
On 09/06/05, William Yu ([EMAIL PROTECTED]) wrote: Rory Campbell-Lange wrote: ... Some have suggested that a single dual core processor is the way to go. The RAM needs to fit the CPU arrangement too; William points out that one needs 2 DIMMS per CPU. Your summary here just pointed out the obvious to me. Start with a 2P MB but only populate a single DC Opteron. That'll give you 2P system with room to expand to 4P in the future. Plus you only need to populate 1 memory bank so you can do 2x1GB. That makes sense. I should by a board with support for 2 Dual-core Opterons, but only use one Opteron for the moment. Then I should buy 2x1GB RAM sticks to service that processor. ... On a four-slot 1U server, would one do a single RAID10 over 4 disks 1rpm U320 disks? I would run the database in its own partition, separate from the rest of the OS, possible on LVM. An LSI-Megaraid-2 appears to be the card of choice. With only 4 disks, a MegaRAID U320-1 is good enough. It's quite a premium to go to the 2x channel MegaRAID. With 4 drives, I'd still do 2 big drives mirrored for the DB partition and 2 small drives for OS+WAL. Should these all RAID1? I'm a bit worried about how to partition up my system if it is strictly divided between a system RAID1 disk entity and a DB disk entity, as the proportion of web server content (images, movies, sounds) to actual database data is an unknown quantity at the moment. I typically keep all the database stuff in a /var logical partition and for this project would expect to keep the web stuff under a /web logical partition. I was thinking of using LVM to be able to shift around space on a large (4 x 147GB RAID 1 or RAID10) raided volume. I appreciate that this may not be optimal for the WAL transaction log. Thanks for your comments; Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help specifying new web server/database machine
On 09/06/05, Matthew Nuzum ([EMAIL PROTECTED]) wrote: On 6/9/05, Rory Campbell-Lange [EMAIL PROTECTED] wrote: Disks: I'm somewhat confused here. I've followed the various notes about SATA vs SCSI and it seems that SCSI is the way to go. On a four-slot 1U server, would one do a single RAID10 over 4 disks 1rpm U320 disks? I would run the database in its own partition, separate from the rest of the OS, possible on LVM. An LSI-Megaraid-2 appears to be the card of choice. Can you tell us about your application? How much data will you have, what is your ratio of reads to writes, how tollerant to data loss are you? (for example, some people load their data in batches and if they loose their data its no big deal, others would have heart failure if a few transactions were lost) The application is a web-based prototype system for kids to make their own galleries based on content found in museums and galleries. They will link to content provided by curators, and be able to add in their own material, including movies, sounds and pictures. All the content, however, will be restricted in size. I also do not intend to store the movies, sounds or pictures in the database (although I have happily done the latter in the past). Up to the data will be uploaded from 3G handsets. The rest will be done on a per-user, per-pc basis through the web interface. The service is expected to be used by about 5 users over 18 months. Of these around half will be content creators, so will account for say half a million rows in the main content table and under 2 million rows in the commentary table. The most used table will probably be a 'history' function required by the contract, tracking use through the site. I imagine this will account for something like 20 million rows (with very little data in them). The main tables will have something like 80% read, 20% write (thumb suck). The history table will be read by an automated process at 3 in the morning, to pick up some stats on how people are using the system. It wouldn't be a problem to very occasionally (once a month) lose a tiny piece of data (i.e a record). Losing any significant amounts of data is entirely out of the question. If your application is 95% writes then people will suggest drastically different hardware than if your application is 95% selects. Here is an example of one of my servers: application is 95+% selects, has 15GB of data (counting indexes), low tollerance for data loss, runs on a 1 GHz P3 Compaq server with mirrored 35 GB IDE disks and 1.6GB of RAM. Application response time is aproximately .1 second to serve a request on a moderately loaded server. Yeah. Maybe the machine I'm speccing up is total overkill for this project? I'm just worried that if it is a big success, or if we have 400 kids pounding the server at once over high-speed school lines, the thing will grind to a halt. Thanks very much for your comments. Regards, Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] Help specifying new web server/database machine
I'm tasked with specifying a new machine to run a web application prototype. The machine will be serving web pages with a Postgresql backend; we will be making extensive use of plpgsql functions. No database tables are likely to go over a million rows during the prototype period. We are considering two RAID1 system disks, and two RAID1 data disks. We've avoided buying Xeons. The machine we are looking at looks like this: Rackmount Chassis - 500W PSU / 4 x SATA Disk Drive Bays S2882-D - Dual Opteron / AMD 8111 Chipset / 5 x PCI Slots 2x - (Dual) AMD Opteron 246 Processors (2.0GHz) - 1MB L2 Cache/core (single core) 2GB (2x 1024MB) DDR-400 (PC3200) ECC Registered SDRAM (single rank) 4 Port AMCC/3Ware 9500-4LP PCI SATA RAID Controller 80GB SATA-150 7200RPM Hard Disk / 8MB Cache 80GB SATA-150 7200RPM Hard Disk / 8MB Cache 250GB SATA-150 7200RPM Hard Disk / 8MB Cache 250GB SATA-150 7200RPM Hard Disk / 8MB Cache Slimline 8x DVD / 24x CD-ROM Drive Standard 3yr (UK) Next Business Day On-site Warranty I would be grateful for any comments about this config. Kind regards, Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Help specifying new machine
I'm thinking of upgrading an existing dedicated server and colocating my own server. The server is used for prototype systems running Postgresql, php and apache. The largest database is presently under 10GB and I haven't had any major performance problems. We expect to have to host larger data sets in the next year and anticipate one or two databases reaching the 30GB mark in size. We write a lot of our webapps functionality using pl/pgsql. The present server is a 2GHz Pentium 4/512 KB cache with 2 software-raided ide disks (Maxtors) and 1GB of RAM. I have been offered the following 1U server which I can just about afford: 1U server Intel Xeon 2.8GHz 512K cache 1 512MB PC2100 DDR ECC Registered 2 80Gb SATA HDD 4 4 port SATA card, 3 ware 8506-4 1 3 year next-day hardware warranty 1 There is an option for dual CPUs. I intend to install the system (Debian testing) on the first disk and run the other 3 disks under RAID5 and ext3. I'm fairly ignorant about the issues relating to SATA vs SCSI and what the best sort of RAM is for ensuring good database performance. I don't require anything spectacular, just good speedy general performance. I imagine dedicating around 25% of RAM to Shared Memory and 2-4% for Sort memory. Comments and advice gratefully received. Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
