Postgres memory - how to increase it

PostgreSQL is the database server used to store information. Do you have a question or are you having problem with PostgreSQL? If so, post them here.

Moderator: Moderators

Postgres memory - how to increase it

Postby MrFlibble » Wed Apr 09, 2008 3:18 pm

Hi. Does anyone know how to increase the memory that postgres uses? Is the memory usage limited somewhere?
It only seems to ever use a little over 40MB while there's GBs free. And the HD usage is going mental so I think it would benefit from more RAM.
MrFlibble
 
Posts: 58
Joined: Sat Mar 08, 2008 7:30 pm
Location: handhq.com for datamined hands

Re: Postgres memory - how to increase it

Postby APerfect10 » Wed Apr 09, 2008 11:22 pm

Go Start->Programs->PostgreSQL->Configuration Files->Edit postgresql.conf

Look for 'shared_buffers = Xkb' Set this equal to about 1/4 of your total memory. If you have 2GB of memory you would change this to 'shared_buffer = 512MB'.

Then look for 'work_mem =' and set this value equivalent to 1/64 of your total RAM Ex) 2GB memory = 32MB of work_mem

Then 'maintenance_work_mem =' and set to approximately 1/16 of your total RAM. Ex) 2GB memory = 128MB of maintenance_work_mem

File->Save and then Start->Programs->PostgreSQL->Reload Configurations for the changes to take effect.

Best regards,

Derek
APerfect10
Site Admin
 
Posts: 4494
Joined: Sat Dec 08, 2007 6:03 pm

Re: Postgres memory - how to increase it

Postby MrFlibble » Thu Apr 10, 2008 8:45 am

Thanks APerfect10

Both work_mem and maintenance_work_mem were commented out. I take that means they use a default value?

The postgres process now takes about 115mb RAM. Still seems like its being restricted somewhere as there's 3.6GB free to play with.
Do you know any query off the top of your head that would make it use all the RAM available so I can test it?

Code: Select all
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -
shared_buffers = 1024MB         # min 128kB or max_connections*16kB
               # (change requires restart)
#temp_buffers = 8MB         # min 800kB
#max_prepared_transactions = 5      # can be 0 or more
               # (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 64MB            # min 64kB
maintenance_work_mem = 256MB      # min 1MB
#max_stack_depth = 2MB         # min 100kB

# - Free Space Map -
max_fsm_pages = 204800      # min max_fsm_relations*16, 6 bytes each
               # (change requires restart)
#max_fsm_relations = 1000      # min 100, ~70 bytes each
               # (change requires restart)
# - Kernel Resource Usage -
#max_files_per_process = 1000      # min 25
               # (change requires restart)
#shared_preload_libraries = ''      # (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0         # 0-1000 milliseconds
#vacuum_cost_page_hit = 1      # 0-10000 credits
#vacuum_cost_page_miss = 10      # 0-10000 credits
#vacuum_cost_page_dirty = 20      # 0-10000 credits
#vacuum_cost_limit = 200      # 0-10000 credits

# - Background writer -

#bgwriter_delay = 200ms         # 10-10000ms between rounds
#bgwriter_lru_percent = 1.0      # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5      # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333      # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5      # 0-1000 buffers max written/round


MrFlibble
 
Posts: 58
Joined: Sat Mar 08, 2008 7:30 pm
Location: handhq.com for datamined hands

Re: Postgres memory - how to increase it

Postby APerfect10 » Thu Apr 10, 2008 3:12 pm

Yes, if they were commented out then it was using the default value which was specified before you changed it.

Is that 115MB across all the postgres.exe processes? Or for one process?

Try something like...

Code: Select all
SELECT
    p.player_name,
    count(hhps.id_hand)
   
FROM
    holdem_hand_player_statistics hhps,
    holdem_hand_player_detail hhpd,
    holdem_hand_summary hhs,
    player p
   
WHERE
    hhs.id_hand = hhpd.id_hand
    AND hhpd.id_hand = hhps.id_hand
    AND hhpd.id_player = hhps.id_player
    AND hhps.id_player = p.id_player
   
GROUP BY
    p.player_name


A lot of the memory usage will depend on how large your database is too. If your database isnt large enough to require all of the memory usage then that could explain why its limited.

-Derek
APerfect10
Site Admin
 
Posts: 4494
Joined: Sat Dec 08, 2007 6:03 pm

Re: Postgres memory - how to increase it

