PDA

View Full Version : Importing Large Databases



iloveOOv
11-05-2009, 07:01 PM
Hello,

I would like to know if my computer can handle the import of large databases with like 5 million hands and how long does it take to import so many hands.

I have Intel Core 2 Quad Q9400 2,66GHz, 3.25 GB Ram, Windows XP Pro SP3 and a 200 GB Samsung HD.

Moreover I would like to know how Table Scanner does work with such databases, especially by searching for fish regarding table selection.

fozzy71
11-05-2009, 08:08 PM
Import speed depends on many factors and can vary wildly. Assuming its a few years old and a desktop, I would expect you to get 20 - 40 hands per second, on a huge import. I would suggest you break it up into smaller groups of a few hundred thousand hands at a time to see how it goes.

You should also make sure to turn off logging to save hard drive space.


Logging:

I would suggest you turn off logging on your new SQL installation as well as these other performance improvements - 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.


You should regularly purge mined hands in your database, after purge vacuum / full analyze the database via pgadmin.
http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=107
http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=163

iloveOOv
11-06-2009, 07:38 PM
So you say it would be good if I re-Install SQL and HM again before importing the hands or can I make these changes without re-install?

fozzy71
11-06-2009, 09:26 PM
There is no need to reinstall postgresql.

Patvs
11-06-2009, 10:17 PM
Moreover I would like to know how Table Scanner does work with such databases, especially by searching for fish regarding table selection.


With Table Section and HEM there are at least five ways to find fish I can think of:
-1 Table Scanner can find players (using your database) who have an absurdly high VPIP called: #High VPIP
-2 You can use custom filters (both for the entire OVERALL table or individual players) to find a table/player with custom stats you WANT them to have. For example: must be a table with three LOSING players.
-3 You can also use SCORING for this
(-4 in the HEM Table Manager (which opens when you start the IMPORT) you can also see how many fish there are at the table)
(-5 use the buddy filter, just add all bad players you know (for example based on their sharkscope/pokertableratings graphs) as your buddy)

iloveOOv
11-07-2009, 11:33 AM
Thx for the answers.

After speaking with severall people I bought a "SEAGATE Barracuda 7200.12 500GB HDD 7200rpm" yesterday, because I have just 20GB left and I read I need a lot of space for a 10 million database and a fast HDD.

When my paypal is finally activated I will purchase Table Scanner, 5 million hands from pokerHH.com and 5 million from several other people.

I thought about re-installing Holdem Manager and SQL on the new space and safe it just for these programms.
You think that would increase my performance?

Patvs
11-07-2009, 11:43 AM
Every 1 million pokerhands will take up about 10 GB space on your harddisk.

Yes, the new drive will harddisk will be a lot faster. Besides installing PostgreSQL on the new drive, you should also consider just getting rid of the old drive and installing both Holdem Manager AND more importantly Windows (!) on the new drive.

iloveOOv
11-07-2009, 12:21 PM
So what about Windows 7?
My brother bought it a few days ago, but I am not sure if Holdem Manager/Table Scanner work 100% properly at windows 7.

So does the old drive still decrease my performance even when I install Windown,HM and SQL on the new one?
I mean I will use the old one just for MP3s, Pictures and Movies.

fozzy71
11-07-2009, 03:15 PM
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.

Patvs
11-07-2009, 06:43 PM
So does the old drive still decrease my performance even when I install Windown,HM and SQL on the new one?
I mean I will use the old one just for MP3s, Pictures and Movies.


I use HEM + TableScanner + Windows 7 myself (ever since the first Windows 7 beta) and it works great.

No, the old drive will not influence performance if you install everything on the new drive. (I wrote what I wrote, because I thought you wanted to keep Windows on your old drive, and install HEM + Postgres on the new drive, in which case it would effect performance)

iloveOOv
11-08-2009, 05:01 PM
You know some other tweaks, tools or programs to maximize performance?
I use "Advanced System Care", "Auslogics Disk Defrag" and "CCleaner".

iloveOOv
11-08-2009, 05:14 PM
As I already stated I bought a new "SEAGATE Barracuda 7200.12 500GB HDD 7200rpm" link (http://www.amazon.de/SEAGATE-Barracuda-7200-12-7200rpm-NEUWARE/dp/B001VKYA5E), because it was reviewed as one of the best at tomshardware.com and chip.de.
My plan was to install just Windows,HM and SQL on the new Drive and everything else on the old one.

But then I found the Top 10: HEM performance increase tips" Guide and you stated that about HDDs:

"-1 Get a fast harddisk!
---x Preferably two+ SSD harddisks in a RAID-0 array. (fastest)
---x Or just one SSD just for your database
---x Or two Western Digital Velociraptors in RAID-0
---x Or just a really fast SCSI/SAS 10k RPM / 15k RPM harddisk
---x A normal 7200 RPM IDE/SATA harddisk (slowest)"

So I would like to know if u think my new HDD is really a good choice or if I should resend it and buy another one.
I dont want to pay more than 150$ (100€).
I think the WD Velociraptor is very expensive, because a 300GB costs 254$ (170€) and you can only purchase it in a few shops.

Patvs
11-08-2009, 06:01 PM
If you take into account price/performance (and price/GB) the new Seagate Barracuda is great.

As I wrote in the Performance tips topic (http://forums.holdemmanager.com/showthread.php?t=11194&highlight=optimize)

"A Western Digital VelociRaptor used to be easily twice as fast as the best 7200 RPM harddisk. However it currently only is about 10% faster than the latest Seagate Barracudas, Hitachi Deskstars, Western Digital Caviar Blacks and Samsung Spinpoints. And the VelociRaptor does not justify its price difference anymore. So for the best price/performance, a high-end 7200 RPM harddisk is your best choice).... Note: a brand new $60 high-end 2009 model 7200 RPM harddisk will be twice as fast as your 3-4+ yrs old 7200 RPM harddisk!... get a SSD if you can afford it."

Intel has been releasing updates for their X25-M SSD every week. It seems they totally screwed up with their latest firmware update. (it BRICKS a drive if you HAPPEN to have it installed on a Windows 7 - 64 bit system).
So you can wait a year buying a SSD (until they're cheaper, and bigger) unless you're a big computer nerd. (which I am, who just needs the fastest hardware) Note: you only get 80 GB for €200 with a Intel X25-M SSD. The "harddisk list" is only taking into account performance.... not sanity and $/€.

iloveOOv
11-09-2009, 04:34 PM
ahh...thanks for the advice.

iloveOOv
11-12-2009, 11:07 AM
hey,

today I got my new HDD and wanted to re-install Windows and HM.
But I want to save my current hands.

When I go for "Database Management" and then "Export Hands" to what location does HM export all my current hands?
And how can I import them again when I installed HM and Windows on the new HDD again?

fozzy71
11-12-2009, 02:21 PM
hey,

today I got my new HDD and wanted to re-install Windows and HM.
But I want to save my current hands.

When I go for "Database Management" and then "Export Hands" to what location does HM export all my current hands?

After exporting it tells you. ...\Holdem Manager\HandHistoryExport_DatabaseName

Here are my full backup instructions: http://www.holdemmanager.net/forum/showthread.php?t=20754


And how can I import them again when I installed HM and Windows on the new HDD again?

http://forums.holdemmanager.com/showthread.php?t=20731

Import From Folder

mkrump
11-17-2009, 01:36 PM
I'm trying to make the change to postgresql file but keep getting access denied msg. I restarted and I don't see postgresql running in the processes? Running windows 7. Do I need to change permissions somewhere?

fozzy71
11-17-2009, 01:50 PM
Save the edited file to your desktop and then paste it into the \data folder of postgresql.

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.

Add exceptions to the windows firewall, even if it is off. If you have a 3rd party firewall, do the equivalent or uninstall it temporarily - http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=171

The secondary logon service is a standard windows service which is available on every windows computer. In the latest versions it is not started automatically. You need to start it via control panel > administrative tools > services. Starting it once should insure it works in the future also.

mkrump
11-17-2009, 02:04 PM
Srry my question was vague. HEM works fine with windows 7. But copy to desktop then pasting into directory worked. It gives you the option to act as administrator which was not available when i just saved the file. Thanks.