Обсуждение: PostgreSQL 9.6 Temporary files

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

--
Adrian Klaver
adrian.klaver@aklaver.com

On 19 March 2018 17:31:20 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>Dear Friends,
>
>I am newbie to postgresql.
>I have 162 GB on my database but when I check size of all tables, I
>approximately obtain 80 GB.


Indexes?

>I also see that I have 68GB of temporary files however I only found

Where can you see that?

>2.4MB
>at postgres/data/base/pgsql_tmp.
>
>Could you tell me what are those temporary files and where are they at?
>Can
>I delete some of them?


No, never delete files in datadir!

>
>All values come from pgAdmin 4 and checked by my own SQL
>queries(postgresql-9.6).
>I already run vacuum full and there is few dead tuples.

A few dead tuples arn't a real problem.


>
>Best regards,
>Jimmy AUGUSTINE


--
2ndQuadrant - The PostgreSQL Support Company

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/19/2018 10:04 AM, Jimmy Augustine wrote:

2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

    On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

        Dear Friends,

        I am newbie to postgresql.
        I have 162 GB on my database but when I check size of all
        tables, I approximately obtain 80 GB.
        I also see that I have 68GB of temporary files however I only
        found 2.4MB at postgres/data/base/pgsql_tmp.


    Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));

So where did the 68GB number for temporary files come from?

--
Adrian Klaver
adrian.klaver@aklaver.com

2018-03-19 18:09 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 03/19/2018 10:04 AM, Jimmy Augustine wrote:

2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

    On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

        Dear Friends,

        I am newbie to postgresql.
        I have 162 GB on my database but when I check size of all
        tables, I approximately obtain 80 GB.
        I also see that I have 68GB of temporary files however I only
        found 2.4MB at postgres/data/base/pgsql_tmp.


    Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));

So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

I used this command and I found the same value in total_size column.

2018-03-19 18:01 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:

On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

--
Adrian Klaver
adrian.klaver@aklaver.com

> I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
>I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

I am not sure what your query was that deteremined table and index sizes, but try using the query instead.

Note that total_size is the size of the table and all it's indexes.

SELECT n.nspname as schema,
       c.relname as table,
       a.rolname as owner,
       c.relfilenode as filename,
       c.reltuples::bigint,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as size,
       pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as total_size,
       pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as size_bytes,
       pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as total_size_bytes,
       CASE WHEN c.reltablespace = 0
            THEN 'pg_default'
            ELSE (SELECT t.spcname
                    FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )                   
        END as tablespace
FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE quote_ident(nspname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'information%'
    AND quote_ident(relname) NOT LIKE 'sql_%'
    AND quote_ident(relkind) IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;


--

Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

On 03/19/2018 10:12 AM, Jimmy Augustine wrote:

             On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

                 Dear Friends,

                 I am newbie to postgresql.
                 I have 162 GB on my database but when I check size of all
                 tables, I approximately obtain 80 GB.
                 I also see that I have 68GB of temporary files however
        I only
                 found 2.4MB at postgres/data/base/pgsql_tmp.


             Exactly how did you determine this?

        I used this command and sum result for all database :
        SELECT pg_size_pretty(pg_total_relation_size('table_name'));

        And this for complete database :
        SELECT pg_size_pretty(pg_database_size('Database Name'));


    So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

In what section of pgAdmin4?

Or do you know what query it used?

SELECT temp_files AS "Temporary files"    , temp_bytes AS "Size of temporary files"
FROM   pg_stat_database db;

    --     Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 19 March 2018 18:21:42 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>2018-03-19 18:15 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
>
>> On 03/19/2018 10:12 AM, Jimmy Augustine wrote:
>>
>>
>>>              On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
>>>
>>>                  Dear Friends,
>>>
>>>                  I am newbie to postgresql.
>>>                  I have 162 GB on my database but when I check size
>of all
>>>                  tables, I approximately obtain 80 GB.
>>>                  I also see that I have 68GB of temporary files
>however
>>>         I only
>>>                  found 2.4MB at postgres/data/base/pgsql_tmp.
>>>
>>>
>>>              Exactly how did you determine this?
>>>
>>>         I used this command and sum result for all database :
>>>         SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>>
>>>         And this for complete database :
>>>         SELECT pg_size_pretty(pg_database_size('Database Name'));
>>>
>>>
>>>     So where did the 68GB number for temporary files come from?
>>>
>>> I don't measure this value by my own. I was disappointed by the gap
>>> between the two queries, so I checked pgAdmin 4 and I saw this
>value.
>>>
>>
>> In what section of pgAdmin4?
>>
>In section "Statistics" when I click on my database.
>
>Or do you know what query it used?
>>
>I have found this but not sure
>
>SELECT temp_files AS "Temporary files"
>   , temp_bytes AS "Size of temporary files"FROM   pg_stat_database db;

That's aggregated. Not current values.

Andreas

--
2ndQuadrant - The PostgreSQL Support Company

On 03/20/2018 03:16 AM, Jimmy Augustine wrote:

Thanks all for your response,

$du -h $MY_DATA/base/$BASE_OID/ returns 162GB but when I execute this query:

|SELECT stats.relname AS table, pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, pg_size_pretty(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid)) AS related_objects_size, pg_size_pretty(pg_total_relation_size(statsio.relid)) AS total_table_size, stats.n_live_tup AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schemaUNION ALL SELECT 'TOTAL' AS table, pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid))) AS related_objects_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS total_table_size, sum(stats.n_live_tup) AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schemaORDER BY live_rows ASC;

|

|I obtain 80GB in total_table_size (half of my database), where are missing data at?

First of all you are using pg_statio_user_tables which does not count system tables.

Second pretty sure the use of current_schema is limiting the results to only one schema in the database.

|


2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

    On 03/19/2018 10:27 AM, Jimmy Augustine wrote:

        I tried this query and my database size is equal to 162GB.


    Well you can always look in $DATA directly. The database will be
    under $DATA/base/<db oid>.

    You can find the <db oid> like this:

    select oid, datname from  pg_database where datname='<db name>';


    --     Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com