Thread

1. Single client performance on trivial SELECTs

   Greg Smith <greg@2ndquadrant.com> — 2011-04-14T07:08:16Z

   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    greg@2ndQuadrant.com   Baltimore, MD
   PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
   
   
   

2. Re: Single client performance on trivial SELECTs

   Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-14T14:43:16Z

   Greg Smith <greg@2ndquadrant.com> writes:
   > 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
   
   Yeah, this is pretty typical ...
   
   > 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.
   
   I doubt that it's possible to make AllocSetAlloc radically cheaper.
   I think the more likely route to improvement there is going to be to
   find a way to do fewer pallocs.  For instance, if we had more rigorous
   rules about which data structures are read-only to which code, we could
   probably get rid of a lot of just-in-case tree copying that happens in
   the parser and planner.
   
   But at the same time, even if we could drive all palloc costs to zero,
   it would only make a 10% difference in this example.  And this sort of
   fairly flat profile is what I see in most cases these days --- we've
   been playing performance whack-a-mole for long enough now that there
   isn't much low-hanging fruit left.  For better or worse, the system
   design we've chosen just isn't amenable to minimal overhead for simple
   queries.  I think a lot of this ultimately traces to the extensible,
   data-type-agnostic design philosophy.  The fact that we don't know what
   an integer is until we look in pg_type, and don't know what an "="
   operator does until we look up its properties, is great from a flexibility
   point of view; but this sort of query is where the costs become obvious.
   
   			regards, tom lane
   
   

3. Re: Single client performance on trivial SELECTs

   David Fetter <david@fetter.org> — 2011-04-14T15:41:45Z

   On Thu, Apr 14, 2011 at 10:43:16AM -0400, Tom Lane wrote:
   > 
   > I doubt that it's possible to make AllocSetAlloc radically cheaper.
   > I think the more likely route to improvement there is going to be to
   > find a way to do fewer pallocs.  For instance, if we had more rigorous
   > rules about which data structures are read-only to which code, we could
   > probably get rid of a lot of just-in-case tree copying that happens in
   > the parser and planner.
   
   How much work would that be, and how would it be enforced in new
   development?
   
   > But at the same time, even if we could drive all palloc costs to
   > zero, it would only make a 10% difference in this example.  And this
   > sort of fairly flat profile is what I see in most cases these days
   > --- we've been playing performance whack-a-mole for long enough now
   > that there isn't much low-hanging fruit left.  For better or worse,
   > the system design we've chosen just isn't amenable to minimal
   > overhead for simple queries.  I think a lot of this ultimately
   > traces to the extensible, data-type-agnostic design philosophy.  The
   > fact that we don't know what an integer is until we look in pg_type,
   > and don't know what an "=" operator does until we look up its
   > properties, is great from a flexibility point of view; but this sort
   > of query is where the costs become obvious.
   
   Is it time to revisit that decision?  Should we wait until, say, we no
   longer claim to support 32-bit machines on the server side?
   
   Cheers,
   David.
   -- 
   David Fetter <david@fetter.org> http://fetter.org/
   Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
   Skype: davidfetter      XMPP: david.fetter@gmail.com
   iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
   
   Remember to vote!
   Consider donating to Postgres: http://www.postgresql.org/about/donate
   
   

4. Re: Single client performance on trivial SELECTs

   Robert Haas <robertmhaas@gmail.com> — 2011-04-14T18:15:00Z

   On Thu, Apr 14, 2011 at 7:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   > I doubt that it's possible to make AllocSetAlloc radically cheaper.
   > I think the more likely route to improvement there is going to be to
   > find a way to do fewer pallocs.  For instance, if we had more rigorous
   > rules about which data structures are read-only to which code, we could
   > probably get rid of a lot of just-in-case tree copying that happens in
   > the parser and planner.
   >
   > But at the same time, even if we could drive all palloc costs to zero,
   > it would only make a 10% difference in this example.  And this sort of
   > fairly flat profile is what I see in most cases these days --- we've
   > been playing performance whack-a-mole for long enough now that there
   > isn't much low-hanging fruit left.
   
   There are other architectural approaches that we could take to
   reducing the parsing overhead.  Random ideas:
   
   1. Separate the parser into a parser for DML statements only, and
   another parser for everything else, to avoid cluttering the main
   parser with lots of keywords and non-terminals that aren't going to be
   used for the kinds of queries people care about parsing speed.
   
   2. Hand-code a parser, or use some tool other than bison to generate one.
   
   3. Some kind of parse-tree cache, so that executing the same exact
   statement many times doesn't require reparsing it every time.
   
   It's fairly far down in the noise on this particular profile, but in
   the low-hanging fruit department, I think we should fix
   ScanKeywordLookup to use a smarter algorithm that is more like O(1)
   rather than O(lg n) in the number of keywords.  It shouldn't be
   terribly difficult to come up with some kind of hash function based
   on, say, the first two characters of the keyword that would be a lot
   faster than what we're doing now.
   
   -- 
   Robert Haas
   EnterpriseDB: http://www.enterprisedb.com
   The Enterprise PostgreSQL Company
   
   

