Re: Deleting 173000 records takes forever, blocks async queries for unrelated records

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(dot)farber(at)gmail(dot)com> 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(at)postgresql(dot)org)
> 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.