Calling PostgreSQL Experts

Discussion in 'MySQL' started by Entrep, Mar 13, 2006.

0
  1. #1
    I have a pretty decent server - Dual Xeon 2.8 with 3GB RAM.

    I use it to run two sites based on PGSQL. Current load is always around 0.1 or less.

    However, sometimes I get the following error:

    My postgresql.conf currently has:

    I am positive all this shouldnt be happening with 3GB RAM - how can I optimize the PGSQL install better to make use of the 3GB?
     
    Entrep, Mar 13, 2006 IP
  2. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #2
    300 Max Connections seems low but do you actually get 300 visits at any one moment? Like 300 people browsing your site in the same second?

    Sounds more like bad code. If you don't close your connection after opening it at the start of a script, then open it again you have 2 going for one visit.
     
    T0PS3O, Mar 13, 2006 IP
  3. amnezia

    amnezia Peon

    Messages:
    990
    Likes Received:
    31
    Best Answers:
    0
    Trophy Points:
    0
    #3
    it might not be a problem with PGSQL, it could be a problem with the software using the database. If the db connections are not closed properly then it can result the number of connections exceeding your max_connections which would give you an error mesage like the one your are recieving.

    EDIT TOPS30 beat me to the punch :)
     
    amnezia, Mar 13, 2006 IP
  4. Entrep

    Entrep Well-Known Member

    Messages:
    588
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    110
    #4
    There is an update script that runs that might push it over the 300 limit...

    Do you have any ideas on what the limit should be with respect to the server RAM, and if I need to change any of the other settings in the conf files?

    Thanks for the help!
     
    Entrep, Mar 13, 2006 IP
  5. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Why would one script need more than 300 connections? We're not talking queries, it's connections.

    MySQL can easily handle 1000 connections on a good server. AFAIK PostGreSQL is of a higher standard than MySQL so your good server should be able to handle that too I would have imagined (though not based on real knowledge).
     
    T0PS3O, Mar 13, 2006 IP
  6. Entrep

    Entrep Well-Known Member

    Messages:
    588
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    110
    #6

    OK cheers, I will try 1000 then. Do you know if any other settings that I showed need to be chaned?

    Also, the developer who made the site no longer supports it, how would I even begin to trouble shoot connections not being closed properly?

    Thanks!
     
    Entrep, Mar 13, 2006 IP
  7. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #7
    You'll have to go through all the pages of code pretty much. I don't know what language your site is in so you;ll have to figure out the functions for opening and closing connections.

    Maybe you can find someone on Get A Freelancer or here on DP to do it for you.
     
    T0PS3O, Mar 13, 2006 IP
  8. Entrep

    Entrep Well-Known Member

    Messages:
    588
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    110
    #8

    Thanks - its in PHP btw...

    Still open to advice on how I can improve the .conf file to take advantage of my server BTW (it only runs postgresql websites, no MYSQL etc)
     
    Entrep, Mar 13, 2006 IP