Thread

1. Analyzing foreign tables & memory problems

   Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-04-30T10:27:45Z

   During ANALYZE, in analyze.c, functions compute_minimal_stats
   and compute_scalar_stats, values whose length exceed
   WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
   other than that they are counted as "too wide rows" and assumed
   to be all different.
   
   This works fine with regular tables; values exceeding that threshold
   don't get detoasted and won't consume excessive memory.
   
   With foreign tables the situation is different.  Even though
   values exceeding WIDTH_THRESHOLD won't get used, the complete
   rows will be fetched from the foreign table.  This can easily
   exhaust maintenance_work_mem.
   
   A foreign data wrapper has no good way to counter the problem.
   It can return truncated values in ist AcquireSampleRowsFunc,
   but WIDTH_THRESHOLD is private to analyze.c and it's a bad idea
   to hard code a cutoff limit of 1025.
   
   I can think of two remedies:
   1) Expose WIDTH_THRESHOLD in commands/vacuum.h and add documentation
      so that the authors of foreign data wrappers are aware of the
      problem and can avoid it on their side.
      This would be quite simple.
   
   2) Instead of one callback that returns all sample rows, have
      a callback that just returns the next table row (or the next
      table row from a subset of the pages of the table in the
      internal case).  This function could be called in a short-lived
      memory context.  Vitter's algorithm for selecting a sample
      and the truncation of excessively long values would then be
      handled in analyze.c.
      This would avoid the problem completely and make it easier
      to write a foreign data wrapper.
      I haven't thought this out completely, and it would require
      bigger changes to analyze.c and the API than are probably
      welcome this close to beta.
   
   What is your opinion?
   
   Yours,
   Laurenz Albe
   
   
   
   

2. Re: Analyzing foreign tables & memory problems

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-04-30T14:24:30Z

   "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
   > During ANALYZE, in analyze.c, functions compute_minimal_stats
   > and compute_scalar_stats, values whose length exceed
   > WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
   > other than that they are counted as "too wide rows" and assumed
   > to be all different.
   
   > This works fine with regular tables; values exceeding that threshold
   > don't get detoasted and won't consume excessive memory.
   
   > With foreign tables the situation is different.  Even though
   > values exceeding WIDTH_THRESHOLD won't get used, the complete
   > rows will be fetched from the foreign table.  This can easily
   > exhaust maintenance_work_mem.
   
   I'm fairly skeptical that this is a real problem, and would prefer not
   to complicate wrappers until we see some evidence from the field that
   it's worth worrying about.  The WIDTH_THRESHOLD logic was designed a
   dozen years ago when common settings for work_mem were a lot smaller
   than today.  Moreover, to my mind it's always been about avoiding
   detoasting operations as much as saving memory, and we don't have
   anything equivalent to that consideration in foreign data wrappers.
   
   			regards, tom lane
   
   

3. Re: Analyzing foreign tables & memory problems

   Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-04-30T14:59:46Z

   Tom Lane wrote:
   >> During ANALYZE, in analyze.c, functions compute_minimal_stats
   >> and compute_scalar_stats, values whose length exceed
   >> WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
   >> other than that they are counted as "too wide rows" and assumed
   >> to be all different.
   > 
   >> This works fine with regular tables; values exceeding that threshold
   >> don't get detoasted and won't consume excessive memory.
   > 
   >> With foreign tables the situation is different.  Even though
   >> values exceeding WIDTH_THRESHOLD won't get used, the complete
   >> rows will be fetched from the foreign table.  This can easily
   >> exhaust maintenance_work_mem.
   
   > I'm fairly skeptical that this is a real problem, and would prefer not
   > to complicate wrappers until we see some evidence from the field that
   > it's worth worrying about.  The WIDTH_THRESHOLD logic was designed a
   > dozen years ago when common settings for work_mem were a lot smaller
   > than today.  Moreover, to my mind it's always been about avoiding
   > detoasting operations as much as saving memory, and we don't have
   > anything equivalent to that consideration in foreign data wrappers.
   
   If I have a table with 100000 rows and default_statistics_target
   at 100, then a sample of 30000 rows will be taken.
   
   If each row contains binary data of 1MB (an Image), then the
   data structure returned will use about 30 GB of memory, which
   will probably exceed maintenance_work_mem.
   
   Or is there a flaw in my reasoning?
   
   Yours,
   Laurenz Albe
   
   

