From bill@math.uchicago.edu Tue Jun 22 14:28:46 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 10D8FD1B220 for ; Tue, 22 Jun 2004 11:28:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75070-08 for ; Tue, 22 Jun 2004 14:28:45 +0000 (GMT) Received: from math.uchicago.edu (math.uchicago.edu [128.135.72.38]) by svr1.postgresql.org (Postfix) with ESMTP id 58929D1B199 for ; Tue, 22 Jun 2004 11:28:40 -0300 (ADT) Received: from billnotebook (wireless-197-223.uchicago.edu [128.135.197.223]) by math.uchicago.edu (8.12.10/8.12.10) with SMTP id i5MESffM016537 for ; Tue, 22 Jun 2004 09:28:45 -0500 Message-ID: <004001c45865$59cc9f20$dfc58780@billnotebook> From: "Bill" To: Subject: postgresql and openmosix migration Date: Tue, 22 Jun 2004 09:29:39 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003D_01C4583B.70D68C00" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200406/225 X-Sequence-Number: 7283 This is a multi-part message in MIME format. ------=_NextPart_000_003D_01C4583B.70D68C00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am trying to make a cluster out of any database, postgresql or mysql = or any other free database. I have looked at openmosix patched with the mi= gshm patch for shared memory support and it seems that neither work fully. = Postgresql in particular uses "shared memory but not the system semaphores= for locking it". Thus apparently it won't benefit from an openmosix clust= er. In addition mysql doesn't seem to migrate because it is multithreaded.= Any ideas of how I can cluster my database (around 800 GB in size so even= partial replication is not really practical)? If interested this is my source for openmosix and migshm information http:/= /howto.ipng.be/MigSHM-openMosix/x90.html Thanks. ------=_NextPart_000_003D_01C4583B.70D68C00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi, I am trying to make a cluster out of a= ny=20 database, postgresql or mysql or any other free database.  I have look= ed at=20 openmosix patched with the migshm patch for shared memory support and it se= ems=20 that neither work fully.  Postgresql in particular uses "shared memory= but=20 not the system semaphores for locking it".  Thus apparently it won't= =20 benefit from an openmosix cluster.  In addition mysql doesn't seem to= =20 migrate because it is multithreaded.  Any ideas of how I can cluster m= y=20 database (around 800 GB in size so even partial replication is not really= =20 practical)?
 
If interested this is my source for openmo= six and=20 migshm information http://howto.ipng.b= e/MigSHM-openMosix/x90.html
 
