Re: [HACKERS] COMMIT NOWAIT Performance Option
I am not sure about some of this. The Oracle option does not change the engine fsync behavior I believe. All that is changed is whether the client side waits for the complete of the fsync or not. If this is true, the data store, logs, etc, are all protected. The user may still experience a data loss if a network, or system failure occurred just after the client issued the commit. This would be something like I send the message and exit. However prior to the engine receiving the message, a network component fails and the message is never delivered. This will turn into an aborted transaction as far as the engine is concerned. Of course, the exact details are in the protocol between the client and the server. The commit nowait is async with respect to the response to the user, not the underlying engine I think. Therefore performance gains are purely a user perspective, not an engine perspective. Perhaps some network traffic could be pruned, not sending the response. Jordan Henderson On Tuesday 27 February 2007, Joshua D. Drake wrote: Josh Berkus wrote: Simon, One of the things I love about doing informal online user support in the PostgreSQL community, and formal user support for Sun's customers, is the almost-ironclad guarentee that if a user has a corrupt database or data loss, one of three things is true: a) they didn't apply some recommended PG update; b) they have a bad disk controller or disk config; c) they have bad ram. That is pretty spot on. It seriously narrows down the problem space to know that PostgreSQL does *not* allow data loss if it's physically possible to prevent it. But we do don't we? fsync = off, full_page_writes = off? Therefore, if we're going to arm a foot-gun as big as COMMIT NOWAIT for PostgreSQL, I'd like to see the answers to two questions: I agree with this. a) Please give some examples of performance gain on applications using COMMIT NOWAIT. The performance gain needs to be substantial (like, 50% to 100%) to justify a compromise like this. WOAH... that seems excessive. There are a couple of things going on here. 1. We have a potential increase in performance for certain workloads. This is good, but must be proven. IS that proof 50%? Bah.. let's talk 15-25%. 2. We have to accept that not everyone wants IRON clad data integrity. We have many, many options for dealing with that now, including PITR and REPLICATION. b) Why this and not global temporary tables or queuing? /me would love global temp tables. Much of the PostgreSQL Users out there today, will happily loose a 15 minutes of data if it means their data is served 25% faster. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] What can we learn from MySQL?
I think that when considering install, it is very important, if not critical, that we all understand who is doing the install. Certainly if it is a person much like us, meaning people on the hackers/development list, we can all handle more terse installs. Personally, I like the freedom of choices, and not having a result of hundreds of megs that I know are not required. On the other hand, we are really a minority. The masses certainly like simple installs, regardless of just how many megs are used, needed or not. If the masses really cared, then Microsoft would be in trouble. But, as we can see in the market place, they don't. In fact, most people think more is better. Somehow they think 2 CDROMs is better than 1 CDROM. So, if it takes an extra 200 meg to make a glitsy install with little videos expounding on how great Postgresql is, then for that user, it will make all of the difference. We need to remember who the audience is. We cannot gain mass market share otherwise. My 2 cents, won't buy coffee, Jordan Henderson --- Bruno Wolff III [EMAIL PROTECTED] wrote: On Fri, Apr 23, 2004 at 16:36:57 -0400, [EMAIL PROTECTED] wrote: Ease of use is VERY important, but few suggestions that address this are ever really accepted. Yes, focusing on the functionality is the primary concern, but how you set it up and deploy it is VERY important. You guys need to remember, people are coming from a world where MySQL, Oracle, and MSSQL all have nice setup programs. nice must be in the eye of the beholder. I have used Oracle's installer to install a client and was not amused by it need hundreds of megabtyes to do a client install. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: 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: [HACKERS] Proposal for a cascaded master-slave replication system
Jan, I am wondering if you are familar with the work covered in 'Recovery in Parallel Database Systems' by Svein-Olaf Hvasshovd (Vieweg) ? The book is an excellent detailed description covering high availablility DB implementations. I think your right on by not thinking smaller!! Jordan Henderson On Wednesday 12 November 2003 10:45, Jan Wieck wrote: Hans-Jürgen Schönig wrote: Jan, First of all we really appreciate that this is going to be an Open Source project. There is something I wanted to add from a marketing point of view: I have done many public talks in the 2 years or so. There is one question people keep asking me: How about the pgreplication project?. In every training course, at any conference people keep asking for synchronous replication. We have offered this people some async solutions which are already out there but nobody seems to be interested in having it (my person impression). People keep asking for a sync approach via email but nobody seems to care about an async approach. This does not mean that async is bad but we can see a strong demand for synchronous replication. Meanwhile we seem to be in a situation where PostgreSQL is rather competing against Oracle than against MySQL. In our case there are more people asking for Oracle - Pg migration than for MySQL - Pg. MySQL does not seem to be the great enemy because most people know that it is an inferior product anyway. What I want to point out is that some people want an alternative Oracle's Real Application Cluster. They want load balancing and hot failover. Even data centers asking for replication did not want to have an async approach in the past. Hans-Jürgen, we are well aware of the high demand for multi-master replication addressing load balancing and clustering. We have that need ourself as well and I plan to work on a follow-up project as soon as Slony-I is released. But as of now, we see a higher priority for a reliable master slave system that includes the cascading and backup features described in my concept. There are a couple of different similar product out there, I know. But show me one of them where you can failover without becoming the single point of failure? We've just recently seen ... or better where not able to see anything any more how failures tend to ripple through systems - half of the US East Coast was dark. So where is the replication system where a slave becomes the master, and not a standalone server. Show me one that has a clear concept of failback, one that has hot-join as a primary design goal. These are the features that I expect if something is labeled Enterprise Level. As far as my ideas for multi-master go, it will be a synchronous solution using group communication. My idea is group commit instead of 2-Phase ... and an early stage test hack has replicated some update 3 weeks ago. The big challange will be to integrate the two systems so that a node can start as an asynchronous Slony-I slave, catch up ... and switch over to synchronous multimaster without stopping the cluster. I have no clue yet how to do that, but I refuse to think smaller. Jan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] O_DIRECT in freebsd
My experience with DB2 showed that properly setup DMS tablespaces provided a significant performance benefit. I have also seen that the average DBA does not generally understand the data or access patterns in the database. Given that, they don't correctly setup table spaces in general, filesystem or raw. Likewise, where it is possible to tie a tablespace to a memory buffer pool, the average DBA does not setup it up to a performance advantage either. However, are we talking about well tuned setups by someone who does understand the data and the general access patterns? For a DBA like that, they should be able to take advantage of these features and get significantly better results. I would not say it requires considerable tuning, but an understanding of data, storage and access patterns. Additionally, these features did not cause our group considerable administrative overhead. Jordan Henderson On Thursday 30 October 2003 12:55, Sailesh Krishnamurthy wrote: DB2 supports cooked and raw file systems - SMS (System Manged Space) and DMS (Database Managed Space) tablespaces. The DB2 experience is that DMS tends to outperform SMS but requires considerable tuning and administrative overhead to see these wins. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] O_DIRECT in freebsd
Personally, I think it is useful to have features. I quite understand the difficulties in maintaining some features however. Also having worked on internals for commercial DB engines, I have specifically how code/data paths can be shortened. I would not make the choice for someone to be forced into using a product in a specific manner. Instead, I would let them decide whether to choose a simple setup or, if they are up to it, something with better performance. I would not prune the options out. In doing so, we limit what a knowledgeable person can do a priori. Jordan Henderson On Thursday 30 October 2003 19:59, Sailesh Krishnamurthy wrote: Jordan == Jordan Henderson [EMAIL PROTECTED] writes: Jordan significantly better results. I would not say it requires Jordan considerable tuning, but an understanding of data, storage Jordan and access patterns. Additionally, these features did not Jordan cause our group considerable administrative overhead. I won't dispute the specifics. I have only worked on the DB2 engine - never written an app for it nor administered it. You're right - the bottomline is that you can get a significant performance advantage provided you care enough to understand what's going on. Anyway, I merely responded to provide a data point. Will PostgreSQL users/administrators care for additional knobs or is there a preference for keep it simple, stupid ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 2-phase commit
On Monday 13 October 2003 20:11, Rod Taylor wrote: I think another way it could be handled is with nested transactions. Just have the promise phase be an inner transaction commit but have an outer transaction bracket that one for the actual commit. Not really. In the event of a crash, most 2PC systems will expect the participant to come back in the same state it crashed in. Yes, this is correct. There are certain phases of the protocol in which the transaction state must be re-instated from the log file after a crash of the DB server. The re-instatement must occur prior to any connections being accepted by the server. Additionally, the coordinator must be fully recoverable as well. The coordinator may, depending on the phase of the commit/abort, contact child servers after it crashes. The requirement is that during log replay, the transaction structures might have to be fully reconstructed and remain in-place after log replay has completed, until the disposition of the (sub)transaction is settled by the coordinator. All dependent on the phase of course. Our nested-transaction implementation (like our standard transaction implementation) aborts all transactions on crash. Jordan Henderson ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] request for sql3 compliance for the update command
Dave, Justin, I have several Informix clients who will be moving to a Postgresql/Aubit4gl solution at some point. The Informix line is, for them, a dead end. One way or another the backend will become Postgresql. Because of the number of SQL statements, I would encourage support where possible and reasonable. Jordan - Original Message - From: Dave Cramer [EMAIL PROTECTED] To: Justin Clift [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; Peter Eisentraut [EMAIL PROTECTED]; Pgsql Hackers [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 10:18 PM Subject: Re: [HACKERS] request for sql3 compliance for the update command Justin, This is certainly the case here. I think IBM is deprecating informix, and many informix users are being forced to make a change, and they are seriously considering postgres as an alternative. It behooves us to look at aubit http://aubit4gl.sourceforge.net/ before making this decision as well. I believe the aubit project has the potential to move postgres forward considerably as well. Dave On Wed, 2003-02-19 at 21:08, Justin Clift wrote: Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? I would vote against it ... but that's only one vote. As a thought, will it add significant maintenance penalties or be detrimental? There seem to be quite a lot of Informix people moving to PostgreSQL these days, moreso than Oracle shops. Might have been brought on by IBM's purchase of Informix. Wondering if this one change be a significant improvement in regards to making it easier to migrate, or just a minor thing? Regards and best wishes, Justin Clift regards, tom lane -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 3: 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
