base directory size getting increased

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

base directory size getting increased

Atul Kumar-2
Hi,

I have a query  from which I am trying to export the data into a csv
file(around 10 lakhs record) but when I am doing so the base sub
directory size of data directory is getting increased.

can someone help me in telling 'why base sub directory size is getting
increased'?


my postgres version is 9.6.

Regards,
Atul.


Reply | Threaded
Open this post in threaded view
|

Re: base directory size getting increased

Laurenz Albe
On Mon, 2021-06-07 at 16:11 +0530, Atul Kumar wrote:
> I have a query  from which I am trying to export the data into a csv
> file(around 10 lakhs record) but when I am doing so the base sub
> directory size of data directory is getting increased.
>
> can someone help me in telling 'why base sub directory size is getting
> increased'?
>
> my postgres version is 9.6.

Based on the little information you gave us, it could be one of

- temporary files that get created by the query
- you export the data into the data directory

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: base directory size getting increased

Atul Kumar-2
initially pgsql_tmp size was 87 GB and after execution of query it was
started increasinng beyond 87GBs so yes, pgsql_tmp directory size is
getting increased while exporting  data into csv file.

But once I rolled back the query, pgsql_tmp directory it was back to
87 GBs so please help me in telling how do I clean that 87GB of space
of pgsql_tmp directory.







Regards,
Atul








On 6/7/21, Laurenz Albe <[hidden email]> wrote:

> On Mon, 2021-06-07 at 16:11 +0530, Atul Kumar wrote:
>> I have a query  from which I am trying to export the data into a csv
>> file(around 10 lakhs record) but when I am doing so the base sub
>> directory size of data directory is getting increased.
>>
>> can someone help me in telling 'why base sub directory size is getting
>> increased'?
>>
>> my postgres version is 9.6.
>
> Based on the little information you gave us, it could be one of
>
> - temporary files that get created by the query
> - you export the data into the data directory
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>


Reply | Threaded
Open this post in threaded view
|

Re: base directory size getting increased

Ravi Krishna-20

>
> initially pgsql_tmp size was 87 GB and after execution of query it was
> started increasinng beyond 87GBs so yes, pgsql_tmp directory size is
> getting increased while exporting  data into csv file.
>
> But once I rolled back the query, pgsql_tmp directory it was back to
> 87 GBs so please help me in telling how do I clean that 87GB of space
> of pgsql_tmp directory.
>

Ideally applications which creates temp table should drop it when no longer needed.
When not dropped, they get dropped automatically when the session which created
them terminates.  Looks like in your case both are not happening.

Which is the oldest session still shown as connected?

Reply | Threaded
Open this post in threaded view
|

Re: base directory size getting increased

Laurenz Albe
In reply to this post by Atul Kumar-2
On Mon, 2021-06-07 at 17:48 +0530, Atul Kumar wrote:
> initially pgsql_tmp size was 87 GB and after execution of query it was
> started increasinng beyond 87GBs so yes, pgsql_tmp directory size is
> getting increased while exporting  data into csv file.
>
> But once I rolled back the query, pgsql_tmp directory it was back to
> 87 GBs so please help me in telling how do I clean that 87GB of space
> of pgsql_tmp directory.

The files in that directory will always be cleaned up when the
query that uses the temporary files is done.
The backend process ID of the query is part of the temporary file name.

It could be that such files are left behind after a crash.
If you are sure that the don't belong to a running query (for example,
if their modification timestamp is old), you can delete them.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: base directory size getting increased

Atul Kumar-2
In reply to this post by Atul Kumar-2
Hi Sir,

I don’t think there is any old connection connected as there is no process I found from pg_stat_activity.


Is there any other way to trace out such sessions.



Regards 
Atul





On Monday, June 7, 2021, Ravi Krishna <[hidden email]> wrote:
>
> initially pgsql_tmp size was 87 GB and after execution of query it was
> started increasinng beyond 87GBs so yes, pgsql_tmp directory size is
> getting increased while exporting  data into csv file.
>
> But once I rolled back the query, pgsql_tmp directory it was back to
> 87 GBs so please help me in telling how do I clean that 87GB of space
> of pgsql_tmp directory.
>

Ideally applications which creates temp table should drop it when no longer needed.
When not dropped, they get dropped automatically when the session which created
them terminates.  Looks like in your case both are not happening.

Which is the oldest session still shown as connected?
Reply | Threaded
Open this post in threaded view
|

Re: base directory size getting increased

Atul Kumar-2
In reply to this post by Laurenz Albe
Hi,

Is there any way to check which temporary file is being used by which query.


Any query/ view to check it, could you share or suggest ?

Is there any way to be safe while deleting such files.








Regards 
Atul







On Monday, June 7, 2021, Laurenz Albe <[hidden email]> wrote:
On Mon, 2021-06-07 at 17:48 +0530, Atul Kumar wrote:
> initially pgsql_tmp size was 87 GB and after execution of query it was
> started increasinng beyond 87GBs so yes, pgsql_tmp directory size is
> getting increased while exporting  data into csv file.
>
> But once I rolled back the query, pgsql_tmp directory it was back to
> 87 GBs so please help me in telling how do I clean that 87GB of space
> of pgsql_tmp directory.

The files in that directory will always be cleaned up when the
query that uses the temporary files is done.
The backend process ID of the query is part of the temporary file name.

It could be that such files are left behind after a crash.
If you are sure that the don't belong to a running query (for example,
if their modification timestamp is old), you can delete them.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Reply | Threaded
Open this post in threaded view
|

Re: base directory size getting increased

Laurenz Albe
On Mon, 2021-06-07 at 21:58 +0530, Atul Kumar wrote:

> On Monday, June 7, 2021, Laurenz Albe <[hidden email]> wrote:
> > On Mon, 2021-06-07 at 17:48 +0530, Atul Kumar wrote:
> > > But once I rolled back the query, pgsql_tmp directory it was back to
> > > 87 GBs so please help me in telling how do I clean that 87GB of space
> > > of pgsql_tmp directory.
> >
> > The files in that directory will always be cleaned up when the
> > query that uses the temporary files is done.
> > The backend process ID of the query is part of the temporary file name.
> >
> > It could be that such files are left behind after a crash.
> > If you are sure that the don't belong to a running query (for example,
> > if their modification timestamp is old), you can delete them.
>
> Is there any way to check which temporary file is being used by which query.

As I said, the file name contains the process ID.

> Any query/ view to check it, could you share or suggest ?

This would be difficult with SQL.
You could use "pg_ls_dir" to list directory contents, but remember that
process IDs get reused, so you would have to check if the file modification
time is older than the session start time in addition to checking for
the process ID.

> Is there any way to be safe while deleting such files.

You can shut down PostgreSQL and remove the "pgsql_tmp" directory.
It will be created again when needed.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com