Hi Sarah, Thanks for your input, it's much appreciated.
We need to rebuild anyway, so a new partitioned build is one the way. I will also try out walbouncer to see if it's an option. > > Hi, > > On Fri, Feb 13, 2015 at 01:36:05PM +0000, Anders Gunnarsson wrote: > > I'm currently looking over our setup of Noiminatim, since data is > growing out of our current storage capacity with high performance. > > > > I noticed the new tablespace settings attached below, which enables > the option to partition into fast and slow storage. > > Does anyone have experience with this? How much space is used by each > type of data for a full planet? What needs to be on fast media and what > can go on slower? I guess all indexes need fast media, and maybe some of > the data too. > > That's a fairly new feature I added for our production servers at > osm.org where it is already deployed. Here is the order I would give to > the importance of the tablespaces. This is purely subjective, I have no > numbers to back this: > > 1. CONST_Tablespace_Search_Index > 2. CONST_Tablespace_Search_Data > 3. CONST_Tablespace_Aux_Index > 4. CONST_Tablespace_Address_Index > 5. CONST_Tablespace_Place_Index > 6. CONST_Tablespace_Address_Data > 7. CONST_Tablespace_Place_Data > 8. CONST_Tablespace_Osm2pgsql_Index > 9. CONST_Tablespace_Aux_Data > 10. CONST_Tablespace_Osm2pgsql_Data > > On our installation 1-2 and 4-5 use 420G together, 5 and 6 use 100 GB, > 8 uses 120 GB (add another 100GB if you don't use the flat-nodes > feature). > Again, take that with a grain of salt because the indexes tend to bloat > pretty badly over time. > > > We currently use one master instance, feeding read only slaves which > are used for lookup. If I interpret the settings correct it would be > possible to use walbouncer to filter out tablespaces only containing > data marked with "update only", so that the slave instances only contain > lookup data. Is that true? > > That is an interesting thought. The tablespace feature wasn't created > with such an application in mind but it should work, if you use the > tablesspaces "update only" and the default tablespace (there are some > tiny tables which are created without any tablespace). Alternatively, > here is a minimal list of tables, you need for a read-only copy: > > *columns > import_polygon_* > import_status > place_addressline > placex > search_name > seq_* > word > place_classtype_* (if you allow special searchterms) > location_property_tiger location_property_aux > > That's around 300GB after a fresh import (add room for index bloat) and > another 350GB if you use Tiger data. You could just manually move them > (and their indexes) in a new dataspace in your existing DB and try > walbouncer on that. That would save you the reimport that is necessary > to be able to use the new tablespace feature. > > Kind regards > > Sarah Warm regards, Anders Gunnarsson _______________________________________________ Geocoding mailing list [email protected] https://lists.openstreetmap.org/listinfo/geocoding
