[GENERAL] postgres table have a large number of relpages and occupied a big memory size
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres table have a large number of relpages and occupied a big memory size
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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres table have a large number of relpages and occupied a big memory size
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 wrote: > On Fri, Aug 5, 2011 at 2:37 AM, Vivekkumar Pandey > 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres table have a large number of relpages and occupied a big memory size
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 wrote: > On Fri, Aug 5, 2011 at 5:26 AM, Tomas Vondra 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres table have a large number of relpages and occupied a big memory size
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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres table have a large number of relpages and occupied a big memory size
On Tue, Aug 9, 2011 at 2:59 PM, Tomas Vondra 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
