Tips for Importing Large Databases?

[cyberrico]

I have a brand new computer with 8GB RAM, a solid state drive and a fast processor on Windows 7 64 bit. I am installing everything from scratch.

I have 5 million hands to import. I have read various threads about changing values in the config file in postgresql, UAC off, firewall off, run as admin, importing small amounts at a time, etc etc etc but I can't get faster speeds than 7 hands per second. I know for a fact that this is very slow. My old computer imported this database in 4 days and on this new computer it will take me a month.

I am willing to start from scratch especially since I just installed it all.

I will be running a tight ship on this computer. I will backup regularly by making regular disk images so that I don't have to do this again but eventually it will be inevitable. In 2-3 years I will have to buy a new system and I will be doing this all over again. I will be obtaining large amounts of data again as I move up so something has to give.

My BB/100 is zero while this is happening. Please help.

[fozzy71]

You are only getting 7 h/s when using the Import From Folder option? Laptop or desktop? Please do not use the Auto Import for bulk imports. It is significantly slower than the bulk import methods.

My 2 month old computer (Ci7 920, 9gb RAM) gets upwards of a 100 h/s when importing to a new DB.

Under Windows 7 you must set \holdem manager\dbcontrolpanel.exe, holdemmanager.exe and hmhud.exe to be started as administrator. Run the windows explorer and navigate to your Holdemmanager installation directory. Right click the executable's and select "Run as admin" from the compatibility tab.

Which Firewall, and Anti-Virus are you using?

[cyberrico]

You are only getting 7 h/s when using the Import From Folder option? Laptop or desktop? Please do not use the Auto Import for bulk imports. It is significantly slower than the bulk import methods.

My 2 month old computer (Ci7 920, 9gb RAM) gets upwards of a 100 h/s when importing to a new DB.

Under Windows 7 you must set \holdem manager\dbcontrolpanel.exe, holdemmanager.exe and hmhud.exe to be started as administrator. Run the windows explorer and navigate to your Holdemmanager installation directory. Right click the executable's and select "Run as admin" from the compatibility tab.

Which Firewall, and Anti-Virus are you using?

Admin is set properly on all of the executables. Are there postgres exe's that benefit from this too?

AV was AVG. I uninstalled it and rebooted. No change.

I run import from folder and point it to a folder that is also on the solid state drive as are all of my apps in this environment for now. I've also tried importing files one at a time. Same thing.

I made the alterations to postgres as recommended in 2+2:

1.) Find your postgresql.conf file and open in notepad. Mine was in C:\Program Files\PostgreSQL\8.2\data

2.) Find the 'shared_buffers' value. The default value is comically low, but a good value is 1/4 of your total memory. I set mine to 768MB as I have 4GB of RAM (of which a little more than 3GB actually can be used in non 64-bit systems).

3.) Find the 'effective_cache_size' value. The default for this is also very low, but can be set to upward of 1/2 of your total memory. I set mine to 1536MB.

4.) Find the 'redirect_stderr' value and change it to 'off', unless you have a specific reason for looking at log files. If you're like me, the HEM queries result it tons of log data being written out. I had 10MB being written out every 20 minutes. Not only does this eat up tons of disk space, but this will cause your system to be wasting time writing to disk for logs you'll likely never look at.

5.) Restart either your computer or the PostgreSQL process from Start -> Control Panel -> Administrative Tools -> Services.

Every time I do this I get a "cannot connect" error in HEM and have to reinstall postgres. I made my adjustments according to the percentage of RAM as suggested by the author of that post and as I type this I am using his exact values despite the fact that I have twice his RAM.

Rebooting and trying again.

[cyberrico]

No change. It started off at 34/s, is down to 23 after 16K hands (16 files) and is steadily declining. It says 2.5 days which sucks but I would suffer through, but I am sure it will degrade to 7/s and will take close to a week.

[_Loki_]

Hi cyberrico & fozzy - I hope it is OK to jump in...

cyberrico thank you for posting those 2+2 tips here

I've just tried those alterations to postgres as shown in your 2+2 quote for postgresql 8.2 (BTW I'm on 8.4)

BUT...

4.) Find the 'redirect_stderr' value and change it to 'off'

I couldn't see a value called "redirect_stderr" & I wonder if this value doesn't exist in 8.4 ?

INSTEAD...
In the ERROR REPORTING AND LOGGING section I have previously changed the value for "logging_collector" from on to off as suggested to me by Fozzy:

#------------------------------------------------------------------------------
# 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

Is this the equivalent step ?

Every time I do this I get a "cannot connect" error in HEM and have to reinstall postgres

I wonder why you have to reinstall postgres ?
I made sure my HM & postgre were not running.
I opened the config file in notepad
I changed the memory values
Saved them
Closed notepad
Rebooted my laptop & all is well - no reinstall of postgres needed

*** NOW THE BELOW POINT IS PURE SPECULATION - I HAVE NO PRACTICAL KNOWLEDGE TO BACK IT UP:

I have been researching SSD's with a view to buying one to make my laptop more rugged. I've decided to not bother because I want to wait until SSD's are proven absolutely...

I posted here recently that I've read that some current SSD's do not like being written to in huge batches - the performance drops very very quickly

The reason (I think) is to do with the firmware on some current SSD's & conventional defragging optimised for normal HDD's can make this situation worse

I'm not sure this helps you

[cyberrico]

Loki,

I did the same thing for logging. I'm pretty sure it's correct. I followed the same steps you did. For whatever reason my system doesn't seem to like the larger values so I changed them to what the author of that post did which is a big difference from the default and there was no change for me at all.

[fozzy71]

An inexpensive, possible solution, might be to get a new hard drive to use specifically for your Postgresql\data folder.

[_Loki_]

I couldn't see a value called "redirect_stderr" & I wonder if this value doesn't exist in 8.4 ?

Cyberrico are you on 8.2 ?

[cyberrico]

I couldn't see a value called "redirect_stderr" & I wonder if this value doesn't exist in 8.4 ?

Cyberrico are you on 8.2 ?

No I'm 8.4 and you're right it's not there and the only similar entry is already off.

[cyberrico]

An inexpensive, possible solution, might be to get a new hard drive to use specifically for your Postgresql\data folder.

I have plenty of drives. Should it be on its own physical drive or will a partition work? I have two issues with this though. 1) I wanted to take advantage of the speed of my SSD (not that it's so amazing for the DB so far) and 2) backing up a disk image would require backing two drives up in tandem and I have to make sure that both backups are always grouped together.