Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On 07/24/2011 03:50 AM, Jeff Janes wrote: On Mon, Jun 13, 2011 at 7:03 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: On 06/13/2011 01:55 PM, Stefan Kaltenbrunner wrote: [...] all those tests are done with pgbench running on the same box - which has a noticable impact on the results because pgbench is using ~1 core per 8 cores of the backend tested in cpu resoures - though I don't think it causes any changes in the results that would show the performance behaviour in a different light. actuall testing against sysbench with the very same workload shows the following performance behaviour: with 40 threads(aka the peak performance point): pgbench:223308 tps sysbench: 311584 tps with 160 threads (backend contention dominated): pgbench:57075 sysbench: 43437 so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. for those curious - the profile for pgbench looks like: samples %symbol name 2937841.9087 doCustom 1750224.9672 threadRun 7629 10.8830 pg_strcasecmp 5871 8.3752 compareVariables 2568 3.6633 getVariable 2167 3.0913 putVariable 2065 2.9458 replaceVariable 1971 2.8117 parseVariable 534 0.7618 xstrdup 278 0.3966 xrealloc 137 0.1954 xmalloc Hi Stefan, How was this profile generated? I get a similar profile using --enable-profiling and gprof, but I find it not believable. The complete absence of any calls to libpq is not credible. I don't know about your profiler, but with gprof they should be listed in the call graph even if they take a negligible amount of time. So I think pgbench is linking to libpq libraries that do not themselves support profiling (I have no idea how that could happen though). If the calls graphs are not getting recorded correctly, surely the timing can't be reliable either. hmm - the profile was generated using oprofile, but now that you are mentioning this aspect, I suppose that this was a profile run without opcontrol --seperate=lib... I'm not currently in a position to retest that - but maybe you could do a run? (I also tried profiling pgbench with perf, but in that case I get nothing other than kernel and libc calls showing up. I don't know what that means) To support this, I've dummied up doCustom so that does all the work of deciding what needs to happen, executing the metacommands, interpolating the variables into the SQL string, but then simply refrains from calling the PQ functions to send and receive the query and response. (I had to make a few changes around the select loop in threadRun to support this). The result is that the dummy pgbench is charged with only 57% more CPU time than the stock one, but it gets over 10 times as many transactions done. I think this supports the notion that the CPU bottleneck is not in pgbench.c, but somewhere in the libpq or the kernel. interesting - iirc we actually had some reports about current libpq behaviour causing scaling issues on some OSes - see http://archives.postgresql.org/pgsql-hackers/2009-06/msg00748.php and some related threads. Iirc the final patch for that was never applied though and the original author lost interest, I think that I was able to measure some noticable performance gains back in the days but I don't think I still have the numbers somewhere. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
Jeff Janes jeff.ja...@gmail.com writes: How was this profile generated? I get a similar profile using --enable-profiling and gprof, but I find it not believable. The complete absence of any calls to libpq is not credible. I don't know about your profiler, but with gprof they should be listed in the call graph even if they take a negligible amount of time. So I think pgbench is linking to libpq libraries that do not themselves support profiling (I have no idea how that could happen though). If the calls graphs are not getting recorded correctly, surely the timing can't be reliable either. Last I checked, gprof simply does not work for shared libraries on Linux --- is that what you're testing on? If so, try oprofile or some other Linux-specific solution. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: interesting - iirc we actually had some reports about current libpq behaviour causing scaling issues on some OSes - see http://archives.postgresql.org/pgsql-hackers/2009-06/msg00748.php and some related threads. Iirc the final patch for that was never applied though and the original author lost interest, I think that I was able to measure some noticable performance gains back in the days but I don't think I still have the numbers somewhere. Huh? That patch did get applied in some form or other -- at least, libpq does contain references to both SO_NOSIGPIPE and MSG_NOSIGNAL these days. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On 07/24/2011 05:55 PM, Tom Lane wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: interesting - iirc we actually had some reports about current libpq behaviour causing scaling issues on some OSes - see http://archives.postgresql.org/pgsql-hackers/2009-06/msg00748.php and some related threads. Iirc the final patch for that was never applied though and the original author lost interest, I think that I was able to measure some noticable performance gains back in the days but I don't think I still have the numbers somewhere. Huh? That patch did get applied in some form or other -- at least, libpq does contain references to both SO_NOSIGPIPE and MSG_NOSIGNAL these days. hmm yeah - your are right, when I looked that up a few hours ago I failed to find the right commit but it was indeed commited: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cea80e726edd42a39bb0220290738f7825de8e57 I think I mentally mixed that up with compare word-at-a-time in bcTruelen patch that was also discussed for affecting query rates for trivial queries. I actually wonder if -HEAD would show that issue even more clearly now that we have parts of roberts performance work in the tree... Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On Mon, Jun 13, 2011 at 7:03 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: On 06/13/2011 01:55 PM, Stefan Kaltenbrunner wrote: [...] all those tests are done with pgbench running on the same box - which has a noticable impact on the results because pgbench is using ~1 core per 8 cores of the backend tested in cpu resoures - though I don't think it causes any changes in the results that would show the performance behaviour in a different light. actuall testing against sysbench with the very same workload shows the following performance behaviour: with 40 threads(aka the peak performance point): pgbench: 223308 tps sysbench: 311584 tps with 160 threads (backend contention dominated): pgbench: 57075 sysbench: 43437 so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. for those curious - the profile for pgbench looks like: samples % symbol name 29378 41.9087 doCustom 17502 24.9672 threadRun 7629 10.8830 pg_strcasecmp 5871 8.3752 compareVariables 2568 3.6633 getVariable 2167 3.0913 putVariable 2065 2.9458 replaceVariable 1971 2.8117 parseVariable 534 0.7618 xstrdup 278 0.3966 xrealloc 137 0.1954 xmalloc Hi Stefan, How was this profile generated? I get a similar profile using --enable-profiling and gprof, but I find it not believable. The complete absence of any calls to libpq is not credible. I don't know about your profiler, but with gprof they should be listed in the call graph even if they take a negligible amount of time. So I think pgbench is linking to libpq libraries that do not themselves support profiling (I have no idea how that could happen though). If the calls graphs are not getting recorded correctly, surely the timing can't be reliable either. (I also tried profiling pgbench with perf, but in that case I get nothing other than kernel and libc calls showing up. I don't know what that means) To support this, I've dummied up doCustom so that does all the work of deciding what needs to happen, executing the metacommands, interpolating the variables into the SQL string, but then simply refrains from calling the PQ functions to send and receive the query and response. (I had to make a few changes around the select loop in threadRun to support this). The result is that the dummy pgbench is charged with only 57% more CPU time than the stock one, but it gets over 10 times as many transactions done. I think this supports the notion that the CPU bottleneck is not in pgbench.c, but somewhere in the libpq or the kernel. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On Mon, Jun 13, 2011 at 9:09 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: I noticed that pgbench's doCustom (the function highest in the profile posted) returns doing nothing if the connection is supposed to be sleeping; seems an open door for busy waiting. I didn't check the rest of the code to see if there's something avoiding that condition. Yes, there is a select in threadRun that avoids that. Also, I don't think anyone would but in a sleep in this particular type of pgbench run. I also noticed that it seems to be very liberal about calling INSTR_TIME_SET_CURRENT in the same function which perhaps could be optimizing by calling it a single time at entry and reusing the value, but I guess that would show up in the profile as a kernel call so it's maybe not a problem. I think that only gets called when you specifically asked for latencies or for logging, or when making new connection (which should be rare) Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On 06/14/2011 02:27 AM, Jeff Janes wrote: On Mon, Jun 13, 2011 at 7:03 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: ... so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. Hi Stefan, pgbench sends each query (per connection) and waits for the reply before sending another. Do we know whether sysbench does that, or if it just stuffs the kernel's IPC buffer full of queries without synchronously waiting for individual replies? I can't get sysbench to make for me, or I'd strace in single client mode and see what kind of messages are going back and forth. yeah sysbench compiled from a release tarball needs some autoconf/makefile hackery to get running on a modern system - but I can provide you with the data you are interested in if you tell me exactly what you are looking for... Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On 06/13/2011 01:55 PM, Stefan Kaltenbrunner wrote: [...] all those tests are done with pgbench running on the same box - which has a noticable impact on the results because pgbench is using ~1 core per 8 cores of the backend tested in cpu resoures - though I don't think it causes any changes in the results that would show the performance behaviour in a different light. actuall testing against sysbench with the very same workload shows the following performance behaviour: with 40 threads(aka the peak performance point): pgbench:223308 tps sysbench: 311584 tps with 160 threads (backend contention dominated): pgbench:57075 sysbench: 43437 so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. for those curious - the profile for pgbench looks like: samples %symbol name 2937841.9087 doCustom 1750224.9672 threadRun 7629 10.8830 pg_strcasecmp 5871 8.3752 compareVariables 2568 3.6633 getVariable 2167 3.0913 putVariable 2065 2.9458 replaceVariable 1971 2.8117 parseVariable 534 0.7618 xstrdup 278 0.3966 xrealloc 137 0.1954 xmalloc Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On Mon, Jun 13, 2011 at 7:03 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: ... so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. Hi Stefan, pgbench sends each query (per connection) and waits for the reply before sending another. Do we know whether sysbench does that, or if it just stuffs the kernel's IPC buffer full of queries without synchronously waiting for individual replies? I can't get sysbench to make for me, or I'd strace in single client mode and see what kind of messages are going back and forth. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On Tue, Jun 14, 2011 at 09:27, Jeff Janes jeff.ja...@gmail.com wrote: pgbench sends each query (per connection) and waits for the reply before sending another. We can use -j option to run pgbench in multiple threads to avoid request starvation. What setting did you use, Stefan? for those curious - the profile for pgbench looks like: samples %symbol name 2937841.9087 doCustom 1750224.9672 threadRun 7629 10.8830 pg_strcasecmp If the bench is bottleneck, it would be better to reduce pg_strcasecmp calls by holding meta command names as integer values of sub-META_COMMAND instead of string comparison for each loop. -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On 06/13/2011 08:27 PM, Jeff Janes wrote: pgbench sends each query (per connection) and waits for the reply before sending another. Do we know whether sysbench does that, or if it just stuffs the kernel's IPC buffer full of queries without synchronously waiting for individual replies? sysbench creates a thread for each client and lets them go at things at whatever speed they can handle. You have to setup pgbench with a worker per core to get them even close to level footing. And even in that case, sysbench has a significant advantage, because it's got the commands it runs more or less hard-coded in the program. pgbench is constantly parsing things in its internal command language and then turning them into SQL requests. That's flexible and allows it to be used for some neat custom things, but it uses a lot more resources to drive the same number of clients. I can't get sysbench to make for me, or I'd strace in single client mode and see what kind of messages are going back and forth. If you're using a sysbench tarball, no surprise. It doesn't build on lots of platforms now. If you grab http://projects.2ndquadrant.it/sites/default/files/bottom-up-benchmarking.pdf it has my sysbench notes starting on page 34. I had to checkout the latest version from their development repo to get it to compile on any recent system. The attached wrapper script may be helpful to you as well to help get over the learning curve for how to run the program; it iterates sysbench over a number of database sizes and thread counts running the complicated to setup OLTP test. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us #!/bin/bash SB=$HOME/0.4/sysbench/sysbench DB=--pgsql-user=postgres --pgsql-password=password --pgsql-db=sysbench --pgsql-host=localhost --db-driver=pgsql #DB=--mysql-user=root --db-driver=mysql --mysql-table-engine=innodb --mysql-db=sysbench #THREADS=1 2 3 4 5 6 7 8 9 10 11 12 16 24 32 48 64 96 THREADS=1 TIME=10 TEST_PARAM=--oltp-read-only=off --oltp-test-mode=complex #TEST_PARAM=--oltp-read-only=on --oltp-test-mode=simple SIZES=1 #SIZES=1 10 100 1000 5000 1 5 10 # In complex, non read-only mode, there will be duplicate key issues, and # threads will fail with deadlock--causing no value to be returned. #TEST_PARAM=--oltp-test-mode=complex for s in $SIZES do SIZE_PARAM=--oltp-table-size=$s # Just in case! $SB $DB --test=oltp cleanup $SB $DB $SIZE_PARAM --test=oltp prepare for t in $THREADS do echo -n $t threads: $SB $DB --test=oltp $TEST_PARAM $SIZE_PARAM --init-rng --max-requests=0 --max-time=$TIME --num-threads=$t run 21 # | grep read/write requests done $SB $DB --test=oltp cleanup done -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
Excerpts from Jeff Janes's message of lun jun 13 20:27:15 -0400 2011: On Mon, Jun 13, 2011 at 7:03 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: ... so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. Hi Stefan, pgbench sends each query (per connection) and waits for the reply before sending another. I noticed that pgbench's doCustom (the function highest in the profile posted) returns doing nothing if the connection is supposed to be sleeping; seems an open door for busy waiting. I didn't check the rest of the code to see if there's something avoiding that condition. I also noticed that it seems to be very liberal about calling INSTR_TIME_SET_CURRENT in the same function which perhaps could be optimizing by calling it a single time at entry and reusing the value, but I guess that would show up in the profile as a kernel call so it's maybe not a problem. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On Tue, Jun 14, 2011 at 13:09, Alvaro Herrera alvhe...@commandprompt.com wrote: I noticed that pgbench's doCustom (the function highest in the profile posted) returns doing nothing if the connection is supposed to be sleeping; seems an open door for busy waiting. pgbench uses select() with/without timeout in the cases, no? -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