Postby MrFlibble » Thu Apr 10, 2008 3:27 pm

115MB for one process. My db it quite big, postgres folder is over 25GB.
I'm using PT2 by the way. I ran this query:

Code: Select all
SELECT players.screen_name,
-- VPIP
      AVG(game_players.vol_put_money_in_pot) as vpip,
-- Total AF
      SUM(
      CAST(game_players.praise AS FLOAT) +
      CAST(game_players.fbet as FLOAT)+
      CAST(game_players.fraise as FLOAT)+
      CAST(game_players.tbet as FLOAT)+
      CAST(game_players.traise as FLOAT)+
      CAST(game_players.rbet as FLOAT)+
      CAST(game_players.rraise as FLOAT)
      )
      / SUM(
      CAST(game_players.pcall AS FLOAT) +
      CAST(game_players.fcall AS FLOAT) +
      CAST(game_players.tcall AS FLOAT) +
      CAST(game_players.rcall AS FLOAT) +
       0.000000000001) AS total_af   

FROM game_players,players
WHERE game_players.real_player_id = players.player_id
GROUP BY players.screen_name
LIMIT 100


which took 270 seconds (1.8GHz 4 core uP, 15k HD) and caused the postgres process to use 80MB ram.
MrFlibble
 
Posts: 58
Joined: Sat Mar 08, 2008 7:30 pm
Location: handhq.com for datamined hands

Re: Postgres memory - how to increase it

Postby APerfect10 » Thu Apr 10, 2008 11:47 pm

The maximum is on a connection by connection basis therefore in PT2's single connection you can conceivably bump these numbers up. In PokerTracker 3, we use multiple connections and if each is using 100MB+ of RAM that will put you close to 1GB of usage...So please keep that in mind when adjusting your PostgreSQL memory configurations.

Best regards,

Derek
APerfect10
Site Admin
 
Posts: 4494
Joined: Sat Dec 08, 2007 6:03 pm

Re: Postgres memory - how to increase it

Postby MrFlibble » Fri Apr 11, 2008 3:55 am

Ok. Thanks Derek.
MrFlibble
 
Posts: 58
Joined: Sat Mar 08, 2008 7:30 pm
Location: handhq.com for datamined hands

Re: Postgres memory - how to increase it

Postby zedjr » Sat Aug 16, 2008 10:55 am

APerfect10 wrote:Go Start->Programs->PostgreSQL->Configuration Files->Edit postgresql.confLook for 'shared_buffers = Xkb'  Set this equal to about 1/4 of your total memory.  If you have 2GB of memory you would change this to 'shared_buffer = 512MB'.Then look for 'work_mem =' and set this value equivalent to 1/64 of your total RAM Ex) 2GB memory = 32MB of work_memThen 'maintenance_work_mem =' and set to approximately 1/16 of your total RAM.  Ex) 2GB memory = 128MB of maintenance_work_memFile->Save and then Start->Programs->PostgreSQL->Reload Configurations for the changes to take effect.Best regards,Derek
I did this and my computer started acting really funny. i have 8gb of ram and set it twice as high as shown but set it at levels where it was recomended. After acting funny i cut it in half like i had 4gb of ram. I did this for my postgresql 8.3 installation. I have a posgresql 8.2 installation also which i greatly reduced that. AM not sure this really helps or not. it does seem now it reads over the duplicate hands really fast but i see no more ram being used in my processes window. is there anything else i can do to speed my sql up. I have 8gb of fb dimm ram on a dual cpu board with one 5450 xeon processor. am getting other one soon. any known tweaks to help performance would be greatly appreciated. Also what are the default settings so i can change my postgresql 8.2 installation back to default.
zedjr
 
Posts: 198
Joined: Mon Feb 04, 2008 6:28 pm

Re: Postgres memory - how to increase it

Postby WhiteRider » Sat Aug 16, 2008 11:09 am

I don't have any more tweaks for you, but I think if you comment out the line (i.e. add a # at the start) it will use defaults.
WhiteRider
Moderator
 
Posts: 54025
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Postgres memory - how to increase it

Postby zedjr » Sat Aug 16, 2008 11:12 am

How can i tell that postgres is taking advantage of the new settings. should there be an increase in ram it uses as i dont see any increase in the ram it is using.
zedjr
 
Posts: 198
Joined: Mon Feb 04, 2008 6:28 pm

Next

Return to PostgreSQL [Read Only]

Who is online

Users browsing this forum: No registered users and 0 guests