On October 15, 2004 03:30 pm, Christopher Browne wrote:
> An issue we recently ran into was that our network guys would like to
> get a _clear_ picture as to how much bandwidth Slony-I is using.  It
> has potential impact on costs, as we have some bandwidth providers
> that charge extra for using more bandwidth than planned for.
>
> I'm sure many would be interested in getting a clearer picture as to
> how much of their bandwidth between destinations is getting "eaten up"
> by replication.
>
> Here are the beginnings of "a thought" on how we might get some
> statistics (at this point incomplete, but hopefully still useful, at
> least provisionally) out of the slon processes.
>
> A Method To Estimate Bandwidth Usage
>
>  A "first order" approximation of how much bandwidth is used might be
>  gotten by recording the sizes of all the queries that get submitted.
>
>  This would likely only reflect about 1/2 of the bandwidth usage, in
>  view of the fact that for every instance of "insert into table x
>  values (a, b, c)" that goes to a subscribing node, the contents of
>  that query were retrieved from the provider node.
>
>  That being said, knowing that "reality" is on the order of 2x the
>  size of the queries submitted is still a useful thing to know.
>
> A Mechanism To Determine Query Sizes
>
>  The cases where slon submits queries, it virtually always submits
>  them via the following idiom:
>
>   res = PQexec(dbconn, dstring_data(&query1));
>
>  dstring_data(x) is actually a macro that references a field in the
>  query structure.
>
>  One might capture the size of the query by modifying dstring_data(x)
>  to surreptitiously use strlen() to measure the size of the query, and
>  then add that value to a variable.
>
>  Alternatively, we might create a wrapper function and replace usage
>  of PQexec() with that function.  That is probably less scary :-).
>
> A Mechanism To Report Usage
>
>  There then needs to be a way to report the amount of query data that
>  has passed through dstring_data(x)'s "mouth."
>
>  I would suggest that reporting it in the slon logs each time the
>  cleanup cycle runs might be a reasonably satisfactory interval.
>
> A Method That Is Incomplete
>
>  The above methodology is conspicuously incomplete in three ways that
>  I can see:
>
>   - It does not make any attempt to measure the amount of bandwidth
>     consumed by results returned by queries.
>
>     The size of the result set would be pretty messy to rummage
>     through as a binary data structure.
>
>     That being said, the results that one would expect to be of
>     material size are those coming from queries of sl_log tables on
>     the provider node that then lead to queries being submitted to the
>     subscriber node.  And we would expect those to be of roughly equal
>     size, implying that it might be right to multiply the sizes of
>     queries by 2 for heavily updated databases.
>
>   - It does not make any attempt to separate statistics for "requests"
>     going to the provider from those for updates going to the
>     subscriber.
>
>   - It uses "size of queries" as a metric for "bandwidth used."
>
>     That is necessarily only an approximation.
>
>  Nonetheless, if we can get the "low hanging fruit" of being able to
>  easily provide _some_ numbers, that may suffice to allow the creation
>  of estimates that are, if not exact, at least based on a rational
>  process.  It is better to have some numbers than to have no
>  numbers...

I'm looking at making these kinds of statistics available through snmp pooling 
of the slon processes.  I havent gotten into the meat of calculating them, 
but am working on creating the snmp access framework at the moment.. I would 
be greatly interested in combining efforts on the implementation details to 
ensure that there is a solid framework. This should allow admins to graph 
their slony statistics though external tools like mrtg. I hope to have 
something that is a workable first cut available for general consumption by 
mid next week baring any unforeseen issues that take up my time elseware. 

If anybody else has any thought on this I look forward to hearing them.

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com