Re: Maintenance question / DB size anomaly... - Mailing list pgsql-performance

Dang it, Tom, don't you ever get tired of being right?  I guess I had
been focusing
on the index numbers since they came up first, and its the index
files that are > 10Gb.

Okay, so I did some digging with pg_filedump, and found the following:

.
.
.
.
Block  406 ********************************************************
<Header> -----
Block Offset: 0x0032c000         Offsets: Lower     208 (0x00d0)
Block: Size 8192  Version    2            Upper     332 (0x014c)
LSN:  logid    950 recoff 0x9ebcc6e4      Special  8192 (0x2000)
Items:   47                   Free Space:  124
Length (including item array): 212

<Data> ------
Item   1 -- Length:  472  Offset: 7720 (0x1e28)  Flags: USED
   XMIN: 1489323584  CMIN: 1  XMAX: 0  CMAX|XVAC: 0
   Block Id: 406  linp Index: 1   Attributes: 6   Size: 32
   infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)

Item   2 -- Length:  185  Offset: 7532 (0x1d6c)  Flags: USED
   XMIN: 1489323584  CMIN: 4  XMAX: 0  CMAX|XVAC: 0
   Block Id: 406  linp Index: 2   Attributes: 6   Size: 32
   infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)

Item   3 -- Length:  129  Offset: 7400 (0x1ce8)  Flags: USED
   XMIN: 1489323590  CMIN: 2  XMAX: 0  CMAX|XVAC: 0
   Block Id: 406  linp Index: 3   Attributes: 6   Size: 32
   infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)

Item   4 -- Length:   77  Offset: 7320 (0x1c98)  Flags: USED
   XMIN: 1489323592  CMIN: 1  XMAX: 0  CMAX|XVAC: 0
   Block Id: 406  linp Index: 4   Attributes: 6   Size: 32
   infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)


...I then looked in the DB:

mydb=# select * from _my_cluster.sl_log_1 where ctid = '(406,1)';
log_origin | log_xid | log_tableid | log_actionseq | log_cmdtype |
log_cmddata
------------+---------+-------------+---------------+-------------
+-------------
(0 rows)

mydb=# select * from _my_cluster.sl_log_1 where ctid = '(406,2)';
log_origin | log_xid | log_tableid | log_actionseq | log_cmdtype |
log_cmddata
------------+---------+-------------+---------------+-------------
+-------------
(0 rows)

mydb=# select * from _my_cluster.sl_log_1 where ctid = '(406,3)';
log_origin | log_xid | log_tableid | log_actionseq | log_cmdtype |
log_cmddata
------------+---------+-------------+---------------+-------------
+-------------
(0 rows)


...is this what you were looking for, Tom?  The only thing that
stands out to me is
the XMAX_INVALID mask.  Thoughts?

Thanks,

/kurt





On Jun 20, 2007, at 11:22 AM, Tom Lane wrote:

> Kurt Overberg <kurt@hotdogrecords.com> writes:
>> Okay, so the sl_log_1 TABLE looks okay.  Its the indexes that seem to
>> be messed up, specifically sl_log_1_idx1 seems to think that there's
>>>> 300,000 rows in the table its associated with.  I just want to fix
>> the index, really.
>
> I'm not sure how you arrive at that conclusion.  The VACUUM VERBOSE
> output you provided here:
> http://archives.postgresql.org/pgsql-performance/2007-06/msg00370.php
> shows clearly that there are lots of rows in the table as well as
> the indexes.  A REINDEX would certainly cut the size of the indexes
> but it isn't going to do anything about the extraneous rows.
>
> When last heard from, you were working on getting pg_filedump
> output for
> some of the bogus rows --- what was the result?
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings