pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex File growing big.
9+ messages / 7 participants
[nested] [flat]
* Index File growing big.
@ 2003-02-26 06:26 PRAGATI SAVAIKAR <pragati@phildigital.com>
0 siblings, 2 replies; 9+ messages in thread
From: PRAGATI SAVAIKAR @ 2003-02-26 06:26 UTC (permalink / raw)
To: pgsql-performance
Hi !!
We have "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96"
installed on Linux (RedHat 7.2)
Our database size is 15 GB.
Since the database size was increasing and was about to cross the actual
Hard Disk parttion Size, we moved the datafiles (also the index files) to
another partition and created link to them from the data directory.
This was working fine.
But what we found was , the index files(2 files) were not getting updated
in the new partition, instead postgres had created another index file with
name
"tableID".1 in the original data directory. The size of this file was
356MB,
The actual size of the data table is 1GB. and there were 2 indexes for the
table. which were of size approximately=150MB.
But after we created link, those 2 index files were not getting updated,
instead the new file with ".1" extension got created in the data directory
(old parttion) and the same is getting updated everyday.
We dropped the table but the file with ".1" extension was not getting
removed from data directory. We manually had to remove it.
Can U please suggest some way to avoid the file getting created when we
move the data file (along with the index files) to another partition.
Thanks in Advance.
Regards,
Pragati.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index File growing big.
@ 2003-02-26 07:29 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: PRAGATI SAVAIKAR <pragati@phildigital.com>
1 sibling, 0 replies; 9+ messages in thread
From: Rajesh Kumar Mallah @ 2003-02-26 07:29 UTC (permalink / raw)
To: PRAGATI SAVAIKAR <pragati@phildigital.com>; pgsql-performance
I remember this is your second posting for the same problem.
I hope you are aware that postgres can manage multiple databases that
lie in different partitions.
If its feasible for you you may think of moving the *big* tables in another
database which can be initialised in another partition. it depends on ur app
design as you will have to create a new db connection.
another possibility is to buy a new bigger hdd ofcourse ;-)
and migrate the data.
also i think the <tableid>.1 or <tableid>.2 are extensions of the
datafile not index files. index files have seperate id of their own.
pg_class have that id in relfilenode.
in case i am getting the problem wrong , my query is have you relocated the
index files also and created the symlinks ? (using ln -s)
Also 7.2.1 is too old an version to use in 7.2.x series 7.2.4 is latest
and in 7.3.x 7.3.2 is latest.
regds
mallah.
On Wednesday 26 February 2003 11:56 am, PRAGATI SAVAIKAR wrote:
> Hi !!
>
> We have "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96"
> installed on Linux (RedHat 7.2)
> Our database size is 15 GB.
> Since the database size was increasing and was about to cross the actual
> Hard Disk parttion Size, we moved the datafiles (also the index files) to
> another partition and created link to them from the data directory.
> This was working fine.
> But what we found was , the index files(2 files) were not getting updated
> in the new partition, instead postgres had created another index file with
> name
> "tableID".1 in the original data directory. The size of this file was
> 356MB,
> The actual size of the data table is 1GB. and there were 2 indexes for the
> table. which were of size approximately=150MB.
>
> But after we created link, those 2 index files were not getting updated,
> instead the new file with ".1" extension got created in the data directory
> (old parttion) and the same is getting updated everyday.
>
> We dropped the table but the file with ".1" extension was not getting
> removed from data directory. We manually had to remove it.
>
> Can U please suggest some way to avoid the file getting created when we
> move the data file (along with the index files) to another partition.
>
>
> Thanks in Advance.
>
>
>
> Regards,
> Pragati.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Regds
Mallah
----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index File growing big.
@ 2003-02-26 12:00 Andrew Sullivan <andrew@libertyrms.info>
parent: PRAGATI SAVAIKAR <pragati@phildigital.com>
1 sibling, 1 reply; 9+ messages in thread
From: Andrew Sullivan @ 2003-02-26 12:00 UTC (permalink / raw)
To: pgsql-performance
On Wed, Feb 26, 2003 at 11:56:48AM +0530, PRAGATI SAVAIKAR wrote:
>
> Can U please suggest some way to avoid the file getting created when we
> move the data file (along with the index files) to another partition.
Yes. Submit a patch which implements tablespaces ;-)
Seriously, there is no way to avoid this in the case where you are
moving the files by hand. The suggestions for how to move files
around note this.
If this is merely a disk-size problem, why not move the entire
postgres installation to another disk, and make a link to it. If you
still need to spread things across disks, you can move things which
don't change in size very much. A good candidate here is the WAL
(pg_xlog), since it grows to a predictable size. You even get a
performance benefit.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index File growing big.
@ 2003-02-27 01:19 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 1 reply; 9+ messages in thread
From: Christopher Kings-Lynne @ 2003-02-27 01:19 UTC (permalink / raw)
To: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance
> > Can U please suggest some way to avoid the file getting created when we
> > move the data file (along with the index files) to another partition.
>
> Yes. Submit a patch which implements tablespaces ;-)
You should note that someone already has sent in a patch for tablespaces, it
hasn't been acted on though - can't quite remember why. Maybe we should
resurrect it...
Chris
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index File growing big.
@ 2003-02-27 06:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2003-02-27 06:56 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Yes. Submit a patch which implements tablespaces ;-)
> You should note that someone already has sent in a patch for tablespaces, it
> hasn't been acted on though - can't quite remember why. Maybe we should
> resurrect it...
It's been awhile, but my recollection is that the patch had restricted
functionality (which would be okay for a first cut) and it invented SQL
syntax that seemed to lock us into that restricted functionality
permanently (not so okay). Details are fuzzy though...
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index File growing big.
@ 2003-02-27 09:03 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Christopher Kings-Lynne @ 2003-02-27 09:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance
> > You should note that someone already has sent in a patch for
tablespaces, it
> > hasn't been acted on though - can't quite remember why. Maybe we should
> > resurrect it...
>
> It's been awhile, but my recollection is that the patch had restricted
> functionality (which would be okay for a first cut) and it invented SQL
> syntax that seemed to lock us into that restricted functionality
> permanently (not so okay). Details are fuzzy though...
Well, I'll resurrect it and see if it can be improved. Tablespaces seem to
be a requested feature these days...
Chris
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index File growing big.
@ 2003-02-27 12:34 Jim Buttafuoco <jim@contactbda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 9+ messages in thread
From: Jim Buttafuoco @ 2003-02-27 12:34 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Tom Lane <tgl@sss.pgh.pa.us>; Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance
All,
I was the person who submitted the patch. I tried to a generic syntax. I also tried to keep the tablespace concept
simple. See my posting on HACKERS/GENERAL from a week or 2 ago about the syntax. I am still interested in working on
this patch with others. I have many system here that are 500+ gigabytes and growing. It is a real pain to add more
disk space (I have to backup, drop database(s), rebuild raid set (I am using raid 10) and reload data).
Jim
> > > You should note that someone already has sent in a patch for
> tablespaces, it
> > > hasn't been acted on though - can't quite remember why. Maybe we should
> > > resurrect it...
> >
> > It's been awhile, but my recollection is that the patch had restricted
> > functionality (which would be okay for a first cut) and it invented SQL
> > syntax that seemed to lock us into that restricted functionality
> > permanently (not so okay). Details are fuzzy though...
>
> Well, I'll resurrect it and see if it can be improved. Tablespaces seem to
> be a requested feature these days...
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] Index File growing big.
@ 2003-02-28 01:57 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Jim Buttafuoco <jim@contactbda.com>
1 sibling, 0 replies; 9+ messages in thread
From: Christopher Kings-Lynne @ 2003-02-28 01:57 UTC (permalink / raw)
To: jim@contactbda.com; Hackers <pgsql-hackers@postgresql.org>
Hi Jim,
Do you have a version of the patch that's synced against CVS HEAD?
Chris
> All,
>
> I was the person who submitted the patch. I tried to a generic syntax. I
also tried to keep the tablespace concept
> simple. See my posting on HACKERS/GENERAL from a week or 2 ago about the
syntax. I am still interested in working on
> this patch with others. I have many system here that are 500+ gigabytes
and growing. It is a real pain to add more
> disk space (I have to backup, drop database(s), rebuild raid set (I am
using raid 10) and reload data).
>
> Jim
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Index File growing big.
@ 2003-03-06 20:18 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Jim Buttafuoco <jim@contactbda.com>
1 sibling, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2003-03-06 20:18 UTC (permalink / raw)
To: jim@contactbda.com; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Tom Lane <tgl@sss.pgh.pa.us>; Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance
Yes, the issue was that it only had places for heap and index location,
not more generic.
I can work with a few folks to get this done. I think it can be done in
a few stages:
Decide on syntax/functionality
Update grammer to support it
Update system catalogs to hold information
Update storage manager to handle storage locations
If folks can decide on the first item, I can do the second and third
ones.
---------------------------------------------------------------------------
Jim Buttafuoco wrote:
> All,
>
> I was the person who submitted the patch. I tried to a generic syntax. I also tried to keep the tablespace concept
> simple. See my posting on HACKERS/GENERAL from a week or 2 ago about the syntax. I am still interested in working on
> this patch with others. I have many system here that are 500+ gigabytes and growing. It is a real pain to add more
> disk space (I have to backup, drop database(s), rebuild raid set (I am using raid 10) and reload data).
>
> Jim
>
>
>
> > > > You should note that someone already has sent in a patch for
> > tablespaces, it
> > > > hasn't been acted on though - can't quite remember why. Maybe we should
> > > > resurrect it...
> > >
> > > It's been awhile, but my recollection is that the patch had restricted
> > > functionality (which would be okay for a first cut) and it invented SQL
> > > syntax that seemed to lock us into that restricted functionality
> > > permanently (not so okay). Details are fuzzy though...
> >
> > Well, I'll resurrect it and see if it can be improved. Tablespaces seem to
> > be a requested feature these days...
> >
> > Chris
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-03-06 20:18 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-26 06:26 Index File growing big. PRAGATI SAVAIKAR <pragati@phildigital.com>
2003-02-26 07:29 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-02-26 12:00 ` Andrew Sullivan <andrew@libertyrms.info>
2003-02-27 01:19 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 09:03 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 12:34 ` Jim Buttafuoco <jim@contactbda.com>
2003-02-28 01:57 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-03-06 20:18 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox