pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex size growing
8+ messages / 4 participants
[nested] [flat]
* Index size growing
@ 2011-04-01 02:01 Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 11:28 ` Re: Index size growing Jerry Sievers <gsievers19@comcast.net>
0 siblings, 1 reply; 8+ messages in thread
From: Rajendra prasad @ 2011-04-01 02:01 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have 50 GB data base folder for postgres. Out of 50 GB 20 GB has been
occupied by 8 indexes of a single table by growing upto 2 GB each. I googled
and got the info that reindex will help brining back the actual size of the
index. But the disk has only 4 GB left . Due to this, when i run reindex it
is getting almost full. I don't know exactly why it is growing like that
during reindex. Due to this i am not able to complete the reindex and see if
i get back the disk spacce. Please suggest me a good solution and the way
that i am doing is logical or not.
Regards,
Rajendra
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Index size growing
2011-04-01 02:01 Index size growing Rajendra prasad <rajendra.dn@gmail.com>
@ 2011-04-01 11:28 ` Jerry Sievers <gsievers19@comcast.net>
2011-04-01 12:08 ` Re: Index size growing Rajendra prasad <rajendra.dn@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jerry Sievers @ 2011-04-01 11:28 UTC (permalink / raw)
To: Rajendra prasad <rajendra.dn@gmail.com>; +Cc: pgsql-admin
Rajendra prasad <rajendra.dn@gmail.com> writes:
> Hi,
>
> I have 50 GB data base folder for postgres. Out of 50 GB 20 GB has
> been occupied by 8 indexes of a single table by growing upto 2 GB
> each. I googled and got the info that reindex will help brining back
> the actual size of the index. But the disk has only 4 GB left . Due to
> this, when i run reindex it is getting almost full. I don't know
> exactly why it is growing like that during reindex. Due to this i am
> not able to complete the reindex and see if i get back the disk
> spacce. Please suggest me a good solution and the way that i am doing
> is logical or not.
Did you try reindexing each index individually?
The old index files will be dropped in that case upon completion of each
command if you are running autocommit. Less transient disk space demand
by that strategy.
Even more disk friendly is just to drop/create the indexes.
Since you can't reindex without blocking anyway, I assume you are clear
of concurrency issues to prevent using this advice (apps down and/or
quiescent).
--
Jerry Sievers
Postgres DBA/Development Consulting
e: gsievers19@comcast.net
p: 305.321.1144
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Index size growing
2011-04-01 02:01 Index size growing Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 11:28 ` Re: Index size growing Jerry Sievers <gsievers19@comcast.net>
@ 2011-04-01 12:08 ` Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 15:04 ` Re: Index size growing Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Rajendra prasad @ 2011-04-01 12:08 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: pgsql-admin
Hi,
Thank you for the info. I am doing the reindex for an individual index. And
i am doing this in the live server and apps are connecting to it at the same
time. Is this not correct?
I am new to postgres. Kindly brief me the steps which i need to follow.
Prasad
On Fri, Apr 1, 2011 at 1:28 PM, Jerry Sievers <gsievers19@comcast.net>wrote:
> Rajendra prasad <rajendra.dn@gmail.com> writes:
>
> > Hi,
> >
> > I have 50 GB data base folder for postgres. Out of 50 GB 20 GB has
> > been occupied by 8 indexes of a single table by growing upto 2 GB
> > each. I googled and got the info that reindex will help brining back
> > the actual size of the index. But the disk has only 4 GB left . Due to
> > this, when i run reindex it is getting almost full. I don't know
> > exactly why it is growing like that during reindex. Due to this i am
> > not able to complete the reindex and see if i get back the disk
> > spacce. Please suggest me a good solution and the way that i am doing
> > is logical or not.
>
> Did you try reindexing each index individually?
>
> The old index files will be dropped in that case upon completion of each
> command if you are running autocommit. Less transient disk space demand
> by that strategy.
>
> Even more disk friendly is just to drop/create the indexes.
>
> Since you can't reindex without blocking anyway, I assume you are clear
> of concurrency issues to prevent using this advice (apps down and/or
> quiescent).
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: gsievers19@comcast.net
> p: 305.321.1144
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Index size growing
2011-04-01 02:01 Index size growing Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 11:28 ` Re: Index size growing Jerry Sievers <gsievers19@comcast.net>
2011-04-01 12:08 ` Re: Index size growing Rajendra prasad <rajendra.dn@gmail.com>
@ 2011-04-01 15:04 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 8+ messages in thread
From: Kevin Grittner @ 2011-04-01 15:04 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; Rajendra prasad <rajendra.dn@gmail.com>; +Cc: pgsql-admin
Rajendra prasad <rajendra.dn@gmail.com> wrote:
> Jerry Sievers <gsievers19@comcast.net>wrote:
>> Rajendra prasad <rajendra.dn@gmail.com> writes:
>>> I have 50 GB data base folder for postgres. Out of 50 GB 20 GB
>>> has been occupied by 8 indexes of a single table by growing upto
>>> 2 GB each.
There's a good chance you don't need all of those indexes or that
you could use narrower indexes. Without seeing the table schema,
including indexes, and hearing more about your use case it's hard to
be sure. But PostgreSQL is often able to make good use of a number
of one-column indexes in ways some other databases can't, while wide
indexes designed for the "covering index" optimization can't be used
that way in PostgreSQL. We don't have that optimization at this
point.
>>> I googled and got the info that reindex will help brining back
>>> the actual size of the index.
Well, until you add or modify data, at which point page splits will
likely pop it right back to the size you're seeing now.
> Thank you for the info. I am doing the reindex for an individual
> index. And i am doing this in the live server and apps are
> connecting to it at the same time. Is this not correct?
Attempts to modify the table during the reindex will block, and
queries won't be able to use an index while it is being rebuilt.
You can work around that with CREATE INDEX CONCURRENTLY followed by
an DROP INDEX on the old index, but that takes some spare disk
space.
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [ADMIN] Index size growing
@ 2011-04-01 10:06 scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>
2011-04-01 12:09 ` Re: Index size growing Rajendra prasad <rajendra.dn@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: scorpdaddy@hotmail.com @ 2011-04-01 10:06 UTC (permalink / raw)
To: Rajendra prasad <rajendra.dn@gmail.com>; pgsql-admin
How recent is the vacuum in this DB?
----- Reply message -----
From: "Rajendra prasad" <rajendra.dn@gmail.com>
Date: Thu, Mar 31, 2011 11:01 pm
Subject: [ADMIN] Index size growing
To: <pgsql-admin@postgresql.org>
Hi,
I have 50 GB data base folder for postgres. Out of 50 GB 20 GB has been
occupied by 8 indexes of a single table by growing upto 2 GB each. I googled
and got the info that reindex will help brining back the actual size of the
index. But the disk has only 4 GB left . Due to this, when i run reindex it
is getting almost full. I don't know exactly why it is growing like that
during reindex. Due to this i am not able to complete the reindex and see if
i get back the disk spacce. Please suggest me a good solution and the way
that i am doing is logical or not.
Regards,
Rajendra
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Index size growing
2011-04-01 10:06 Re: [ADMIN] Index size growing scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>
@ 2011-04-01 12:09 ` Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 14:53 ` Re: Index size growing Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Rajendra prasad @ 2011-04-01 12:09 UTC (permalink / raw)
To: scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>; +Cc: pgsql-admin
Hi,
I am not doing vacuum atall.
Prasad
On Fri, Apr 1, 2011 at 12:06 PM, scorpdaddy@hotmail.com <
scorpdaddy@hotmail.com> wrote:
> How recent is the vacuum in this DB?
>
>
>
> ----- Reply message -----
> From: "Rajendra prasad" <rajendra.dn@gmail.com>
> Date: Thu, Mar 31, 2011 11:01 pm
> Subject: [ADMIN] Index size growing
> To: <pgsql-admin@postgresql.org>
>
>
> Hi,
>
> I have 50 GB data base folder for postgres. Out of 50 GB 20 GB has been
> occupied by 8 indexes of a single table by growing upto 2 GB each. I
> googled
> and got the info that reindex will help brining back the actual size of the
> index. But the disk has only 4 GB left . Due to this, when i run reindex it
> is getting almost full. I don't know exactly why it is growing like that
> during reindex. Due to this i am not able to complete the reindex and see
> if
> i get back the disk spacce. Please suggest me a good solution and the way
> that i am doing is logical or not.
>
> Regards,
> Rajendra
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Index size growing
2011-04-01 10:06 Re: [ADMIN] Index size growing scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>
2011-04-01 12:09 ` Re: Index size growing Rajendra prasad <rajendra.dn@gmail.com>
@ 2011-04-01 14:53 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-02 07:44 ` Re: Index size growing Rajendra prasad <rajendra.dn@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kevin Grittner @ 2011-04-01 14:53 UTC (permalink / raw)
To: Rajendra prasad <rajendra.dn@gmail.com>; scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>; +Cc: pgsql-admin
Rajendra prasad <rajendra.dn@gmail.com> wrote:
> I am not doing vacuum atall.
Please run the query on this page and post the results:
http://wiki.postgresql.org/wiki/Server_Configuration
My hope is that you are on a recent version of PostgreSQL with
autovacuum taking care of things by default.
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Index size growing
2011-04-01 10:06 Re: [ADMIN] Index size growing scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>
2011-04-01 12:09 ` Re: Index size growing Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 14:53 ` Re: Index size growing Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-04-02 07:44 ` Rajendra prasad <rajendra.dn@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rajendra prasad @ 2011-04-02 07:44 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>; pgsql-admin
Hi,
Pls find the below output of the query given in the below link.
name |
current_setting
------------------------------+-----------------------------------------------------------------------------------------------------
version | PostgreSQL 8.3.5 on
x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (SUSE
Linux)
archive_command | /var/lib/pgsql/bin/cmd_archiver -A -C
/var/lib/pgsql/etc/cmd_archiver.ini -F %p
archive_mode | on
checkpoint_completion_target | 0.7
checkpoint_segments | 10
checkpoint_timeout | 15min
default_statistics_target | 100
effective_cache_size | 1328MB
lc_collate | en_GB.UTF-8
lc_ctype | en_GB.UTF-8
listen_addresses | *
log_destination | stderr
log_filename | postgresql-%Y-%m-%d.log
log_line_prefix | %t %d %u
log_min_duration_statement | 5s
log_rotation_size | 0
logging_collector | on
maintenance_work_mem | 256MB
max_connections | 250
max_fsm_pages | 2048000
max_stack_depth | 2MB
server_encoding | UTF8
shared_buffers | 768MB
silent_mode | on
TimeZone | Europe/Brussels
work_mem | 2MB
Prasad
On Fri, Apr 1, 2011 at 8:23 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov>wrote:
> Rajendra prasad <rajendra.dn@gmail.com> wrote:
>
> > I am not doing vacuum atall.
>
> Please run the query on this page and post the results:
>
> http://wiki.postgresql.org/wiki/Server_Configuration
>
> My hope is that you are on a recent version of PostgreSQL with
> autovacuum taking care of things by default.
>
> -Kevin
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-04-02 07:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-01 02:01 Index size growing Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 11:28 ` Jerry Sievers <gsievers19@comcast.net>
2011-04-01 12:08 ` Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 15:04 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-01 10:06 Re: [ADMIN] Index size growing scorpdaddy@hotmail.com <scorpdaddy@hotmail.com>
2011-04-01 12:09 ` Rajendra prasad <rajendra.dn@gmail.com>
2011-04-01 14:53 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-02 07:44 ` Rajendra prasad <rajendra.dn@gmail.com>
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