Re: [ADMIN] download problems
Thomas Burns <[EMAIL PROTECTED]> writes: > I'm having a really stupid problem -- it seems to be impossible to > download postgre. All of the US mirrors timeout. Is this normal? Four out of the listed seven responded when I tried 'em just now ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] download problems
ftp3.us.postgresql.org is working for me. On Tue, 11 May 2004, Thomas Burns wrote: > Hi, > > I'm having a really stupid problem -- it seems to be impossible to > download postgre. All of the US mirrors timeout. Is this normal? > There are no apparent problems with my connection. > > Thomas E. Burns > Founder, jGuru and knowspam.net > http://www.knowspam.net -- Enjoy Email with knowspam.net > http://www.jguru.com -- FAQs, Forums and News for Java developers > [EMAIL PROTECTED] > 415.255.7285 > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] download problems
Thomas Burns <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm having a really stupid problem -- it seems to be impossible to > download postgre. Postgres, PostgreSQL, psql, pgsql, but not "postgre," please :). >All of the US mirrors timeout. Is this normal? > There are no apparent problems with my connection. Odd. Pgsql's download servers are *usually* very fast. Testing, I was just getting full bandwidth (which is only 16.5k/s on my l'il ol' 144k IDSL ckt.) from the fftp3.us server. Jim ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] a warnig yes, but to worry or not
WARNING: Cache reference leak: cache pg_statistic (31), tuple 0 has count -1 redhat 9.0 -> Linux technet1.t-n-i.fst 2.4.20-27.9smp #1 SMP Thu Dec 11 13:15:04 EST 2003 i686 i686 i386 GNU/Linux pstgres rpm postgresql-server-7.3.4-3.rhl9 looks as though the table vacuum and my database wide vacuum were happening at same time. So do I need to worry? Do abeter jop of making sure that the table vacuum does'nt happen at the wrong time? Thanks in advance Arno ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] could not bind socket for statistics collector
Hi - I'm trying to get postgres 7.4.2 going on RedHat ES The build went fine but I get the following when trying to start postgres with this command: [EMAIL PROTECTED] bin]$ ./pg_ctl -D /usr/local/pgsql/data/ -o "-i" start LOG: could not create IPv6 socket: Address family not supported by protocol postmaster successfully started [EMAIL PROTECTED] bin]$ LOG: could not bind socket for statistics collector: C annot assign requested address LOG: disabling statistics collector for lack of working socket LOG: database system was shut down at 2004-05-11 10:12:17 EDT LOG: checkpoint record is at 0/9B12D8 LOG: redo record is at 0/9B12D8; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 536; next OID: 17142 LOG: database system is ready Thanks in advance for help & insight into what's going on here. Cheers Graham Graham Clarke [EMAIL PROTECTED] www.53Tech.com 603.643.9955 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] OID Overflow for large objects
Tom Lane wrote: (http://www3.sk.postgresql.org/docs/faqs/FAQ.html): OIDs are stored as 4-byte integers, and will overflow at 4 billion. No one has reported this ever happening, and we plan to have the limit removed before anyone does. That comment in the FAQ seems quite out-of-date. What will actually happen is that the OID generator will wrap around. This will not bother Postgres particularly, but you may start having occasional transaction failures due to duplicate OIDs --- for example, I believe lo_create will fail if the OID it selects already exists in pg_largeobject. Pardon my incredulity, but doesn't that seem like a bug? Or at least a limitation? Does this mean that the effective useful lifetime of pg_largeobject is only as long as it takes to wrap around, after which you *must* dump and reload to prevent problems like this? (I realize this is pretty much the same issue as having a sequence number on a table, but if I'm interpreting all this correctly, the OID wrap-around is going to occur a lot sooner than my table sequence number wrap-around.) -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] could not bind socket for statistics collector
Graham Clarke <[EMAIL PROTECTED]> writes: > Hi - I'm trying to get postgres 7.4.2 going on RedHat ES > The build went fine but I get the following when trying to start postgres > with this command: > LOG: disabling statistics collector for lack of working socket It tries to bind a UDP port to the hostname "localhost". I'm guessing there is something messed up in your /etc/hosts or DNS configuration, such that that name doesn't resolve to 127.0.0.1 like it should. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] [Cross-post] initdb failed on Windows 2003 Advanced Server
Hi, For some time I'm using a well-working (for me) Cygwin distribution - v1.3.2, PostgreSql 7.3.2, CygIPC v.1.13.2, PsqlODBC v.07_02_0005. I configured and started it fine in the past on WinNT, Win2K Pro/Server, WinXP Home/Pro. Recently I tried to manage it under Win2003 Advanced Server (as allways, foolowing the "postgresql-7.3.2.README" located in myDrive\cygwin\usr\doc\Cygwin). I have cygIPC started, but initDB failed with the following message: "The program 'postgres' is needed by initdb but was not found in the directory /usr/bin. Check your installation." I tryed to figure out what is wrong with my installation, checked initdb script, tryed to correct it a bit, no success. Due to lack of time I have had to switch to Win XP/Pro - everything went well with the same distribution. I'm curious - what was wrong with W2003 ? I tried to google this group, but didn't found a relative topic (yet). I'm not sure if I will have access to that 2003 box anymore, but would like to have a correct answer in case of future Win2003 instalations. I wouldn't like to upgade (if possible) to the latest CygWin/CygIpc distribution due to missing time for investigation of differences/new bugs and for rewriting of my GUI application in order to comply with the latest requirements (as I did it when migrated to PostgreSql 7.3.2). I'm using triggers, stored procedures, have optimization plan, etc. Many thanks in advance. Ivaylo Mutafchiev ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] download problems
Hi, I'm having a really stupid problem -- it seems to be impossible to download postgre. All of the US mirrors timeout. Is this normal? There are no apparent problems with my connection. Thomas E. Burns Founder, jGuru and knowspam.net http://www.knowspam.net -- Enjoy Email with knowspam.net http://www.jguru.com -- FAQs, Forums and News for Java developers [EMAIL PROTECTED] 415.255.7285 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Quad processor options
Hi, I am curious if there are any real life production quad processor setups running postgresql out there. Since postgresql lacks a proper replication/cluster solution, we have to buy a bigger machine. Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI hardware-raid 10. Has anyone experiences with quad Xeon or quad Opteron setups? I am looking at the appropriate boards from Tyan, which would be the only option for us to buy such a beast. The 30k+ setups from Dell etc. don't fit our budget. I am thinking of the following: Quad processor (xeon or opteron) 5 x SCSI 15K RPM for Raid 10 + spare drive 2 x IDE for system ICP-Vortex battery backed U320 Hardware Raid 4-8 GB Ram Would be nice to hear from you. Regards, Bjoern ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] [PERFORM] Quad processor options
We use XEON Quads (PowerEdge 6650s) and they work nice, provided you configure the postgres properly. Dell is the cheapest quad you can buy i think. You shouldn't be paying 30K unless you are getting high CPU-cache on each processor and tons of memory. I am actually curious, have you researched/attempted any postgresql clustering solutions? I agree, you can't just keep buying bigger machines. They have 5 internal drives (4 in RAID 10, 1 spare) on U320, 128MB cache on the PERC controller, 8GB RAM. Thanks, Anjan -Original Message- From: Bjoern Metzdorf [mailto:[EMAIL PROTECTED] Sent: Tue 5/11/2004 3:06 PM To: [EMAIL PROTECTED] Cc: Pgsql-Admin (E-mail) Subject: [PERFORM] Quad processor options Hi, I am curious if there are any real life production quad processor setups running postgresql out there. Since postgresql lacks a proper replication/cluster solution, we have to buy a bigger machine. Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI hardware-raid 10. Has anyone experiences with quad Xeon or quad Opteron setups? I am looking at the appropriate boards from Tyan, which would be the only option for us to buy such a beast. The 30k+ setups from Dell etc. don't fit our budget. I am thinking of the following: Quad processor (xeon or opteron) 5 x SCSI 15K RPM for Raid 10 + spare drive 2 x IDE for system ICP-Vortex battery backed U320 Hardware Raid 4-8 GB Ram Would be nice to hear from you. Regards, Bjoern ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] [PERFORM] Quad processor options
On Tue, 11 May 2004, Bjoern Metzdorf wrote: > Hi, > > I am curious if there are any real life production quad processor setups > running postgresql out there. Since postgresql lacks a proper > replication/cluster solution, we have to buy a bigger machine. > > Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI > hardware-raid 10. > > Has anyone experiences with quad Xeon or quad Opteron setups? I am > looking at the appropriate boards from Tyan, which would be the only > option for us to buy such a beast. The 30k+ setups from Dell etc. don't > fit our budget. > > I am thinking of the following: > > Quad processor (xeon or opteron) > 5 x SCSI 15K RPM for Raid 10 + spare drive > 2 x IDE for system > ICP-Vortex battery backed U320 Hardware Raid > 4-8 GB Ram Well, from what I've read elsewhere on the internet, it would seem the Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the exception to this is SGI's altix, which uses their own chipset and runs the itanium with very good memory bandwidth. But, do you really need more CPU horsepower? Are you I/O or CPU or memory or memory bandwidth bound? If you're sitting at 99% idle, and iostat says your drives are only running at some small percentage of what you know they could, you might be memory or memory bandwidth limited. Adding two more CPUs will not help with that situation. If your I/O is saturated, then the answer may well be a better RAID array, with many more drives plugged into it. Do you have any spare drives you can toss on the machine to see if that helps? Sometimes going from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in performance. In short, don't expect 4 CPUs to solve the problem if the problem isn't really the CPUs being maxed out. Also, what type of load are you running? Mostly read, mostly written, few connections handling lots of data, lots of connections each handling a little data, lots of transactions, etc... If you are doing lots of writing, make SURE you have a controller that supports battery backed cache and is configured to write-back, not write-through. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [PERFORM] Quad processor options
it's very good to understand specific choke points you're trying to address by upgrading so you dont get disappointed. Are you truly CPU constrained, or is it memory footprint or IO thruput that makes you want to upgrade? IMO The best way to begin understanding system choke points is vmstat output. Would you mind forwarding the output of "vmstat 10 120" under peak load period? (I'm asusming this is linux or unix variant) a brief description of what is happening during the vmstat sample would help a lot too. I am curious if there are any real life production quad processor setups running postgresql out there. Since postgresql lacks a proper replication/cluster solution, we have to buy a bigger machine. Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI hardware-raid 10. Has anyone experiences with quad Xeon or quad Opteron setups? I am looking at the appropriate boards from Tyan, which would be the only option for us to buy such a beast. The 30k+ setups from Dell etc. don't fit our budget. I am thinking of the following: Quad processor (xeon or opteron) 5 x SCSI 15K RPM for Raid 10 + spare drive 2 x IDE for system ICP-Vortex battery backed U320 Hardware Raid 4-8 GB Ram Would be nice to hear from you. Regards, Bjoern ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [PERFORM] Quad processor options
Anjan Dave wrote: We use XEON Quads (PowerEdge 6650s) and they work nice, > provided you configure the postgres properly. > Dell is the cheapest quad you can buy i think. > You shouldn't be paying 30K unless you are getting high CPU-cache > on each processor and tons of memory. good to hear, I tried to online configure a quad xeon here at dell germany, but the 6550 is not available for online configuration. at dell usa it works. I will give them a call tomorrow. I am actually curious, have you researched/attempted any > postgresql clustering solutions? > I agree, you can't just keep buying bigger machines. There are many asynchronous, trigger based solutions out there (eRserver etc..), but what we need is basically a master <-> master setup, which seems not to be available soon for postgresql. Our current dual Xeon runs at 60-70% average cpu load, which is really much. I cannot afford any trigger overhead here. This machine is responsible for over 30M page impressions per month, 50 page impressums per second at peak times. The autovacuum daemon is a god sent gift :) I'm curious how the recently announced mysql cluster will perform, although it is not an option for us. postgresql has far superior functionality. They have 5 internal drives (4 in RAID 10, 1 spare) on U320, > 128MB cache on the PERC controller, 8GB RAM. Could you tell me what you paid approximately for this setup? How does it perform? It certainly won't be twice as fast a as dual xeon, but I remember benchmarking a quad P3 xeon some time ago, and it was disappointingly slow... Regards, Bjoern ---(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
Re: [ADMIN] [PERFORM] Quad processor options
scott.marlowe wrote: Well, from what I've read elsewhere on the internet, it would seem the Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the exception to this is SGI's altix, which uses their own chipset and runs the itanium with very good memory bandwidth. This is basically what I read too. But I cannot spent money on a quad opteron just for testing purposes :) But, do you really need more CPU horsepower? Are you I/O or CPU or memory or memory bandwidth bound? If you're sitting at 99% idle, and iostat says your drives are only running at some small percentage of what you know they could, you might be memory or memory bandwidth limited. Adding two more CPUs will not help with that situation. Right now we have a dual xeon 2.4, 3 GB Ram, Mylex extremeraid controller, running 2 Compaq BD018122C0, 1 Seagate ST318203LC and 1 Quantum ATLAS_V_18_SCA. iostat show between 20 and 60 % user avg-cpu. And this is not even peak time. I attached a "vmstat 10 120" output for perhaps 60-70% peak load. If your I/O is saturated, then the answer may well be a better RAID array, with many more drives plugged into it. Do you have any spare drives you can toss on the machine to see if that helps? Sometimes going from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in performance. Next drives I'll buy will certainly be 15k scsi drives. In short, don't expect 4 CPUs to solve the problem if the problem isn't really the CPUs being maxed out. Also, what type of load are you running? Mostly read, mostly written, few connections handling lots of data, lots of connections each handling a little data, lots of transactions, etc... In peak times we can get up to 700-800 connections at the same time. There are quite some updates involved, without having exact numbers I'll think that we have about 70% selects and 30% updates/inserts. If you are doing lots of writing, make SURE you have a controller that supports battery backed cache and is configured to write-back, not write-through. Could you recommend a certain controller type? The only battery backed one that I found on the net is the newest model from icp-vortex.com. Regards, Bjoern ~# vmstat 10 120 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 1 0 24180 10584 32468 2332208 0 1 0 21 2 2 0 0 0 2 0 24564 10480 27812 2313528 8 0 7506 574 1199 8674 30 7 63 2 1 0 24692 10060 23636 2259176 0 18 8099 298 2074 6328 25 7 68 2 0 0 24584 18576 21056 2299804 3 6 13208 305 1598 8700 23 6 71 1 21 1 24504 16588 20912 2309468 4 0 1442 1107 754 6874 42 13 45 6 1 0 24632 13148 19992 2319400 0 0 2627 499 1184 9633 37 6 58 5 1 0 24488 10912 19292 2330080 5 0 3404 150 1466 10206 32 6 61 4 1 0 24488 12180 18824 2342280 3 0 293440 1052 3866 19 3 78 0 0 0 24420 14776 19412 2347232 6 0 403 216 1123 4702 22 3 74 0 0 0 24548 14408 17380 2321780 4 0 522 715 965 6336 25 5 71 4 0 0 24676 12504 17756 2322988 0 0 564 830 883 7066 31 6 63 0 3 0 24676 14060 18232 2325224 0 0 483 388 1097 3401 21 3 76 0 2 1 24676 13044 18700 2322948 0 0 701 195 1078 5187 23 3 74 2 0 0 24676 21576 18752 2328168 0 0 467 177 1552 3574 18 3 78 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] [PERFORM] Quad processor options
Did you mean to say the trigger-based clustering solution is loading the dual CPUs 60-70% right now? Performance will not be linear with more processors, but it does help with more processes. We haven't benchmarked it, but we haven't had any problems also so far in terms of performance. Price would vary with your relation/yearly purchase, etc, but a 6650 with 2.0GHz/1MB cache/8GB Memory, RAID card, drives, etc, should definitely cost you less than 20K USD. -anjan -Original Message- From: Bjoern Metzdorf [mailto:[EMAIL PROTECTED] Sent: Tue 5/11/2004 4:28 PM To: Anjan Dave Cc: [EMAIL PROTECTED]; Pgsql-Admin (E-mail) Subject: Re: [PERFORM] Quad processor options Anjan Dave wrote: > We use XEON Quads (PowerEdge 6650s) and they work nice, > provided you configure the postgres properly. > Dell is the cheapest quad you can buy i think. > You shouldn't be paying 30K unless you are getting high CPU-cache > on each processor and tons of memory. good to hear, I tried to online configure a quad xeon here at dell germany, but the 6550 is not available for online configuration. at dell usa it works. I will give them a call tomorrow. > I am actually curious, have you researched/attempted any > postgresql clustering solutions? > I agree, you can't just keep buying bigger machines. There are many asynchronous, trigger based solutions out there (eRserver etc..), but what we need is basically a master <-> master setup, which seems not to be available soon for postgresql. Our current dual Xeon runs at 60-70% average cpu load, which is really much. I cannot afford any trigger overhead here. This machine is responsible for over 30M page impressions per month, 50 page impressums per second at peak times. The autovacuum daemon is a god sent gift :) I'm curious how the recently announced mysql cluster will perform, although it is not an option for us. postgresql has far superior functionality. > They have 5 internal drives (4 in RAID 10, 1 spare) on U320, > 128MB cache on the PERC controller, 8GB RAM. Could you tell me what you paid approximately for this setup? How does it perform? It certainly won't be twice as fast a as dual xeon, but I remember benchmarking a quad P3 xeon some time ago, and it was disappointingly slow... Regards, Bjoern ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] [PERFORM] Quad processor options
Paul Tuckfield wrote: Would you mind forwarding the output of "vmstat 10 120" under peak load period? (I'm asusming this is linux or unix variant) a brief description of what is happening during the vmstat sample would help a lot too. see my other mail. We are running Linux, Kernel 2.4. As soon as the next debian version comes out, I'll happily switch to 2.6 :) Regards, Bjoern ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] [PERFORM] Quad processor options
Anjan Dave wrote: Did you mean to say the trigger-based clustering solution > is loading the dual CPUs 60-70% right now? No, this is without any triggers involved. Performance will not be linear with more processors, > but it does help with more processes. > We haven't benchmarked it, but we haven't had any > problems also so far in terms of performance. From the amount of processes view, we certainly can saturate a quad setup :) Price would vary with your relation/yearly purchase, etc, > but a 6650 with 2.0GHz/1MB cache/8GB Memory, RAID card, > drives, etc, should definitely cost you less than 20K USD. Which is still very much. Anyone have experience with a self built quad xeon, using the Tyan Thunder board? Regards, Bjoern ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] [PERFORM] Quad processor options
scott.marlowe wrote: Next drives I'll buy will certainly be 15k scsi drives. Better to buy more 10k drives than fewer 15k drives. Other than slightly faster select times, the 15ks aren't really any faster. Good to know. I'll remember that. In peak times we can get up to 700-800 connections at the same time. There are quite some updates involved, without having exact numbers I'll think that we have about 70% selects and 30% updates/inserts. Wow, a lot of writes then. Yes, it certainly could also be only 15-20% updates/inserts, but this is also not negligible. Sure, adaptec makes one, so does lsi megaraid. Dell resells both of these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I believe. We run the lsi megaraid with 64 megs battery backed cache. The LSI sounds good. Intel also makes one, but I've heard nothing about it. It could well be the ICP Vortex one, ICP was bought by Intel some time ago.. I haven't directly tested anything but the adaptec and the lsi megaraid. Here at work we've had massive issues trying to get the adaptec cards configured and installed on, while the megaraid was a snap. Installed RH, installed the dkms rpm, installed the dkms enabled megaraid driver and rebooted. Literally, that's all it took. I didn't hear anything about dkms for debian, so I will be hand-patching as usual :) Regards, Bjoern ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] [PERFORM] Quad processor options
On Tue, 11 May 2004, Bjoern Metzdorf wrote: > scott.marlowe wrote: > > Sure, adaptec makes one, so does lsi megaraid. Dell resells both of > > these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I > > believe. We run the lsi megaraid with 64 megs battery backed cache. > > The LSI sounds good. > > > Intel also makes one, but I've heard nothing about it. > > It could well be the ICP Vortex one, ICP was bought by Intel some time ago.. Also, there are bigger, faster external RAID boxes as well, that make the internal cards seem puny. They're nice because all you need in your main box is a good U320 controller to plug into the external RAID array. That URL I mentioned earlier that had prices has some of the external boxes listed. No price, not for sale on the web, get out the checkbook and write a blank check is my guess. I.e. they're not cheap. The other nice thing about the LSI cards is that you can install >1 and the act like one big RAID array. i.e. install two cards with a 20 drive RAID0 then make a RAID1 across them, and if one or the other cards itself fails, you've still got 100% of your data sitting there. Nice to know you can survive the complete failure of one half of your chain. > > I haven't directly tested anything but the adaptec and the lsi megaraid. > > Here at work we've had massive issues trying to get the adaptec cards > > configured and installed on, while the megaraid was a snap. Installed RH, > > installed the dkms rpm, installed the dkms enabled megaraid driver and > > rebooted. Literally, that's all it took. > > I didn't hear anything about dkms for debian, so I will be hand-patching > as usual :) Yeah, it seems to be an RPM kinda thing. But, I'm thinking the 2.0 drivers got included in the latest 2.6 kernels, so no biggie. I was looking around in google, and it definitely appears the 2.x and 1.x megaraid drivers were merged into "unified" driver in 2.6 kernel. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] [PERFORM] Quad processor options
On Tue, 11 May 2004, Bjoern Metzdorf wrote: > scott.marlowe wrote: > > > Well, from what I've read elsewhere on the internet, it would seem the > > Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the > > exception to this is SGI's altix, which uses their own chipset and runs > > the itanium with very good memory bandwidth. > > This is basically what I read too. But I cannot spent money on a quad > opteron just for testing purposes :) Wouldn't it be nice to just have a lab full of these things? > > If your I/O is saturated, then the answer may well be a better RAID > > array, with many more drives plugged into it. Do you have any spare > > drives you can toss on the machine to see if that helps? Sometimes going > > from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in > > performance. > > Next drives I'll buy will certainly be 15k scsi drives. Better to buy more 10k drives than fewer 15k drives. Other than slightly faster select times, the 15ks aren't really any faster. > > In short, don't expect 4 CPUs to solve the problem if the problem isn't > > really the CPUs being maxed out. > > > > Also, what type of load are you running? Mostly read, mostly written, few > > connections handling lots of data, lots of connections each handling a > > little data, lots of transactions, etc... > > In peak times we can get up to 700-800 connections at the same time. > There are quite some updates involved, without having exact numbers I'll > think that we have about 70% selects and 30% updates/inserts. Wow, a lot of writes then. > > If you are doing lots of writing, make SURE you have a controller that > > supports battery backed cache and is configured to write-back, not > > write-through. > > Could you recommend a certain controller type? The only battery backed > one that I found on the net is the newest model from icp-vortex.com. Sure, adaptec makes one, so does lsi megaraid. Dell resells both of these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I believe. We run the lsi megaraid with 64 megs battery backed cache. Intel also makes one, but I've heard nothing about it. If you get the LSI megaraid, make sure you're running the latest megaraid 2 driver, not the older, slower 1.18 series. If you are running linux, look for the dkms packaged version. dkms, (Dynamic Kernel Module System) automagically compiles and installs source rpms for drivers when you install them, and configures the machine to use them to boot up. Most drivers seem to be slowly headed that way in the linux universe, and I really like the simplicity and power of dkms. I haven't directly tested anything but the adaptec and the lsi megaraid. Here at work we've had massive issues trying to get the adaptec cards configured and installed on, while the megaraid was a snap. Installed RH, installed the dkms rpm, installed the dkms enabled megaraid driver and rebooted. Literally, that's all it took. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [PERFORM] Quad processor options
I'm confused why you say the system is 70% busy: the vmstat output shows 70% *idle*. The vmstat you sent shows good things and ambiguous things: - si and so are zero, so your not paging/swapping. Thats always step 1. you're fine. - bi and bo (physical IO) shows pretty high numbers for how many disks you have. (assuming random IO) so please send an "iostat 10" sampling during peak. - note that cpu is only 30% busy. that should mean that adding cpus will *not* help. - the "cache" column shows that linux is using 2.3G for cache. (way too much) you generally want to give memory to postgres to keep it "close" to the user, not leave it unused to be claimed by linux cache (need to leave *some* for linux tho) My recommendations: - I'll bet you have a low value for shared buffers, like 1. On your 3G system you should ramp up the value to at least 1G (125000 8k buffers) unless something else runs on the system. It's best to not do things too drastically, so if Im right and you sit at 1 now, try going to 3 then 6 then 125000 or above. - if the above is off base, then I wonder why we see high runque numbers in spite of over 60% idle cpu. Maybe some serialization happening somewhere. Also depending on how you've laid out your 4 disk drives, you may see all IOs going to one drive. the 7M/sec is on the high side, if that's the case. iostat numbers will reveal if it's skewed, and if it's random, tho linux iostat doesn't seem to report response times (sigh) Response times are the golden metric when diagnosing IO thruput in OLTP / stripe situation. On May 11, 2004, at 1:41 PM, Bjoern Metzdorf wrote: scott.marlowe wrote: Well, from what I've read elsewhere on the internet, it would seem the Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the exception to this is SGI's altix, which uses their own chipset and runs the itanium with very good memory bandwidth. This is basically what I read too. But I cannot spent money on a quad opteron just for testing purposes :) But, do you really need more CPU horsepower? Are you I/O or CPU or memory or memory bandwidth bound? If you're sitting at 99% idle, and iostat says your drives are only running at some small percentage of what you know they could, you might be memory or memory bandwidth limited. Adding two more CPUs will not help with that situation. Right now we have a dual xeon 2.4, 3 GB Ram, Mylex extremeraid controller, running 2 Compaq BD018122C0, 1 Seagate ST318203LC and 1 Quantum ATLAS_V_18_SCA. iostat show between 20 and 60 % user avg-cpu. And this is not even peak time. I attached a "vmstat 10 120" output for perhaps 60-70% peak load. If your I/O is saturated, then the answer may well be a better RAID array, with many more drives plugged into it. Do you have any spare drives you can toss on the machine to see if that helps? Sometimes going from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in performance. Next drives I'll buy will certainly be 15k scsi drives. In short, don't expect 4 CPUs to solve the problem if the problem isn't really the CPUs being maxed out. Also, what type of load are you running? Mostly read, mostly written, few connections handling lots of data, lots of connections each handling a little data, lots of transactions, etc... In peak times we can get up to 700-800 connections at the same time. There are quite some updates involved, without having exact numbers I'll think that we have about 70% selects and 30% updates/inserts. If you are doing lots of writing, make SURE you have a controller that supports battery backed cache and is configured to write-back, not write-through. Could you recommend a certain controller type? The only battery backed one that I found on the net is the newest model from icp-vortex.com. Regards, Bjoern ~# vmstat 10 120 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 1 0 24180 10584 32468 2332208 0 1 0 21 2 2 0 0 0 2 0 24564 10480 27812 2313528 8 0 7506 574 1199 8674 30 7 63 2 1 0 24692 10060 23636 2259176 0 18 8099 298 2074 6328 25 7 68 2 0 0 24584 18576 21056 2299804 3 6 13208 305 1598 8700 23 6 71 1 21 1 24504 16588 20912 2309468 4 0 1442 1107 754 6874 42 13 45 6 1 0 24632 13148 19992 2319400 0 0 2627 499 1184 9633 37 6 58 5 1 0 24488 10912 19292 2330080 5 0 3404 150 1466 10206 32 6 61 4 1 0 24488 12180 18824 2342280 3 0 293440 1052 3866 19 3 78 0 0 0 24420 14776 19412 2347232 6 0 403 216 1123 4702 22 3 74 0 0 0 24548 14408 17380 2321780 4 0 522 715 965 6336 25 5 71 4 0 0 24676 12504 17756 2322988 0 0 564 830 883 7066 31 6 63 0 3 0 24676 14060 18232
Re: [ADMIN] [PERFORM] Quad processor options
On Tue, 11 May 2004, Bjoern Metzdorf wrote: > I am curious if there are any real life production quad processor setups > running postgresql out there. Since postgresql lacks a proper > replication/cluster solution, we have to buy a bigger machine. Du you run the latest version of PG? I've read the thread bug have not seen any information about what pg version. All I've seen was a reference to debian which might just as well mean that you run pg 7.2 (probably not but I have to ask). Some classes of queries run much faster in pg 7.4 then in older versions so if you are lucky that can help. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] [PERFORM] Quad processor options
...and on Tue, May 11, 2004 at 03:02:24PM -0600, scott.marlowe used the keyboard: > > If you get the LSI megaraid, make sure you're running the latest megaraid > 2 driver, not the older, slower 1.18 series. If you are running linux, > look for the dkms packaged version. dkms, (Dynamic Kernel Module System) > automagically compiles and installs source rpms for drivers when you > install them, and configures the machine to use them to boot up. Most > drivers seem to be slowly headed that way in the linux universe, and I > really like the simplicity and power of dkms. > Hi, Given the fact LSI MegaRAID seems to be a popular solution around here, and many of you folx use Linux as well, I thought sharing this piece of info might be of use. Running v2 megaraid driver on a 2.4 kernel is actually not a good idea _at_ _all_, as it will silently corrupt your data in the event of a disk failure. Sorry to have to say so, but we tested it (on kernels up to 2.4.25, not sure about 2.4.26 yet) and it comes out it doesn't do hotswap the way it should. Somehow the replaced disk drives are not _really_ added to the array, which continues to work in degraded mode for a while and (even worse than that) then starts to think the replaced disk is in order without actually having resynced it, thus beginning to issue writes to non-existant areas of it. The 2.6 megaraid driver indeed seems to be a merged version of the above driver and the old one, giving both improved performance and correct functionality in the event of a hotswap taking place. Hope this helped, -- Grega Bremec Senior Administrator Noviforum Ltd., Software & Media http://www.noviforum.si/ pgp2JqHMlTO7C.pgp Description: PGP signature
[ADMIN] \set
Hi, How to use an internal variable? Original question was how to set a variable in postgresql? If I want to set a variable like start_date='2004-05-10'; How could I use it in my SQL statement? E.g. Db> set start_date '2004-05-10' Db> select start_date as 'start date'; It's not executable! Thanks. Jie Liang ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
