Re: char() overhead on read-only workloads not so insignifcant as the docs claim it is... - Mailing list pgsql-hackers

<div dir="ltr">Comments?<br /><br /><div class="gmail_quote">On Sat, Jun 13, 2009 at 3:44 PM, Stefan Kaltenbrunner
<spandir="ltr"><stefan@kaltenbrunner.cc></span> wrote:<br /><blockquote class="gmail_quote" style="border-left:
1pxsolid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> I'm currently doing some benchmarking on a
Nehalembox(<a
href="http://www.kaltenbrunner.cc/blog/index.php?/archives/26-Benchmarking-8.4-Chapter-1Read-Only-workloads.html"
target="_blank">http://www.kaltenbrunner.cc/blog/index.php?/archives/26-Benchmarking-8.4-Chapter-1Read-Only-workloads.html</a>)
with8.4 and while investigating what looks like issues in pgbench I also noticed that using char() has more than a
negligableoverhead on some (very special) readonly(!) workloads.<br /><br /> for example running sysbench in read-only
modeagainst 8.4 results in a profile(for the full run) that looks similiar to:<br /><br /> samples  %        symbol
name<br/> 981690   11.0656  bcTruelen<br /> 359183    4.0487  index_getnext<br /> 311128    3.5070  AllocSetAlloc<br />
272330   3.0697  hash_search_with_hash_value<br /> 258157    2.9099  LWLockAcquire<br /> 195673    2.2056
 _bt_compare<br/> 190303    2.1451  slot_deform_tuple<br /> 168101    1.8948  PostgresMain<br /> 164191    1.8508
 _bt_checkkeys<br/> 126110    1.4215  FunctionCall2<br /> 123965    1.3973  SearchCatCache<br /> 120629    1.3597
 LWLockRelease<br/><br /> the default sysbench mode actually uses a number of different queries and the ones dealing
withchar() are actually only a small part of the full set of queries sent.<br /> The specific query is causing
bcTruelento show up in the profile is:<br /><br /> "SELECT c from sbtest where id between $1 and $2 order by c" where
theparameters are for example<br /> $1 = '5009559', $2 = '5009658' - ie ranges of 100.<br /><br /><br /> benchmarking
onlythat query results in:<br /><br /> samples  %        symbol name<br /> 2148182  23.5861  bcTruelen<br /> 369463  
 4.0565 index_getnext<br /> 362784    3.9832  AllocSetAlloc<br /> 284198    3.1204  slot_deform_tuple<br /> 185279  
 2.0343 _bt_checkkeys<br /> 180119    1.9776  LWLockAcquire<br /> 172733    1.8965  appendBinaryStringInfo<br /> 144158
  1.5828  internal_putbytes<br /> 141040    1.5486  AllocSetFree<br /> 138093    1.5162  printtup<br /> 124255  
 1.3643 hash_search_with_hash_value<br /> 117054    1.2852  heap_form_minimal_tuple<br /><br /> at around 46000
queries/s<br/><br /> changing the fault sysbench schema from:<br /><br />                             Table
"public.sbtest"<br/>  Column |      Type      |                      Modifiers <br />
--------+----------------+-----------------------------------------------------<br/>  id     | integer        | not
nulldefault nextval('sbtest_id_seq'::regclass)<br />  k      | integer        | not null default 0<br />  c      |
character(120)| not null default ''::bpchar<br />  pad    | character(60)  | not null default ''::bpchar<br />
Indexes:<br/>    "sbtest_pkey" PRIMARY KEY, btree (id)<br />    "k" btree (k)<br /><br /><br /> to<br />              
              Table "public.sbtest"<br />  Column |       Type        |                      Modifiers <br />
--------+-------------------+-----------------------------------------------------<br/>  id     | integer           |
notnull default nextval('sbtest_id_seq'::regclass)<br />  k      | integer           | not null default 0<br />  c    
 |character varying | not null default ''::character varying<br />  pad    | character(60)     | not null default
''::bpchar<br/> Indexes:<br />    "sbtest_pkey" PRIMARY KEY, btree (id)<br />    "k" btree (k)<br /><br /> results in a
near50%(!) speedup in terms of tps to around 67000 queries/s. This is however an extreme case because the c column
actuallycontains no data at all (except for an empty string).<br /><br /> the profile for the changed testcase looks
like:<br/> 430797    5.2222  index_getnext<br /> 396750    4.8095  AllocSetAlloc<br /> 345508    4.1883
 slot_deform_tuple<br/> 228222    2.7666  appendBinaryStringInfo<br /> 227766    2.7610  _bt_checkkeys<br /> 193818  
 2.3495 LWLockAcquire<br /> 179925    2.1811  internal_putbytes<br /> 168871    2.0471  printtup<br /> 152026    1.8429
 AllocSetFree<br/> 146333    1.7739  heap_form_minimal_tuple<br /> 144305    1.7493  FunctionCall2<br /> 128320  
 1.5555 hash_search_with_hash_value<br /><br /><br /> at the very least we should reconsider this part of our docs:<br
/><br/> "  There is no performance difference between these three types, apart from increased storage space when using
theblank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column."<br
/><br/> from <a href="http://www.postgresql.org/docs/8.4/static/datatype-character.html"
target="_blank">http://www.postgresql.org/docs/8.4/static/datatype-character.html</a><br/><br /><br /><br /> regards<br
/><br/> Stefan<br /><font color="#888888"><br /> -- <br /> Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org"target="_blank">pgsql-hackers@postgresql.org</a>)<br /> To make changes to
yoursubscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></font></blockquote></div><br /><br
clear="all"/><br />-- <br />Lets call it Postgres<br /><br />EnterpriseDB      <a href="http://www.enterprisedb.com"
target="_blank">http://www.enterprisedb.com</a><br/><br />gurjeet[.singh]@EnterpriseDB.com<br /> singh.gurjeet@{ gmail
|hotmail | indiatimes | yahoo }.com<br /> Mail sent from my BlackLaptop device<br /></div>