Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri,  1 Feb 2013 10:38:26 +0100, Alexander Farber 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

I believe an index on pref_money.id could help.
Without it Postgres is, if I understand correctly, making 173936 table scans on pref_money
to try (and fail) to find for each deleted row the referencing row in that table.
 
> #  \d pref_users
[...]

-- 
Michał Politowski


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux