Re: GiST index performance

От: Matthew Wakeling
Тема: Re: GiST index performance
Дата: ,
Msg-id: alpine.DEB.2.00.0906111447140.4337@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: Re: GiST index performance  (Tom Lane)
Ответы: Re: GiST index performance  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

GiST index performance  (Matthew Wakeling, )
 Re: GiST index performance  (Robert Haas, )
  Re: GiST index performance  (Matthew Wakeling, )
   Re: GiST index performance  (Tom Lane, )
    Re: GiST index performance  (Matthew Wakeling, )
     Re: GiST index performance  (Tom Lane, )
      Re: GiST index performance  (Matthew Wakeling, )
       Re: GiST index performance  (Tom Lane, )
        Re: GiST index performance  (Matthew Wakeling, )
         Re: GiST index performance  (Matthew Wakeling, )
          Re: GiST index performance  (Matthew Wakeling, )
           Re: GiST index performance  (Tom Lane, )
           Re: GiST index performance  (Adam Gundy, )
        Re: GiST index performance  (Heikki Linnakangas, )
      Re: GiST index performance  (Greg Smith, )
       Re: GiST index performance  (Robert Haas, )
 Re: GiST index performance  (Bruce Momjian, )
  Re: GiST index performance  (Robert Haas, )
  Re: GiST index performance  (Matthew Wakeling, )
   Re: GiST index performance  (Robert Haas, )
   Re: GiST index performance  (Yeb Havinga, )
    Re: GiST index performance  (Yeb Havinga, )
     Re: GiST index performance  (Yeb Havinga, )
      Re: GiST index performance  (Yeb Havinga, )
       Re: GiST index performance  (Yeb Havinga, )
        Re: GiST index performance  (Kenneth Marshall, )
         Re: GiST index performance  (Yeb Havinga, )
          Re: GiST index performance  (Matthew Wakeling, )
           Re: GiST index performance  (Yeb Havinga, )
            Re: GiST index performance  (Matthew Wakeling, )

On Thu, 11 Jun 2009, Tom Lane wrote:
>> Oprofile scares me with the sheer number of options.
>
> You can ignore practically all of them; the defaults are pretty sane.

Thanks, that was helpful. Here is the top of opreport --long-filenames:

CPU: Core 2, speed 1998 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count
100000
CPU_CLK_UNHALT...|
   samples|      %|
------------------
   5114464 61.5404 /lib/libc-2.9.so
   1576829 18.9734 /changeable/pgsql_8.4_profiling/bin/postgres
         CPU_CLK_UNHALT...|
           samples|      %|
         ------------------
           1572346 99.7157 /changeable/pgsql_8.4_profiling/bin/postgres
              4482  0.2842 [vdso] (tgid:13593 range:0x7fff8dbff000-0x7fff8dc00000)
                 1 6.3e-05 [vdso] (tgid:13193 range:0x7fff8dbff000-0x7fff8dc00000)
    409534  4.9278 /no-vmlinux
    309990  3.7300 /changeable/pgsql_8.4_profiling/lib/btree_gist.so
    203684  2.4509 /changeable/pgsql_8.4_profiling/lib/bioseg.so

So it seems that btree_gist and bioseg are not using that much CPU at all,
compared to core postgres code. In fact, the majority of time seems to be
spent in libc. Unfortunately my libc doesn't have any debugging symbols.

Anyway, running opannotate seems to make it clear that time *is* spent in
the gistnext function, but almost all of that is in children of the
function. Lots of time is actually spent in fmgr_oldstyle though.

Here is the top of opreport -l:

CPU: Core 2, speed 1998 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count
100000
samples  %        image name               app name                 symbol name
5114464  61.5404  libc-2.9.so              libc-2.9.so              (no symbols)
496215    5.9708  postgres                 postgres                 gistnext
409534    4.9278  no-vmlinux               no-vmlinux               (no symbols)
404037    4.8616  postgres                 postgres                 fmgr_oldstyle
170079    2.0465  btree_gist.so            btree_gist.so            gbt_int4_consistent
160016    1.9254  postgres                 postgres                 gistdentryinit
153266    1.8442  nvidia_drv.so            nvidia_drv.so            (no symbols)
152463    1.8345  postgres                 postgres                 FunctionCall5
149374    1.7974  postgres                 postgres                 FunctionCall1
131112    1.5776  libxul.so                libxul.so                (no symbols)
120871    1.4544  postgres                 postgres                 .plt
94506     1.1372  bioseg.so                bioseg.so                bioseg_gist_consistent

I'm guessing my next step is to install a version of libc with debugging
symbols?

Matthew

--
 Some people, when confronted with a problem, think "I know, I'll use regular
 expressions." Now they have two problems.                  -- Jamie Zawinski


В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: GiST index performance
От: Erik Aronesty
Дата:
Сообщение: Re: Best way to load test a postgresql server