Re: Troubles with Postgresql performance

Denis Mironchuk <denis(dot)mironchuk(at)ardas(dot)dp(dot)ua> wrote:

> Hello. I had a strange issue with postgresql 9.3 server. Load on
> server rapidly increased, queries which took about 4ms were
> executed for 100ms and creation a foreign key on empty table
> blocked all queries to server. After server restart everithing
> became fine again, the same foreign key was created less then in
> a second, and queries worked fast again. What may be the reason?
>
> I understand that this information is not enough to give some
> reasonable answer but still will be grateful for any advise.

There are too many possibilities to list, really.  My advice would
be to be ready to capture as much information as possible if it
happens again.  At the top of my list would be the output from
selecting everything in the pg_stat_activity and pg_locks views, as
well as the output from `vmstat 1 30` (assuming a platform which
supports that).  If you post that with the information suggested on
this page, people should be able to offer helpful advice:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

Of particular interest would be the OS, exact PostgreSQL version
(from SELECT version();), a hardware description, and the
PostgreSQL configuration settings.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company