Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
Andrew McMillan wrote: > mlw wrote: > > > > My music database has 50,000 arises and 210,000 albums. Many artists > > have only one or 2 entries in the albums table (for the youngsters, CD > > table ;-). About 34,000 have the integer key for "Various Artists" as > > their artist entry, and another few thousand have things like "Movie > > Soundtrack" and so on. > > > > When the statistics are computed, these relatively few records with a > > huge number of relations distort the statistics and make it impossible > > to get postgres to use an index on that table without the -fs switch. > > > > This is bad because it always forces use of an index, even when postgres > > would legitimately ignore it. > > What about doing: > SET enable_seqscan TO 'Off'; > Just before the query in question? > > That way you'd only affect the single query. Possibly you could even > code to spot the two aberrant situations and not do it in those ones. I'd rather not pollute the application's SQL with postgres-isms. Not that I don't love postgres, but there are always critics looking for a reason to use Oracle or (gasp) MS-SQL. As for "code to spot.." I am fairly new to hacking postgres. (Though, I have been using it in various projects since ~1995), but I am excellent C/C++ guy, give me a pointer to where (a) statistics are calculated, and (b) where they are interpreted, and I would do that. Just a question, however, what is the feeling about the way statistics are currently being calculated? My feeling is that some sort of windowing algorithm be used to normalize the statistics to the majority of the entries in a table. It could be as simple as discarding the upper and lower 10% of the record stats, and use the remaining 80% for statistics. That would certainly take care of my problem (and others I am sure), and I'd be glad to write it. ;-) > > > Regards, > Andrew. > -- > _ > Andrew McMillan, e-mail: [EMAIL PROTECTED] > Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington > Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Re: [HACKERS] Using Threads?
I have been watching this thread vs non-threaded discussion and am completely with the process-only crew for a couple reasons, but lets look at a few things: The process vs threads benchmark which showed 160us vs 120us, only did the process creation, not the delayed hit of the "copy on write" pages in the new process. Just forking is not as simple as forking, once the forked process starts to work, memory that is not explicitly shared is copied to the new process once it is modified. So this is a hit, possibly a big hit. Threads are far more efficient, it really is hard to debate. I can see a number of reasons why a multithreaded version of a database would be good. Asynchronous I/O perhaps, or even parallel joins, but with that being said, I think stability and work are by far the governing factors. Introducing multiple threads into a non-multithreaded code base invariably breaks everything. So, we want to weight the possible performance gains of multithreads vs all the work and effort to make them work reliably. The question is fundamentally, where are we spending our time? If we are spending our time in context switches, then multithreading may be a way of reducing this, however, in all the applications I have built with postgres, it is always (like most databases) I/O bound or bound by computation. I think the benefits of rewriting code to be multithreaded are seldom worth the work and the risks, unless there is a clear advantage to do so. I think most would agree that any increase in performance gained by going multithreaded would be minimal, and the amount of work to do so would be great.
[HACKERS] OSDL DBT-2 for PostgreSQL
Hi everyone, I've just got our DBT-2 workload (TPC-C derivate) working with PostgreSQL using C stored functions and libpq. I'd love to get some feedback. v0.10 is available on SourceForge at: http://prdownloads.sourceforge.net/osdldbt/dbt2-v0.10.tar.gz?download We keep the source in BitKeeper at: bk://developer.osdl.org/dbt2 For anyone interested in more discussion on our workloads, we have a mailing list setup at: [EMAIL PROTECTED] -- 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) http://www.osdl.org/archive/markw/ ---(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
[HACKERS] PostgreSQL 7.3.4 code coverage with OSDL DBT-2
http://developer.osdl.org/markw/pgsql/7.3.4-1/ I used lcov to generate some fancy webpages that shows code coverage of PostgreSQL 7.3.4 from running our DBT-2 workload (TPC-C derivative) against it. I still have some kinks to work out of the workload, but is this something that would interest folks? -- 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) http://www.osdl.org/archive/markw/ ---(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
[HACKERS] Prelimiary DBT-2 Test results
http://developer.osdl.org/markw/44/ I threw together (kind of sloppily) a web page of the data I was starting to collect for our DBT-2 workload (TPC-C derivative) on PostgreSQL 7.3.4. Keep in mind not much database tuning has been done yet. Feel free to ask any questions. -- 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) http://www.osdl.org/archive/markw/ ---(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: [osdldbt-general] Re: [HACKERS] Prelimiary DBT-2 Test results
On 4 Sep, Manfred Spraul wrote: > [EMAIL PROTECTED] wrote: > >>http://developer.osdl.org/markw/44/ >> >>I threw together (kind of sloppily) a web page of the data I was >>starting to collect for our DBT-2 workload (TPC-C derivative) on >>PostgreSQL 7.3.4. Keep in mind not much database tuning has been done >>yet. Feel free to ask any questions. >> >> >> > The kernel readprofile output is very odd: > sys_ipc receives lots of hits, but that function is a trivial multiplexer. > sys_timedsemop, and try_atomic_semop got 0 hits - that's the main > implementation of sysv semaphores. Could you double check your > readprofile scripts? It looks like I have the system.map correct. I'll certainly keep my eyes open and ask around, but seeing poll_idle and schedule on top seem to suggest it's ok. ---(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: [osdldbt-general] Re: [HACKERS] Prelimiary DBT-2 Test results
On 6 Sep, Manfred Spraul wrote: > Another question: > Is it possible to apply patches to postgresql before a DBT-2 run, or is > only patching the kernel supported? The data I reported is from a test system I'm using in our lab, so I can certainly try patches. The current state of STP only allows patches to the kernel, but we're moving in a direction so that other components, like PostgreSQL can also be patched. There is also another option. You can request hardware resources here at the OSDL and get remote access, and we'd be glad to help out set up our workload, if you want to base your tests with it. If that's something you would be interested in all you have to do is sign up as an associate of the OSDL (free): http://www.osdl.org/lab_activities/be_an_associate.html and propose a project: http://www.osdl.org/lab_activities/lab_projects/propose_a_project.html Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Prelimiary DBT-2 Test results
On 4 Sep, Manfred Spraul wrote: > [EMAIL PROTECTED] wrote: > >>http://developer.osdl.org/markw/44/ >> >>I threw together (kind of sloppily) a web page of the data I was >>starting to collect for our DBT-2 workload (TPC-C derivative) on >>PostgreSQL 7.3.4. Keep in mind not much database tuning has been done >>yet. Feel free to ask any questions. >> >> >> > The kernel readprofile output is very odd: > sys_ipc receives lots of hits, but that function is a trivial multiplexer. > sys_timedsemop, and try_atomic_semop got 0 hits - that's the main > implementation of sysv semaphores. Could you double check your > readprofile scripts? Someone here was kind enough to run a little test comparing profiles from 2.4.20-19 (a redhat kernel) and 2.6.0-test4-mm5. He found that the 2.6.0-test4-mm5 profile lacked sys_timedsemop and try_atomic_semop. Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] More Prelimiary DBT-2 Test Results with PostgreSQL 7.3.4
http://developer.osdl.org/markw/74/ I had a couple of hiccups doubling the database size, but I have results with proper linux kernel profile data now. The increase in database size has decreased the overall performance, as expected... I haven't had the opportunity to try different database parameters, such as effective cache sizes, WAL buffers, etc, yet. I'm trying to track down why I'm attempting to insert duplicate keys into one of the tables, which will hopefully be a simple driver error. But will get back with more data when I can. -- 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])
[HACKERS] Is this a commit problem?
I've been observing a interesting behavior with our DBT-2 workload. It appears that a commit to a transaction is taking some time to occur. I'll try to briefly describe what we're seeing. The transaction goes something like this: 1. BEGIN 2. SELECT d_next_o_id INTO current_o_id FROM district WHERE d_w_id = 1 AND d_id = 8 3. UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = 1 AND d_id = 8 4. INSERT INTO new_order (no_o_id, no_w_id, no_d_id) VALUES (current_o_id, 1, 8) 5. COMMIT/ROLLBACK The transaction is implemented as a C stored function and is called through the liqpq interface with an isolation level of read committed and autocommit set to false. The first pass through this transaction appears to execute correctly, and a COMMIT is executed in step 5. The second pass through reads an old d_next_i_id in step 2. For example, if d_next_o_id is 300 in the first pass, it should also be updated to 301. The next time this transaction is called, it is still 300. Also, on the second instance the transaction is called, step 4 causes the libpq library to throw a 'Cannot insert a duplicate key into unique index pk_new_order' error so my application executes a ROLLBACK, since the no_o_id, no_w_id, no_d_id columns are the primary key in the new_order table. I can verify that the first transaction is eventually committed to the database by examining the data in the database after the test has run. This only occurs about 1% of the time. I'm not sure how else to analyze the situation. Let me know if I can clarify anything or provide any more information. 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] More Prelimiary DBT-2 Test Results with PostgreSQL
On 22 Sep, Greg Stark wrote: > > [EMAIL PROTECTED] writes: > >> http://developer.osdl.org/markw/74/ > > Are those response times in the right unit? 7-10s? Yeah, the database really isn't tuned at all. I've gotten some suggestions off the list that I will be trying. I'll report them as I complete them. > Are these mostly full table scans and big batch updates? The plans (http://developer.osdl.org/markw/74/db/plan0.out) don't show any table scans. They appears to be mostly index scans. There aren't any batch updates. > Personally, I'm more interested in seeing OLTP-oriented benchmarks testing > quick index based transactions in the 20-200ms range, not big i/o-bound batch > transactions. There's certainly a need for both, but I think the former are > much more important to micro-optimize, at least for my needs. Right, and when this thing is tuned better, I expect the transactions to be down in the 20-200ms range. I do have about 70 drives for the database, so it really shouldn't be i/o bound at with the size of the database I'm using now, which is about 20 or 30 GB. 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: [HACKERS] Is this a commit problem?
On 25 Sep, Peter Eisentraut wrote: > [EMAIL PROTECTED] writes: > >> This only occurs about 1% of the time. I'm not sure how else to analyze >> the situation. Let me know if I can clarify anything or provide any >> more information. > > Are you running more than one of these transactions in parallel, > overlapping? > Yes, but each transaction has a unique d_w_id, d_id pair, so there is never any 2 transactions touching the same row in the district table. 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: [HACKERS] More Prelimiary DBT-2 Test Results with PostgreSQL
On 24 Sep, Greg Stark wrote: > [EMAIL PROTECTED] writes: > >> On 22 Sep, Greg Stark wrote: >> > Are those response times in the right unit? 7-10s? >> >> The plans (http://developer.osdl.org/markw/74/db/plan0.out) don't show >> any table scans. They appears to be mostly index scans. There aren't >> any batch updates. > > Would it be easy to generate this file doing "explain analyze" instead? It > would give me a better idea what you're doing since I don't know much about > the data. No problem: http://developer.osdl.org/markw/misc/plana.out > Just looking at the plans I don't see how any of these queries could possibly > take 7s. There are two that could possibly take a few hundred milliseconds, > the others I would expect to be under 100ms. Obviously this is all just a gut > reaction, not having looked at the data, and it would depend on the hardware. > But none of the queries look like they should measuring reaction times in > seconds. I have a 4-way 1.5Ghz Xeon with 256KB cache and hyper-threading enabled and 4GB of memory. Although the disk controllers are reducing my physical memory by about 500MB. I can provide more details if you like. >> Right, and when this thing is tuned better, I expect the transactions to >> be down in the 20-200ms range. I do have about 70 drives for the >> database, so it really shouldn't be i/o bound at with the size of the >> database I'm using now, which is about 20 or 30 GB. > > 70 drives for 20-30G? That's pretty extreme. Yeah, we're starting small but we'll increase the database size as we move along. Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] More Prelimiary DBT-2 Test Results with PostgreSQL
On 25 Sep, Zeugswetter Andreas SB SD wrote: > >> > >> > Are those response times in the right unit? 7-10s? >> > >> > No problem: http://developer.osdl.org/markw/misc/plana.out >> >> Ok, I guess I misunderstood you. These queries are taking 0.5ms - 300ms except >> for the last aggregate query which takes just over 1s. > > Yes, but because this is a benchmark he probably has many clients running > in parallel. > > It would be interesting to see the 'explain analyze' from a session that runs > concurrently with the benchmark. Funny you should mention that, because I generated that 'explain analyze' while I was running the workload. :) Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Is this a commit problem?
On 25 Sep, Gaetano Mendola wrote: > [EMAIL PROTECTED] wrote: > >> 2. SELECT d_next_o_id >>INTO current_o_id >>FROM district >>WHERE d_w_id = 1 >>AND d_id = 8 >> >> 3. UPDATE district >>SET d_next_o_id = d_next_o_id + 1 >>WHERE d_w_id = 1 >>AND d_id = 8 > > I don't know exactly what you are trying to do > but usualy in cases like this, in order to avoid > further problem in the commit phase you ought to > do a SELECT FOR UPDATE instead. That appears to have done the trick. Thanks! Mark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Is this a commit problem?
On 25 Sep, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> I've been observing a interesting behavior with our DBT-2 workload. > > AFAICS the only possible explanation for this is that you aren't > actually waiting for the first transaction to commit before you start > the second one. What is the client doing exactly to issue these > queries? > > regards, tom lane Basically 4 steps: PQexec(dbc->conn, "BEGIN"); PQexec(dbc->conn, "DECLARE mycursor CURSOR FOR SELECT new_order(...)"); PQexec(dbc->conn, "FETCH ALL IN mycursor"); PQexec(dbc->conn, "COMMIT"); I take it PQexec() should wait until the COMMIT finishes? Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is this a commit problem?
On 25 Sep, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> I take it PQexec() should wait until the COMMIT finishes? > > Yeah, it does. Where is the next iteration of the transaction coming > from? > > Another thought occurred to me --- you said you have many parallel > instances of this transaction, but they affect different rows because > they have different keys. Is it possible there's a logic bug that > occasionally allows parallel transactions to be fired with the same key? Yeah, that thought has crossed my mind. I did catch an addition error in my logic that contributed to additional rollbacks. ;) But I did try to be diligent and output everything that was going on in the transaction to make sure there weren't any parallel transactions with the same key. I feel confident that the logic is correct. Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Lock partitions
> Mark Wong <[EMAIL PROTECTED]> writes: >> Curious, I'm still seeing the same behavior. Maybe I'll take another >> snapshot from CVS. > > Hm, maybe I need to try a bit harder here. Does the "not registered" > error happen immediately/reliably for you, or do you need to run the > test awhile? It appears to occur for every order status transaction. You can monitor the transactions by watching dbt2/scripts/output/*/driver/mix.log. A 'o' (lowercase) indicates a successful order status transaction while a 'O' (uppercase) indiciates an unsuccessful transaction. or, perhaps a simplier way is to start the database then: cd dbt2/src ./transaction_test -d localhost -l 5432 -t o That will connect directly to the database and execute an order status transaction. Thanks, Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] continuing daily testing of dbt2 against postgresql
> Mark Wong <[EMAIL PROTECTED]> writes: >> After over a year of problems (old site >> http://developer.osdl.org/markw/postgrescvs/) I have resumed producing >> daily results of dbt-2 against PostgreSQL CVS code with results here: >> http://dbt.osdl.org/dbt2.html > > This is good to hear! I am curious where we are now compared to where > we were a year ago ... do you still have the old data, and is the test > setup still comparable? The test setup is on completely different hardware. I still have the old data and it's accessible, but it'll take a little bit of work to regenerate the links. I'll try to work on that. Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] data on devel code perf dip
> On Tue, 2005-08-16 at 16:07 -0700, Mark Wong wrote: >> On Tue, 16 Aug 2005 18:53:55 -0400 >> Tom Lane <[EMAIL PROTECTED]> wrote: >> >> > Mary Edie Meredith <[EMAIL PROTECTED]> writes: >> > > I'm still very concerned about what I'm seeing in the oprofile: >> > > namely: .CreateLWLocks is the second highest entry for postgres. >> > > http://developer.osdl.org/maryedie/DBT2_PGSQL/59/oprofile.txt >> > >> > This says there's something wrong with your oprofile setup. >> > CreateLWLocks is run *once* at postmaster startup, and it doesn't >> > take all that long. Check to see if maybe your executables are >> > out of sync with what oprofile is looking at? >> >> It is a POWER5 platform, if that has anything to do with it. It >> certainly doesn't look sane, but for user apps oprofile is supposed to >> be getting the symbols directly from the binaries its using. I'm >> inclined to blame the platform support. ;) > > Could it have to do with the fact that I'm running in a Virtual Machine? > Maybe they forgot to change the symbols to reflect the VM's "virtual > physical address" if you get my drift. Who do you know on the IBM side, > Mark, who could help us sort this out? Not likely, that would imply the system knew it was running in a virtual machine. You're not supposed to know you're in a virtual machine. I don't know who at IBM to ask about the hypervisor. As for oprofile, the oprofile list would be a start. Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] fsync method checking
On 18 Mar, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: >> 1) This is an OSS project. Why not just recruit a bunch of people on >> PERFORMANCE and GENERAL to test the 4 different synch methods using real >> databases? No test like reality, I say > > I agree --- that is likely to yield *far* more useful results than > any standalone test program, for the purpose of finding out what > wal_sync_method to use in real databases. However, there's a second > issue here: we would like to move sync/checkpoint responsibility into > the bgwriter, and that requires knowing whether it's valid to let one > process fsync on behalf of writes that were done by other processes. > That's got nothing to do with WAL sync performance. I think that it > would be sensible to make a test program that focuses on this one > specific question. (There has been some handwaving to the effect that > everybody knows this is safe on Unixen, but I question whether the > handwavers have seen the internals of HPUX or AIX for instance; and > besides we need to worry about Windows now.) I could certainly do some testing if you want to see how DBT-2 does. Just tell me what to do. ;) Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PostgreSQL block size vs. LVM2 stripe width
I have some results from DBT-2 testing PostgreSQL with difference block sizes against different lvm stripe widths on Linux. I've found that iostat appears to report more erratic numbers as the block size of the database increases but I'm not able to see any reason for it. I have pg_xlog on a separate set of drives from the rest of the database and was wondering if having different block sizes for the log and the data has been discusses? Or does anyone have any tips for an optimal combination of settings? Here's a summary from an Itanium2 system, where bigger is better: Linux-2.6.3, LVM2 Stripe Width (going across) PostgreSQL BLCKSZ (going down)16 KB 32 KB 64 KB 128 KB 256 KB 512 KB 2 KB261726562652266426672642 4 KB439344864577455745114448 8 KB433744234471457641113642 16 KB 441244954532453629852312 32 KB 370537843886392529362362 Links to more data: http://developer.osdl.org/markw/lvm2/blocks.html Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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 6: Have you searched our list archives? http://archives.postgresql.org
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 1: subscribe and unsubscribe commands go 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL block size vs. LVM2 stripe width
Hi Manfred, On 27 Mar, Manfred Koizar wrote: > Mark, > > how often did you run your tests? Are the results reproduceable? In this case, I've only done 1 per each combination. I've found the results for this test to be reproduceable. > On Fri, 26 Mar 2004 14:00:01 -0800 (PST), [EMAIL PROTECTED] wrote: >>Linux-2.6.3, LVM2 Stripe Width >>(going across) >>PostgreSQL >>BLCKSZ >>(going down)16 KB 32 KB 64 KB 128 KB 256 KB 512 KB >>2 KB261726562652266426672642 >>4 KB439344864577455745114448 >>8 KB433744234471457641113642 >>16 KB 441244954532453629852312 >>32 KB 370537843886392529362362 > > Unless someone can present at least an idea of a theory why a BLCKSZ of > 8 KB is at a local minimum (1 or 2% below the neighbouring values) for > stripe widths up to 64 KB I'm not sure whether we can trust these > numbers. > > Before I hit the send button, I did a quick check of the link you > provided. The links in the table contain the following test numbers: > > 16 KB 32 KB 64 KB 128 KB 256 KB 512 KB > 2 KB 72 71 70 69 66 65 > 4 KB 64 63 62 61 60 58 > 8 KB 54 53 52 51 50 49 > 16 KB79 78 77 76 75 74 > 32 KB86 85 84 83 82 80 > > Does this mean that you first ran all test with 8 KB, then with 4, 2, 16 > and 32 KB BLCKSZ? If so, I suspect that you are measuring the effects > of something different. Yes, that's correct, but why do you suspect that? Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL block size vs. LVM2 stripe width
On 30 Mar, Manfred Koizar wrote: > On Mon, 29 Mar 2004 08:50:42 -0800 (PST), [EMAIL PROTECTED] wrote: >>In this case, I've only done 1 per each combination. I've found the >>results for this test to be reproduceable. > > Pardon? I haven't repeated any runs for each combination, e.g. 1 test with 16kb lvm stripe width and 2kb BLCKSZ, 1 test with 16kb lvm stripe width and 4kb BLCKSZ... Linux-2.6.3, LVM2 Stripe Width BLCKSZ (going down)16 KB 32 KB 64 KB 128 KB 256 KB 512 KB 2 KB261726562652266426672642 4 KB439344864577455745114448 8 KB433744234471457641113642 16 KB 441244954532453629852312 32 KB 370537843886392529362362 > >>> Does this mean that you first ran all test with 8 KB, then with 4, 2, 16 >>> and 32 KB BLCKSZ? If so, I suspect that you are measuring the effects >>> of something different. >> >>Yes, that's correct, but why do you suspect that? > > Gut feelings, hard to put into words. Let me try: > > Nobody really knows what the "optimal" BLCKSZ is. Most probably it > depends on the application, OS, hardware, and other factors. 8 KB is > believed to be a good general purpose BLCKSZ. > > I wouldn't be surprised if 8 KB turns out to be suboptimal in one or the > other case (or even in most cases). But if so, I would expect it to be > either too small or too large. > > In your tests, however, there are three configurations where 8 KB is > slower than both 4 KB and 16 KB. Absent any explanation for this > interesting effect, it is easier to mistrust your numbers. > > If you run your tests in the opposite order, on the same hardware, in > the same freshly formatted partitions, and you get the same results, > that would be an argument in favour of their accurancy. > > Maybe we find out that those 1.5% are just noise. I did reformat each partition between tests. :) When I have tested for repeatability in the past I have found results to fluxuate up to 5%, so I would claim the 1.5% to be noise. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Proposed Query Planner TODO items
On 9 Feb, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> I'll see what I can do about the "explain" and "explain analyze" >> results. I remember in the past that someone said it would be most >> interesting to execute the latter while the test while running, as >> opposed to before or after a test. Should I do that here too? > > If possible, but I'd settle for a standalone result, so long as it's > executed against the correct database contents (including pg_statistic > settings). Ok, I've found that the kit does capture "explain" results and I've added a "Query Plans" links under the query time charts on each of the pages. Um, but I did notice a couple of problems. It looks liks one of the 22 queries is missing and they're not labeled. I'll see about getting that fixed. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Proposed Query Planner TODO items
On 12 Feb, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> Ok, I have EXPLAIN ANALYZE results for both the power and throughput >> tests: >> http://developer.osdl.org/markw/dbt3-pgsql/ > > Thanks. I just looked at Q9 and Q21, since those are the slowest > queries according to your chart. (Are all the queries weighted the same > for evaluation purposes, or are some more important than others?) > [snip] > > The estimate for the part/partsupp join is close enough (60K vs 90K > rows), but why is it estimating 92 rows out of the join to lineitem when > the true figure is 681518? With a more accurate estimate the planner > would probably have chosen different join methods above this point. > > Can you show us the pg_stats rows for the columns p_partkey, l_partkey, > ps_suppkey, and l_suppkey? > > It would also be interesting to see whether a better estimate emerges > if you increase default_statistics_target (try 100 or so). http://developer.osdl.org/markw/dbt3-pgsql/62/ This run changes default_statistics_target to 1000 and I have p_partkey, l_partkey, ps_suppkey, and l_suppkey pg_stats here at 1 min intervals http (no links on the web page.) Pretty significant performance change. Power: http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/power.l_partkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/power.l_suppkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/power.p_partkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/power.ps_suppkey.out Throughput: http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/thuput.l_partkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/thuput.l_suppkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/thuput.p_partkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/thuput.ps_suppkey.out Something went wrong when I tried to run another test with the Q21 changes overnight, so I'll have to get back to you on that one. Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Status in 7.5 patches
On 16 Jun, Bruce Momjian wrote: > With today being June 16th, we are half-way into the one month extension > of the feature freeze, now scheduled for July 1. Here is the status on > the various outstanding features: > > Tablespaces - This has been in the queue since June 1 and should have > been reviewed and applied by now. We must give this first priority. Would there happen to be a newer patch for tablespaces than tablespace-20.diff.gz? This one has a couple of rejects against today's CVS head. Thanks, 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
[HACKERS] DBT-2 results using tablespaces
Hello, Just wanted to share some data I've generated with dbt-2 using tablespaces from a CVS export of PostgreSQL on June 22. I have results and details on how I have the disks laid out with LVM2 here: http://www.osdl.org/projects/dbt2dev/results/dev4-006/538/ The performance appears to be on par with results I've been getting with PostgreSQL 7.4.1. For a comparison, you can see those results here: http://www.osdl.org/projects/dbt2dev/results/dev4-006/526/ The volume where the database log resides is identicle between the two tests. The disk layout has only changed for where the tables and indexes reside. -- 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) http://developer.osdl.org/markw/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] dbt2-pgsql on OSDL
Hi Manfred, Oopsies, fixed that. I've removed the -r flag. Thanks for catching that. Mark On 6 Jul, Manfred Koizar wrote: > Mark, > > I've tried to run some performance tests on your Scalable Test Platform > but the tests failed at the build step. > > I guess the problem is near line 282 of > http://khack.osdl.org/stp/294734/logs/run-log.txt > > | + wget -nv -t 0 --waitretry=60 -r http://stp/data/dbt-2/postgresql-7.4.tar.gz > | 09:45:01 URL:http://stp/data/dbt-2/postgresql-7.4.tar.gz [12311256/12311256] -> > "stp/data/dbt-2/postgresql-7.4.tar.gz" [1] > | > | FINISHED --09:45:01-- > | Downloaded: 12,311,256 bytes in 1 files > | + tar zxf postgresql-7.4.tar.gz > | tar (child): postgresql-7.4.tar.gz: Cannot open: No such file or directory > | tar (child): Error is not recoverable: exiting now > | tar: Child returned status 2 > | tar: Error exit delayed from previous errors > > Obviously wget -r downloads into a newly created subdirectory structure. > http://khack.osdl.org/stp/294335/logs/run-log.txt which belongs to a > successful test run has > | + wget -nv -t 0 --waitretry=60 -nc http://stp/data/dbt-2/postgresql-7.4.tar.gz > | 21:00:55 URL:http://stp/data/dbt-2/postgresql-7.4.tar.gz [12311256/12311256] -> > "postgresql-7.4.tar.gz" [1] > > Servus > Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Point in Time Recovery
On 14 Jul, Simon Riggs wrote: > PITR Patch v5_1 just posted has Point in Time Recovery working > > Still some rough edgesbut we really need some testers now to give > this a try and let me know what you think. > > Klaus Naumann and Mark Wong are the only [non-committers] to have tried > to run the code (and let me know about it), so please have a look at > [PATCHES] and try it out. > > Many thanks, > > Simon Riggs Simon, I just tried applying the v5_1 patch against the cvs tip today and got a couple of rejections. I'll copy the patch output here. Let me know if you want to see the reject files or anything else: $ patch -p0 < ../../../pitr-v5_1.diff patching file backend/access/nbtree/nbtsort.c Hunk #2 FAILED at 221. 1 out of 2 hunks FAILED -- saving rejects to file backend/access/nbtree/nbtsort.c.rej patching file backend/access/transam/xlog.c Hunk #11 FAILED at 1802. Hunk #15 FAILED at 2152. Hunk #16 FAILED at 2202. Hunk #21 FAILED at 3450. Hunk #23 FAILED at 3539. Hunk #25 FAILED at 3582. Hunk #26 FAILED at 3833. Hunk #27 succeeded at 3883 with fuzz 2. Hunk #28 FAILED at 4446. Hunk #29 succeeded at 4470 with fuzz 2. 8 out of 29 hunks FAILED -- saving rejects to file backend/access/transam/xlog.c.rej patching file backend/postmaster/Makefile patching file backend/postmaster/postmaster.c Hunk #3 succeeded at 1218 with fuzz 2 (offset 70 lines). Hunk #4 succeeded at 1827 (offset 70 lines). Hunk #5 succeeded at 1874 (offset 70 lines). Hunk #6 succeeded at 1894 (offset 70 lines). Hunk #7 FAILED at 1985. Hunk #8 succeeded at 2039 (offset 70 lines). Hunk #9 succeeded at 2236 (offset 70 lines). Hunk #10 succeeded at 2996 with fuzz 2 (offset 70 lines). 1 out of 10 hunks FAILED -- saving rejects to file backend/postmaster/postmaster.c.rej patching file backend/storage/smgr/md.c Hunk #1 succeeded at 162 with fuzz 2. patching file backend/utils/misc/guc.c Hunk #1 succeeded at 342 (offset 9 lines). Hunk #2 succeeded at 1387 (offset 9 lines). patching file backend/utils/misc/postgresql.conf.sample Hunk #1 succeeded at 113 (offset 10 lines). patching file bin/initdb/initdb.c patching file include/access/xlog.h patching file include/storage/pmsignal.h ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Point in Time Recovery
On 26 Jul, To: [EMAIL PROTECTED] wrote: > Sorry I wasn't clearer. I think I have a better idea about what's going > on now. With the archiving enabled, it looks like the database is able > to complete 1 transaction per database connection, but doesn't complete > any subsequent transactions. I'm not sure how to see what's going on. > Perhaps I should try a newer snapshot from CVS while I'm at it? I tried to do an strace on the postmaster (and child processes) to see if that might show something, but when the postmaster starts the database isn't accepting any connections. I have the feeling it's not really starting up. Trying to shut it down seems to agrees with that. My wild guess is that the database is sitting waiting for something when a stored function is called but I'm not sure how to verify that. Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Automated Testing with PostgreSQL-8.0.0beta1
The OSDL provides two services to aid in development processes: The Patch Lifecycle Manager (PLM) and the Scalable Test Platform (STP). Recent releases allow software other than the linux kernel to be actively tested. PLM (http://www.osdl.org/plm-cgi/plm) can be used for storing a series of patches against any source tree or other patches, in this case the database source code. STP (http://www.osdl.org/stp) can be used to test the patches stored in PLM with various tests on 4- and 8-way IA-32 systems. Currently the only test taking advantage of the recent enhancements is DBT-2, an OLTP workload labeled dbt2-pgsql in STP. I'm currently working on getting DBT-3, a decision support workload, working with the recent release of STP. In addition to PLM and STP, the OSDL provides hardware resources free of charge. More information can be found here: http://www.osdl.org/lab_activities/lab_projects/ Let me know if you have any questions. -- 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) http://developer.osdl.org/markw/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] More Prelimiary DBT-2 Test Results with PostgreSQL
On 24 Sep, Greg Stark wrote: > Ok, I guess I misunderstood you. These queries are taking 0.5ms - 300ms except > for the last aggregate query which takes just over 1s. http://developer.osdl.org/markw/dbt2-pgsql/120/ I have more data where I got the response times for each transaction much lower by increasing shared_buffers and wal_buffers. I wanted to let everyone know in case someone wanted to take another look. I'm working on increasing the database size to drive the system harder. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
I thought someone might be interested in a data point I have comparing 7.3.4 and 7.4beta5 with results from our DBT-2 workload. Keep in mind I haven't done much tuning with either version. The following links have references iostat, vmstat, sar, readprofile (linux kernel profile), and oprofile (postgresql profile) statistics. Results from 7.3.4: http://developer.osdl.org/markw/dbt2-pgsql/184/ - metric 1354.58 Results from 7.4beta5 http://developer.osdl.org/markw/dbt2-pgsql/188/ - metric 1446.01 7.4beta5 offers more throughput. One significant difference I see is in the oprofile for the database. For the additional 7% increase in the metric, there are about 32% less ticks in SearchCatCache. These are the only database parameters I've explicitly set for each one, any other differences will be differences in default values: - shared_buffers = 4 - tcpip_socket = true - checkpoint_segments = 200 - checkpoint_timeout = 1800 - stats_start_collector = true - stats_command_string = true - stats_block_level = true - stats_row_level = true - stats_reset_on_server_start = true If anyone has any tuning recommendations for either 7.3 or 7.4, I'll be happy to try them. Or if anyone wants to be able to poke around on the system, we can arrange that too. Feel free to ask any questions. -- 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: Avoiding SIGPIPE (was Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL
On 1 Nov, Tom Lane wrote: > Manfred Spraul <[EMAIL PROTECTED]> writes: >> Tom Lane wrote: >>> What we really want is to turn off SIGPIPE delivery on our socket >>> (only), but AFAIK there is no API to do that. >>> >> Linux has as MSG_NOSIGNAL flag for send(), but that seems to be Linux >> specific. > > Hmm ... a Linux-specific solution would be better than none at all. > > A bigger objection is that we couldn't get libssl to use it (AFAIK). > The flag really needs to be settable on the socket (eg, via fcntl), > not per-send. I'm a bit unfamiliar with this stuff, so I wanted to ask if this was something that Linux appears to be handling differently than other OS's, or if this was a platform specific issue with postgresql. Thanks, 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: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
On 1 Nov, Tom Lane wrote: > Manfred Spraul <[EMAIL PROTECTED]> writes: >> signal handlers are a process property, not a thread property - that >> code is broken for multi-threaded apps. > > Yeah, that's been mentioned before, but I don't see any way around it. > What we really want is to turn off SIGPIPE delivery on our socket > (only), but AFAIK there is no API to do that. Will this be a problem for multi-threaded apps with any of the client interfaces? Anyone working on making it threadsafe? Thanks, 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
[HACKERS] DBT-2 running against PostgreSQL and LVM2
I just wanted to share that we're on the brink of having an automated test on our Scalable Test Platform (http://www.osdl.org/stp/) that features our DBT-2 workload, a fair use implementation of the TPC-C, that uses PostgreSQL 7.4 and LVM2 on Linux on our 8-processor systems. Currently the test only works with a scale factor of 1 warehouse, which amounts to about a 500MB of raw data. You can request tests at this scale factor at the above link and typing "-w 1" in the "Optional Script Parameters" field. We're working through some Linux kernel related issues so we can run more interesting workloads against larger databases. I'm planning to allow an option for either a 100 and 200 warehouse database to be built. I wanted to let people know early for feedback on the kind of data that is reported from the test. You can see a sample of a test report here: http://khack.osdl.org/stp/284353/ 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) http://developer.osdl.org/markw/ ---(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
[HACKERS] more dbt-2 results hyperthreading on linux-2.6.0-test11
Hi Nick, Here are the results of the comparisons I said I would do. no-hyperthreading: http://developer.osdl.org/markw/dbt2-pgsql/282/ - metric 2288.43 - baseline hyperthreading: http://developer.osdl.org/markw/dbt2-pgsql/278/ - metric 1944.42 - 15% throughput decrease hyperthreading w/ Ingo's C1 patch: http://developer.osdl.org/markw/dbt2-pgsql/277/ - metric 1978.39 - 13.5% throughput decrease hyperthreading w/ Nick's w26 patch: http://developer.osdl.org/markw/dbt2-pgsql/274/ - metric 1955.91 - 14.5% throughput decrease It looks like there is some marginal benefit to your or Ingo's patches with a workload like DBT-2. I probably don't understand enough about hyperthreading, but I wonder if there's something PostgreSQL can do to take advantage of hyperthreading Anyway, each link has pointers to readprofile and annotated oprofile assembly output (if you find that useful.) I haven't done enough tests to have an idea of the error margin, but I wouldn't be surprised if it's at least 1%. Let me know if there's anything else you'd like me to try. Thanks, Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] more dbt-2 results hyperthreading on linux-2.6.0-test11
Hi Jun, DBT-2 is a fair use implementation of the TPC-C (OLTP), if you're familiar with that. I have 14 drives attached through 1 megaraid raid controller, and 52 drives connected through 4 channels on 2 mylex raid controllers, all in a raid-0 configuration. I am using LVM2 on both sets of drives. iostat tells me that each of the 14 drives attached through the raid controller are utilized < 2%, while each of the other 52 drives seem to peak at about 48% in one of the hyperthreaded cases (274). I'm not too familiar with the umpteen other columns that iostat reports, but that suggests to me I have a fair amount of i/o headroom. Unfortunitely, I don't believe I can get my hands on any addition drives or controllers... Mark On 12 Dec, Nakajima, Jun wrote: > I'm not familiar with this particular workload, but noticed higher idle > and wait time with HT enabled, compared to HT-disabled case. This kind > of symptom often indicated insufficient I/O bandwidth from my > experience, and faster systems (with more threads) tend to show lower > throughput because you end up measuring disk seek time of more I/O > requests. Can you add more disk controller(s) and disks? > > Jun >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:linux-kernel- >> [EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] >> Sent: Friday, December 12, 2003 2:28 PM >> To: [EMAIL PROTECTED] >> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql- >> [EMAIL PROTECTED] >> Subject: more dbt-2 results hyperthreading on linux-2.6.0-test11 >> >> Hi Nick, >> >> Here are the results of the comparisons I said I would do. >> >> no-hyperthreading: >> http://developer.osdl.org/markw/dbt2-pgsql/282/ >> - metric 2288.43 >> - baseline >> >> hyperthreading: >> http://developer.osdl.org/markw/dbt2-pgsql/278/ >> - metric 1944.42 >> - 15% throughput decrease >> >> hyperthreading w/ Ingo's C1 patch: >> http://developer.osdl.org/markw/dbt2-pgsql/277/ >> - metric 1978.39 >> - 13.5% throughput decrease >> >> hyperthreading w/ Nick's w26 patch: >> http://developer.osdl.org/markw/dbt2-pgsql/274/ >> - metric 1955.91 >> - 14.5% throughput decrease >> >> It looks like there is some marginal benefit to your or Ingo's patches >> with a workload like DBT-2. I probably don't understand enough about >> hyperthreading, but I wonder if there's something PostgreSQL can do to >> take advantage of hyperthreading >> >> Anyway, each link has pointers to readprofile and annotated oprofile >> assembly output (if you find that useful.) I haven't done enough > tests >> to have an idea of the error margin, but I wouldn't be surprised if > it's >> at least 1%. >> >> Let me know if there's anything else you'd like me to try. >> >> Thanks, >> 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
[HACKERS] DBT-2 PostgreSQL on STP w/ LVM2
DBT-2 and PostgreSQL 7.4 is finally working with LVM2 at 100 and 200 warehouse scale factors on 8-processor system in STP with 40 drives (38 managed under LVM), but not without a few catches. I have written up some brief instructions on how to execute the test successfully with the Linux-2.6.0 stable release: http://developer.osdl.org/markw/stp_dbt2_howto.html The instructions also cover how to get oprofile annoted assembly source in the test results. Feel free to ask any questions. -- 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) http://developer.osdl.org/markw/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] using stp for dbt2 + postgresql
Hi Manfred, Just wanted to let you know I tried your patch-spinlock-i386 patch on our STP (our automated test platform) 8-way systems and saw a 5.5% improvement with Pentium III Xeons. If you want to see those results: PostgreSQL 7.4.1: http://khack.osdl.org/stp/285062/ PostgreSQL 7.4.1 w/ your patch: http://khack.osdl.org/stp/285087/ Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Proposed Query Planner TODO items
On 5 Jan, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: >> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES > >> Summary: Currently, queries with complex "or group" criteria get devolved by >> the planner into canonical and-or filters resulting in very poor execution on >> large data sets. We should find better ways of dealing with these queries, >> for example UNIONing. > >> Description: While helping OSDL with their derivative TPC-R benchmark, we ran >> into a query (#19) which took several hours to complete on PostgreSQL. > > I've made some progress on this over the last week or two. Would it be > possible to retry that benchmark with CVS tip? Yeah, no problem. We'll pull the code from CVS and give it a try. Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] DBT-2 pulls PostgreSQL from CVS for STP
I've added a new test parameter so that the test on STP can pull from CVS. When requesting a test, use the flag '-x 1' in the "Optimal Script Parameters" box and the test will pull from CVS. A test I ran the other day failed because the database ran out of shared memory. I was going to try to patch src/backend/storage/ipc/ipci.c to see if I can get around it, but it looks promising otherwise. :) -- 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) http://developer.osdl.org/markw/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] DBT-2 pulls PostgreSQL from CVS for STP
On 14 Jan, Jan Wieck wrote: > Neil Conway wrote: > >> Tom Lane <[EMAIL PROTECTED]> writes: >>> This is a bug Jan introduced recently --- he forgot to modify the >>> shared memory setup code to allow space for the new data structures >>> used by ARC. Jan, would you mind fixing that soon? It's getting in >>> people's way. >> >> Jan had asked that I include the fix for the bug in my bufmgr work >> (which I have), but considering that it will probably take me a while >> to get that into a state that is suitable for committing, it might >> make more sense for Jan to just commit the fix himself. > > K, will do > > > Jan > Ok, it did take me while to try another go at it, but it works. :) You can see some results against today's snapsnot here: http://khack.osdl.org/stp/287106/ And for review, you can see intructions on how to run your own here: http://developer.osdl.org/markw/stp_dbt2_howto.html Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposed Query Planner TODO items
On 5 Jan, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: >> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES > >> Summary: Currently, queries with complex "or group" criteria get devolved by >> the planner into canonical and-or filters resulting in very poor execution on >> large data sets. We should find better ways of dealing with these queries, >> for example UNIONing. > >> Description: While helping OSDL with their derivative TPC-R benchmark, we ran >> into a query (#19) which took several hours to complete on PostgreSQL. > > I've made some progress on this over the last week or two. Would it be > possible to retry that benchmark with CVS tip? > > regards, tom lane Sorry it's taking so long. I tried to take a export from CVS today and the database appears not to be able to connect to the postmaster when I attempt to create the database. Let me know if getting a trace of anything will help, if you guys already aren't already aware of the problem. 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: [HACKERS] Proposed Query Planner TODO items
On 6 Feb, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> Sorry it's taking so long. I tried to take a export from CVS today and >> the database appears not to be able to connect to the postmaster when I >> attempt to create the database. Let me know if getting a trace of >> anything will help, if you guys already aren't already aware of the >> problem. > > CVS tip is not broken to my knowledge. Details please? I ran this: $ strace -o /tmp/initdb-7.5.out initdb -D /opt/pgdb/dbt2 The files belonging to this database system will be owned by user "markw". This user must also own the server process. The database cluster will be initialized with locale C. creating directory /opt/pgdb/dbt2 ... ok creating directory /opt/pgdb/dbt2/global ... ok creating directory /opt/pgdb/dbt2/pg_xlog ... ok creating directory /opt/pgdb/dbt2/pg_clog ... ok creating directory /opt/pgdb/dbt2/base ... ok creating directory /opt/pgdb/dbt2/base/1 ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 1000 creating configuration files ... ok creating template1 database in /opt/pgdb/dbt2/base/1 ... ERROR: relnatts disagrees with indnatts for index 16601 initdb: child process exited with exit code 1 initdb: failed initdb: removing data directory "/opt/pgdb/dbt2" I've never seen this relnatts and indnatts disagreements message before. I'll attach a compressed strace. Thanks, Mark initdb-7.5.out.gz Description: Binary data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Proposed Query Planner TODO items
On 6 Feb, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> creating template1 database in /opt/pgdb/dbt2/base/1 ... ERROR: relnatts disagrees >> with indnatts for index 16601 > > Wow, that's a bizarre one. Are you sure you did a clean rebuild? > I usually like to do "make distclean" before or after "cvs update"; > it tends to save me a lot of wasted time chasing build inconsistencies. > Which is what I suspect this is. > > FWIW, my last CVS pull was yesterday about 15:00 EST, and it works fine. Well, that make distclean did the trick. I actually did an export this morning, not a checkout, but not like that should matter. Ok, will hopefully get back with results soon. Thanks, Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Proposed Query Planner TODO items
On 6 Feb, To: [EMAIL PROTECTED] wrote: > On 5 Jan, Tom Lane wrote: >> Josh Berkus <[EMAIL PROTECTED]> writes: >>> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES >> >>> Summary: Currently, queries with complex "or group" criteria get devolved by >>> the planner into canonical and-or filters resulting in very poor execution on >>> large data sets. We should find better ways of dealing with these queries, >>> for example UNIONing. >> >>> Description: While helping OSDL with their derivative TPC-R benchmark, we ran >>> into a query (#19) which took several hours to complete on PostgreSQL. http://developer.osdl.org/markw/dbt3-pgsql/ There's a short summary of the tests I ran over the weekend, with links to detailed retults. Comparing runs 43 (7.4) and 52 (7.5devel), it looks like query #7 had the only significant improvement. Oprofile data should be there too, if that'll help. Let us know if there's anything else we can try for you. Mark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Proposed Query Planner TODO items
On 9 Feb, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> http://developer.osdl.org/markw/dbt3-pgsql/ > >> There's a short summary of the tests I ran over the weekend, with links >> to detailed retults. Comparing runs 43 (7.4) and 52 (7.5devel), it >> looks like query #7 had the only significant improvement. Oprofile data >> should be there too, if that'll help. Let us know if there's anything >> else we can try for you. > > I couldn't figure out anything at all from that, possibly because many > of the links are dead, eg the "task" descriptions. I don't even see > where you see the time for query #7. > > What would be interesting from my perspective is "explain" results (or > even better, "explain analyze" results) for the problem queries. Any > chance of extracting such a thing? Sorry about the task links, I think I've got that corrected. I'll see what I can do about the "explain" and "explain analyze" results. I remember in the past that someone said it would be most interesting to execute the latter while the test while running, as opposed to before or after a test. Should I do that here too? Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposed Query Planner TODO items
On 9 Feb, Josh Berkus wrote: > Mark, > >> Ok, I've found that the kit does capture "explain" results and I've >> added a "Query Plans" links under the query time charts on each of the >> pages. Um, but I did notice a couple of problems. It looks liks one of >> the 22 queries is missing and they're not labeled. I'll see about >> getting that fixed. > > If #19 is missing it's because Oleg & I could not get it to complete. That > was also the query which we are most interested in testing. Oh, it's probably because we've altered Q19 and Q20. I'm still not all that familiar with this kit, so I'm learning as we go. So we need to change it back to make it worthwhile for you. Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Proposed Query Planner TODO items
Ok, I have EXPLAIN ANALYZE results for both the power and throughput tests: http://developer.osdl.org/markw/dbt3-pgsql/ It's run #60 and the links are towards the bottom of the page under the "Run log data" heading. The results from the power test is "power_query.result" and "thuput_qs1.result", etc. for each stream in the throughput test. Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposed Query Planner TODO items
On 12 Feb, Josh Berkus wrote: > Mark, > >> It's run #60 and the links are towards the bottom of the page under the >> "Run log data" heading. The results from the power test is >> "power_query.result" and "thuput_qs1.result", etc. for each stream in >> the throughput test. > > I'm confused. Were you able to get the original-form query #19 to complete, > or not? Oh sorry, I completely forgot that Q19 the whole purpose of this. So #60 doesn't have the right Q19. I'll run with the one you want now. 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: [HACKERS] Proposed Query Planner TODO items
On 12 Feb, Josh Berkus wrote: > Mark, > >> Oh sorry, I completely forgot that Q19 the whole purpose of this. So >> #60 doesn't have the right Q19. I'll run with the one you want now. > > Thanks! And the original, not the "fixed", Q19 if you please. It's the > original that wouldn't finish on Postgres 7.3. Josh, http://developer.osdl.org/markw/dbt3-pgsql/ Check out #61. I replaced the Q19 template with the one Jenny sent out. Looks like it ran just fine. This run also has the EXPLAIN ANALYZE results, but none of the other things Tom has asked for yet. Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposed Query Planner TODO items
On 16 Feb, Dennis Haney wrote: > [EMAIL PROTECTED] wrote: > >>On 12 Feb, Tom Lane wrote: >> >> >>http://developer.osdl.org/markw/dbt3-pgsql/62/ >> >>This run changes default_statistics_target to 1000 and I have p_partkey, >>l_partkey, ps_suppkey, and l_suppkey pg_stats here at 1 min intervals >>http (no links on the web page.) Pretty significant performance change. >> >> >> > Why the filesystem change to ext2 at the same time? I've been rotating filesystems occasionally. Otherwise no specific reason. Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Proposed Query Planner TODO items
On 15 Feb, Tom Lane wrote: > I wrote: >> I see what is going on to make Q4 slow, too. It's this: >> where o_orderdate >= date '1995-04-01' and o_orderdate < date '1995-04-01' + >> interval '3 month' >> ... >> As of CVS tip the issue could be eliminated by introducing >> cross-data-type comparison operators between types date and timestamp >> without time zone, and then making these be members of the date index >> opclass. I'm strongly tempted to do so ... > > I have now done this, so if you care to re-sync with CVS tip you should > find that the queries using this sort of date constraint go faster. > (You do have indexes on all the date columns, no?) I ran a test with the CAST you recommended for Q4 over the weekend: http://developer.osdl.org/markw/dbt3-pgsql/68/ But it didn't seem to have much of an affect on Q4, compared to run #66. I'll still give the CVS tip a try. 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: [HACKERS] Proposed Query Planner TODO items
On 16 Feb, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> I ran a test with the CAST you recommended for Q4 over the weekend: >> http://developer.osdl.org/markw/dbt3-pgsql/68/ >> But it didn't seem to have much of an affect on Q4, compared to run >> #66. I'll still give the CVS tip a try. > > Hm. Disappointing. I can see from the EXPLAIN results that it is > picking up the additional index constraint correctly in this run. > That should have saved a good number of useless heap fetches. > [ works with the numbers a little... ] Actually, I guess it did: > it looks like the time spent in the indexscan proper went down from > 44msec to 7msec. The problem is that the bulk of the query time is > actually going into the repeated EXISTS() sub-selects, and those didn't > get any better. > > There are some other queries in the set that also have date limits of > this kind, so I still think it's worth redoing a run with CVS tip to > see if we pick up anything overall. (You do have indexes created on > all the date columns no?) > > There's probably no way to make Q4 fly without finding a way to optimize > the EXISTS into an IN-join. I'll put that on my to-do list ... in the > meantime, if you feel like making a run to confirm that theory, try > modifying Q4 to replace > > and exists ( select * from lineitem >where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) > > with > > and o_orderkey in ( select l_orderkey from lineitem > where l_commitdate < l_receiptdate ) > > I think that either 7.4 or CVS tip will do better with this variant, > but it probably ought to be checked. It looks like we have indexes on all of the date columns except l_commitdate, which appears to be in Q4. So I think I'll run against the CVS tip as is, again with an index on l_commitdate, and then another test to confirm your theory. Sound good? 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: [HACKERS] Proposed Query Planner TODO items
On 16 Feb, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> It looks like we have indexes on all of the date columns except >> l_commitdate, which appears to be in Q4. > >> So I think I'll run against the CVS tip as is, again with an index on >> l_commitdate, and then another test to confirm your theory. Sound good? > > Sure, it's only cycles ;-). I am not certain that an index on > commitdate would help any, but it's worth trying. http://developer.osdl.org/markw/dbt3-pgsql/70/ Those are results from a pull from CVS I did this morning. I reverted Q4 (removed the CAST), but the extra WHERE constraints are still in Q21. Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS]
http://developer.osdl.org/markw/ia64/dbt2/ I have a summary of intial results from our DBT-2 workload with PostgreSQL 7.4.1 on a 4-way Itanium2 system with 16GB of memory and 56 drives using LVM2 and linux-2.6.3. There's readprofile and oprofile data, but oprofile is seg faulting when it's trying to generate the annotated assembly source. I'm still trying to get a feel for how large of a database I can run with it, but I wanted to share what I've got so far. It looks like I have quite a bit of unused system resources, so I probably have to tune the database and perhaps how I'm driving the load a bit. Let me know if there are any questions. -- 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) http://developer.osdl.org/markw/ ---(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
