Обсуждение: weird table sizes
hello to all i would like your help in the following matter -> we have 2 identical databases. the 1st was built from scratch while the 2nd was 'restored' from a dump of another database (without the data). so the sequences for instance on the 2nd started from very big numbers. in these databases there is a table. in the first it has around 350 mil rows and size of 65GB (sequence started from 1). in the second it has 250 mil and size of 430 GB(sequence started from 9 billions). how can that be?the sizes are from the pg_relation_size function. i know that with what i said you certainly cannot answer to my question but i dont know what kind of extra info you would like to know in order to help me, so i can provide you anything you find useful. i would appreciate it if you could point me to some direction.thank you in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/weird-table-sizes-tp4626505p4626505.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Saturday, July 23, 2011 11:29:48 am MirrorX wrote: > hello to all > > i would like your help in the following matter -> > > we have 2 identical databases. the 1st was built from scratch while the 2nd > was 'restored' from a dump of another database (without the data). so the > sequences for instance on the 2nd started from very big numbers. in these > databases there is a table. in the first it has around 350 mil rows and > size of 65GB (sequence started from 1). in the second it has 250 mil and > size of 430 GB(sequence started from 9 billions). how can that be?the > sizes are from the pg_relation_size function. > > i know that with what i said you certainly cannot answer to my question but > i dont know what kind of extra info you would like to know in order to help > me, so i can provide you anything you find useful. First I am having a problem with they are identical but different:) Questions: 1) Same or different versions of Postgres? 2) Same or different OSes.? 3) The 2nd was restored from a schema only dump, but has data in it. Where did the data come from? 4) What is the data? 5) Why did the sequence jump? > > i would appreciate it if you could point me to some direction.thank you in > advance > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/weird-table-sizes-tp4626505p462650 > 5.html Sent from the PostgreSQL - general mailing list archive at > Nabble.com. -- Adrian Klaver adrian.klaver@gmail.com
thx for the reply :) the table are identical, and i mean that they have the same columns, the same constraints, the same indexes etc 1) the small table(65gb) is on version 8.4.7 and the big one(430gb) on 8.4.4 2) the small in on Red Hat 4.1.2-50 and the big on Red Hat 4.1.2-46 3) the 2nd was restored from a dump with data in it (my bad for the msg before where i said that it was just the schema). now it is running and has more and more data but the total rowcount is the one i gave, around 250million rows for that table 4) the data in these tables are 2 columns with dates, 10 integers and some varchar columns. these columns are varchar(128) but i checked and they data in there are far less and almost the same on the 2 tables. so if the varchar is working "properly" and gets only the actual size of the string inside, then the difference in the sizes of the 2 tables is not coming from these columns. 5) i dont know why the sequence jumped. is there anything i can run to find that? i just saw that it started from 9 billions (the min(id) for that table is 9billions) -- View this message in context: http://postgresql.1045698.n5.nabble.com/weird-table-sizes-tp4626505p4626577.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Saturday, July 23, 2011 12:18:25 pm MirrorX wrote: > thx for the reply :) > > the table are identical, and i mean that they have the same columns, the > same constraints, the same indexes etc > > 1) the small table(65gb) is on version 8.4.7 and the big one(430gb) on > 8.4.4 2) the small in on Red Hat 4.1.2-50 and the big on Red Hat 4.1.2-46 > 3) the 2nd was restored from a dump with data in it (my bad for the msg > before where i said that it was just the schema). now it is running and > has more and more data but the total rowcount is the one i gave, around > 250million rows for that table So where was the data dumped from? Did it come from the original table? > 4) the data in these tables are 2 columns with dates, 10 integers and some > varchar columns. these columns are varchar(128) but i checked and they data > in there are far less and almost the same on the 2 tables. so if the > varchar is working "properly" and gets only the actual size of the string > inside, then the difference in the sizes of the 2 tables is not coming > from these columns. > 5) i dont know why the sequence jumped. is there anything i can run to find > that? i just saw that it started from 9 billions (the min(id) for that > table is 9billions) One place to look is the dump file you seeded the second database with. Look for the sequence name. Usually there is a CREATE SEQUENCE followed by a SELECT setval(sequence_name) that actually sets the sequence value. See if it started out big or not. Also if comes from the data in table1, does the max(id) for table1 correspond to min(id) for table2? Another cause for the jump could be an enormous amount of churn in the second table i.e. a lot of deletes followed by inserts or failed inserts. These leads to the next question, has the table been VACUUMed lately? > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/weird-table-sizes-tp4626505p462657 > 7.html Sent from the PostgreSQL - general mailing list archive at > Nabble.com. -- Adrian Klaver adrian.klaver@gmail.com
i mentioned the sequences number only b/c it seemed stange and i didnt know if it could be related to the "weird" sizes. now i found something more weird...the autovacuum is ON but on pg_stat_user_tables on this specific table tha last_vacuum and last_autovacuum are both NULL...how can this happen? thx a lot for all the help -- View this message in context: http://postgresql.1045698.n5.nabble.com/weird-table-sizes-tp4626505p4626910.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Saturday, July 23, 2011 3:34:07 pm MirrorX wrote: > i mentioned the sequences number only b/c it seemed stange and i didnt know > if it could be related to the "weird" sizes. > > now i found something more weird...the autovacuum is ON but on > pg_stat_user_tables on this specific table tha last_vacuum and > last_autovacuum are both NULL...how can this happen? Old stats? Try manually running ANALYZE against the table. > > > thx a lot for all the help > -- Adrian Klaver adrian.klaver@gmail.com
On Sun, Jul 24, 2011 at 11:45 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Saturday, July 23, 2011 3:34:07 pm MirrorX wrote: >> i mentioned the sequences number only b/c it seemed stange and i didnt know >> if it could be related to the "weird" sizes. >> >> now i found something more weird...the autovacuum is ON but on >> pg_stat_user_tables on this specific table tha last_vacuum and >> last_autovacuum are both NULL...how can this happen? > > Old stats? Try manually running ANALYZE against the table. > Are these on the same server or different servers? Is it possible that there is database bloat on the second? How many rows do you (roughly) expect? is that estimate reasonable? Best wishes, Chris Travers
thank you all for your help. finally the big table had many more rows(2 billions) than the stats showed so there is no "weird" thing going on. -- View this message in context: http://postgresql.1045698.n5.nabble.com/weird-table-sizes-tp4626505p4630238.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.