View Full Version : Import Speed Issue
Bloodninja66
06-26-2009, 12:11 AM
Lately I have been experiencing fairly slow speeds (appx 25 hands/sec) when Importing large batching of hands from Pokerstars.
I am not very familiar with databases, so I don't really know what could be affecting the import performance. My typical speed for these batch imports has been around 100 hands/sec in the past.
Any ideas?
Running quad core processor, 6 gig tri-channel ram, 64 bit OS.
fozzy71
06-26-2009, 12:36 AM
- I would start by turning off the logging in your SQL and deleting all the logs that are wasting space:
*If you want some help with this part let me know*
- 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 Off to On.
------------------------------------------------------------------------------
# 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.
*edit:
- 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 (http://www.ccleaner.com) to clean up all your junk/temporary files. You want to do this before you get to the vacuum and defrag process.
************************************************** *****************
- Vacuum:
http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=60
- If you get an error, then vacuum manually:
http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=163
************************************************** *****************
- You can follow this FAQ for other ways to make HEM run faster. One of the most important steps is the defrag. I personally use Defraggler (http://www.defraggler.com/) on my PC. Make sure you Stop the PostgreSQL service before you defrag, and Start it back up after, so it will defrag properly.
http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=162
************************************************** *****************
- You can also use the SQL Tuning Wizard :
http://www.holdemmanager.net/faq/afmmain.aspx?faqid=172
*****************************
Bloodninja66
06-26-2009, 01:39 AM
Very informative, Fozzy. Thank you very much for the follow up, I'll give these tricks a shot.
fozzy71
06-26-2009, 01:48 AM
I did a remote support for a guy the other day that had PostgreSQL problems and we decided we needed to export his DB to text files, uninstall PostgreSQL, Re-Install SQL, and import the exported text files. I went into his pg_log directory to see how much of the 21 GB was taken up by log files......
.........
......
...
.
18 GB. :eek:
Needless to say the first thing we did after re-installing SQL was turn off logging, and reload the configuration file, before he re-imported his text files. :cool:
fozzy71
06-26-2009, 10:59 AM
I forgot to include the step to actually delete the log files before you get to the vaccuum and defrag process. I edited my original post to include it. Here it is again:
- 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 (http://www.ccleaner.com) to clean up all your junk/temporary files. You want to do this before you get to the vacuum and defrag process.
Powered by vBulletin® Version 4.2.3 Copyright © 2024 vBulletin Solutions, Inc. All rights reserved.