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] Traduc Party
> "Matthieu" == Matthieu Compin <[EMAIL PROTECTED]> writes: [...] >> mais je réitère ma proposition de m'occuper de la partie qui a >> posé problème cette fois-ci, c'est à dire la prise de contact >> avec les différentes personnes qui pourraient être intéressées. Matthieu> La balle est dans ton camps. Prend contact avec Les Matthieu> Projets Importants, fixe moi une date et je te trouve des Matthieu> salles et du réseau. Matthieu> On a donc la possibilité de faire une belle grossse Matthieu> manifestation maitenant et tu peux pas dire non ;) Ouf! Je suis soulagé de la tournure que ça prends. -- Laurent Martellivice-président de Parinux http://www.bearteam.org/~laurent/ http://www.parinux.org/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go 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? 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] Traduc Party
How in hell did could this mail be sent to pgsql-performance ??? I must have inadvertently hit a fatal and obscure keystroke in Emacs/Gnus. Sorry for the noise. -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] after using pg_resetxlog, db lost
Tom I see you from past emails that you reference using -i -f with pg_filedump. I have tried this, but do not know what I am looking at. What would be the the transaction id? What parameter am I supposed to pass to find it? *** * PostgreSQL File/Block Formatted Dump Utility - Version 3.0 * * File: /npmu_base/data/base/17347/1259 * Options used: -i -f * * Dump created on: Thu Jun 24 02:44:59 2004 *** Block0 - Block Offset: 0x Offsets: Lower 232 (0x00e8) Block: Size 8192 Version1Upper 268 (0x010c) LSN: logid 0 recoff 0x00632c08 Special 8192 (0x2000) Items: 53 Free Space: 36 Length (including item array): 236 : 082c6300 0b00 e8000c01 .,c. 0010: 00200120 c4908801 00908801 3c8f8801 . . <... 0020: 788e8801 b48d8801 f08c8801 2c8c8801 x...,... 0030: 689f3001 688b8801 a48a8801 e0898801 h.0.h... 0040: 1c898801 5801 94878801 d0868801 X... 0050: 3c862801 a8852801 e4848801 50842801 <.(...(.P.(. 0060: bc832801 f8828801 64822801 d0812801 ..(.d.(...(. 0070: 0c818801 6c11 d810 4410 l...D... 0080: b00f 1c0f d49e2801 409e2801 ..([EMAIL PROTECTED](. 0090: ac9d2801 189d2801 849c2801 f09b2801 ..(...(...(...(. 00a0: 5c9b2801 c89a2801 349a2801 a0992801 \.(...(.4.(...(. 00b0: 0c992801 78982801 e4972801 50972801 ..(.x.(...(.P.(. 00c0: bc962801 28962801 94952801 00952801 ..(.(.(...(...(. 00d0: 6c942801 d8932801 44932801 b0922801 l.(...(.D.(...(. 00e0: 1c922801 88912801 ..(...(. -- Item 1 -- Length: 196 Offset: 4292 (0x10c4) Flags: USED XID: min (2) CMIN|XMAX: 211 CMAX|XVAC: 469 Block Id: 0 linp Index: 1 Attributes: 24 Size: 28 infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) 10c4: 0200 d300 d501 10d4: 01001800 12291c00 cc42 7461626c .)...B..tabl 10e4: 655f636f 6e737472 61696e74 7300 e_constraints... 10f4: 1104: 1114: 5142 QB.. 1124: cd42 0100 cc42 .B...B.. 1134: 1144: 7600 0900 ..v. 1154: 0100 3000 0100 0... 1164: 0904 0200 0100 1174: 0100 7f803f40 0100 [EMAIL PROTECTED] 1184: 0200 Item 2 -- Length: 196 Offset: 4096 (0x1000) Flags: USED XID: min (2) CMIN|XMAX: 215 CMAX|XVAC: 469 Block Id: 0 linp Index: 2 Attributes: 24 Size: 28 infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) 1000: 0200 d700 d501 1010: 02001800 12291c00 d042 7461626c .)...B..tabl 1020: 655f7072 6976696c 65676573 e_privileges 1030: 1040: 1050: 5142 QB.. 1060: d142 0100 d042 .B...B.. 1070: 1080: 7600 0800 ..v. 1090: 0100 3000 0100 0... 10a0: 0904 0200 0100 10b0: 0100 7f803f40 0100 [EMAIL PROTECTED] 10c0: 0200 Item 3 -- Length: 196 Offset: 3900 (0x0f3c) Flags: USED XID: min (2) CMIN|XMAX: 219 CMAX|XVAC: 469 Block Id: 0 linp Index: 3 Attributes: 24 Size: 28 Dan. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 22, 2004 3:36 PM To: Shea,Dan [CIS] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] after using pg_resetxlog, db lost "Shea,Dan [CIS]" <[EMAIL PROTECTED]> writes: > The pg_resetxlog was run as root. It caused ownership problems of > pg_control and xlog files. > Now we have no access to the data now through psql. The data is still > there under /var/lib/pgsql/data/base/17347 (PWFPM_DEV DB name). But > there is no reference to 36 of our tables in pg_class. Also the 18 > other tables that are reported in this database have no data in them. > Is there anyway to have the database resync or make it aware of the data > under /var/lib/pgsql/data/base/17347? > How can this problem be resolved? What this sounds like is that you reset the transaction counter along with the xlog, so that those tables appear to ha
Re: [PERFORM] Traduc Party
On 23/06/2004 19:47 Laurent Martelli wrote: How in hell did could this mail be sent to pgsql-performance ??? I must have inadvertently hit a fatal and obscure keystroke in Emacs/Gnus. That sort of implies that there are Emacs keystrokes which aren't obsure. I've been using it dayly for 2 years now and have yet to discover any key sequence which makes any sense. But then I don't do drugs so my perseption is probably at odds with the origators of Emacs ;) -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] after using pg_resetxlog, db lost
"Shea,Dan [CIS]" <[EMAIL PROTECTED]> writes: > Tom I see you from past emails that you reference using -i -f with > pg_filedump. I have tried this, but do not know what I am looking at. What you want to look at is valid XMIN and XMAX values. In this example: > Item 1 -- Length: 196 Offset: 4292 (0x10c4) Flags: USED > XID: min (2) CMIN|XMAX: 211 CMAX|XVAC: 469 > Block Id: 0 linp Index: 1 Attributes: 24 Size: 28 > infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) the infomask shows XMIN_COMMITTED, so xmin (here 2) is valid, but it also shows XMAX_INVALID, so the putative XMAX (211) should be ignored. In general the xmin field should be valid, but xmax shares storage with cmin and so you have to look at the infomask bits to know whether to believe that the cmin/xmax field represents a transaction ID. The cmax/xvac field could also hold a transaction ID. If I had only the above data to go on, I'd guess that the current transaction counter is at least 469. Under normal circumstances, command counter values (cmin or cmax) are unlikely to exceed a few hundred, while the transaction IDs you are looking for are likely to be much larger. So you could get away with just computing the max of *all* the numbers you see in xmin, cmin/xmax, or cmax/cvac, and then using something a million or so bigger for safety factor. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
