Deadlocks, page size, hash

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andrew Wilson - 17 Dec 2003 12:27 GMT

Hi,

We have an important system table (fimbul_status) that usually gives no
problems. However the last 2 days the number of deadlocks has rocketed, and
I suspect that it has something to do with this table (it is a very central
table used by many rules).

I've talked with a dba and tried running "modify to reoganize" and still get
77004 overflow pages (there are actually 795 rows in the table right now,
from "select count(*)").

They originally tried to organise the table that 1 page=1 row, but to my
mind it looks like 1 page=2 rows?

Row size I can easily get changed but number of overflow pages in a heap?

Thanks.

Andrew Wilson

Name: fimbul_status
Owner: fiksdba
Created: 17.04.2003 18:11:21
Location: ii_database,
db1,
db2,
db3
Type: user table
Version: OI2.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 871
Row width: 871
Number of rows: 574
Storage structure: hash with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 79058
Overflow data pages: 77004
Journaling: enabled
Base table for view: yes
Optimizer statistics: yes; see avg count below, more info in the iistats
catalog

Column Information:
Key Avg
Count
Column Name Type Length Nulls Defaults Seq Per
Value
medl_ident integer 4 no yes 1
unique
fra_dato date no yes
sagsbeh_godkendt_jn char 1 no yes
sbhgrp_gruppe char 10 no yes
fistako_kode char 4 no yes
fejl_tekst char 420 no yes
reg_init char 12 no yes
reg_tid date no yes
fimbul_start_tid date no yes
fimbul_slut_tid date no yes
afd_nr char 3 no yes
filial_nr char 3 no yes
cpr_nr_dag char 2 no yes
udbetalte_ydelser char 60 no yes
fista_dato_nr integer 4 no yes
spildplads_bruges_ej char 300 no yes

Secondary indexes: none

Reply to this Message

Ronald Jeninga - 17 Dec 2003 14:04 GMT

Hi,

a modify to reorganize won't help you. It is only a method to redistribute a
table from a set of locations to another set of locations.

What you need is a

modify fimbul_status to hash unique on medl_ident
with minpages = 4096; /* or some other power of two */

This will actually reorganize the storage structure. (and drop all indexes,
but since you don't have any, that's no problem). Because of the number of
rows in the table, it'll only last a few seconds (after you got the lock).

You'll have to watch the overflow pages. If they still occur, choosing
another number for the minpages might help. (Another number of pages
results into a differently behaving hash-function).

selecting the tid/512 gives you a clue on which page(s) your data resides.
If the selected number is larger than the number of mainpages, it is an
overflowpage. (The number of mainpages = Number of pages - Overflow data pages).

HTH

Ronald

> Hi,
>
[quoted text clipped - 65 lines]
>
> Secondary indexes: none

Signature

independIT Integrative Technologies GmbH
Sitz der Gesellschaft: Schrobenhausen
HRB Neuburg B 1.521
Geschäftsführer:
Dieter Stubler, Dipl. Inform. (FH)
Ronald Jeninga, Diplom Mathematiker

Reply to this Message

Andrew Wilson - 18 Dec 2003 22:35 GMT

Hi, thanks,

"modify fimbul_status to hash unique"

solved the problem for now (but still have about 6% overflow pages!).

Problem is caused by a 3 monthly batch process that inserts many more rows
than usual into the table, and after they are deleted we are left with a
grossly overflowed table (or were they overflowed before the deletes?). We
have a weekly batch process that reorganises our tables, but unfortunately
the other batch job ran after the reorganise.

This is the only hash table we have in the system, and that to avoid locks
on index pages, as the table is so central.

I'm a bit busy right now on several unrelated projects, but will certainly
look into minpages next year (unless I can persuade a dba, or someone in the
"fimbul" team, to look into it).

Andrew

Hi,

a modify to reorganize won't help you. It is only a method to redistribute a
table from a set of locations to another set of locations.

