SQL Postgres 进程的 CPU 使用率高
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32175223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
High CPU Usage By Postgres Process
提问by Ola Fashade Samson
I have an application running on Postgres database, sometimes when I have about 8-10 people working on the application, the CPU usage soars high to something between 99-100%, The application was built on Codeigniter framework which I believe had made provision for closing up connections to the database each and every time it is not needed, What could be solution to this problem. I would appreciate any suggestions. Thank you
我有一个在 Postgres 数据库上运行的应用程序，有时当我有大约 8-10 人在处理该应用程序时，CPU 使用率飙升至 99-100% 之间，该应用程序构建在 Codeigniter 框架上，我认为该框架已为每次不需要时关闭与数据库的连接，这个问题的解决方案是什么。我将不胜感激任何建议。谢谢
Basically, what the people do on the application is to running insert queries but at a very fast rate, A person could run between 70 - 90 insert queries in a minute.
基本上，人们在应用程序上所做的是运行插入查询，但速度非常快，一个人可以在一分钟内运行 70 - 90 个插入查询。
回答by Ajeet Khan
I came across with the similar kind of issue. The reason was - some transactions were getting stuck and running since long time. Hence CPU utilization got increased to 100%. Following command helped to find out the connections running for the longest time:
我遇到了类似的问题。原因是 - 一些事务很长时间以来一直卡住并运行。因此 CPU 利用率提高到 100%。以下命令有助于找出运行时间最长的连接：
SELECT max(now() - xact_start) FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');
This command shows the the amount of time a connection has been running. This time should not be greater than an hour. So killing the connection which was running for a long long time or stuck at any point worked for me. I followed thispost for monitoring and solving my issue. Postincludes lots of useful commands to monitor this situation.
此命令显示连接已运行的时间。这个时间不应超过一个小时。因此，终止运行了很长时间或在任何时候卡住的连接对我有用。我按照这篇文章来监控和解决我的问题。Post包含许多有用的命令来监控这种情况。
回答by Samuli Pahaoja
You need to find out what PostgreSQL is doing. Relevant resources:
您需要了解 PostgreSQL 在做什么。相关资源：
Once you find what the slow or the most common queries are use, use EXPLAINto make sure they are being executed efficiently.
一旦你找到了最慢或最常见的查询正在使用什么，使用 EXPLAIN来确保它们被有效地执行。
