Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> wrote: > By increasing shared_buffers,effective_cache_size ,work_mem, > maintainance etc , we can achieve performance in select queries. > > But In my application about 200 connections are made to DB server > and insert into 2 tables occured. > And it takes more than hours to complete. Unless you have 100 cores, 200 connections is counter-productive. You should probably be looking at a connection pooler which can route the requests of that many clients through a connection pooled limited to a number of database connections somewhere between two and three times the number of actual cores. Both throughput and response time will probably improve dramatically. The other thing is that for good performance with writes, you should be using a hardware RAID controller with battery-backed cache, configured fro write-back. You should also be trying to group many writes into a single database transaction where that is feasible, particularly when the writes are related in such a way that you wouldn't want to see some of them in the database without others. > I understand the variable checkpoint_segments & want to know is > there any more ways to increase the write performance. One obvious omission from your list is wal_buffers, which should almost always be set to 16MB. If you can afford to lose some transactions after an apparently successful commit, you could look at turning off synchronous_commit. If you don't mind losing the entire database on a crash, there are lots of other settings you could use, which is collectively often referred to as "DBAs running with scissors." Most people don't want to do that, but there are some cases where it makes sense: if there are redundant databases, the database is easily rebuilt from other sources, or the data is just not that important. I'm afraid that to get more detailed advice, you would need to provide more details about the problem. http://wiki.postgresql.org/wiki/SlowQueryQuestions -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
