Обсуждение: dbsize patch

Поиск
Список
Период
Сортировка

dbsize patch

От
"Ed L."
Дата:
The attached dbsize patch:

    + makes relation_size(relname) include toast tables;
    + adds aggregate_relation_size(relname) to count table data and indices;
    + adds indices_size(relname) to report the size of indices for a relation;

I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu,
compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5).

Ed

Вложения

Re: dbsize patch

От
Neil Conway
Дата:
On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote:
> The attached dbsize patch:
>
>     + makes relation_size(relname) include toast tables;
>     + adds aggregate_relation_size(relname) to count table data and indices;
>     + adds indices_size(relname) to report the size of indices for a relation;
>
> I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu,
> compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5).

Barring any objections, I'll apply this to HEAD tomorrow.

-Neil



Re: dbsize patch

От
"Michael Paesold"
Дата:
Neil Conway wrote:

> On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote:
>> The attached dbsize patch:
>>
>> + makes relation_size(relname) include toast tables;
>> + adds aggregate_relation_size(relname) to count table data and indices;
>> + adds indices_size(relname) to report the size of indices for a
>> relation;
>>
>> I've minimally tested it against PostgreSQL 8.1devel on
>> i686-pc-linux-gnu,
>> compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5).
>
> Barring any objections, I'll apply this to HEAD tomorrow.

Perhaps you could rename indices_size to indexes_size. A quick google search
on "site:postgresql.org indices" and "site:postgresql.org indexes" shows
that indices is used much less (7,080) than indexes (23,400). Top hits for
indices are 7.1 docs, for indexes it's 7.3 and 7.4.
It seems to me that indexes is the term more commonly used with postgresql.

Best Regards,
Michael


Re: dbsize patch

От
Neil Conway
Дата:
On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
> Perhaps you could rename indices_size to indexes_size.

Yeah, sorry -- forgot to mention that. I believe we decided to
standardize on "indexes" as the plural of "index" (at least in
user-visible stuff) a few releases go.

Good catch :)

-Neil



Re: dbsize patch

От
"Ed L."
Дата:
On Thursday January 27 2005 12:08, Neil Conway wrote:
> On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
> > Perhaps you could rename indices_size to indexes_size.
>
> Yeah, sorry -- forgot to mention that. I believe we decided to
> standardize on "indexes" as the plural of "index" (at least in
> user-visible stuff) a few releases go.

Attached patch identical except for s/indices/indexes/g.

Ed

Вложения

Re: dbsize patch

От
Andreas Pflug
Дата:
Neil Conway wrote:
> On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote:
>
>>The attached dbsize patch:
>>
>>    + makes relation_size(relname) include toast tables;
>>    + adds aggregate_relation_size(relname) to count table data and indices;
>>    + adds indices_size(relname) to report the size of indices for a relation;

Hm, these are all implementable as SQL functions, do we need these hard
coded too?

e.g.
create function aggregate_relation_size(oid) returns int8 as $CODE$
select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1;
$CODE$ language 'SQL'

Regards,
Andreas

Re: dbsize patch

От
Tom Lane
Дата:
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Hm, these are all implementable as SQL functions, do we need these hard
> coded too?

> e.g.
> create function aggregate_relation_size(oid) returns int8 as $CODE$
> select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1;
> $CODE$ language 'SQL'

Your suggestion would be more compelling if the example were correct ;-).
Consider more than one index on the same table.

This does raise the question of whether the C implementations count the
right things either --- I have not looked.  Neil, I trust you're going
to review this and not just apply it?

            regards, tom lane

Re: dbsize patch

От
"Ed L."
Дата:
> > On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
> > > Perhaps you could rename indices_size to indexes_size.
>
> Attached patch identical except for s/indices/indexes/g.

Attached is the same patch as context diff.  (prior send from unregistered
email address)

Ed




Вложения

Re: dbsize patch

От
"Ed L."
Дата:
On Thursday January 27 2005 6:59, Andreas Pflug wrote:
> Neil Conway wrote:
> > On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote:
> >>The attached dbsize patch:
> >>
> >>    + makes relation_size(relname) include toast tables;
> >>    + adds aggregate_relation_size(relname) to count table data and
> >> indices; + adds indices_size(relname) to report the size of indices
> >> for a relation;
>
> Hm, these are all implementable as SQL functions, do we need these hard
> coded too?
>
> e.g.
> create function aggregate_relation_size(oid) returns int8 as $CODE$
> select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1;
> $CODE$ language 'SQL'

Well, it seems quite a bit more complicated than that to me, but I'm going
to rework the patch so it drops into 7.3 as well and resubmit shortly.

Ed


Re: dbsize patch

От
"Ed L."
Дата:
On Thursday January 27 2005 2:12, Ed L. wrote:
>
> Well, it seems quite a bit more complicated than that to me, but I'm
> going to rework the patch so it drops into 7.3 as well and resubmit
> shortly.

Too much trouble for now.  Neil, if the latest patch is acceptable or useful
for others as-is, great, please apply.

Ed




Re: dbsize patch

От
Andreas Pflug
Дата:
Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>Hm, these are all implementable as SQL functions, do we need these hard
>>coded too?
>
>
>>e.g.
>>create function aggregate_relation_size(oid) returns int8 as $CODE$
>>select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1;
>>$CODE$ language 'SQL'
>
>
> Your suggestion would be more compelling if the example were correct ;-).
> Consider more than one index on the same table.

