[PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Hi,. We are new to Postgresql. I am appreciated if the following question can be answered. Our application has a strict speed requirement for DB operation. Our tests show that it takes about 10secs for the operation when setting fsync off, but takes about 70 seconds when setting fsync ON (with other WAL related parametered tuned). We have to looking at setting fsync OFF option for performance reason, our questions are a) if we set fsync OFF and anything (very low chance though) like OS crash, loss of power, or hardware fault happened, can postgresql rolls back to the state that the last checkpoint was done ( but all the operations after that is lost) b) Does this roll back to last checkpoint can ensure the database back to consistent state? c) What is worst scenarios if setting fsync OFF in term of database safety. We try to avoid to restore the database from nightly backup. We view our application is not that data loss critical, say loss of five minutes of data and operation occasionally, but the database integrity and consistency must be kept. Can we set fsync OFF for the performance benefit, have the risk of only 5 minutes data loss or much worse? Thanks in advance. Regards, Guoping ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync
On Thu, 2006-04-27 at 16:31 +1000, Guoping Zhang wrote: We have to looking at setting fsync OFF option for performance reason, our questions are a) if we set fsync OFF and anything (very low chance though) like OS crash, loss of power, or hardware fault happened, can postgresql rolls back to the state that the last checkpoint was done ( but all the operations after that is lost) There is no rollback, only a rollforward from the checkpoint. b) Does this roll back to last checkpoint can ensure the database back to consistent state? Therefore no consistent state guaranteed if some WAL is missing c) What is worst scenarios if setting fsync OFF in term of database safety. We try to avoid to restore the database from nightly backup. Losing some DDL changes, probably. You'd need to be wary of things like ANALYZE, VACUUM etc, since these make catalog changes also. We view our application is not that data loss critical, say loss of five minutes of data and operation occasionally, but the database integrity and consistency must be kept. Can we set fsync OFF for the performance benefit, have the risk of only 5 minutes data loss or much worse? Thats up to you. fsync can be turned on and off, so you can make critical changes with fsync on, then continue with fsync off. The risk and the decision, are yours. You are warned. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com/ ---(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: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Get a SCSI controller with a battery backed cache, and mount the disks with data=writeback (if you use ext3). If you loose power in the middle of a transaction, the battery will ensure that the write operation still completes. With asynch writing setup like this, fsync operations will return almost immidiately giving you performance close to that of running with fsync off. Regards, Mikael -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Guoping Zhang Sent: den 27 april 2006 08:31 To: pgsql-performance@postgresql.org Cc: Guoping Zhang (E-mail) Subject: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql Hi,. We are new to Postgresql. I am appreciated if the following question can be answered. Our application has a strict speed requirement for DB operation. Our tests show that it takes about 10secs for the operation when setting fsync off, but takes about 70 seconds when setting fsync ON (with other WAL related parametered tuned). We have to looking at setting fsync OFF option for performance reason, our questions are a) if we set fsync OFF and anything (very low chance though) like OS crash, loss of power, or hardware fault happened, can postgresql rolls back to the state that the last checkpoint was done ( but all the operations after that is lost) b) Does this roll back to last checkpoint can ensure the database back to consistent state? c) What is worst scenarios if setting fsync OFF in term of database safety. We try to avoid to restore the database from nightly backup. We view our application is not that data loss critical, say loss of five minutes of data and operation occasionally, but the database integrity and consistency must be kept. Can we set fsync OFF for the performance benefit, have the risk of only 5 minutes data loss or much worse? Thanks in advance. Regards, Guoping ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Guoping, On 4/27/06, Guoping Zhang [EMAIL PROTECTED] wrote: We have to looking at setting fsync OFF option for performance reason, Did you try the other wal sync methods (fdatasync in particular)? I saw a few posts lately explaining how changing sync method can affect performances in specific cases. -- Guillaume ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Introducing a new linux readahead framework
On Wed, Apr 26, 2006 at 10:43:48AM -0400, Michael Stone wrote: patch a 512k blocksize would get ~100MB/s. I'm now watching to see how it does over a couple of days on real-world workloads. I've got one DB where the VACUUM ANALYZE generally takes 11M-12M ms; with the patch the job took 1.7M ms. Another VACUUM that normally takes between 300k-500k ms took 150k. Definately a promising addition. Mike Stone ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Firebird 1.5.3 X Postgresql 8.1.3 (linux [PERFORM] Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)]
Hello, Many thanks for your suggestions. I will try them. The last two queries almost did not use disk, but used 100% cpu. The differences of performance are big. Firebird has something similiar to EXPLAIN. Please look below. Is there something really wrong with the postgresql configuration (at my previous msg) that is causing this poor performance at these 2 queries? I tweaked until almost no disk was used, but now it is using 100% cpu and took too much time to complete. Thanks. Andre Felipe Machado http://www.techforce.com.br SQL set plan on; SQL set stats on; SQL update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0 and1 in (select CAD3.ID_CADASTRO from CADASTRO CAD3 where CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end; PLAN (CAD3 INDEX (PK_CADASTRO_DESC)) PLAN (DEC2 NATURAL) PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN18)) PLAN (CADASTRO NATURAL) Current memory = 911072 Delta memory = 355620 Max memory = 911072 Elapsed time= 1.89 sec Cpu = 0.00 sec Buffers = 2048 Reads = 1210 Writes = 14 Fetches = 310384 SQL SQL update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0 and exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end; PLAN (CAD3 INDEX (RDB$FOREIGN18)) PLAN (DEC2 NATURAL) PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN18)) PLAN (CADASTRO NATURAL) Current memory = 938968 Delta memory = 8756 Max memory = 15418996 Elapsed time= 1.09 sec Cpu = 0.00 sec Buffers = 2048 Reads = 0 Writes = 0 Fetches = 301007 SQL ---(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: [PERFORM] Running on an NFS Mounted Directory
I am looking for the best solution to have a large amount of disk storage attached to my PostgreSQL 8.1 server. I was thinking of having a san or nas attached device be mounted by the pg server over nfs, hence the question about nfs performance. What other options/protocols are there to get high performance and data integrity while having the benefit of not having the physical storage attached to the db server? On 4/27/06 12:55 AM, Jim C. Nasby [EMAIL PROTECTED] wrote: On Wed, Apr 26, 2006 at 07:35:42PM -0700, Steve Wampler wrote: On Wed, Apr 26, 2006 at 10:06:58PM -0400, Ketema Harris wrote: I was wondering if there were any performance issues with having a data directory that was an nfs mounted drive? Say like a SAN or NAS device? Has anyone done this before? My understanding is that NFS is pretty poor in performance in general, so I would expect it to be particularly bad for a DB. You might run some (non-DB) performance tests to get a feel for how bad it might me. (Someone once told me that NFS topped out at around 12MB/s, but I don't know if that's really true [they were trying to sell a competitive networked filesystem]). In any event, you're at least limited by ethernet speeds, if not more. More importantly, the latency involved will kill commit performance. If it doesn't then it's likely that fsync isn't being obeyed, which means 0 data integrity. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Running on an NFS Mounted Directory
On Thu, Apr 27, 2006 at 08:38:55AM -0400, Ketema Harris wrote: I am looking for the best solution to have a large amount of disk storage attached to my PostgreSQL 8.1 server. What other options/protocols are there to get high performance and data integrity while having the benefit of not having the physical storage attached to the db server? These are two distinct requirements. Are both really requirements or is one nice to have? The best solution for a large amount of disk storage isn't not having the physical storage attached to the db server. If you use non-local storage it will be slower and more expensive, quite likely by a large margin. There may be other advantages to doing so, but you haven't mentioned any of those as requirements. Mike Stone ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Running on an NFS Mounted Directory
OK. My thought process was that having non local storage as say a big raid 5 san ( I am talking 5 TB with expansion capability up to 10 ) would allow me to have redundancy, expandability, and hopefully still retain decent performance from the db. I also would hopefully then not have to do periodic backups from the db server to some other type of storage. Is this not a good idea? How bad of a performance hit are we talking about? Also, in regards to the commit data integrity, as far as the db is concerned once the data is sent to the san or nas isn't it written? The storage may have that write in cache, but from my reading and understanding of how these various storage devices work that is how they keep up performance. I would expect my bottleneck if any to be the actual Ethernet transfer to the storage, and I am going to try and compensate for that with a full gigabit backbone. On 4/27/06 8:44 AM, Michael Stone [EMAIL PROTECTED] wrote: On Thu, Apr 27, 2006 at 08:38:55AM -0400, Ketema Harris wrote: I am looking for the best solution to have a large amount of disk storage attached to my PostgreSQL 8.1 server. What other options/protocols are there to get high performance and data integrity while having the benefit of not having the physical storage attached to the db server? These are two distinct requirements. Are both really requirements or is one nice to have? The best solution for a large amount of disk storage isn't not having the physical storage attached to the db server. If you use non-local storage it will be slower and more expensive, quite likely by a large margin. There may be other advantages to doing so, but you haven't mentioned any of those as requirements. Mike Stone ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Running on an NFS Mounted Directory
On Thu, Apr 27, 2006 at 08:57:51 -0400, Ketema Harris [EMAIL PROTECTED] wrote: performance from the db. I also would hopefully then not have to do periodic backups from the db server to some other type of storage. Is this not a good idea? How bad of a performance hit are we talking about? Also, You always need to do backups if you care about your data. What if someone accidental deletes a lot of data? What if someone blows up your data center (or there is a flood)? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Running on an NFS Mounted Directory
Yes, your right, I meant not have to do the backups from the db server itself. I can do that within the storage device now, by allocating space for it, and letting the device copy the data files on some periodic basis. On 4/27/06 9:05 AM, Bruno Wolff III [EMAIL PROTECTED] wrote: On Thu, Apr 27, 2006 at 08:57:51 -0400, Ketema Harris [EMAIL PROTECTED] wrote: performance from the db. I also would hopefully then not have to do periodic backups from the db server to some other type of storage. Is this not a good idea? How bad of a performance hit are we talking about? Also, You always need to do backups if you care about your data. What if someone accidental deletes a lot of data? What if someone blows up your data center (or there is a flood)? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Running on an NFS Mounted Directory
On Thu, Apr 27, 2006 at 08:57:51AM -0400, Ketema Harris wrote: OK. My thought process was that having non local storage as say a big raid 5 san ( I am talking 5 TB with expansion capability up to 10 ) would allow me to have redundancy, expandability, and hopefully still retain decent performance from the db. I also would hopefully then not have to do periodic backups from the db server to some other type of storage. Is this not a good idea? How bad of a performance hit are we talking about? Also, in regards to the commit data integrity, as far as the db is concerned once the data is sent to the san or nas isn't it written? The storage may have that write in cache, but from my reading and understanding of how these various storage devices work that is how they keep up performance. I would expect my bottleneck if any to be the actual Ethernet transfer to the storage, and I am going to try and compensate for that with a full gigabit backbone. Well, if you have to have both the best performance and remote attach storage, I think you'll find that a fibre-channel SAN is still the king of the hill. 4Gb FC switches are common now, though finding a 4Gb HBA for your computer might be a trick. 2Gb HBAs are everywhere in FC land. That's a premium price solution, however, and I don't know anything about how well PG would perform with a FC SAN. We use our SAN for bulk science data and leave the PGDB on a separate machine with local disk. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(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: [PERFORM] Running on an NFS Mounted Directory
On Thu, Apr 27, 2006 at 08:57:51AM -0400, Ketema Harris wrote: OK. My thought process was that having non local storage as say a big raid 5 san ( I am talking 5 TB with expansion capability up to 10 ) That's two disk trays for a cheap slow array. (Versus a more expensive solution with more spindles and better seek performance.) would allow me to have redundancy, expandability, and hopefully still retain decent performance from the db. I also would hopefully then not have to do periodic backups from the db server to some other type of storage. No, backups are completely unrelated to your storage type; you need them either way. On a SAN you can use a SAN backup solution to back multiple systems up with a single backup unit without involving the host CPUs. This is fairly useless if you aren't amortizing the cost over a large environment. Is this not a good idea? It really depends on what you're hoping to get. As described, it's not clear. (I don't know what you mean by redundancy, expandability or decent performance.) How bad of a performance hit are we talking about? Way too many factors for an easy answer. Consider the case of NAS vs SCSI direct attach storage. You're probably in that case comparing a single 125MB/s (peak) gigabit ethernet channel to (potentially several) 320MB/s (peak) SCSI channels. With a high-end NAS you might get 120MB/s off that GBE. With a (more realistic) mid-range unit you're more likely to get 40-60MB/s. Getting 200MB/s off the SCSI channel isn't a stretch, and you can fairly easily stripe across multiple SCSI channels. (You can also bond multiple GBEs, but then your cost complexity start going way up, and you're never going to scale as well.) If you have an environment where you're doing a lot of sequential scans it isn't even a contest. You can also substitute SATA for SCSI, etc. For a FC SAN the peformance numbers are a lot better, but the costs complexity are a lot higher. An iSCSI SAN is somewhere in the middle. Also, in regards to the commit data integrity, as far as the db is concerned once the data is sent to the san or nas isn't it written? The storage may have that write in cache, but from my reading and understanding of how these various storage devices work that is how they keep up performance. Depends on the configuration, but yes, most should be able to report back a write once the data is in a non-volatile cache. You can do the same with a direct-attached array and eliminate the latency inherent in accessing the remote storage. I would expect my bottleneck if any to be the actual Ethernet transfer to the storage, and I am going to try and compensate for that with a full gigabit backbone. see above. The advantages of a NAS or SAN are in things you haven't really touched on. Is the filesystem going to be accessed by several systems? Do you need the ability to do snapshots? (You may be able to do this with direct-attach also, but doing it on a remote storage device tends to be simpler.) Do you want to share one big, expensive, reliable unit between multiple systems? Will you be doing failover? (Note that failover requires software to go with the hardware, and can be done in a different way with local storage also.) In some environments the answers to those questions are yes, and the price premium performance implications are well worth it. For a single DB server the answer is almost certainly no. Mike Stone ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Running on an NFS Mounted Directory
On Thu, Apr 27, 2006 at 09:06:48 -0400, Ketema Harris [EMAIL PROTECTED] wrote: Yes, your right, I meant not have to do the backups from the db server itself. I can do that within the storage device now, by allocating space for it, and letting the device copy the data files on some periodic basis. Only if the database server isn't running or your SAN provides a way to provide a snapshot of the data at a particular instant in time. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Running on an NFS Mounted Directory
First, I appreciate all of your input. No, backups are completely unrelated to your storage type; you need them either way. Please another post. I meant the storage would do the back ups. redundancy, expandability What I mean by these stupid flavor words is: Redundancy : raid 5. Expandability : the ability to stick another drive in my array and get more storage and not have to turn of the db. Do you need the ability to do snapshots? Yes. Do you want to share one big, expensive, reliable unit between multiple systems? Will you be doing failover? Yes, and Yes. Really on one other system, a phone system, but it is the crux of my business and will be writing a lot of recorded phone calls. I am working with a storage company now to set up the failover, I want the db and phone systems to never no if the storage switched over. You have given me a lot to think about. The performance concerns me and I will have to find some way to test. Perhaps spending a little less on the storage system and more on the actual servers is the way to go? Then utilize some combination off pg_backup, and the archive_command directive with a periodic script. Thank You all. I will keep researching this and the more input the better. Thank You. On 4/27/06 9:24 AM, Michael Stone [EMAIL PROTECTED] wrote: On Thu, Apr 27, 2006 at 08:57:51AM -0400, Ketema Harris wrote: OK. My thought process was that having non local storage as say a big raid 5 san ( I am talking 5 TB with expansion capability up to 10 ) That's two disk trays for a cheap slow array. (Versus a more expensive solution with more spindles and better seek performance.) would allow me to have redundancy, expandability, and hopefully still retain decent performance from the db. I also would hopefully then not have to do periodic backups from the db server to some other type of storage. No, backups are completely unrelated to your storage type; you need them either way. On a SAN you can use a SAN backup solution to back multiple systems up with a single backup unit without involving the host CPUs. This is fairly useless if you aren't amortizing the cost over a large environment. Is this not a good idea? It really depends on what you're hoping to get. As described, it's not clear. (I don't know what you mean by redundancy, expandability or decent performance.) How bad of a performance hit are we talking about? Way too many factors for an easy answer. Consider the case of NAS vs SCSI direct attach storage. You're probably in that case comparing a single 125MB/s (peak) gigabit ethernet channel to (potentially several) 320MB/s (peak) SCSI channels. With a high-end NAS you might get 120MB/s off that GBE. With a (more realistic) mid-range unit you're more likely to get 40-60MB/s. Getting 200MB/s off the SCSI channel isn't a stretch, and you can fairly easily stripe across multiple SCSI channels. (You can also bond multiple GBEs, but then your cost complexity start going way up, and you're never going to scale as well.) If you have an environment where you're doing a lot of sequential scans it isn't even a contest. You can also substitute SATA for SCSI, etc. For a FC SAN the peformance numbers are a lot better, but the costs complexity are a lot higher. An iSCSI SAN is somewhere in the middle. Also, in regards to the commit data integrity, as far as the db is concerned once the data is sent to the san or nas isn't it written? The storage may have that write in cache, but from my reading and understanding of how these various storage devices work that is how they keep up performance. Depends on the configuration, but yes, most should be able to report back a write once the data is in a non-volatile cache. You can do the same with a direct-attached array and eliminate the latency inherent in accessing the remote storage. I would expect my bottleneck if any to be the actual Ethernet transfer to the storage, and I am going to try and compensate for that with a full gigabit backbone. see above. The advantages of a NAS or SAN are in things you haven't really touched on. Is the filesystem going to be accessed by several systems? Do you need the ability to do snapshots? (You may be able to do this with direct-attach also, but doing it on a remote storage device tends to be simpler.) Do you want to share one big, expensive, reliable unit between multiple systems? Will you be doing failover? (Note that failover requires software to go with the hardware, and can be done in a different way with local storage also.) In some environments the answers to those questions are yes, and the price premium performance implications are well worth it. For a single DB server the answer is almost certainly no. Mike Stone ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining
Re: [PERFORM] Running on an NFS Mounted Directory
The SAN has the snapshot capability. On 4/27/06 9:31 AM, Bruno Wolff III [EMAIL PROTECTED] wrote: On Thu, Apr 27, 2006 at 09:06:48 -0400, Ketema Harris [EMAIL PROTECTED] wrote: Yes, your right, I meant not have to do the backups from the db server itself. I can do that within the storage device now, by allocating space for it, and letting the device copy the data files on some periodic basis. Only if the database server isn't running or your SAN provides a way to provide a snapshot of the data at a particular instant in time. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Running on an NFS Mounted Directory
On Thu, Apr 27, 2006 at 09:41:21AM -0400, Ketema Harris wrote: No, backups are completely unrelated to your storage type; you need them either way. Please another post. I meant the storage would do the back ups. Which isn't a backup. Even expensive storage arrays can break or burn down. redundancy, expandability What I mean by these stupid flavor words is: Redundancy : raid 5. You can get that without external storage. Expandability : the ability to stick another drive in my array and get more storage and not have to turn of the db. You can also get that without external storage assuming you choose a platform with a volume manager. Do you need the ability to do snapshots? Yes. If that's a hard requirement you'll have to eat the cost performance problems of an external solution or choose a platform that will let you do that with direct-attach storage. (Something with a volume manager.) Do you want to share one big, expensive, reliable unit between multiple systems? Will you be doing failover? Yes, and Yes. Really on one other system, a phone system, but it is the crux of my business and will be writing a lot of recorded phone calls. I am working with a storage company now to set up the failover, I want the db and phone systems to never no if the storage switched over. If you actually have a couple of systems you're trying to fail over, a FC SAN may be a reasonable solution. Depending on your reliability requirement you can have multiple interfaces FC switches to get redundant paths and a much higher level of storage reliability than you could get with direct attach storage. OTOH, if the DB server itself breaks you're still out of luck. :) You might compare that sort of solution with a solution that has redundant servers and implements the failover in software instead of hardware. Mike Stone ---(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: [PERFORM] Hardware: HP StorageWorks MSA 1500
On Tue, 25 Apr 2006, Mark Kirkwood wrote: Mikael Carneholm wrote: There are two SCSI U320 buses, with seven bays on each. I don't know what the overhead of SCSI is, but you're obviously not going to get 490MB/s for each set of seven even if the FC could do it. You should be able to get close to 300Mb/s on each SCSI bus - provided the PCI bus on the motherboard is 64-bit and runs at 133Mhz or better (64-bit and 66Mhz give you a 524Mb/s limit). I've no idea if the MSA1500's controllers use PCI internally. Obviously this argument applies to the PCI bus you plug your FC adapters in to, though. AIUI it's difficult to get PCI to actually give you it's theoretical maximum bandwidth. Those speeds are still a lot more than 200MB/s, though. Of course your database may not spend all day doing sequential scans one at a time over 14 disks, so it doesn't necessarily matter... Yeah, it depends on the intended workload, but at some point most databases end up IO bound... so you really want to ensure the IO system is as capable as possible IMHO. IO bound doesn't imply IO bandwidth bound. 14 disks doing a 1ms seek followed by an 8k read over and over again is a bit over 100MB/s. Adding in write activity would make a difference, too, since it'd have to go to at least two disks. There are presumably hot spares, too. I still wouldn't really want to be limited to 200MB/s if I expected to use a full set of 14 disks for active database data where utmost performance really matters and where there may be some sequential scans going on, though. That's probably true, but *knowing* that the max seq scan speed is that high gives you some confidence (true or fake) that the hardware will be sufficient the next 2 years or so. So, if dual 2GBit FC:s still don't deliver more than 200Mb/s, what does? Most modern PCI-X or PCIe RAID cards will do better than 200Mb/s (e.g. 3Ware 9550SX will do ~800Mb/s). By way of comparison my old PIII with a Promise TX4000 plus 4 IDE drives will do 215Mb/s...so being throttled to 200Mb/s on modern hardware seems unwise to me. Though, of course, these won't do many of the things you can do with a SAN - like connect several computers, or split a single array in to two pieces and have two computers access them as if they were separate drives, or remotely shut down one database machine and then start up another using the same disks and data. The number of IO operations per second they can do is likely to be important, too...possibly more important. There's 4GB FC, and so presumably 4GB SANs, but that's still not vast bandwidth. Using multiple FC ports is the other obvious way to do it with a SAN. I haven't looked, but I suspect you'll need quite a budget to get that... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Guoping Zhang [EMAIL PROTECTED] writes: Our application has a strict speed requirement for DB operation. Our tests show that it takes about 10secs for the operation when setting fsync off, but takes about 70 seconds when setting fsync ON (with other WAL related parametered tuned). I can't believe that a properly tuned application would have an fsync penalty that large. Are you performing that operation as several thousand small transactions, or some such? Try grouping the operations into one (or at most a few) transactions. Also, what wal_buffers and wal_sync_method settings are you using, and have you experimented with alternatives? What sort of platform is this on? What PG version? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync
Simon Riggs [EMAIL PROTECTED] writes: On Thu, 2006-04-27 at 16:31 +1000, Guoping Zhang wrote: Can we set fsync OFF for the performance benefit, have the risk of only 5 minutes data loss or much worse? Thats up to you. fsync can be turned on and off, so you can make critical changes with fsync on, then continue with fsync off. I think it would be a mistake to assume that the behavior would be nice clean we only lost recent changes. Things could get arbitrarily badly corrupted if some writes make it to disk and some don't. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Large (8M) cache vs. dual-core CPUs
On Apr 25, 2006, at 5:09 PM, Ron Peacetree wrote: ...and even if you do buy Intel, =DONT= buy Dell unless you like causing trouble for yourself. Bad experiences with Dell in general and their poor PERC RAID controllers in specific are all over this and other DB forums. I don't think that their current controllers suck like their older ones did. That's what you'll read about in the archives -- the old stuff. Eg, the 1850's embedded RAID controller really flies, but it only works with the internal disks. I can't comment on the external array controller for the 1850, but I cannot imagine it being any slower. And personally, I've not experienced any major problems aside from two bad PE1550's 4 years ago. And I have currently about 15 Dell servers running 24x7x365 doing various tasks, including postgres. However, my *big* databases always go on dual opteron boxes. my current favorite is the SunFire X4100 with an external RAID. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux [PERFORM] Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)]
andremachado [EMAIL PROTECTED] writes: Firebird has something similiar to EXPLAIN. Please look below. Hm, maybe I just don't know how to read their output, but it's not obvious to me where they are doing the min/max aggregates. Is there something really wrong with the postgresql configuration (at my previous msg) that is causing this poor performance at these 2 queries? I don't think it's a configuration issue, it's a quality-of-plan issue. Could you put together a self-contained test case for this problem? I don't have the time or interest to try to reverse-engineer tables and test data for these queries --- but I would be interested in finding out where the time is going, if I could run the queries. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Running on an NFS Mounted Directory
On Thu, Apr 27, 2006 at 10:04:19AM -0400, Michael Stone wrote: redundancy, expandability What I mean by these stupid flavor words is: Redundancy : raid 5. You can get that without external storage. Yes, but some dedicated storage devices actually provide good performance with RAID5. Most simpler solutions give pretty abysmal write performance. Do you need the ability to do snapshots? Yes. If that's a hard requirement you'll have to eat the cost performance problems of an external solution or choose a platform that will let you do that with direct-attach storage. (Something with a volume manager.) I'm wondering if PITR would suffice. Or maybe even Slony. Do you want to share one big, expensive, reliable unit between multiple systems? Will you be doing failover? Yes, and Yes. Really on one other system, a phone system, but it is the crux of my business and will be writing a lot of recorded phone calls. I am working with a storage company now to set up the failover, I want the db and phone systems to never no if the storage switched over. If you actually have a couple of systems you're trying to fail over, a FC SAN may be a reasonable solution. Depending on your reliability requirement you can have multiple interfaces FC switches to get redundant paths and a much higher level of storage reliability than you could get with direct attach storage. OTOH, if the DB server itself breaks you're still out of luck. :) You might compare that sort of solution with a solution that has redundant servers and implements the failover in software instead of hardware. BTW, I know a company here in Austin that does capacity planning for complex systems like this; contact me off-list if you're interested in talking to them. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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: [PERFORM] Running on an NFS Mounted Directory
On Thu, Apr 27, 2006 at 12:50:16PM -0500, Jim C. Nasby wrote: Yes, but some dedicated storage devices actually provide good performance with RAID5. Most simpler solutions give pretty abysmal write performance. dedicated storage device != SAN != NAS. You can get good performance in a dedicated direct-attach device without paying for the SAN/NAS infrastructure if you don't need it; you don't have to go right from EMC to PERC with nothing in the middle. Mike Stone ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Why so slow?
Hi folks,
Sorry to be bringing this up again, but I'm stumped by this problem
and hope you can shed some light on it.
I'm running postgresql 8.0 on a RLE4 server with 1.5 GB of RAM and a
Xenon 2 GHz CPU. The OS is bog standard and I've not done any kernel
tuning on it. The file system is also bog standard ext3 with no raid
of any kind. I know I could improve this aspect of the set up with
more disks and raid 0+1 etc, but the lack of performance that I'm
experiencing is not likely to be attributable to this sort of
thing. More likely it's my bad understanding of Postgresql - I hope
it's my bad understanding of Postgresql!!
My database is very simple and not by the book (normal forms etc. are
not all as they should be). My biggest table, by a factor of 3000 or
so is one of 4 tables in my tiny database. It looks like this
\d job_log
Table job_log
Column |Type |Modifiers
+-+--
job_log_id | integer | not null default
nextval('job_log_id_seq'::text)
first_registry | timestamp without time zone |
customer_name | character(50) |
node_id| integer |
job_type | character(50) |
job_name | character(256) |
job_start | timestamp without time zone |
job_timeout| interval|
job_stop | timestamp without time zone |
nfiles_in_job | integer |
status | integer |
error_code | smallint|
file_details | text|
Indexes:
job_log_id_pkey PRIMARY KEY, btree (job_log_id)
idx_customer_name_filter btree (customer_name)
idx_job_name_filter btree (job_name)
idx_job_start_filter btree (job_start)
idx_job_stop_filter btree (job_stop)
Check constraints:
job_log_status_check CHECK (status = 0 OR status = 1 OR status = 8 OR
status = 9)
Foreign-key constraints:
legal_node FOREIGN KEY (node_id) REFERENCES node(node_id)
The node table is tiny (2500 records). What I'm pulling my hair out
over is that ANY Query, even something as simple as select count(*)
form job_log takes of the order of tens of minutes to complete. Just
now I'm trying to run an explain analyze on the above query, but so
far, it's taken 35min! with no result and there is a postgres process at
the top of top
What am I doing wrong??
Many thanks,
Bealach
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Why so slow?
Bealach-na Bo [EMAIL PROTECTED] schrieb: The node table is tiny (2500 records). What I'm pulling my hair out over is that ANY Query, even something as simple as select count(*) form job_log takes of the order of tens of minutes to complete. Just now I'm trying to run an explain analyze on the above query, but so far, it's taken 35min! with no result and there is a postgres process at the top of top What am I doing wrong?? The 'explain analyse' don't return a result, but it returns the query plan and importance details, how PG works. That's why you should paste the query and the 'explain analyse' - output. This is very important. Anyway, do you periodical vacuum your DB? My guess: no, and that's why you have many dead rows. 20:26 akretschmer|home ??vacuum 20:26 rtfm_please For information about vacuum 20:26 rtfm_please see http://developer.postgresql.org/~wieck/vacuum_cost/ 20:26 rtfm_please or http://www.postgresql.org/docs/current/static/sql-vacuum.html 20:26 rtfm_please or http://www.varlena.com/varlena/GeneralBits/116.php 20:27 akretschmer|home ??explain 20:27 rtfm_please For information about explain 20:27 rtfm_please see http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining_Explain_Public.sxi 20:27 rtfm_please or http://www.gtsm.com/oscon2003/toc.html 20:27 rtfm_please or http://www.postgresql.org/docs/current/static/sql-explain.html Read this links for more informations about vacuum and explain. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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
[PERFORM] CPU usage goes to 100%, query seems to ran forever
I have small database running in 8.1.3 in W2K server.
The following query causes Postgres process to use 100% CPU and seems to run
forever.
If I change '1EEKPANT' to less frequently used item code, it runs fast.
How to speed it up ?
set search_path to public,firma2;
select rid.toode
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
LEFT JOIN artliik ON toode.grupp=artliik.grupp and
toode.liik=artliik.liik
WHERE (NOT '0' or dok.kinnitatud)
AND dok.kuupaev BETWEEN '2006-04-08' AND '2006-04-27'
AND rid.toode='1EEKPANT'
AND (NOT dok.eimuuda or '0' ) and
dok.laonr='1'::float8 and
POSITION( dok.doktyyp IN 'OSIDVGYKIF')!=0 AND
( ( ('1' OR (POSITION(dok.doktyyp IN 'TUNH')=0 and
(rid.kogus0 or
('1' and rid.kogus=0
and
POSITION(dok.doktyyp IN 'VGYKITDNHMEBARCFJ' )!=0
AND CASE WHEN NOT dok.objrealt OR dok.doktyyp='I' THEN dok.yksus
ELSE rid.kuluobjekt END LIKE 'LADU%' ESCAPE '!'
)
OR
(POSITION(dok.doktyyp IN 'OSIUDP' )!=0
AND CASE WHEN dok.objrealt THEN rid.kuluobjekt ELSE dok.sihtyksus
END LIKE 'LADU%' ESCAPE '!'
)
)
AND dok.kuupaev||dok.kellaaeg BETWEEN '2006-04-08' AND '2006-04-2723 59'
AND ('0' or ( length(trim(rid.toode))2 AND
rid.toode is NOT NULL))
AND ( LENGTH('' )=0 OR rid.partii='' OR (dok.doktyyp='I' AND
rid.kulupartii='' ) )
AND (NOT dok.inventuur or rid.kogus!=0)
AND dok.dokumnr!= 0
AND ( artliik.arttyyp NOT IN ('Teenus', 'Komplekt' ) OR artliik.arttyyp IS
NULL)
explain returns:
Nested Loop Left Join (cost=0.00..1828.18 rows=1 width=24)
Filter: (((inner.arttyyp 'Teenus'::bpchar) AND (inner.arttyyp
'Komplekt'::bpchar)) OR (inner.arttyyp IS NULL))
- Nested Loop (cost=0.00..1822.51 rows=1 width=43)
- Nested Loop (cost=0.00..1816.56 rows=1 width=24)
Join Filter: ((outer.dokumnr = inner.dokumnr) AND
(((position('VGYKITDNHMEBARCFJ'::text, (outer.doktyyp)::text) 0) AND
(CASE WHEN ((NOT (outer.objrealt)::boolean) OR (outer.doktyyp =
'I'::bpchar)) THEN outer.yksus ELSE inner (..)
- Seq Scan on dok (cost=0.00..787.80 rows=1 width=39)
Filter: ((kuupaev = '2006-04-08'::date) AND (kuupaev
= '2006-04-27'::date) AND (NOT (eimuuda)::boolean) AND ((laonr)::double
precision = 1::double precision) AND (position('OSIDVGYKIF'::text,
(doktyyp)::text) 0) AND (((kuupaev):: (..)
- Seq Scan on rid (cost=0.00..1019.42 rows=249 width=51)
Filter: ((toode = '1EEKPANT'::bpchar) AND
(length(btrim((toode)::text)) 2) AND (toode IS NOT NULL))
- Index Scan using toode_pkey on toode (cost=0.00..5.94 rows=1
width=43)
Index Cond: ('1EEKPANT'::bpchar = toode)
- Index Scan using artliik_pkey on artliik (cost=0.00..5.65 rows=1
width=88)
Index Cond: ((outer.grupp = artliik.grupp) AND (outer.liik =
artliik.liik))
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Running on an NFS Mounted Directory
So do NAS's Dan On Apr 27, 2006, at 6:42 AM, Ketema Harris wrote: The SAN has the snapshot capability. On 4/27/06 9:31 AM, Bruno Wolff III [EMAIL PROTECTED] wrote: On Thu, Apr 27, 2006 at 09:06:48 -0400, Ketema Harris [EMAIL PROTECTED] wrote: Yes, your right, I meant not have to do the backups from the db server itself. I can do that within the storage device now, by allocating space for it, and letting the device copy the data files on some periodic basis. Only if the database server isn't running or your SAN provides a way to provide a snapshot of the data at a particular instant in time. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] CPU usage goes to 100%, query seems to ran forever
Andrus [EMAIL PROTECTED] writes: I have small database running in 8.1.3 in W2K server. The following query causes Postgres process to use 100% CPU and seems to run forever. If I change '1EEKPANT' to less frequently used item code, it runs fast. You have ANALYZEd all these tables recently, I hope? The planner certainly doesn't think this query will take very long. To find out what's wrong, you're going to have to be patient enough to let an EXPLAIN ANALYZE run to completion. Plain EXPLAIN won't tell. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hardware: HP StorageWorks MSA 1500
Alex Hayward wrote:
IO bound doesn't imply IO bandwidth bound. 14 disks doing a 1ms seek
followed by an 8k read over and over again is a bit over 100MB/s. Adding
in write activity would make a difference, too, since it'd have to go to
at least two disks. There are presumably hot spares, too.
Very true - if your workload is primarily random, ~100Mb/s may be enough
bandwidth.
I still wouldn't really want to be limited to 200MB/s if I expected to use
a full set of 14 disks for active database data where utmost performance
really matters and where there may be some sequential scans going on,
though.
Yeah - thats the rub, Data mining, bulk loads, batch updates, backups
(restores) often use significant bandwidth.
Though, of course, these won't do many of the things you can do with a SAN
- like connect several computers, or split a single array in to two pieces
and have two computers access them as if they were separate drives, or
remotely shut down one database machine and then start up another using
the same disks and data. The number of IO operations per second they can
do is likely to be important, too...possibly more important.
SAN flexibility is nice (when it works as advertised), the cost and
performance however, are the main detractors. On that note I don't
recall IO/s being anything special on most SAN gear I've seen (this
could have changed for later products I guess).
There's 4GB FC, and so presumably 4GB SANs, but that's still not vast
bandwidth. Using multiple FC ports is the other obvious way to do it with
a SAN. I haven't looked, but I suspect you'll need quite a budget to get
that...
Yes - the last place I worked were looking at doing this ('multiple
attachment' was the buzz word I think) - I recall it needed special
(read extra expensive) switches and particular cards...
Cheers
Mark
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Hi, Tom, Thanks for the reply. a) The tests consists of ten thousands very small transactions, which are not grouped, that is why so slow with compare to set fsync off. b) we are using Solaris 10 on a SUN Fire 240 SPARC machine with a latest postgresql release (8.1.3) c) wal_sync_method is set to 'open_datasync', which is fastest among the four, right? d) wal_buffers set to 32 Looks like, if we have to set fsync be true, we need to modify our application. Thanks and regards, Guoping -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: 2006Äê4ÔÂ28ÈÕ 0:53 To: [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org; Guoping Zhang (E-mail) Subject: Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql Guoping Zhang [EMAIL PROTECTED] writes: Our application has a strict speed requirement for DB operation. Our tests show that it takes about 10secs for the operation when setting fsync off, but takes about 70 seconds when setting fsync ON (with other WAL related parametered tuned). I can't believe that a properly tuned application would have an fsync penalty that large. Are you performing that operation as several thousand small transactions, or some such? Try grouping the operations into one (or at most a few) transactions. Also, what wal_buffers and wal_sync_method settings are you using, and have you experimented with alternatives? What sort of platform is this on? What PG version? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Guoping Zhang [EMAIL PROTECTED] writes: a) The tests consists of ten thousands very small transactions, which are not grouped, that is why so slow with compare to set fsync off. Yup. c) wal_sync_method is set to 'open_datasync', which is fastest among the four, right? Well, is it? You shouldn't assume that without testing. Looks like, if we have to set fsync be true, we need to modify our application. Yes, you should definitely look into batching your operations into larger transactions. On normal hardware you can't expect to commit transactions faster than one per disk revolution (unless they're coming from multiple clients, where there's a hope of ganging several parallel commits per revolution). Or buy a disk controller with battery-backed write cache and put your faith in that cache surviving a machine crash. But don't turn off fsync if you care about your data. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync
Hi, Simon/tom, Thanks for the reply. It appears to me that we have to set fsync ON, as a badly corrupted database by any chance in production line will lead a serious problem. However, when try the differnt 'wal_sync_method' setting, lead a quite different operation time (open_datasync is best for speed). But altering the commit_delay from 1 to 10, I observed that there is no time difference for the operation. Why is that? As our tests consists of 1 small transactions which completed in 66 seconds, that is, about 160 transactions per second. When commit_delay set to 10 (i.e., 0.1 second), that in theory, shall group around 16 transactions into one commit, but result is same from the repeated test. Am I mistaken something here? Cheers and Regards, Guoping -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 2006Äê4ÔÂ28ÈÕ 0:58 To: Simon Riggs Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org; Guoping Zhang (E-mail) Subject: Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync Simon Riggs [EMAIL PROTECTED] writes: On Thu, 2006-04-27 at 16:31 +1000, Guoping Zhang wrote: Can we set fsync OFF for the performance benefit, have the risk of only 5 minutes data loss or much worse? Thats up to you. fsync can be turned on and off, so you can make critical changes with fsync on, then continue with fsync off. I think it would be a mistake to assume that the behavior would be nice clean we only lost recent changes. Things could get arbitrarily badly corrupted if some writes make it to disk and some don't. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync
Guoping Zhang [EMAIL PROTECTED] writes: But altering the commit_delay from 1 to 10, I observed that there is no time difference for the operation. Why is that? As our tests consists of 1 small transactions which completed in 66 seconds, that is, about 160 transactions per second. When commit_delay set to 10 (i.e., 0.1 second), that in theory, shall group around 16 transactions into one commit, but result is same from the repeated test. Am I mistaken something here? commit_delay can only help if there are multiple clients issuing transactions concurrently, so that there are multiple commits pending at the same instant. If you are issuing one serial stream of transactions, it's useless. If you do have multiple active clients, then we need to look more closely; but your statement does not indicate that. 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
Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Hi, Tom Many thanks for quick replies and that helps a lot. Just in case, anyone out there can recommend a good but cost effective battery-backed write cache SCSI for Solaris SPARC platform? How well does it work with UFS or newer ZFS for solaris? Cheers and regards, Guoping -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 2006Äê4ÔÂ28ÈÕ 14:57 To: [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org; 'Guoping Zhang (E-mail)' Subject: Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync OFF for postgresql Guoping Zhang [EMAIL PROTECTED] writes: a) The tests consists of ten thousands very small transactions, which are not grouped, that is why so slow with compare to set fsync off. Yup. c) wal_sync_method is set to 'open_datasync', which is fastest among the four, right? Well, is it? You shouldn't assume that without testing. Looks like, if we have to set fsync be true, we need to modify our application. Yes, you should definitely look into batching your operations into larger transactions. On normal hardware you can't expect to commit transactions faster than one per disk revolution (unless they're coming from multiple clients, where there's a hope of ganging several parallel commits per revolution). Or buy a disk controller with battery-backed write cache and put your faith in that cache surviving a machine crash. But don't turn off fsync if you care about your data. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
