Обсуждение: Optimal settings for embedded system running PostgreSQL

Поиск
Список
Период
Сортировка

Optimal settings for embedded system running PostgreSQL

От
"Christian Walter"
Дата:
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

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?

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

In addition I would like to mention that we would also be intersted in
commercial support regarding this issue if some companies providing
PostgreSQL support are monitoring this forum.

Thanks for any help,
   Christian

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


Re: Optimal settings for embedded system running PostgreSQL

От
Vick Khera
Дата:
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

Re: Optimal settings for embedded system running PostgreSQL

От
Ivan Voras
Дата:
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.


Re: Optimal settings for embedded system running PostgreSQL

От
Christian Walter
Дата:
> ________________________________
> 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

Re: Optimal settings for embedded system running PostgreSQL

От
Kenneth Buckler
Дата:
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:
>> ________________________________
>> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: Optimal settings for embedded system running PostgreSQL

От
Ivan Voras
Дата:
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.