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] postgresql and openmosix migration
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? This is a very broad question. Optimizing your SQL to run fast as on any other database is something of an art form. This is a very broad topic that could fill a book. For example, a common performance killer is not having enough sort memory for large ordered result sets. A critical skill is being able to figure out if the planner is optimizing your queries badly. Knowing this is a mixture of observation and intuition that comes with experience. The absolute best case performance of a query is roughly defined by the data that is looked at to generate the result set and the size of the result set itself when the query is pulling data from the cache. The cache problem is compromisable by throwing more money at the problem but a poorly planned query will run slowly on any hardware. I would suggest isolating particular problems and posting them to the list. (explain analyze works wonders). Merlin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] postgresql and openmosix migration
Bill, 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? Well, you can do this 2 ways: 1) you can pick out one query at a time, and send us complete information on it, like Matt's really nice e-mail describes. People on this list will help you troubleshoot it. It will take a lot of time, but no money. 2) You can hire a PG database expert.This will be much faster, but cost you a lot of money. Is this better in mysql or postgresql? Complex queries? Large databases? That's us. MySQL is obtimized for simple queries on small databases. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] postgresql and openmosix migration
2) You can hire a PG database expert.This will be much faster, but cost you a lot of money. I wouldn't exactly say a lot of money. Lots of consulters out there are willing to put in a weeks worth of effort, on site, for significantly less than a support contract with most commercial DB organizations (including MySQL) -- and often give better results since they're on-site rather than over phone or via email. But yes, doing it via this mailing list is probably the cheapest option. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] postgresql and openmosix migration
On Wed, 23 Jun 2004 13:52:39 -0400 Rod Taylor [EMAIL PROTECTED] wrote: But yes, doing it via this mailing list is probably the cheapest option. yes, he just needs to decide how big a hurry he's in. also, if he does decide to hire a consultant, i suggest he pop over to pgsql-jobs and ask there. richard -- Richard Welty [EMAIL PROTECTED] Averill Park Networking 518-573-7592 Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] postgresql and openmosix migration
Hi Bill, I am more often in the needing help category than the giving help when it comes to advise about using postgresql. I have found it to be an extremely powerful tool and by far the best performance/price for my work. I think you will get some excellent answers and help to your performance questions if you send the list details about specific queries that are running too slow. If you are willing to throw more/bigger hardware at the problem, let people know that when you ask and they will tell you if your bottleneck can be alleviated through more ram, disks, cpu or whatever. Having been watching this list for some time now, I suspect most of the performance problems can be improved using non-intuitive query or configuration modifications (for example, replacing min()/max() as suggested by Mr. Wolf). The heavy hitters on the list will usually ask for an explain analyze of your query. If your query is select * from foo, then change it to EXPLAIN ANALYZE select * from foo and post the output. It will look something like this: QUERY PLAN --- Seq Scan on foo (cost=0.00..1.04 rows=4 width=44) (actual time=8.46..8.47 rows=4 loops=1) Total runtime: 19.63 msec (2 rows) I'm sure your data is confidential; mine is too. The good news is that none of your data is included in the query. Only technical details about what the database is doing. If your problem might involve the application that works with the data, give some details about that. For example, if you're using a Java application, let people know what driver version you use, what jvm and other related info. There are lurkers on this list using just about every programming language imaginable on more platforms than you can shake a stick at (I don't care how good you are at shaking sticks, either). The more details you give the better help you're going to get and you'd be amazed at the results I've seen people get with a judicious amount of tweaking. The other day someone had a query that took hours decrease to less than 10 minutes by using some techniques prescribed by members on the list. Bringing 30 - 60 second queries down to 2-3 seconds is commonplace. You seem to be ready to throw money at the problem by investing in new hardware but I would suggest digging into the performance problems first. Too many times we've seen people on the list say, I've just spent $x0,000 on a new xyz and I'm still having problems with this query. Often times the true solution is rewriting queries, tweaking config parameters, adding RAM and upgrading disks (in that order I believe). As I found out even today on the SQL list, it's best to ask questions in this form: I want to do this... I've been trying this... I'm getting this... which is problematic because... The more clearly you state the abstract goal the more creative answers you'll get with people often suggesting things you'd never considered. I hope this helps and I hope that you achieve your goals of a well performing application. Matthew Nuzum | Makers of Elite Content Management System www.followers.net | View samples of Elite CMS in action [EMAIL PROTECTED] | http://www.followers.net/portfolio/ -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-performance- [EMAIL PROTECTED] On Behalf Of Bill Sent: Tuesday, June 22, 2004 1:31 PM To: Josh Berkus Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] postgresql and openmosix migration 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? Thanks. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] postgresql and openmosix migration
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? If the bottleneck is really computational, not I/O, you might try PL/R in conjunction with the rpvm R package. rpvm allows R to make use of pvm to split its load among a cluster. See: R: http://www.r-project.org/ PL/R: http://www.joeconway.com/plr/ rpvm: http://cran.r-project.org/src/contrib/Descriptions/rpvm.html http://cran.r-project.org/doc/packages/rpvm.pdf I haven't had a chance to play with this myself yet, but I hope to relatively soon. HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
