Thanks for the tips - I had some of them implemented already, but every little bit helps.
After making these changes along with the indexes that Justin suggested the query time for the bbox below is now down to 2.4 seconds. That's just a little bit of an improvement over 130+ seconds... :) I have all of my clients running through it overnight - if all is well after a day or so then I think it'll be ready. The server address is roma.king-nerd.com. Feel free to hammer on it and see how it holds up. Munin graphs are at http://roma.king-nerd.com/munin Are there any special changes needed to interact with your load balancer? -Jeremy -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mathieu Arnold Sent: Wednesday, December 03, 2008 4:51 AM To: 'TilesAtHome' Subject: Re: [Tilesathome] Two new ROMA... +--On 2 décembre 2008 19:49:13 -0500 Milenko <[EMAIL PROTECTED]> wrote: | I have been attempting to bring my own ROMA server online as yet another | ROMA option (YAROMAO??) Once it's up, I'll just add it to the load balancer, and it'll rock. | For the record, my server is a 2x 3.0 Ghz Xeon with 8GB RAM and 4x 150GB | Velociraptor drives in RAID0 on an LSI MegaRAID 8344ELP controller. | Bonnie++ shows block reads at 426MB/s and over 700 seeks/s. Hum, mine is a, hum, dual p3 1.2GHz with 3.5GB of RAM, 4x36GB + 2x73GB drives in a somewhat raid0 configuration. I think bonnie would have said my box is far much less powerful than yours. | While fetching bbox 37.221680,11.594560,37.397461,11.709906 I get data in | sub-30 seconds from your two servers while mine takes over 130 seconds. Just tried on mine, it took 12 seconds. | I'll be the first to admit I'm not a pgsql admin, so there hasn't been | much tweaking done to the config on my server. | | Any help would be greatly appreciated. First, what version of postgresql are you running ? What OS ? The first thing you have to do, is to run ANALYZE on the database, otherwise, postgresql will still think your tables are mostly empty, and thus, will certainly do sequential scans instead of index scans. If that's enough, you don't have to do much more :-) Now, what have I done, in my postgresql.conf file... shared_buffers = 128M (that is, you'll certainly have to raise up some SHM things in your OS, I can tell you how to do that with FreeBSD, but I have no idea for other OS) temp_buffers = 128MB (that is not shared memory, so you can raise it without problems) work_mem = 16MB (for sorting and aggregating, though we shouldn't need much) maintenance_work_mem = 64MB max_fsm_pages = 1000000 max_fsm_relations = 1000 checkpoint_segments = 16 (because 3 just are not enough) autovacuum = on log_autovacuum_min_duration = 0 autovacuum_naptime = 60min autovacuum_vacuum_threshold = 100000 autovacuum_analyze_threshold = 50000 autovacuum_vacuum_scale_factor = 0.005 autovacuum_analyze_scale_factor = 0.001 And one last thing, I did a : enable_seqscan = off which will tell the planer not to, never, ever, do a seq scan if it can do something else. (If it has no choice, it will still do a seq scan, but it will always prefer an index.) All those directives are explained in detail in postgresql's documentation, just search google for them, it'l give you the right place. -- Mathieu Arnold _______________________________________________ Tilesathome mailing list [email protected] http://lists.openstreetmap.org/listinfo/tilesathome _______________________________________________ Tilesathome mailing list [email protected] http://lists.openstreetmap.org/listinfo/tilesathome
