Re: [PERFORM] [HACKERS] fsync method checking
On 25 Mar, Manfred Spraul wrote: > Tom Lane wrote: > >>[EMAIL PROTECTED] writes: >> >> >>>I could certainly do some testing if you want to see how DBT-2 does. >>>Just tell me what to do. ;) >>> >>> >> >>Just do some runs that are identical except for the wal_sync_method >>setting. Note that this should not have any impact on SELECT >>performance, only insert/update/delete performance. >> >> > I've made a test run that compares fsync and fdatasync: The performance > was identical: > - with fdatasync: > > http://khack.osdl.org/stp/290607/ > > - with fsync: > http://khack.osdl.org/stp/290483/ > > I don't understand why. Mark - is there a battery backed write cache in > the raid controller, or something similar that might skew the results? > The test generates quite a lot of wal traffic - around 1.5 MB/sec. > Perhaps the writes are so large that the added overhead of syncing the > inode is not noticable? > Is the pg_xlog directory on a seperate drive? > > Btw, it's possible to request such tests through the web-interface, see > http://www.osdl.org/lab_activities/kernel_testing/stp/script_param.html We have 2 Adaptec 2200s controllers, without the battery backed add-on, connected to four 10-disk arrays in those systems. I can't think of anything off hand that would skew the results. The pg_xlog directory is not on a separate drive. I haven't found the best way to lay out of the drives on those systems yet, so I just have everything on a 28 drive lvm2 volume. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [HACKERS] fsync method checking
On 22 Mar, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> I could certainly do some testing if you want to see how DBT-2 does.
>> Just tell me what to do. ;)
>
> Just do some runs that are identical except for the wal_sync_method
> setting. Note that this should not have any impact on SELECT
> performance, only insert/update/delete performance.
Ok, here are the results I have from my 4-way xeon system, a 14 disk
volume for the log and a 52 disk volume for everything else:
http://developer.osdl.org/markw/pgsql/wal_sync_method.html
7.5devel-200403222
wal_sync_method metric
default (fdatasync) 1935.28
fsync 1613.92
# ./test_fsync -f /opt/pgdb/dbt2/pg_xlog/test.out
Simple write timing:
write0.018787
Compare fsync times on write() and non-write() descriptor:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 13.057781
write, close, fsync 13.311313
Compare one o_sync write to two:
one 16k o_sync write 6.515122
two 8k o_sync writes12.455124
Compare file sync methods with one 8k write:
(o_dsync unavailable)
open o_sync, write 6.270724
write, fdatasync13.275225
write, fsync, 13.359847
Compare file sync methods with 2 8k writes:
(o_dsync unavailable)
open o_sync, write 12.479563
write, fdatasync13.651709
write, fsync, 14.000240
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [HACKERS] fsync method checking
On 26 Mar, Bruce Momjian wrote: > [EMAIL PROTECTED] wrote: >> On 26 Mar, Manfred Spraul wrote: >> > [EMAIL PROTECTED] wrote: >> > >> >>Compare file sync methods with one 8k write: >> >>(o_dsync unavailable) >> >>open o_sync, write 6.270724 >> >>write, fdatasync13.275225 >> >>write, fsync, 13.359847 >> >> >> >> >> > Odd. Which filesystem, which kernel? It seems fdatasync is broken and >> > syncs the inode, too. >> >> It's linux-2.6.5-rc1 with ext2 filesystems. > > Would you benchmark open_sync for wal_sync_method too? Oh yeah. Will try to get results later today. Mark ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] [HACKERS] fsync method checking
On 26 Mar, Manfred Spraul wrote: > [EMAIL PROTECTED] wrote: > >>Compare file sync methods with one 8k write: >>(o_dsync unavailable) >>open o_sync, write 6.270724 >>write, fdatasync13.275225 >>write, fsync, 13.359847 >> >> > Odd. Which filesystem, which kernel? It seems fdatasync is broken and > syncs the inode, too. It's linux-2.6.5-rc1 with ext2 filesystems. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.
On 4 Apr, Cott Lang wrote: > On Sun, 2004-04-04 at 01:56, Gary Doades wrote: >> Unfortunately I don't understand the question! >> >> My background is the primarily Win32. The last time I used a *nix OS >> was about 20 years ago apart from occasional dips into the linux OS >> over the past few years. If you can tell be how to find out what you want >> I will gladly give you the information. > > There are two available io schedulers in 2.6 (new feature), deadline and > anticipatory. It should show be listed in the boot messages: > > dmesg | grep scheduler > > I've seen people arguing for each of the two schedulers, saying one is > better than the other for databases. I'm curious which one you're > using. :) Our database tests (TPC fair use implementations) show that the deadline scheduler has an edge on the anticipatory scheduler. Depending on the current state of the AS scheduler, it can be within a few percent to 10% or so. I have some data with one of our tests here: http://developer.osdl.org/markw/fs/dbt2_project_results.html Mark ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] backup/restore - another area.
Jeff, I'm curious to what kind of testing you've done with LVM. I'm not currently trying any backup/restore stuff, but I'm running our DBT-2 workload using LVM. I've started collecting vmstat, iostat, and readprofile data, initially running disktest to gauge the performance. For anyone curious, I have some data on a 14-disk volume here: http://developer.osdl.org/markw/lvm/results.4/log/ and a 52-disk volume here: http://developer.osdl.org/markw/lvm/results.5/data/ Mark >Jeff <[EMAIL PROTECTED]> writes: > > Idea #1: > Use an LVM and take a snapshop - archive that. > From the way I see it. the downside is the LVM will use a lot of space > until the snapshot is removed. Also PG may be in a slightly inconsistant > state - but this should "appear" to PG the same as if the power went out. > > For restore, simply unarchive this snapshot and point postgres at it. Let > it recover and you are good to go. > > Little overhead from what I see... > I'm leaning towards this method the more I think of it. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] analyzing postgresql performance for dbt-2
I'm running our DBT-2 workload against PostgreSQL 7.3.4 and I'm having some trouble figuring out what I should be looking for when I'm trying to tune the database. I have results for a decent baseline, but when I try to increase the load on the database, the performance drops. Nothing in the graphs (in the links listed later) sticks out to me so I'm wondering if there are other database statitics I should try to collect. Any suggestions would be great and let me know if I can answer any other questions. Here are a pair of results where I just raise the load on the database, where increasing the load increases the area of the database touched in addition to increasing the transaction rate. The overall metric increases somewhat, but the response time for most of the interactions also increases significantly: http://developer.osdl.org/markw/dbt2-pgsql/158/ [baseline] - load of 100 warehouses - metric 1249.65 http://developer.osdl.org/markw/dbt2-pgsql/149/ - load of 140 warehouses - metric 1323.90 Both of these runs had wal_buffers set to 8, checkpoint_segments 200, and checkpoint_timeout 1800. So far I've only tried various wal_buffers and checkpoint_segments settings in the next set of results for a load of 140 warehouses. http://developer.osdl.org/markw/dbt2-pgsql/148/ - metric 1279.26 - wal_buffers 8 - checkpoint_segments 100 - checkpoint_timeout 300 http://developer.osdl.org/markw/dbt2-pgsql/149/ - metric 1323.90 - wal_buffers 8 - checkpoint_segments 200 - checkpoint_timeout 1800 http://developer.osdl.org/markw/dbt2-pgsql/150/ - metric 1281.13 - wal_buffers 8 - checkpoint_segments 300 - checkpoint_timeout 1800 http://developer.osdl.org/markw/dbt2-pgsql/151/ - metric 1311.99 - wal_buffers 32 - checkpoint_segments 200 - checkpoint_timeout 1800 http://developer.osdl.org/markw/dbt2-pgsql/152/ - metric 1268.37 - wal_buffers 64 - checkpoint_segments 200 - checkpoint_timeout 1800 http://developer.osdl.org/markw/dbt2-pgsql/154/ - metric 1314.62 - wal_buffers 16 - checkpoint_segments 200 - checkpoint_timeout 1800 Thanks! -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 x 32 (office) (503) 626-2436 (fax) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] analyzing postgresql performance for dbt-2
On 26 Oct, Bruce Momjian wrote: > Mark Wong wrote: >> > > Here are a pair of results where I just raise the load on the >> > > database, where increasing the load increases the area of the database >> > > touched in addition to increasing the transaction rate. The overall >> > > metric increases somewhat, but the response time for most of the >> > > interactions also increases significantly: >> > > >> > > http://developer.osdl.org/markw/dbt2-pgsql/158/ [baseline] >> > > - load of 100 warehouses >> > > - metric 1249.65 >> > > >> > > http://developer.osdl.org/markw/dbt2-pgsql/149/ >> > > - load of 140 warehouses >> > > - metric 1323.90 >> > >> > I looked at these charts and they looked normal to me. It looked like >> > your the load increased until your computer was saturated. Is there >> > something I am missing? >> >> I've run some i/o tests so I'm pretty sure I haven't saturated that. And it >> looks like I have almost 10% more processor time left. I do agree that it >> appears something might be saturated, I just don't know where to look... > > Could the 10% be context switching time, or is the I/O saturated? There are about 14,000 to 17,000 context switches/s according to the vmstat output. This is on a 1.5Ghz hyperthreaded Xeon processor. I don't know what I'm supposed to be able to expect in terms of context switching. I really doubt the i/o is saturated because I've run disktest (part of the Linux Test Project suite) and saw much higher throughput for various sequential/random read/write tests. I'm starting to collect oprofile data (and will hopefully have some results soon) to get an idea where the database is spending its time, just in case that may have something to do with it. Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] analyzing postgresql performance for dbt-2
I've done a better controlled series of tests where I restore the database before each test and have grabbed sar and oprofile data: http://developer.osdl.org/markw/dbt2-pgsql/176/ - load of 100 warehouses - metric 1234.52 http://developer.osdl.org/markw/dbt2-pgsql/177/ - load of 120 warehouses - metric 1259.43 http://developer.osdl.org/markw/dbt2-pgsql/178/ - load of 140 warehouses - metric 1244.33 For the most part our primary metric, and the vmstat and sar output look fairly close for each run. Here are a couple of things that I've found to be considerably different from run 176 to 178: - oprofile says postgresql calls to SearchCatCache increased ~ 20% - readprofile says there are 50% more calls in the linux kernel to do_signaction (in kernel/signal.c) Would these two things offer any insight to what might be throttling the throughput? Thanks, Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] High Performance/High Reliability File system on
On 23 Jan, Dave Thompson wrote:
> Hello All
>
> Just wanted to gather opinions on what file system has the best balance between
> performance and reliability when used on a quad processor machine running SuSE64.
> Thanks
>
> DAve
Hi Dave,
I have some data for performance using our DBT-2 workload (OLTP type
transactions) with Linux-2.6 with various filesystems and i/o
schedulers. I know it doesn't address the reliability part of your
question, and it's in a completely different environment (32-bit as well
as a different distro), but if you think you'll find the results
interesting:
http://developer.osdl.org/markw/fs/project_results.html
Mark
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] 7.3 vs 7.4 performance
I have some results with our DBT-2 (OLTP) workload on various linux-2.6 filesystems, if you'll find that interesting: http://developer.osdl.org/markw/fs/dbt2_project_results.html I've found JFS to perform similarly to ext2. Reiserfs isn't far behind. XFS and ext3 fall off a bit. These results are also on a 4-way Xeon, with about 70 drives and a ~ 30GB database. Mark On 5 Feb, Carlos Eduardo Smanioto wrote: >> I did some heavy-transaction-oriented tests recently on somewhat >> heftier quad-Xeon hardware, and found little difference between 2.4 >> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9. >> Now, I'm quite sure my load was rather different from yours, but I >> find the claim of doubling of speed rather surprising. >> -- > > What's the type of File System you used in the Linux? I am wanting to know > which is the operational system better for PostgreSQL: FreeBSD versus Linux > 2.6. > > Thanks. > > []'s > Carlos Eduardo Smanioto (Brazil) > > - Original Message - > From: "Christopher Browne" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, February 05, 2004 3:32 AM > Subject: Re: [PERFORM] 7.3 vs 7.4 performance > > >> Oops! [EMAIL PROTECTED] (Orion Henry) was seen spray-painting on a > wall: >> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30% >> > slower than 7.3.4. Is this common knowledge or am I just unlucky with >> > my query/data selection? >> >> That seems unusual; the opposite seems more typical in view of there >> being some substantial improvements to the query optimizer. >> >> Have you tried doing EXPLAIN ANALYZE on the queries on both sides? >> There would doubtless be interest in figuring out what is breaking >> down... >> >> > Things of note that might matter: the machine is a dual Opteron >> > 1.4GHz running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from >> > the Fedora distro and the 7.4.1 was the PGDG package. The database >> > is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram. >> > >> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel >> > has more than DOUBLED the speed of all my Postgres queries over the >> > 2.4. =) >> >> I did some heavy-transaction-oriented tests recently on somewhat >> heftier quad-Xeon hardware, and found little difference between 2.4 >> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9. >> Now, I'm quite sure my load was rather different from yours, but I >> find the claim of doubling of speed rather surprising. >> -- >> (format nil "[EMAIL PROTECTED]" "aa454" "freenet.carleton.ca") >> http://www.ntlug.org/~cbbrowne/spiritual.html >> Failure is not an option. It comes bundled with your Microsoft product. >> >> ---(end of broadcast)--- >> TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/docs/faqs/FAQ.html > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
