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.m.guerrero@xxxxxxxxxxx> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