4. Re: Analyzing foreign tables & memory problems

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-04-30T15:23:25Z

   "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
   > Tom Lane wrote:
   >> I'm fairly skeptical that this is a real problem, and would prefer not
   >> to complicate wrappers until we see some evidence from the field that
   >> it's worth worrying about.
   
   > If I have a table with 100000 rows and default_statistics_target
   > at 100, then a sample of 30000 rows will be taken.
   
   > If each row contains binary data of 1MB (an Image), then the
   > data structure returned will use about 30 GB of memory, which
   > will probably exceed maintenance_work_mem.
   
   > Or is there a flaw in my reasoning?
   
   Only that I don't believe this is a real-world scenario for a foreign
   table.  If you have a foreign table in which all, or even many, of the
   rows are that wide, its performance is going to suck so badly that
   you'll soon look for a different schema design anyway.
   
   I don't want to complicate FDWs for this until it's an actual bottleneck
   in real applications, which it may never be, and certainly won't be
   until we've gone through a few rounds of performance refinement for
   basic operations.
   
   			regards, tom lane
   
   

5. Re: Analyzing foreign tables & memory problems

   Simon Riggs <simon@2ndquadrant.com> — 2012-04-30T15:29:17Z

   On Mon, Apr 30, 2012 at 3:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   > "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
   >> During ANALYZE, in analyze.c, functions compute_minimal_stats
   >> and compute_scalar_stats, values whose length exceed
   >> WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
   >> other than that they are counted as "too wide rows" and assumed
   >> to be all different.
   >
   >> This works fine with regular tables; values exceeding that threshold
   >> don't get detoasted and won't consume excessive memory.
   >
   >> With foreign tables the situation is different.  Even though
   >> values exceeding WIDTH_THRESHOLD won't get used, the complete
   >> rows will be fetched from the foreign table.  This can easily
   >> exhaust maintenance_work_mem.
   >
   > I'm fairly skeptical that this is a real problem
   
   AFAIK its not possible to select all columns from an Oracle database.
   If you use an unqualified LONG column as part of the query then you
   get an error.
   
   So there are issues with simply requesting data for analysis.
   
   -- 
    Simon Riggs                   http://www.2ndQuadrant.com/
    PostgreSQL Development, 24x7 Support, Training & Services
   
   

6. Re: Analyzing foreign tables & memory problems

   Noah Misch <noah@leadboat.com> — 2012-04-30T17:12:42Z

   On Mon, Apr 30, 2012 at 12:27:45PM +0200, Albe Laurenz wrote:
   > During ANALYZE, in analyze.c, functions compute_minimal_stats
   > and compute_scalar_stats, values whose length exceed
   > WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
   > other than that they are counted as "too wide rows" and assumed
   > to be all different.
   > 
   > This works fine with regular tables; values exceeding that threshold
   > don't get detoasted and won't consume excessive memory.
   > 
   > With foreign tables the situation is different.  Even though
   > values exceeding WIDTH_THRESHOLD won't get used, the complete
   > rows will be fetched from the foreign table.  This can easily
   > exhaust maintenance_work_mem.
   > 
   > A foreign data wrapper has no good way to counter the problem.
   > It can return truncated values in ist AcquireSampleRowsFunc,
   > but WIDTH_THRESHOLD is private to analyze.c and it's a bad idea
   > to hard code a cutoff limit of 1025.
   > 
   > I can think of two remedies:
   > 1) Expose WIDTH_THRESHOLD in commands/vacuum.h and add documentation
   >    so that the authors of foreign data wrappers are aware of the
   >    problem and can avoid it on their side.
   >    This would be quite simple.
   
   Seems reasonable.  How would the FDW return an indication that a value was
   non-NULL but removed due to excess width?
   
   Not all databases can cheaply filter out wide column values; by the time the
   remote side has an exact width, the remote I/O damage may already be done.  To
   dodge that problem, when a column has "SET STATISTICS 0", the FDW should be
   able to completely omit reading it.  (I haven't studied the API needs, if any,
   to make that possible.)
   
   > 2) Instead of one callback that returns all sample rows, have
   >    a callback that just returns the next table row (or the next
   >    table row from a subset of the pages of the table in the
   >    internal case).  This function could be called in a short-lived
   >    memory context.  Vitter's algorithm for selecting a sample
   >    and the truncation of excessively long values would then be
   >    handled in analyze.c.
   >    This would avoid the problem completely and make it easier
   >    to write a foreign data wrapper.
   >    I haven't thought this out completely, and it would require
   >    bigger changes to analyze.c and the API than are probably
   >    welcome this close to beta.
   
   This solves the (in your downthread example) 30 GiB of memory consumption, but
   you'll still read 30 GiB on the remote side and ship it all over the network.
   To call this fixed, we'll need something like (1) that lets the FDW limit
   volume at the remote side.
   
   Thanks,
   nm
   
   

