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
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
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
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
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
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
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
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
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
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?
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
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
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
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
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 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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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.
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
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
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
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
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
35 matches
Mail list logo
