3

Solution (short version):

Coding error in the SQL used by the feeder apps caused "no transaction in progress" warnings (not errors!) that were logged every time an insertion into the tables happened. The log folder ballooned to 400 GB.

Solution (long version):

  • As the SQL used is complex and depends on what type of table is created and which options a user selected, it was created as an array in a step-wise fashion.
  • Each part was tested and copied over into a text editor where it was turned into code for creating the SQL array with a script
  • the resulting code was copied to the IDE for the feeder console apps that process the data and insert the result into the database
  • during one of the copy/paste steps the first line was lost: BEGIN;
  • this resulted in a warning being logged: "no transaction in progress" EVERY TIME an insert happened (about 100 Million to 300 Million times for each data source), and consequently the log folder ballooned to 400 GB
  • the code in the feeder apps deals with database errors but not with warnings
  • pgadmin4 did not show any warning either, nor did it warn about the ballooning log folder
  • as the log folder is locked and inaccessible it was not clear where the hard disk space disappeared to, resulting in this question

Solution posted in the hope my mistake will help others experiencing similar problems. I will also inform the pgadmin team that it might be helpful to keep an eye on size of the log folder.

Short version of problem:

  • Large tables are created, called tblX, tbl1, tbl2, tbl3 etc.
  • tblX should have only unique entries that are not in the other tables.
  • SELECT INTO is used to create a new table X_without_1 when compared with tbl1, and the process is repeated for each of the tables resulting in X_without_2, X_without_3, etc.
  • only the new tables are kept, the already processed ones (eg tbl1 and X_without_1) are deleted
  • after 20 rounds of this the tables occupy only 65 GB, but 500 GB of disk space is being used.
  • why, and how can I reclaim the space?

Long version of problem:

I’m interested in 2 chromosomes X and Y, and need to exclude all 20mers that exist in all the other chromosomes of the genome

Procedure:

tables with identical structure, 4 columns each:

  • ID
  • position as integer
  • sequence as char(20)
  • gc_content as real

Tables were created by:

  • create table for chromosome x: chr_x
  • create table for chromosome y: chr_y

FROM HERE IT LOOPS:

  • create table for chromosome 1: chr_1

  • fill tables via helper apps that analyse a large chromosome (here x, y, 1) sequence (40-180 MB) and extract all possible 20mers from that chromosome

  • create new table chr_x_without_1 and use

Select 
* 
INTO TABLE chr_x_without_1
FROM 
chr_x t
WHERE Not EXISTS (
Select -- Select list mostly irrelevant; can just be empty In Postgres
FROM chr_1
WHERE sequence = t.sequence
);

  • do the same for chr_y which creates table chr_y_without_1

  • drop table chr_x

  • drop table chr_y

  • drop table chr_1

so now you have tables:

  • chr_x_without_1
  • chr_y_without_1

REPEAT THE LOOP and do the same with the next chr which is #2

Problem:

table sizes of the tables I currently have are:

  • chr_18_20mer 14.07 GB with 221,363,456 rows

  • chr_19_20mer 14.41 GB with 226,635,578 rows

  • chr_x_20mer_without_1_to_17 11.77 GB with 185,152,978 rows

  • chr_y_20mer_without_1_to_17 3.01 GB with 47,334,082 rows

// these two I keep for a side project

  • chr_x_20mer_y_specific 14.54 GB with 228,744,190 rows
  • chr_y_20mer_x_specific 3.44 GB with 54,055,054 rows

so 61.24 GB in total.

auto-vacuum was performed on each table last night

full vacuum was performed as well

SELECT pg_database_size(‘db_name’)

gives 65786606383 which I presume is 65,786,606,383 or 66 GB

BUT postgres is using over 500 GB of disk space !!

The last time I extracted duplicates in chr_17 from chr_y_without_1_to_16 into chr_y_without_1_to_17 the size of the table went from 3.02 GB to 3.01 GB as expected - but 60 GB of disk space vanished.

I left postgres alone over night as I thought maybe it needs to catch up but my disk space is still down to 17 GB (on a 1 TB SSD, and yes, Trim is enabled).

So I thought maybe postgres releases space as required and tried to process the next chromosome. But I got:

ERROR: postgres BgWorkerShutdown

because it had run out of space.

This seems insane. Where is the space used? And more importantly, how do I reclaim the space???

P.S. None of the tables have an index

Addendum 1:

I have now exported the tables in csv format to a different disk, and then deleted the tables with DROP CASCADE followed by a VACUUM FULL, but only 65 GB of space was freed on the volume (the size of the deleted tables).

I next deleted the database itself - no change.

While 'postgres' is now a user on the system and I can see under "other users" that their usage has ballooned by over 400 GB, I can't see that postgres user in the Finder nor the terminal.

Worse, postgres has also buggered up my backups as my two backups have also swollen by over 400 GB - but going into them doesn't show postgres either.

Next try will be to remove postgres completely - this has been a pretty unpleasant experience so far …

Addendum 2:

I found that the normally inaccessible logs folder in the locked postgres data folder contained over 389 GB of 10.5 MB log files that simple had endless rows of

WARNING: no transaction in progress

Googling for that warning has not yet provided any useful leads, but 389 GB of pointless log files seems very wrong …

On the plus side I should be able to remove that from my backups …

btw as I don't have a score of over 50 yet I can't add a comment, I can only add info to the question …

user297020
  • 31
  • 2

0 Answers0