Which process is killed by the kernel?
Oh, and 4 GB RAM for OpenStreetMap seems ... a bit low.
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ő
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
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).
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
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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!
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.
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
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).
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
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen