Re: Postgress is taking lot of CPU on our embedded hardware.

On Sat, Jan 28, 2012 at 19:11, Jayashankar K B
<Jayashankar(dot)KB(at)lnties(dot)com> wrote:
> But we are stumped by the amount of CPU Postgres is eating up.

You still haven't told us *how* slow it actually is and how fast you
need it to be? What's your database layout like (tables, columns,
indexes, foreign keys)? What do the queries look like that you have
problems with?

> Our database file is located on a class 2 SD Card. So it is understandable if there is lot of IO activity and speed is less.

Beware that most SD cards are unfit for database write workloads,
since they only perform very basic wear levelling (in my experience
anyway -- things might have changed, but I'm doubtful). It's a matter
of time before you wear out some frequently-written blocks and they
start returning I/O errors or corrupted data.

If you can spare the disk space, increase checkpoint_segments, as that
means at least WAL writes are spread out over a larger number of
blocks. (But heap/index writes are still a problem)

They can also corrupt your data if you lose power in the middle of a
write -- since they use much larger physical block sizes than regular
hard drives and it can lose the whole block, which file systems or
Postgres are not designed to handle. They also tend to not respect
flush/barrier requests that are required for database consistency.

Certainly you should do such power-loss tests before you release your
product. I've built an embedded platform with a database. Due to disk
corruptions, in the end I opted for mounting all file systems
read-only and keeping the database only in RAM.

> Any configuration settings we could check up?

For one, you should reduce max_connections to a more reasonable number
-- I'd guess you don't need more than 5 or 10 concurrent connections.

Also set synchronous_commit=off; this means that you may lose some
committed transactions after power loss, but I think with SD cards all
bets are off anyway.

Regards,
Marti