[GENERAL] full_page_writes = off?
Hi, would full_page_writes=off increase the risk of inconsistency or db corruption in 8.3 and FreeBSD 7? fsync = on; Definitely on, as single power outage after three years is guaranteed to make your life interesting otherwise. synchronous_commit = off; Can be off in my case as I'm not doing any external actions based on transaction's success or failure (if I understand the docs correctly). So I don't care at which moment the evil strikes as long as the db is consistent. full_page_writes = off; AFAIK when this is on it means synchronous WAL writing with less impact, as it occurs once per checkpoint, but it's still synchronous. Not sure at all about this one. How would FreeBSD 7's UFS survive the power crash etc. with this set to off? OTOH, does on play well with synchronous_commit=off? Meaning, will the transaction holder get success immediately on commit, still guaranteeing consistency? Thanks for any insights or clarifying missed points. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] libeay32.dll and libpq.dll
--- Original Message --- From: [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: 30/11/07, 23:28:33 Subject: [GENERAL] libeay32.dll and libpq.dll I'm working on a Windows application that will connect to a Postgresql database without having through ODBC. I'm using libpq and testing with a simple PQconnectdb/PQfinish call but the application fails as soon as PQconnectdb is called. The error I get says that libeay32.dll is missing. I have no idea what this program is and why it would be required. They are the OpenSSL libraries that your libpq was built against. If you're developing with libpq you should really do so against a build rather than the source tree - using the windows installer will get you all the headers, dlls and import libraries you need provided you opt to install the Development feature. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recheck condition
Martijn van Oosterhout [EMAIL PROTECTED] writes: On Fri, Nov 30, 2007 at 11:27:24AM -0500, Josh Harrison wrote: Thanks for your reply Is there a way to get them not to use the heap for intermediate result and go to heap only for final data? This will drastically improve the performance but Im not sure if postgres can do that? Will creating the index in a different way and/or rewriting the query in a different way achieve this result? I'm trying to imagine what it would take to avoid the heap access after the index scan I don't think it's possible. It would require that the bitmaps generated by the bitmap scan have the person_id attached and then have the bitmap AND operation only happen if the person IDs match. No such machinary currently exists. I think you're describing a star schema join. This is a common checklist item for data warehousing databases. The classic data warehouse has a table like person which has the info you're looking for, and dozens of tables with person_id and possibly some associated data. In some cases those tables don't even have any other data, the mere existence of the person_id in that table is enough. So a typical query could look like something like: select * from person where person_id in (select person_id from people_who_used_service_in_the_past) and person_id in (select person_id from people_with_big_balances) and person_id in (select person_id from people_...) and person_id not in (select person_id from people_who_unsubscribed) and person_id not in (select person_id from people_who_we_mailed_last_week) The best plan for this is to gather up the person_ids in a kind of bitmap scan with a bitmap of ids. And once the bitmap is done scan an index on person for just the matching records. Postgres doesn't support anything like this (yet:). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] full_page_writes = off?
rihad [EMAIL PROTECTED] writes: Hi, would full_page_writes=off increase the risk of inconsistency or db corruption in 8.3 and FreeBSD 7? yes. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] full_page_writes = off?
On Sat, 01 Dec 2007, rihad wrote: fsync = on; synchronous_commit = off; full_page_writes = off; Thanks for any insights or clarifying missed points. Also check whether a disks have write-caching turned off - it should. On Linux the command is: # hdparm -W /dev/sda /dev/sda: write-caching = 0 (off) I don't know how to check it on BSD. It can be on for extra performance only if your disks are connected to battery backed RAID controller. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] full_page_writes = off?
Tomasz Ostrowski escribió: On Sat, 01 Dec 2007, rihad wrote: fsync = on; synchronous_commit = off; full_page_writes = off; Thanks for any insights or clarifying missed points. Also check whether a disks have write-caching turned off - it should. On Linux the command is: # hdparm -W /dev/sda /dev/sda: write-caching = 0 (off) Uh? That command as was written lacks an argument for -W (0/1). From the hdparm --help: -W set drive write-caching flag (0/1) (DANGEROUS) # hdparm -V hdparm v6.9 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] full_page_writes = off?
On Sat, 01 Dec 2007, Martin Marques wrote: Also check whether a disks have write-caching turned off - it should. On Linux the command is: # hdparm -W /dev/sda /dev/sda: write-caching = 0 (off) Uh? That command as was written lacks an argument for -W (0/1). From the hdparm --help: -W set drive write-caching flag (0/1) (DANGEROUS) # hdparm -V hdparm v6.9 # hdparm -V hdparm v7.7 # hdparm --help 21 | grep write-caching -W get/set drive write-caching flag (0/1) You can also use hdparm -I to check this - look for a Write caching in Commands/features section. If it has a * in front then it is enabled and dangerous. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] HA and Replication - how to choose among all the available solutions
By the way I found also another tool called CyberCluster that will probably not make my choice easier ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] hibernate + postgresql ?
On Fri, Nov 30, 2007 at 08:10:11AM +0100, Luca Ferrari wrote: On Thursday 29 November 2007 Joshua D. Drake's cat, walking on the keyboard, wrote: If you are not lazy you can push outside the standard hibernate methods and produce very usable code but then you have to wonder why you have hibernate there at all. What do you mean with this? I think ORM could save developers' time especially when there are a lot of relationships among objects, that should be manually mapped thru SQL statements. You'd only think so if you hadn't actually seen these things in action. They save no time because of the silly, unreasonable assumptions underlying them, which in turn cause people to do silly, unreasonable things in order to make them work. Now what do you mean with push outside hibernate methods? You write the each SQL statement or simply skip some Hibernate facilities? You'll wind up writing each SQL statement anyway, so just start out with that rather than imagining that a piece of software can pick the appropriate level of abstraction and then finding out that it can't. :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cluster using tablespaces?
Alvaro Herrera wrote: Rainer Bauer wrote: Ok, I expected that. Does this work: ALTER TABLE foo CLUSTER ON index_name SET TABLESPACE new_tablespace I.e. is the table moved to the other tablespace and clustered at the same time or are these independant operations? No, the ALTER TABLE/CLUSTER ON only defines what index will the table be clustered on in the future, but it doesn't cluster it at that time. Perhaps it could be improved so that if a table rewrite is going to be done anyway for some other reason, then make sure the rewrite uses the cluster order. I think it's far from trivial though. Yeah that is what I was originally looking for. What I am trying to achieve is cutting down the time the cluster command takes. I thought the most promising way would be if the new data is written to different drive. It has been theorized that cluster would be faster in general if instead of doing an indexscan we would instead use a seqscan + sort step. It would be good to measure it. Could a reindex on the clustered index speed up the clustering (when executed immediatelly before the cluster command)? As I understand it, this index is used to fetch the table data in the correct order. Or is most of the time spend fetching the table data? Also, would it make sense to increase shared_buffers for the cluster operation. This is set to 32MB here on my Windows box as was recommended. From my questions you can see that I don't know how the clustering is working internally. I.e. I don't have a concrete idea how to make cluster any faster. For btree indexes, there is a temporary copy of the index data, which will go wherever you have arranged for temp files to go. (I think that easy user control of this may be new for 8.3, though.) Could you give me a hint where that would be on Windows? I guess this might be worth a try since there are a couple of btree indexes in the database. I think Tom is referring to the new temp_tablespaces config variable. I moved the pgsql_tmp directory to another disk, but that didn't speed up the cluster command. Rainer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing
WHich solution are you using now, Guido? Has anyone made experiences with cybercluster? I am thankful to hear comments and especially comparision to other products thanks Ragnar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Guido Neitzer Sent: Freitag, 30. November 2007 21:03 To: Shane Ambler Cc: Postgresql General Subject: Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing On 30.11.2007, at 12:50, Shane Ambler wrote: I project where the latest news page shows the newest entry from March 2005 and the install talks only about PostgreSQL 8.0 isn't really inspiring confidence ... Although they aren't the fastest with releases, they really aren't as bad as that. Nicely said ... ;-) Yeah, I was looking at the wrong site. Maybe there should be a redirect to the new page to avoid that. I have last looked at pgcluster back in 2005 when I was trying to find an affordable multi master solution an the setup sounded so horrific that we spent about 10k EUR to get a different solution that is actually working fine now. Setting this up on just two machines was about 10 minutes work ... I hope the setup has changed to the better. cug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux v.s. Mac OS-X Performance
At 6:15 PM -0500 11/30/07, Greg Smith wrote: On Fri, 30 Nov 2007, Guido Neitzer wrote: Actually - In our test if just used with a similar load as pgbench (e.g. typical web applications) Mac OS X 10.4.7 performed better then Yellow Dog Linux (I was testing with G5 hardware) on the same hardware as soon as more than about 90 concurrent clients were simulated. At this point, that's just an interesting historical note. Yellow Dog is not a particularly good Linux compared with the ones that have gotten years worth of performance tuning for Intel/AMD processors. And you really can't extrapolate anything useful today from how it ran on a G5--that's two layers of obsolete. The comparisons that matter now are Intel+Mac OS vs. Intel+a popular Linux aimed at servers. As an unrelated note, I'm curious what you did with pgbench that you consider it a reasonable similation of a web application. The default pgbench transaction is very write-heavy, and the read-only option available is way too simple to be realistic. You'd need to pass in custom scripts to execute to get something that acted like a web app. pgbench is an unruly tool, and there's many ways to run it that gives results that aren't so useful. If this is any help to anyone, I'm running Postgresql on an Intel Xserve Mac OS X. Performance is more than fine for my usage. If anyone would like me to run some benchmark code to test comparisons, I'd be happy to do so. -Owen ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing
On 01.12.2007, at 09:35, Ragnar Heil wrote: WHich solution are you using now, Guido? For the one installation we needed multi-master, we have FrontBase running. PostgreSQL multi-master just wasn't right for us. Too much hardware needed to get real redundancy, the setup is too complex and didn't provide what we needed: multi-master replication with two machines for real redundancy not really performance (which is good enough with one simple, cheap server), load-balancing is done in the apps (we have instances connecting to one or the other server), automatic failover in case of one server goes down. If we want to bring it back up, it's a matter of some really simple commands and it synchronizes itself with the master, no copying of files, no complex scripting, no complex procedures at all. Performance of one PostgreSQL server would be faster compared to that setup, at least for the load we have, as FrontBase seems to be getting slower with inserting in large tables with complex indexes. PostgreSQL index creation is way faster. There are also other annoying things we have seen, but overall the solution works fine. cug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] power failure....
Tom Lane wrote: Tom Allison [EMAIL PROTECTED] writes: 2007-11-30 19:35:20 EST PANIC: could not locate a valid checkpoint record Ugh :-(. pg_resetxlog should get you back into the database, but it's anybody's guess whether and how badly the contents will be corrupted. I would recommend trying a dump/reload and also doing any manual consistency crosschecks you can think of. postgres version 8.2 sitting on a Reiser FS on RAID1 Better take a second look at your disk hardware configuration. At least in theory, this Can't Happen if your disk hardware is handling fsync honestly. regards, tom lane Cool, thanks. I wonder what I'm supposed to do with my debian installation since there doesn't seem to be any such thing as pg_resetxlog. Or is it hiding somewhere? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] power failure....
Tom Lane wrote: Tom Allison [EMAIL PROTECTED] writes: 2007-11-30 19:35:20 EST PANIC: could not locate a valid checkpoint record Ugh :-(. pg_resetxlog should get you back into the database, but it's anybody's guess whether and how badly the contents will be corrupted. I would recommend trying a dump/reload and also doing any manual consistency crosschecks you can think of. postgres version 8.2 sitting on a Reiser FS on RAID1 Better take a second look at your disk hardware configuration. At least in theory, this Can't Happen if your disk hardware is handling fsync honestly. regards, tom lane I tracked it down and did a reset. I only have one large table right now. And now I've decided to start using fsync=on!!! :) I don't think I want to test this just yet... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] power failure....
On Sat, 01 Dec 2007 15:22:38 -0500 Tom Allison [EMAIL PROTECTED] wrote: regards, tom lane Cool, thanks. I wonder what I'm supposed to do with my debian installation since there doesn't seem to be any such thing as pg_resetxlog. Or is it hiding somewhere? I don't recall if it is in contrib or not.. try?: apt-file is your friend: apt-file search pg_resetxlog /usr/lib/postgresql/8.1/bin/pg_resetxlog Joshua D. Drake ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings signature.asc Description: PGP signature
Re: [GENERAL] power failure....
On Sat, 01 Dec 2007 15:32:50 -0500 Tom Allison [EMAIL PROTECTED] wrote: I tracked it down and did a reset. I only have one large table right now. And now I've decided to start using fsync=on!!! :) change wal_sync_method to open_sync and fsync=on isn't nearly as bad as it sounds. joshua drake I don't think I want to test this just yet... ---(end of broadcast)--- TIP 6: explain analyze is your friend signature.asc Description: PGP signature
Re: [GENERAL] power failure....
Tom Allison escribió: I wonder what I'm supposed to do with my debian installation since there doesn't seem to be any such thing as pg_resetxlog. Or is it hiding somewhere? On one debian I have: /usr/lib/postgresql/8.1/bin/pg_resetxlog /usr/lib/postgresql/8.2/bin/pg_resetxlog ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] power failure....
Joshua D. Drake escribió: On Sat, 01 Dec 2007 15:22:38 -0500 I wonder what I'm supposed to do with my debian installation since there doesn't seem to be any such thing as pg_resetxlog. Or is it hiding somewhere? I don't recall if it is in contrib or not.. try?: apt-file is your friend: apt-file search pg_resetxlog /usr/lib/postgresql/8.1/bin/pg_resetxlog $ dlocate /usr/lib/postgresql/8.2/bin/pg_resetxlog postgresql-8.2: /usr/lib/postgresql/8.2/bin/pg_resetxlog ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Stored procedure issue
Hello I have a stored procedure which does the billing stuff in our system,it works ok,but if I put in production,where there is some 5-10 billing events per second,the whole database slows down. It won't even drop some test table,reindex,vacuum,things which were done before in the blink of an eye. If I stop the application which calls the procedure,all is back to normal. We didn't implement any special locking mechanism in the procedure,all is default. The procedure is updating user's balance in table 'users'. On the other hand a couple of 'heavy load' table has foreign keys pointing to table 'users'. Is it the matter of concurency and some locking issue or maybe the existing of all those foreign keys pointing to table 'users',or maybe something else which we're not aware at the moment ? Sincerely Pera Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stored procedure issue
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/01/07 20:40, Dragan Zubac wrote: Hello I have a stored procedure which does the billing stuff in our system,it works ok,but if I put in production,where there is some 5-10 billing events per second,the whole database slows down. It won't even drop some test table,reindex,vacuum,things which were done before in the blink of an eye. If I stop the application which calls the procedure,all is back to normal. We didn't implement any special locking mechanism in the procedure,all is default. The procedure is updating user's balance in table 'users'. On the other hand a couple of 'heavy load' table has foreign keys pointing to table 'users'. Is it the matter of concurency and some locking issue or maybe the existing of all those foreign keys pointing to table 'users',or maybe something else which we're not aware at the moment ? Are you using transactions? Are the tables properly indexed? Are the queries in the SP using the indexes properly? Did you do all the testing on a tiny database. Is the SP written as efficiently? (Think of ways to refactor it in order to get the same results with less effort.) - -- Ron Johnson, Jr. Jefferson LA USA %SYSTEM-F-FISH, my hovercraft is full of eels -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHUh9nS9HxQb37XmcRAjPTAJ4jRUZUaF+j2KAB3+lBY6A3ROfynACfawWT 0QN026Ncl/Iag2M6E1kfjUg= =RlXy -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Dump/Restore Large Object OID
Why not give it a try once? Dump and restore once and see for yourself. You'd have done that by now, but if you haven't do give it a try instead of waiting any more. You may learn a thing or two in the process... Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco * Mail sent from my BlackLaptop device On 11/29/07, Norberto Delle [EMAIL PROTECTED] wrote: Hi all If I don't use the --oids option when dumping a database with pg_dump, can I assure that the loid field of the pg_largeobject table will keep it's value when restoring? Thanks in advance Norberto ---(end of broadcast)--- TIP 6: explain analyze is your friend