What you need is a

modify fimbul_status to hash unique on medl_ident
with minpages = 4096; /* or some other power of two */

This will actually reorganize the storage structure. (and drop all indexes,
but since you don't have any, that's no problem). Because of the number of
rows in the table, it'll only last a few seconds (after you got the lock).

You'll have to watch the overflow pages. If they still occur, choosing
another number for the minpages might help. (Another number of pages
results into a differently behaving hash-function).

selecting the tid/512 gives you a clue on which page(s) your data resides.
If the selected number is larger than the number of mainpages, it is an
overflowpage. (The number of mainpages = Number of pages - Overflow data
pages).

HTH

Ronald

On Wed, 17 Dec 2003 13:27:08 +0100
"Andrew Wilson" <bluemalov@hotmail.com> wrote:

> Hi,
>
[quoted text clipped - 65 lines]
>
> Secondary indexes: none

Signature

independIT Integrative Technologies GmbH
Sitz der Gesellschaft: Schrobenhausen
HRB Neuburg B 1.521
Gesch?ftsf?hrer:
Dieter Stubler, Dipl. Inform. (FH)
Ronald Jeninga, Diplom Mathematiker

Reply to this Message

Ronald Jeninga - 19 Dec 2003 11:08 GMT

Hi,

having a lot of main pages doesn't harm, especially if you intend to have only
one row per page. So you could prepare for your 3-monthly batch by increasing
the number of mainpages to something far more than your 3-monthly batch will
ever need.
The only disadvantage is the amount of diskspace consumed by the table then.

So if you issue a

modify fimbul_status to hash unique on ...
with minpages = 262144; /* or 524288, or ... */

your 3-month Job won't do any harm anymore (asuming it needs about 80.000 pages).
Under normal circumstances you'll have one row per page and no overflow.
During your large job you might have more than one row per page (which is not
really a problem then) and only a little overflow (or maybe even no overflow
at all).
As I said, the disadvantage is that you have a 256MB file in your filesystem.
There are no negative impacts on your caching, as long as you do full qualified
searches.

This is a warning: DON'T DO TABLE SCANS on such hash-tables, they will be horribly
slow and sweep out your cache, thereby reducing the overall system performance.

HTH

Ronald

> Hi, thanks,
>
[quoted text clipped - 16 lines]
>
> Andrew

<SNIP>

Signature

independIT Integrative Technologies GmbH
Sitz der Gesellschaft: Schrobenhausen
HRB Neuburg B 1.521
Geschäftsführer:
Dieter Stubler, Dipl. Inform. (FH)
Ronald Jeninga, Diplom Mathematiker

Reply to this Message

Fred Myers - 18 Dec 2003 06:33 GMT

-----Original Message-----
From: Andrew Wilson [mailto:bluemalov@hotmail.com]
Sent: Wednesday, 17 December 2003 8:27 PM
Subject: Deadlocks, page size, hash

We have an important system table (fimbul_status) that usually gives no
problems. However the last 2 days the number of deadlocks has rocketed, and
I suspect that it has something to do with this table (it is a very central
table used by many rules).

I've talked with a dba and tried running "modify to reoganize" and still get
77004 overflow pages (there are actually 795 rows in the table right now,
from "select count(*)").

They originally tried to organise the table that 1 page=1 row, but to my
mind it looks like 1 page=2 rows?

Row size I can easily get changed but number of overflow pages in a heap?

Andrew

There are a number of ways to fool Ingres into keeping only
one row per page.

Add a column to take the row length to more than
1005 is perhaps the least elegant, but is understandable to
programmers/administrators that follow after, if documented.

More creative is to run sql and modify the table changing the
fillfactor to less than (rowsize/2008) = 43 which will achieve
the same result. Given that you want to remove the overflow
pages anyway, changing the fillfactor using the scripts created
by copydb may also be a choice.

Enjoy.

Fred Myers

Reply to this Message