pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index 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