CODEWITHSUNDEEP
postgresql
oberstet
oberstet

Reputation: 22051

Tracking amount of bytes written to temp tables

With PostgreSQL 9.5, I would like to track the total amount of bytes written (since DB cluster start) to:

  1. WAL
  2. temp files
  3. temp tables

For 1.:

select
   pg_size_pretty(archived_count * 16*1024*1024) temp_bytes,
   (now() - stats_reset)::text uptime
from pg_stat_archiver;

For 2.:

select
   (now() - stats_reset)::text uptime,
   pg_size_pretty(temp_bytes) temp_bytes
from pg_stat_database where datname = 'mydb';

How do I get 3.?

In response to a comment below, I did some tests to check where temp tables are actually written.

First, the DB parameter temp_buffers is at 8GB on this cluster:

select pg_size_pretty(setting::bigint*8192) from pg_settings
     where name = 'temp_buffers';
-- "8192 MB"

Lets create a temp table:

drop table if exists foo;

create temp table foo as
select random() from generate_series(1, 1000000000);
-- Query returned successfully: 1000000000 rows affected, 10:22 minutes execution time.

Check the PostgreSQL backend pid and OID of the created temp table:

select pg_backend_pid(), 'pg_temp.foo'::regclass::oid;
-- 46573;398695055

Check the RSS size of the backend process

~$ grep VmRSS /proc/46573/status
VmRSS:   9246276 kB

As can be seen, this is only slightly above the 8GB set with temp_buffers.

The data inserted into the temp table is however immediately written, and it is written to the normal tablespace directories, not temp files:

select * from pg_relation_filepath('pg_temp.foo')
-- "base/16416/t3_398695055"

Here is the number of files and amount written:

with temp_table_files as
(
    select * from pg_ls_dir('base/16416/') fn
        where fn like 't3_398695055%'
)
select
    count(*) as cnt,
    pg_size_pretty(sum((pg_stat_file('base/16416/' || fn)).size)) as size
from temp_table_files;
-- 34;"34 GB"

And finally verify that the set of temp files owned by this backend PID is indeed empty:

with temp_files_per_pid as
(
    with temp_files as
    (
    select
        temp_file,
        (regexp_replace(temp_file, $r$^pgsql_tmp(\d+)\..*$$r$, $rr$\1$rr$, 'g'))::int as pid,
        (pg_stat_file('base/pgsql_tmp/' || temp_file)).size as size
    from pg_ls_dir('base/pgsql_tmp') temp_file
    )
    select pid, pg_size_pretty(sum(size)) from temp_files group by pid order by pid
)
select * from temp_files_per_pid where pid = 46573;

Returns nothing.

What is also "interesting", after dropping the temp table

DROP TABLE foo;

the RSS of the backend process does not reduce:

~$ grep VmRSS /proc/46573/status
VmRSS:   9254544 kB

Doing the following will also not free the RSS again:

RESET ALL;
DEALLOCATE ALL;
DISCARD TEMP;

Upvotes: 0

Views: 1968

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45900

What I know, there are not any special metric for temp tables. The temp tables uses session (process) memory to temp_buffers size (8MB by default). When these temp buffers are full, then temporary files are generated.

Upvotes: 1

Related Questions