[HACKERS] Single client performance on trivial SELECTs

[Greg Smith]

This week several list regulars here waded into the MySQL Convention.  I 
decided to revisit PostgreSQL vs. MySQL performance using the sysbench 
program as part of that.  It's not important to what I'm going to 
describe to understand exactly what statements sysbench runs here or how 
to use it, but if anyone is curious I've got some more details about how 
I ran the tests in my talk slides at 
http://projects.2ndquadrant.com/talks  The program has recently gone 
through some fixes that make it run a bit better both in general and 
against PostgreSQL.  The write tests are still broken against 
PostgreSQL, but it now seems to do a reasonable job simulating a simple 
SELECT-only workload.  A fix from Jignesh recently made its way into the 
database generation side of the code that makes it less tedious to test 
with it too. 

The interesting part was how per-client scaling compared between the two 
databases; graph attached.  On my 8 core server, PostgreSQL scales 
nicely up to a steady 50K TPS.  I see the same curve, almost identical 
numbers, with PostgreSQL and pgbench--no reason to suspect sysbench is 
doing anything shady.  The version of MySQL I used hits around 67K TPS 
with innodb when busy with lots of clients.  That part doesn't bother 
me; nobody expects PostgreSQL to be faster on trivial SELECT statements 
and the gap isn't that big.

The shocking part was the single client results.  I'm using to seeing 
Postgres get around 7K TPS per core on those, which was the case here, 
and I never considered that an interesting limitation to think about 
before.  MySQL turns out to hit 38K TPS doing the same work.  Now that's 
a gap interesting enough to make me wonder what's going on.

Easy enough to exercise the same sort of single client test case with 
pgbench and put it under a profiler:

sudo opcontrol --init
sudo opcontrol --setup --no-vmlinux
createdb pgbench
pgbench -i -s 10 pgbench
psql -d pgbench -c "vacuum"
sudo opcontrol --start
sudo opcontrol --reset
pgbench -S -n -c 1 -T 60 pgbench
sudo opcontrol --dump ; sudo opcontrol --shutdown
opreport -l image:$HOME/pgwork/inst/test/bin/postgres

Here's the top calls, from my laptop rather than the server that I 
generated the graph against.  It does around 5.5K TPS with 1 clients and 
10K with 2 clients, so same basic scaling:

samples  %        image name               symbol name
53548     6.7609  postgres                 AllocSetAlloc
32787     4.1396  postgres                 MemoryContextAllocZeroAligned
26330     3.3244  postgres                 base_yyparse
21723     2.7427  postgres                 hash_search_with_hash_value
20831     2.6301  postgres                 SearchCatCache
19094     2.4108  postgres                 hash_seq_search
18402     2.3234  postgres                 hash_any
15975     2.0170  postgres                 AllocSetFreeIndex
14205     1.7935  postgres                 _bt_compare
13370     1.6881  postgres                 core_yylex
10455     1.3200  postgres                 MemoryContextAlloc
10330     1.3042  postgres                 LockAcquireExtended
10197     1.2875  postgres                 ScanKeywordLookup
9312      1.1757  postgres                 MemoryContextAllocZero

I don't know nearly enough about the memory allocator to comment on 
whether it's possible to optimize it better for this case to relieve any 
bottleneck.  Might just get a small gain then push the limiter to the 
parser or hash functions.  I was surprised to find that's where so much 
of the time was going though.

P.S. When showing this graph in my talk, I pointed out that anyone who 
is making decisions about which database to use based on trivial SELECTs 
on small databases isn't going to be choosing between PostgreSQL and 
MySQL anyway--they'll be deploying something like MongoDB instead if 
that's the important metric.

--
Greg Smith   2ndQuadrant US    g...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


<<inline: sysbench-simple.png>>-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers