Re: Deleting 173000 records takes forever, blocks async queries for unrelated records - Mailing list pgsql-general

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@gmail.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@postgresql.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.