Optimal settings for embedded system running PostgreSQL

On Thu, Jan 13, 2011 at 8:30 AM, Christian Walter
<cwalter@embedded-solutions.at> wrote:

We are currently using PostgreSQL 7.3 on an Embedded System (Based on
http://www.msc-ge.com/de/produkte/com/qseven/q7-us15w.html) running Windows
XP Embedded / SP3.

You're asking for an explosion of some sort to happen. Take a read of
this page: http://www.postgresql.org/about/news.865

On 13/01/2011 14:30, Christian Walter wrote:

Dear Members,

We are currently using PostgreSQL 7.3 on an Embedded System (Based on
http://www.msc-ge.com/de/produkte/com/qseven/q7-us15w.html) running
Windows XP Embedded / SP3. The onbard flash shows the following
performance figures:

- Average read = 15,6Mb/s
- 4Kbyte reads = 3,5Mb/s
- 1Kbyte read = 1Mb/s

This is very slow. Have you considered something more light-weight like
SQLite?

We are sometimes facing problems that the system seems to hang due to
database activity and now we are looking to improve the situation. If
the database application is running and we run the benchmark again
figures drop down to zero so we assume that the database (and
application) is responsible for the high load. Since the embedded flash
is quite hard to change we are looking for solution on improving this.
For me it seems that blocksize seems a big issue and I would like to
know if a recompile of Postgres with a larger block size would resolve
this?

You can, see --with-blocksize and --with-wal-blocksize arguments to
./configure, but flash memory is notorious for having really large block
sizes, on the order of 256 KiB - I suspect it would be very inefficient
if the database tried to do everything in such large blocks.

Are there any other options which should be set for flash based disks
since latency is a lower than on harddisks.

First, you need to confirm or find where the real problem is. Windows is
hard for debugging but you may get some information from the "perfmon"
applet.

________________________________
Von: pgsql-general-owner@postgresql.org im Auftrag von Ivan Voras
Gesendet: Do 13.01.2011 15:47
An: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Optimal settings for embedded system running
PostgreSQL

On 13/01/2011 14:30, Christian Walter wrote:

Dear Members,

We are currently using PostgreSQL 7.3 on an Embedded System (Based on
http://www.msc-ge.com/de/produkte/com/qseven/q7-us15w.html) running
Windows XP Embedded / SP3. The onbard flash shows the following
performance figures:

This was a typo. Of course we are using PostgreSQL 8.3 and not 7.3.

- Average read = 15,6Mb/s
- 4Kbyte reads = 3,5Mb/s
- 1Kbyte read = 1Mb/s

This is very slow. Have you considered something more light-weight like
SQLite?

This is comparable to a standard USB2.0 flash drive. Reads are faster
on very large block sizes but I thought postgres by default uses
8Kbyte. At 8KByte we have about 5000kbyte/s. For example if you
compare a standard flash disk, e.g.
http://www.innodisk.com/production.jsp?flashid=34 so can see that they
specify 26mb/sec as a max. The same is specified here but I think is
is only for very large block sizes. Using a different database is not
an option for us since our software depends on PostgreSQL and on its
JDBC drivers. We are working with the vendor to find a solution for
this but of course this is only a long term option.

We are sometimes facing problems that the system seems to hang due to
database activity and now we are looking to improve the situation. If
the database application is running and we run the benchmark again
figures drop down to zero so we assume that the database (and
application) is responsible for the high load. Since the embedded flash
is quite hard to change we are looking for solution on improving this.
For me it seems that blocksize seems a big issue and I would like to
know if a recompile of Postgres with a larger block size would resolve
this?

You can, see --with-blocksize and --with-wal-blocksize arguments to
./configure, but flash memory is notorious for having really large block
sizes, on the order of 256 KiB - I suspect it would be very inefficient
if the database tried to do everything in such large blocks.

I think increasing to 32Kbyte would not be a problem because 8KByte is
the default. If this can give us some benefit I am willing to give it
a try and recompile the software using VS2005.

Are there any other options which should be set for flash based disks
since latency is a lower than on harddisks.

First, you need to confirm or find where the real problem is. Windows is
hard for debugging but you may get some information from the "perfmon"
applet.

We have already planned to use perfmon for this task to double check
if its really IO related. I am sure for about 60% because a parallel
harddisk bench shows a decrease in performance when this happens.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"YES WE SCAN !"

---------------------------------------------------------------------

NEW ! > pH-independent Chlorine Sensor <

---------------------------------------------------------------------

The chlori::lyser is a revolutionary pH- and flow- independent sensor for
monitoring free chlorine or total chlorine. The digital sensor runs on
RS485-Modbus, comes works-calibrated, and allows simple "plug & measure"
functionality on all s::can terminals, panels, and software. It's internal
buffer electrolyte allows pH-independent measurement between pH 4 to 10+,
and it's 3 electrode system provides extremely stable readings, even at high
fluctuations of pH, temperature and flow - with only a minimal requirement
for maintenance.

plug&measure installation on an s::can panel is recommended. Combine it with
our pH, conductivity in just one flow-cell and on one panel, or combine with
one of our turbidity or spectral sensors on an extra panel,

and you will own the most cost efficient, compact and reliable solution for
your water monitoring purpose available today, at practically zero running
costs.

For more information please visit our website http://www.s-can.at or call
your local dealer.

---------------------------------------------------------------------

s::can - intelligent, optical, online

scan Messtechnik GmbH

Brigittagasse 22-24

A-1200 Wien/Vienna

tel. +43 1 219 73 93 - 0

fax +43 1 219 73 93 - 12

http://www.s-can.at/

SALES@s-can.at Verkauf / Sales

ACCOUNTING@s-can.at Rechnungen / Invoicing

OFFICE@s-can.at Administration / Office

MARKETING@s-can.at Marketing

SUPPORT@s-can.at Technische Unterstuetzung / Technical Support

SERVICE@s-can.at Reparaturen / Repairs

PROCUREMENT@s-can.at Einkauf / Procurement

JOBS@s-can.at Bewerbungen / Jobs

LEGAL@s-can.at Rechtsfragen / Legal questions

Geschaeftsfuehrer/President: DI Andreas Weingartner

Firmenbuchnummer/Incorporation No: FN178880i

Gerichtsstand/Court of Jurisdiction: Wien/Vienna

---------------------------------------------------------------------

Haftungserklaerung / Disclaimer

Der Inhalt dieses E-Mails ist rein informativ und spiegelt nicht
notwendigerweise den Standpunkt der Firma s::can Messtechnik GmbH wieder.

Sollten Sie eine verbindliche Auskunft benoetigen, wenden Sie sich bitte an
LEGAL@s-can.at.

This electronic message does not necessarily reflect the official point of
view of s::can Messtechnik GmbH and is considered as informal information.

To receive a formally binding answer we invite you to contact
LEGAL@s-can.at.

---------------------------------------------------------------------

-- 
--
+--------------------------------------+-----------------------------------+
Embedded Solutions                     | DI Christian Walter
Lorenz Böhler Gasse 4/4, A-1200 Wien   | cwalter@embedded-solutions.at
http://www.embedded-solutions.at       | +43-664-2486048

Have you considered switching to embedded Linux instead of XP?

This has the potential to help increase performance, as embedded Linux
will most likely have a smaller footprint.

Give this a read:
http://www.lynuxworks.com/products/whitepapers/xp-vs-linux.php3

Of course, if you're using an application which is completely reliant
on Windows, this may not be cost feasible.

But, if you application is Java based, as many embedded applications
are, the transition from XP to Linux could be pretty simple.

Ken

On Thu, Jan 13, 2011 at 11:31 AM, Christian Walter
<embedded.solutions.at@gmail.com> wrote:

On 13/01/2011 17:31, Christian Walter wrote:

Von: pgsql-general-owner@postgresql.org im Auftrag von Ivan Voras

- Average read = 15,6Mb/s
- 4Kbyte reads = 3,5Mb/s
- 1Kbyte read = 1Mb/s

This is very slow. Have you considered something more light-weight like
SQLite?

This is comparable to a standard USB2.0 flash drive. Reads are faster
on very large block sizes but I thought postgres by default uses
8Kbyte. At 8KByte we have about 5000kbyte/s. For example if you
compare a standard flash disk, e.g.
http://www.innodisk.com/production.jsp?flashid=34 so can see that they
specify 26mb/sec as a max. The same is specified here but I think is
is only for very large block sizes. Using a different database is not
an option for us since our software depends on PostgreSQL and on its
JDBC drivers. We are working with the vendor to find a solution for
this but of course this is only a long term option.

Of course, it depends on your constraints, but my suggestion was not
only related to block IO speed but more to the nature of the
application: there is a large difference between the number and the
complexity of operations between postgresql and sqlite. SQLite might
need much less IO bandwidth independently of block sizes.

As others said, switching to a non-Windows OS will probably also
increase performance, independently of database type or block sizes.

My point is: you may need to scale everything optimally for your
application, not only PostgreSQL.

Your original post doesn't really mention if your database activity is
write-mostly or read-mostly, so you can do additional tuning. For
example, if you are write-heavy and can survive a couple of last
transactions being lost, you could configure PostgreSQL for
"synchronous_commit=off" without losing complete database integrity on
power failure.