pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
How to Speed up Insert from Multiple Connections 2+ messages / 2 participants [nested] [flat]
* How to Speed up Insert from Multiple Connections @ 2011-08-01 07:31 Adarsh Sharma <adarsh.sharma@orkash.com> 0 siblings, 1 reply; 2+ messages in thread From: Adarsh Sharma @ 2011-08-01 07:31 UTC (permalink / raw) To: pgsql-performance Dear all, I research a lot on Postgresql Performance Tuning and find some parameters to increase the select performance in postgresql. 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. I understand the variable checkpoint_segments & want to know is there any more ways to increase the write performance. Thanks ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to Speed up Insert from Multiple Connections @ 2011-08-01 13:57 Kevin Grittner <Kevin.Grittner@wicourts.gov> parent: Adarsh Sharma <adarsh.sharma@orkash.com> 0 siblings, 0 replies; 2+ messages in thread From: Kevin Grittner @ 2011-08-01 13:57 UTC (permalink / raw) To: Adarsh Sharma <adarsh.sharma@orkash.com>; pgsql-performance Adarsh Sharma <adarsh.sharma@orkash.com> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-08-01 13:57 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-08-01 07:31 How to Speed up Insert from Multiple Connections Adarsh Sharma <adarsh.sharma@orkash.com> 2011-08-01 13:57 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox