Import Speed Issue

[Bloodninja66]

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]

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

Very informative, Fozzy. Thank you very much for the follow up, I'll give these tricks a shot.

[fozzy71]

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]

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.