Re: [PERFORM] postgresql 9.6 data directory fs becomes full

Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:

> I want to check something regarding postgresql performance during my
> app is running.
>
> My app does the next things on 20 tables in a loop :
>
> 1.truncate table.
> 2.drop constraints on table
> 3.drop indexes on table
> 4.insert into local_table select * from remote_oracle_table
> 4.1.Recently I'm getting an error in this part : SQLERRM = could not extend 
>    file "base/16400/124810.23": wrote only 4096 of 8192 bytes at block 
>    3092001
> 5.create constraints on table
> 6.create indexes on table.
>
> This operation runs every night. Most of the tables are small 500M-2G
> but few tables are pretty big 24G-45G.
>
> My wals and my data directory are on different fs. My data directory
> fs size is 400G. During this operation the data directory fs becomes
> full. However, after this operation 100G are freed which means that
> 300G are used from the 400g of the data directory fs. Something
> regarding those sizes doesnt seems ok.
>
> When I check my database size :
>
> mydb=# SELECT
> mydb-#     pg_database.datname,
> mydb-#     pg_size_pretty(pg_database_size(pg_database.datname)) AS size
> mydb-#     FROM pg_database;
>   datname  |  size   
>   -----------+---------
>   template0 | 7265 kB
>   mydb      | 246 GB
>   postgres  | 568 MB
>   template1 | 7865 kB
>   (4 rows)
>
> When I check all the tables in mydb database :
>
> mydb-#    relname as "Table",
> mydb-#    pg_size_pretty(pg_total_relation_size(relid)) As "Size",
> mydb-#    pg_size_pretty(pg_total_relation_size(relid) -     
>           pg_relation_size(relid)) as "External Size"
> mydb-#    FROM pg_catalog.pg_statio_user_tables ORDER BY 
>           pg_total_relation_size(relid) DESC;
>          Table             |    Size    | External Size 
>         -------------------+------------+---------------
>                     table 1| 45 GB      | 13 GB
>                     table 2| 15 GB      | 6330 MB
>                     table 3| 9506 MB    | 3800 MB
>                     table 4| 7473 MB    | 1838 MB
>                     table 5| 7267 MB    | 2652 MB
>                     table 6| 5347 MB    | 1701 MB
>                     table 7| 3402 MB    | 1377 MB
>                     table 8| 3092 MB    | 1318 MB
>                     table 9| 2145 MB    | 724 MB
>                     table 10| 1804 MB    | 381 MB
>                     table 11 293 MB     | 83 MB
>                     table 12| 268 MB     | 103 MB
>                     table 13| 225 MB     | 108 MB
>                     table 14| 217 MB     | 40 MB
>                     table 15| 172 MB     | 47 MB
>                     table 16| 134 MB     | 36 MB
>                     table 17| 102 MB     | 27 MB
>                     table 18| 86 MB      | 22 MB
>                    .....
>
> In the data directory the base directory`s size is 240G. I have 16G
> of ram in my machine.
>
> Waiting for help, thanks.

You didn't say but if I can assume you're doing this work in a
transaction...

You understand that space is *not* freed by the truncate until commit, right?



>
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance