PDA

View Full Version : Help my postgreSQL database is 10gig



Dirkvanb
10-26-2009, 12:50 PM
Hey, my postgreSQL database is like 10gig big, ive allready tryde to analye and vacuumize manualy, but the size is still big
Ive only have 300k-500k hands imported.
Is this a normal size for such a database, and is there any way to compress it or make it smaller ?

thanks

fozzy71
10-26-2009, 04:31 PM
First thing to do is turn off logging and delete all the unnecessary log files.



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
(http://208.109.95.123/forum/showthread.php?p=62613&posted=1#post62613)

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.

*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 to clean up all your junk/temporary files. You want to do this before you get to the vacuum and defrag process.



After that, I would next do a full/vacuum/analyze and reindex. Then defrag my whole PC. Make sure you stop the postgresql service before you defrag, so it can properly defrag your DB.

http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=163

Dirkvanb
10-28-2009, 01:27 PM
Thanks!, yeah the log files folder of postgresql was getting pretty big, 8gig lol.

fozzy71
10-28-2009, 04:19 PM
Thanks!, yeah the log files folder of postgresql was getting pretty big, 8gig lol.

Yep. I wish it was off by default, as it is a waste of performance for poker players. People with more important DB's for websites and stuff, need the logs, to trouble shoot. For us its easier to reinstall and reimport. It is usually more like 20 - 30% of your DB size, but I have seen it as high as 80% before. I had a guy with a 33gb DB, that had 26gb of log files. :eek:

Dirkvanb
10-28-2009, 04:33 PM
Yep. I wish it was off by default, as it is a waste of performance for poker players. People with more important DB's for websites and stuff, need the logs, to trouble shoot. For us its easier to reinstall and reimport. It is usually more like 20 - 30% of your DB size, but I have seen it as high as 80% before. I had a guy with a 33gb DB, that had 26gb of log files. :eek:

lol wtf :O
yeah its kinda useless for us pokerplayers, maybe you could put a note to do this when installing postgres, or there might be already and i over read it dunno :D
But im just glad i can keep increasing my database w/o worrying about disk space! :D

fozzy71
10-29-2009, 08:46 PM
lol wtf :O
yeah its kinda useless for us pokerplayers, maybe you could put a note to do this when installing postgres, or there might be already and i over read it dunno :D
But im just glad i can keep increasing my database w/o worrying about disk space! :D

We are working on our own postgresql installer which will hide that postgres windows user login and also turn off the logging by default. ;)