Re: Optimal settings for embedded system running PostgreSQL

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.