Hello,
in a Facebook game running on
PostgreSQL 8.4.13 and having so many players:
# select count(*) from pref_users;
count
--------
223964
I am trying to get rid of inactive users,
who just visited the canvas page, but
never played (I'm sure, Facebook has
a clever-sounding name for them):
# select count(*) from pref_users
where id not in (select distinct id from pref_money);
count
--------
173936
(1 row)
So I call:
# delete from pref_users
where id not in (select distinct id from pref_money);
but that query lasts forever and
what's more troubling me - it blocks
the async queries of my game daemon
(the Perl function pg_ready starts returning
false all the time and my game accumulates
thousands of yet-to-be-executed SQL queries).
The good news is, that my quad server
doesn't hang - I just see 1 postmaster
process at 90-100% CPU but total load is 20%.
Also my game daemon in Perl recovers
and executes the thousands of queued
up async queries, when I interrupt the
above DELETE query with CTRL-C at
the pgsql prompt - i.e. my game is not buggy.
My question is how handle this?
Why does deleting takes so long,
is it because of CASCADES?
And why does it make the pg_ready
calls of my game daemon return false?
The users I'm deleting aren't active,
they shouldn't "intersect" with the
async queries of my game daemon.
Below are the both SQL tables involved,
thank you for any insights.
Regards
Alex
# \d pref_money
Table "public.pref_money"
Column | Type | Modifiers
--------+-----------------------+-----------------------------------------
id | character varying(32) |
money | integer | not null
yw | character(7) | default to_char(now(), 'IYYY-IW'::text)
Indexes:
"pref_money_money_index" btree (money DESC)
"pref_money_yw_index" btree (yw)
Foreign-key constraints:
"pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) ON
DELETE CASCADE
# \d pref_users
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+--------------------
id | character varying(32) | not null
first_name | character varying(64) |
last_name | character varying(64) |
female | boolean |
avatar | character varying(128) |
city | character varying(64) |
login | timestamp without time zone | default now()
last_ip | inet |
logout | timestamp without time zone |
vip | timestamp without time zone |
mail | character varying(254) |
medals | integer | not null default 0
Indexes:
"pref_users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "pref_cards" CONSTRAINT "pref_cards_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_catch" CONSTRAINT "pref_catch_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_chat" CONSTRAINT "pref_chat_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_discuss" CONSTRAINT "pref_discuss_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_game" CONSTRAINT "pref_game_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_match" CONSTRAINT "pref_match_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_payment" CONSTRAINT "pref_payment_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_rep" CONSTRAINT "pref_rep_author_fkey" FOREIGN KEY
(author) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_rep" CONSTRAINT "pref_rep_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_scores" CONSTRAINT "pref_scores_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_status" CONSTRAINT "pref_status_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
TABLE "pref_votes" CONSTRAINT "pref_votes_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id) ON DELETE CASCADE
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
