Re: : Performance Improvement Strategy

Hello,

I was attempting to calculate the actual occupied space by a Table.

Below is what i did -

I summed up the avg_width of each column of a table from pg_stats, which
gives me the average size of a row (277 bytes).

select* sum(avg_width) as average_row_size from pg_stats *where
tablename='tablename'

average_row_size
---------------------------
277

(1 row)

Calculated the actual occupied space by rows in the table as below -

*Took the average_row_size * number_of_rows from pg_class*

select 277*reltuples/1024 as occupied_space from pg_class where
relname='tablename'; == 552 KB

occupied_space
-------------------------
552.6474609375

Calculated the actual Table size (600 kb)

select pg_size_pretty(pg_relation_size('tablename'));

pg_size_pretty
----------------
600 KB

(1 row)

Calculated the free space with in the table (by scanning the pages - as
suggested by Shaun Thomas) -- 14 KB

SELECT pg_size_pretty(free_space) AS mb_free FROM pgstattuple('tablename');

mb_free
---------
14 KB

(1 row)

600 KB is the size of the table (taken through pg_size_pretty)
14 KB is the free space (taken through contrib modules)
600+14 = 586 KB -- is the occupied space by normal calculation through
contrib modules. This is based on number of pages allocated to the table.
552 KB is the actual occupied size by the rows (taken by calculating avg row
size ). This is based on number of rows with in the pages.
586-552 = 34 KB -- is still free some where with in the occupied pages (
calculated through pg_stats and pg_class )
34 KB is still free within the pages ( each 8K ) which is basically taken as
occupied space.

This is similar concept which i successfully applied in an other RDBMS
Technology to calculate space usage metrics on production.
This is all calculated after considering Vacuum and Analyze jobs are
executed.

Please comment !

Sorry if this is too confusing and too long.

Thanks
VB

On Wed, Sep 21, 2011 at 6:33 PM, Shaun Thomas <sthomas(at)peak6(dot)com> wrote:

> On 09/20/2011 11:22 AM, Venkat Balaji wrote:
>
> Please help me understand how to calculate free space in Tables and
>> Indexes even after vacuuming and analyzing is performed.
>>
>
> Besides the query Mark gave you using freespacemap, there's also the
> pgstattuple contrib module. You'd use it like this:
>
> SELECT pg_size_pretty(free_space) AS mb_free
> FROM pgstattuple('some_table');
>
> Query must be run as a super-user, and I wouldn't recommend running it on
> huge tables, since it scans the actual data files to get its information.
> There's a lot of other useful information in that function, such as the
> number of dead rows.
>
>
> What i understand is that, even if we perform VACUUM ANALYZE
>> regularly, the free space generated is not filled up.
>>
>
> VACUUM does not actually generate free space. It locates and marks reusable
> tuples. Any future updates or inserts on that table will be put in those
> newly reclaimed spots, instead of being bolted onto the end of the table.
>
>
> I see lot of free spaces or free pages in Tables and Indexes. But, I
>> need to give an exact calculation on how much space will be reclaimed
>> after VACUUM FULL and RE-INDEXING.
>>
>
> Why? If your database is so desperate for space, VACUUM and REINDEX won't
> really help you. A properly maintained database will still have a certain
> amount of "bloat" equal to the number of rows that change between
> maintenance intervals. One way or another, that space is going to be used by
> *something*.
>
> It sounds more like you need to tweak your autovacuum settings to be more
> aggressive if you're seeing significant enough turnover that your tables are
> bloating significantly. One of our tables, for instance, gets vacuumed more
> than once per hour because it experiences 1,000% turnover daily.
>
> --
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
> 312-676-8870
> sthomas(at)peak6(dot)com
>
> ______________________________**________________
>
> See http://www.peak6.com/email-**disclaimer/<http://www.peak6.com/email-disclaimer/>for terms and conditions related to this email
>