@markir: "I'm trying to pin down some performance issues with a machine where I 
work, we are seeing (read only) query response times blow out by an 
order of magnitude or more at busy times. Initially we blamed 
autovacuum, but after a  tweak of the cost_delay it is *not* the 
problem. Then I nhouston@example.org... and altho there was some 
correlation with them and the query response - I'm thinking that the 
raid chunksize may well be the issue.
Fortunately there is an identical DR box, so I could do a little 
testing. Details follow:
Sun 4140 2x quad-core opteron 2356 16G RAM,  6x 15K 140G SAS
Debian Lenny
Pg 8.3.6
The disk is laid out using software (md) raid:
4 drives raid 10 *4K* chunksize with database files (ext3 ordered, noatime)
2 drives raid 1 with database transaction logs (ext3 ordered, noatime)
The relevant non default .conf params are:
shared_buffers = 2048MB           
work_mem = 4MB               
maintenance_work_mem = 1024MB       
max_fsm_pages = 153600           
bgwriter_lru_maxpages = 200       
wal_buffers = 2MB           
checkpoint_segments = 32      
effective_cache_size = 4096MB
autovacuum_vacuum_scale_factor = 0.1   
autovacuum_vacuum_cost_delay = 60    # This is high, but seemed to help...
I've run pgbench:
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 655.335102 (including connections establishing)
tps = 655.423232 (excluding connections establishing)
Looking at iostat while it is running shows (note sda-sdd raid10, sde 
and sdf raid 1):
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s 
avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    56.80    0.00  579.00     0.00     2.47     
8.74   133.76  235.10   1.73 100.00
sdb               0.00    45.60    0.00  583.60     0.00     2.45     
8.59    52.65   90.03   1.71 100.00
sdc               0.00    49.00    0.00  579.80     0.00     2.45     
8.66    72.56  125.09   1.72 100.00
sdd               0.00    58.40    0.00  565.00     0.00     2.42     
8.79   135.31  235.52   1.77 100.00
sde               0.00     0.00    0.00    0.00     0.00     0.00     
0.00     0.00    0.00   0.00   0.00
sdf               0.00     0.00    0.00    0.00     0.00     0.00     
0.00     0.00    0.00   0.00   0.00
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s 
avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    12.80    0.00   23.40     0.00     0.15    
12.85     3.04  103.38   4.27  10.00
sdb               0.00    12.80    0.00   22.80     0.00     0.14    
12.77     2.31   73.51   3.58   8.16
sdc               0.00    12.80    0.00   21.40     0.00     0.13    
12.86     2.38   79.21   3.63   7.76
sdd               0.00    12.80    0.00   21.80     0.00     0.14    
12.70     2.66   90.02   3.93   8.56
sde               0.00  2546.80    0.00  146.80     0.00    10.53   
146.94     0.97    6.38   5.34  78.40
sdf               0.00  2546.80    0.00  146.60     0.00    10.53   
147.05     0.97    6.38   5.53  81.04
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s 
avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00   231.40    0.00  566.80     0.00     3.16    
11.41   124.92  228.26   1.76  99.52
sdb               0.00   223.00    0.00  558.00     0.00     3.06    
11.23    46.64   83.55   1.70  94.88
sdc               0.00   230.60    0.00  551.60     0.00     3.07    
11.40    94.38  171.54   1.76  96.96
sdd               0.00   231.40    0.00  528.60     0.00     2.94    
11.37   122.55  220.81   1.83  96.48
sde               0.00  1495.80    0.00   99.00     0.00     6.23   
128.86     0.81    8.15   7.76  76.80
sdf               0.00  1495.80    0.00   99.20     0.00     6.26   
129.24     0.73    7.40   7.10  70.48
Top looks like:
Cpu(s):  2.5%us,  1.9%sy,  0.0%ni, 71.9%id, 23.4%wa,  0.2%hi,  0.2%si,  
0.0%st
Mem:  16474084k total, 15750384k used,   723700k free,  1654320k buffers
Swap:  2104440k total,      944k used,  2103496k free, 13552720k cached
It looks to me like we are maxing out the raid 10 array, and I suspect 
the chunksize (4K) is the culprit. However as this is a pest to change 
(!) I'd like some opinions on whether I'm jumping to conclusions. I'd 
also appreciate comments about what chunksize to use (I've tended to use 
256K in the past, but what are folks preferring these days?)
regards
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @drees76: "On Tue, Mar 24, 2009 at 6:48 PM, Scott Carey <email_address> wrot=
e:
<quote>
 at
