Alexander Farber skrev 2010-10-04 11.20:
Hello Postgres users,
I have a Linux website with phpBB serving a small Facebook game:
# uname -a
Linux XXXXX 2.6.18-194.17.1.el5 #1 SMP Wed Sep 29 12:50:31 EDT 2010
x86_64 x86_64 x86_64 GNU/Linux
# cat /etc/redhat-release
CentOS release 5.5 (Final)
# rpm -qa | grep -i postgres
postgresql-devel-8.1.21-1.el5_5.1
postgresql-8.1.21-1.el5_5.1
postgresql-server-8.1.21-1.el5_5.1
postgresql-devel-8.1.21-1.el5_5.1
postgresql-libs-8.1.21-1.el5_5.1
postgresql-libs-8.1.21-1.el5_5.1
postgresql-docs-8.1.21-1.el5_5.1
It works generally ok and with little load since June.
(My game has less than 100 users and I have AMD/Quad+4GB)
On Friday I've installed a cronjob (full source code at the bottom):
3 3 * * * psql -a -f
$HOME/bin/clean-phpbb-forum.sql
and on Monday I've found out, that the site is overloaded:
top - 10:16:56 up 3 days, 23:56, 1 user, load average: 20.55, 21.38, 22.92
Tasks: 157 total, 24 running, 133 sleeping, 0 stopped, 0 zombie
Cpu(s): 98.9%us, 1.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4019028k total, 3073968k used, 945060k free, 50604k buffers
Swap: 2104496k total, 132k used, 2104364k free, 2316196k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28974 postgres 16 0 122m 14m 10m R 73.3 0.4 20:56.06 postmaster
28727 postgres 16 0 121m 13m 10m R 54.7 0.3 37:58.58 postmaster
28714 postgres 16 0 122m 14m 10m R 50.4 0.4 38:38.98 postmaster
29412 postgres 16 0 121m 13m 10m R 46.5 0.4 8:03.96 postmaster
28542 postgres 16 0 122m 14m 10m R 46.2 0.4 58:49.38 postmaster
28482 postgres 16 0 122m 14m 10m R 45.8 0.4 61:53.37 postmaster
28468 postgres 16 0 122m 14m 10m R 44.2 0.4 62:46.17 postmaster
29017 postgres 16 0 122m 14m 10m R 43.9 0.4 19:17.06 postmaster
28929 postgres 15 0 122m 14m 10m R 42.2 0.4 22:01.43 postmaster
28500 postgres 16 0 122m 14m 10m R 41.3 0.4 59:40.23 postmaster
28460 postgres 16 0 122m 14m 10m R 40.6 0.4 64:17.16 postmaster
28894 postgres 16 0 122m 14m 10m R 38.6 0.4 23:35.53 postmaster
28489 postgres 16 0 122m 14m 10m R 36.0 0.4 60:32.59 postmaster
28719 postgres 15 0 121m 13m 10m R 25.2 0.3 38:10.33 postmaster
29496 postgres 16 0 121m 13m 10m R 22.9 0.4 4:20.32 postmaster
28556 postgres 15 0 122m 14m 10m R 17.7 0.4 57:32.62 postmaster
28735 postgres 15 0 122m 14m 10m R 15.7 0.4 36:09.45 postmaster
29602 postgres 15 0 119m 11m 9680 S 8.2 0.3 0:00.25 postmaster
28457 postgres 17 0 122m 14m 9m R 3.6 0.4 64:34.38 postmaster
26092 apache 15 0 238m 16m 3740 S 0.3 0.4 0:03.38 httpd
29596 afarber 15 0 12744 1116 800 R 0.3 0.0 0:00.09 top
1 root 15 0 10352 700 592 S 0.0 0.0 0:01.69 init
I understand, that I probably supply not enough info,
but how would you approach debugging this problem?
I would connect to the db with psql and query the pg_stat_activity and
the pg_locks views.
I've run my cronjob numerous times from CLI - it works ok
and takes only few seconds. I've installed it as an every-3 mins
cronjob on my development VM - it works ok there too.
My biggest problem is, that I don't see any information
from Postgres at the production site - why did it have to start
so many postmaster's (whatever those processes do).
Those are backends running queries.
The only log file I've found has been /var/lib/pgsql/pgstartup.log
and there is nothing suspicious there. (Also nothing related to Postgres
in messages, mcelog (empty), audit.log, access_log, error_log).
You might want to increase logging. Take a look at
http://www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html
for details.
Regards
/roppert
Please give me some hints
Alex
# cat bin/clean-phpbb-forum.sql
start transaction;
delete from phpbb_users
where user_type=1 and user_inactive_reason=1 and user_lastvisit=0 and
age(to_timestamp(user_regdate))>interval '3 days';
create temp table old_topics (topic_id integer) on commit delete rows;
create temp table old_posts (post_id integer) on commit delete rows;
insert into old_topics select topic_id from phpbb_topics
where forum_id=5 and topic_poster=1 and
age(to_timestamp(topic_time))>interval '7 days';
-- select count(topic_id) as "old topics:" from old_topics;
insert into old_posts select p.post_id from phpbb_posts p, old_topics t
where p.forum_id=5 and p.poster_id=1 and p.topic_id=t.topic_id;
-- select count(post_id) as "old posts:" from old_posts;
delete from phpbb_posts where post_id in (select post_id from old_posts);
delete from phpbb_topics where topic_id in (select topic_id from old_topics);
update phpbb_config set
config_value = (select count(topic_id) from phpbb_topics)
where config_name = 'num_topics';
update phpbb_config set
config_value = (select count(post_id) from phpbb_posts)
where config_name = 'num_posts';
update phpbb_users set
user_posts = (select count(post_id) from phpbb_posts where poster_id=1)
where user_id = 1;
update phpbb_forums set
forum_posts = (select count(post_id) from phpbb_posts),
forum_topics = (select count(topic_id) from phpbb_topics),
forum_topics_real = (select count(topic_id) from phpbb_topics)
where forum_id = 5;
commit
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
