Skip site navigation (1) Skip section navigation (2)

Re: !!URGENT!! Should I keep INDEX on the table?

From: "Rajan Bhide" <rbhide(at)nulinkinc(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-novice(at)postgresql(dot)org>," Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: "Bruno Wolff III" <bruno(at)wolff(dot)to>,"Oliver Fromme" <olli(at)lurza(dot)secnetix(dot)de>
Subject: Re: !!URGENT!! Should I keep INDEX on the table?
Date: 2004-05-24 07:11:13
Message-ID: FF851C7EEB75954F9BCFB5CA117AB1EC6742D1@delta.nulinkinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
So concentrating on the client side issue first:

This was reported by the customer site.
The system info is as follows:
Sun Netra T4 system with 4G physical memory running Solaris 9 with
Postgres 7.4.2.
The table is accessed by two independent processes : One performs
Insertion and Other performs Select and Delete.
Table structure:
CREATE TABLE "cdr_archive" (
        "accesstime" bigint,
        "cgfaddr" bigint not NULL,
        "cgfseqnum" integer default 0,
        "reqindex" integer default 0,
        "aaamgrid" smallint,
        "cdrcount"  integer,
        "reqcount" integer,
        "transid" bigint,
        "reqinfo" bytea not NULL,
        "status" smallint default 0,
        UNIQUE ( accesstime, cgfaddr, transid, reqindex )
);


CREATE UNIQUE INDEX "access_time_req_idx"
       ON "cdr_archive"("accesstime","reqindex","transid");
       

The "reqinfo" field contains binary data stored using bytea type of
around 65K (*4 for bytea conversion).

The transaction rate (Insertion/Deletion) is around 2M per six hours and
As I mentioned the App is performing "VACCUM ANALYSE" on the table once
every 10 mins.
The error log file is not available as this was reported by customer
site.

I have introduced external reindexing mechanism using crob based job
which periodically DROP and CREATE "access_time_req_idx" index every 2
hours after which both the problems (Prob#2 also) are not seen.

Under what circumstances "message type 0x44 arrived from server while
idle." is reported to client?
Is this a problem with the transaction rate or the data type used?

Thanks,
Rajan Bhide



"Rajan Bhide" <rbhide(at)nulinkinc(dot)com> writes:
> 2)WARNING:  specified item offset is too large
> PANIC:  failed to add item to the page for "cdr_transid_key"

[blinks]  Can you offer a reproducible test case for that one?

Your mail seems to mention a client bug (message ... while idle), a
server bug (the above), performance issues, and I'm not sure what else.
It would be well to try to subdivide your concerns. I also wonder
whether you might be suffering from flaky-hardware problems, seeing that
you are reporting at least two odd behaviors that no one else has seen
with PG 7.4.

			regards, tom lane


pgsql-novice by date

Next:From: Eduardo Vázquez RodríguezDate: 2004-05-24 17:54:00
Subject: Copy from problem
Previous:From: Tom LaneDate: 2004-05-24 06:14:54
Subject: Re: !!URGENT!! Should I keep INDEX on the table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group