7. Re: Analyzing foreign tables & memory problems

   Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-04-30T18:36:21Z

   Simon Riggs wrote:
   >>> During ANALYZE, in analyze.c, functions compute_minimal_stats
   >>> and compute_scalar_stats, values whose length exceed
   >>> WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
   >>> other than that they are counted as "too wide rows" and assumed
   >>> to be all different.
   >>
   >>> This works fine with regular tables; values exceeding that threshold
   >>> don't get detoasted and won't consume excessive memory.
   >>
   >>> With foreign tables the situation is different.  Even though
   >>> values exceeding WIDTH_THRESHOLD won't get used, the complete
   >>> rows will be fetched from the foreign table.  This can easily
   >>> exhaust maintenance_work_mem.
   >>
   >> I'm fairly skeptical that this is a real problem
   
   > AFAIK its not possible to select all columns from an Oracle database.
   > If you use an unqualified LONG column as part of the query then you
   > get an error.
   >
   > So there are issues with simply requesting data for analysis.
   
   To detail on the specific case of Oracle, I have given up on LONG
   since a) it has been deprecated for a long time and
   b) it is not possible to retrieve a LONG column unless you know
   in advance how long it is.
   
   But you can have several BLOB and CLOB columns in a table, each
   of which can be arbitrarily large and can lead to the problem
   I described.
   
   Yours,
   Laurenz Albe
   
   

8. Re: Analyzing foreign tables & memory problems

   Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-04-30T18:50:04Z

   Tom Lane wrote:
   >>> I'm fairly skeptical that this is a real problem, and would prefer not
   >>> to complicate wrappers until we see some evidence from the field that
   >>> it's worth worrying about.
   
   >> If I have a table with 100000 rows and default_statistics_target
   >> at 100, then a sample of 30000 rows will be taken.
   
   >> If each row contains binary data of 1MB (an Image), then the
   >> data structure returned will use about 30 GB of memory, which
   >> will probably exceed maintenance_work_mem.
   
   >> Or is there a flaw in my reasoning?
   
   > Only that I don't believe this is a real-world scenario for a foreign
   > table.  If you have a foreign table in which all, or even many, of the
   > rows are that wide, its performance is going to suck so badly that
   > you'll soon look for a different schema design anyway.
   
   Of course it wouldn't work well to SELECT * from such a foreign table,
   but it would work well enough to get one or a few rows at a time,
   which is probably such a table's purpose in life anyway.
   
   > I don't want to complicate FDWs for this until it's an actual bottleneck
   > in real applications, which it may never be, and certainly won't be
   > until we've gone through a few rounds of performance refinement for
   > basic operations.
   
   I agree that it may not be the right thing to do something invasive
   to solve an anticipated problem that may never be one.
   
   So scrap my second idea.  But I think that exposing WIDTH_THRESHOLD
   wouldn't be unreasonable, would it?
   
   Yours,
   Laurenz Albe
   
   

9. Re: Analyzing foreign tables & memory problems

   Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-04-30T19:01:54Z

   Noah Misch wrote:
   >> During ANALYZE, in analyze.c, functions compute_minimal_stats
   >> and compute_scalar_stats, values whose length exceed
   >> WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
   >> other than that they are counted as "too wide rows" and assumed
   >> to be all different.
   >> 
   >> This works fine with regular tables;
   
   >> With foreign tables the situation is different.  Even though
   >> values exceeding WIDTH_THRESHOLD won't get used, the complete
   >> rows will be fetched from the foreign table.  This can easily
   >> exhaust maintenance_work_mem.
   
   >> I can think of two remedies:
   >> 1) Expose WIDTH_THRESHOLD in commands/vacuum.h and add documentation
   >>    so that the authors of foreign data wrappers are aware of the
   >>    problem and can avoid it on their side.
   >>    This would be quite simple.
   
   > Seems reasonable.  How would the FDW return an indication that a value was
   > non-NULL but removed due to excess width?
   
   The FDW would return a value of length WIDTH_THRESHOLD+1 that is
   long enough to be recognized as too long, but not long enough to
   cause a problem.
   
   > Not all databases can cheaply filter out wide column values; by the time the
   > remote side has an exact width, the remote I/O damage may already be done.  To
   > dodge that problem, when a column has "SET STATISTICS 0", the FDW should be
   > able to completely omit reading it.  (I haven't studied the API needs, if any,
   > to make that possible.)
   
   Depending on the capabilities of the remote side, a FDW can
   do more or less intelligent things to avoid the problem.
   But it must know WIDTH_THRESHOLD.
   
   Disabling statistics for a column as a workaround is an
   interesting idea, but would be more work for the FDW writer
   and the user.
   
   >> 2) Instead of one callback that returns all sample rows, have
   >>    a callback that just returns the next table row (or the next
   >>    table row from a subset of the pages of the table in the
   >>    internal case).  This function could be called in a short-lived
   >>    memory context.  Vitter's algorithm for selecting a sample
   >>    and the truncation of excessively long values would then be
   >>    handled in analyze.c.
   >>    This would avoid the problem completely and make it easier
   >>    to write a foreign data wrapper.
   
   > This solves the (in your downthread example) 30 GiB of memory consumption, but
   > you'll still read 30 GiB on the remote side and ship it all over the network.
   > To call this fixed, we'll need something like (1) that lets the FDW limit
   > volume at the remote side.
   
   You are right.  I guess the first idea is the more promising one.
   
   Yours,
   Laurenz Albe
   
   

10. Re: Analyzing foreign tables & memory problems

    Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-05-02T10:20:39Z

    I wrote:
    > Noah Misch wrote:
    >>> During ANALYZE, in analyze.c, functions compute_minimal_stats
    >>> and compute_scalar_stats, values whose length exceed
    >>> WIDTH_THRESHOLD (= 1024) are not used for calculating statistics
    >>> other than that they are counted as "too wide rows" and assumed
    >>> to be all different.
    >>>
    >>> This works fine with regular tables;
    
    >>> With foreign tables the situation is different.  Even though
    >>> values exceeding WIDTH_THRESHOLD won't get used, the complete
    >>> rows will be fetched from the foreign table.  This can easily
    >>> exhaust maintenance_work_mem.
    
    >>> I can think of two remedies:
    >>> 1) Expose WIDTH_THRESHOLD in commands/vacuum.h and add documentation
    >>>    so that the authors of foreign data wrappers are aware of the
    >>>    problem and can avoid it on their side.
    >>>    This would be quite simple.
    
    >> Seems reasonable.  How would the FDW return an indication that a
    value was
    >> non-NULL but removed due to excess width?
    > 
    > The FDW would return a value of length WIDTH_THRESHOLD+1 that is
    > long enough to be recognized as too long, but not long enough to
    > cause a problem.
    
    Here is a simple patch for that.
    
    Yours,
    Laurenz Albe
    

11. Re: Analyzing foreign tables & memory problems

    Noah Misch <noah@leadboat.com> — 2012-05-13T15:45:03Z

    On Wed, May 02, 2012 at 12:20:39PM +0200, Albe Laurenz wrote:
    > >>> 1) Expose WIDTH_THRESHOLD in commands/vacuum.h and add documentation
    > >>>    so that the authors of foreign data wrappers are aware of the
    > >>>    problem and can avoid it on their side.
    > >>>    This would be quite simple.
    > 
    > >> Seems reasonable.  How would the FDW return an indication that a
    > value was
    > >> non-NULL but removed due to excess width?
    > > 
    > > The FDW would return a value of length WIDTH_THRESHOLD+1 that is
    > > long enough to be recognized as too long, but not long enough to
    > > cause a problem.
    > 
    > Here is a simple patch for that.
    
    It feels to me like a undue hack to ask FDW authors to synthesize such values.
    It's easy enough for data types such as text/bytea.  In general, though,
    simple truncation may not produce a valid value of the type.  That shouldn't
    matter, since the next action taken on the value should be to discard it, but
    it's fragile.  Can we do better?
    
    Just thinking out loud, we could provide an "extern Datum AnalyzeWideValue;"
    and direct FDW authors to use that particular datum.  It could look like a
    toasted datum of external size WIDTH_THRESHOLD+1 but bear va_toastrelid ==
    InvalidOid.  Then, if future code movement leads us to actually examine one of
    these values, we'll get an early, hard error.
    
    Thanks,
    nm
    
    

12. Re: Analyzing foreign tables & memory problems

    Albe Laurenz <laurenz.albe@wien.gv.at> — 2012-05-14T07:21:20Z

    Noah Misch wrote:
    >>>>> 1) Expose WIDTH_THRESHOLD in commands/vacuum.h and add
    documentation
    >>>>>    so that the authors of foreign data wrappers are aware of the
    >>>>>    problem and can avoid it on their side.
    >>>>>    This would be quite simple.
    >>
    >>>> Seems reasonable.  How would the FDW return an indication that a
    value was
    >>>> non-NULL but removed due to excess width?
    >>>
    >>> The FDW would return a value of length WIDTH_THRESHOLD+1 that is
    >>> long enough to be recognized as too long, but not long enough to
    >>> cause a problem.
    >>
    >> Here is a simple patch for that.
    > 
    > It feels to me like a undue hack to ask FDW authors to synthesize such
    values.
    > It's easy enough for data types such as text/bytea.  In general,
    though,
    > simple truncation may not produce a valid value of the type.  That
    shouldn't
    > matter, since the next action taken on the value should be to discard
    it, but
    > it's fragile.  Can we do better?
    > 
    > Just thinking out loud, we could provide an "extern Datum
    AnalyzeWideValue;"
    > and direct FDW authors to use that particular datum.  It could look
    like a
    > toasted datum of external size WIDTH_THRESHOLD+1 but bear
    va_toastrelid ==
    > InvalidOid.  Then, if future code movement leads us to actually
    examine one of
    > these values, we'll get an early, hard error.
    
    That would be very convenient indeed.
    
    Even better would be a function
    extern Datum createAnalyzeWideValue(integer width)
    so that row width calculations could be more accurate.
    
    Yours,
    Laurenz Albe
    
    

13. Re: Analyzing foreign tables & memory problems

    Noah Misch <noah@leadboat.com> — 2012-05-15T13:08:08Z

    On Mon, May 14, 2012 at 09:21:20AM +0200, Albe Laurenz wrote:
    > Noah Misch wrote:
    > > Just thinking out loud, we could provide an "extern Datum
    > AnalyzeWideValue;"
    > > and direct FDW authors to use that particular datum.  It could look
    > like a
    > > toasted datum of external size WIDTH_THRESHOLD+1 but bear
    > va_toastrelid ==
    > > InvalidOid.  Then, if future code movement leads us to actually
    > examine one of
    > > these values, we'll get an early, hard error.
    > 
    > That would be very convenient indeed.
    > 
    > Even better would be a function
    > extern Datum createAnalyzeWideValue(integer width)
    > so that row width calculations could be more accurate.
    
    Yes; good call.