5. Re: Single client performance on trivial SELECTs

   Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2011-04-14T19:36:09Z

   On 14.04.2011 17:43, Tom Lane wrote:
   > Greg Smith<greg@2ndquadrant.com>  writes:
   >> 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_compareSince
   >> 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
   >
   > Yeah, this is pretty typical ...
   
   In this case you could just use prepared statements and get rid of all 
   the parser related overhead, which includes much of the allocations.
   
   >> 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.
   >
   > I doubt that it's possible to make AllocSetAlloc radically cheaper.
   > I think the more likely route to improvement there is going to be to
   > find a way to do fewer pallocs.  For instance, if we had more rigorous
   > rules about which data structures are read-only to which code, we could
   > probably get rid of a lot of just-in-case tree copying that happens in
   > the parser and planner.
   >
   > But at the same time, even if we could drive all palloc costs to zero,
   > it would only make a 10% difference in this example.  And this sort of
   > fairly flat profile is what I see in most cases these days --- we've
   > been playing performance whack-a-mole for long enough now that there
   > isn't much low-hanging fruit left.  For better or worse, the system
   > design we've chosen just isn't amenable to minimal overhead for simple
   > queries.  I think a lot of this ultimately traces to the extensible,
   > data-type-agnostic design philosophy.  The fact that we don't know what
   > an integer is until we look in pg_type, and don't know what an "="
   > operator does until we look up its properties, is great from a flexibility
   > point of view; but this sort of query is where the costs become obvious.
   
   I think the general strategy to make this kind of queries faster will be 
   to add various fastpaths to cache and skip even more work. For example,
   
   There's one very low-hanging fruit here, though. I profiled the pgbench 
   case, with -M prepared, and found that like in Greg Smith's profile, 
   hash_seq_search pops up quite high in the list. Those calls are coming 
   from LockReleaseAll(), where we scan the local lock hash to find all 
   locks held. We specify the initial size of the local lock hash table as 
   128, which is unnecessarily large for small queries like this. Reducing 
   it to 8 slashed the time spent in hash_seq_search().
   
   I think we should make that hash table smaller. It won't buy much, 
   somewhere between 1-5 % in this test case, but it's very easy to do and 
   I don't see much downside, it's a local hash table so it will grow as 
   needed.
   
   -- 
      Heikki Linnakangas
      EnterpriseDB   http://www.enterprisedb.com
   
   

6. Re: Single client performance on trivial SELECTs

   Noah Misch <noah@leadboat.com> — 2011-04-14T19:45:26Z

   On Thu, Apr 14, 2011 at 11:15:00AM -0700, Robert Haas wrote:
   > It's fairly far down in the noise on this particular profile, but in
   > the low-hanging fruit department, I think we should fix
   > ScanKeywordLookup to use a smarter algorithm that is more like O(1)
   > rather than O(lg n) in the number of keywords.
   
   +1
   
   > It shouldn't be
   > terribly difficult to come up with some kind of hash function based
   > on, say, the first two characters of the keyword that would be a lot
   > faster than what we're doing now.
   
   I'd look at `gperf', which generates code for this from your keyword list.
   
   

7. Re: Single client performance on trivial SELECTs

   Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-14T20:02:50Z

   Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
   > There's one very low-hanging fruit here, though. I profiled the pgbench 
   > case, with -M prepared, and found that like in Greg Smith's profile, 
   > hash_seq_search pops up quite high in the list. Those calls are coming 
   > from LockReleaseAll(), where we scan the local lock hash to find all 
   > locks held. We specify the initial size of the local lock hash table as 
   > 128, which is unnecessarily large for small queries like this. Reducing 
   > it to 8 slashed the time spent in hash_seq_search().
   
   > I think we should make that hash table smaller. It won't buy much, 
   > somewhere between 1-5 % in this test case, but it's very easy to do and 
   > I don't see much downside, it's a local hash table so it will grow as 
   > needed.
   
   8 sounds awfully small.  Can you even get as far as preparing the
   statements you intend to use without causing that to grow?
   
   I agree that 128 may be larger than necessary, but I don't think we
   should pessimize normal usage to gain a small fraction on trivial
   queries.  I'd be happier with something like 16 or 32.
   
   			regards, tom lane
   
   

8. Re: Single client performance on trivial SELECTs

   Andres Freund <andres@anarazel.de> — 2011-04-14T20:08:34Z

   Hi,
   
   On Thursday 14 April 2011 16:43:16 Tom Lane wrote:
   > I doubt that it's possible to make AllocSetAlloc radically cheaper.
   I actually doubt your doubt. I think you could add some SLAB like interface 
   for common allocation sizes making it significantly faster for some uses 
   (because allocation/deallocation is a single linked list operation).
   Maybe even treat everything < some_size as a slab object in the next bigger 
   slab.
   
   Note that I think youre otherwise right...
   
   Andres
   
   

9. Re: Single client performance on trivial SELECTs

   Alvaro Herrera <alvherre@commandprompt.com> — 2011-04-14T20:21:26Z

   Excerpts from Andres Freund's message of jue abr 14 17:08:34 -0300 2011:
   > Hi,
   > 
   > On Thursday 14 April 2011 16:43:16 Tom Lane wrote:
   > > I doubt that it's possible to make AllocSetAlloc radically cheaper.
   > I actually doubt your doubt. I think you could add some SLAB like interface 
   > for common allocation sizes making it significantly faster for some uses 
   > (because allocation/deallocation is a single linked list operation).
   > Maybe even treat everything < some_size as a slab object in the next bigger 
   > slab.
   
   I think the problem with a slab allocator is that it would make
   hierarchical context management slower and/or more complicated (e.g.
   reset context on transaction abort).
   
   -- 
   Álvaro Herrera <alvherre@commandprompt.com>
   The PostgreSQL Company - Command Prompt, Inc.
   PostgreSQL Replication, Consulting, Custom Development, 24x7 support
   
   

10. Re: Single client performance on trivial SELECTs

    Greg Smith <greg@2ndquadrant.com> — 2011-04-14T20:22:26Z

    Heikki Linnakangas wrote:
    > In this case you could just use prepared statements and get rid of all 
    > the parser related overhead, which includes much of the allocations.
    
    Trying that gives me around 9200 TPS instead of 5500 on my laptop, so a 
    pretty big gain here.  Will have to include that in my next round of 
    graphs across multiple client loads once I'm home again and can run 
    easily on my server.
    
    To provide a matching profile from the same system as the one I already 
    submitted from, for archival sake, here's what the profile I get looks 
    like with "-M prepared":
    
    samples  %        image name               symbol name
    33093     4.8518  postgres                 AllocSetAlloc
    30012     4.4001  postgres                 hash_seq_search
    27149     3.9803  postgres                 MemoryContextAllocZeroAligned
    26987     3.9566  postgres                 hash_search_with_hash_value
    25665     3.7628  postgres                 hash_any
    16820     2.4660  postgres                 _bt_compare
    14778     2.1666  postgres                 LockAcquireExtended
    12263     1.7979  postgres                 AllocSetFreeIndex
    11727     1.7193  postgres                 tas
    11602     1.7010  postgres                 SearchCatCache
    11022     1.6159  postgres                 pg_encoding_mbcliplen
    10963     1.6073  postgres                 MemoryContextAllocZero
    9296      1.3629  postgres                 MemoryContextCreate
    8368      1.2268  postgres                 fmgr_isbuiltin
    7973      1.1689  postgres                 LockReleaseAll
    7423      1.0883  postgres                 ExecInitExpr
    7309      1.0716  postgres                 pfree
    
    -- 
    Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
    
    
    
    

