PDA

View Full Version : 15 gigs for POSTSQL right?



4entourage
07-07-2009, 09:25 PM
My C:\Program Files\PostgreSQL\8.3 folder is 15.8 gigs for ~ 20,000 people. Does this seem right? Does Vacuum make this smaller? (Im afraid to Vacuum because I dont want to lose hand replays or anything....)

Just didnt realize it got this big - When I extract all hands to my hard drive for the same players it only comes out to like 1.17 Gigs.....

fozzy71
07-07-2009, 11:22 PM
Do you know how many hands are in your DB?

Open pgAdmin, doubleclick the "localhost server" so the red X goes away. Select your HM database in the treeview.

Do "Tools -> Query Tool" (or click the "SQL" icon in the toolbar).

enter this in the top section of the query window:


SELECT COUNT(pokerhand_id) as hands FROM handhistories;

Then do "Query -> Execute" (or click the "play" icon, or hit F5).
It will grind the disk for a little then spit out a number in the lower pane of the query tool if all has gone well




You probably have a fair amount of space being taken up by (useless) log files. I have 60k players, ~235k hands, and my DB is 2.2gb. To see how much of yours is log files, do this:

Start > My Computer > D:\Program Files\PostgreSQL\8.3\data\, Right-Click > Properties on the \pg_log folder. You can go in there and delete the entire contents of (only) the \pg_log folder.

I helped a customer a last week with a 21gb DB, and 18gb of it was log files. :eek:


I would suggest you turn off logging on any new SQL installation as well as these other performance improvements, particularly the SQL Tuning Wizard which configures SQL for your machines spec's. - 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.

4entourage
07-27-2009, 06:23 PM
Does deleting the log files make me lose any hand replays/statistics/ANYTHING really? I use the hand replayer a lot - and dont want to lose statistics on anyone!

Thanks a lot. Awaiting your reply!

fozzy71
07-27-2009, 06:52 PM
No. They are logs about functions of postgresql, so that if there were problems a SQL geek could read the logs to try and solve problems. For me, it's easier to reinstall and reimport than worry about why my SQL DB got corrupted, especially when weighed against all the space and performance they waste. HM has it's own logging feature, but it is off by default because it uses extra CPU power, etc. I once helped a customer that had a 21GB DB. 18GB of it was in the \pg_log folder. :p

4entourage
07-27-2009, 07:35 PM
No. They are logs about functions of postgresql, so that if there were problems a SQL geek could read the logs to try and solve problems. For me, it's easier to reinstall and reimport than worry about why my SQL DB got corrupted, especially when weighed against all the space and performance they waste. HM has it's own logging feature, but it is off by default because it uses extra CPU power, etc. I once helped a customer that had a 21GB DB. 18GB of it was in the \pg_log folder. :p


Ya I have 1.8 million hands on this database. (Different on another computers)
But my PostgreSQL folder is 20.4 gigs -- this folder is 11.3 of the gigs...
So will deletings this speed up HEM at all?

fozzy71
07-27-2009, 08:14 PM
It certainly won't slow it down. ;) It does help, but how much I can't say really. I have had people tell me they see noticeable improvements after the delete logs, vacuum, defrag, and do the SQL Tuning Wizard. The most important reason to get rid of the logs, in addition to possible performance improvements, is that they are unnecessary and wasting 10gb of your hard drive. Make sure you do a full vacuum/analyze, reindex, turn off the sql service, and defrag after deleting all those logs. Make sure you check out the other performance suggestions in that thread I linked.

4entourage
08-03-2009, 04:48 PM
You said to figure out my total hands I put
" SELECT COUNT(pokerhand_id) as hands FROM handhistories; "

For "(pokerhand_id) do I put my alias -- or do I leave that line exactly as it is?

ffrllc
08-03-2009, 07:27 PM
Leave it exactly as is