Thanks.
------=_NextPart_000_003D_01C4583B.70D68C00-- From josh@agliodbs.com Tue Jun 22 16:32:04 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 162ADD1B1C9 for ; Tue, 22 Jun 2004 13:32:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41934-09 for ; Tue, 22 Jun 2004 16:31:59 +0000 (GMT) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id F1998D1B1B6 for ; Tue, 22 Jun 2004 13:31:58 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 5450260; Tue, 22 Jun 2004 09:33:37 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Bill" , Subject: Re: postgresql and openmosix migration Date: Tue, 22 Jun 2004 09:31:23 -0700 User-Agent: KMail/1.4.3 References: <004001c45865$59cc9f20$dfc58780@billnotebook> In-Reply-To: <004001c45865$59cc9f20$dfc58780@billnotebook> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200406220931.23780.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/229 X-Sequence-Number: 7287 Bill, > Any ideas of how I can cluster my database (around 800 GB > in size so even partial replication is not really practical)? Um, raise $150,000 to pay for a clustering implementation? Various techniques of "shared memory clustering" have been tried with PostgreSQL, and none work. Neither does LinuxLabs "ClusGres", which is based on similar principles -- unfortunately. (at least, LL repeatedly postponed the demo they said they'd give me. I've yet to see anything working ...) Frankly, we're waiting for a well-funded corporation to jump in and decide they want PostgreSQL clustering. Database server clustering is a "big ticket item" requiring roughly 1,000 hours of programming and troubleshooting. As such, you're not likely to see it come out of the OSS community unaided. Oh, and FYI, MySQL's "clustering" doesn't work either. It requires your entire database to fit into available RAM .... -- Josh Berkus Aglio Database Solutions San Francisco From bill@math.uchicago.edu Tue Jun 22 17:30:30 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D70D6D1B18F for ; Tue, 22 Jun 2004 14:30:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75487-07 for ; Tue, 22 Jun 2004 17:30:27 +0000 (GMT) Received: from math.uchicago.edu (math.uchicago.edu [128.135.72.38]) by svr1.postgresql.org (Postfix) with ESMTP id 0C665D1B1E0 for ; Tue, 22 Jun 2004 14:30:21 -0300 (ADT) Received: from billnotebook (wireless-199-135.uchicago.edu [128.135.199.135]) by math.uchicago.edu (8.12.10/8.12.10) with SMTP id i5MHUHfM019307; Tue, 22 Jun 2004 12:30:20 -0500 Message-ID: <008201c4587e$b8856510$dfc58780@billnotebook> From: "Bill" To: "Josh Berkus" Cc: References: <004001c45865$59cc9f20$dfc58780@billnotebook> <200406220931.23780.josh@agliodbs.com> Subject: Re: postgresql and openmosix migration Date: Tue, 22 Jun 2004 12:31:15 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/230 X-Sequence-Number: 7288 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. ----- Original Message ----- From: "Josh Berkus" To: "Bill" ; Sent: Tuesday, June 22, 2004 11:31 AM Subject: Re: [PERFORM] postgresql and openmosix migration > Bill, > > > Any ideas of how I can cluster my database (around 800 GB > > in size so even partial replication is not really practical)? > > Um, raise $150,000 to pay for a clustering implementation? > > Various techniques of "shared memory clustering" have been tried with > PostgreSQL, and none work. Neither does LinuxLabs "ClusGres", which is > based on similar principles -- unfortunately. (at least, LL repeatedly > postponed the demo they said they'd give me. I've yet to see anything > working ...) > > Frankly, we're waiting for a well-funded corporation to jump in and decide > they want PostgreSQL clustering. Database server clustering is a "big > ticket item" requiring roughly 1,000 hours of programming and > troubleshooting. As such, you're not likely to see it come out of the OSS > community unaided. > > Oh, and FYI, MySQL's "clustering" doesn't work either. It requires your > entire database to fit into available RAM .... > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > From bruno@wolff.to Tue Jun 22 17:47:14 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 228A4D1B1B9 for ; Tue, 22 Jun 2004 14:47:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81790-08 for ; Tue, 22 Jun 2004 17:47:11 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 258B0D1B1C8 for ; Tue, 22 Jun 2004 14:47:09 -0300 (ADT) Received: (qmail 20271 invoked by uid 500); 22 Jun 2004 17:53:28 -0000 Date: Tue, 22 Jun 2004 12:53:28 -0500 From: Bruno Wolff III To: Bill Cc: Josh Berkus , pgsql-performance@postgresql.org Subject: Re: postgresql and openmosix migration Message-ID: <20040622175328.GA20086@wolff.to> Mail-Followup-To: Bill , Josh Berkus , pgsql-performance@postgresql.org References: <004001c45865$59cc9f20$dfc58780@billnotebook> <200406220931.23780.josh@agliodbs.com> <008201c4587e$b8856510$dfc58780@billnotebook> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <008201c4587e$b8856510$dfc58780@billnotebook> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/231 X-Sequence-Number: 7289 On Tue, Jun 22, 2004 at 12:31:15 -0500, 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? Queries using max (or min) can often be rewritten as queries using ORDER BY and LIMIT so that they can take advantage of indexes. Doing this might help with some of the problems you are seeing. If you commonly query on aggregated data it might be better to create derived tables of the aggregated data maintained by triggers, and query against them. If you do lots of selects relative to inserts and updates, this could be a big win. From jason.servetar@ccgenesis.com Tue Jun 22 17:57:27 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0F110D1B209 for ; Tue, 22 Jun 2004 14:57:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93966-03 for ; Tue, 22 Jun 2004 17:57:22 +0000 (GMT) Received: from corpmsx.gaiam.com (unknown [12.147.81.200]) by svr1.postgresql.org (Postfix) with ESMTP id 62010D1B173 for ; Tue, 22 Jun 2004 14:57:19 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: postgresql and openmosix migration Date: Tue, 22 Jun 2004 11:58:39 -0600 Message-ID: <8D36D5916571CB4489C2E4D0CAD6E89301BEE630@corpmsx.gaiam.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] postgresql and openmosix migration Thread-Index: AcRYfv5jy4Hw+zAxRROAx38bzoyvUAAADmMw From: To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200406/232 X-Sequence-Number: 7290 Sounds like an issue I have experienced in Oracle as well. If you can you might want consider breaking out your database into oltp (on line transaction processing) and data warehouse db. You run you any reports you can nightly into a set of warehouse tables and save your daytime cpus for incoming info and special real-time (hottest commodity of the day) reports that you have tuned the best you can. Anything you can calculate in advance that won't change over time, should be saved in the warehouse tables, so you don't waste cpus, re-working data in real time. Pre-running your reports won't speed them up but your users won't be waiting for a report to calculate while they are looking at the screen.=20 -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Bill Sent: Tuesday, June 22, 2004 11:31 AM To: Josh Berkus Cc: pgsql-performance@postgresql.org 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. ----- Original Message -----=20 From: "Josh Berkus" To: "Bill" ; Sent: Tuesday, June 22, 2004 11:31 AM Subject: Re: [PERFORM] postgresql and openmosix migration > Bill, > > > Any ideas of how I can cluster my database (around 800 GB > > in size so even partial replication is not really practical)? > > Um, raise $150,000 to pay for a clustering implementation? > > Various techniques of "shared memory clustering" have been tried with > PostgreSQL, and none work. Neither does LinuxLabs "ClusGres", which is > based on similar principles -- unfortunately. (at least, LL repeatedly > postponed the demo they said they'd give me. I've yet to see anything > working ...) > > Frankly, we're waiting for a well-funded corporation to jump in and decide > they want PostgreSQL clustering. Database server clustering is a "big > ticket item" requiring roughly 1,000 hours of programming and > troubleshooting. As such, you're not likely to see it come out of the OSS > community unaided. > > Oh, and FYI, MySQL's "clustering" doesn't work either. It requires your > entire database to fit into available RAM .... > > --=20 > Josh Berkus > Aglio Database Solutions > San Francisco > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings From rwelty@averillpark.net Tue Jun 22 18:10:07 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 81E1DD1B170 for ; Tue, 22 Jun 2004 15:10:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01552-01 for ; Tue, 22 Jun 2004 18:10:04 +0000 (GMT) Received: from krusty-motorsports.com (krusty-motorsports.com [192.94.170.8]) by svr1.postgresql.org (Postfix) with ESMTP id 81F46D1B169 for ; Tue, 22 Jun 2004 15:10:03 -0300 (ADT) Received: from [24.194.112.77] (helo=skipper.averillpark.net) by krusty-motorsports.com with asmtp (TLSv1:DES-CBC3-SHA:168) (Exim 4.22) id 1Bcpib-00086T-TJ for pgsql-performance@postgresql.org; Tue, 22 Jun 2004 18:10:01 +0000 Received: from [127.0.0.1] (helo=localhost) by skipper.averillpark.net with esmtp (Exim 4.22) id 1Bcpgo-0006dc-KV for pgsql-performance@postgresql.org; Tue, 22 Jun 2004 14:08:10 -0400 Date: Tue, 22 Jun 2004 14:08:10 -0400 (EDT) From: Richard Welty Subject: Re: postgresql and openmosix migration To: pgsql-performance@postgresql.org Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; CHARSET=US-ASCII Content-Disposition: INLINE References: <004001c45865$59cc9f20$dfc58780@billnotebook> <200406220931.23780.josh@agliodbs.com> <008201c4587e$b8856510$dfc58780@billnotebook> In-Reply-To: <008201c4587e$b8856510$dfc58780@billnotebook> Organization: Averill Park Networking X-Mailer: Mahogany 0.66.0 'Clio', compiled for Linux 2.4.18-14 i686 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/233 X-Sequence-Number: 7291 On Tue, 22 Jun 2004 12:31:15 -0500 Bill wrote: > I have installed indicies, but are there any statistics? vacuum analyze is your friend > 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? 1) have you gone to the effort to tune the values in postgresql.conf? 2) have you tried using explain to find out what the query planner is up to? > Is this better in mysql or > postgresql? if there is any complexity to the queries, postgresql will serve you better if you learn how to use it properly. richard -- Richard Welty rwelty@averillpark.net Averill Park Networking 518-573-7592 Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security From matt@followers.net Tue Jun 22 18:50:15 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F11B1D1B19C for ; Tue, 22 Jun 2004 15:50:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22101-01 for ; Tue, 22 Jun 2004 18:50:12 +0000 (GMT) Received: from ms-smtp-02.tampabay.rr.com (ms-smtp-02-smtplb.tampabay.rr.com [65.32.5.132]) by svr1.postgresql.org (Postfix) with ESMTP id 8CD00D1B191 for ; Tue, 22 Jun 2004 15:50:09 -0300 (ADT) Received: from MATTSPC (222-60.26-24.tampabay.rr.com [24.26.60.222]) by ms-smtp-02.tampabay.rr.com (8.12.10/8.12.7) with ESMTP id i5MInpnb027568; Tue, 22 Jun 2004 14:49:52 -0400 (EDT) Message-Id: <200406221849.i5MInpnb027568@ms-smtp-02.tampabay.rr.com> From: "Matthew Nuzum" To: "'Bill'" Cc: Subject: Re: postgresql and openmosix migration Date: Tue, 22 Jun 2004 14:49:48 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-reply-to: <008201c4587e$b8856510$dfc58780@billnotebook> X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Thread-Index: AcRYf3uKWWOFXR3HQby3OJJ6Deo82gABLDkg X-Virus-Scanned: Symantec AntiVirus Scan Engine X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.4 tagged_above=0.0 required=5.0 tests=BE_AMAZED, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/235 X-Sequence-Number: 7293 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 matt@followers.net | http://www.followers.net/portfolio/ > -----Original Message----- > From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] On Behalf Of Bill > Sent: Tuesday, June 22, 2004 1:31 PM > To: Josh Berkus > Cc: pgsql-performance@postgresql.org > 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. From mail@joeconway.com Wed Jun 23 01:25:31 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1264BD1B269 for ; Tue, 22 Jun 2004 22:25:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91443-08 for ; Wed, 23 Jun 2004 01:25:16 +0000 (GMT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 9E625D1B170 for ; Tue, 22 Jun 2004 22:25:12 -0300 (ADT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 1846306; Tue, 22 Jun 2004 18:17:29 -0700 Message-ID: <40D8DBFC.2090208@joeconway.com> Date: Tue, 22 Jun 2004 18:25:16 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040510 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bill Cc: Josh Berkus , pgsql-performance@postgresql.org Subject: Re: postgresql and openmosix migration References: <004001c45865$59cc9f20$dfc58780@billnotebook> <200406220931.23780.josh@agliodbs.com> <008201c4587e$b8856510$dfc58780@billnotebook> In-Reply-To: <008201c4587e$b8856510$dfc58780@billnotebook> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/239 X-Sequence-Number: 7297 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 From ahammond@ca.afilias.info Wed Jun 23 14:42:20 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D39A9D1B18F for ; Wed, 23 Jun 2004 11:42:20 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 62813-02 for ; Wed, 23 Jun 2004 14:42:16 +0000 (GMT) Received: from mail.libertyrms.com (unknown [207.219.45.62]) by svr1.postgresql.org (Postfix) with ESMTP id B20F2D1BAAF for ; Wed, 23 Jun 2004 11:42:08 -0300 (ADT) Received: from dba4.int.libertyrms.com ([10.1.3.13] ident=ahammond) by mail.libertyrms.com with esmtp (Exim 4.22) id 1Bd8x2-00062i-IR; Wed, 23 Jun 2004 10:42:12 -0400 Message-ID: <40D997F9.7000700@ca.afilias.info> Date: Wed, 23 Jun 2004 10:47:21 -0400 From: Andrew Hammond Organization: Afilias Canada Corp. User-Agent: Mozilla Thunderbird 0.6 (X11/20040512) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bill Cc: pgsql-performance@postgresql.org Subject: Re: postgresql and openmosix migration References: <004001c45865$59cc9f20$dfc58780@billnotebook> <200406220931.23780.josh@agliodbs.com> <008201c4587e$b8856510$dfc58780@billnotebook> In-Reply-To: <008201c4587e$b8856510$dfc58780@billnotebook> X-Enigmail-Version: 0.83.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/mixed; boundary="------------020507050102040004020805" X-SA-Exim-Mail-From: ahammond@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/240 X-Sequence-Number: 7298 This is a multi-part message in MIME format. --------------020507050102040004020805 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit -----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 Hammond 416-673-4138 ahammond@ca.afilias.info 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----- --------------020507050102040004020805 Content-Type: text/x-vcard; charset=utf8; name="ahammond.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="ahammond.vcf" 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:ahammond@ca.afilias.info 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 --------------020507050102040004020805-- From merlin.moncure@rcsonline.com Wed Jun 23 16:34:53 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DC9ADD1B170 for ; Wed, 23 Jun 2004 13:34:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34225-05 for ; Wed, 23 Jun 2004 16:34:45 +0000 (GMT) Received: from Herge.rcsinc.local (mail.rcsonline.com [205.217.85.91]) by svr1.postgresql.org (Postfix) with ESMTP id 0359BD1B175 for ; Wed, 23 Jun 2004 13:34:41 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: postgresql and openmosix migration X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0 Date: Wed, 23 Jun 2004 12:34:42 -0400 Message-ID: <6EE64EF3AB31D5448D0007DD34EEB34101AE73@Herge.rcsinc.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] postgresql and openmosix migration Thread-Index: AcRYf2kMuvf/7wxrQTWMFqa/O2pfkwAvfBJA From: "Merlin Moncure" To: "Bill" Cc: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/242 X-Sequence-Number: 7300 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 From josh@agliodbs.com Wed Jun 23 17:32:33 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 87252D1B241 for ; Wed, 23 Jun 2004 14:32:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43523-08 for ; Wed, 23 Jun 2004 17:32:29 +0000 (GMT) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 4B299D1B18F for ; Wed, 23 Jun 2004 14:32:27 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 5462042; Wed, 23 Jun 2004 10:34:08 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Bill" Subject: Re: postgresql and openmosix migration Date: Wed, 23 Jun 2004 10:31:45 -0700 User-Agent: KMail/1.4.3 Cc: References: <004001c45865$59cc9f20$dfc58780@billnotebook> <200406220931.23780.josh@agliodbs.com> <008201c4587e$b8856510$dfc58780@billnotebook> In-Reply-To: <008201c4587e$b8856510$dfc58780@billnotebook> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200406231031.45401.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/243 X-Sequence-Number: 7301 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 From pg@rbt.ca Wed Jun 23 17:54:25 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5351FD1B199 for ; Wed, 23 Jun 2004 14:54:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56480-02 for ; Wed, 23 Jun 2004 17:54:16 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 678BFD1B33D for ; Wed, 23 Jun 2004 14:54:14 -0300 (ADT) Received: from [134.22.68.4] (dyn-68-4.tor.dsl.tht.net [134.22.68.4]) by tht.net (Postfix) with ESMTP id 7C81076C0B; Wed, 23 Jun 2004 13:52:43 -0400 (EDT) Subject: Re: postgresql and openmosix migration From: Rod Taylor To: Josh Berkus Cc: Bill , Postgresql Performance In-Reply-To: <200406231031.45401.josh@agliodbs.com> References: <004001c45865$59cc9f20$dfc58780@billnotebook> <200406220931.23780.josh@agliodbs.com> <008201c4587e$b8856510$dfc58780@billnotebook> <200406231031.45401.josh@agliodbs.com> Content-Type: text/plain Message-Id: <1088013159.95078.21.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Wed, 23 Jun 2004 13:52:39 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/244 X-Sequence-Number: 7302 > 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. From rwelty@averillpark.net Wed Jun 23 18:22:08 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2FDEED1B1A9 for ; Wed, 23 Jun 2004 15:22:08 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67820-04 for ; Wed, 23 Jun 2004 18:22:07 +0000 (GMT) Received: from krusty-motorsports.com (krusty-motorsports.com [192.94.170.8]) by svr1.postgresql.org (Postfix) with ESMTP id 9DC4CD1B170 for ; Wed, 23 Jun 2004 15:22:05 -0300 (ADT) Received: from [24.194.112.77] (helo=skipper.averillpark.net) by krusty-motorsports.com with asmtp (TLSv1:DES-CBC3-SHA:168) (Exim 4.22) id 1BdCNp-0004rZ-Kq for pgsql-performance@postgresql.org; Wed, 23 Jun 2004 18:22:05 +0000 Received: from [127.0.0.1] (helo=localhost) by skipper.averillpark.net with esmtp (Exim 4.22) id 1BdCMC-0007p8-9M for pgsql-performance@postgresql.org; Wed, 23 Jun 2004 14:20:24 -0400 Date: Wed, 23 Jun 2004 14:20:24 -0400 (EDT) From: Richard Welty Subject: Re: postgresql and openmosix migration To: Postgresql Performance Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; CHARSET=US-ASCII Content-Disposition: INLINE References: <004001c45865$59cc9f20$dfc58780@billnotebook><200406220931.23780.josh@agliodbs.com><008201c4587e$b8856510$dfc58780@billnotebook><200406231031.45401.josh@agliodbs.com> <1088013159.95078.21.camel@jester> In-Reply-To: <1088013159.95078.21.camel@jester> Organization: Averill Park Networking X-Mailer: Mahogany 0.66.0 'Clio', compiled for Linux 2.4.18-14 i686 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/245 X-Sequence-Number: 7303 On Wed, 23 Jun 2004 13:52:39 -0400 Rod Taylor 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 rwelty@averillpark.net Averill Park Networking 518-573-7592 Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security