PDA

View Full Version : database size



runhot
01-22-2010, 01:16 PM
is there a way to tell the size of a database, and is there a suggested limit to the size you should keep a database under?

fozzy71
01-22-2010, 01:39 PM
If you only have 1 DB your \Postgresql\8.x\data folder size will tell you.

If you want to know the total hands in the DB - http://faq.holdemmanager.com/questions/115/Check+Total+Amount+of+Hands+in+Database+

Technically only limited by your hard drive space and PC performance. The bigger the DB the slower the import speeds will become, so regular maintenance is important to minimize the impact of that. I also recommend you be sure logging is turned off and all log files are deleted. We have many customers with multi-million hand DB's, so for most users there is no need to use multiple DB's.

runhot
01-22-2010, 02:14 PM
my DB size is approaching 80gigs and aproaching 10mm hands....

by maintainence, do you mean doing this:
http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+#manual

if so, how frequently should i do it? if you mean something else, what?

regarding logging- what exactly do you mean by this? also, which log files should i delete and where can i find them?

fozzy71
01-22-2010, 02:33 PM
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.



Regarding database maintenance:

You should regularly purge mined hands in your database, after purge vacuum / full analyze the database via pgadmin.
http://faq.holdemmanager.com/questions/100/Purge+Hands+from+Database
http://faq.holdemmanager.com/questions/197/Purge+Hands

Then you can vacuum/analyze - http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+#manual

Before you defragment you should stop the PostgreSQL service:

Start > Programs > PostgreSQL 8.x > Stop Server

Use your favorite defrag tool. I recommend Smart Defrag.

After defrag I recommend you reboot your PC. If you need to access the DB before rebooting you can start eh PostgreSQL service.

Start > Programs > PostgreSQL 8.x > Start Server