Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
I will write a technical document about Sigres in a week. Hideyuki Bruce Momjian wrote: I am still unclear why sigres is better than a temporary file system. I relize your patch is faster, but what is about your patch that makes it faster. And if we were going to add such capability, we would name it based on what it does, rather than on a 'sigres' mode. --- Hideyuki Kawashima wrote: Simon, Not checkpointing at all is not a good plan, since this will lead to an enormous build up of WAL files and a very long recovery time if the system does fail. I appreciate your detailed comments. Following your comments, I revised the problem. Sigres-0.1.3 does checkpointings. In summary, the features of Sigres-0.1.3 are as follows. 0: 10% faster than conventional PostgreSQL under tmpfs. 1: Checkpointings are continually executed. 2: Sigres mode is in default (the mode can be turned off via postgresql.conf). 3: issue_xlog_sync is called only by bgwriter (continually, via createcheckpoint) 4: The entity of XLogWrite (_XLogWrite in my code) is called by both backends and a bgwriter. For each backend, _XLogWrite is called only via AdvanceXLInsertBuffer. For a bgwriter, _XLogWrite is called via CreateCheckPoint. Please try it if you have interest. http://sourceforge.jp/projects/sigres/ Again, I really appreciate beneficial comments from this community ! Regards, -- Hideyuki -- Hideyuki Kawashima (Ph.D.) University of Tsukuba Assistant Professor ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Hideyuki Kawashima (Ph.D), University of Tsukuba, Graduate School of Systems and Information Engineering Assistant Professor, TEL: +81-29-853-5322 ---(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: [HACKERS] Acclerating INSERT/UPDATE using UPS
I am still unclear why sigres is better than a temporary file system. I relize your patch is faster, but what is about your patch that makes it faster. And if we were going to add such capability, we would name it based on what it does, rather than on a 'sigres' mode. --- Hideyuki Kawashima wrote: Simon, Not checkpointing at all is not a good plan, since this will lead to an enormous build up of WAL files and a very long recovery time if the system does fail. I appreciate your detailed comments. Following your comments, I revised the problem. Sigres-0.1.3 does checkpointings. In summary, the features of Sigres-0.1.3 are as follows. 0: 10% faster than conventional PostgreSQL under tmpfs. 1: Checkpointings are continually executed. 2: Sigres mode is in default (the mode can be turned off via postgresql.conf). 3: issue_xlog_sync is called only by bgwriter (continually, via createcheckpoint) 4: The entity of XLogWrite (_XLogWrite in my code) is called by both backends and a bgwriter. For each backend, _XLogWrite is called only via AdvanceXLInsertBuffer. For a bgwriter, _XLogWrite is called via CreateCheckPoint. Please try it if you have interest. http://sourceforge.jp/projects/sigres/ Again, I really appreciate beneficial comments from this community ! Regards, -- Hideyuki -- Hideyuki Kawashima (Ph.D.) University of Tsukuba Assistant Professor ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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: [HACKERS] Acclerating INSERT/UPDATE using UPS
Simon, Not checkpointing at all is not a good plan, since this will lead to an enormous build up of WAL files and a very long recovery time if the system does fail. I appreciate your detailed comments. Following your comments, I revised the problem. Sigres-0.1.3 does checkpointings. In summary, the features of Sigres-0.1.3 are as follows. 0: 10% faster than conventional PostgreSQL under tmpfs. 1: Checkpointings are continually executed. 2: Sigres mode is in default (the mode can be turned off via postgresql.conf). 3: issue_xlog_sync is called only by bgwriter (continually, via createcheckpoint) 4: The entity of XLogWrite (_XLogWrite in my code) is called by both backends and a bgwriter. For each backend, _XLogWrite is called only via AdvanceXLInsertBuffer. For a bgwriter, _XLogWrite is called via CreateCheckPoint. Please try it if you have interest. http://sourceforge.jp/projects/sigres/ Again, I really appreciate beneficial comments from this community ! Regards, -- Hideyuki -- Hideyuki Kawashima (Ph.D.) University of Tsukuba Assistant Professor ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua D. Drake wrote: From an deployable application perspective, this could be a big deal. We are already starting to see very large traction in the Win32 desktop app arena. There seem to be a few overlapping proposals in terms of reducing various guarantees in the name of performance. As more and more options are added that affect integrity (fsync, full page writes, commit nowait, sigres) it might be nice to outline and compare the approaches, and particularly to describe clearly the failure scenarios and how they are significantly different from one another. One potentially needs to track an increasing number of ways in which items might be set which reduce certain guarantees on data integrity which is unpleasant. If a setting is wrong on a performance knob, no problem, when there are complaints things are slow you can go through and adjust them. The same is not true of data consistency. When the complaint comes it is usually too late to fiddle with knobs. I'm just thinking some caution should be exercised in adding too many of them in the first place. I happen to love COMMIT NOWAIT though, for many, this replaces fsync=off. - August ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua D. Drake wrote: Christopher Browne wrote: A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. You should consider submitting a patch for this against CVS HEAD. And actually, I'd think it a better idea to define a GUC variable and use that to control whether Sigres is active or not. At the more sophisticated end of the spectrum, you might set things up so that it could be activated/deactivated at runtime by a superuser. At the less sophisticated end, it might need to be configured in postgresql.conf... Whatever happen with this? I would like to see more analysis about why Sigres is faster than an in-memory file system. I think the idea was that locking was reduced but I am unclear on why locking is different in the two cases. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
On Mon, 2007-02-26 at 09:44 -0500, Bruce Momjian wrote: Joshua D. Drake wrote: Christopher Browne wrote: A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. You should consider submitting a patch for this against CVS HEAD. And actually, I'd think it a better idea to define a GUC variable and use that to control whether Sigres is active or not. At the more sophisticated end of the spectrum, you might set things up so that it could be activated/deactivated at runtime by a superuser. At the less sophisticated end, it might need to be configured in postgresql.conf... Whatever happen with this? I would like to see more analysis about why Sigres is faster than an in-memory file system. I think the idea was that locking was reduced but I am unclear on why locking is different in the two cases. Reading through the design, I see the following - bgwriter performs XLogWrite, not each backend - WAL fsync is only performed when WAL file fills - no checkpoints are performed until shutdown This is approximately the same performance as fsync=off, with a big boost because we never do checkpoints either (i.e. 10% as stated by the OP). There is a slight gain because of reduced lock contention, but I wouldn't expect that to be major. This comes back to the idea of deferred fsync we've spoken about a few times, to provide MySQL mode performance and reduced robustness guarantees. The idea of having a special backend perform the XLogWrites rather than the individual backends is already a TODO item, even if this implementation is somewhat more extreme in its approach to fsync delay. The actual potential data loss is similar to an archive recovery though, so I do like that touch - i.e. we might lose everything in the current WAL file. IMHO, it shouldn't be the bgwriter that fsyncs WAL because it cannot clean shared_buffers and fsync the WAL concurrently with any effectiveness. Perhaps such infrequent fsyncs mean thats not a problem. Not checkpointing at all is not a good plan, since this will lead to an enormous build up of WAL files and a very long recovery time if the system does fail. Overall, these choices effect the whole system whereas what I think we need is something that can be applied to just certain transactions or tables. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Simon Riggs [EMAIL PROTECTED] writes: Reading through the design, I see the following - bgwriter performs XLogWrite, not each backend - WAL fsync is only performed when WAL file fills - no checkpoints are performed until shutdown Not checkpointing at all is not a good plan, since this will lead to an enormous build up of WAL files and a very long recovery time if the system does fail. Indeed, that seems utterly unacceptable; moreover it's hard to see how you get a noticeable performance win, compared to a sane checkpoint interval of some-fraction-of-an-hour. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Christopher Browne wrote: A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. You should consider submitting a patch for this against CVS HEAD. And actually, I'd think it a better idea to define a GUC variable and use that to control whether Sigres is active or not. At the more sophisticated end of the spectrum, you might set things up so that it could be activated/deactivated at runtime by a superuser. At the less sophisticated end, it might need to be configured in postgresql.conf... Whatever happen with this? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Bruce, Let me answer to your question. Question 1: How much faster is it ? To answer to your question, I measured execution times of INSERT operations on concurrent accesses on dual-core x 2 CPU (each 2.80GHz), In the experiment, each client issues 5000 INSERT operations concurrently. The following shows the average times (seconds) of 5 measurements. #Cli: The number of concurrent clients P-D: PostgreSQL Default (i.e. usual file system) P-T: PostgreSQL tmpfs S-D: Sigres Default (i.e. usual file system) S-T: Sigres tmpfs P-T/S-T Improve ratio of S-T to P-T The result shows S-T is 10% to 18% faster than P-T. Thus my answer to your question is 10% to 18% when concurrency is from1 to 100. #CliP-DP-TS-DS-TP-T/S-T 1 1.72 0.50 0.46 0.45 1.10 2 2.87 0.62 0.58 0.54 1.15 3 3.08 0.89 0.77 0.77 1.15 4 3.14 0.98 0.86 0.84 1.16 5 3.31 1.23 1.09 1.07 1.15 6 3.57 1.44 1.31 1.27 1.14 7 3.91 1.68 1.51 1.48 1.14 8 4.49 1.89 1.71 1.67 1.13 9 4.78 2.21 1.91.92 1.15 10 5.33 2.47 2.22 2.14 1.15 20 11.50 5.66 5.16 4.86 1.16 50 32.96 16.54 14.92 13.97 1.18 100 79.60 43.71 39.55 38.38 1.14 Question 2: Is that worth adding extra code to improve it ? Yes, I think it is worth. It is because in the case of commercial DBMS, only 5% improvement is achieved with version-up. BTW, I and a friend of mine try to design implement a parallel access way to the wal buffer on a shared memory. I think this is promising direction since WALInsertLock is more frequently issued than WALWriteLock, and the number of CPU-cores will increase definitely. -- Hideyuki Bruce Momjian wrote: Hideyuki Kawashima wrote: Bruce, Thanks for your comments, and let me answer to your question. Sigres is *not* significantly faster than just creating a file system on the permanent memory and putting xlog on there. Sigres is slightly faster than the case because each backend does not call XLogWrite while bgWriter does. The question then is how much faster is it, and is that worth adding extra code to improve it. --- -- Hideyuki Bruce Momjian wrote: Tom Lane wrote: Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. I am unsure why Sigres is significantly faster than just creating a file system on the permanent memory and putting xlog on there. ---(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 -- Hideyuki Kawashima (Ph.D), University of Tsukuba, Graduate School of Systems and Information Engineering Assistant Professor, TEL: +81-29-853-5322 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Tom, In my experience, in last April, a BBWC solution did not accelerate PostgreSQL well. The device which I tried was i-ram by gigabyte (http://techreport.com/reviews/2006q1/gigabyte-iram/index.x?pg=1 ). The i-ram showed only a little performance improvement compared to PostgreSQL with fsync to disk. (However, in then case of PostgreSQL fsync=off, the performance improvement was great). Thus I think Sigres is better than BBWC, to the best of my knowledge. However, I do not know other BBWC technologies such as HP smart array E200 controller. (http://h18004.www1.hp.com/products/servers/proliantstorage/arraycontrollers/smartarraye200/index.html) So, I am sorry if I describe wrong conclusion. Best Regards, -- Hideyuki Tom Lane wrote: Gene [EMAIL PROTECTED] writes: I was curious to see how postgres would perform with wal on a tmpfs vs disk here are some numbers I got from pgbench. Let me know if I did something stupid, this is the first time I've used pgbench. The wal on tmpfs method is not significantly faster. This comparison is not very useful because you were using battery-backed write cache, which gives pretty much all the performance improvement that is to be looked for in this area. Try it against a plain vanilla disk drive (that's not lying about write complete) and you'll find the maximum TPS rate is closely related to the disk's rotation rate. At the same time though, the existence of BBWC solutions makes me wonder why we need another. regards, tom lane -- Hideyuki Kawashima (Ph.D), University of Tsukuba, Graduate School of Systems and Information Engineering Assistant Professor, TEL: +81-29-853-5322 ---(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: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua, I revised. Now Sigres can be activated by setting sigres = on in postgresql.conf. You can download the version (0.1.2) from http://sourceforge.jp/projects/sigres . And, I attach the diff between PostgreSQL-8.2.1 and Sigres-0.1.2 to this mail. Thanks for your comments. -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Joshua, I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. The best way is to create a patch against -head and submit that patch with a complete description of why, and what. If you have test cases that show the improvement all the better. I would suggest though if you are going to submit the patch that you take a look at how you could disable/enable the feature within the postgresql.conf via a guc. Sincerely, Joshua D. Drake However, Sigres modifies WAL which is the most important point of DBMS on stability. Although I myself could not find any bugs in Sigres, I am really afraid of it. It a bug exists on Sigres, it puts everyone to huge inconvenience... Therefore, before incorporating Sigres into PostgreSQL, the code must be checked, and the behaviors of Sigres must be checked carefully. Since PostgreSQL is a famous and wide spread software, I strongly want to avoid losing its great reputation. Unfortunately in Japan, I do not know any WAL hackers except for a friend of mine, and he is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am really happy. Best Regards, -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- Hideyuki Kawashima (Ph.D), University of Tsukuba, Graduate School of Systems and Information Engineering Assistant Professor, TEL: +81-29-853-5322 Only in sigres-0.1.2: CHANGELOG Only in postgresql-8.2.1: README Only in sigres-0.1.2: README.pgsql Only in sigres-0.1.2: README.sigres diff -c -r postgresql-8.2.1/src/backend/access/transam/xlog.c sigres-0.1.2/src/backend/access/transam/xlog.c *** postgresql-8.2.1/src/backend/access/transam/xlog.c Fri Dec 1 03:29:11 2006 --- sigres-0.1.2/src/backend/access/transam/xlog.c Fri Feb 16 09:45:05 2007 *** *** 10,15 --- 10,18 * $PostgreSQL: pgsql/src/backend/access/transam/xlog.c,v 1.258 2006/11/30 18:29:11 tgl Exp $ * *- + * + * Extended to Sigres by Hideyuki Kawashima ([EMAIL PROTECTED]) + * */ #include postgres.h *** *** 142,147 --- 145,154 bool XLOG_DEBUG = false; #endif + /* Entities are in globals.c, for SIGRES */ + extern int BgWriterPid; + extern bool enableSigres; + /* * XLOGfileslop is used in the code as the allowed fuzz in the number of * preallocated XLOG segments --- we try to have at least XLOGfiles advance *** *** 474,480 static bool XLogCheckBuffer(XLogRecData *rdata, bool doPageWrites, XLogRecPtr *lsn, BkpBlock *bkpb); static bool AdvanceXLInsertBuffer(bool new_segment); ! static void XLogWrite(XLogwrtRqst WriteRqst, bool flexible, bool xlog_switch); static int XLogFileInit(uint32 log, uint32 seg, bool *use_existent, bool use_lock); static bool InstallXLogFileSegment(uint32 *log, uint32
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Christopher Browne wrote: [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Your idea sounds interesting; there is likely to be a considerable resistance to mechanisms, however, that would be likely to make PostgreSQL less robust. Be aware, also, that in a public forum like this, people are sometimes less gentle than Joshua. The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. I can think of a few places where I don't care about the data if the power is lost - * Web-based session data A lot of web sites have separate session-only databases. If the database goes down, we have to truncate the tables anyways when it comes back up. * Reporting slaves We have replication slaves setup for internal (staff-only) reporting. Often a lot of temp and summary tables as well. If the data is lost, don't care. Its a reporting database. Re-syncing from another slave is no biggie for total data loss. Less a concern given the speed increase of the data it creates as well as data coming in from the master. * Front-end cache slaves Same type of situation as the reporting slaves. Basic front-end cache that replicates data to take load off the master. The slaves still have to do all the same insert/updates, but this means they'll spend less time in locks. They crash, point the apps to the master or another slave while you fix it. Weslee ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Tom Lane wrote: Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. I am unsure why Sigres is significantly faster than just creating a file system on the permanent memory and putting xlog on there. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
I was curious to see how postgres would perform with wal on a tmpfs vs disk here are some numbers I got from pgbench. Let me know if I did something stupid, this is the first time I've used pgbench. The wal on tmpfs method is not significantly faster. [[ WAL ON TMPFS ]] pgbench -i -s 10 -U postgres -d benchmark ... pgbench -Upostgres -s 10 -c 10 -t 1 benchmark starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 10 number of transactions per client: 1 number of transactions actually processed: 10/10 tps = 5817.693724 (including connections establishing) tps = 5825.646441 (excluding connections establishing) [[ WAL ON EXT2 14 U320 DRIVE RAID10 WITH BBU (same as data) ]] pgbench -Upostgres -s 10 -c 10 -t 1 benchmark starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 10 number of transactions per client: 1 number of transactions actually processed: 10/10 tps = 5653.187997 (including connections establishing) tps = 5660.554438 (excluding connections establishing) pgbench -Upostgres -s 100 -c 10 -t 1 benchmark starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 10 number of transactions per client: 1 number of transactions actually processed: 10/10 tps = 5536.019864 (including connections establishing) tps = 5543.834350 (excluding connections establishing)
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
On 2/12/07, Gene [EMAIL PROTECTED] wrote: I was curious to see how postgres would perform with wal on a tmpfs vs disk here are some numbers I got from pgbench. Let me know if I did something stupid, this is the first time I've used pgbench. The wal on tmpfs method is not significantly faster. tmpfs uses virtual memory, so it is likely hitting the disk. I would try and repeat your cases with ramfs, which uses real memory. Testing shows that just putting pg_xlog on ramfs yields a 30% boost in performance over disk with a stock configuration. So there is room for improvement, I am sure.
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Gene [EMAIL PROTECTED] writes: I was curious to see how postgres would perform with wal on a tmpfs vs disk here are some numbers I got from pgbench. Let me know if I did something stupid, this is the first time I've used pgbench. The wal on tmpfs method is not significantly faster. This comparison is not very useful because you were using battery-backed write cache, which gives pretty much all the performance improvement that is to be looked for in this area. Try it against a plain vanilla disk drive (that's not lying about write complete) and you'll find the maximum TPS rate is closely related to the disk's rotation rate. At the same time though, the existence of BBWC solutions makes me wonder why we need another. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Bruce, Thanks for your comments, and let me answer to your question. Sigres is *not* significantly faster than just creating a file system on the permanent memory and putting xlog on there. Sigres is slightly faster than the case because each backend does not call XLogWrite while bgWriter does. -- Hideyuki Bruce Momjian wrote: Tom Lane wrote: Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. I am unsure why Sigres is significantly faster than just creating a file system on the permanent memory and putting xlog on there. ---(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: [HACKERS] Acclerating INSERT/UPDATE using UPS
Hideyuki Kawashima wrote: Bruce, Thanks for your comments, and let me answer to your question. Sigres is *not* significantly faster than just creating a file system on the permanent memory and putting xlog on there. Sigres is slightly faster than the case because each backend does not call XLogWrite while bgWriter does. The question then is how much faster is it, and is that worth adding extra code to improve it. --- -- Hideyuki Bruce Momjian wrote: Tom Lane wrote: Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. I am unsure why Sigres is significantly faster than just creating a file system on the permanent memory and putting xlog on there. ---(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 -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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: [HACKERS] Acclerating INSERT/UPDATE using UPS
Gene, I think tmpfs/ramfs method would increase the performance of PostgreSQL when workload is write-intensive. Does pgbench issue write intensive queries ? Since I do not know the detailed queries issued in pgbench, I am happy if you teach me. Regards, -- Hideyuki Gene wrote: I was curious to see how postgres would perform with wal on a tmpfs vs disk here are some numbers I got from pgbench. Let me know if I did something stupid, this is the first time I've used pgbench. The wal on tmpfs method is not significantly faster. [[ WAL ON TMPFS ]] pgbench -i -s 10 -U postgres -d benchmark ... pgbench -Upostgres -s 10 -c 10 -t 1 benchmark starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 10 number of transactions per client: 1 number of transactions actually processed: 10/10 tps = 5817.693724 (including connections establishing) tps = 5825.646441 (excluding connections establishing) [[ WAL ON EXT2 14 U320 DRIVE RAID10 WITH BBU (same as data) ]] pgbench -Upostgres -s 10 -c 10 -t 1 benchmark starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 10 number of transactions per client: 1 number of transactions actually processed: 10/10 tps = 5653.187997 (including connections establishing) tps = 5660.554438 (excluding connections establishing) pgbench -Upostgres -s 100 -c 10 -t 1 benchmark starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 10 number of transactions per client: 1 number of transactions actually processed: 10/10 tps = 5536.019864 (including connections establishing) tps = 5543.834350 (excluding connections establishing) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Christopher, Thanks for your comments and I am sorry for my bad response because of my business. In the first place, criticisms are welcome since without criticisms and discussions, I cannot improve my idea. Christopher, thanks to your comments, I understood why this community does not incline to accept Sigres approach. Reason 1: Sigres method may make PostgreSQL less robust. Reason 2: There is a not-inconsiderable risk that people will fail to read warnings Let me write my opinions for the above. Opinion for Reason 1: Right. Apparently Sigres method makes PostgreSQL less robust. Thus I propose to use Sigres method as an option like fsync=off. In my understanding, fsync=off option is ambiguous. I think unfortunately it is unclear when exactly the option should be used. Even if one uses UPS, the option is dangerous because the final issue_xlog_sync called by XLogWrite called by CreateCheckPoint called by ShutdownXLOG called by bgWriter process at postgres shutdown is not executed. (Sorry for the exact but wired descriptions). Without using UPS, the option never be used. On the other hand, *IF* UPS is reliable, Sigres assures synchronization of wal records to a disk because Sigres executes issue_xlog_fsync at each XLogFileClose() and at shutdown. That is, the question would be expressed as Can an administrator have a reliable UPS system ? If an administrator is confident on his/her UPS system, the administrator may select Sigres method. In summary, fsync=off is ambiguous option, and Sigres can be used *IF* an administrator is confident on his/her UPS system. Opinion for Reason 2: Right. I think it is easy to avoid the risk. Sigres should not be used in default such as fsync=off. If my opinion lacks in common sense, I am sorry. Regards, -- Hideyuki Christopher Browne wrote: [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Your idea sounds interesting; there is likely to be a considerable resistance to mechanisms, however, that would be likely to make PostgreSQL less robust. Be aware, also, that in a public forum like this, people are sometimes less gentle than Joshua. The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. But there is a not-inconsiderable risk that people will fail to read warnings, deploy databases in a way that leaves them exposed to total loss, and then lay blame at this community's feet. I'm sure you can understand why the community might resist that... ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Christopher, Thanks for your advices ! OK, I will submit a patch as soon as possible. In the patch, Sigres will be activated by postgresql.conf. Best Regards, -- Hideyuki A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. You should consider submitting a patch for this against CVS HEAD. And actually, I'd think it a better idea to define a GUC variable and use that to control whether Sigres is active or not. At the more sophisticated end of the spectrum, you might set things up so that it could be activated/deactivated at runtime by a superuser. At the less sophisticated end, it might need to be configured in postgresql.conf... -- output = (cbbrowne @ acm.org) http://linuxfinances.info/info/ If you've done six impossible things this morning, why not round it off with breakfast at Milliways, the Restaurant at the End of the Universe? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Andrew, Your experience is really informative. Thanks for giving me such a really precious information. Since I am a researcher, I rarely faces on real troubles. Andrew, I agree with you. UPS is not reliable all the time. On the other hand, however, disks or raids are also not reliable all the time. Moreover, on the earth, there is NO device which completely assures durability of the ACID transaction property. Even if disks and power supplies are perfect, the system would be destroyed by an earthquake (thus, disaster recovery techniques are recently popular). Thus I think the problem is not in a device. It is in the management of system administrators. (However, this is just my opinion and it is not objective one required by large questionnaire). On the other hand, speed is required. My first motivation of this work is a friend of mine who is working at a financial firm. He strongly required performance of UPDATE/INSERT on the database servers in his company (the database was a major commercial product). I think in such a rich environment, strong power management facilities may be expected though this is just my one thought). In summary, I think the problem lies in system management rather than device itself. Regards, -- Hideyuki J. Andrew Rogers wrote: On Feb 10, 2007, at 9:33 PM, Christopher Browne wrote: The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. Ask me about the time a year ago that I had a 24x7 database, with two power supplies connected to two UPSes on independent mains circuits, dropped dead because one UPS was overloaded (more than one server connected to it, apparently too much) and the other UPS was simply dead (undetected zombie UPS), when a catastrophic power failure killed both of the generator backed mains circuits. I wasn't pleased, but it happened nonetheless. A UPS is not a 100% guarantee of anything. They fail more often than they should. No amount of paranoia guarantees uptime. That said, I see plenty of use for loosening restrictions on databases where the contents do not matter and a little loss is acceptable. Cheers, J. Andrew Rogers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Tom, Thanks for your comments ! Let me answer to your beneficial mail. Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; The motivation of my work is accelerating INSERT/UPDATE without losing data integrity guarantees. In the first place, I thought *IF* a battery supplied memory can be considered as a persistent device, Sigres method works. However, my assumption seems not to be accepted unfortunately. Without sending my first email, I could not know your informative comments. Thank you ! there are plenty of ways to do that already :-(. Could you please let me know or give me some pointers for the important information ? For ubiquitous computing researchers or sensor data processing researchers, that is a great concern. Since INSERT/UPDATE is too heavy, several universities have developed stream processing engines (SPE) which do not store any data. Also I have already published more than 4 international conference papers in this topic. Moreover, I have been developing a brand-new DBMS from scratch in my research project. I am all ears ! Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. In the first place, I thought Sigres method works *IF* UPSs are reliable. This was my assumption. However, I am in the process of understanding that unfortunately UPSs may not be reliable usually through this discussion :-( On the other hand, however, there not negligible people who hopes to dramatically accelerate INSERT/UPDATE such as financial companies or sensor data processing research groups such as our team. For example, In our team, the default postgresql is too slow and thus sensor data insertions delays over an hour. The purpose of our team is detecting real-world events in real-time, but it does not work because of bad performance. In such a case, Sigres is useful if I can believe UPS. . I am not confident whether my explanation is clear or not. If unclear, sorry. Best Regards, -- Hideyuki Tom Lane wrote: Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Tom and all, I apologize destroying the thread information with this topic. Unfortunately my basic smtp server does not work now and I am writing all the responses via gmail tonight... Tom, Thanks for teaching me about the development assumption of PostgreSQL. The assumption and my direction are different since the assumption considers only disk drive as persistent device while my assumption is battery-supplied memory. Honestly speaking, I am not sure whether there is a person who accepts my assumption (i.e. battery-supplied memory as persistent device) or not. And I am not sure whether my approach can be integrated into PostgreSQL since some accept and some reject. So, anyway I will write a patch and submit. Then I leave to this community the decision of accept/reject. If someone has interest on Sigres, please download it from http://sourceforge.jp/projects/sigres/ and try it. I will continue to accelerate Sigres more anyway under my assumption. Since I believe time always wins in transaction processing as Jim Gray told last June in ACM SIGMOD 2006 keynote talk, I wish UPS will be reliable or nice non volatile memories such as MRAM will appear in the near future. Finally, this discussion was really beneficial for me. I would like to say thank you for everyone who gave me great information. Thank you all ! Best Regards, -- Hideyuki Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. Absolutely agreed there. Just to be clear: I believe our current assumptions can be stated as Postgres will not lose data if the kernel and disk drive do not lose data that they have acknowledged as being successfully fsync'd. This is independent of any questions about Postgres bugs or measures that we take to limit the impact of our bugs --- it's about what our extent of responsibility is. I think that Hideyuki-san is proposing a different contract for data integrity, and I want to understand what that contract is and why someone would want it. regards, tom lane ---(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
[HACKERS] Acclerating INSERT/UPDATE using UPS
Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(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: [HACKERS] Acclerating INSERT/UPDATE using UPS
Hideyuki Kawashima wrote: Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). Interesting and what happens when the UPS fails? My main concern is that one of the purposes of PostgreSQL is data integrity. I am all for every performance enhancement we can achieve, that does *not* sacrifice that. Sincerely, Joshua D. Drake The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua, Thanks for your comments ! Right. As you pointed out, Sigres cannot recover if UPS fails. Therefore, I think currently Sigres cannot be used for mission critical applications. Sigres keeps data integrity *only when* UPS works. On the other hand, some users such as researchers of sensor networks would like store and analyze 10^6 Hz sensor data insertions in real-time, and obviously research usages are not mission critical. Thus Sigres may be accepted for researchers, but I have no prospect now since I have just started to distributing Sigres for research institutes in Japan. BTW, Joshua, could you please let me know or give me any pointers for the reason why fsync=off option exists on PostgreSQL although PostgreSQL developers does not allow sacrificing data integrity ? If the reason is famous and clear in the community, I am sorry for bothering you. -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). Interesting and what happens when the UPS fails? My main concern is that one of the purposes of PostgreSQL is data integrity. I am all for every performance enhancement we can achieve, that does *not* sacrifice that. Sincerely, Joshua D. Drake The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
BTW, Joshua, could you please let me know or give me any pointers for the reason why fsync=off option exists on PostgreSQL although PostgreSQL A couple of reasons that I can think of. One would be data loads or restoring from backup. Another would be on data that you can afford to throw away. developers does not allow sacrificing data integrity ? If the reason is famous and clear in the community, I am sorry for bothering you. No bother at all! We invite all good ideas and I am glad to see more from our eastern community participate. Another option you might want to look at to further give yourself a boost in performance is full_page_writes. Joshua D. Drake -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). Interesting and what happens when the UPS fails? My main concern is that one of the purposes of PostgreSQL is data integrity. I am all for every performance enhancement we can achieve, that does *not* sacrifice that. Sincerely, Joshua D. Drake The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Major topic. The full_page_writes option is already ignored. In Sigres, bgWriter does not call CreatCheckPoint periodically. Sigres calls CreateCheckPoint only when bgWriter calls ShutdownXLOG. Thus also from this point, durable UPS is required for Sigres. The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. Regards, -- Hideyuki Joshua D. Drake wrote: BTW, Joshua, could you please let me know or give me any pointers for the reason why fsync=off option exists on PostgreSQL although PostgreSQL A couple of reasons that I can think of. One would be data loads or restoring from backup. Another would be on data that you can afford to throw away. developers does not allow sacrificing data integrity ? If the reason is famous and clear in the community, I am sorry for bothering you. No bother at all! We invite all good ideas and I am glad to see more from our eastern community participate. Another option you might want to look at to further give yourself a boost in performance is full_page_writes. Joshua D. Drake -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Hello PostgreSQL Hackers, I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is Sigres, and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ . In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off). Interesting and what happens when the UPS fails? My main concern is that one of the purposes of PostgreSQL is data integrity. I am all for every performance enhancement we can achieve, that does *not* sacrifice that. Sincerely, Joshua D. Drake The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.) Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ? Best Regards, -- Hideyuki Kawashima Assistant Professor, University of Tsukuba ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend -- 筑波大学大学院システム情報工学研究科 講師 川島英之 〒305-8573 つくば市天王台1-1-1 TEL: 029-853-5322 #2月より所属が変わりました ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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: [HACKERS] Acclerating INSERT/UPDATE using UPS
I think it would be great to have this kind of functionality built into postgres (optional and disabled by default of course) I use postgres mainly for its querying and concurrency features (a 10x increase in insert/update speed would be phenomenal) I know most people need 100% data integrity but as Hideyuki pointed out we all don't need 100%. On our project, when the power is out, we aren't receiving data anyways... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? On 2/10/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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 -- Gene Hart cell: 443-604-2679
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua, I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. However, Sigres modifies WAL which is the most important point of DBMS on stability. Although I myself could not find any bugs in Sigres, I am really afraid of it. It a bug exists on Sigres, it puts everyone to huge inconvenience... Therefore, before incorporating Sigres into PostgreSQL, the code must be checked, and the behaviors of Sigres must be checked carefully. Since PostgreSQL is a famous and wide spread software, I strongly want to avoid losing its great reputation. Unfortunately in Japan, I do not know any WAL hackers except for a friend of mine, and he is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am really happy. Best Regards, -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- 筑波大学大学院システム情報工学研究科 講師 川島英之 〒305-8573 つくば市天王台1-1-1 TEL: 029-853-5322 #2月より所属が変わりました ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Hideyuki Kawashima wrote: Joshua, I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. The best way is to create a patch against -head and submit that patch with a complete description of why, and what. If you have test cases that show the improvement all the better. I would suggest though if you are going to submit the patch that you take a look at how you could disable/enable the feature within the postgresql.conf via a guc. Sincerely, Joshua D. Drake However, Sigres modifies WAL which is the most important point of DBMS on stability. Although I myself could not find any bugs in Sigres, I am really afraid of it. It a bug exists on Sigres, it puts everyone to huge inconvenience... Therefore, before incorporating Sigres into PostgreSQL, the code must be checked, and the behaviors of Sigres must be checked carefully. Since PostgreSQL is a famous and wide spread software, I strongly want to avoid losing its great reputation. Unfortunately in Japan, I do not know any WAL hackers except for a friend of mine, and he is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am really happy. Best Regards, -- Hideyuki Joshua D. Drake wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We (www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
[EMAIL PROTECTED] (Hideyuki Kawashima) wrote: Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Your idea sounds interesting; there is likely to be a considerable resistance to mechanisms, however, that would be likely to make PostgreSQL less robust. Be aware, also, that in a public forum like this, people are sometimes less gentle than Joshua. The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. But there is a not-inconsiderable risk that people will fail to read warnings, deploy databases in a way that leaves them exposed to total loss, and then lay blame at this community's feet. I'm sure you can understand why the community might resist that... -- output = reverse(moc.liamg @ enworbbc) http://linuxdatabases.info/info/internet.html Babbage's Rule: No man's cipher is worth looking at unless the inventor has himself solved a very difficult cipher (The Codebreakers by Kahn, 2nd ed, pg 765) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: I appreciate your great suggestion! It is great honor for me if Sigres will be merged to PostgreSQL. Since the changes of Sigres from PostgreSQL-8.2.1 are not many, and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif, incorporating Sigres into PostgreSQL would be easy. You should consider submitting a patch for this against CVS HEAD. And actually, I'd think it a better idea to define a GUC variable and use that to control whether Sigres is active or not. At the more sophisticated end of the spectrum, you might set things up so that it could be activated/deactivated at runtime by a superuser. At the less sophisticated end, it might need to be configured in postgresql.conf... -- output = (cbbrowne @ acm.org) http://linuxfinances.info/info/ If you've done six impossible things this morning, why not round it off with breakfast at Milliways, the Restaurant at the End of the Universe? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Gene, Thanks for your comments ! On our project, when the power is out, we aren't receiving data anyways... just my two cents. I am sorry, but I do not know how I can appropriately answer since I do not understand what you would like to ask me in this sentence. I understand that you have a project with UPS, and when the power failure occurs on UPS, suddenly your system cannot get data. Is my understanding right ? If so, then I agree with you and the reliability and durability of UPS is mandatory for your system. on a side note, would putting the wal on a tmpfs partition give you something similar? There are two differences between Sigres method and tmpfs with UPS method. 1: XLogWrite Even if you use tmpfs, your system executes XLogWrite which includes write(). Since write() is heavy system call, Sigres would be slightly faster than tmpfs method. 2: XLogWriteLock Even if you use tmpfs, your system lock/release XLogWriteLock while Sigres ignores. Although the frequency of XLogWriteLock accesses is lower than XLogInsertLock, ignoring XLogWriteLock improves the performance especially in many core environment. Best Regards, -- Hideyuki Gene wrote: I think it would be great to have this kind of functionality built into postgres (optional and disabled by default of course) I use postgres mainly for its querying and concurrency features (a 10x increase in insert/update speed would be phenomenal) I know most people need 100% data integrity but as Hideyuki pointed out we all don't need 100%. On our project, when the power is out, we aren't receiving data anyways... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? On 2/10/07, *Joshua D. Drake* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hideyuki Kawashima wrote: Joshua, :) The reason why I made the Sigres is, the advances of recent non volatile memories. Just now we do not usually use non volatile memories. But in the near future, situation would change. I think if a non volatile memories can be considered as a persistence device, PostgreSQL WAL mechanism should be modified. However, I do not use such devices usually. Thus I made Sigres which requires UPS. This is actually very interesting. We ( www.commandprompt.com http://www.commandprompt.com) have had several customers ask us how we can make PostgreSQL more reasonable within a flash environment. I agree with you that in the future you will see many such databases including PostgreSQL living on these devices. Tom? What do you think? Is there some room for movement here within the postgresql.conf to make something like sigres usable within PostgreSQL proper? Currently I have just ignored XLogWrite and WALWriteLock, but a friend of mine (a Japanese great hacker of PostgreSQL) has more idea to improve WAL if a battery supplied memory can be considered as a persistent device. We are coming up very quickly on a feature freeze for the next version of PostgreSQL. If... we can has something out quickly enough and in a thought out fashion, the hackers may be willing to accept a patch for 8.3.. If not there is always 8.4.. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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 -- Gene Hart cell: 443-604-2679 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
On Feb 10, 2007, at 9:33 PM, Christopher Browne wrote: The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. Ask me about the time a year ago that I had a 24x7 database, with two power supplies connected to two UPSes on independent mains circuits, dropped dead because one UPS was overloaded (more than one server connected to it, apparently too much) and the other UPS was simply dead (undetected zombie UPS), when a catastrophic power failure killed both of the generator backed mains circuits. I wasn't pleased, but it happened nonetheless. A UPS is not a 100% guarantee of anything. They fail more often than they should. No amount of paranoia guarantees uptime. That said, I see plenty of use for loosening restrictions on databases where the contents do not matter and a little loss is acceptable. Cheers, J. Andrew Rogers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Christopher Browne wrote: [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Your idea sounds interesting; there is likely to be a considerable resistance to mechanisms, however, that would be likely to make PostgreSQL less robust. Be aware, also, that in a public forum like this, people are sometimes less gentle than Joshua. The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. Correct, that is certainly a problem. However, I think the interesting opportunity here is in devices that don't typically loose power. A PDA for example. The PostgreSQL footprint is actually quite small, and PDAs are getting larger and larger in capacity. Heck, they even have 32GB SD now. In the near future I believe we can expect to see always on, mini laptops as well. From an deployable application perspective, this could be a big deal. We are already starting to see very large traction in the Win32 desktop app arena. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. But there is a not-inconsiderable risk that people will fail to read warnings, deploy databases in a way that leaves them exposed to total loss, and then lay blame at this community's feet. I'm sure you can understand why the community might resist that... I certainly can, but a feature left off by default and documented thoroughly can mitigate a lot of that. Heck if we really wanted to we could even make it a custom build; --with-lazy-wal ;) Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Tom Lane wrote: Gene [EMAIL PROTECTED] writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Under a Linux or FreeBSD environment sure... but what about where a good portion of the memory *is* flash? We have a customer right now where they have a device that has 64 megs of ram and 512 megs of flash. The system itself considers it total ram. They run over the 64 megs almost on boot. Allowing postgresql to be less aggressive on writes would help them quite a bit. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. Absolutely agreed there. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. Absolutely agreed there. Just to be clear: I believe our current assumptions can be stated as Postgres will not lose data if the kernel and disk drive do not lose data that they have acknowledged as being successfully fsync'd. This is independent of any questions about Postgres bugs or measures that we take to limit the impact of our bugs --- it's about what our extent of responsibility is. I think that Hideyuki-san is proposing a different contract for data integrity, and I want to understand what that contract is and why someone would want it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings