[Geocoding] Table space settings and storage

[Anders Gunnarsson]

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