Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Posts
    1

    Unanswered: Postgres memory issues, I believe

    Hi,
    I'm having a problem with backing up a large database.

    The db contains over 3 million records. When it was about 2.8 million, the db was 1.5GB (uncompressed)

    We recently increased our server RAM from 2GB to 4GB and I changed 3 variables in postgresql.conf and they are:
    Code:
    shared_buffers = 60000
    maintenance_work_mem = 120000
    effective_cache_size =  60000
    Also, i adjusted /etc/sysctl.conf to have the following to accomodate the changes above:
    Code:
    kernel.shmmax = 2147483648


    Afterwards, I tried to backup the db and this is what hapenned:
    Code:
    [root@austin1 sql]# su postgres
    bash-3.00$ pg_dump -d  service_2_3 | gzip > backup.gz
    could not change directory to "/path/to/folder"
    pg_dump: SQL command failed
    pg_dump: Error message from server: server closed the connection unexpectedly
            This probably means the server terminated abnormally
            before or while processing the request.
    pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor


    This only happens for this particular db. other smaller db's on the same server don't suffer the same problems.




    Any idea what the issue is? I'd greatly appreciate any help

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    What version of postgreSQL are you running?

    What were the original values for the configuration variables you edited?

    If you return the configuration variables back to their original values, does the error go away?

    what is the maximum number of connections configured? as well as the superuser reserved connections?
    Last edited by loquin; 01-16-07 at 11:37.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    shared_buffers = 60000
    Per system documentation: "Values of a few thousand are recommended for production installations." Furthermore, "Increasing this parameter may cause PostgreSQL to request more System V shared memory than your operating system's default configuration allows."

    The minimum amount is 10 or 2* the maximum number of connections.

    You've specified nearly 500 megabytes for shared buffers alone.

    I would start with no more than 3-5 thousand for this variable. Increment it a few thousand at a time until you stop seeing any performance gains.

    maintenance_work_mem = 120000
    This entry is in KB. You've specified 120 megabytes in memory for maintenence operations. This value is probably ok, since usually, only one service that uses maintenence_work_mem will ever be running at any one time. The default value is almost certainly too low for your system.

    effective_cache_size = 60000
    The only affect this setting will have is on the operation of the query planner. It is an estimate of disk cache that will be available to a single index scan, not a chunk of memory that is reserved. It is factored into the cost of using an index. When this value is high, it's more likely that an index scan will be used; when it's low, it's more likely that a sequential scan will be used.

    Probably a good maximum is about 25% of the "available memory" (After the OS and all applications are loaded) for a decent cache size. (NOT 25% of total memory) Note: If this is tuned properly, you won't need to increase shared_buffers unnecessarily.

    ref Post #11 in the PostgreSQL FAQ
    Last edited by loquin; 01-16-07 at 13:41.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •