Обсуждение: Diagnosing poor insert performance in production?
Where can I look to if I'm trying to diagnose poor insert performance? The database is behind a pgbouncer instance receiving on average ~1100 requests per second. It's read heavy, but has the occasional insert. I'd estimate about 20-50 per minute. In the past few days, we loaded a large amount of rows into the database (about 5 million on one table, 5 million on another, etc.) and now insert performance is poor on all tables... even tables that didn't get data loaded into them. I thought insert performance only went down when you try to concurrently insert a lot of data, because each insert has to wait on the other in the case of an auto-incrementing index. So I'm kind of lost here.... does anyone have any clues of what I should be looking at? -- View this message in context: http://postgresql.nabble.com/Diagnosing-poor-insert-performance-in-production-tp5853106.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Hi,
Could you provide more details please for us to help.
1. table definition
2. insert command
3. explain plan
Thanks!
On 10 June 2015 at 03:20, Kay <kay@9cloud.us> wrote:
Where can I look to if I'm trying to diagnose poor insert performance?
The database is behind a pgbouncer instance receiving on average ~1100
requests per second. It's read heavy, but has the occasional insert. I'd
estimate about 20-50 per minute.
In the past few days, we loaded a large amount of rows into the database
(about 5 million on one table, 5 million on another, etc.) and now insert
performance is poor on all tables... even tables that didn't get data loaded
into them.
I thought insert performance only went down when you try to concurrently
insert a lot of data, because each insert has to wait on the other in the
case of an auto-incrementing index. So I'm kind of lost here.... does anyone
have any clues of what I should be looking at?
--
View this message in context: http://postgresql.nabble.com/Diagnosing-poor-insert-performance-in-production-tp5853106.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Regards,
Ang Wei Shan
Ang Wei Shan
On Wed, Jun 10, 2015 at 10:26 PM, Wei Shan <weishan.ang@gmail.com> wrote: > Hi, > > Could you provide more details please for us to help. > > 1. table definition > 2. insert command > 3. explain plan Yeah, as always, please follow the guide here. https://wiki.postgresql.org/wiki/Slow_Query_Questions In particular, often the very first thing to determine is if you are facing a storage (aka: i/o) based issue. Storage problems have a very different set of resolutions. merlin