PDA

View Full Version : Vacuum, reindex and dump/restore



SkyWalker01
04-19-2010, 08:40 AM
My sql db have 16gb, what can i do in order to reduce the size?
Vacuum, reindex and dump/restore?
Or what else?
And doing that will i lose the replayer hand option?

Tks
regards

fozzy71
04-19-2010, 10:30 AM
You only lose the ability to replay them if you Purge them from the DB.


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. You do not want to purge any Hero hands so make sure your alias is properly configured before purging.
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

SkyWalker01
04-19-2010, 01:33 PM
And after al this proccess will i reduce the size of the db?
And will i lose the replay hand option?

Tks again
regards

fozzy71
04-19-2010, 06:56 PM
Yes, it should reduce the DB size. The biggest amount of space regained will probably come from the \pg_log folder being emptied. The vacuum and defrag will help performance.

If you want to be able to always replay all of your hero hands DO NOT do wither of the Purge DB options I posted above. Or, if you do use a Purge option, make sure you properly configure your alias with all your screen names, so you don't purge any of your own hands.

SkyWalker01
09-01-2010, 12:29 PM
Hi
i cant find the options for purge the db in this new version

tks
regards

Patvs
09-02-2010, 12:17 AM
options--> settings---> observed handhistory --> purge

DON'T USE options--> settings---> purge

SkyWalker01
09-09-2010, 02:49 PM
Sorry!
I used it the 2nd one, will i have a problem in the db ?
Tks

fozzy71
09-09-2010, 06:19 PM
No it shouldnt cause any issues with your DB, it just doesn't work properly at this time.

SkyWalker01
09-09-2010, 09:52 PM
I used the 2nd one because in the first one i didnīt see the option for select the "alias" in order to donīt purge my hands for replay it in the future, may this cause that after play a sesion of 1500/2000 hands the HM use the 100% of the cpu and i have to restart the pc ? have i to purge "all my hands too" ?

tks

Patvs
09-10-2010, 01:19 AM
You can still select your alias if you pick #1.

options--> settings--> observed hands configuration--> purge hands--->