Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> 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@xxxxxxxxxxx p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
