Over the past couple of weeks I have upgraded my system and experimented with various settings in the postgresql.conf file. The first two posts in this thread detail the hardware used, procedures employed, values tested, the results obtained, and finally the observations I have made along the way. A lot of this first post is a bit bloated, but it was easier to simply transfer all of my notes.
You may just want to skip most of the bottom of this post and concentrate on the salient parts in the second post. Beyond these posts, I'd like to read your input and results that you have obtained in your own testing. I think that just a little more active involvement of the community of PokerTracker 3 users can lead to making this software perfom better and get ever closer to its full potential.
Please understand that while the final settings I used for my system worked well for me; these settings for the postgresql.conf file may not work for you at all. Rather than looking at these posts as a specific how to, think of them as a guideline for one possible approach to finetune your own setup and to maybe give you the impetus to enact some hardware changes and testing of your own.
I also felt that sharing my results may give some of you an insight into what is possible (performance wise) with PokerTracker 3 right now. While my completed system is nothing you can buy at your local electronics store or simply order online, all of the hardware used is of the shelf and easily available.
Other than my SSD drives all of my hardware is almost a year old, including the MegaRAID controller that I bought used on eBay at 1/4 of the new price. None of the components are exotic or high end, with the possible exception of the SSD drives. My CPU for example currently costs about $180. It is true that the combined costs of the components in my system exceed the cost of two high end store-bought systems. However, almost half of that is in the four SSDs alone. But when you gauge the performance achieved, and consider the value of the time saved by using such a setup, you may find the added expense quite reasonable and a worthwile investment.
As for building your own computer; it's fairly simple and at least to me is always a very satisfying experience (typically after some initial frustration and cursing). If you have never built your own computer, just do some research first, or ask a friend for some advice and/ or help.
The two initial posts are:
I.) Testing Notes, Preliminary Testing Log (This Post)
II.) Final Testing, Results, and Observations
--------------------------------------------------------------------------------------------------------
-- T E S T I N G -- N O T E S :--------------------------------------------------------------------------------------------------------
All Tests performed on HAL-MMX:------------------------------------08/13-20/2010 by MV
FoxConn Destroyer MB, AMD Phenom II x4 Deneb Black Edition 965 CPU (Quad-Core, AM3),
8GB OCZ Reaper RAM, CPU & RAM @ Defaults [3.4GHz, 1.0V | DDR2-AUTO, 1.8V, 5-5-5-12]
[Motherboard has nVIDIA onboard graphics + 4 PCIE x16 slots: The LSI MegaRAID card
is in slot #4, other slots used by 3 XFX GTS 250 1GB nVIDIA based graphics cards.
7 monitors (20"-28") are used (a 22" switchable to my notebook), plus 1 HDTV (#8)]
Boot Drive (C:\): 4 OCZ Vertex 2 SSDs, Controller: LSI MegaRAID 8888ELP 512MB Cache,
in RAID 0 Array (Direct I/O - No Disk Cache - Write Through - 577MB/s Tested Speed)
Secondary Drive (K:\): 2 WD VelociRaptor 10k RpM HDDs in RAID 0 Array, controlled by
nVIDIA 780a SLI (Chipset on MB). 191MB/s Tested Speed (Crystal DiskMark 3.64)
Copying (K:\) > (C:\) works at 11.2GB/Min. A 128GB Database transfers in 11.5 Mins
OS: Windows 7 (64-bit) Ultimate; PostgreSQL Version: 8.4.4; PT3 Version: 3.07.2 Beta
Files used for imports with equal structures and similar sizes (30kH-75kH) per run.
[Tests performed before Windows 7 service structure cleanup & tossing out the trash.]
PokerTracker3 Startup Times:-----11.00 Seconds from Icon click to running mode, i.e.
Holdem/Cash/General Tabs open, with 25.8k Players displayed in 354k Hand database.
20.50 Seconds from Icon click to running mode, i.e. Holdem/Cash/General Tabs Open,
with 82.4k Players displayed in 7.06M Hand database.
--------------------------------------------------------------------------------------------------------
PSQL Wiki Article at: http://wiki.postgresql.org/wiki/Tuning_ ... SQL_Server
Tests/ Changes performed here based on this Article & Modifications by Tuning Wizard.
FILE MODIFIED BY TUNING WIZARD (Ded. Server) DID NOT WORK ! (Server would not start.)
Probable Cause: PostgreSQL 8.4.4. was unable to understand the Memory Units inserted
by Tuning Wizard. "The Service did not report an error . . see . . NET HELPMSG 3534."
Tuning Wizard File CORRECTED and re-tested afterwards (see >>> Tests for results).
--------------------------------------------------------------------------------------------------------
--T E S T I N G -- L O G :-----------------------------[Server Restarted for each Test]--------------------------------------------------------------------------------------------------------
PRELIMINARY TESTS
08/13/2010 First Baseline Tests with Customized File (postgresql.conf):
shared_buffers = 512MB-----work_mem = 256MB-----maintenance_work_mem = 512MB
wal_buffers, checkpoint_segments, autovacuum, effective_cache - all at default values
Configuration works at ~420H/s (K:\) | ~408H/s (C:\) - NOTE: MAIN DRIVE IS SLOWER !
shared_buffers = 1024MB-----work_mem = 256MB-----maintenance_work_mem = 512MB
wal_buffers, checkpoint_segments, autovacuum, effective_cache - all at default values
Configuration runs @ ~140H/s (K:\) Imp. Speed, with VACUUM ERROR after Housekeeping.
[NEWER FILES (07/08/2010+): Structure must be different somehow! - INVESTIGATE]
shared_buffers = 512MB-----work_mem = 256MB-----maintenance_work_mem = 512MB
wal_buffers, checkpoint_segments, autovacuum, effective_cache - at default values
Configuration works at ~153H/s (K:\) | ~148H/s (C:\) Improved Speeds, NO ERRORS
Settings for shared_buffers above 512MB apparently cause problems. Also, PSQL Wiki
refers to 512MB as upper useful limit in Windows OS. [OTHER THAN THREE BLANK LINES
SEPARATING HANDS IN THE NEWER FILES COMPARED TO ONE LINE IN OLD FILES AND MORE
HANDS PER FILE (Same relation of size in MB to number of actual hands.), THERE IS NO
OTHER APPARENT DIFFERENCE BETWEEN OLDER AND NEWER FILES, THIS TEST SEEMS TO
CONFIRM THAT A PROBLEM WITH THE NEWER FILES MAY EXIST. FURTHER INVESTIGATION
WILL BE PERFORMED UPON COMPLETION OF ALL TESTS. MEANWHILE ONLY FILES MADE
BEFORE 07/08/2010 WILL BE USED. COULD THE TWO ADDITIONAL BLANK LINES PER HAND
CAUSE SUCH A DRAMATIC DIFFERENCE?]
As Above: w. wal_buffers = 1MB [4x Tuning Wizard (Ded.) Value | 16x Default Value]
Configuration runs @ ~475H/s (K:\) | ~470H/s (C:\) Imp. Speeds, NO ERRORS; This run
with an older file appears to prove the point made above, as it imported 275% faster
than the two previous tests. However, compared to Baseline Test, the wal_buffers may
be responsible for a 13% Gain. Further tests with wal_buffers to follow.
As Above: w. wal_buffers = 4MB [16x Tuning Wizard (Ded.) Value | 64x Default Value]
Configuration runs @ ~491H/s (K:\) Imp. Speed, wal_buffers responsible for 17% Gain.
As Above: w. wal_buffers = 16MB [64x Tuning Wizard (Ded.) Value | 256x Default Value]
AND temp_buffers at 32MB [4x Default Value], Maint. Run 13:10
Configuration runs @ ~483H/s (K:\) Import Speed, No Additional Gain.
As Above: w. wal_buffers = 16MB [64x Tuning Wizard (Ded.) Value | 256x Default Value]
AND temp_buffers at 32MB [4x Default Value], Maint. Run 13:15
Configuration runs @ ~457H/s (K:\) Import Speed, SLOWER/ reducing wal_buffers to 8MB.
As Above: w. wal_buffers = 8MB [32x Tuning Wizard (Ded.) Value | 128x Default Value]
PLUS: maintenance_work_mem = 1024MB [2x previous tests], Maint. Run ~13.5 Min.
Configuration runs @ ~476H/s (K:\) Imp. Speed, No Gain, Rolling back to prev. value.
As Above: w. effective_cache_size = 1024MB [~2x Tuning Wizard (Ded.) | 8x Default]
Configuration runs @ ~477H/s (K:\) Import Speed, No Gain, Maint. Run 13:00
As Above: w. effective_cache_size = 2048MB [~4x Tuning Wizard (Ded.) | 16x Default]
Configuration runs @ ~477H/s (K:\) Imp.Speed, No Gain, Maint. Run 12:50; Slight Gain
As Above: with wal_sync_method = open_sync [per PostgreSQL Wiki]
SERVER WILL NOT RESTART - REVERTED TO DEFAULT
As Above: w. checkpoint_segments = 64 [= Tuning Wizard (Ded.) | 21x Default (3)]
Configuration runs @ ~449H/s (K:\) Import Speed, 6% Slower, Maint. Run 13:05
As Above: w. checkpoint_segments = 32 [= 1/2 Tuning Wizard (Ded.) | 10x Default (3)]
AND checkpoint_completion_target = 0.9 [Per PostgreSQL Tuning Wiki; Default is 0.5]
Config. runs @ ~485H/s (K:\) Imp. Speed, 3% Faster, Maint. Run 16:40 - 27% SLOWER?
Effect of the increasing size of database? (Re-try prev. settings +Larger Eff. Cache)
As Above: w. wal_buffers = 12MB [48x Tuning Wizard (Ded.) Value | 192x Default Value]
AND checkpoint_segments = 24 [= 1/3 Tuning Wizard (Dedicated) | 8x Default (3)]
AND checkpoint_completion_target = 0.7 [Per PostgreSQL Tuning Wiki | Default is 0.5]
Configuration works at ~483H/s (K:\), Maint. Run 16:12 - Slightly Faster
PURGE RUN of 131 Sessions (G2): 8:50; Vacuum, Analyze & Rebuild Cache Run 45:55
Database (Observed) with 1.36M Hands after Purge.
As Above: with checkpoint_completion_target = 0.5 [Default]
Configuration runs @ ~456H/s (K:\) Import Speed, Maint. Run 18:15
Re-tried with checkpoint_segments = 64; checkpoint_completion_target = Default [0.5]
Configuration works at ~489H/s (K:\) Import Speed, Maint. Run 17:00 - Slightly Faster
Re-tried w. checkpoint_segments @ Default & checkpoint_completion_target @ Default
Configuration works at ~477H/s (K:\) Import Speed, Maint. Run 18:30 - Slower Again
As Above: w. checkpoint_segments = 128; checkpoint_completion_target = Default [0.5]
Configuration runs @ ~472H/s (K:\) Imp. Speed (files had 24 errors), Maint. Run 17:25
As Above: w. checkpoint_segments = 128; checkpt_completion_target = 0.7 [Def. = 0.5]
Configuration runs @ ~503H/s (K:\) Import Speed (files w. 9 errors), Maint. Run 17:35
As Above: w. checkpoint_segments = 128; checkpt_completion_target = 0.9 [Def. = 0.5]
Configuration runs @ ~497H/s (K:\) Imp. Speed (files w. 10 errors), Maint. Run 18:15
--------------------------------------------------------------------------------------------------------
PRELIM. TESTS W. CORRECTED TUNING WIZARD (Dedicated) FILE (All Tests Marked >>>)
>>> Corrections Made: Converted TUNING WIZARD's memory values to closest MB figures.
>>>
>>> shared_buffers = 152MB, work_mem = 42MB, maintenance_work_mem = 256MB
>>> effective_cache_size = 500MB, checkpoint_segments = 64, random_page_cost = 3.5
>>> autov_vacuum_threshold = 1000, autov_analyze_threshold = 250 [8.4.4 Defaults]
>1> Config. runs @ ~480H/s (K:\) Imp. Speed (files had 1 error), Maint. Run 15:45
>>> Import Speed Slightly Slower (4%) >>> BUT MAINT. RUN IS SHORTER BY 14% !
>2> Config. runs @ ~522H/s (K:\) Import Speed (files had 10 errors), Maint. Run 18:00
>>> Import Speed 10% FASTER AND MAINT. RUN IS SLIGHTLY SHORTER (2.7%)
>3> Config. runs @ ~525H/s (K:\) Import Speed (files had 7 errors), Maint. Run 19:10
>>> Import Speed Slightly Faster(1%) >>> BUT MAINT. RUN IS SLOWER BY 5%
>>> TRY random_page_cost modification first, then re-try different memory settings.
--------------------------------------------------------------------------------------------------------
BASED ON TESTING W. TUN-WIZ FILE: 152MB/42MB/256MB/500MB vs.512MB/256MB/512MB/2048MB
TESTS BELOW W. MEMORY VALUES HIGHER THAN TUN-WIZ's, BUT DIFFERENT FROM PREV. VALUES:
sh_buff = 456MB, wk_mem = 128MB, maint_work_mem = 768MB, eff._cache = 1536MB [TW x 3]
Config. runs @ ~526H/s (K:\) Import Speed (files had 5 errors), Maint. Run 14:30
VAST IMPROVEMENT IN MAINT. >> BUT RETURNS VACUUM ERROR ON COMPLETION OF HOUSEKEEPING.
(Improvement is probably the result of vacuum function being skipped.)
TEST RE-RUN W. REDUCTION IN maint_work_mem: 512MB APPEARS TO BE UPPER USEABLE LIMIT !
shared_buffers = 512MB, work_mem = 128MB, maint_work_mem = 512MB, eff._cache = 1536MB
Configuration runs @ ~510H/s (K:\) Imp. Speed (files had 37 errors), Maint. Run 18:30
2nd TEST RE-RUN WITH REDUCTION IN maintenance_work_mem sh._buffers = 512MB,
work_mem = 128MB, maint._work_mem = 512MB, eff._cache = 1536MB
Config. works at ~501H/s (K:\) Import Speed (files had 11 errors), Maint. Run 18:00
TEST BELOW WITH MEMORY VALUES ~2.5 x TUNING WIZARD's NUMBERS (except maint._work_mem)
sh._buffers = 384MB, work_mem = 96MB, maint._work_mem = 512MB, eff._cache = 1280MB
Config. runs at ~500H/s (K:\) Import Speed (files had 9 errors), Maint. Run 17:15
>>> Test >3> (TW Corrected) from above rerun for verification at this point. OK
---------------------------------------------------------------------------------------------------------
PURGED 56 Sessions (G2) w. Test >3> Settings: 8:48; Vac., An. & Reb. Cache - 1:00:53
Database (Observed): 1.83M Hands after Purge. CORRECTED TUNING WIZARD SPEEDS LOOK OK
--------------------------------------------------------------------------------------------------------
08/16/2010 RE-RUN TEST W. BEST RESULTS SO FAR TO CONFIRM VACUUM ERROR OCCURS AGAIN.
sh._buff = 512MB, wk_mem = 128MB, mt_work_mem = 768MB, eff._cache = 1536MB [TW x 3]
CONFIGURATION WILL NOT RESTART (No Error - As Before) - SETTINGS CHANGED TO:
shared_buffers = 512MB, work_mem = 96MB, maint_work_mem = 640MB, eff._cache = 1408MB
CONFIGURATION STILL DOES NOT RESTART (Still No Error) - SETTINGS CHANGED TO:
shared_buffers = 512MB, work_mem = 96MB, maint_work_mem = 512MB, eff._cache = 1280MB
CONFIGURATION STILL DOES NOT RESTART ! RUN FURTHER TESTS ON TW FILE, THEN FIND CAUSE.
--------------------------------------------------------------------------------------------------------
>4> 08/16/2010 Tuning Wizard Conf. File Retested to obtain current comparison values.
>>> Config. runs @ ~457H/s (K:\) Import Speed (files had 13 errors), Maint. Run 27:01
>>> Import Speed 10% Slower Than Yesterday. MAINTENANCE RUN TIMES STILL INCREASING.
>>> Machine is running 61 services, investigate which addl. services are running.
>>> (Probably Maintenance Tasks. Find & shut them down to obtain valid test numbers.)
>>>
>5> Tuning Wizard Conf. Tested w. 3x Memory Values: 384/128/768/1536 +1MB wal_buffers
>>> ATTEMPT TO CREATE THE SAME ERROR THAT OCCURS USING THIS FILE AT SAME SETTINGS.
>>> Config. works at ~482H/s (K:\) Import Speed (files w. 9 errors), Maint. Run 14:30
>>> VACUUM ERROR !!! Import Speed 5.5% BETTER THAN >4>. MAINT. RUN IS SHORTER BY 40%
>>>
>6> Config. >5> with Lower maint_work_mem Value: 384/128/512/1536 + 1MB wal_buffers
>>> Config. runs @ at ~498H/s (K:\) Imp. Speed (files had 7 errors), Maint. Run 19:30
>>> NO ERRORS !!! Import Speed 3% BETTER THAN >5>. MAINTENANCE RUN OK
>>> TESTS >5> & >6> CONFIRM THAT SETTING maint_work_mem ABOVE 512MB RESULTS IN ERROR
>>>
>7> Configuration >5> with increased wal_buffers: 384/128/512/1536 + 4MB wal_buffers
>>> Config. runs @ ~481H/s (K:\) Imp. Speed (files had 16 errors), Maint. Run 19:31
>>> NO ERRORS - Import Speed same as >5>. Maintenance Run = >6>
>B> Second Run: ~509H/s (K:\) Import Speed (files had 11 errors), Maint. Run 19:40
>>>
>8> Further increase to 8MB in wal_buffers: 512/128/512/1536 + 24MB temp_buffers
WOW Config. works at ~594H/s (K:\) Imp. Speed (files w. 14 errors), Maint. Run 20:30
>B> Second Run: ~561H/s (K:\) Import Speed (files had 9 errors), Maint. Run 20:30
>>> Apparently adding a decent amount of temp_buffers (wal x3) in addition to upping
>>> the amount of wal_buffers themselves works like a charm ! (~Avg. % Gain !)
##################################################################################
#--Based on PT3 Forum Post by kraada 06/19/2010, and email confirmation by Derek on-- #
#--08/17/2010 Cluster Function will be skipped from now on. (Not needed on SSDs.)------ #
##################################################################################
>9> Config. >8> increased wal_buffers to 12MB: 512/128/512/1536 + 32MB temp_buffers
>>> Config. works at ~547H/s (K:\) Imp. Speed (files had 5 errors), Maint. Run 7:35!
>B> Second Run: ~562H/s (K:\) Import Speed (files had x errors), Maint. Run 6:45!
>>> No Speed Gain vs. Test >8>, Database Now Contains 2.2 M Hands.
>>>
>10 wal_buffers red. by 2MB: 512/128/512/1536 + 10MB wal_buffers + 30MB temp_buffers
>>> Config. runs @ ~536H/s (K:\) Imp. Speed (files had 11 errors), Maint. Run 7:03
>B> Second Run: ~531H/s (K:\) Import Speed (files had 14 errors), Maint. Run 7:35
>>>
>>> PER POSTGRESQL WIKI: Upper useful limit of wal_buffers is 16MB = Next Tests
>>>
>11 512/128/512/2048 + 16MB wal_buffers + 32MB temp_buffers
>>> Config. runs @ at ~537H/s (K:\) Imp. Speed (files w. 15 errors), Maint. Run 6:40
>B> Second Run: ~531H/s (K:\) Import Speed (files had 13 errors), Maint. Run 6:20
>>> NO APPARENT SPEED GAIN (First Test Peaked at 614H/s) - Back to 8MB wal_buffers
--------------------------------------------------------------------------------------------------------
TEST SERIES BELOW: Performed w. HIGH LIMIT ($30/60+) hands, may make a difference in
that shorter hands/ no flop hands, take less space & should import at a faster rate.
>81 Config. RE-TESTED with 8MB wal_buffers: 512/128/512/2048 + 24MB temp_buffers
WOW Config runs @ ~675 H/s (K:\) Import Speed (files had 10 errors), Maint. Run 6:15
>B> Second Run: ~676H/s (K:\) Import Speed (files had 7 errors), Maint. Run 6:50
>C> Third Run: ~662H/s (K:\) Import Speed (files had 18 errors), Maint. Run 6:35
>>> PEAK IMPORT SPEED AT 948H/s ! ! !
>>>
>>> TESTS BELOW: Config. >81 with fsync_writethrough as synchronous commit setting
>>> Config. runs @ ~656H/s (K:\)Import Speed (files w. 11 errors), Maint. Run 7:30
>B> Second Run: ~629H/s (K:\) Import Speed (files had 5 errors), Maint. Run 6:35
>>> NO APPARENT SPEED GAIN - Rolling Back to previous synchronous commit setting.
>>>
>>> TESTS BELOW: Configuration >81 w. max_stack_depth increased to 8MB [Default x4]
>>> SERVER WILL NOT RESTART (No Error)
>>> ---> trying max_stack_depth increased to 4MB [Default x2]
>>> SERVER WILL NOT RESTART (No Error)
>>> ---> trying max_stack_depth reduced to only 1MB [1/2 Default]
>>> Config. runs @ ~597H/s (K:\) Import Speed (files had 2 errors), Maint. Run 4:20
>>>
>>> ---> trying max_stack_depth increased to 3MB [Default +50%]
>>> Config. works at ~650H/s (K:\) Imp. Speed (files had 8 errors), Maint. Run 6:25
>>> Second Run: ~610H/s (K:\) Import Speed (files had 27 errors), Maint. Run 7:00
>>>
>>> RESET max_stack_depth to 2MB [Default] for Comparison Test with same conditions.
>>> Config. works at ~655H/s (K:\) Imp. Speed (files had 13 errors), Maint. Run 5:40
>>> Second Run: ~646H/s (K:\) Import Speed (files had 2 errors), Maint. Run 4:00
>>> ---> LEAVE max_stack_depth at 2MB [Default]
--------------------------------------------------------------------------------------------------------
Now Using $6NL Files, Next 2 Tests w. above Config. (>81) to establish new baseline.
>>> Config. runs @ ~554H/s (K:\) Imp. Speed (files had 3 errors), Maint. Run 8:46
>>> Second Run: ~552H/s (K:\) Import Speed (files had 11 errors), Maint. Run 8:00
>>>
>>> ---> trying max_prepared_transactions increased to 10 [Default x2]
>>> Config. works at ~542H/s (K:\) Imp. Speed (files had 8 errors), Maint. Run 9:10
>B> Second Run: ~545H/s (K:\) Import Speed (files had 6 errors), Maint. Run 9:22
>>> Appears to have no significant Impact (2% slower), reverting to Default Setting.
>>>
>>> ---> TURNED OFF LOGGING
>>> Config. works at ~541H/s (K:\) Import Speed (files had 6 errors), Maint. Run 8:40
>B> Second Run: ~543H/s (K:\) Import Speed (files had 5 errors), Maint. Run 8:28
>>> NO GAIN FROM TURNING OFF LOGGING > > Logging Re-Enabled
PURGE of 71 Sessions (G2) [>8> Settings]: 39:15; Vac., Analyze & Reb Cache - 3:04:09
Database (Observed) has 3.45M Hands after Purge.
--------------------------------------------------------------------------------------------------------
COMPARISION OF TUNING WIZARD (Modified) & THIS FILE--------------------(08/18/2010)
To determine the cause(s) that will not allow a server restart with
THIS file. Differences in Formating / Missing or Added Symbols (#) ?
(Also: Increased effective_cache_size to 4096MB below.)
SEARCH RESULTS:
Some superfluous # symbols on far right, plus possibly a problem w.
previous formatting at 100 characters width, narrowed it to 90.
> > > FILE NOW WORKS AGAIN - Ready for Final Testing
--------------------------------------------------------------------------------------------------------
