Обсуждение: DB Slowing Down
Hi, we have a database. not to big about 50 tables, 20m records. On a daily basis we update/insert/delete between 500k to 1M records in total. We run a full vacuum every night. The db grows probably by 200k records each day and at the end of the month it gets cleaned up. We run 7.4.1 and 7.4.6 Now, after a few weeks the db starts to slow down after about 2 months same operations take 2-3 times as long as originally. The only way to speed it up again is to drop the entire database; run an initdb, create and restore the database. Is that an expected behavior? Any help or suggestions are appreciated. Thanks Alex
Alex wrote: > Hi, > we have a database. not to big about 50 tables, 20m records. On a daily > basis we update/insert/delete between 500k to 1M records in total. We > run a full vacuum every night. The db grows probably by 200k records > each day and at the end of the month it gets cleaned up. > We run 7.4.1 and 7.4.6 > > Now, after a few weeks the db starts to slow down after about 2 months > same operations take 2-3 times as long as originally. The only way to > speed it up again is to drop the entire database; run an initdb, create > and restore the database. Information, Alex, information. Can you give a specific example of where a query gets slower? Is this a gradual effect or sudden? Do you know if your indexes are growing unexpectedly? (shouldn't be, but worth keeping an eye on) Are you happy PG is tuned -- Richard Huxton Archonet Ltd
It a gradual process. For example, we have 3 reference tables that get updated very day. they have between 3,5 and 7M records. All we do is simple inserts, deletes. The number of records is different each day so its a bit difficult to say. Another table is a price database. This is where we actually see the biggest difference. The 2 tables have about 2M records each, each day we add about 60-80k records and update about the same number. At the end of the day about 80% of these will be removed. So the tables grow by 500k records a month, but at the end of the month again we remove about 300-400k of these records. What we see is that this load of 60-80 records at the beginning of a month and after re creating the db takes in the are of 8min or so. after 2-3 month the whole process will take up to 20-25 minutes. even though the two tables have only grown max. 500k. Even the cleanup at the end of the month does not help a lot. it speeds things up but nowhere close to what it would be after recreating the db. Alex Richard Huxton wrote: > Alex wrote: > >> Hi, >> we have a database. not to big about 50 tables, 20m records. On a >> daily basis we update/insert/delete between 500k to 1M records in >> total. We run a full vacuum every night. The db grows probably by >> 200k records each day and at the end of the month it gets cleaned up. >> We run 7.4.1 and 7.4.6 >> >> Now, after a few weeks the db starts to slow down after about 2 >> months same operations take 2-3 times as long as originally. The only >> way to speed it up again is to drop the entire database; run an >> initdb, create and restore the database. > > > Information, Alex, information. > Can you give a specific example of where a query gets slower? > Is this a gradual effect or sudden? > Do you know if your indexes are growing unexpectedly? (shouldn't be, > but worth keeping an eye on) > Are you happy PG is tuned >
Alex wrote: > It a gradual process. For example, we have 3 reference tables that get > updated very day. they have between 3,5 and 7M records. All we do is > simple inserts, deletes. The number of records is different each day so > its a bit difficult to say. > Another table is a price database. This is where we actually see the > biggest difference. The 2 tables have about 2M records each, each day we > add about 60-80k records and update about the same number. At the end of > the day about 80% of these will be removed. So the tables grow by 500k > records a month, but at the end of the month again we remove about > 300-400k of these records. What we see is that this load of 60-80 > records at the beginning of a month and after re creating the db takes > in the are of 8min or so. after 2-3 month the whole process will take up > to 20-25 minutes. even though the two tables have only grown max. 500k. > Even the cleanup at the end of the month does not help a lot. it speeds > things up but nowhere close to what it would be after recreating the db. Well, if you're not getting index bloat (and I don't think you should be in 7.4.x) then that suggests you're doing a VACUUM not VACUUM FULL (and don't have enough free-space-map allocated. Try adding a VERBOSE to your VACUUMs and see what figures come out, and check the manuals for the fsm... settings. Alternatively, it could be that the physical order of records is better when you dump/restore. It might be looking into what CLUSTER might do for you if run overnight. Basically, it sounds like one of 3 things: 1. The indexes are continually growing. (REINDEX) 2. Increasing numbers of "gaps" in the tables due to updates/deletes. (VACUUM) 3. The dump/restore puts the data in a useful physical ordering which gets shuffled as you update. (CLUSTER) -- Richard Huxton Archonet Ltd
Thanks for the suggestions. I run a vaccum analyze every night. I will look into all the options you suggested. Thanks Alex Richard Huxton wrote: > Alex wrote: > >> It a gradual process. For example, we have 3 reference tables that >> get updated very day. they have between 3,5 and 7M records. All we >> do is simple inserts, deletes. The number of records is different >> each day so its a bit difficult to say. >> Another table is a price database. This is where we actually see the >> biggest difference. The 2 tables have about 2M records each, each day >> we add about 60-80k records and update about the same number. At the >> end of the day about 80% of these will be removed. So the tables grow >> by 500k records a month, but at the end of the month again we remove >> about 300-400k of these records. What we see is that this load of >> 60-80 records at the beginning of a month and after re creating the >> db takes in the are of 8min or so. after 2-3 month the whole process >> will take up to 20-25 minutes. even though the two tables have only >> grown max. 500k. Even the cleanup at the end of the month does not >> help a lot. it speeds things up but nowhere close to what it would be >> after recreating the db. > > > Well, if you're not getting index bloat (and I don't think you should > be in 7.4.x) then that suggests you're doing a VACUUM not VACUUM FULL > (and don't have enough free-space-map allocated. Try adding a VERBOSE > to your VACUUMs and see what figures come out, and check the manuals > for the fsm... settings. > > Alternatively, it could be that the physical order of records is > better when you dump/restore. It might be looking into what CLUSTER > might do for you if run overnight. > > Basically, it sounds like one of 3 things: > 1. The indexes are continually growing. (REINDEX) > 2. Increasing numbers of "gaps" in the tables due to updates/deletes. > (VACUUM) > 3. The dump/restore puts the data in a useful physical ordering which > gets shuffled as you update. (CLUSTER) > > -- > Richard Huxton > Archonet Ltd > >
Alex wrote: > Thanks for the suggestions. > I run a vaccum analyze every night. I will look into all the options you > suggested. And please post back your results as there are others who are interested in this thread. :) -- Until later, Geoffrey