Thread: [PERFORM] postgresql 9.6 data directory fs becomes full
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.
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=# SELECTsize(pg_database.datname)) AS size mydb-# FROM pg_database;datname | size-----------+---------template0 | 7265 kBmydb | 246 GBpostgres | 568 MBtemplate1 | 7865 kB(4 rows)
When I check all the tables in mydb database :
mydb-# relname as "Table",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 GBtable 2| 15 GB | 6330 MBtable 3| 9506 MB | 3800 MBtable 4| 7473 MB | 1838 MBtable 5| 7267 MB | 2652 MBtable 6| 5347 MB | 1701 MBtable 7| 3402 MB | 1377 MBtable 8| 3092 MB | 1318 MBtable 9| 2145 MB | 724 MBtable 10| 1804 MB | 381 MBtable 11 293 MB | 83 MBtable 12| 268 MB | 103 MBtable 13| 225 MB | 108 MBtable 14| 217 MB | 40 MBtable 15| 172 MB | 47 MBtable 16| 134 MB | 36 MBtable 17| 102 MB | 27 MBtable 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.
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> 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.
Hm, are you committing anywhere in this loop? If not, the old data
remains on disk till you do end the transaction.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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