tion.
I would tend to recommend ext3 in data=3Dwriteback and make sure that
it's mounted with noatime over using ext2 - for the sole reason that
if the system shuts down unexpectedly, you don't have to worry about a
long fsck when bringing it back up.
Performance between the two filesystems should really be negligible
for Postgres logging.
-Dave
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @scottmarlowe: "On Tue, Mar 24, 2009 at 7:09 PM, Mark Kirkwood <email_address> wro=
te:
<quote>
k,
r a
ry
Sounds to me like you're mostly just running out of bandwidth on your
RAID array.  Whether or not you can tune it to run faster is the real
issue.  This problem becomes worse as you add clients and the RAID
array starts to thrash.  Thrashing is likely to be worse with a small
chunk size, so that's definitely worth a nhouston@example.org.
Can you try changing the chunksize on the test box you're testing on
to see if that helps?
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @markir: "<quote>
Yeah, I was wondering if we are maxing out the bandwidth...
Yes - or I am hoping to anyway (part of posting here was to collect some 
outside validation for the idea). Thanks for your input!
Cheers
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @jchamplin: "It sounds to me like you need to tune everything you can related to 
postgresql, but it will unlikely be enough as your load continues to 
increase.  You might want to look into moving some of the read activity 
off of the database.  Depending on you application, memcached or ehcache 
could help.  You could also look at using something like Tokyo Cabinet 
as a short term front end data store.  Without understanding the 
application architecture, I can't offer much in way of a specific 
suggestion.
-Jerry
Jerry Champlin
Absolute Performance Inc.
<quote>
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @stef: "-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
<quote>
Hello Mark,
    Okay, so, take all of this with a pinch of salt, but, I have the
same config (pretty much) as you, with checkpoint_Segments raised to
192. The 'test' database server is Q8300, 8GB ram, 2 x 7200rpm SATA
into motherboard which I then lvm stripped together; lvcreate -n
data_lv -i 2 -I 64 mylv -L 60G (expandable under lvm2). That gives me
a stripe size of 64. Running pgbench with the same scaling factors;
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 1398.907206 (including connections establishing)
tps = 1399.233785 (excluding connections establishing)
    It's also running ext4dev, but, this is the 'playground' server,
not the real iron (And I dread to do that on the real iron). In short,
I think that chunksize/stripesize is killing you. Personally, I would
go for 64 or 128 .. that's jst my 2c .. feel free to
ignore/scorn/laugh as applicable ;)
    Regards
    Stef
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - <url>
iEYEARECAAYFAknK0UsACgkQANG7uQ+9D9VK3wCeO/guLVb4K4V7VAQ29hJsmstb
2JMAmQEmJjNTQlxng/49D2/xHNw2W19/
=/rKD
-END PGP SIGNATURE-
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @markir: "<quote>
Rebuilt with 64K chunksize:
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 866.512162 (including connections establishing)
tps = 866.651320 (excluding connections establishing)
So 64K looks quite a bit better. I'll endeavor to try out 256K next week 
too.
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @markir: "<quote>
See my other post, however I agree - it wasn't clear whether split 
writes (from the small chunksize) were killing us or the array was 
simply maxed out...
Yeah, no write nhouston@example.org.
Right - but then a big very noticeable stall when you do have to 
checkpoint? We want to avoid that I think, even at the cost of a little 
overall throughput.
Yeah - with 64K chunksize I'm seeing a result more congruent with yours 
(866 or so for 24 clients), I think another pair of disks so we could 
have 3 effective disks for the database would help get us to similar 
results to yours... however for the meantime I'm trying to get the best 
out of what's there!
Thanks for your help
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @markir: "<quote>
Stef - I suspect that your (quite high) tps is because your SATA disks 
are not honoring the fsync() request for each commit. SCSI/SAS disks 
tend to by default flush their cache at fsync - ATA/SATA tend not to. 
Some filesystems (e.g xfs) will try to work around this with write 
barrier support, but it depends on the disk firmware.
Thanks for your reply!
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @markir: "<quote>
[Looks at  Stef's  config - 2x 7200 rpm SATA RAID 0]  I'm still highly 
suspicious of such a system being capable of outperforming one with the 
same number of (effective) - much faster - disks *plus* a dedicated WAL 
disk pair... unless it is being a little loose about fsync! I'm happy to 
believe ext4 is better than ext3 - but not that much!
However, its great to have so many different results to compare against!
Cheers
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @markir: "<quote>
Rebuilt with 256K chunksize:
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 942.852104 (including connections establishing)
tps = 943.019223 (excluding connections establishing)
A noticeable improvement again. I'm not sure that we will have time (or 
patience from the system guys that I keep bugging to redo the raid 
setup!) to try 1M, but 256K gets us 40% or so improvement over the 
original 4K setup  - which is quite nice!
Looking on the net for md raid benchmarks, it is not 100% clear to me 
that 1M is the overall best - several I found had tested sizes like 64K, 
128K, 512K, 1M and concluded that 1M was best - but without testing 
256K! whereas others had included ranges <=512K and decided that that 
256K was the best. I'd be very interested in seeing your data! (several 
years ago I had carried out this type of testing - on a different type 
of machine, and for a different database vendor, but found that 256K 
seemed to give the overall best result).
The next step is to align the raid 10 partitions, as you and Greg 
suggest and see what effect that has!
Thanks again
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @stef: "-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
<quote>
Hello Mark,
    For the record, this is a 'base' debian 5 install (with openVZ but
postgreSQL is running on the base hardware, not inside a container)
and I have -explicitly- enabled sync in the conf. Eg;
fsync = on                                            # turns forced
synchronization on or off
synchronous_commit = on                 # immediate fsync at commit
#wal_sync_method = fsync                # the default is the first option
    Infact, if I turn -off- sync commit, it gets about 200 -slower-
rather than faster. Curiously, I also have an intel x25-m winging it's
way here for testing/benching under postgreSQL (along with a vertex
120gb). I had one of the nice lads on the OCZ forum bench against a
30gb vertex ssd, and if you think -my- TPS was crazy.. you should have
seen his.
postgres@rob-desktop:~$ /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t
12000 test_db
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 3662.200088 (including connections establishing)
tps = 3664.823769 (excluding connections establishing)
    (Nb; Thread here;
<url> )
    Curiously, I think with SSD's there may have to be an 'off' flag
if you put the xlog onto an ssd. It seems to complain about 'too
frequent checkpoints'.
    I can't wait for -either- of the drives to arrive. I want to see
in -my- system what the speed is like for SSD's. The dataset I have to
work with is fairly small (30-40GB) so, using an 80GB ssd (even a few
raided) is possible for me. Thankfully ;)
    Regards
    Stef
(ps. I should note, running postgreSQL in a prod environment -without-
a nice UPS is never going to happen on my watch, so, turning on
write-cache (to me) seems like a no-brainer really if it makes this
kind of boost possible)
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - <url>
iEYEARECAAYFAknTfKMACgkQANG7uQ+9D9XZ7wCfdU3JDXj1f2Em9dt7GdcxRbWR
eHUAn1zDb3HKEiAb0d/0R1MubtE44o/k
=HXmP
-END PGP SIGNATURE-
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @stef: "-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
<quote>
Hello Greg,
    Turning off fsync -does- increase the throughput noticeably,
- -however-, turning off synchronous_commit seemed to slow things down
for me. Your right though, when I toggled the sync_commit on the
system, there was a small variation with TPS coming out between 1100
and 1300. I guess I saw the initial run and thought that there was a
'loss' in sync_commit = off
     I do agree that the benefit is probably from write-caching, but I
think that this is a 'win' as long as you have a UPS or BBU adaptor,
and really, in a prod environment, not having a UPS is .. well. Crazy ?
Good to know, I thought it maybe was atypical behaviour due to the
nature of SSD's.
Regards
Stef
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - <url>
iEYEARECAAYFAknTky0ACgkQANG7uQ+9D9UuNwCghLLC96mj9zzZPUF4GLvBDlQk
fyIAn0V63YZJGzfm+4zPB9zjm8YKn42X
=A6x2
-END PGP SIGNATURE-
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @scottmarlowe: "<quote>
You do know that UPSes can fail, right?  En masse sometimes even.
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @stef: "<quote>
Hello Scott,
    Well, the only time the UPS has failed in my memory, was during the
great Eastern Seaboard power outage of 2003. Lots of fond memories
running around Toronto with a gas can looking for oil for generator
power. This said though, anything could happen, the co-lo could be taken
out by a meteor and then sync on or off makes no difference.
    Good UPS, a warm PITR standby, offsite backups and regular checks is
"good enough" for me, and really, that's what it all comes down to.
Mitigating risk and factors into an 'acceptable' amount for each person.
However, if you see over a 2x improvement from turning write-cache 'on'
and have everything else in place, well, that seems like a 'no-brainer'
to me, at least ;)
    Regards
    Stef
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @scottmarlowe: "<quote>
 I
Meteor strike is far less likely than a power surge taking out a UPS.
I saw a whole data center go black when a power conditioner blew out,
taking out the other three power conditioners, both industrial UPSes
and the switch for the diesel generator.  And I have friends who have
seen the same type of thing before as well.  The data is the most
expensive part of any server.
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @scottmarlowe: "On Wed, Apr 1, 2009 at 11:01 AM, Matthew Wakeling <email_address> wro=
te:
<quote>
ld
This is especially true in that you can reduce downtime.  A lot of
times downtime costs as much as anything else.
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @stef: "<quote>
Hey Matthew,
    See about 3 messages ago.. We already have them (I did say UPS or
BBU, it should have been a logical 'and' instead of logical 'or' .. my
bad ;). Your right though, that was a no-brainer as well.
    I am wondering how the card (3ware 9550sx) will work with SSD's, md
or lvm, blocksize, ext3 or ext4 .. but.. this is the point of
benchmarking ;)
    Regards
    Stef
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @scottmarlowe: "On Wed, Apr 1, 2009 at 11:54 AM, Matthew Wakeling <email_address> wro=
te:
<quote>
ies,
out
Yeah, our primary DB servers have three PSes and can run on any two
just fine.  We have three power busses each coming from a different
UPS at the hosting center.
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @stef: "-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
<quote>
Fyi, I got my intel x25-m in the mail, and I have been benching it for
the past hour or so. Here are some of the rough and ready figures.
Note that I don't get anywhere near the vertex benchmark. I did
hotplug it and made the filesystem using Theodore Ts'o webpage
directions (
<url>
) ; The only thing is, ext3/4 seems to be fixated on a blocksize of
4k, I am wondering if this could be part of the 'problem'. Any
ideas/thoughts on tuning gratefully received.
Anyway, benchmarks (same system as previously, etc)
(ext4dev, 4k block size, pg_xlog on 2x7.2krpm raid-0, rest on SSD)
root@debian:~# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 1407.254118 (including connections establishing)
tps = 1407.645996 (excluding connections establishing)
(ext4dev, 4k block size, everything on SSD)
root@debian:~# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 2130.734705 (including connections establishing)
tps = 2131.545519 (excluding connections establishing)
(I wanted to try and see if random_page_cost dropped down to 2.0,
sequential_page_cost = 2.0 would make a difference. Eg; making the
planner aware that a random was the same cost as a sequential)
root@debian:/var/lib/postgresql/8.3/main#
/usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 1982.481185 (including connections establishing)
tps = 1983.223281 (excluding connections establishing)
Regards
Stef
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - <url>
iEYEARECAAYFAknTxccACgkQANG7uQ+9D9XoPgCfRwWwh0jTIs1iDQBVVdQJW/JN
CBcAn3zoOO33BnYC/FgmFzw1I+isWvJh
=0KYa
-END PGP SIGNATURE-
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @stef: "-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
<quote>
Here is the single x25-m SSD, write cache -disabled-, XFS, noatime
mounted using the no-op scheduler;
stef@debian:~$ sudo /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000
test_db
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 1427.781843 (including connections establishing)
tps = 1428.137858 (excluding connections establishing)
Regards
Stef
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - <url>
iEYEARECAAYFAknT0hEACgkQANG7uQ+9D9X8zQCfcJ+tRQ7Sh6/YQImPejfZr/h4
/QcAn0hZujC1+f+4tBSF8EhNgR6q44kc
=XzG/
-END PGP SIGNATURE-
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @scottmarlowe: "<quote>
ut I
 ?
the
he.
That's why you use two controllers with mirror sets across them and
them RAID-0 across the top.  But I know what you mean.  Now the mobo
and memory are the single point of failure.  Next stop, sequent etc.
hey
ta
You'd better be running them on sequent or Sysplex mainframe type hardware.
an
Pleaze, such hyperbole!  Everyone know it can run on uranium just as
well.  I'm sure these guys:
<url>
ter-fit-for-a-james-bond-villain/
can sort that out for you.
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @markir: "<quote>
Sorry Stef - didn't mean to doubt you....merely your disks!
Cheers
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @markir: "<quote>
Well I went away and did this (actually organized for for the system 
folks to...). Retesting showed no appreciable difference (if anything 
slower). Then I got to thinking:
For a partition created on a (hardware) raided device, sure - alignment 
is very important, however in my case we are using software (md) raid - 
which creates devices out of individual partitions (which are on 
individual SAS disks) e.g:
md3 : active raid10 sda4[0] sdd4[3] sdc4[2] sdb4[1]
     177389056 blocks 256K chunks 2 near-copies [4/4] [UUUU]
I'm thinking that alignment issues do not apply here, as md will 
allocate chunks starting at the beginning of wherever sda4 (etc) begins 
- so the absolute starting position of sda4 is irrelevant. Or am I 
missing something?
Thanks again
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @mmoncure: "On Wed, Mar 25, 2009 at 12:16 PM, Scott Carey <email_address> wro=
te:
<quote>
e cut
I disagree.  The 4k raid chunks are likely to be grouped together on
disk and read sequentially.  This will only give two seeks in special
cases.  Now, if the PostgreSQL block size is _smaller_ than the raid
chunk size,  random writes can get expensive (especially for raid 5)
because the raid chunk has to be fully read in and written back out.
But this is mainly a theoretical problem I think.
I'm going to go out on a limb and say that for block sizes that are
within one or two 'powers of two' of each other, it doesn't matter a
whole lot.  SSDs might be different, because of the 'erase' block
which might be 128k, but I bet this is dealt with in such a fashion
that you wouldn't really notice it when dealing with different block
sizes in pg.
merlin
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @james: "<quote>
That's a big pointy finger you are aiming at that drive - are you sure 
it was sent the flush instruction?  Clearly *something* isn't right.
Try similar tests with Solaris and Vista?
(Might have to give the whole disk to ZFS with Solaris to give it 
confidence to enable write cache, which mioght not be easy with a laptop 
boot drive: XP and Vista should show the toggle on the drive)
James
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @mmoncure: "<quote>
wrote:
er
WO
 be cut
e old
ity
ne
rom
yep...that's good analysis...thinko on my part.
merlin
=20
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @rm_pg: "<quote>
I believe it's ext3 who's cheating in this scenario.
Any chance you can test the program I posted here that
tweaks the inode before the fsync:
<url>
On my system with the fchmod's in that program I was getting one
fsync per disk revolution.   Without the fchmod's, fsync() didn't
nhouston@example.org.
This was the case on dozens of drives I tried, dating back to
old PATA drives from 2000.  Only drives from last century didn't
behave that way - but I can't accuse them of lying because
hdparm showed that they didn't claim to support FLUSH_CACHE.
I think this program shows that practically all hard drives are
physically capable of doing a proper fsync; but annoyingly
ext3 refuses to send the FLUSH_CACHE commands to the drive
unless the inode changed.
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @light: "<quote>
I assume so too. Here the same test using XFS, first with barriers (XFS 
default) and then without:
Linux 2.6.28-gentoo-r2 #1 SMP Intel(R) Core(TM)2 CPU 6400 @ 2.13GHz 
GenuineIntel GNU/Linux
/dev/sdb /data2 xfs rw,noatime,attr2,logbufs=8,logbsize=256k,noquota 0 0
# sysbench test=fileio file-fsync-freq=1 file-num=1 
file-total-size=16384 file-test-mode=rndwr run
sysbench 0.4.10:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Extra file open flags: 0
1 files, 16Kb each
16Kb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 1 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Done.
Operations performed:  0 Read, 10000 Write, 10000 Other = 20000 Total
Read 0b  Written 156.25Mb  Total transferred 156.25Mb  (463.9Kb/sec)
    28.99 Requests/sec executed
Test execution summary:
     total time:                          344.9013s
     total number of events:              10000
     total time taken by event execution: 0.1453
     per-request statistics:
          min:                                  0.01ms
          avg:                                  0.01ms
          max:                                  0.07ms
          approx.  95 percentile:               0.01ms
Threads fairness:
     events (avg/stddev):           10000.0000/0.00
     execution time (avg/stddev):   0.1453/0.00
And now without barriers:
/dev/sdb /data2 xfs 
rw,noatime,attr2,nobarrier,logbufs=8,logbsize=256k,noquota 0 0
# sysbench test=fileio file-fsync-freq=1 file-num=1 
file-total-size=16384 file-test-mode=rndwr run
sysbench 0.4.10:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Extra file open flags: 0
1 files, 16Kb each
16Kb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 1 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Done.
Operations performed:  0 Read, 10000 Write, 10000 Other = 20000 Total
Read 0b  Written 156.25Mb  Total transferred 156.25Mb  (62.872Mb/sec)
  4023.81 Requests/sec executed
Test execution summary:
     total time:                          2.4852s
     total number of events:              10000
     total time taken by event execution: 0.1325
     per-request statistics:
          min:                                  0.01ms
          avg:                                  0.01ms
          max:                                  0.06ms
          approx.  95 percentile:               0.01ms
Threads fairness:
     events (avg/stddev):           10000.0000/0.00
     execution time (avg/stddev):   0.1325/0.00
Best regards,
Hannes Dorbath"
    @markir: "<quote>
Increasing checkpoint_segments to 96 and decreasing 
bgwriter_lru_maxpages to 100:
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 24
number of transactions per client: 12000
number of transactions actually processed: 288000/288000
tps = 1219.221721 (including connections establishing)
tps = 1219.501150 (excluding connections establishing)
... as suggested by Greg (actually he suggested reducing 
bgwriter_lru_maxpages to 0, but this seemed to be no better). Anyway, 
seeing quite a reasonable improvement (about 83% from where we started). 
It will be interesting to see how/if the improvements measured in 
pgbench translate into the "real" application. Thanks for all your help 
(particularly to both Scotts, Greg and Stef).
regards
Mark
Sent via pgsql-performance mailing list (<email_address>)
To make changes to your subscription:
<url>"
{'source': 'USENET', 'other_information': '{"reply_count": 29, "forum_name": "pgsql.performance.20141113.mbox"}', 'file': 'data/output/usenet-pgsql.performance.parquet'}