Thread: Query re disk usage
I'm running PG 8.3beta3 on a W2K3 server.
I've set up a tablespace on D drive, with PG itself on C drive and
loaded a bunch of data into a database to test. The directory I've
created the tablespace in on D drive grows to 116Mb - which would be
about right for the amount of data I've plugged in.
(pg_size_pretty(pg_database(size()) certainly corroborates that value
anyway)
I note however, that the pg_database directory on C drive also grows at
the same time to 116MB.
If I truncate the table I've added the data to, both directories shrink
down to around 7 or so Kb.
Why is PG apparently storing my data twice? Is this some sort of
redundancy thing I haven't heard about or have I got something
configured incorrectly? Or am I misinterpreting the way table-spaces are
handled?
The tablespace was set up thusly:
CREATE TABLESPACE "ts_autodrs_main"
OWNER "AutoDRS"
LOCATION 'D:/Database/AutoDRS/Data';
And the table in question defined as:
create table job_classification (
dealer_id text not null ,
date_changed timestamp null ,
time_changed time null ,
job_id text not null ,
des text ,
user_id text null,
access_reports_processed text
) WITHOUT OIDS TABLESPACE ts_autodrs_main
;
Cheers,
P.
--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company
Paul Lambert wrote: > I note however, that the pg_database directory on C drive also grows at > the same time to 116MB. > That was meant to say the pg_tblspc directory. Both directories (my tablespace and pg_tablespace) contain the same set of files - same names and sizes, eg both contain a file 17177 with a size of around 58Mb and both contain another file 17179 with a size of around 53Mb - I'm assuming one being the data, one being the primary key index. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds & Reynolds Company
On Thu, 2007-11-22 at 15:26 +0900, Paul Lambert wrote: > I'm running PG 8.3beta3 on a W2K3 server. > > I've set up a tablespace on D drive, with PG itself on C drive and > loaded a bunch of data into a database to test. The directory I've > created the tablespace in on D drive grows to 116Mb - which would be > about right for the amount of data I've plugged in. > (pg_size_pretty(pg_database(size()) certainly corroborates that value > anyway) > > I note however, that the pg_database directory on C drive also grows at > the same time to 116MB. > > If I truncate the table I've added the data to, both directories shrink > down to around 7 or so Kb. > > Why is PG apparently storing my data twice? Is this some sort of > redundancy thing I haven't heard about or have I got something > configured incorrectly? Or am I misinterpreting the way table-spaces are > handled? Sounds like the WAL log. It's in the pg_xlog directory - verify that that's where the data is increasing. The WAL log is global and not per-tablespace, so it doesn't follow your tablespaces location. //Magnus
Magnus Hagander wrote: > Sounds like the WAL log. It's in the pg_xlog directory - verify that > that's where the data is increasing. > > The WAL log is global and not per-tablespace, so it doesn't follow your > tablespaces location. > Nope, it's the files in the pg_tblspc directory on my C drive, they are named identically to the files in my tablespace directory on D drive and have identical sizes. When I add data to a table in this tablespace, I see concurrent increases in file size in both my own tablespace directory and the pg_tblspc directory tree. Eg within this directory tree I have a file 17177 which represents my job_classification table, if I insert a bunch of records, the 17177 file on my D drive increases in size as does the 17177 file in the pg_tblspc subdirectory. I can see the pg_xlog files, which aren't an issue as they are archived off to E drive. I've just noticed in the tablespace documentation (Ch 19.6) that PG makes use of symbolic links that point to any user-defined tablespaces but AFAIK W2K3 doesn't support symlinks. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds & Reynolds Company
Paul Lambert wrote: > I've just noticed in the tablespace documentation (Ch 19.6) that PG > makes use of symbolic links that point to any user-defined tablespaces > but AFAIK W2K3 doesn't support symlinks. > OK, W2K3 supports a thing it calls Junctions, which are similar to symlinks - and PG appears to be using that in this case. Crisis averted. Cheers, P. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds & Reynolds Company
On Thu, 2007-11-22 at 17:04 +0900, Paul Lambert wrote: > Paul Lambert wrote: > > I've just noticed in the tablespace documentation (Ch 19.6) that PG > > makes use of symbolic links that point to any user-defined tablespaces > > but AFAIK W2K3 doesn't support symlinks. > > > > OK, W2K3 supports a thing it calls Junctions, which are similar to > symlinks - and PG appears to be using that in this case. > > Crisis averted. I was just going to suggest that. It's a pretty neat feature, but the support in the GUI for knowing when you're entering one is nonexistant. If you do "dir" on the commandline it'll tell you the truth. //Magnus
Magnus Hagander wrote: > On Thu, 2007-11-22 at 17:04 +0900, Paul Lambert wrote: >> OK, W2K3 supports a thing it calls Junctions, which are similar to >> symlinks - and PG appears to be using that in this case. >> >> Crisis averted. > > I was just going to suggest that. It's a pretty neat feature, but the > support in the GUI for knowing when you're entering one is nonexistant. > If you do "dir" on the commandline it'll tell you the truth. > Yep, I noticed that... took me a while to find it though. Cheers for the assistance Magnus. P. -- Paul Lambert Database Administrator AutoLedgers