Thread: SMP Read-only Performance
I have a read-only database that I am testing the performance of to get a sense of how many concurrent users I can support. The database fits entirely in RAM so I expect there to be little to no disk activity. Because of this, I expect throughput to scale almost linearly with the number of CPUs I have. However, that is not what I am seeing. For example, take the following results. Postgres 8.3.8 Fedora 9 Intel(R) Xeon(R) CPU 5160 @ 3.00GHz X 2 (4 cores total) 4 GB RAM -bash-3.2$ pgbench -c 1 -S -t 400000 test starting vacuum...end. transaction type: SELECT only scaling factor: 64 number of clients: 1 number of transactions per client: 400000 number of transactions actually processed: 400000/400000 tps = 11373.127109 (including connections establishing) tps = 11373.907131 (excluding connections establishing) -bash-3.2$ pgbench -c 2 -S -t 200000 test starting vacuum...end. transaction type: SELECT only scaling factor: 64 number of clients: 2 number of transactions per client: 200000 number of transactions actually processed: 400000/400000 tps = 11422.541958 (including connections establishing) tps = 11423.979642 (excluding connections establishing) -bash-3.2$ pgbench -c 3 -S -t 200000 test starting vacuum...end. transaction type: SELECT only scaling factor: 64 number of clients: 3 number of transactions per client: 200000 number of transactions actually processed: 600000/600000 tps = 11330.641664 (including connections establishing) tps = 11332.074176 (excluding connections establishing) -bash-3.2$ pgbench -c 4 -S -t 100000 test starting vacuum...end. transaction type: SELECT only scaling factor: 64 number of clients: 4 number of transactions per client: 100000 number of transactions actually processed: 400000/400000 tps = 11747.647262 (including connections establishing) tps = 11750.710786 (excluding connections establishing) During these tests top(1) reported 60-70% idle CPU. As you see, the TPS remains the same as I increase the number of clients. These results make it look like PostgreSQL is single-threaded and not taking advantage of the multiple cores. Could someone please explain?
On Tue, Jan 26, 2010 at 3:01 PM, Mike Bresnahan <mike.bresnahan@bestbuy.com> wrote: > During these tests top(1) reported 60-70% idle CPU. > > As you see, the TPS remains the same as I increase the number of clients. These > results make it look like PostgreSQL is single-threaded and not taking advantage > of the multiple cores. Could someone please explain? This is more likely a limitation of pgbench and not postgresql. pgsql 9.0 has a multi-threaded pgbench coming with it that should help for testing these situations. http://www.depesz.com/index.php/2009/08/26/waiting-for-8-5-multi-threaded-pgbench/
Mike Bresnahan wrote: > As you see, the TPS remains the same as I increase the number of clients. These > results make it look like PostgreSQL is single-threaded and not taking advantage > of the multiple cores. Could someone please explain? > You're probably running into this problem: http://notemagnet.blogspot.com/2008/05/pgbench-suffering-with-linux-2623-2626.html I discovered that the issue with pgbench not running well on recent Linux kernels only occurs if you're connecting over the default sockets interface. If you setup your server to listen over TCP/IP instead (may have to tweak pg_hba.conf and listen_address in the postgresql.conf file), so that you can connect to it like this successfully: psql -h localhost You can then use the same method on pgbench: pgbench -c 1 -S -t 400000 test -h localhost And I'd bet that you'd then see the scaling you expect. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
Greg Smith <greg <at> 2ndquadrant.com> writes: > You're probably running into this problem: > http://notemagnet.blogspot.com/2008/05/pgbench-suffering-with-linux-2623-2626.html You are so right. The last thing I would have suspected is a kernel bug. I am definitely going to try to be more aware of kernel happenings from now on. Thank you! -bash-3.2$ uname -a Linux devpgre 2.6.25-14.fc9.x86_64 #1 SMP Thu May 1 06:06:21 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux -bash-3.2$ pgbench -c 1 -S -t 400000 -h localhost test starting vacuum...end. transaction type: SELECT only scaling factor: 64 number of clients: 1 number of transactions per client: 400000 number of transactions actually processed: 400000/400000 tps = 10716.907529 (including connections establishing) tps = 10717.650674 (excluding connections establishing) -bash-3.2$ pgbench -c 2 -S -t 400000 -h localhost test starting vacuum...end. transaction type: SELECT only scaling factor: 64 number of clients: 2 number of transactions per client: 400000 number of transactions actually processed: 800000/800000 tps = 14355.737471 (including connections establishing) tps = 14356.991106 (excluding connections establishing) -bash-3.2$ pgbench -c 3 -S -t 400000 -h localhost test starting vacuum...end. transaction type: SELECT only scaling factor: 64 number of clients: 3 number of transactions per client: 400000 number of transactions actually processed: 1200000/1200000 tps = 19068.715403 (including connections establishing) tps = 19070.958268 (excluding connections establishing) -bash-3.2$ pgbench -c 4 -S -t 400000 -h localhost test starting vacuum...end. transaction type: SELECT only scaling factor: 64 number of clients: 4 number of transactions per client: 400000 number of transactions actually processed: 1600000/1600000 tps = 22951.995256 (including connections establishing) tps = 22955.104092 (excluding connections establishing)