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