Top 10: HEM performance increase tips

[Patvs]

Yes... I would manually run vacuum/analyse/reindex in pgadminIII, until we've tweaked/fixed the new optimization tool

[rowhousepd]

So just to be clear, you'd recommend doing all three (vac/analyze/reindex) every 50k hands, correct?

Yes... I would manually run vacuum/analyse/reindex in pgadminIII, until we've tweaked/fixed the new optimization tool

Also, is the optimization tool not working properly? I've run the full optimization a few times and didn't get any errors.

[Patvs]

Yes, run all three (vac/analyze/reindex) every 50k hands.
But you can also use the optimization tool, since it works for you.

The optimization tool has some problems for some HM users, which is why I still recommended using pgadminIII

[jdoury]

My Computer has run out of hard drive space, and I suspect HM to be the prob, as it looks like there are about 25G worth of files that look like:

c:\Program files\PostgreSQL\8.3\data\base\16403

About 25 of these at about 1G each. Can I get rid of some of these somehow? I surely can delete a lot of my hands, especially old ones, but don't want to do it wrong and mess up the whole database. Any advice?

[Patvs]

Don't delete any files in the /data subfolders.
Only delete ALL log files from the /pg_log folder (don't delete the actual folder, just its content)

[fozzy71]

Logging:

I would suggest you turn off logging on your SQL installation.

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
FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands

Then you can vacuum/analyze - FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database

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

[czglory]

if I bought a SSD to add to my already existing HD, how would this be done? Would I have to format it with windows, re-format my computer, or something? Also, I have a really large database, do SSDs come in larger than 160gB?

[AVO|Ratatuj]

you may buy up to 1Tb ssd now but they do cost a lot. you can install OS and base just like on a regular HDD. format with win7 is ok. you can also back up and then restore your base at ssd or you can use soft like Acronis. you can also purge hands and delite logs to reduce DB's size.

[Patvs]

Ideally you reinstall Windows on the SSD. During the Windows install, it will automatically format the drive, creating a aligned partition, etc.
If you install Windows 7 it will also automatically turn off defrag, enable TRIM (if your SSD supports TRIM), etc.

Note: change your BIOS setting from IDE to AHCI, before you install Windows on the SSD.

Then you restore the backup of your database to the SSD. (Or you reimport all your hands into a new database)


----

The alternative would be... to keep using your old harddisk for Windows... and only use the SSD for your database. But Windows on a SSD is a blast: since it will be so much faster.

SSDs larger than 160 GB exist. You have 1 TB SSDs... but they are CRAZY expensive. And the 400+ GB ones usually are just 2x 200 GB SSD glued together with a inboard RAID chip. And RAID is NOT recommended since it doesn't support TRIM.


If you need 160+ GB, I'd recommend to WAIT on the Intel X25-M G3 Postville Refresh which will have a capacity of 80/160/300/600 GB. (note the 600 GB version will be really expensive)
Or buy a 180/200/240 GB OCZ Vertex 2 now.
A cheaper alternative is the Intel X25-m G2 80/160 GB, or smaller OCZ Vertex 2. It's probably a lot cheaper to just buy two. One for Windows. One for your database. But going crazy with SSDs is only something you should consider if you really need faster SQL queries (running a report, showing a graph, importing hands, etc.)

[dad604]

Just a note of caution:

Format SSD's only with quick format and NOT the full format.
Quick format fills it with zero's, full format fills drive with 1's which will really slow the drive down. If you have full formatted it, use Secure Erase for OCZ SSD. You will have to look up program for other drives.