Re: [PERFORM] really quick multiple inserts can use COPY?
Jens Schipkowski wrote:
Thanks a lot to all for your tips.
Of course, I am doing all the INSERTs using a transaction. So the cost
per INSERT dropped from 30 ms to 3 ms.
The improvement factor matches with the hint by Brian Hurt.
Sorry, I forgot to mention we are using PostgreSQL 8.1.4.
Thanks for the code snippet posted by mallah. It looks like you are
using prepared statements, which are not available to us.
But I will check our database access if its possible to do a
workaround, because this looks clean and quick to me.
regards
Jens Schipkowski
On Mon, 11 Dec 2006 17:53:52 +0100, Guillaume Cottenceau [EMAIL PROTECTED]
wrote:
Jens Schipkowski jens.schipkowski 'at' apus.co.at writes:
Hello!
In our JAVA application we do multiple inserts to a table by data from
a Hash Map. Due to poor database access implemention - done by
another company (we got the job to enhance the software) - we cannot
use prepared statements. (We are not allowed to change code at
database access!)
First, we tried to fire one INSERT statement per record to insert.
This costs 3 ms per row which is to slow because normally we insert
10.000 records which results in 30.000 ms just for inserts.
for(){
sql = INSERT INTO tblfoo(foo,bar)
VALUES(+it.next()+,+CONST.BAR+);;
}
You should try to wrap that into a single transaction. PostgreSQL
waits for I/O write completion for each INSERT as it's
implicitely in its own transaction. Maybe the added performance
would be satisfactory for you.
--**
APUS Software GmbH
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
This link might be what you are looking for, it has some information
about implementing COPY in the JDBC driver. Check the reply message as
well.
http://archives.postgresql.org/pgsql-jdbc/2005-04/msg00134.php
Another solution might be to have Java dump the contents of the HashMap
to a CVS file and have it load through psql with COPY commands.
Good luck,
Nick
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] New to PostgreSQL, performance considerations
Mike, are you using -mtune/-mcpu or -march with GCC? Witch GCC version? Are you working with a 32bits OS or 64bits? Daniel On 12/11/06, Michael Stone [EMAIL PROTECTED] wrote: Can anyone else reproduce these results? I'm on similar hardware (2.5GHz P4, 1.5G RAM) and my test results are more like this: (postgresql 8.2.0) CFLAGS=(default) tps = 527.300454 (including connections establishing) tps = 528.898671 (excluding connections establishing) tps = 517.874347 (including connections establishing) tps = 519.404970 (excluding connections establishing) tps = 534.934905 (including connections establishing) tps = 536.562150 (excluding connections establishing) CFLAGS=686 tps = 525.179375 (including connections establishing) tps = 526.801278 (excluding connections establishing) tps = 557.821136 (including connections establishing) tps = 559.602414 (excluding connections establishing) tps = 532.142941 (including connections establishing) tps = 533.740209 (excluding connections establishing) CFLAGS=pentium4 tps = 518.869825 (including connections establishing) tps = 520.394341 (excluding connections establishing) tps = 537.759982 (including connections establishing) tps = 539.402547 (excluding connections establishing) tps = 538.522198 (including connections establishing) tps = 540.200458 (excluding connections establishing) Mike Stone ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Low throughput of binary inserts from windows to linux
On 12/12/06, Tom Lane [EMAIL PROTECTED] wrote: Axel Waggershauser [EMAIL PROTECTED] writes: I tested different sizes on linux some time ago and found that 64KB was optimal. But playing with different sizes again revealed that my windows-linux problem seems to be solved if I use _any_ other (reasonable - meaning something between 4K and 512K) power of two ?!? I think this almost certainly indicates a Nagle/delayed-ACK interaction. I googled and found a nice description of the issue: http://www.stuartcheshire.org/papers/NagleDelayedAck/ But that means I must have misinterpreted fe-connect.c, right? Meaning on the standard windows build the setsockopt(conn-sock, IPPROTO_TCP, TCP_NODELAY, (char *) on, sizeof(on)) line gets never called (eather because TCP_NODELAY is not defined or IS_AF_UNIX(addr_cur-ai_family) in PQconnectPoll evaluates to true). In case I was mistaken, this explanation makes perfectly sens to me. But then again it would indicate a 'bug' in libpq, in the sense that it (apparently) sets TCP_NODELAY on linux but not on windows. Axel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
On 12.12.2006, at 02:37, Michael Stone wrote: Can anyone else reproduce these results? I'm on similar hardware (2.5GHz P4, 1.5G RAM) and my test results are more like this: I'm on totally different hardware / software (MacBook Pro 2.33GHz C2D) and I can't reproduce the tests. I have played with a lot of settings in the CFLAGS including -march and -O3 and -O2 - there is no significant difference in the tests. With fsync=off I get around 2100tps on average with all different settings I have tested. I tried to get the rest of the setup as similar to the described on ty Daniel as possible. It might be that the crappy Pentium 4 needs some special handling, but I can't get the Core 2 Duo in my laptop produce different tps numbers with the different optimizations. Btw: best results were 2147 with -march=i686 and 2137 with - march=nocona. Both with -O3. cug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] New to PostgreSQL, performance considerations
On Dec 11, 2006, at 23:22 , Daniel van Ham Colchete wrote: I ran this test at a Gentoo test machine I have here. It's a Pentium 4 3.0GHz (I don't know witch P4) Try cat /proc/cpuinfo. TESTS RESULTS == On a dual-core Opteron 280 with 4G RAM with an LSI PCI-X Fusion-MPT SAS controller, I am getting wildly uneven results: tps = 264.775137 (excluding connections establishing) tps = 160.365754 (excluding connections establishing) tps = 151.967193 (excluding connections establishing) tps = 148.010349 (excluding connections establishing) tps = 260.973569 (excluding connections establishing) tps = 144.693287 (excluding connections establishing) tps = 148.147036 (excluding connections establishing) tps = 259.485717 (excluding connections establishing) I suspect the hardware's real maximum performance of the system is ~150 tps, but that the LSI's write cache is buffering the writes. I would love to validate this hypothesis, but I'm not sure how. Alexander. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] New to PostgreSQL, performance considerations
On Tue, Dec 12, 2006 at 01:35:04AM -0500, Greg Smith wrote: These changes could easily explain the magnitude of difference in results you're seeing, expecially when combined with a 20% greater raw CPU clock. I'm not interested in comparing the numbers between the systems (which is obviously pointless); I am intested in the fact that there was a consistent difference among the numbers on his system (based on difference optimizations) and no difference among mine. Mike Stone ---(end of broadcast)--- TIP 1: 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] New to PostgreSQL, performance considerations
On Tue, Dec 12, 2006 at 07:10:34AM -0200, Daniel van Ham Colchete wrote: are you using -mtune/-mcpu or -march with GCC? I used exactly the options you said you used. Witch GCC version? Are you working with a 32bits OS or 64bits? 3.3.5; 32 Mike Stone ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
On Tue, Dec 12, 2006 at 12:29:29PM +0100, Alexander Staubo wrote: I suspect the hardware's real maximum performance of the system is ~150 tps, but that the LSI's write cache is buffering the writes. I would love to validate this hypothesis, but I'm not sure how. With fsync off? The write cache shouldn't really matter in that case. (And for this purpose that's probably a reasonable configuration.) Mike Stone ---(end of broadcast)--- TIP 1: 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] New to PostgreSQL, performance considerations
Luke Lonergan wrote: Can you try this with just -O3 versus -O2? Thanks to Daniel for doing these tests. I happen to have done the same tests about 3/4 years ago, and concluded that gcc flags did *not* influence performance. Moved by curiosity, I revamped those tests now on a test machine (single P4 @ 3.2 Ghz, with 2Mb cache and 512 Mb Ram). Here are the results: http://www.streppone.it/cosimo/work/pg/gcc.png In short: tests executed with postgresql 8.2.0, gcc version 3.4.3 20041212 (Red Hat 3.4.3-9.EL4), tps figures computed as average of 9 pgbench runs (don't ask why 9... :-), with exactly the same commands given by Daniel: -O0 ~ 957 tps -O1 -mcpu=pentium4 -mtune=pentium4 ~ 1186 tps -O2 -mcpu=pentium4 -mtune=pentium4 ~ 1229 tps -O3 -mcpu=pentium4 -mtune=pentium4 ~ 1257 tps -O6 -mcpu=pentium4 -mtune=pentium4 ~ 1254 tps I'm curious now to get the same tests run with a custom-cflags-compiled glibc. -- Cosimo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] New to PostgreSQL, performance considerations
On Tue, Dec 12, 2006 at 01:42:06PM +0100, Cosimo Streppone wrote: -O0 ~ 957 tps -O1 -mcpu=pentium4 -mtune=pentium4 ~ 1186 tps -O2 -mcpu=pentium4 -mtune=pentium4 ~ 1229 tps -O3 -mcpu=pentium4 -mtune=pentium4 ~ 1257 tps -O6 -mcpu=pentium4 -mtune=pentium4 ~ 1254 tps For the record, -O3 = -O6 for regular gcc. It used to matter for pgcc, but that is hardly in use anymore. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] New to PostgreSQL, performance considerations
On Tue, Dec 12, 2006 at 01:42:06PM +0100, Cosimo Streppone wrote: -O0 ~ 957 tps -O1 -mcpu=pentium4 -mtune=pentium4 ~ 1186 tps -O2 -mcpu=pentium4 -mtune=pentium4 ~ 1229 tps -O3 -mcpu=pentium4 -mtune=pentium4 ~ 1257 tps -O6 -mcpu=pentium4 -mtune=pentium4 ~ 1254 tps I'm curious now to get the same tests run with a custom-cflags-compiled glibc. I'd be curious to see -O2 with and without the arch-specific flags, since that's mostly what the discussion is about. Mike Stone ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
On Tue, Dec 12, 2006 at 07:48:06AM -0500, Michael Stone wrote: I'd be curious to see -O2 with and without the arch-specific flags, since that's mostly what the discussion is about. That came across more harshly than I intended; I apologize for that. It's certainly a useful data point to compare the various optimization levels. I'm rerunning the tests with gcc 4.1.2 and another platform to see if that makes any difference. Mike Stone ---(end of broadcast)--- TIP 1: 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] New to PostgreSQL, performance considerations
On Mon, 2006-12-11 at 20:22 -0200, Daniel van Ham Colchete wrote: I'm thinking about writing a script to make all the tests (more than 3 times each), get the data and plot some graphs. I don't have the time right now to do it, maybe next week I'll have. Check out the OSDL test suite stuff. It runs the test and handles all the reporting for you (including graphs). http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/ I invite everyone to comment/sugest on the procedure or the results. I'd recommend running each test for quite a bit longer. Get your buffers dirty and exercised, and see what things look like then. Also, if you have the disk, offload your wal files to a separate disk. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] New to PostgreSQL, performance considerations
On Dec 12, 2006, at 13:32 , Michael Stone wrote: On Tue, Dec 12, 2006 at 12:29:29PM +0100, Alexander Staubo wrote: I suspect the hardware's real maximum performance of the system is ~150 tps, but that the LSI's write cache is buffering the writes. I would love to validate this hypothesis, but I'm not sure how. With fsync off? The write cache shouldn't really matter in that case. (And for this purpose that's probably a reasonable configuration.) No, fsync=on. The tps values are similarly unstable with fsync=off, though -- I'm seeing bursts of high tps values followed by low-tps valleys, a kind of staccato flow indicative of a write caching being filled up and flushed. Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] New to PostgreSQL, performance considerations
1= In all these results I'm seeing, no one has yet reported what their physical IO subsystem is... ...when we are benching a DB. 2= So far we've got ~ a factor of 4 performance difference between Michael Stone's 1S 1C Netburst era 2.5GHz P4 PC and Guido Neitzer's 1S 2C MacBook Pro 2.33GHz C2D. If the physical IO subsystems are even close to equivalent across the systems benched so far, we've clearly established that pg performance is more sensitive to factors outside the physical IO subsystem than might usually be thought with regard to a DBMS. (At least for this benchmark SW.) 3= Daniel van Ham Colchete is running Gentoo. That means every SW component on his box has been compiled to be optimized for the HW it is running on. There may be a combination of effects going on for him that others not running a system optimized from the ground up for its HW do not see. 4= If we are testing arch specific compiler options and only arch specific compiler options, we should remove the OS as a variable. Since Daniel has presented evidence in support of his hypothesis, the first step should be to duplicate his environment as =exactly= as possible and see if someone can independently reproduce the results when the only significant difference is the human involved. This will guard against procedural error in the experiment. Possible Outcomes A= Daniel made a procedural error. We all learn what is and to avoid it. B= The Gentoo results are confirmed but no other OS shows this effect. Much digging ensues ;-) C= Daniel's results are confirmed as platform independent once we take all factor into account properly We all learn more re: how to best set up pg for highest performance. Ron Peacetree At 01:35 AM 12/12/2006, Greg Smith wrote: On Mon, 11 Dec 2006, Michael Stone wrote: Can anyone else reproduce these results? I'm on similar hardware (2.5GHz P4, 1.5G RAM)... There are two likely candidates for why Daniel's P4 3.0GHz significantly outperforms your 2.5GHz system. 1) Most 2.5GHZ P4 processors use a 533MHz front-side bus (FSB); most 3.0GHZ ones use an 800MHz bus. 2) A typical motherboard paired with a 2.5GHz era processor will have a single-channel memory interface; a typical 3.0GHZ era board supports dual-channel DDR. These changes could easily explain the magnitude of difference in results you're seeing, expecially when combined with a 20% greater raw CPU clock. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] New to PostgreSQL, performance considerations
* Cosimo Streppone: -O0 ~ 957 tps -O1 -mcpu=pentium4 -mtune=pentium4 ~ 1186 tps -O2 -mcpu=pentium4 -mtune=pentium4 ~ 1229 tps -O3 -mcpu=pentium4 -mtune=pentium4 ~ 1257 tps -O6 -mcpu=pentium4 -mtune=pentium4 ~ 1254 tps -mcpu and -mtune are synonymous. You really should -march here (but the result is non-generic code). Keep in mind that GCC does not contain an instruction scheduler for the Pentium 4s. I also believe that the GCC switches are not fine-grained enough to cover the various Pentium 4 variants. For instance, some chips don't like the CMOV instruction at all, but others can process it with decent speed. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
In response to Ron [EMAIL PROTECTED]: 3= Daniel van Ham Colchete is running Gentoo. That means every SW component on his box has been compiled to be optimized for the HW it is running on. There may be a combination of effects going on for him that others not running a system optimized from the ground up for its HW do not see. http://www.potentialtech.com/wmoran/source.php You get an idea of how old these tests are by the fact that the latest and greatest was FreeBSD 4.9 at the time, but I suppose it may still be relevent. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
Alexander Staubo [EMAIL PROTECTED] writes: No, fsync=on. The tps values are similarly unstable with fsync=off, though -- I'm seeing bursts of high tps values followed by low-tps valleys, a kind of staccato flow indicative of a write caching being filled up and flushed. It's notoriously hard to get repeatable numbers out of pgbench :-( A couple of tips: * don't put any faith in short runs. I usually use -t 1000 plus -c whatever. * make sure you loaded the database (pgbench -i) with a scale factor (-s) at least equal to the maximum -c you want to test. Otherwise you're mostly measuring update contention. * pay attention to when checkpoints occur. You probably need to increase checkpoint_segments if you want pgbench not to be checkpoint-bound. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
On 12/12/06, Florian Weimer [EMAIL PROTECTED] wrote: * Cosimo Streppone: -O0 ~ 957 tps -O1 -mcpu=pentium4 -mtune=pentium4 ~ 1186 tps -O2 -mcpu=pentium4 -mtune=pentium4 ~ 1229 tps -O3 -mcpu=pentium4 -mtune=pentium4 ~ 1257 tps -O6 -mcpu=pentium4 -mtune=pentium4 ~ 1254 tps -mcpu and -mtune are synonymous. You really should -march here (but the result is non-generic code). Keep in mind that GCC does not contain an instruction scheduler for the Pentium 4s. I also believe that the GCC switches are not fine-grained enough to cover the various Pentium 4 variants. For instance, some chips don't like the CMOV instruction at all, but others can process it with decent speed. You can use -march=pentium4, -march=prescott and -march=nocona to the different Pentium4 processors. But you have to use -march (and not -mcpu or -mtune) because without it you are still using only i386 instructions. Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] New to PostgreSQL, performance considerations
Alexander Staubo wrote: No, fsync=on. The tps values are similarly unstable with fsync=off, though -- I'm seeing bursts of high tps values followed by low-tps valleys, a kind of staccato flow indicative of a write caching being filled up and flushed. Databases with checkpointing typically exhibit this cyclical throughput syndrome. (put another way : this is to be expected and you are correct that it indicates buffered data being flushed to disk periodically). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
On Tue, 12 Dec 2006, Tom Lane wrote: Um, you entirely missed the point: the hardware speedups you mention are quite independent of any compiler options. The numbers we are looking at are the relative speeds of two different compiles on the same hardware, not whether hardware A is faster than hardware B. The point that I failed to make clear is that expecting Mike's system to perform like Daniel's just because they have similar processors isn't realistic, considering the changes that happened in the underlying hardware during that period. Having very different memory subsystems will shift which optimizations are useful and which have minimal impact even if the processor is basically the same. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
Tom Lane wrote: Alexander Staubo [EMAIL PROTECTED] writes: No, fsync=on. The tps values are similarly unstable with fsync=off, though -- I'm seeing bursts of high tps values followed by low-tps valleys, a kind of staccato flow indicative of a write caching being filled up and flushed. It's notoriously hard to get repeatable numbers out of pgbench :-( A couple of tips: * don't put any faith in short runs. I usually use -t 1000 plus -c whatever. * make sure you loaded the database (pgbench -i) with a scale factor (-s) at least equal to the maximum -c you want to test. Otherwise you're mostly measuring update contention. * pay attention to when checkpoints occur. You probably need to increase checkpoint_segments if you want pgbench not to be checkpoint-bound. While skimming over the pgbench source it has looked to me like it's necessary to pass the -s switch (scale factor) to both the initialization (-i) and the subsequent (non -i) runs. I'm not sure if this is obvious from the documentation but I thought it may be useful to mention. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Low throughput of binary inserts from windows to linux
Axel Waggershauser [EMAIL PROTECTED] writes: On 12/12/06, Tom Lane [EMAIL PROTECTED] wrote: I think this almost certainly indicates a Nagle/delayed-ACK interaction. I googled and found a nice description of the issue: http://www.stuartcheshire.org/papers/NagleDelayedAck/ In case I was mistaken, this explanation makes perfectly sens to me. But then again it would indicate a 'bug' in libpq, in the sense that it (apparently) sets TCP_NODELAY on linux but not on windows. No, it would mean a bug in Windows in that it fails to honor TCP_NODELAY. Again, given that you only see the behavior at one specific message length, I suspect this is a corner case rather than a generic it doesn't work issue. We're pretty much guessing though. Have you tried tracing the traffic with a packet sniffer to see what's really happening at different message sizes? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] New to PostgreSQL, performance considerations
On Tue, 12 Dec 2006, Alvaro Herrera wrote: While skimming over the pgbench source it has looked to me like it's necessary to pass the -s switch (scale factor) to both the initialization (-i) and the subsequent (non -i) runs. For non-custom runs, it's computed based on the number of branches. Around line 1415 you should find: res = PQexec(con, select count(*) from branches); ... scale = atoi(PQgetvalue(res, 0, 0)); So it shouldn't be required during the run, just the initialization. However, note that there were some recent bug fixes to the scaling implementation, and I would recommend using the version that comes with 8.2 (pgbench 1.58 2006/10/21). It may compile fine even if you copy that pgbench.c into an older version's contrib directory; it's certainly a drop-in replacement (and improvement) for the pgbench 1.45 that comes with current Postgres 8.1 versions. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Low throughput of binary inserts from windows to linux
Tom Lane wrote: In case I was mistaken, this explanation makes perfectly sens to me. But then again it would indicate a 'bug' in libpq, in the sense that it (apparently) sets TCP_NODELAY on linux but not on windows. No, it would mean a bug in Windows in that it fails to honor TCP_NODELAY. Last time I did battle with nagle/delayed ack interaction in windows (the other end has to be another stack implementation -- windows to itself I don't think has the problem), it _did_ honor TCP_NODELAY. That was a while ago (1997) but I'd be surprised if things have changed much since then. Basically nagle has to be turned off for protocols like this (request/response interaction over TCP) otherwise you'll sometimes end up with stalls waiting for the delayed ack before sending, which in turn results in very low throughput, per connection. As I remember Windows client talking to Solaris server had the problem, but various other permutations of client and server stack implementation did not.
Re: [PERFORM] New to PostgreSQL, performance considerations
Mike, I'm making some other tests here at another hardware (also Gentoo). I found out that PostgreSQL stops for a while if I change the -t parameter on bgbench from 600 to 1000 and I have ~150 tps instead of ~950tps. I don't know why PostgreSQL stoped, but it was longer than 5 seconds and my disk IO was comsuming 100% of my CPU time during this period. And I'm testing with my fsync turned off. Maybe if you lower your -t rate you are going to see this improvement. Best regards, Daniel On 12/12/06, Michael Stone [EMAIL PROTECTED] wrote: On Tue, Dec 12, 2006 at 07:10:34AM -0200, Daniel van Ham Colchete wrote: are you using -mtune/-mcpu or -march with GCC? I used exactly the options you said you used. Witch GCC version? Are you working with a 32bits OS or 64bits? 3.3.5; 32 Mike Stone ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] New to PostgreSQL, performance considerations
Alvaro Herrera [EMAIL PROTECTED] writes: While skimming over the pgbench source it has looked to me like it's necessary to pass the -s switch (scale factor) to both the initialization (-i) and the subsequent (non -i) runs. No, it's not supposed to be, and I've never found it needed in practice. The code seems able to pull the scale out of the database (I forget how it figures it out exactly). regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] New to PostgreSQL, performance considerations
Daniel van Ham Colchete [EMAIL PROTECTED] writes: I'm making some other tests here at another hardware (also Gentoo). I found out that PostgreSQL stops for a while if I change the -t parameter on bgbench from 600 to 1000 and I have ~150 tps instead of ~950tps. I don't know why PostgreSQL stoped, but it was longer than 5 seconds and my disk IO was comsuming 100% of my CPU time during this period. Checkpoint? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] New to PostgreSQL, performance considerations
At 10:47 AM 12/12/2006, Tom Lane wrote: It's notoriously hard to get repeatable numbers out of pgbench :-( That's not a good characteristic in bench marking SW... Does the ODSL stuff have an easier time getting reproducible results? A couple of tips: * don't put any faith in short runs. I usually use -t 1000 plus -c whatever. * make sure you loaded the database (pgbench -i) with a scale factor (-s) at least equal to the maximum -c you want to test. Otherwise you're mostly measuring update contention. * pay attention to when checkpoints occur. You probably need to increase checkpoint_segments if you want pgbench not to be checkpoint-bound. This all looks very useful. Can you give some guidance as to what checkpoint_segments should be increased to? Do the values you are running pgbench with suggest what value checkpoint_segments should be? Ron Peacetree ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
I just made another test with a second Gentoo machine: Pentium 4 3.0Ghz Prescott GCC 4.1.1 Glibc 2.4 PostgreSQL 8.1.5 Kernel 2.6.17 Same postgresql.conf as yesterday's. First test == GLIBC: -O2 -march=i686 PostgreSQL: -O2 -march=i686 Results: 974.638731 975.602142 975.882051 969.142503 992.914167 983.467131 983.231575 994.901330 970.375221 978.377467 Average (error): 980 tps (13 tps) Second test === GLIBC: -O2 -march=i686 PostgreSQL: -O2 -march=prescott Results: 988.319643 976.152973 1006.482553 992.431322 983.090838 992.674065 989.216746 990.897615 987.129802 975.907955 Average (error): 988 tps (15 tps) Third test == GLIBC: -O2 -march=prescott PostgreSQL: -O2 -march=i686 Results: 969.085400 966.187309 994.882325 968.715150 956.766771 970.151542 960.090571 967.680628 986.568462 991.756520 Average (error): 973 tps (19 tps) Forth test == GLIBC: -O2 -march=prescott PostgreSQL: -O2 -march=prescott Results: 980.888371 978.128269 969.344669 978.021509 979.256603 993.236457 984.078399 981.654834 976.295925 969.796277 Average (error): 979 tps (11 tps) The results showed no significant change. The conclusion of today's test would be that there are no improvement at PostgreSQL when using -march=prescott. I only see 3 diferences between yesterday's server and today's: the kernel version (y: 2.6.18, t:2.6.17), the server uses an IDE harddrive (yesterday was SATA), and the gcc version (3.4.6 - 4.1.1). I don't know why yesterday we had improved and today we had not. Best Daniel On 12/12/06, Daniel van Ham Colchete [EMAIL PROTECTED] wrote: I'm making some other tests here at another hardware (also Gentoo). I found out that PostgreSQL stops for a while if I change the -t parameter on bgbench from 600 to 1000 and I have ~150 tps instead of ~950tps. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] New to PostgreSQL, performance considerations
At 01:35 PM 12/12/2006, Daniel van Ham Colchete wrote: I just made another test with a second Gentoo machine: snip The results showed no significant change. The conclusion of today's test would be that there are no improvement at PostgreSQL when using -march=prescott. I only see 3 diferences between yesterday's server and today's: the kernel version (y: 2.6.18, t:2.6.17), the server uses an IDE harddrive (yesterday was SATA), and the gcc version (3.4.6 - 4.1.1). I don't know why yesterday we had improved and today we had not. SATA HD's, particularly SATA II HD's and _especially_ 10Krpm 150GB SATA II Raptors are going to have far better performance than older IDE HDs. Do some raw bonnie++ benches on the two systems. If the numbers from bonnie++ are close to those obtained during the pgbench runs, then the HDs are limiting pgbench. Best would be to use the exact same HD IO subsystem on both boxes, but that may not be feasible. In general, it would be helpful if the entire config, HW + OS + pg stuff, was documented when submitting benchmark results. (For instance, it would not be outside the realm of plausibility for Guidos C2D laptop to be HD IO limited and for Micheal's 2.5 GHZ P4 PC to be CPU limited during pgbench runs.) Ron Peacetree ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] New to PostgreSQL, performance considerations
I just made another test with a second Gentoo machine: Pentium 4 3.0Ghz Prescott GCC 4.1.1 Glibc 2.4 PostgreSQL 8.1.5 Kernel 2.6.17 Same postgresql.conf as yesterday's. First test == GLIBC: -O2 -march=i686 PostgreSQL: -O2 -march=i686 Results: 974.638731 975.602142 975.882051 969.142503 992.914167 983.467131 983.231575 994.901330 970.375221 978.377467 Average (error): 980 tps (13 tps) Do you have any scripts for the above process you could share with the list? I have a few machines (a woodcrest and an older Xeon) that I could run this on... Thanks, Bucky ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
On Tue, 12 Dec 2006, Daniel van Ham Colchete wrote: I'm making some other tests here at another hardware (also Gentoo). I found out that PostgreSQL stops for a while if I change the -t parameter on bgbench from 600 to 1000 and I have ~150 tps instead of ~950tps. Sure sounds like a checkpoint to me; the ones pgbench generates really aren't fun to watch when running against IDE drives. I've seen my test system with 2 IDE drives pause for 15 seconds straight to process one when fsync is on, caching was disabled on the WAL disk, and the shared_buffer cache is large. If you were processing 600 transactions/client without hitting a checkpoint but 1000 is, try editing your configuration file, double checkpoint_segments, restart the server, and then try again. This is cheating but will prove the source of the problem. This kind of behavior is what other list members were trying to suggest to you before: once you get disk I/O involved, that drives the performance characteristics of so many database operations that small improvements in CPU optimization are lost. Running the regular pgbench code is so wrapped in disk writes that it's practically a worst-case for what you're trying to show. I would suggest that you run all your optimization tests with the -S parameter to pgbench that limits it to select statements. That will let you benchmark whether the core code is benefitting from the CPU improvements without having disk I/O as the main driver of performance. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
[EMAIL PROTECTED] (Alexander Staubo) wrote: On Dec 12, 2006, at 13:32 , Michael Stone wrote: On Tue, Dec 12, 2006 at 12:29:29PM +0100, Alexander Staubo wrote: I suspect the hardware's real maximum performance of the system is ~150 tps, but that the LSI's write cache is buffering the writes. I would love to validate this hypothesis, but I'm not sure how. With fsync off? The write cache shouldn't really matter in that case. (And for this purpose that's probably a reasonable configuration.) No, fsync=on. The tps values are similarly unstable with fsync=off, though -- I'm seeing bursts of high tps values followed by low-tps valleys, a kind of staccato flow indicative of a write caching being filled up and flushed. If that seems coincidental with checkpoint flushing, that would be one of the notable causes of that sort of phenomenon. You could get more readily comparable numbers either by: a) Increasing the frequency of checkpoint flushes, so they would be individually smaller, or b) Decreasing the frequency so you could exclude it from the time of the test. -- output = (cbbrowne @ gmail.com) http://cbbrowne.com/info/slony.html It can be shown that for any nutty theory, beyond-the-fringe political view or strange religion there exists a proponent on the Net. The proof is left as an exercise for your kill-file. -- Bertil Jonell ---(end of broadcast)--- TIP 6: explain analyze is your friend
