Thread: Optimal settings for embedded system running PostgreSQL
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
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: >> ________________________________ >> 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 >
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.