hem 2 Performance

[sharon29uk]

After reading the following =

PostgreSQL:

Edit your PostgreSQL.conf file in the SQL 8.x/data folder. (you need to reboot after you've made the changes)
Do not use the Tuning Wizard. On some systems it will make changes to the postgresql.conf it shouldn't, resulting in being unable to connect to the database!
If you experience this problem--> simply go to the 8.4/data folder, undo the changes you made and reboot.
Recommended changes:

shared_buffers = <number> MB (set it to 20% of your system's memory, but not higher than 512 MB)
effective_cache_size = <number> MB (set this to 4x the size of the shared_buffers)

Change at your own risk: (change them back to default if postgresql stops running, or your performance decreases)
wal_buffers = 8MB
temp_buffers = 24MB #(wal_buffers x3)
work_mem = 128MB
maintenance_work_mem = 512MB
commit_delay = 10000
checkpoint_segments = 128
checkpoint_completion_target = 0.9

I would like to edit your PostgreSQL.conf file
But the location of PostgreSQL.conf on my system is C:\Program Files\PostgreSQL\data i can not find a folder which is named 8.4, is it still ok to edit it?

also in PostgreSQL.conf do i edit shared_buffers under the title RESOURCE USAGE (except WAL) or # CUSTOMIZED OPTIONS ?
thanks

[_Loki_]

[1] TIPS LOCATION
You are referring to the performance tips for postgreSQL located here :- http://forums.holdemmanager.com/general-support/494402-hm2-performance-tips.html

[2] POSTGRESQL VERSION
What version of PostgreSQL are you using? Some of the values are 8.x specific & may not work for 9.x for example [or different values for 9.x]

[3] LOCATION OF postgresql.conf FILE
Where it says "...your PostgreSQL.conf file in the SQL 8.x/data folder" don't worry about that exact address.
I have mine directly off my C drive here:- C:\postgreSQL\data\postgresql.conf with no mention of "8.4" in the path.

[4] WHICH shared_buffers?

http://forums.holdemmanager.com/attachments/general-support/157882-hem-2-performance-000postgresql.png

If it appears twice the value has been changed for "shared_buffers" already and postgreSQL leaves a note of the original value where the value used to live in the RESOURCE USAGE section [with a # in front so the system ignores the old value]. It then rewrites the line, but with the new value in the CUSTOMIZED OPTIONS section [without a # in front so the system will read it]

Like this:-

# RESOURCE USAGE (except WAL)
### REPLACED #### shared_buffers = 32MB

# CUSTOMIZED OPTIONS
shared_buffers = 1024MB

[_Loki_]

For editing the file I recommend you download Notepad++ here:- Notepad++ v6.6.9 - Current Version (http://notepad-plus-plus.org/download/v6.6.9.html)

It's free
It's far superior to Windows notepad & as you can see from my pic in the previous post the lines of script code are numbered & coloured - much easier to record what lines you changed! :)

[udbrky]

For any txt/xml usage, I recommend Notepad++ :)

Also, don't forget to remove the # from the start of the line that you edit.

[sharon29uk]

Would this be correct ? do i need to remove the other 5 hash tags #

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

#custom_variable_classes = '' # list of custom variable class names

shared_buffers = 1024MB
effective_cache_size = 4096MB
work_mem = 16MB
maintenance_work_mem = 16MB
commit_delay = 10000
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 2MB


Will changing the other values increase perfomance ?
wal_buffers = 8MB
temp_buffers = 24MB #(wal_buffers x3)
work_mem = 128MB
maintenance_work_mem

tho temp_buffers = 24MB #(wal_buffers x3) is not availlable in the file.

[_Loki_]

No:-
...do i need to remove the other 5 hash tags # I need the answer to this:-
... [2] POSTGRESQL VERSION
What version of PostgreSQL are you using? Some of the values are 8.x specific & may not work for 9.x for example [or different values for 9.x]...

[sharon29uk]

No:- I need the answer to this:-

thanks for reply
using 8.4

[_Loki_]

8.4
This gives most of the answers from the Elephant's trunk:- https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server It's worth skimming through to see what's recommended for your Windows system & it gives you some idea what's going on [why make the changes]
It was the above link I used for most of my settings & they're a bit less in some cases than yours [see the image I posted] EXAMPLES:-

QUOTE:- "Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount.

shared_buffers = 1024MB probably no better than 512MB

You also ask this:- "Will changing the other values increase performance?" [wal_buffers = 8MB, temp_buffers = 24MB #(wal_buffers x3), work_mem = 128MB, maintenance_work_mem,]
Judging by the link... I would say "No"

IMPORTANT NOTES:-
You will have to stop/start postgreSQL for some of the changes to take effect
Use Notepad++ & keep a written record of your changes with line numbers

[fozzy71]

Would this be correct ? do i need to remove the other 5 hash tags #

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

#custom_variable_classes = '' # list of custom variable class names

shared_buffers = 1024MB
effective_cache_size = 4096MB
work_mem = 16MB
maintenance_work_mem = 16MB
commit_delay = 10000
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 2MB


Will changing the other values increase perfomance ?
wal_buffers = 8MB
temp_buffers = 24MB #(wal_buffers x3)
work_mem = 128MB
maintenance_work_mem

tho temp_buffers = 24MB #(wal_buffers x3) is not availlable in the file.

You do not need to remove those 5 hash tags I bolded. Those are for comments.

[_Loki_]

...I would like to edit your PostgreSQL.conf file... How did it work out? Improved performance?

[sharon29uk]

ok it looks like this, i have only changed shared_buffers and effective_cache_size
but how do i know if these edits have taken effect ?

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

#custom_variable_classes = '' # list of custom variable class names

shared_buffers = 512MB
effective_cache_size = 2048MB

[fozzy71]

If you did Start > All Programs > PostgreSQL > Reload Configuration, or restarted your computer, then the new settings should have taken effect now. You edited them properly according to the text above.