Dear Postgres Masters:
We are using postgres 7.4 in our java application on RedHat linux. The Java application connects to Postgres via JDBC. The application goes through a ‘discovery’ phase, whereas it adds large amount of data into postgres. Typically, we are adding about a million records in various tables. The application also issues multiple queries to the database at the same time. We do not delete any records during the discovery phase. Both the java application and the postgres are installed on the same machine.
At the beginning, the application is able to add in the order of 100 record per minute. Gradually (after several hours), it slows down to less than 10 records per minute. At this time, postgres processes take between 80-99% of CPU. When we reindex the database, the speed bumps up to about 30 records per minute. Now, postgres server takes between 50-70% CPU.
We have the following in the postgresql.conf :
max_fsm_pages = 500000
fsync = false
We certainly can not live with this kind of performance. I believe postgres should be able to handle much larger datasets but I can not point my finger as to what are we doing wrong. Can somebody please point me to the right direction.
With kind regards,
-- Shachindra Agarwal.