100% of CPU utilization postgres process
Hello,
I observed the event that CPU utilization of the process related to postgres
records almost 100% for unknown reason. It would be appreciated if any of you
provide any information on this.
The following line is a part of the result of "ps -auxeww".
=============================================================
pgsql 682 99.0 0.1 9336 2740 ?? Rs 27Nov08 343573:19.27 USER=pgsql MAIL=/var/mail/pgsql HOME=/usr/local/pgsql BLOCKSIZE=K PGLOCALEDIR=/usr/local/share/locale PGSYSCONFDIR=/usr/local/etc/postgresql PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/usr/local/pgsql/bin SHELL=/bin/sh PWD=/usr/local/pgsql FTP_PASSIVE_MODE=YES PGDATA=/sa/db postgres: stats collector process (postgres)
=============================================================
Judging from the result, I could see that stats collector process caused this unusually high CPU utilization rate.
I found similar problem at http://archives.postgresql.org/pgsql-general/2008-06/msg00934.php, although there seemed
no clear cause proven nor the statement that it's because of postgres bug.
Also, the following message was seen in the postgres log.
==========================================================================================
Nov 12 02:49:53 postgres[681]: [2-1] WARNING: worker took too long to start; cancelled
Nov 12 02:50:53 postgres[681]: [3-1] WARNING: worker took too long to start; cancelled
…
Nov 12 11:14:12 postgres[681]: [506-1] WARNING: worker took too long to start; cancelled
Nov 12 11:15:12 postgres[681]: [507-1] WARNING: worker took too long to start; cancelled
==========================================================================================
Once the message which started with "postgres[xxx]" appeared, it had been repeated until
the OS was manually shut down. I'm not sure if each of the two could happen separately nor
if one of the two could trigger the other.
Do any of you happen to know more than what was posted at
http://archives.postgresql.org/pgsql-general/2008-06/msg00934.php ?
The event above was observed under the condition as follows.
-Postgres version : "PostgreSQL 8.3.3 on i386-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD]"
(It was installed via ports.)
-To connect the PostgreSQL Database, the following drivers are used.
php5-pdo_pgsql-5.2.12
p5-DBD-Pg-2.16.0
-Operating system and version
・FreeBSD 7.0-RELEASE-p2
-Hardware
・CPU : Intel, Xeon2.4
・RAM : 2GB RAM
・Storage
RAID controller : LSI MegaRAID
-battery backed cache : none
-write-back : disabled
-Software RAID : not used
-SAN : not used
-disk configuration :
3 HITACHI 7,200rpm SATA disks in RAID5
- filesystem : ufs
Regards,
_________________________________________________________________
【節約！】インターネット代、見直しませんか？
http://campaign.live.jp/eaccess/Top/
Hashimoto Yuya wrote:
-Postgres version : "PostgreSQL 8.3.3 on i386-portbld-freebsd7.0,
compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD]"
8.3.3 is fairly old, they are up to 8.3.9 in that version. seee the
release notes for each version from 8.3.4 to 8.3.9 to see what bugs were
fixed...
http://www.postgresql.org/docs/current/static/release.html
-disk configuration :
3 HITACHI 7,200rpm SATA disks in RAID5
raid 5 performs rather badly on database update kind of operations,
especially without a battery backed writeback cache
not saying this is your problem, but its not a real good idea. We use
raid1+0 aka raid10 for our database volumes
Hashimoto Yuya <hill_climb@hotmail.com> writes:
[ lots of time spent by stats collector process ]
How large is $PGDATA/global/pgstat.stat ?
If it's very large (many MB), try doing pg_stats_reset(). If that makes
the stats collector CPU usage drop, consider an update to PG 8.4.x,
which is more efficient at dealing with large stats files.
regards, tom lane
8.3.3 is fairly old, they are up to 8.3.9 in that version. seee the
release notes for each version from 8.3.4 to 8.3.9 to see what bugs were
fixed...
http://www.postgresql.org/docs/current/static/release.html
Thanks,
I was planning to update the postgres to the newer version, but I was not sure if the problem
would be solved or not since I couldn't find statement about stats collector ,as far as I read
through the release notes for each version from 8.3.4 to 8.3.9. That's way I posted a question,
hoping I could get any information...
Regards,
_________________________________________________________________
Windows 7とOfficeが安くなる！（ダウンロード版）
http://promotion.live.jp/special/msstore/
Thanks,
How large is $PGDATA/global/pgstat.stat ?
Unfortunately, the size of pgstat.stat was not taken when the CPU utilization of the postgress process reached nearly 100%...
If it's very large (many MB), try doing pg_stats_reset(). If that makes
the stats collector CPU usage drop, consider an update to PG 8.4.x,
which is more efficient at dealing with large stats files.
Does it mean that CPU usage rate of stats collector process could possiblly reach 100% unless the postgres is updated to the version 8.4.x, instead of 8.3.x? (So far I'm planning to update the postgres to the version 8.3.9)
Regards,
_________________________________________________________________
【節約！】インターネット代、見直しませんか？
http://campaign.live.jp/eaccess/Top/
Hashimoto Yuya wrote:
Judging from the result, I could see that stats collector process
caused this unusually high CPU utilization rate.
I found similar problem at
http://archives.postgresql.org/pgsql-general/2008-06/msg00934.php,
although there seemed
no clear cause proven nor the statement that it's because of postgres bug.
Right, that thread concluded with
http://archives.postgresql.org/pgsql-general/2008-06/msg01026.php where
Tom suggested it looked like a FreeBSD bug on that version. I just poked
around a bit, and there do seem to have been a number of bugs in their
poll() implementation in various versions of that OS, so it seems
reasonable this is just another one of those.
Note sure if Depez is reading this list or not, just added him to the cc
list here. Herbert, did you ever get anywhere with tracking this issue down?
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
On Tue, Jan 26, 2010 at 05:12:51PM -0500, Greg Smith wrote:
Hashimoto Yuya wrote:
Judging from the result, I could see that stats collector process
caused this unusually high CPU utilization rate.
I found similar problem at
http://archives.postgresql.org/pgsql-general/2008-06/msg00934.php,
although there seemed
no clear cause proven nor the statement that it's because of postgres bug.Right, that thread concluded with
http://archives.postgresql.org/pgsql-general/2008-06/msg01026.php where
Tom suggested it looked like a FreeBSD bug on that version. I just poked
around a bit, and there do seem to have been a number of bugs in their
poll() implementation in various versions of that OS, so it seems
reasonable this is just another one of those.Note sure if Depez is reading this list or not, just added him to the cc
list here. Herbert, did you ever get anywhere with tracking this issue down?
No. The database was of friend of a friend, and afair they upgraded, and
afterwards they didn't contact me back.
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007