PDA

View Full Version : Connecting two databases to increase import speed?



iloveOOv
11-16-2009, 02:26 PM
Hey,

I want to ask if you can connect/combine 2 or more databases in HM to increase the import speed.
I bought 5 million hands and the daily updates and some people told me after you imported all 5 million the daily updates of 50.000 to 70.000 will last like many hours everytime.
That would really suck.

Ive extra bought a new Seagate Barracuda 500GB, because I think the SCSI and SDDs are still to expensive.
I use a Intel Core 2 Quad Q9400 at 2,66GHz, 4GB Ram and Win XP.

fozzy71
11-16-2009, 04:16 PM
HM doesnt support multiple DB's, and if it did it wouldn't help performance. DB's will naturally get slower as they get bigger. You can connect multiple hard drives in RAID0 to improve disk performance, but this is no simple task. Installing a second/slave drive, and moving your DB to that new drive should improve DB performance.

You should also perform some standard DB performance steps, and perfroma regular maintenenance/purging to maintain performance.


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
and you can delete all files in the postgresql subfolder data\pg_log.

If you plan to have more hands in your database, and fear you might run out of space, you could move the postgresql installation to a different partition/drive:
http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=101

and check the performance tips from the link in my signature.

iloveOOv
11-17-2009, 05:13 PM
hey,

I performed all your tipps and now the import speed is slightly increased, but I am not sure about the Vacuum and Reeindex.

Ive exactly followed all the steps from http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=163 , but I am not sure if I did it right.

When I click "Vacuum Analyze Database" in HM it gives me an error and so I followed your Guide for Manual Vacuum/Reeindex.
But when I click on my Database ("Tim") and "Maintenance" and then Vacuum - [x] Full [x] Analyze I only get this Window after a few seconds.
Then nothing seems to happen and I can only click on "Cancel".

http://img94.imageshack.us/img94/7973/vacuum.jpg

So when do I now its finished and how long would it take for a 6 million DB?

fozzy71
11-17-2009, 09:07 PM
When you hit OK to vacuum the window should start showing some details about the progress of the procedure (the detail window to the right in your pic). If you have never vacuumed it can take quite a while to do it the first time, especially for a 6mm hand DB. I vacuumed a 1mm hand DB earlier tonight for a customer, and it probably took 20 or 30 minutes. You will want to leave it running until the 'OK' button reappears.

See if it helps, if you right-click the pgadmin3 from the start menu and choose 'run as administrator' before doing the vacuum.