[PERFORM] Increasing RAM for more than 4 Gb. using postgresql
I wonder if I would like to increase more RAM from 4 Gb. to 6 Gb. [which I hope to increase more performance ] and I now I used RH 9 and Pgsql 7.3.2 ON DUAL Xeon 3.0 server thay has the limtation of 4 Gb. ram, I should use which OS between FC 2-3 or redhat EL 3 [which was claimed to support 64 Gb.ram] .May I use FC 2 [which is freely downloaded] with 6 Gb. and PGsql 7.4 ? Amrit Thailand ---(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: [ADMIN] [PERFORM] Assimilation of these "versus" and hardware threads
Matt, > I had one comment on the pg_autovacuum section. Near the bottom it > lists some of it's limitations, and I want to clarify the 1st one: "Does > not reset the transaction counter". I assume this is talking about the > xid wraparound problem? If so, then that bullet can be removed. > pg_autovacuum does check for xid wraparound and perform a database wide > vacuum analyze when it's needed. Keen. That's an 8.0 fix? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Assimilation of these "versus" and hardware threads
People: > All of these recent threads about fastest hardware and "who's better than > who" has inspired me to create a new website: > > http://www.dbtuning.org Well, time to plug my web site, too, I guess: http://www.powerpostgresql.com I've got a configuration primer up there, and the 8.0 Annotated .Conf file will be coming this week. That web site runs on Framewerk, a PostgreSQL-based CMS developed by our own Gavin Roy. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Assimilation of these "versus" and hardware threads
All of these recent threads about fastest hardware and "who's better than who" has inspired me to create a new website: http://www.dbtuning.org I snipped a few bits from recent posts to get some pages started - hope the innocent don't mind. It's a bit postgres biased at the moment, since well, so am I (though FireBird is now mounting a strong showing...) This site uses a wiki so anyone interested can make contributions. We are all short on time, so I would love any help. I haven't entered any hardware info yet. I'll also take a minute to plug a postgres saavy open-source project used for this site - http://www.tikipro.org - It's a very flexible web framework with a very powerful and extendible CMS engine. It just hit Alpha 4, and we hope to go beta very soon. If you have feedback (or bugs), please send me a note. (and of course dbtuning is running on postgres ;-) [ \ / [ >X< Christian Fowler | spider AT viovio.com [ / \ http://www.viovio.com | http://www.tikipro.org ---(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] PostgreSQL vs. Oracle vs. Microsoft
Dave Cramer wrote: I understand that but I have seen VM's crash. This does bring up another point. Since postgresql is not threaded a .NET pl would require a separate VM for each connection (unless you can share the vm ?). One of the java pl's (pl-j) for postgres has dealt with this issue. For a hundred connections that's a hundred .NET vm's or java vm's. Is the .NET VM shareable ? In Windows, most certainly. Not sure about mono. Cheers, Gary. ---(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] which dual-CPU hardware/OS is fastest for PostgreSQL?
Steinar H. Gunderson wrote: On Mon, Jan 10, 2005 at 08:31:22PM -0800, Joshua D. Drake wrote: Unless something has changed though, you can't run raid 10 with linux software raid Hm, why not? What stops you from making two RAID-0 devices and mirroring those? (Or the other way round, I can never remember :-) ) O.k. that seems totally wrong ;) but yes your correct you could probably do it. Sincerely, Josuha D. Drake /* Steinar */ -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > heh, our apps do tend to be CPU bound. Generally, I think the extra CPU > horsepower is worth the investment until you get to the really high end > cpus. I find that while most applications I work with shouldn't be cpu intensive they do seem end up being cpu bound quite frequently. What happens is that 90% of the workload has a working set that fits in RAM. So the system ends up being bound by the memory bus speed. That appears exactly the same as cpu-bound, though I'm unclear whether increasing the cpu clock will help. It's quite possible to have this situation at the same time as other queries are i/o bound. It's quite common to have 95% of your workload be frequently executed fast queries on commonly accessed data and 5% be bigger data warehouse style queries that need to do large sequential reads. Incidentally, the same was true for Oracle on Solaris. If we found excessive cpu use typically meant some frequently executed query was using a sequential scan on a small table. Small enough to fit in RAM but large enough to consume lots of cycles reading it. -- greg ---(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] PostgreSQL vs. Oracle vs. Microsoft
Jim wrote: you'd be hard-pressed to find too many real-world examples where you could do something with a PostgreSQL procedural language that you couldn't do with PL/SQL. Rick mumbled: You can't get it for nothing! %) "Jim C. Nasby" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Sent by: cc: Frank Wiles <[EMAIL PROTECTED]>, Yann Michel <[EMAIL PROTECTED]>, [EMAIL PROTECTED] [EMAIL PROTECTED], pgsql-performance@postgresql.org tgresql.orgSubject: Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft 01/10/2005 06:29 PM On Mon, Jan 10, 2005 at 12:46:01PM -0500, Alex Turner wrote: > You sir are correct! You can't use perl in MS-SQL or Oracle ;). On the other hand, PL/SQL is incredibly powerful, especially combined with all the tools/utilities that come with Oracle. I think you'd be hard-pressed to find too many real-world examples where you could do something with a PostgreSQL procedural language that you couldn't do with PL/SQL. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
while you weren't looking, Greg Stark wrote: > Back in the day, we used to have problems with our 1U dual pentiums. We > attributed it to heat accelerating failure. I would fear four opterons in 1U > would be damned hard to cool effectively, no? Opterons actually run pretty coolly, comparatively. If it's a big concern, you can always drop a few more clams for the low-voltage versions -- available in 1.4 and 2.0 GHz flavors, and of which I've heard several accounts of their being run successfully /without/ active cooling -- or punt until later this year, when they ship Winchester core Opterons (90nm SOI -- the current, uniprocessor silicon fabbed with that process has some 3W heat dissipation idle, ~30W under full load; as a point of contrast, current 90nm P4s have 34W idle dissipation, and some 100W peak). We have a number of 1U machines (P4s, I believe), and a Dell blade server (six or seven P3 machines in a 3U cabinet) as our webservers, and none of them seem to have any trouble with heat. That's actually a bigger deal than it might first seem, given how frighteningly crammed with crap our machine room is. /rls -- :wq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
> $4000 is not going to get you much disk - If you buy components from > the cheapest source I know (newegg.com) you end up around $5k with > 14x36gig Raptor SATA drives and a 4U chasis with a 14xSATA built in > back plane packing 2x9500S AMCC Escalade RAID cards, which are > supported in Linux, 4Gig RAM and 2xOpteron 242. If you are not CPU > bound, there isn't much point going to 246. If you want SCSI, then > you will be paying more. Check out rackmountmart.com for Chasises, > they have a nice 5U that has a 24xSATA backplane (We will be acquiring > this in the next few weeks). If you really want to go nuts, they have > an 8U with 40xSATA backplane. > > Alex Turner > NetEconomist heh, our apps do tend to be CPU bound. Generally, I think the extra CPU horsepower is worth the investment until you get to the really high end cpus. I definitely agree with all your hardware choices though...seems like you've hit the 'magic formula'. Merlin ---(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] which dual-CPU hardware/OS is fastest for PostgreSQL?
$4000 is not going to get you much disk - If you buy components from the cheapest source I know (newegg.com) you end up around $5k with 14x36gig Raptor SATA drives and a 4U chasis with a 14xSATA built in back plane packing 2x9500S AMCC Escalade RAID cards, which are supported in Linux, 4Gig RAM and 2xOpteron 242. If you are not CPU bound, there isn't much point going to 246. If you want SCSI, then you will be paying more. Check out rackmountmart.com for Chasises, they have a nice 5U that has a 24xSATA backplane (We will be acquiring this in the next few weeks). If you really want to go nuts, they have an 8U with 40xSATA backplane. Alex Turner NetEconomist On Tue, 11 Jan 2005 08:33:09 -0500, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > Subject: [PERFORM] which dual-CPU hardware/OS is fastest for > PostgreSQL? > > > > I'm sorry if there's a URL out there answering this, but I couldn't > find > > it. > > > > For those of us that need the best performance possible out of a > > dedicated dual-CPU PostgreSQL server, what is recommended? > > > > AMD64/Opteron or i386/Xeon? > > > > Linux or FreeBSD or _?_ > > > > I'm assuming hardware RAID 10 on 15k SCSI drives is fastest disk > > performance. > > > > Any hardware-comparison benchmarks out there showing the results for > > different PostgreSQL setups? > > My recommendation would be: > 2 way or 4 way Opteron depending on needs (looking on a price for 4-way? > Go here: http://www.swt.com/qo3.html). Go no less than Opteron 246. > Tyan motherboard > Serial ATA controller by 3ware (their latest escalade series size for > your needs) (if money is no object, go scsi). Make sure you pick up the > bbu. > Redhat Linux FC3 x86-64 > Good memory (DDR400 registered, at least)...lots of it. > > You can get a two way rackmount for under 4000$. You can get a 4-way > for under 10k$. Make sure you pick up a rackmount case that has a > serial ATA backplane that supports led status light for disk drives, and > make sure you get the right riser, heh. > > Merlin > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
Rosser Schwarz <[EMAIL PROTECTED]> writes: > Try also the Appro 1U 4-way Opteron server, at: > http://www.appro.com/product/server_1142h.asp Back in the day, we used to have problems with our 1U dual pentiums. We attributed it to heat accelerating failure. I would fear four opterons in 1U would be damned hard to cool effectively, no? -- greg ---(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] PostgreSQL vs. Oracle vs. Microsoft
Connect to an external data system using a socket and propagate data changes using a trigger... I've had to do this, and it sucks to be stuck in Oracle! Alex Turner NetEconomist On Mon, 10 Jan 2005 17:29:52 -0600, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > On Mon, Jan 10, 2005 at 12:46:01PM -0500, Alex Turner wrote: > > You sir are correct! You can't use perl in MS-SQL or Oracle ;). > > On the other hand, PL/SQL is incredibly powerful, especially combined > with all the tools/utilities that come with Oracle. I think you'd be > hard-pressed to find too many real-world examples where you could do > something with a PostgreSQL procedural language that you couldn't do > with PL/SQL. > -- > Jim C. Nasby, Database Consultant [EMAIL PROTECTED] > Give your computer some brain candy! www.distributed.net Team #1828 > > Windows: "Where do you want to go today?" > Linux: "Where do you want to go tomorrow?" > FreeBSD: "Are you guys coming, or what?" > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(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] which dual-CPU hardware/OS is fastest for PostgreSQL?
while you weren't looking, Merlin Moncure wrote: > 2 way or 4 way Opteron depending on needs (looking on a price for 4-way? > Go here: http://www.swt.com/qo3.html). Try also the Appro 1U 4-way Opteron server, at: http://www.appro.com/product/server_1142h.asp I specced a 4-way 842 (1.6 GHz: little to none of our db work is CPU bound; there's just a lot of it going on at once) with 32G core for within delta of what SWT wants /just/ for the 32G -- the price of the box itself and anything else atop that. Stepping up to a faster CPU should increase the cost directly in line with the retail price for the silicon. We haven't yet ordered the machine (and the quote was from early last month, so their prices will have fluctuated) and consequently, I can't comment on their quality. Their default warranty is three years, "rapid exchange", though, and they offer on-site service for only nominally more, IIRC. Some slightly more than cursory googling hasn't turned up anything overly negative, either. As a 1U, the box has no appreciable storage of its own but we're shopping for a competent, non bank-breaking fibre setup right now, so that's not an issue for our situation. While on the subject, anyone here have anything to say about JMR fibre raid cabinets? (Fibre-to-fibre, not fibre-to-SATA or the like.) /rls -- :wq ---(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] Best filesystem for PostgreSQL Database Cluster under Linux
After a long battle with technology, "Pete de Zwart" <[EMAIL PROTECTED]>, an
earthling, wrote:
> Greetings to one and all,
>
> I've been trying to find some information on selecting an optimal
> filesystem setup for a volume that will only contain a PostgreSQL Database
> Cluster under Linux. Searching through the mailing list archive showed some
> promising statistics on the various filesystems available to Linux, ranging
> from ext2 through reiserfs and xfs.
>
> I have come to understand that PostgreSQLs Write Ahead Logging
> (WAL) performs a lot of the journal functionality provided by the
> majoirty of contemporary filesystems and that having both WAL and
> filesystem journalling can degrade performance.
>
> Could anyone point me in the right direction so that I can read
> up some more on this issue to discern which filesystem to choose and
> how to tune both the FS and PostgreSQL so that they can compliment
> each other? I've attempted to find this information via the FAQ,
> Google and the mailing list archives but have lucked out for the
> moment.
Your understanding of the impact of filesystem journalling isn't
entirely correct. In the cases of interest, journalling is done on
metadata, not on the contents of files, with the result that there
isn't really that much overlap between the two forms of "journalling"
that are taking place.
I did some benchmarking last year that compared, on a write-heavy
load, ext3, XFS, and JFS.
I found that ext3 was materially (if memory serves, 15%) slower than
the others, and that there was a persistent _slight_ (a couple
percent) advantage to JFS over XFS.
This _isn't_ highly material, particularly considering that I was
working with a 100% Write load, whereas "real world" work is likely to
have more of a mixture.
If you have reason to consider one filesystem or another better
supported by your distribution vendor, THAT is a much more important
reason to pick a particular filesystem than 'raw speed.'
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/fs.html
Rules of the Evil Overlord #138. "The passageways to and within my
domain will be well-lit with fluorescent lighting. Regrettably, the
spooky atmosphere will be lost, but my security patrols will be more
effective."
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
I understand that but I have seen VM's crash. This does bring up another point. Since postgresql is not threaded a .NET pl would require a separate VM for each connection (unless you can share the vm ?). One of the java pl's (pl-j) for postgres has dealt with this issue. For a hundred connections that's a hundred .NET vm's or java vm's. Is the .NET VM shareable ? Dave Gary Doades wrote: Dave Cramer wrote: Ok, so one use case is to select a large number of rows and do some non-trivial operation on them. I can see where getting the rows inside the server process ( ie some procedural language ) thereby reducing the round trip overhead would be beneficial. However how do you deal with the lack of control ? For instance what happens if you run out of memory while doing this ? I'm not sure about other DB'S but if you crash the procedural language inside postgres you will bring the server down. It would seem to me that any non-trivial operation would be better handled outside the server process, even if it costs you the round trip. Since a .NET language is operating effectively inside a VM it is pretty much impossible to bring down the server that way. Only a bug in the .NET runtime itself will do that. The C# try/catch/finally with .NET global execption last chance handlers will ensure the server and your code is well protected. Cheers, Gary. ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
> Subject: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL? > > I'm sorry if there's a URL out there answering this, but I couldn't find > it. > > For those of us that need the best performance possible out of a > dedicated dual-CPU PostgreSQL server, what is recommended? > > AMD64/Opteron or i386/Xeon? > > Linux or FreeBSD or _?_ > > I'm assuming hardware RAID 10 on 15k SCSI drives is fastest disk > performance. > > Any hardware-comparison benchmarks out there showing the results for > different PostgreSQL setups? My recommendation would be: 2 way or 4 way Opteron depending on needs (looking on a price for 4-way? Go here: http://www.swt.com/qo3.html). Go no less than Opteron 246. Tyan motherboard Serial ATA controller by 3ware (their latest escalade series size for your needs) (if money is no object, go scsi). Make sure you pick up the bbu. Redhat Linux FC3 x86-64 Good memory (DDR400 registered, at least)...lots of it. You can get a two way rackmount for under 4000$. You can get a 4-way for under 10k$. Make sure you pick up a rackmount case that has a serial ATA backplane that supports led status light for disk drives, and make sure you get the right riser, heh. Merlin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
On 11 Jan 2005 04:25:04 GMT Christopher Browne <[EMAIL PROTECTED]> wrote: > Xeon sux pretty bad... > > > Linux or FreeBSD or _?_ > > The killer question won't be of what OS is "faster," but rather of > what OS better supports the fastest hardware you can get your hands > on. Well, if multiple OSs work on the hardware you like, there is nothing wrong with selecting the fastest among them of course. As for Linux or FreeBSD, you may also want to consider NetBSD. It seems that with the latest releases of both, NetBSD outperforms FreeBSD in at least one benchmark. http://www.feyrer.de/NetBSD/gmcgarry/ The benchmarks were run on a single processor but you can always run the benchmark on whatever hardware you select - assuming that it runs both. Isn't there also a PostgreSQL specific benchmark available? -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
...and on Mon, Jan 10, 2005 at 08:31:22PM -0800, Joshua D. Drake used the keyboard: > > > > >RAID controllers tend to use i960 or StrongARM CPUs that run at speeds > >that _aren't_ all that impressive. With software RAID, you can take > >advantage of the _enormous_ increases in the speed of the main CPU. > > > >I don't know so much about FreeBSD's handling of this, but on Linux, > >there's pretty strong indication that _SOFTWARE_ RAID is faster than > >hardware RAID. > > > > > Unless something has changed though, you can't run raid 10 > with linux software raid and raid 5 sucks for heavy writes. > > J Hello, Joshua. Things have changed. :) From 2.6.10's drivers/md/Kconfig: config MD_RAID10 tristate "RAID-10 (mirrored striping) mode (EXPERIMENTAL)" depends on BLK_DEV_MD && EXPERIMENTAL ---help--- RAID-10 provides a combination of striping (RAID-0) and mirroring (RAID-1) with easier configuration and more flexable layout. Unlike RAID-0, but like RAID-1, RAID-10 requires all devices to be the same size (or atleast, only as much as the smallest device will be used). RAID-10 provides a variety of layouts that provide different levels of redundancy and performance. RAID-10 requires mdadm-1.7.0 or later, available at: ftp://ftp.kernel.org/pub/linux/utils/raid/mdadm/ There is a problem, however, that may render software RAID non-viable though. According to one of my benchmarks, it makes up for an up to 10% increase in system time consumed under full loads, so if the original poster's application is going to be CPU-bound, which might be the case, as he is looking for a machine that's strong on the CPU side, that may be the "too much" bit. Of course, if Opteron is being chosen for the increase in the amount of memory it can address, this is not the issue. HTH, -- Grega Bremec gregab at p0f dot net pgpte0vXuMyG4.pgp Description: PGP signature
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
On Mon, Jan 10, 2005 at 08:31:22PM -0800, Joshua D. Drake wrote: > Unless something has changed though, you can't run raid 10 > with linux software raid Hm, why not? What stops you from making two RAID-0 devices and mirroring those? (Or the other way round, I can never remember :-) ) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(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