11. Re: Single client performance on trivial SELECTs

    Merlin Moncure <mmoncure@gmail.com> — 2011-04-14T20:30:25Z

    On Thu, Apr 14, 2011 at 2:08 AM, Greg Smith <greg@2ndquadrant.com> wrote:
    > 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.
    
    on my workstation VM, I get:
    6.7k selects single client,
    12k selects piped through single user backend,
    13.5k piped through single user backend, one transaction
    23k in plpgsql 'execute' in loop (which is really two queries, one to
    build the query and one to execute),
    100k in non dynamic query plpgsql in loop.
    
    in addition to parsing and planning, the network and the protocol
    really factor in.  this is why i'm so keen on being able to inject
    queries directly in to the backend via stored procedure.  I'm also
    really curious how a plpgsql-ish language performs when managing
    transactions itself.
    
    libpqtypes was written so you could stage data on the client and hand
    it off to the backend and act on it in plpgsl world.  it works really
    well -- you can amortize the turnaround losses through the newtork
    over a lot of data.
    
    merlin
    
    

12. Re: Single client performance on trivial SELECTs

    Andres Freund <andres@anarazel.de> — 2011-04-14T20:34:57Z

    On Thursday 14 April 2011 22:21:26 Alvaro Herrera wrote:
    > Excerpts from Andres Freund's message of jue abr 14 17:08:34 -0300 2011:
    > > Hi,
    > > 
    > > On Thursday 14 April 2011 16:43:16 Tom Lane wrote:
    > > > I doubt that it's possible to make AllocSetAlloc radically cheaper.
    > > 
    > > I actually doubt your doubt. I think you could add some SLAB like
    > > interface for common allocation sizes making it significantly faster for
    > > some uses (because allocation/deallocation is a single linked list
    > > operation). Maybe even treat everything < some_size as a slab object in
    > > the next bigger slab.
    > I think the problem with a slab allocator is that it would make
    > hierarchical context management slower and/or more complicated (e.g.
    > reset context on transaction abort).
    I am not that sure that it would be slower. I think that if youre careful you 
    mostly can reuse what currently is done for chunks to implement slabs.
    For context resets you can just throw away all chunks/blocks.
    
    Where I am with you is that its quite possible that it will not make sense 
    (performancewise) for all contexts. Which is quite annoying.
    
    Andres
    
    

13. Re: Single client performance on trivial SELECTs

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-14T20:46:03Z

    Andres Freund <andres@anarazel.de> writes:
    > Where I am with you is that its quite possible that it will not make sense 
    > (performancewise) for all contexts. Which is quite annoying.
    
    The mcxt stuff was designed from day one to support multiple types of
    contexts, so it wouldn't be very hard at all to have different contexts
    using different allocation policies.  The issue is to figure out what
    to use where ...
    
    			regards, tom lane
    
    

14. Re: Single client performance on trivial SELECTs

    Robert Haas <robertmhaas@gmail.com> — 2011-04-14T21:07:20Z

    On Thu, Apr 14, 2011 at 12:45 PM, Noah Misch <noah@leadboat.com> wrote:
    >> It shouldn't be
    >> terribly difficult to come up with some kind of hash function based
    >> on, say, the first two characters of the keyword that would be a lot
    >> faster than what we're doing now.
    >
    > I'd look at `gperf', which generates code for this from your keyword list.
    
    I thought of that, but wasn't sure we wanted to introduce a dependency
    on that tool.  That might be a pain, especially on Windows.  But maybe
    we could steal the basic approach.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
    
    

15. Re: Single client performance on trivial SELECTs

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-14T21:10:41Z

    Noah Misch <noah@leadboat.com> writes:
    > On Thu, Apr 14, 2011 at 11:15:00AM -0700, Robert Haas wrote:
    >> It shouldn't be
    >> terribly difficult to come up with some kind of hash function based
    >> on, say, the first two characters of the keyword that would be a lot
    >> faster than what we're doing now.
    
    > I'd look at `gperf', which generates code for this from your keyword list.
    
    FWIW, mysql used to use gperf for this purpose, but they've abandoned it
    in favor of some homegrown hashing scheme.  I don't know exactly why,
    but I wonder if it was for licensing reasons.  gperf itself is GPL, and
    I don't see any disclaimer in the docs saying that its output isn't.
    
    			regards, tom lane
    
    

