Re: Postgres Performance on CPU limited Platforms

George McCollister wrote:
> I'm trying to optimize postgres performance on a headless solid state
> hardware platform (no fans or disks). I have the database stored on a
> USB 2.0 flash drive (hdparm benchmarks reads at 10 MB/s). Performance is
> limited by the 533Mhz CPU.
>
> Hardware:
> IXP425 XScale (big endian) 533Mhz 64MB RAM
> USB 2.0 Flash Drive
>

Hmmm  ARM/XScale, 64MB.  Just curious. Are you running a Postgres server
on a pocket pc or possibly a cell phone?

>
> Software:
> Linux 2.6.21.4
> postgres 8.2.5
>
> I created a fresh database using initdb, then added one table.
>
> Here is the create table:
> CREATE TABLE archivetbl
> (
>   "DateTime" timestamp without time zone,
>   "StationNum" smallint,
>   "DeviceDateTime" timestamp without time zone,
>   "DeviceNum" smallint,
>   "Tagname" character(64),
>   "Value" double precision,
>   "Online" boolean
> )
> WITH (OIDS=FALSE);
> ALTER TABLE archivetbl OWNER TO novatech;
>
> I've attached my postgresql.conf
>
> I populated the table with 38098 rows.
>
> I'm doing this simple query:
> select * from archivetbl;
>
> It takes 79 seconds to complete the query (when postgres is compiled
> with -O2). I'm running the query from pgadmin3 over TCP/IP.
>
> top shows CPU usage is at 100% with 95% being in userspace. oprofile
> shows memset is using 58% of the CPU cycles!
>
> CPU: ARM/XScale PMU2, speed 0 MHz (estimated)
> Counted CPU_CYCLES events (clock cycles counter) with a unit mask of
> 0x00 (No unit mask) count 100000
> samples  %        app name                 symbol name
> 288445   57.9263  libc-2.5.so              memset
> 33273     6.6820  vmlinux                  default_idle
> 27910     5.6050  vmlinux                  cpu_idle
> 12611     2.5326  vmlinux                  schedule
> 8803      1.7678  libc-2.5.so              __printf_fp
> 7448      1.4957  postgres                 dopr
> 6404      1.2861  libc-2.5.so              vfprintf
> 6398      1.2849  oprofiled                (no symbols)
> 4992      1.0025  postgres                 __udivdi3
> 4818      0.9676  vmlinux                  run_timer_softirq
>
>
> I was having trouble getting oprofile to give a back trace for memset
> (probably because my libc is optimized). So I redefined MemSet to call this:
> void * gmm_memset(void *s, int c, size_t n)
> {
>         int i=0;
>         unsigned char * p = (unsigned char *)s;
>         for(i=0; i<n; i++)
>         {
>                 p[i]=0;
>         }
>         return s;
> }
>
> Here are the oprofile results for the same select query.
>
> CPU: ARM/XScale PMU2, speed 0 MHz (estimated)
> Counted CPU_CYCLES events (clock cycles counter) with a unit mask of
> 0x00 (No unit mask) count 100000
> samples  %        image name               app name
> symbol name
> -------------------------------------------------------------------------------
>   1        5.2e-04  postgres                 postgres
> LockAcquire
>   1        5.2e-04  postgres                 postgres
> set_ps_display
>   20        0.0103  postgres                 postgres
> pg_vsprintf
>   116695   60.2947  postgres                 postgres                 dopr
> 116717   60.3061  postgres                 postgres
> gmm_memset
>   116717   60.3061  postgres                 postgres
> gmm_memset [self]
> -------------------------------------------------------------------------------
> 20304    10.4908  oprofiled                oprofiled                (no
> symbols)
>   20304    10.4908  oprofiled                oprofiled
> (no symbols) [self]
> -------------------------------------------------------------------------------
>   4587      2.3700  vmlinux                  vmlinux
> rest_init
>   6627      3.4241  vmlinux                  vmlinux
> cpu_idle
> 11214     5.7941  vmlinux                  vmlinux
> default_idle
>   11214     5.7941  vmlinux                  vmlinux
> default_idle [self]
> -------------------------------------------------------------------------------
>   16151     8.3450  vmlinux                  vmlinux
> rest_init
> 9524      4.9209  vmlinux                  vmlinux                  cpu_idle
>   9524      4.9209  vmlinux                  vmlinux
> cpu_idle [self]
>   6627      3.4241  vmlinux                  vmlinux
> default_idle
> -------------------------------------------------------------------------------
> 5111      2.6408  oprofile                 oprofile                 (no
> symbols)
>   5111      2.6408  oprofile                 oprofile
> (no symbols) [self]
>
> oprofile shows dopr is making most of the calls to memset.
>
> Are these results typical? If memset is indeed using over 50% of the CPU
> something seems seriously wrong.
>
> Should I be expecting more performance from this hardware than what I'm
> getting in these tests?
>
> Regards,
> George McCollister
>
>
>
>


--
H. Hall
ReedyRiver Group LLC
http://www.reedyriver.com