Re: table size growing out of control
От | Robert Treat |
---|---|
Тема | Re: table size growing out of control |
Дата | |
Msg-id | 1026922249.21423.138.camel@camel обсуждение исходный текст |
Ответ на | Re: table size growing out of control (Manfred Koizar <mkoi-pg@aon.at>) |
Список | pgsql-general |
On Wed, 2002-07-17 at 11:14, Manfred Koizar wrote: > On 16 Jul 2002 15:45:08 -0400, Robert Treat <rtreat@webmd.net> wrote: > >> > We run a function against the table > >> > about every 5 minutes which updates on average maybe 100 rows and adds > >> > rows at the rate of maybe 1 an hour, > > Robert, are you sure about those 100 updated rows/5 minutes? > Yesterday I started thinking this as well and after much digging and swearing that there was no way I was updating more than 300/5 minutes, I have found an update statement in one of the functions that contains no where clause. I need to do some more digging, but if I'm right this means I am updating all 5500 rows every 5 minutes. Actually I am probably updating more but I think at least 5500 rows are getting updated! > > > > relname | relkind | relpages | mb > >-----------------------+---------+----------+----- > > health_ex_group | i | 21 | 0 > > health_exception_test | r | 24839 | 194 > > > > >i then ran vacuum analyze on the table which gives me sizes of: > > > > relname | relkind | relpages | mb > >-----------------------+---------+----------+----- > > health_ex_group | i | 686 | 5 > > health_exception_test | r | 26331 | 205 > > So in the time between reindex and vacuum your table has grown by 1500 > pages or (estimated) 150000 tuples. That's 30 times the number of > rows, or - in other words - at a rate of 20 rows/minute this growth > would be expected in 100 days. > > Now I may be wrong, but ISTM there is a process (or more) running that > does a *lot* of updates. Can you tell us something about the function > that is supposed to update 100 rows every five minutes? Is anything > else doing updates you were not aware of at first sight? > Everything else falls into place with that many updates. Clearly my FSM would be too small to remember all of that, so my vacuums had little chance of being effective. Temporarily I should be able to add a vacuum every 5 minutes along with the function call to keep things from getting out of hand until the function is fixed. Thanks to everyone else who helped out on this, hopefully this thread will prove of use to some other folks. Robert Treat
В списке pgsql-general по дате отправления: