Thread: postgres table have a large number of relpages and occupied a big memory size
postgres table have a large number of relpages and occupied a big memory size
From
Vivekkumar Pandey
Date:
Hi all,
I have a cluster database with a master and slave ,
the size of master database is very high reltive to slave
while both have approximatly same data.
On master
# select pg_size_pretty(pg_database_size('table_name'));
pg_size_pretty
----------------
15 GB
(1 row)
relname | reltuples | MB
--------------------------------------+-------------+------
Table1 | 876 | 4395
Table2 | 1.69135e+06 | 1919
Table3 | 1.69137e+06 | 1298
Table4 | 289 | 1062
Table5 | 2007 | 1031
Table6 | 1.69136e+06 | 1019
Table7 | 1809 | 673
Table8 | 1.69137e+06 | 476
On Slave
# select pg_size_pretty(pg_database_size('table_name'));
pg_size_pretty
----------------
1411 MB
(1 row)
all of the table having same number of tuples on slave while having
very low space relative to master .
Please give the reason and appropriate solution of this problem.................
--
Thanks
VIVEK KUMAR PANDEY
Re: postgres table have a large number of relpages and occupied a big memory size
From
"Tomas Vondra"
Date:
On 5 Srpen 2011, 9:00, Vivekkumar Pandey wrote: > Hi all, > > I have a cluster database with a master and slave , > the size of master database is very high reltive to slave > while both have approximatly same data. What version of PostgreSQL is this, what kind of cluster, and what do you mean by "approximately"? With xlog-based replication (file shipping or streaming replication), the relations should have exactly the same size, but they should also contain exactly the same data. That suggests you're using something else to build the cluster (e.g. slony or something like that). In that case the size difference may be simply due to data differences or dead tuples. VACUUM FULL should compact the dead tuples, but it's not a cheap command (takes exclusive locks, time and memory). Tomas
Re: postgres table have a large number of relpages and occupied a big memory size
From
Vivekkumar Pandey
Date:
Hi Tomas,
I am using the slony cluster and both the database have the same Data.
So Please provide the appropriate solution....
On Fri, Aug 5, 2011 at 12:47 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 5 Srpen 2011, 9:00, Vivekkumar Pandey wrote:
>> Hi all,
>>
>> I have a cluster database with a master and slave ,
>> the size of master database is very high reltive to slave
>> while both have approximatly same data.
>
> What version of PostgreSQL is this, what kind of cluster, and what do you
> mean by "approximately"?
>
> With xlog-based replication (file shipping or streaming replication), the
> relations should have exactly the same size, but they should also contain
> exactly the same data.
>
> That suggests you're using something else to build the cluster (e.g. slony
> or something like that). In that case the size difference may be simply
> due to data differences or dead tuples. VACUUM FULL should compact the
> dead tuples, but it's not a cheap command (takes exclusive locks, time and
> memory).
>
> Tomas
>
>
--
Thanks
VIVEK KUMAR PANDEY
Re: postgres table have a large number of relpages and occupied a big memory size
From
Jaime Casanova
Date:
On Fri, Aug 5, 2011 at 2:37 AM, Vivekkumar Pandey <vivekkumar.pandey@globallogic.com> wrote: > > Hi Tomas, > > I am using the slony cluster and both the database have the same Data. > > So Please provide the appropriate solution.... > > On Fri, Aug 5, 2011 at 12:47 PM, Tomas Vondra <tv@fuzzy.cz> wrote: > > > > That suggests you're using something else to build the cluster (e.g. slony > > or something like that). In that case the size difference may be simply > > due to data differences or dead tuples. VACUUM FULL should compact the > > dead tuples, but it's not a cheap command (takes exclusive locks, time and > > memory). > > It seems like Tomas gives you the solution (at least part of it): use VACUUM FULL to compact your data on the master. Also, probably you want to revisiti your autovacuum's configuration. Finally, remember that Slony has two tables that logs all changes in the database... normally only one of the table should be in use while Slony is processing the queu of the other and truncate it. but if the slon process are not running those tables start to grow... can you check that the slon processes are running -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
Re: postgres table have a large number of relpages and occupied a big memory size
From
Vivekkumar Pandey
Date:
Hi, slon process is running on the system . Now , I have a question that Why dead tupples are remains in the table while AUTOVACUUM process running at the fixed interval of time without any error. Also suggest the Query that can view the dead tuples in the table. Thanks for instant reply....... On Fri, Aug 5, 2011 at 1:22 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > On Fri, Aug 5, 2011 at 2:37 AM, Vivekkumar Pandey > <vivekkumar.pandey@globallogic.com> wrote: >> >> Hi Tomas, >> >> I am using the slony cluster and both the database have the same Data. >> >> So Please provide the appropriate solution.... >> >> On Fri, Aug 5, 2011 at 12:47 PM, Tomas Vondra <tv@fuzzy.cz> wrote: >> > >> > That suggests you're using something else to build the cluster (e.g. slony >> > or something like that). In that case the size difference may be simply >> > due to data differences or dead tuples. VACUUM FULL should compact the >> > dead tuples, but it's not a cheap command (takes exclusive locks, time and >> > memory). >> > > > It seems like Tomas gives you the solution (at least part of it): use > VACUUM FULL to compact your data on the master. > Also, probably you want to revisiti your autovacuum's configuration. > > Finally, remember that Slony has two tables that logs all changes in > the database... normally only one of the table should be in use while > Slony is processing the queu of the other and truncate it. but if the > slon process are not running those tables start to grow... can you > check that the slon processes are running > > -- > Jaime Casanova www.2ndQuadrant.com > Professional PostgreSQL: Soporte 24x7 y capacitación > -- Thanks VIVEK KUMAR PANDEY
Re: postgres table have a large number of relpages and occupied a big memory size
From
"Tomas Vondra"
Date:
On 5 Srpen 2011, 10:52, Vivekkumar Pandey wrote: > Hi, > slon process is running on the system . > > Now , I have a question that Why dead tupples are remains in the table > while AUTOVACUUM process running at the fixed interval of time without > any error. Well, because that's how vacuum works. Vacuum does not compact the tables, it just marks the tuples as "deleted" so the space may be reused for new rows (inserted or updated). VACUUM FULL compacts the table, but that's not how autovacuum works, autovacuum uses plain VACUUM. So it's possible that, for example (a) once in the past the table grew to this size, then many rows were deleted but only a few inserted, so the space was not reused (b) there's a long running transaction that accesses the table, so the rows may not be marked as dead It's really difficult to say which is true. > Also suggest the Query that can view the dead tuples in the table. You can't see the dead tuples with a query - that's why they're called dead. It would be possible with the "read uncommitted" isolation level, but that's not implemented (you get "read committed" instead). If you really need to inspect the dead tuples, you have to use "pageinspect" contrib module, that gives you access to the raw data. Tomas
Re: postgres table have a large number of relpages and occupied a big memory size
From
Jaime Casanova
Date:
On Fri, Aug 5, 2011 at 5:26 AM, Tomas Vondra <tv@fuzzy.cz> wrote: > On 5 Srpen 2011, 10:52, Vivekkumar Pandey wrote: > >> Also suggest the Query that can view the dead tuples in the table. > > You can't see the dead tuples with a query - that's why they're called > dead. you can see an estimate of how many dead tuples are looking at the n_dead_tup in pg_stat_all_tables -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
Re: postgres table have a large number of relpages and occupied a big memory size
From
Vivekkumar Pandey
Date:
Hi ,
I have version of PostgreSQL as given below:---
version
-------------------------------------------------------------------------------------
PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.3 (SuSE Linux)
And it does't have any n_dead_tup columns in pg_stat_all_tables like this:
mydb=# \d pg_stat_all_tables
View "pg_catalog.pg_stat_all_tables"
Column | Type | Modifiers
---------------+--------+-----------
relid | oid |
schemaname | name |
relname | name |
seq_scan | bigint |
seq_tup_read | bigint |
idx_scan | bigint |
idx_tup_fetch | bigint |
n_tup_ins | bigint |
n_tup_upd | bigint |
n_tup_del | bigint |
In what situations table creates a new row instead of using deleted
marked tuples that's why size of table increases abnormally.
On Sat, Aug 6, 2011 at 12:12 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> On Fri, Aug 5, 2011 at 5:26 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
>> On 5 Srpen 2011, 10:52, Vivekkumar Pandey wrote:
>>
>>> Also suggest the Query that can view the dead tuples in the table.
>>
>> You can't see the dead tuples with a query - that's why they're called
>> dead.
>
> you can see an estimate of how many dead tuples are looking at the
> n_dead_tup in pg_stat_all_tables
>
> --
> Jaime Casanova www.2ndQuadrant.com
> Professional PostgreSQL: Soporte 24x7 y capacitación
>
--
Thanks
VIVEK KUMAR PANDEY
Re: postgres table have a large number of relpages and occupied a big memory size
From
"Tomas Vondra"
Date:
On 8 Srpen 2011, 8:02, Vivekkumar Pandey wrote: > Hi , > > I have version of PostgreSQL as given below:--- > > version > ------------------------------------------------------------------------------------- > PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) > 3.3.3 (SuSE Linux) > > And it does't have any n_dead_tup columns in pg_stat_all_tables like this: That's because this column was added in 8.3. BTW you're using a way too old version. It's not just that 8.1 is unsupported, but the last minor subversion in this branch is 8.1.23. There were numerous bugfixes since 8.1.2, so you should upgrade at least to this version (and plan to upgrade to something more fresh). > In what situations table creates a new row instead of using deleted > marked tuples that's why size of table increases abnormally. Whenever a row is modified - that means insert, update or delete. That's how PostgreSQL MVCC works. This wasted space needs to be reclaimed, which is exactly what (auto)vacuum does. PS: With HOT this is not exactly true, but HOT is not available in 8.1 anyway. Another reason to upgrade to a more recent version. Tomas
Re: postgres table have a large number of relpages and occupied a big memory size
From
Vivekkumar Pandey
Date:
I have seen that autovacuum takes long time to process . please give me the reason ...... On Mon, Aug 8, 2011 at 2:10 PM, Tomas Vondra <tv@fuzzy.cz> wrote: > On 8 Srpen 2011, 8:02, Vivekkumar Pandey wrote: >> Hi , >> >> I have version of PostgreSQL as given below:--- >> >> version >> ------------------------------------------------------------------------------------- >> PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) >> 3.3.3 (SuSE Linux) >> >> And it does't have any n_dead_tup columns in pg_stat_all_tables like this: > > That's because this column was added in 8.3. > > BTW you're using a way too old version. It's not just that 8.1 is > unsupported, but the last minor subversion in this branch is 8.1.23. There > were numerous bugfixes since 8.1.2, so you should upgrade at least to this > version (and plan to upgrade to something more fresh). > >> In what situations table creates a new row instead of using deleted >> marked tuples that's why size of table increases abnormally. > > Whenever a row is modified - that means insert, update or delete. That's > how PostgreSQL MVCC works. This wasted space needs to be reclaimed, which > is exactly what (auto)vacuum does. > > PS: With HOT this is not exactly true, but HOT is not available in 8.1 > anyway. Another reason to upgrade to a more recent version. > > Tomas > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Thanks VIVEK KUMAR PANDEY
Re: postgres table have a large number of relpages and occupied a big memory size
From
"Tomas Vondra"
Date:
On 9 Srpen 2011, 9:18, Vivekkumar Pandey wrote: > I have seen that autovacuum takes long time to process . > please give me the reason ...... What do you mean by "takes long time to process"? How do you measure it? Autovacuum is meant as a background process, and it should run on background and not influence the performance significantly. In some cases the default settings is not aggressive enough, so the database grows. In that case you probably need to - lower autovacuum_vacuum_threshold - lower autovacuum_vacuum_scale_factor - lower autovacuum_vacuum_cost_delay - increase autovacuum_vacuum_cost_limit But I'm not sure this is the case. And this does not release the space, it's still occupied by the database (just ready to be reused by new data). If you really want to compact the database, you may run VACUUM FULL. But that may be very intensive process, locks tables etc. Tomas
Re: postgres table have a large number of relpages and occupied a big memory size
From
Vivekkumar Pandey
Date:
On Tue, Aug 9, 2011 at 2:59 PM, Tomas Vondra <tv@fuzzy.cz> wrote: > On 9 Srpen 2011, 9:18, Vivekkumar Pandey wrote: >> I have seen that autovacuum takes long time to process . >> please give me the reason ...... > > What do you mean by "takes long time to process"? How do you measure it? > > Autovacuum is meant as a background process, and it should run on > background and not influence the performance significantly. In some cases > the default settings is not aggressive enough, so the database grows. > > In that case you probably need to > - lower autovacuum_vacuum_threshold > - lower autovacuum_vacuum_scale_factor > - lower autovacuum_vacuum_cost_delay > - increase autovacuum_vacuum_cost_limit > > But I'm not sure this is the case. And this does not release the space, > it's still occupied by the database (just ready to be reused by new data). > > If you really want to compact the database, you may run VACUUM FULL. But > that may be very intensive process, locks tables etc. > > Tomas > > I have seen postgres.log file and search for a string "autovacuum" . I found that time | DB -------------------------- 5:04 | template0 5:09 | DB1 5:25 | DB2 5:30 | template1 5:35 | template0 5:40 | DB1 5:54 | DB2 5:59 | template1 this time is given when autovacuum process available for Database. Here we see that DB1 consume around 15 min of autovacuum process while others consume 5 min(equal to naptime). this shows autovacuum process takes too much time for DB1. Also, size of DB1 is 15 GB . Is there any relation of b/w big size of DB and long autovacuum process time???? -- Thanks VIVEK KUMAR PANDEY