Re: [Bacula-users] Better way to garbage collect postgresql database
Hemant Shah wrote: This is a database question, but I figured some of the bacula users may have come across this problem so I am posting it here. Every monday I run following commands to check and garbage collect bacula database: dbcheck command vacuumdb -q -d bacula -z -f There is absolutely no reason to vacumm full unless your data-size actually are shrinking over time. (longer periods). A normal vacuum will make it available for the next run.. you most likely are running autovacuum anyway. reindexdb Might make sense, but weekly?.. There is AFAIK a small amount of index-bloat collecting up over time in PG. But in general just yearly or monthly should really be sufficient. Usually I purge one or two backup volumes and the above commands run in less than 20 minutes. Before my monthly Full backup I delete large amount of data from the database as I delete one month worth of Full and Incremental backups. When I run the above commands after the Full backup, the vacummdb command take 12 hours to run. Is there a faster/better way of doing it? No not really VACUUM FULL i telling PG to reorder the data on-disk and free up space for the os, that is a hard task. But it is also not needed, since you are going to used it within the next week/month anyway.. so ordinary VACUUM is sufficient. My database is about 9GB. If I backup database using pgdump and then restore it, will it do the same thing as vacuumdb and reindexdb commands? Basically yes. -- Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Better way to garbage collect postgresql database
--- On Thu, 3/19/09, Kevin Keane subscript...@kkeane.com wrote: From: Kevin Keane subscript...@kkeane.com Subject: Re: [Bacula-users] Better way to garbage collect postgresql database To: Cc: baculausers bacula-users@lists.sourceforge.net Date: Thursday, March 19, 2009, 8:30 PM Hemant Shah wrote: Folks, This is a database question, but I figured some of the bacula users may have come across this problem so I am posting it here. Every monday I run following commands to check and garbage collect bacula database: dbcheck command vacuumdb -q -d bacula -z -f reindexdb Usually I purge one or two backup volumes and the above commands run in less than 20 minutes. Before my monthly Full backup I delete large amount of data from the database as I delete one month worth of Full and Incremental backups. When I run the above commands after the Full backup, the vacummdb command take 12 hours to run. Is there a faster/better way of doing it? It has been a long time since I administered a postgres DB, but if memory serves me right you might be able to drop some indexes, then do the vacuuming, and then recreate them. Also, I believe you can vacuum individual tables rather than the database as a whole. The lion's share of the vacuuming would happen in the files table, so that's probably the one you'd want to first look at in terms of dropping/recreating indexes, and also in terms of vacuuming separately. Also there are several levels of vacuuming. With this type of table, you would probably not want to get too aggressive. What you don't want to do is eliminate all the empty space in the database, only to later need the same empty space again. You do want to vacuum simply to consolidate empty space into larger chunks. Basically, the same idea as disk defragmentation. If memory serves me right, this milder vacuuming is the default. Kevin, This is exactly what I want to do. I come from DB2 world and we would reorg the table. I run milder version of vacuum on other days, but I run extensive vacuum after full backup. Sorry I have to speak in concepts rather than concrete here, but it just has been too long. My database is about 9GB. If I backup database using pgdump and then restore it, will it do the same thing as vacuumdb and reindexdb commands? Pretty close, but keep in mind that you would have considerable database downtime. You can do this, too, on a per-table basis. If I can reduce the time it takes to do full vacuum then I would like to do it before my full backup. This database is for bacula only so unless I am doing backups it could be down for few hours. If dump/load takes less than four hours then I can do it before full backups start. -- Kevin Keane Owner The NetTech Find the Uncommon: Expert Solutions for a Network You Never Have to Think About Office: 866-642-7116 http://www.4nettech.com This e-mail and attachments, if any, may contain confidential and/or proprietary information. Please be advised that the unauthorized use or disclosure of the information is strictly prohibited. The information herein is intended only for use by the intended recipient(s) named above. If you have received this transmission in error, please notify the sender immediately and permanently delete the e-mail and any copies, printouts or attachments thereof. -- Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users Hemant Shah E-mail: hj...@yahoo.com -- Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Better way to garbage collect postgresql database
--- On Fri, 3/20/09, Jesper Krogh jes...@krogh.cc wrote: From: Jesper Krogh jes...@krogh.cc Subject: Re: [Bacula-users] Better way to garbage collect postgresql database To: hj...@yahoo.com Cc: baculausers bacula-users@lists.sourceforge.net Date: Friday, March 20, 2009, 12:30 AM Hemant Shah wrote: This is a database question, but I figured some of the bacula users may have come across this problem so I am posting it here. Every monday I run following commands to check and garbage collect bacula database: dbcheck command vacuumdb -q -d bacula -z -f There is absolutely no reason to vacumm full unless your data-size actually are shrinking over time. (longer periods). A normal vacuum will make it available for the next run.. you most likely are running autovacuum anyway. Yes, I do run autovacuum, but I just start using postgresql and I am not sure how efficient it is in space usage. I was afraid that the data would be fragmented if it re-uses the free space. reindexdb Might make sense, but weekly?.. There is AFAIK a small amount of index-bloat collecting up over time in PG. But in general just yearly or monthly should really be sufficient. Weekly may be overkill, but it is part of script that run through cron. Usually I purge one or two backup volumes and the above commands run in less than 20 minutes. Before my monthly Full backup I delete large amount of data from the database as I delete one month worth of Full and Incremental backups. When I run the above commands after the Full backup, the vacummdb command take 12 hours to run. Is there a faster/better way of doing it? No not really VACUUM FULL i telling PG to reorder the data on-disk and free up space for the os, that is a hard task. But it is also not needed, since you are going to used it within the next week/month anyway.. so ordinary VACUUM is sufficient. My database is about 9GB. If I backup database using pgdump and then restore it, will it do the same thing as vacuumdb and reindexdb commands? Basically yes. Hemant Shah E-mail: hj...@yahoo.com -- Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Better way to garbage collect postgresql database
Hemant Shah wrote: --- On Thu, 3/19/09, Kevin Keane subscript...@kkeane.com wrote: From: Kevin Keane subscript...@kkeane.com Subject: Re: [Bacula-users] Better way to garbage collect postgresql database To: Cc: baculausers bacula-users@lists.sourceforge.net Date: Thursday, March 19, 2009, 8:30 PM Hemant Shah wrote: Folks, This is a database question, but I figured some of the bacula users may have come across this problem so I am posting it here. Every monday I run following commands to check and garbage collect bacula database: dbcheck command vacuumdb -q -d bacula -z -f reindexdb Usually I purge one or two backup volumes and the above commands run in less than 20 minutes. Before my monthly Full backup I delete large amount of data from the database as I delete one month worth of Full and Incremental backups. When I run the above commands after the Full backup, the vacummdb command take 12 hours to run. Is there a faster/better way of doing it? It has been a long time since I administered a postgres DB, but if memory serves me right you might be able to drop some indexes, then do the vacuuming, and then recreate them. Also, I believe you can vacuum individual tables rather than the database as a whole. The lion's share of the vacuuming would happen in the files table, so that's probably the one you'd want to first look at in terms of dropping/recreating indexes, and also in terms of vacuuming separately. Also there are several levels of vacuuming. With this type of table, you would probably not want to get too aggressive. What you don't want to do is eliminate all the empty space in the database, only to later need the same empty space again. You do want to vacuum simply to consolidate empty space into larger chunks. Basically, the same idea as disk defragmentation. If memory serves me right, this milder vacuuming is the default. Kevin, This is exactly what I want to do. I come from DB2 world and we would reorg the table. I run milder version of vacuum on other days, but I run extensive vacuum after full backup. I think that's excessive. I used to run a major production database, processing hundreds of online purchases per hour, in Postgres. And we only did the regular vacuum once a week, if memory serves me right. Full vacuums only in very rare circumstances - pretty much never. Not saying that this is always true; our database at the time pretty much only ever grew, we almost never deleted any records from it. The bacula database obviously does a lot more deleting. In fact, a full vacuum can be harmful to performance, because it eliminates the free space that Postgres can work with. In a scenario such as yours, it may well be better if the database stays at its biggest size at all times, and you let Postgres manage the free space. -- Kevin Keane Owner The NetTech Find the Uncommon: Expert Solutions for a Network You Never Have to Think About Office: 866-642-7116 http://www.4nettech.com This e-mail and attachments, if any, may contain confidential and/or proprietary information. Please be advised that the unauthorized use or disclosure of the information is strictly prohibited. The information herein is intended only for use by the intended recipient(s) named above. If you have received this transmission in error, please notify the sender immediately and permanently delete the e-mail and any copies, printouts or attachments thereof. -- Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Better way to garbage collect postgresql database
Hemant Shah wrote: --- On Fri, 3/20/09, Jesper Krogh jes...@krogh.cc wrote: From: Jesper Krogh jes...@krogh.cc Subject: Re: [Bacula-users] Better way to garbage collect postgresql database To: hj...@yahoo.com Cc: baculausers bacula-users@lists.sourceforge.net Date: Friday, March 20, 2009, 12:30 AM Hemant Shah wrote: This is a database question, but I figured some of the bacula users may have come across this problem so I am posting it here. Every monday I run following commands to check and garbage collect bacula database: dbcheck command vacuumdb -q -d bacula -z -f There is absolutely no reason to vacumm full unless your data-size actually are shrinking over time. (longer periods). A normal vacuum will make it available for the next run.. you most likely are running autovacuum anyway. Yes, I do run autovacuum, but I just start using postgresql and I am not sure how efficient it is in space usage. I was afraid that the data would be fragmented if it re-uses the free space. The opposite is true, actually. When there is no free space left in the database, postgres has no choice but to append new data at the end, regardless of where the remaining table data is stored. When you let postgres manage the free space, it can pick the space most appropriate for storing any particular piece of data, and minimize fragmentation. -- Kevin Keane Owner The NetTech Find the Uncommon: Expert Solutions for a Network You Never Have to Think About Office: 866-642-7116 http://www.4nettech.com This e-mail and attachments, if any, may contain confidential and/or proprietary information. Please be advised that the unauthorized use or disclosure of the information is strictly prohibited. The information herein is intended only for use by the intended recipient(s) named above. If you have received this transmission in error, please notify the sender immediately and permanently delete the e-mail and any copies, printouts or attachments thereof. -- Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Better way to garbage collect postgresql database
Hemant Shah wrote: Folks, This is a database question, but I figured some of the bacula users may have come across this problem so I am posting it here. Every monday I run following commands to check and garbage collect bacula database: dbcheck command vacuumdb -q -d bacula -z -f reindexdb Usually I purge one or two backup volumes and the above commands run in less than 20 minutes. Before my monthly Full backup I delete large amount of data from the database as I delete one month worth of Full and Incremental backups. When I run the above commands after the Full backup, the vacummdb command take 12 hours to run. Is there a faster/better way of doing it? It has been a long time since I administered a postgres DB, but if memory serves me right you might be able to drop some indexes, then do the vacuuming, and then recreate them. Also, I believe you can vacuum individual tables rather than the database as a whole. The lion's share of the vacuuming would happen in the files table, so that's probably the one you'd want to first look at in terms of dropping/recreating indexes, and also in terms of vacuuming separately. Also there are several levels of vacuuming. With this type of table, you would probably not want to get too aggressive. What you don't want to do is eliminate all the empty space in the database, only to later need the same empty space again. You do want to vacuum simply to consolidate empty space into larger chunks. Basically, the same idea as disk defragmentation. If memory serves me right, this milder vacuuming is the default. Sorry I have to speak in concepts rather than concrete here, but it just has been too long. My database is about 9GB. If I backup database using pgdump and then restore it, will it do the same thing as vacuumdb and reindexdb commands? Pretty close, but keep in mind that you would have considerable database downtime. You can do this, too, on a per-table basis. -- Kevin Keane Owner The NetTech Find the Uncommon: Expert Solutions for a Network You Never Have to Think About Office: 866-642-7116 http://www.4nettech.com This e-mail and attachments, if any, may contain confidential and/or proprietary information. Please be advised that the unauthorized use or disclosure of the information is strictly prohibited. The information herein is intended only for use by the intended recipient(s) named above. If you have received this transmission in error, please notify the sender immediately and permanently delete the e-mail and any copies, printouts or attachments thereof. -- Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
