Обсуждение: PostgreSQL 9.2.4 temp files never released?
I've a 12Gb database running without problems in Linux Centos 64bit for years now. Looking database statistics (in pgAdmin III), I can see that there are 366 temporary files, and they sum up 11,863,839,867 bytes in size. Is that normal? When will this space be released? I've restarted the server, but seems those files (in number and in size) are just growing and growing... Regards, Edson Richter
On 09/28/2013 11:16 AM, Edson Richter wrote: > I've a 12Gb database running without problems in Linux Centos 64bit for > years now. > Looking database statistics (in pgAdmin III), I can see that there are > 366 temporary files, and they sum up 11,863,839,867 bytes in size. What are the temp files named and where are they located? > > Is that normal? When will this space be released? > > I've restarted the server, but seems those files (in number and in size) > are just growing and growing... > > Regards, > > Edson Richter > > > -- Adrian Klaver adrian.klaver@gmail.com
Em 28/09/2013 15:16, Edson Richter escreveu: > I've a 12Gb database running without problems in Linux Centos 64bit > for years now. > Looking database statistics (in pgAdmin III), I can see that there are > 366 temporary files, and they sum up 11,863,839,867 bytes in size. > > Is that normal? When will this space be released? > > I've restarted the server, but seems those files (in number and in > size) are just growing and growing... > > Regards, > > Edson Richter > > > Forgot to mention: it is currently running 9.2.4. Configuration files has currently the following configuration regarding work_mem and temporary files: shared_buffers = 1500MB temp_buffers = 32MB max_prepared_transactions = 0 work_mem = 64MB maintenance_work_mem = 16MB max_stack_depth = 2MB temp_file_limit = 4GB max_files_per_process = 2000 ulimit -a shows: core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 62703 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1048576 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 62703 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
Em 28/09/2013 15:22, Adrian Klaver escreveu: > On 09/28/2013 11:16 AM, Edson Richter wrote: >> I've a 12Gb database running without problems in Linux Centos 64bit for >> years now. >> Looking database statistics (in pgAdmin III), I can see that there are >> 366 temporary files, and they sum up 11,863,839,867 bytes in size. > > What are the temp files named and where are they located? Sorry if this sounds silly, but how can I discover this information? Edson Richter > >> >> Is that normal? When will this space be released? >> >> I've restarted the server, but seems those files (in number and in size) >> are just growing and growing... >> >> Regards, >> >> Edson Richter >> >> >> > >
On 09/28/2013 11:30 AM, Edson Richter wrote: > Em 28/09/2013 15:22, Adrian Klaver escreveu: >> On 09/28/2013 11:16 AM, Edson Richter wrote: >>> I've a 12Gb database running without problems in Linux Centos 64bit for >>> years now. >>> Looking database statistics (in pgAdmin III), I can see that there are >>> 366 temporary files, and they sum up 11,863,839,867 bytes in size. >> >> What are the temp files named and where are they located? > > Sorry if this sounds silly, but how can I discover this information? Assuming pgAdmin is using pg_stat_database then: http://www.postgresql.org/docs/9.2/static/storage-file-layout.html "Temporary files (for operations such as sorting more data than can fit in memory) are created within PGDATA/base/pgsql_tmp, or within a pgsql_tmp subdirectory of a tablespace directory if a tablespace other than pg_default is specified for them. The name of a temporary file has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning backend and NNN distinguishes different temporary files of that backend." > > Edson Richter -- Adrian Klaver adrian.klaver@gmail.com
Em 28/09/2013 15:54, Adrian Klaver escreveu: > On 09/28/2013 11:30 AM, Edson Richter wrote: >> Em 28/09/2013 15:22, Adrian Klaver escreveu: >>> On 09/28/2013 11:16 AM, Edson Richter wrote: >>>> I've a 12Gb database running without problems in Linux Centos 64bit >>>> for >>>> years now. >>>> Looking database statistics (in pgAdmin III), I can see that there are >>>> 366 temporary files, and they sum up 11,863,839,867 bytes in size. >>> >>> What are the temp files named and where are they located? >> >> Sorry if this sounds silly, but how can I discover this information? > > Assuming pgAdmin is using pg_stat_database then: > > http://www.postgresql.org/docs/9.2/static/storage-file-layout.html > > "Temporary files (for operations such as sorting more data than can > fit in memory) are created within PGDATA/base/pgsql_tmp, or within a > pgsql_tmp subdirectory of a tablespace directory if a tablespace other > than pg_default is specified for them. The name of a temporary file > has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning > backend and NNN distinguishes different temporary files of that backend." Ok. Found the place. So, there is nothing there. PG_DATA/base/pgsql_tmp is a empty directory. I've run the query over pg_stat_database view and there is nothing wrong with pgAdmin III - the information is all there. I've also run a vacuum freeze analyze, but made no difference. I believe that statistics are outdated, since there is no temp file at all. Any tip on that? Thanks, Edson
On 28 Září 2013, 22:54, Edson Richter wrote: > Em 28/09/2013 15:54, Adrian Klaver escreveu: >> On 09/28/2013 11:30 AM, Edson Richter wrote: >>> Em 28/09/2013 15:22, Adrian Klaver escreveu: >>>> On 09/28/2013 11:16 AM, Edson Richter wrote: >>>>> I've a 12Gb database running without problems in Linux Centos 64bit >>>>> for >>>>> years now. >>>>> Looking database statistics (in pgAdmin III), I can see that there >>>>> are >>>>> 366 temporary files, and they sum up 11,863,839,867 bytes in size. >>>> >>>> What are the temp files named and where are they located? >>> >>> Sorry if this sounds silly, but how can I discover this information? >> >> Assuming pgAdmin is using pg_stat_database then: >> >> http://www.postgresql.org/docs/9.2/static/storage-file-layout.html >> >> "Temporary files (for operations such as sorting more data than can >> fit in memory) are created within PGDATA/base/pgsql_tmp, or within a >> pgsql_tmp subdirectory of a tablespace directory if a tablespace other >> than pg_default is specified for them. The name of a temporary file >> has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning >> backend and NNN distinguishes different temporary files of that >> backend." > > Ok. Found the place. > So, there is nothing there. PG_DATA/base/pgsql_tmp is a empty directory. > I've run the query over pg_stat_database view and there is nothing wrong > with pgAdmin III - the information is all there. > I've also run a vacuum freeze analyze, but made no difference. > > I believe that statistics are outdated, since there is no temp file at > all. Do you realize the counters in pg_stat_database (temp_files, temp_bytes) are counters tracking all the temp files created since the last reset of the stats (possible since the cluster was created)? So these values only increment (and it's meant to behave like that). BTW this is true for most values in the stats catalogs, so there's nothing like "current stats" - to get something like that you need to get two snapshots and subtract them (to get the delta). Tomas
On Sat, Sep 28, 2013 at 1:54 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
Em 28/09/2013 15:54, Adrian Klaver escreveu:Ok. Found the place.
So, there is nothing there. PG_DATA/base/pgsql_tmp is a empty directory.
I've run the query over pg_stat_database view and there is nothing wrong with pgAdmin III - the information is all there.
I've also run a vacuum freeze analyze, but made no difference.
I believe that statistics are outdated, since there is no temp file at all.
The statistics are not current, they are cumulative since the database was created, or pg_stat_reset() was last called.
Cheers,
Jeff
Em 28/09/2013 18:12, Tomas Vondra escreveu: > On 28 Září 2013, 22:54, Edson Richter wrote: >> Em 28/09/2013 15:54, Adrian Klaver escreveu: >>> On 09/28/2013 11:30 AM, Edson Richter wrote: >>>> Em 28/09/2013 15:22, Adrian Klaver escreveu: >>>>> On 09/28/2013 11:16 AM, Edson Richter wrote: >>>>>> I've a 12Gb database running without problems in Linux Centos 64bit >>>>>> for >>>>>> years now. >>>>>> Looking database statistics (in pgAdmin III), I can see that there >>>>>> are >>>>>> 366 temporary files, and they sum up 11,863,839,867 bytes in size. >>>>> What are the temp files named and where are they located? >>>> Sorry if this sounds silly, but how can I discover this information? >>> Assuming pgAdmin is using pg_stat_database then: >>> >>> http://www.postgresql.org/docs/9.2/static/storage-file-layout.html >>> >>> "Temporary files (for operations such as sorting more data than can >>> fit in memory) are created within PGDATA/base/pgsql_tmp, or within a >>> pgsql_tmp subdirectory of a tablespace directory if a tablespace other >>> than pg_default is specified for them. The name of a temporary file >>> has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning >>> backend and NNN distinguishes different temporary files of that >>> backend." >> Ok. Found the place. >> So, there is nothing there. PG_DATA/base/pgsql_tmp is a empty directory. >> I've run the query over pg_stat_database view and there is nothing wrong >> with pgAdmin III - the information is all there. >> I've also run a vacuum freeze analyze, but made no difference. >> >> I believe that statistics are outdated, since there is no temp file at >> all. > Do you realize the counters in pg_stat_database (temp_files, temp_bytes) > are counters tracking all the temp files created since the last reset of > the stats (possible since the cluster was created)? > > So these values only increment (and it's meant to behave like that). BTW > this is true for most values in the stats catalogs, so there's nothing > like "current stats" - to get something like that you need to get two > snapshots and subtract them (to get the delta). > > Tomas Ah, ok! One more lesson learned... I would expect stats being current of, or up-to-date after a vacuum analyze... Thank you all very much. Edson
Em 28/09/2013 18:23, Jeff Janes escreveu:
Thanks, Jeff!On Sat, Sep 28, 2013 at 1:54 PM, Edson Richter <edsonrichter@hotmail.com> wrote:Em 28/09/2013 15:54, Adrian Klaver escreveu:Ok. Found the place.
So, there is nothing there. PG_DATA/base/pgsql_tmp is a empty directory.
I've run the query over pg_stat_database view and there is nothing wrong with pgAdmin III - the information is all there.
I've also run a vacuum freeze analyze, but made no difference.
I believe that statistics are outdated, since there is no temp file at all.The statistics are not current, they are cumulative since the database was created, or pg_stat_reset() was last called.Cheers,Jeff
I now understand.
Regards,
Edson
On 28 Září 2013, 23:25, Edson Richter wrote: > Em 28/09/2013 18:12, Tomas Vondra escreveu: >> Do you realize the counters in pg_stat_database (temp_files, temp_bytes) >> are counters tracking all the temp files created since the last reset of >> the stats (possible since the cluster was created)? >> >> So these values only increment (and it's meant to behave like that). BTW >> this is true for most values in the stats catalogs, so there's nothing >> like "current stats" - to get something like that you need to get two >> snapshots and subtract them (to get the delta). >> >> Tomas > > Ah, ok! One more lesson learned... > I would expect stats being current of, or up-to-date after a vacuum > analyze... > Thank you all very much. Vacuum has nothing to do with this. The temp files are removed immediately (by the particular operation that created them) once not needed anymore. Unless something goes terribly wrong, of course ... Tomas