Re: Using Postgres to store high volume streams of sensor readings

Ciprian Dorin Craciun wrote:
[............]
>
> So what can I do / how could I optimize the use of Postgres for this usage?
>

Hello, here you have some comments that will probably help you to get
more from this test machine ......

>
> * test machine: Linux (Ubuntu 8.04 x64), IBM x3750, 2 x 500Gb,
> SCSI, RAID 0, 8Gb RAM, 2 x Intel Xeon 5160 x 2 core (3Ghz, 4Mb L2);
> * Postgres version: 8.3.3;
>

(RAID 0 is never a good thing with databases if you don't have another
redundant system that can be used to restore your data or if you want to
minimize your downtime.)

Putting the database transaction logs ($PGDATA/pg_xlog) on its own
dedicated disk resource will probably increase write performace.

>>
>> create index sds_benchmark_data__client_sensor__index on sds_benchmark_data (client, sensor);
>

You don't need this index if the primary key is (client, sensor, timestamp).

>> shared_buffers = 24MB

I would increase this to 25% of your RAM. 2GB in the test machine (if it
is a dedicated postgres server). It will help read-rate.

You will probably have to increase kernel.shmmax and kernel.shmall in
/etc/sysctl.conf (linux)

>> fsync = off

Do you have the results with this on?

>> checkpoint_segments = 32

I would increase this to 128-256 if you work with large write loads
(several GB of data). $PGDATA/pg_xlog would use some extra disk if you
change this value.

>> effective_cache_size = 1024MB
>

50% of your RAM. 4GB in the test machine (if it is a dedicated postgres
server). It will probably help read-rate.

In addition, I will try to change these parameters also:

wal_buffers = 64
random_page_cost = 2.0

In general, faster and more disks in a RAID 1+0 / 0+1 will help write
performace. autovacuum should be on.

regards.
--
Rafael Martinez, <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/