Hi,
What Albe said about PKs. I'm also not very fond of people using text
in keys (even if it's a varchar or a char field). Test the same thing
with a numeric key and you are likely to see a difference.
Apart from making sure your design is ok, you might want to keep this
stuff well in the background, firing it as a frequent (and small) job.
Sort of "your own private vacuum job".
Something like
delete from pref_users
where
id not in ( select distinct id
from pref_money )
limit 100;
Where 100 may be any number of records you find to be compatible with
a smooth performance. Keep calling this stuff at a suitable interval
(X secs in between each call), and it will silently do the cleaning
without creating giant transactions.
You definitely want to make a good design BEFORE doing this, though.
Cheers
Bèrto
On 1 February 2013 09:38, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:
> 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
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