16. Re: Single client performance on trivial SELECTs

    Merlin Moncure <mmoncure@gmail.com> — 2011-04-14T21:13:47Z

    On Thu, Apr 14, 2011 at 4:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Noah Misch <noah@leadboat.com> writes:
    >> On Thu, Apr 14, 2011 at 11:15:00AM -0700, Robert Haas wrote:
    >>> It shouldn't be
    >>> terribly difficult to come up with some kind of hash function based
    >>> on, say, the first two characters of the keyword that would be a lot
    >>> faster than what we're doing now.
    >
    >> I'd look at `gperf', which generates code for this from your keyword list.
    >
    > FWIW, mysql used to use gperf for this purpose, but they've abandoned it
    > in favor of some homegrown hashing scheme.  I don't know exactly why,
    > but I wonder if it was for licensing reasons.  gperf itself is GPL, and
    > I don't see any disclaimer in the docs saying that its output isn't.
    
    I dont think it matters -- see the entry in the bison faq:
    "Is there some way that I can GPL the output people get from use of my
    program? For example, if my program is used to develop hardware
    designs, can I require that these designs must be free?
    
        In general this is legally impossible; copyright law does not give
    you any say in the use of the output people make from their data using
    your program. If the user uses your program to enter or convert his
    own data, the copyright on the output belongs to him, not you. More
    generally, when a program translates its input into some other form,
    the copyright status of the output inherits that of the input it was
    generated from."
    
    merlin
    
    

17. Re: Single client performance on trivial SELECTs

    Andres Freund <andres@anarazel.de> — 2011-04-14T21:23:53Z

    On Thursday 14 April 2011 23:10:41 Tom Lane wrote:
    > Noah Misch <noah@leadboat.com> writes:
    > > On Thu, Apr 14, 2011 at 11:15:00AM -0700, Robert Haas wrote:
    > >> It shouldn't be
    > >> terribly difficult to come up with some kind of hash function based
    > >> on, say, the first two characters of the keyword that would be a lot
    > >> faster than what we're doing now.
    > > 
    > > I'd look at `gperf', which generates code for this from your keyword
    > > list.
    > 
    > FWIW, mysql used to use gperf for this purpose, but they've abandoned it
    > in favor of some homegrown hashing scheme.  I don't know exactly why,
    > but I wonder if it was for licensing reasons.  gperf itself is GPL, and
    > I don't see any disclaimer in the docs saying that its output isn't.
    http://lists.gnu.org/archive/html/bug-gnu-utils/2008-08/msg00005.html :
    
    > Thanks for the suggestion; it indeed becomes sort of an FAQ. I've added
    > 
    > this text to the documentation:
    >    gperf is under GPL, but that does not cause the output produced
    >    by gperf to be under GPL.  The reason is that the output contains
    >    only small pieces of text that come directly from gperf's source
    >    code -- only about 7 lines long, too small for being significant --,
    >    and therefore the output is not a "derivative work" of gperf (in the
    >    sense of U.S.@: copyright law).
    
    Andres
    
    

18. Re: Single client performance on trivial SELECTs

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-15T03:16:03Z

    David Fetter <david@fetter.org> writes:
    > On Thu, Apr 14, 2011 at 10:43:16AM -0400, Tom Lane wrote:
    >> ... I think a lot of this ultimately
    >> traces to the extensible, data-type-agnostic design philosophy.  The
    >> fact that we don't know what an integer is until we look in pg_type,
    >> and don't know what an "=" operator does until we look up its
    >> properties, is great from a flexibility point of view; but this sort
    >> of query is where the costs become obvious.
    
    > Is it time to revisit that decision?
    
    Umm ... what are you proposing?  Toss user-defined data types and
    user-defined operators overboard?  No interest in that here.
    
    			regards, tom lane
    
    

19. Re: Single client performance on trivial SELECTs

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2011-04-15T11:57:40Z

    On 14.04.2011 23:02, Tom Lane wrote:
    > Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  writes:
    >> There's one very low-hanging fruit here, though. I profiled the pgbench
    >> case, with -M prepared, and found that like in Greg Smith's profile,
    >> hash_seq_search pops up quite high in the list. Those calls are coming
    >> from LockReleaseAll(), where we scan the local lock hash to find all
    >> locks held. We specify the initial size of the local lock hash table as
    >> 128, which is unnecessarily large for small queries like this. Reducing
    >> it to 8 slashed the time spent in hash_seq_search().
    >
    >> I think we should make that hash table smaller. It won't buy much,
    >> somewhere between 1-5 % in this test case, but it's very easy to do and
    >> I don't see much downside, it's a local hash table so it will grow as
    >> needed.
    >
    > 8 sounds awfully small.  Can you even get as far as preparing the
    > statements you intend to use without causing that to grow?
    
    I added a debug print into the locking code, the pgbench test case uses 
    up to 6 locks. It needs those 6 locks at backend startup, for 
    initializing caches I guess. The queries after that need only 3 locks.
    
    > I agree that 128 may be larger than necessary, but I don't think we
    > should pessimize normal usage to gain a small fraction on trivial
    > queries.  I'd be happier with something like 16 or 32.
    
    I'll change it to 16.
    
    -- 
       Heikki Linnakangas
       EnterpriseDB   http://www.enterprisedb.com
    
    

