Re: [PERFORM] [pgsql-jobs] Looking for database hosting
On 8/19/07, Luke Lonergan <[EMAIL PROTECTED]> wrote: > > Andrew, > > I'd say that commodity systems are the fastest with postgres - many have > seen big slowdowns with high end servers. 'Several orders of magnitude' is > not possible by just changing the HW, > Going from one or two SATA disks to a SAN farm ought to achieve orders of magnitude in improvement. And cost. Going from 2GB of memory up to 16 or 32GB can make significant changes as well. However I agree with you that intelligence at the application layer such that you can take advantage of a parallel approach is a superior solution both in terms of overall effectiveness and cost effectiveness. you've got a SW problem to solve first. We have done 100+ times faster than > both Postgres and popular (even gridded) commercial DBMS using an > intrinsically parallel SW approach. > That is both cool and unsurprising at the same time. One of the major challenges I've seen in practice is that small companies don't generally start off with a db design that's capable of a parallel approach. With success and growth, there comes a point where a massive re-design is needed. Companies that recognize this, make the investment and take the risk are rare. If the objective is OLAP / DSS there's no substitute for a parallel DB that > does query and load / transform using all the CPUs and IO channels > simultaneously. This role is best met from a value standpoint by clustering > commodity systems. > > For OLTP, we need better SMP and DML algorithmic optimizations for > concurrency, at which point big SMP machines work. Right now you can buy a > 32 CPU commodity (opteron) machine from SUN (X4600) for about $60K loaded. > WRT hosting, we've done a bit of it on GPDB systems, but we're not making it > a focus area. Instead, we do subscription pricing by the amount of data > used and recommend / help get systems set up. > > - Luke > > Msg is shrt cuz m on ma treo > > > -Original Message- > From: Andrew Hammond [mailto:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > ] > Sent: Sunday, August 19, 2007 03:49 PM Eastern Standard Time > To: Niklas Saers > Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org > Subject:Re: [PERFORM] [pgsql-jobs] Looking for database hosting > > Nik, you may be underestimating just how much performance can be obtained > from a single database server. For example, an IBM p595 server connected > to > an array of ds8300 storage devices could reasonably be expected to provide > several orders of magnitude more performance when compared to commodity > hardware. In commodity space (albeit, just barely), a 16 core opteron > running (the admittedly yet-to-be-released) FreeBSD 7, and a suitably > provisioned SAN should also enormously outperform a beige-box solution, > and > at a fraction of the cost. If it's performance you care about then the > pgsql-performance list (which I have cc'd) is the place to talk about it. > > I realize this doesn't address your desire to get out of database server > administration. I am not aware of any company which provides database > hosting, further I'm not entirely convinced that's a viable business > solution. The technical issues (security, latency and reliability are the > ones that immediately come to mind) associated with a hosted database > server > solution suggest to me that this would not be economically viable. The > business issues around out-sourcing a critical, if not central component > of > your architecture seem, at least to me, to be insurmountable. > > Andrew > > > On 8/19/07, Niklas Saers <[EMAIL PROTECTED]> wrote: > > > > Hi, > > the company I'm doing work for is expecting a 20 times increase in > > data and seeks a 10 times increase in performance. Having pushed our > > database server to the limit daily for the past few months we have > > decided we'd prefer to be database users rather than database server > > admins. :-) > > > > Are you or can you recommend a database hosting company that is good > > for clients that require more power than what a single database > > server can offer? > > > > Cheers > > > > Nik > > > > ---(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] [pgsql-jobs] Looking for database hosting
Nik, you may be underestimating just how much performance can be obtained from a single database server. For example, an IBM p595 server connected to an array of ds8300 storage devices could reasonably be expected to provide several orders of magnitude more performance when compared to commodity hardware. In commodity space (albeit, just barely), a 16 core opteron running (the admittedly yet-to-be-released) FreeBSD 7, and a suitably provisioned SAN should also enormously outperform a beige-box solution, and at a fraction of the cost. If it's performance you care about then the pgsql-performance list (which I have cc'd) is the place to talk about it. I realize this doesn't address your desire to get out of database server administration. I am not aware of any company which provides database hosting, further I'm not entirely convinced that's a viable business solution. The technical issues (security, latency and reliability are the ones that immediately come to mind) associated with a hosted database server solution suggest to me that this would not be economically viable. The business issues around out-sourcing a critical, if not central component of your architecture seem, at least to me, to be insurmountable. Andrew On 8/19/07, Niklas Saers <[EMAIL PROTECTED]> wrote: > > Hi, > the company I'm doing work for is expecting a 20 times increase in > data and seeks a 10 times increase in performance. Having pushed our > database server to the limit daily for the past few months we have > decided we'd prefer to be database users rather than database server > admins. :-) > > Are you or can you recommend a database hosting company that is good > for clients that require more power than what a single database > server can offer? > > Cheers > > Nik > > ---(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 >
[PERFORM] FiberChannel cards for FreeBSD on AMD64
Does anyone here have positive experiences to relate running fiberchannel cards on FreeBSD on AMD64? The last time I tried it was with FreeBSD 4 about 2 years ago and none of the cards I tried could cross the 32bit memory barrier (since they were all actually 32bit cards despite plugging into a 64bit PCI bus). Andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Partitioned tables in queries
> 2. I found that using the now() function - and arbitrary interval will > produce a different execution plan that using a specific date. For example: > assuming the current time is 16:00: > a) where start_date > now() - interval '4 hours' scans all child tables. > b) where start_date > '2006-07-21 12:00:00' only scans the child > table with today's data. > > So am I to assume that the value in the query must be a constant, and > cannot be a result of a built-in function in order for > constraint_exclusion to work correctly? Have you tried WHERE start_date > (SELECT now() - interval '4 hours')? Certainly using the constant will allow CBE to work. I think that a subquery might too. ---(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] Large Database Performance suggestions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joshua Marsh wrote: | Thanks for all of your help so far. Here is some of the information | you guys were asking for: | | Test System: | 2x AMD Opteron 244 (1.8Ghz) | 8GB RAM | 7x 72GB SCSI HDD (Raid 5) You probably want to look at investing in a SAN. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFBmMnSgfzn5SevSpoRAlp2AKCVXQkZLR7TuGId/OLveHPqpzC4zwCffNFC 7zjXzJ6Ukg4TeO1ecWj/nFQ= =N5vp -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] SAN performance
I'm about to do a whole bunch of testing here on various DA element sizes, and datablock sizes and how the affect pg performance. It doesn't appear possible to get > 4kb filesystem blocks under linux due to the limitation of the pagesize. We're running AMD64 for these tests, but the DA configuration should be pretty much identical for IA32. My best guess right now is that recompiling pg with a 4kb datablock size, and using 4kb filesystem blocks with an 8 sector (4kb) element size is probably the way to go for an active database. Contact me off-list if you want a copy of the EMC CLARiiON "Best Practices for Fiber Channel Storage" white paper. Haven't read it since I only got my copy this morning, but... looks promising. Drew Anjan Dave wrote: I believe 1/0 or 1+0 is aka RAID-10. CX300 doesn't support 0+1. So far i am aware of two things, the cache page size is 8KB (can be increased or decreased), and the stripe element size of 128 sectors default. Thanks, Anjan -Original Message- From: Mr Pink [mailto:[EMAIL PROTECTED] Sent: Thu 9/23/2004 11:39 AM To: Anjan Dave; [EMAIL PROTECTED] Cc: Subject: Re: [PERFORM] SAN performance Hi, I expect you mean RAID 1/0 or 1+0 since the CX300 didn't support RAID 10 last time I looked. Whether you are using a SAN or not, you should consider putting the WAL files (pg_xlog folder) on seperate diskes from the DB. Since the log files are mostly written to, not read from you could just use RAID 1. It's a pity pg doesn't have a way to use a cluster of servers to get the most out of your expensive SAN. I read a comment earlier about setting block sizes to 8k to math pg's block size. Seems to make sense, you should check it out. Have fun, Mr Pink --- Anjan Dave <[EMAIL PROTECTED]> wrote: > Hello, > > > > I'll be moving a DB from internal RAID-10 SCSI storage to an EMC CX300 > FC RAID-10 LUN, bound to the host. I've setup a test host machine and a > test LUN. The /var/lib/pgsql/data folder is sym-linked to a partition on > the LUN. > > > > Other than the shared_buffers, effective cache size, and sort memory, I > am not sure if I need to change any other parameters in the > postgresql.conf file for getting maximum performance from the EMC box. > > > > Is there a general guideline for setting up postgres database and the > tunable parameters on a SAN, especially for EMC? > > > > Appreciate any help, > > > > Thanks, > Anjan > > __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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] NAS, SAN or any alternate solution ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rod Taylor wrote: | I've used both a NetApp and Hitachi based SANs with PostgreSQL. Both | work as well as expected, but do require some tweeking as they normally | are not optimized for the datablock size that PostgreSQL likes to deal | with (8k by default) -- this can make as much as a 50% difference in | performance levels. I'm looking for documentation about the datablock size you mentioned above. My goal is to tune the disk / filesystem on our prototype system. It's an EMC disk array, so sectors on disk are 512 bytes of usable space. We've decided to go with RAID 10 since the goal is to maximize performance. Currently the raid element size is set at 16 sectors which is 8192 bytes of payload. I've got a sysadmin working on getting XFS going with 8192 byte blocks. My next task will be to calculate the amount of space used by XFS for headers etc. to find out how much of those 8192 bytes can be used for the postgres payload. Then configure postgres to use datablocks that size. So I'm looking for details on how to manipulate the size of the datablock. I'm also not entirely sure how to make the datablocks line up with the filesystem blocks. Any suggestions on this would be greatly appreciated. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFBUeHmgfzn5SevSpoRAu2sAJ4nHHup5lhp4+RcgBPGoJpUFoE1SQCgyvW1 ixyAvqb7ZkB+IIdGb36mpxI= =uDLW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] postgresql and openmosix migration
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bill wrote: | Ok, so maybe someone on this group will have a better idea. We have a | database of financial information, and this has literally millions of | entries. I have installed indicies, but for the rather computationally | demanding processes we like to use, like a select query to find the | commodity with the highest monthly or annual returns, the computer generally | runs unacceptably slow. So, other than clustring, how could I achieve a | speed increase in these complex queries? Is this better in mysql or | postgresql? Postgres generally beats MySQL on complex queries. The easiest solution to speed issues is to throw hardware at it. Generally, you're first bound by disk, RAM then CPU. 1) Move your data over to an array of smallish 15kRPM disks. The more spindles the better. 2) Use a 64 bit platform and take advantage of >4 GB memory. There are dozens of options for the disk array. For the processing platform, I'd recommend looking at Opteron. I've heard only good things and their price is much more reasonable than the other options. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA2Zf3gfzn5SevSpoRAr0HAJ0S/uVjuqYEuhMgdSAI3rfHK0ga1wCgwpHl g+yuBYpAt58vnJWtX+wii1s= =2fGN -END PGP SIGNATURE- begin:vcard fn:Andrew Hammond n:Hammond;Andrew org:Afilias Canada Corp.;Operations adr:Suite 204;;4141 Yonge Street;North York;Ontario;M2P 2A8;Canada email;internet:[EMAIL PROTECTED] title:Database Administrator tel;work:416-673-4138 tel;fax:416-646-1541 tel;home:416-214-1109 tel;cell:647-285-7106 note;quoted-printable:I sign all emails with my GPG key. Fingerprint is:=0D=0A= CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A x-mozilla-html:TRUE url:http://www.afilias.info/ version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Postgres over Linux NBD or NFS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 |> What exactly are you trying to gain, avoid, or do? Gain: seperate database storage from processing. This lets me move clusters from one server to another easily. Just stop the postgres instance on server A and unmount it's filesystem. Then mount it on server B and start postgres instance on server B. It gives me some fail-over capability as well as scalability and a lot of flexibility in balancing load over multiple servers. Avoid: paying for brutally expensive FC gear. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA2Djagfzn5SevSpoRAj+bAKDFFgrhX+G1gkZRrydow3j/j35VaACbBN3Y C/0nWmqcwo/UlqvYpng06Ks= =k2vg -END PGP SIGNATURE- begin:vcard fn:Andrew Hammond n:Hammond;Andrew org:Afilias Canada Corp.;Operations adr:Suite 204;;4141 Yonge Street;North York;Ontario;M2P 2A8;Canada email;internet:[EMAIL PROTECTED] title:Database Administrator tel;work:416-673-4138 tel;fax:416-646-1541 tel;home:416-214-1109 tel;cell:647-285-7106 note;quoted-printable:I sign all emails with my GPG key. Fingerprint is:=0D=0A= CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A x-mozilla-html:TRUE url:http://www.afilias.info/ version:2.1 end:vcard ---(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] Pl/Pgsql Functions running simultaneously
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Marcus Whitney wrote: | Am I on the wrong list to ask this question, or does this list usually have | low activity? Just asking because I am new and I need to know where to ask | this question. Thanks. Your .sig may hold the reason why people are not responding. You seem like an intelligent guy and you asked an interesting question, but... | cold feet presents emma | | email marketing for discriminating | ^^^ | organizations everywhere | | visit www.myemma.com - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA1zstgfzn5SevSpoRApgdAKCtjL4qMwNQ9mZN57RHmHJi5Ana0wCggXhb 7HYFtE3S9zQ2hSGR9vYdXYQ= =Kfqd -END PGP SIGNATURE- begin:vcard fn:Andrew Hammond n:Hammond;Andrew org:Afilias Canada Corp.;Operations adr:Suite 204;;4141 Yonge Street;North York;Ontario;M2P 2A8;Canada email;internet:[EMAIL PROTECTED] title:Database Administrator tel;work:416-673-4138 tel;fax:416-646-1541 tel;home:416-214-1109 tel;cell:647-285-7106 note;quoted-printable:I sign all emails with my GPG key. Fingerprint is:=0D=0A= CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A x-mozilla-html:TRUE url:http://www.afilias.info/ version:2.1 end:vcard ---(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
[PERFORM] Postgres over Linux NBD or NFS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We're looking for an alternative to fiber-channel disk arrays for mass storage. One of the ideas that we're exploring would involve having the cluster on an NFS mounted filesystem. Another technology we're looking at is the Linux NBD (Network Block Device). Has anyone had any experience with running postgres over either of these technologies? What issues do we need to know about / pay attention to? - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA1yK6gfzn5SevSpoRAtYAAKCrghfAKV5kVuiTd/2TOwEbr4Q7hACgr3rT mEvFi8AOHX9I43T45fH1e0U= =1Cs9 -END PGP SIGNATURE- begin:vcard fn:Andrew Hammond n:Hammond;Andrew org:Afilias Canada Corp.;Operations adr:Suite 204;;4141 Yonge Street;North York;Ontario;M2P 2A8;Canada email;internet:[EMAIL PROTECTED] title:Database Administrator tel;work:416-673-4138 tel;fax:416-646-1541 tel;home:416-214-1109 tel;cell:647-285-7106 note;quoted-printable:I sign all emails with my GPG key. Fingerprint is:=0D=0A= CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A x-mozilla-html:TRUE url:http://www.afilias.info/ version:2.1 end:vcard ---(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 opinions wanted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm working through the aquisition process for a quad Opteron box right now. I'll be benchmarking it against a quad processor p630 as well as a quad Xeon after we get it and posting results here. But that's about a month or two from now. I expect that the results will be strongly in favour of the Opetron, especially the price / performance since the Opteron box is being quoted at about half the price of the p630 systems. One thing you may wish to consider is going with lots of 10kRPM SATA disks instead of 15kRPM SCSI disks. Two companies that I'm aware of offer quad Opteron solutions with SATA raid: http://www.quatopteron.com/ http://alltec.com/home.php Andrew Hammond DBA - Afilias -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAvjYKgfzn5SevSpoRAvq0AJkBDXOKL52HXg43mQ6rXe/i9RzFkQCfYQn8 HpHP2U0jvjfYIvihNLFLbzA= =LyqB -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
