Обсуждение: Troubles with Postgresql performance
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.
Denis Mironchuk <denis.mironchuk@ardas.dp.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