Thread: 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,
【節約！】インターネット代、見直しませんか？ 月額1,480円で快適ブロードバンド
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,
【節約！】インターネット代、見直しませんか？ 月額1,480円で快適ブロードバンド
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が安くなる！（ダウンロード版） 今だけ！Windows 7特別価格を見る
> 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が安くなる！（ダウンロード版） 今だけ！Windows 7特別価格を見る
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,
【節約！】インターネット代、見直しませんか？ 月額1,480円で快適ブロードバンド
> 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,
【節約！】インターネット代、見直しませんか？ 月額1,480円で快適ブロードバンド
Hashimoto Yuya wrote:
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?
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