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]