20. Re: Single client performance on trivial SELECTs

    Noah Misch <noah@leadboat.com> — 2011-04-15T12:40:46Z

    On Thu, Apr 14, 2011 at 05:10:41PM -0400, Tom Lane wrote:
    > Noah Misch <noah@leadboat.com> writes:
    > > On Thu, Apr 14, 2011 at 11:15:00AM -0700, Robert Haas wrote:
    > >> It shouldn't be
    > >> terribly difficult to come up with some kind of hash function based
    > >> on, say, the first two characters of the keyword that would be a lot
    > >> faster than what we're doing now.
    > 
    > > I'd look at `gperf', which generates code for this from your keyword list.
    > 
    > FWIW, mysql used to use gperf for this purpose, but they've abandoned it
    > in favor of some homegrown hashing scheme.  I don't know exactly why,
    > but I wonder if it was for licensing reasons.  gperf itself is GPL, and
    > I don't see any disclaimer in the docs saying that its output isn't.
    
    Do you have any details, like when mysql did this?  With a quick look, I'm
    failing to find confirmation that mysql ever did use gperf.  (Drizzle has
    replaced the mysql homegrown scheme with gperf, apparently in 2009, though.)
    
    

21. Re: Single client performance on trivial SELECTs

    David Fetter <david@fetter.org> — 2011-04-15T13:02:43Z

    On Thu, Apr 14, 2011 at 11:16:03PM -0400, Tom Lane wrote:
    > David Fetter <david@fetter.org> writes:
    > > On Thu, Apr 14, 2011 at 10:43:16AM -0400, Tom Lane wrote:
    > >> ... I think a lot of this ultimately traces to the extensible,
    > >> data-type-agnostic design philosophy.  The fact that we don't
    > >> know what an integer is until we look in pg_type, and don't know
    > >> what an "=" operator does until we look up its properties, is
    > >> great from a flexibility point of view; but this sort of query is
    > >> where the costs become obvious.
    > 
    > > Is it time to revisit that decision?
    > 
    > Umm ... what are you proposing?
    
    Having a short, default path for the "only built-ins" case.
    
    Presumably people who create their own data types, operators, etc.,
    understand that there's a performance trade-off for the feature.
    
    Cheers,
    David.
    -- 
    David Fetter <david@fetter.org> http://fetter.org/
    Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
    Skype: davidfetter      XMPP: david.fetter@gmail.com
    iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
    
    Remember to vote!
    Consider donating to Postgres: http://www.postgresql.org/about/donate
    
    

22. Re: Single client performance on trivial SELECTs

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-15T14:23:53Z

    Noah Misch <noah@leadboat.com> writes:
    > On Thu, Apr 14, 2011 at 05:10:41PM -0400, Tom Lane wrote:
    >> FWIW, mysql used to use gperf for this purpose, but they've abandoned it
    >> in favor of some homegrown hashing scheme.
    
    > Do you have any details, like when mysql did this?  With a quick look, I'm
    > failing to find confirmation that mysql ever did use gperf.  (Drizzle has
    > replaced the mysql homegrown scheme with gperf, apparently in 2009, though.)
    
    Now I'm not sure.  The evidence that I based that comment on is that Red
    Hat's mysql RPMs have had "BuildRequires: gperf" since forever --- as
    far back as I can find in their package CVS anyway --- and I doubt that
    would've got put in unless it were really needed to build.  But I can't
    find any mention of gperf in either the current mysql releases or the
    oldest mysql tarball I have, which is mysql 3.23.58.  So if it really
    was used, it was a very very long time ago.  If the Drizzle guys went
    in the other direction, that would suggest that they don't have any
    institutional memory of having rejected gperf, so maybe that
    BuildRequires is just a thinko after all.
    
    			regards, tom lane
    
    

23. Re: Single client performance on trivial SELECTs

    Josh Berkus <josh@agliodbs.com> — 2011-04-15T16:59:40Z

    All,
    
    While it would be nice to improve our performance on this workload, let me point out that it's not a very important workload from the point of view of real performance challenges.  Yes, there are folks out there with 100MB databases who only run one-liner select queries.  But frankly, Valemort/Redis/Mongo are going to whip both our and MySQL's butts on that kind of workload anyway.
    
    Certainly any sacrifice of functionality in order to be faster at that kind of trivial workload would be foolhardy.
    
    -- 
    Josh Berkus
    PostgreSQL Experts Inc.
    http://pgexperts.com
    San Francisco
    
    

24. Re: Single client performance on trivial SELECTs

    Joshua D. Drake <jd@commandprompt.com> — 2011-04-15T17:30:55Z

    On 04/15/2011 09:59 AM, Joshua Berkus wrote:
    > All,
    >
    > While it would be nice to improve our performance on this workload, let me point out that it's not a very important workload from the point of view of real performance challenges.  Yes, there are folks out there with 100MB databases who only run one-liner select queries.  But frankly, Valemort/Redis/Mongo are going to whip both our and MySQL's butts on that kind of workload anyway.
    
    Yes but if we can kick MySQL's butt, that would certainly be a good 
    thing. There are people (lots of people) that do not like the whole 
    Valemort/Redis/Mongo paradigm.
    
    > Certainly any sacrifice of functionality in order to be faster at that kind of trivial workload would be foolhardy.
    
    Agreed.
    
    JD
    
    
    
    
    
    

25. Re: Single client performance on trivial SELECTs

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-15T19:13:04Z

    Joshua Berkus <josh@agliodbs.com> writes:
    > Certainly any sacrifice of functionality in order to be faster at that kind of trivial workload would be foolhardy.
    
    Yeah, and I'd further say that any sacrifice of maintainability would be
    equally foolhardy.  In particular I'm repelled by David's proposal of a
    whole parallel parse/plan/execute chain for "standard" datatypes
    (whichever those are).
    
    			regards, tom lane
    
    

26. Re: Single client performance on trivial SELECTs

    Greg Smith <greg@2ndquadrant.com> — 2011-04-15T20:00:44Z

    I did some more research into the memory allocation hotspots found in 
    the profile, in regards to what MySQL has done in those areas.  (And by 
    "research" I mean "went to the bar last night and asked Baron about 
    it")  The issue of memory allocation being a limiter on performance has 
    been nagging that community for long enough that the underlying malloc 
    used can even be swapped with a LD_PRELOAD trick:  
    http://dev.mysql.com/doc/refman/5.5/en/mysqld-safe.html#option_mysqld_safe_malloc-lib
    
    Plenty of people have benchmarked that and seen a big difference between 
    the implementations; some sample graphs:
    
    http://locklessinc.com/articles/mysql_performance/
    http://blogs.sun.com/timc/entry/mysql_5_1_memory_allocator
    http://mysqlha.blogspot.com/2009/01/double-sysbench-throughput-with_18.html
    
    To quote from the last of those, "Malloc is a bottleneck for sysbench 
    OLTP readonly", so this problem is not unique to PostgreSQL.  As of 5.5 
    the better builds are all defaulting to TCMalloc, which is interesting 
    but probably not as useful because it's focused on improving 
    multi-threaded performance:  
    http://goog-perftools.sourceforge.net/doc/tcmalloc.html
    
    I'm not sure exactly what is useful to be learned from that specific 
    work.  But it does suggest two things:  one, this is far from an easy 
    thing to fix.  Two, the only reason MySQL does so well on it is because 
    there was some focused work on it, taking a quite a while to accomplish, 
    and involving many people.  Doing better for PostgreSQL is something I 
    see as more of a long-term goal, rather than something it would be 
    reasonable to expect quick progress on.
    
    -- 
    Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
    PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
    
    
    
    

27. Re: Single client performance on trivial SELECTs

    Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-04-15T21:33:38Z

    Greg Smith <greg@2ndquadrant.com> wrote:
     
    > I'm not sure exactly what is useful to be learned from that
    > specific work.  But it does suggest two things:  one, this is far
    > from an easy thing to fix.  Two, the only reason MySQL does so
    > well on it is because there was some focused work on it, taking a
    > quite a while to accomplish, and involving many people.  Doing
    > better for PostgreSQL is something I see as more of a long-term
    > goal, rather than something it would be reasonable to expect quick
    > progress on.
     
    Nice research.  Thanks for putting that in front of people.
     
    -Kevin