Seite 1 von 2 12 LetzteLetzte
Ergebnis 1 bis 10 von 17

Thema: "Out of memory" in Linux

  1. #1
    GergoH ist offline Neuer Benutzer
    Registriert seit
    02.02.2011
    Beiträge
    9

    Standard "Out of memory" in Linux

    Hi All!

    Entschuldigung, dass ich nicht in deutsch schreibe. Aber ich glaube das ist besser für allen

    I'm using PostgreSQL 8.3 on a dedicated server (64bits, with 4GB RAM) with Debian.
    I'd like to build/index a huge database (OpenStreetMap). With the default configuration it would take months, so I've been trying to optimize it.
    The progress is much faster now, but the kernel keeps killing the postgres process due to "out of memory".

    What am I doing wrong? Any help or suggestion would be welcome!


    The changes to the default configuration are the following:

    Postgresql:

    107,108c107
    < shared_buffers = 800MB
    ---
    > shared_buffers = 32MB
    115,116c114,115
    < work_mem = 128MB
    < maintenance_work_mem = 128MB
    ---
    > #work_mem = 1MB
    > #maintenance_work_mem = 16MB
    121c120
    < max_fsm_pages = 411000
    ---
    > max_fsm_pages = 204800
    153c152
    < fsync = off
    ---
    > #fsync = on
    163c162
    < wal_buffers = 6MB
    ---
    > #wal_buffers = 64kB
    172c171
    < checkpoint_segments = 15
    ---
    > #checkpoint_segments = 3
    174c173
    < checkpoint_completion_target = 0.8
    ---
    > #checkpoint_completion_target = 0.5
    205c204
    < random_page_cost = 2.0
    ---
    > #random_page_cost = 4.0
    209c208
    < effective_cache_size = 3GB
    ---
    > #effective_cache_size = 128MB
    383c381
    < autovacuum = off
    ---
    > #autovacuum = on


    Debian:
    I've set SHMMAX to 1GB = 1024*1024*1024 = 1073741824, and SHMALL to SHMMAX/4096 = 262144

    After a few tries I've set strict overcommit mode (sysctl -w vm.overcommit_memory=2) as was suggested in the "managing server resources" part of the pg documentation.

    Thanks in advance!
    Gergő

  2. #2
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    Which process is killed by the kernel?

    Oh, and 4 GB RAM for OpenStreetMap seems ... a bit low.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  3. #3
    GergoH ist offline Neuer Benutzer
    Registriert seit
    02.02.2011
    Beiträge
    9

    Standard

    Thanks for your quick reply!

    The postgres process was killed (by oom-killer). (while running gazetteer-loaddata.sql, if you are familiar with OSM things)

    I've forgot to mention that since the overcommit mode was set to strict, the oom-killer doesn't kill the postgres process, but the postgresql is giving "out of memory" error messages (and the script stops running).

  4. #4
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    Some things which are not total related, but which are questionable:

    - Why is work_mem set to 128 MB?
    - Why is fsync off?
    - max_fsm_pages is probably too low.
    - effective_cache_size is probably too high.
    - autovacuum should be on.
    - More checkpoint_segments would be nice. Please monitor the pg_xlog directory, how many logfiles are created by PostgreSQL.


    How big is your SQL file? Is it all load in one big transaction?
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  5. #5
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.468

    Standard

    Zitat Zitat von ads Beitrag anzeigen
    Some things which are not total related, but which are questionable:

    - Why is work_mem set to 128 MB?
    Yes, good question. Consider: work_mem is for every client and, if you have complex queries, more than once per query. In other words: if you have several connections and complex queries, you run out of memory very fast ...

    Regards, Andreas (and forgive my bad english...)
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  6. #6
    Gisman ist offline Erfahrener Benutzer
    Registriert seit
    20.01.2006
    Ort
    Pfalz
    Beiträge
    792

    Standard

    Maybe this link will help you. Ok, it is in german, but maybe it will work or you can get some hints. The main problem is, that you have not enough memory to run what you want as fast as you want. So all playing around with kernel parameters will not solve this problem. Another question is, why do you need such a large osm-database? For testing, maybe a subset of the data will do the same(and you can specify your future hardware-requirements ) and for production use the server is for sure too small. If you can not get your boss to buy you a new server, maybe some partitoning of the data may be helpful, but this is also not a fast solution.

    CU Uwe
    Have a lot of fun!

  7. #7
    GergoH ist offline Neuer Benutzer
    Registriert seit
    02.02.2011
    Beiträge
    9

    Standard

    Thank you for your suggestions! That's exactly what I need because my config settings were ad-hoc, I'm not familiar with PostgreSQL.

    This particular sql file is small. The query is inserting records from a table to another. A before-insert trigger is doing the work for the database making it ready for mapping purposes (using PostGIS). The size of the original table is 13GB with 58M records (Europe extract of the OSM data).

    Turning off the fsync and the autovacuum for the initial process was suggested by the OSM stuff.
    I've misunderstood the effective_cache_size. I thought it was using hard drive space...

    So I'm running the script now with the following changes:
    work_mem=32MB
    maintenance_work_mem=64MB
    max_fsm_pages=611000
    checkpoint_segments=20
    effective_cache_size=1GB

    With the current settings 15 MB/h is processed (and it will slow down as the size of the new table grows), so it would take 1-2 months to finish. :S
    With my very first settings it was like 25 times faster. (the main differences to the current: 8MB wal_buffers, 128MB work_mem, 5GB effecitve cache_size)

    In the pg_xlog directory 28 files were created in the first 30 mins.

  8. #8
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    Loading 13 GB should not take 1 month. It should take at longest some hours.

    I could even load in in some minutes, but not on your hardware.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  9. #9
    GergoH ist offline Neuer Benutzer
    Registriert seit
    02.02.2011
    Beiträge
    9

    Standard

    The system has run out of memory again, and the process has stopped with this latest settings. What do you think waht should I adjust on it?

    Getting some RAM makes sense. I'm trying to borrow some big modules for this inital phase hoping that I won't need to build the database again (I'll make a backup of it).

  10. #10
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    Really, try more ram ...
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

Seite 1 von 2 12 LetzteLetzte

Ähnliche Themen

  1. Antworten: 7
    Letzter Beitrag: 03.02.2012, 09:58
  2. Antworten: 1
    Letzter Beitrag: 28.08.2009, 13:51
  3. Shared-memory (linux) shmmax?
    Von CenText im Forum Konfiguration
    Antworten: 4
    Letzter Beitrag: 18.01.2008, 20:52
  4. "Group-by" und "max" Funktionen verknüpfen
    Von superguddy777 im Forum SQL
    Antworten: 1
    Letzter Beitrag: 22.08.2007, 08:35
  5. OLD.sum(...) ->record "old" has no field "sum"
    Von Mster im Forum Trigger und Funktionen
    Antworten: 5
    Letzter Beitrag: 18.01.2007, 15:00

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum