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] [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] [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] 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]
[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] 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
