Re: Optimal settings for embedded system running PostgreSQL

> ________________________________
> 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