PDA

View Full Version : Minor info 'bugs': purge, vacuum & FAQ's



_Loki_
04-12-2010, 03:40 PM
Minor bugs (probably reported before ?)

** 1.10.04b when purging finishes this dialog pops up:

xxxx hands deleted. Purging complete. In order to reclaim the space you will need to do a Database Vacuum either through the options menu or in pgAdmin I suggest this is changed to the below & a link to the FAQ is included in the dialog:

xxxx hands deleted. Purging complete. In order to reclaim the space you will need to do a Database Vacuum through pgAdmin III see this: FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database (http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+)** FAQ - Hold'em Manager Poker Tracking Software :: Database Management (http://faq.holdemmanager.com/questions/87/Database+Management)


You can do two types of Vacuums.
* Manual Vacuum
* Simple Vacuum - Click Options / Vacuum Analyze Database in Holdem Manager and follow the directions.
Both methods reduce the size of your database and improve performance
There aren't actually two methods, because if you do as advised "Click Options / Vacuum Analyze Database in Holdem Manager" You are merely taken to here: FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database (http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+#manual) which tells you to do a manual vacuum :)

** QUESTION: I'm not sure about this - can you do DB maintenance or purging while table manager is open (for replayer, importing etc) ? I would have said "no", but this isn't mentioned in the dialog above nor in the relevant FAQ's. Perhaps this is covered by a warning popup before maintenance or purging begins ? I'm asking because I'm wondering if this could be one of the causes of some DB problems reported here

fozzy71
04-12-2010, 04:54 PM
Thank you for pointing these out. I will forward this to the tech team and dev's.

I would never recommend you use HEM or even keep it open while doing DB maintenance. I honestly don't know if it will hurt anything but I have never done so purposefully. I vacuum/reindex/defrag alot more than I should for the number of hands I play, and wayyyyy more often than 99% of our customers I would guess. I bet vacuumed/reindexed 30+ times last year and I didnt play much more than 30k hands. :o

Of all the teamviewer sessions I have done for DB corruption or connection issues, when asked if they perform regular DB maintenance, 9/10 have NEVER done any maintenance. I have never had DB connection/corruption issues with my personal DB, so you can probably make your own assumptions. :cool:

_Loki_
04-12-2010, 05:44 PM
Tknx - yes I can - I also never have DB problems

Do you remember recently a post I did called 'database daemon' or similar ?

...

I still think HM combo installer should include a daemon with user-definable preferences (including 'switch off') - by default it would monitor your postgreSQL & IT DECIDES when it wants to vacuum & reindex. It does this somehow (& not very often) when a DB is not being read/written. Incidentally I believe I read about it in the postgreSQL forums & it was strongly recommended NOT to vacuum/full just to vacuum only - to do with vacuum/full making the DB size-on-disk larger than before you vacuum/full'd (I need to check the details & the accuracy of my memory on this though :) )

I know that you've written that reindexing often isn't good - why ?

From what I've read it is SMART & works in the background - [eg My Mozilla will DL updates for my addons & I know nothing about it until I'm asked If I want to close/reopen Firefox to activate/install the updates] - Easy. So I think postgreSQL for HM should have this daemon functionality & also auto updates of security/stability patches for pg too. All this done through HM controls.

fozzy71
04-12-2010, 11:11 PM
.....

I know that you've written that reindexing often isn't good - why ?
........

I actually reindex my own DB almost every single time I vacuum, and I also have always used the Full option. :confused:

I honestly don't have any proper technical reasons as to why, just like I dont really know what 'Full' does. I mostly say to not reindex everytime because most other people I respect on poker forums never mention doing a reindex. I dont think many of them suggest you use the 'Full' option either, but I always have.

_Loki_
04-13-2010, 10:27 AM
Very heavily edited. Go here for the source, but it's very dry :)
http://www.postgresql.org/docs/current/static/routine-vacuuming.html:

23.1. Routine Vacuuming
... PostgreSQL databases require periodic maintenance known as vacuuming. For many installations, it is sufficient to let vacuuming be performed by the autovacuum daemon...

23.1.1. Vacuuming Basics
VACUUM can run in parallel with database operations
VACUUM FULL can reclaim more disk space but runs much more slowly. It requires exclusive lock on the table it is working on & it does not reduce index size proportionally; in fact it can make indexes larger.... administrators should strive to use standard VACUUM and avoid VACUUM FULL.

23.1.2. Recovering Disk Space
The usual goal of routine vacuuming is to do standard VACUUMs often enough to avoid needing VACUUM FULL. The autovacuum daemon attempts to work this way, and in fact will never issue VACUUM FULL...Although VACUUM FULL can be used to shrink a table back to its minimum size and return the disk space to the operating system, there is not much point in this if the table will just grow again in the future. Thus, moderately-frequent standard VACUUM runs are a better approach than infrequent VACUUM FULL runs for maintaining heavily-updated tables

23.1.3. Updating Planner Statistics
The autovacuum daemon, if enabled, will automatically issue ANALYZE commands whenever the content of a table has changed sufficiently

fozzy71
04-18-2010, 04:59 PM
Thank you for posting that. I have modified my new script reply for db maintenance.