PDA

View Full Version : Problem of database size : ridiculous disk space free after purge & vacuum...



MikPok
02-24-2010, 10:11 AM
Hello,

After months of HM using, my data folder of PostGres grows to 12 Go !

So, I try to start with a new clean and little database. I purge all hands played before 2010, I do not tick the player area, so all users are concerned, I choose all Poker Sites, do it for all cash limit, and tourneys.

The process works during a while and says about 100 000 hands deleted.

Just after, I do a vacuum via PGadmin3.

After these works, I see my data folder goes from 12 Go to 10 Go !

I can't understand such a ridiculous space gain when I kill over than 90% of my hands played.

So, after a backup, I test a whole delete of the hand (no date restriction). Purge, vacuum... In HM, no hands visible, so the process is ok. Data folder size : 10go!!!

What's wrong ?
Moreover, I see HM says 150 000 players in the left panel. But no hands in the database after the purge... What's wrong ?

Thanks

fozzy71
02-24-2010, 05:39 PM
If you wanted a new, small DB you don't want to start with a huge DB and purge things. Simply make a new DB and set it as default. Then you can import the hands from 2010 to the new DB.


*Try creating a new UTF DB - FAQ - Hold'em Manager Poker Tracking Software :: Create / Delete Database (http://faq.holdemmanager.com/questions/75/Create+%7B47%7D+Delete+Database)
*Now import a small portion of your \HMArchive so you can see if the problem exists in the new DB - FAQ - Hold'em Manager Poker Tracking Software :: How to Import & Export Hands (http://faq.holdemmanager.com/questions/98/How+to+Import+%26+Export+Hands)
*If the new DB seems to work properly, you will want to import the rest of your archives to the new DB, export/import the hands from the old DB to the new DB, and export/import any player notes and tourney summaries - and then delete the old DB.
*Make sure you export/backup everything before deleting the old DB



If you want to delete your old DB to save space, that can be done also. Chances are your first DB is that big because you have logging enabled still.


Logging:

I would suggest you turn off logging on your new SQL installation as well as these other performance improvements in my signature - Top 10: HEM performance increase tips


To turn off logging, follow these instructions. This is the first thing I do anytime I install SQL for someone.

- Start > My Computer > Tools > Folder Options > View > UnCheck 'Hide Extensions for known file types' > OK

- C:\Program Files\PostgreSQL\data\postgresql.conf > Right-Click > Open With.. > NotePad > Check 'Always use this program'

- Scroll about half way down to ~Lines 245 - 260:

- The last line of this block of code, for the 'logging_collector', must be changed from On to Off.



------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = off # Enable capturing of stderr and csvlog


File > Save; File > Close

Start > Programs > PostgreSQL 8.3 > Reload Configuration.


- Start > My Computer > C:\Program Files\PostgreSQL\data\pg_log, or C:\Program Files (x86)\PostgreSQL\data\pg_log, if you have 64 bit Vista. Delete all files in that folder. And it might be a good idea to empty your recycle bin at that point, or use a 3rd party utility like CCleaner to clean up all your junk/temporary files. You want to do this before you get to the vacuum and defrag process.



Regarding database maintenance:

You should regularly purge mined hands in your database, after purge vacuum / full analyze the database via pgadmin.
FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands from Database (http://faq.holdemmanager.com/questions/100/Purge+Hands+from+Database)
FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands (http://faq.holdemmanager.com/questions/197/Purge+Hands)

Then you can vacuum/analyze - FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database (http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+#manual)

Before you defragment you should stop the PostgreSQL service:

Start > Programs > PostgreSQL 8.x > Stop Server

Use your favorite defrag tool. I recommend Smart Defrag.

After defrag I recommend you reboot your PC. If you need to access the DB before rebooting you can start the PostgreSQL service.

Start > Programs > PostgreSQL 8.x > Start Server

vlsup
02-24-2010, 06:04 PM
btw, there is another log folder in Data with lot of files
\pg_xlog
some of them are month old, some - new
can I delete these old ones? And how much?

MikPok
02-24-2010, 08:48 PM
Thanks for this help. Iv'e deleted the log files, and disable the logging in the config file. It's ok, great.

The hands are now not in the database anymore. Cool. But a question : do the deleted hands are considered in the data shown in the HUD ? I hope not...

Sincerely,

fozzy71
02-25-2010, 12:07 AM
btw, there is another log folder in Data with lot of files
\pg_xlog
some of them are month old, some - new
can I delete these old ones? And how much?

No, you can only remove the \pg_log files.


Thanks for this help. Iv'e deleted the log files, and disable the logging in the config file. It's ok, great.

The hands are now not in the database anymore. Cool. But a question : do the deleted hands are considered in the data shown in the HUD ? I hope not...

Sincerely,

You didn't remove any hands from the DB. You deleted unnecessary log files from the postgresql database. This wont affect anything with how HM or the HUD displays things. The original hand histories are archived and all of your hands should still appear in the DB. The log files are used by database guru's for troubleshooting or queries, etc. For 99.9% of poker players they are just a waste of space.

MikPok
02-25-2010, 10:09 AM
You didn't remove any hands from the DB. You deleted unnecessary log files from the postgresql database. This wont affect anything with how HM or the HUD displays things. The original hand histories are archived and all of your hands should still appear in the DB. The log files are used by database guru's for troubleshooting or queries, etc. For 99.9% of poker players they are just a waste of space.

Oops, I was speaking about the first post of this thread, about purge and vacuum. About the hands deleted by purge, and after vacuum: are they considered by the HUD ? I know these deleted hands are no more visible in the reports, but what about the computed variables in the HUD ? I hope the variables are reseted ?

Sincerely,

netsrak
02-25-2010, 11:00 AM
No, the stats are still used for the Hud.

You can configure a time period in the additional Hud filters.

MikPok
02-25-2010, 09:27 PM
Ok.

So, how could I do if I want to ignore stats of the 5 last days (special not significant games that I do not want to keep the hands, and ignore them in the HUD) from some players I've played against. If I decide to kill these hands, I don't understand why these hands are considered in the HUD...

morny
02-25-2010, 09:47 PM
The hud works off a cache so deleting those wont delete it from the hud, we will be introducing an option to re-cache in later versions but for now you'll need to either export your hands into a new database or wait for the re-cache option, running a query from the database every time would significantly slow the hud performance

MikPok
02-25-2010, 10:00 PM
Thanks for answer.

An option to rebuild the cache is a great idea. I understand the problem of performance, moreover, let the user having the choice of rebuild the cache on demand is the better accommodation. :)

morny
02-25-2010, 10:18 PM
yeah thats the exact plan, it will be a user option and not enforced

BubbleBoy
02-26-2010, 04:43 AM
when I backup-restore, are the cached hud stats moved as well?

morny
02-26-2010, 12:05 PM
Im not 100% sure but it should be, if you want to test import a couple of hands into a new database, delete some hands and backup and restore and youll be able to see. i Would expect so

MikPok
02-27-2010, 11:02 AM
Im not 100% sure but it should be, if you want to test import a couple of hands into a new database, delete some hands and backup and restore and youll be able to see. i Would expect so

So, I think, for the future option "On demand rebuild the cache", it will be as important to delete stats from the cache read by the hud, if the hands are deleted, but also, to add in the cache, to be read by the hud, the data from a restored database... :)