Hopefully SUM() will do the job.

Regards,
Andreas

Re: dbsize patch

От
Ed Loehr
Дата:
> > On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
> > > Perhaps you could rename indices_size to indexes_size.
>
> Attached patch identical except for s/indices/indexes/g.

Attached is the same patch as context diff.

Ed


Вложения

Re: dbsize patch

От
Ed Loehr
Дата:
If the C code for the prior dbsize patch is not acceptable for whatever
reason, here's a SQL-based patch to replace it.  It's not a drop-in for
7.3/7.4 as I'd hoped, only an 8.1 patch.  I believe it is functionally
equivalent to the C patch, but simpler, shorter, and probably a tad slower.
I also removed the README section on how to aggregate since it was
incomplete/incorrect (it didn't count toasted indices) and added a SQL
function that itemizes the size for a relation's table and index data
(helpful to us in identifying bloat, measuring performance, capacity
estimation, etc).

Ed

Вложения

Re: dbsize patch

От
"Ed L."
Дата:
Neil, do you have a verdict on this patch?

On Friday January 28 2005 10:30, Ed L. wrote:
> If the C code for the prior dbsize patch is not acceptable for
> whatever reason, here's a SQL-based patch to replace it.  It's
> not a drop-in for 7.3/7.4 as I'd hoped, only an 8.1 patch.  I
> believe it is functionally equivalent to the C patch, but
> simpler, shorter, and probably a tad slower. I also removed
> the README section on how to aggregate since it was
> incomplete/incorrect (it didn't count toasted indices) and
> added a SQL function that itemizes the size for a relation's
> table and index data (helpful to us in identifying bloat,
> measuring performance, capacity estimation, etc).
>
> Ed


Re: dbsize patch

От
"Ed L."
Дата:
On Thursday February 3 2005 9:23, Ed L. wrote:
> Neil, do you have a verdict on this patch?
>
> On Friday January 28 2005 10:30, Ed L. wrote:
> > If the C code for the prior dbsize patch is not acceptable
> > for whatever reason, here's a SQL-based patch to replace it.

I submitted a dbsize patch on Jan 25, revised it twice per
concerns raised by Michael Paesold and Neil Conway ("indexes"
instead of "indices") and Andreas Pflug and Tom Lane (implement
in SQL instead of C) and resubmitted Jan 28.  I've not received
any further communication regarding the patch.  Please advise if
there are concerns.  I've attached the patch again, slightly
cleaned up, in case it has fallen through the cracks.

Ed

Вложения

Re: dbsize patch

От
Bruce Momjian
Дата:
It is still in my mailbox for review. Sorry.

---------------------------------------------------------------------------

Ed L. wrote:
> On Thursday February 3 2005 9:23, Ed L. wrote:
> > Neil, do you have a verdict on this patch?
> >
> > On Friday January 28 2005 10:30, Ed L. wrote:
> > > If the C code for the prior dbsize patch is not acceptable
> > > for whatever reason, here's a SQL-based patch to replace it.
>
> I submitted a dbsize patch on Jan 25, revised it twice per
> concerns raised by Michael Paesold and Neil Conway ("indexes"
> instead of "indices") and Andreas Pflug and Tom Lane (implement
> in SQL instead of C) and resubmitted Jan 28.  I've not received
> any further communication regarding the patch.  Please advise if
> there are concerns.  I've attached the patch again, slightly
> cleaned up, in case it has fallen through the cracks.
>
> Ed

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: dbsize patch

От
Neil Conway
Дата:
On Wed, 2005-02-09 at 18:35 -0500, Bruce Momjian wrote:
> It is still in my mailbox for review. Sorry.

Yeah, my apologies as well, I've been busy with other things. Bruce, if
you'd like to review & apply this you are welcome to. Otherwise let me
know and I'll take a look.

-Neil



Re: dbsize patch

От
Bruce Momjian
Дата:
Neil Conway wrote:
> On Wed, 2005-02-09 at 18:35 -0500, Bruce Momjian wrote:
> > It is still in my mailbox for review. Sorry.
>
> Yeah, my apologies as well, I've been busy with other things. Bruce, if
> you'd like to review & apply this you are welcome to. Otherwise let me
> know and I'll take a look.

Who ever gets to it first can deal with it.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: dbsize patch

От
Bruce Momjian
Дата:
Patch applied.  Thanks.

I renamed aggregate_relation_size() to total_relation_size().  To me
'aggregate' was too closely associated with 'aggregates'.  If you have
improved wording please let me know.

---------------------------------------------------------------------------


Ed L. wrote:
> On Thursday February 3 2005 9:23, Ed L. wrote:
> > Neil, do you have a verdict on this patch?
> >
> > On Friday January 28 2005 10:30, Ed L. wrote:
> > > If the C code for the prior dbsize patch is not acceptable
> > > for whatever reason, here's a SQL-based patch to replace it.
>
> I submitted a dbsize patch on Jan 25, revised it twice per
> concerns raised by Michael Paesold and Neil Conway ("indexes"
> instead of "indices") and Andreas Pflug and Tom Lane (implement
> in SQL instead of C) and resubmitted Jan 28.  I've not received
> any further communication regarding the patch.  Please advise if
> there are concerns.  I've attached the patch again, slightly
> cleaned up, in case it has fallen through the cracks.
>
> Ed

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073