public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mariel Cherkassky <[email protected]>
To: [email protected]
Subject: postgresql 9.6 data directory fs becomes full
Date: Mon, 11 Sep 2017 12:42:01 +0300
Message-ID: <CA+t6e1=s49eVoYVhYAS56pBo=C1eFDa6HHbpt8-tnu00HZ_gnw@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: postgresql 9.6 data directory fs becomes full
In-Reply-To: <CA+t6e1=s49eVoYVhYAS56pBo=C1eFDa6HHbpt8-tnu00HZ_gnw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox