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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Eduardo Vázquez Rodríguez | 2004-05-24 17:54:00 | Copy from problem |
Previous Message | Tom Lane | 2004-05-24 06:14:54 | Re: !!URGENT!! Should I keep INDEX on the table? |