Re: [PERFORM] Optimizing disk throughput on quad Opteron
You can find a diagram of my initial spec here: http://img266.imageshack.us/img266/9171/dbserverdiagramuc3.jpg Can you guys see any glaring bottlenecks in my layout? Any other suggestions to offer (throw in more controllers, different RAID layout, etc.)? Our budget limit is $50k. The thing I would ask is would you not be better with SAS drives? Since the comments on Dell, and the highlighted issues I have been looking at HP and the the Smart Array P600 controller with 512 BBWC. Although I am looking to stick with the 8 internal disks, rather than use external ones. The HP Smart Array 50 is the external array for SAS drives. Not really looked into it much though. Regards Ben ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] New hardware thoughts
Hello all, I am currently working out the best type of machine for a high volume pgsql database that I going to need for a project. I will be purchasing a new server specifically for the database, and it won't be running any other applications. I will be using FreeBSD 6.1 Stable. I think it may be beneficial if I give a brief overview of the types of database access. There are several groups of tables and associated accesses to them. The first can be thought of as users details and configuration tables. They will have low read and write access (say around 10 - 20 a min). SIzed at around 1/2 Million rows. The second part is logging, this will be used occasionally for reads when reports are run, but I will probably back that off to more aggregated data tables, so can probably think of this as a write only tables. Several table will each have around 200-300 inserts a second. The can be archived on a regular basis to keep the size down, may be once a day, or once a week. Not sure yet. The third part will be transactional and will have around 50 transaction a second. A transaction is made up of a query followed by an update, followed by approx 3 inserts. In addition some of these tables will be read out of the transactions at approx once per second. There will be around 50 simultaneous connections. I hope that overview is a) enough and b) useful background to this discussion. I have some thoughts but I need them validating / discussing. If I had the money I could buy the hardware and sped time testing different options, thing is I need to get this pretty much right on the hardware front first time. I'll almost certainly be buying Dell kit, but could go for HP as an alternative. Processor : I understand that pgsql is not CPU intensive, but that each connection uses its own process. The HW has an option of upto 4 dual core xeon processors. My thoughts would be that more lower spec processors would be better than fewer higher spec ones. But the question is 4 (8 cores) wasted because there will be so much blocking on I/O. Is 2 (4 cores) processors enough. I was thinking 2 x 2.6G dual core Xeons would be enough. Memory : I know this is very important for pgsql, and the more you have the more of the tables can reside in memory. I was thinking of around 8 - 12G, but the machine can hold a lot more. Thing is memory is still quite expensive, and so I don't to over spec it if its not going to get used. Disk : Ok so this is the main bottleneck of the system. And the thing I know least about, so need the most help with. I understand you get good improvements if you keep the transaction log on a different disk from the database, and that raid 5 is not as good as people think unless you have lots of disks. My option in disks is either 5 x 15K rpm disks or 8 x 10K rpm disks (all SAS), or if I pick a different server I can have 6 x 15K rpm or 8 x 10K rpm (again SAS). In each case controlled by a PERC 5/i (which I think is an LSI Mega Raid SAS 8408E card). So the question here is will more disks at a slower speed be better than fewer disks as a higher speed? Assuming I was going to have a mirrored pair for the O/S and transaction logs that would leave me with 3 or 4 15K rpm for the database, 3 would mean raid 5 (not great at 3 disks), 4 would give me raid 10 option if I wanted it. Or I could have raid 5 across all 5/6 disks and not separate the transaction and database onto different disks. Better performance from raid 5 with more disks, but does having the transaction logs and database on the same disks counteract / worsen the performance? If I had the 8 10K disks, I could have 2 as a mirrored pair for O/S Transaction, and still have 6 for raid 5. But the disks are slower. Anybody have any good thoughts on my disk predicament, and which options will serve me better. Your thoughts are much appreciated. Regards Ben ---(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] New hardware thoughts
Cheers Shane, Sounds like you have a very good idea of what to expect. Are these solid stats or certain estimates? Estimates can vary when it comes time to start. The figures all come from how my application interacts with the database when an event happens, so the scaling of operations to each other is accurate, the number of operations is based on an estimate of the user interactions with the system, and the figures I quote are actually peak figures based on some fairly reliable research. If anything its more likely to be lower then higher, but I like to air on the side of caution, and so its important for know that I can sustain this throughput, and have an easy upgrade path in the hardware I choose now to help if I do need to be able to cope with more load in the future. Although I suspect the next step would be to move things like the logging into a separate database to relieve some of the load. I would think 2 will cope with what you describe but what about in 12 months time? Can you be sure your needs won't increase? And will the cost of 4 CPU's cut your other options? If all 50 users may be running the 3rd part at the same time (or is that your 50 trans. a second?) then I'd consider the 4. The 50 connections is pretty much a constant from the distributes application servers, and only some about 10 of them will be responsible for running the transactions , the others being more related to the reading, and logging, and thus mainly staying in the idle state. So I would think I am better off keeping the CPU sockets spare, and adding them if needed. Thus enabling more budget for memory / disks. 8GB is a good starting point for a busy server but a few hundred $ on the extra ram can make more difference than extra disks (more for the reading part than writing). I guess any spare budget I have after the disks should be spend on as much memory as possible. What you describe plans several times 300 inserts to logging plus 150 inserts and 50 updates and 1 read a second plus occasional reads to the logging and user data. Will it be raw data fed in and saved or will the server be calculating a majority of the inserted data? If so go for the 4 cpu's. The inserts are all raw (pre calculated) data, so not work needed by the database server its self bar the actual insert. Generally more disks at slower speed - 2 10K disks in raid 0 is faster than 1 15K disk. More disks also allow more options. Yes I figured striped slow disks are faster then non striped fast disks, but what about 8 striped slow disks vs 5 striped fast disks? How do you calculate what the maximum throughput of a disk system would be? I know that a bit academic really as I need to split the disks up for the transfer log and the table data, so the large number of slower disks is as you suggest better anyway. I might consider RAID 5 with 8 disks but would lean more for 2 RAID 10 setups. This can give you the reliability and speed with system and xlog on one and data on the other. Assuming I go with 8 disks, I guess the real question I have no idea about is the speed relationship of the transfer log to the table space data. In other words if I have 2 disks in a raid 1 mirrored pair for the transfer log (and the O/S, but can't see it needing to use disk once boots really - so long as it does not need swap space) and 6 disks in a raid 1 + 0 striped mirrored pair would that be better than having 2 equal raid 1 + 0 sets of 4 disks. Clearly if the requirements on the transfer log are the same as the table data then 2 equal 1+0 sets are better, but if the table data is at least 1/3 more intensive that the transfer log I think the 2 + 6 should be better. Does anybody know which it is? Sounds to me like you have it worked out even if you are a little indecisive on a couple of finer points. Thanks, I guess its more about validating my thoughts are more or less right, and helping tweak the bits that could be better. Regards Ben ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] New hardware thoughts
You mentioned a Perc controller, so I'll assume this is a Dell. My advice is to find another supplier. check the archives for Dell. Basically you have no idea what the Perc controller is since it is whatever Dell decides to ship that day. In general though you are going down the right path here. Disks first, memory second, cpu third Dave Yes I am looking at either the 2950 or the 6850. I think the only think that the 6850 really offers me over the 2950 is more expandability in the spare processor, and additional memory sockets. In all other respects the config I am looking at would fit either chassis. Although the 2950, being slightly newer has the DRAC 5 (dells implementation of IPMI) management, which may be useful. I hear what you say about the raid card, but how likely are they to change it from the LSI Mega Raid one in reality? But I am open to suggestions if you have any specific models from other manufacturers I should look at. I do need to be able to get the fast hardware support on it though that I can get from the likes of Dells 4 hours on site call out, so rolling my own isn't an option on this one really (unless it was so much cheaper I could have a hot standby or at least a cupboard of all the needed parts instantly available to me) Regards Ben ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] New hardware thoughts
Yes I am looking at either the 2950 or the 6850. I think the only think that the 6850 really offers me over the 2950 is more expandability in the spare processor, and additional memory I see (in first mail) you plan to use bsd 6.1 on dell2950. --- flame on Off topic for postgresql performance , but i'd like to warn you neither perc5i crap nor network adapter got proper support for bsd 6.1 stable ( dell2950 box ) dmesg -a | grep bce bce0: flags=8843UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST mtu 1500 inet6 fe80::213:72ff:fe61:2ef6%bce1 prefixlen 64 tentative scopeid 0x2 bce0: link state changed to UP bce0: /usr/src/sys/dev/bce/if_bce.c(5032): Watchdog timeout occurred, resetting! bce0: link state changed to DOWN bce0: link state changed to UP uname -a FreeBSD xxx 6.1-STABLE FreeBSD 6.1-STABLE #0: xxx:/usr/obj/usr/ src/sys/customkenelcompiled-30-Aug-2006 i386 Problem with (latest?) raid perc is that only one logical volume is supported. You may find some bits of info on freebsd mailing lists. At least for n/w card problem i see no solution until now. 3 month old history: due to buggy firmware on maxtor disks sold by dell 2 servers from our server farm having raid5 crashed and data on raid array was lost. We were lucky to have proper replication solution. If you decide to choose 2950, you have to use linux instead of bsd 6.1 . Also buy 2 boxes instead of 1 and set up slony replication for redundancy. go dell , go to hell. --- flame off good luck! Thanks Alvis, its good to hear this sort of problem before one commits to a purchase decision! I guess it makes the HP's Joshua mentioned in a reply more promising. Are there any other suppliers I should be looking at do you think. I'm keen on FreeBSD to be honest rather than Linux (I don't want to start any holy wars on this as its not the place) as then its the same as all my other servers, so support / sysadmin is easier if they are all the same. How about the Fujitsu Siemens Sun Clones? I have not really looked at them but have heard the odd good thing about them. Ben ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
