dead lock?
I have an user's table has three columns
id char(8), value decimal(6,2), time timestamp
also indexing (id, time), where id is not a primary key, it is a foreign
key from
other table. User has 24 of this tables with different name. Every 4
minute user
insert rows into the tables for few hundred rows by dbload. So each day
will
be few thousand rows insert.
And "DELETE FROM table_names WHERE time < TODAY-10";
User need to keep records for 10 days.
The problem I have is I don't see the DELETE works very well,
1) take looooooong time (hours) to delete the records from all tables.
2) some time may not delete or no more lock (with database to buffered
logging).
3) I am just wondering will this create dead lock? Because those tables
are
indexing the id, time columns, and doing insert and delete at the
same time.
Thank you!
--
Why we want to teach our babies to talk and walk,
then later we tell them "sit down!", "be quiet!" ?
Democracy is not a better way for a solution,
it is just another way to spread the blames.
--Raymond
it sounds like there is a lot of io on that disk,
with your inserts competing with deletes, the head on that
one disk is overly busy.
have you tried or thought about fragmenting this table across
at least 3 spindles or more? , and moving your index to a different disk .
( assign new dbspaces to new disks on a different spindle then fragment by
expression
on date ), that way when you do your delete a differerent thread will be
operating
on a different disk, removing contention or a greater percentage of it.
Raymond Chui wrote:
> I have an user's table has three columns
>
> id char(8), value decimal(6,2), time timestamp
>
> also indexing (id, time), where id is not a primary key, it is a foreign
> key from
> other table. User has 24 of this tables with different name. Every 4
> minute user
>
> insert rows into the tables for few hundred rows by dbload. So each day
> will
> be few thousand rows insert.
>
> And "DELETE FROM table_names WHERE time < TODAY-10";
> User need to keep records for 10 days.
>
> The problem I have is I don't see the DELETE works very well,
> 1) take looooooong time (hours) to delete the records from all tables.
> 2) some time may not delete or no more lock (with database to buffered
> logging).
> 3) I am just wondering will this create dead lock? Because those tables
> are
> indexing the id, time columns, and doing insert and delete at the
> same time.
>
> Thank you!
>
> --
> Why we want to teach our babies to talk and walk,
> then later we tell them "sit down!", "be quiet!" ?
>
> Democracy is not a better way for a solution,
> it is just another way to spread the blames.
>
> --Raymond