<commit_before># CouchDB Metrics

There are two scripts to pull metrics from a couchdb server and its databases - both need json npm (npm -i json)

![Example couch dashboard](/couchdb/pics/couch.png)

##Metrics from couchdb server (/_stats)

```couch_get_server_stats.sh couch_host metrics_host_and_db [metric-host-name]```

This will add a single metric document basically made up of the output of /_stats with some bits cleaned up.

Example metric doc:

```
{
   "_id": "3ad893cb4cf1560add7b4caffd4b6126",
   "_rev": "1-1f0ce165e1d210319cf6e9f9c6ff654f",
   "name": "mw-staging.couchdb",
   "type": "couchdb",
   "ts": 1445785730,
   "couchdb": { 
       "auth_cache_misses": { "current": null, "sum": null, "mean":  null, "stddev": null, "min": null, "max": null },
       "database_writes":   { "current": 1955, "sum": 1955, "mean": 0.004, "stddev": 0.061, "min": 0, "max": 1 },
       "open_databases":    { "current": 47, "sum": 47, "mean": 0, "stddev": 0.03, "min": 0, "max": 14 },
       "auth_cache_hits":   { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "request_time":      { "current": 934798.325, "sum": 934798.325, "mean": 247.236, "stddev": 9323.841, "min": 0, "max": 415733 },
       "database_reads":    { "current": 688315, "sum": 688315, "mean": 1.316, "stddev": 69.941, "min": 0, "max": 5497 },
       "open_os_files":     { "current": 101, "sum": 101, "mean": 0, "stddev": 0.061, "min": -1, "max": 28 }
   },
   "httpd_request_methods": {
       "PUT":    { "current": 18, "sum": 18, "mean": 0, "stddev": 0.009, "min": 0, "max": 1 },
       "GET":    { "current": 11172, "sum": 11172, "mean": 0.021, "stddev": 0.747, "min": 0, "max": 66 },
       "COPY":   { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "DELETE": { "current": 2, "sum": 2, "mean": 0, "stddev": 0.003, "min": 0, "max": 1 },
       "POST":   { "current": 1948, "sum": 1948, "mean": 0.004, "stddev": 0.061, "min": 0, "max": 1 },
       "HEAD":   { "current": 1, "sum": 1, "mean": 0, "stddev": 0.004, "min": 0, "max": 1 } 
   },
   "httpd_status_codes": { 
       "200": { "current": 9073, "sum": 9073, "mean": 0.017, "stddev": 0.589, "min": 0, "max": 53 },
       "201": { "current": 1949, "sum": 1949, "mean": 0.004, "stddev": 0.061, "min": 0, "max": 1},
       "202": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "301": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "304": { "current": 81, "sum": 81, "mean": 0, "stddev": 0.026, "min": 0, "max": 3 },
       "400": { "current": 2, "sum": 2, "mean": 0, "stddev": 0.005, "min": 0, "max": 1 },
       "401": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "403": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "404": { "current": 1585, "sum": 1585, "mean": 0.007, "stddev": 0.375, "min": 0, "max": 33 },
       "405": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "409": { "current": 4, "sum": 4, "mean": 0, "stddev": 0.008, "min": 0, "max": 1 },
       "412": { "current": 2, "sum": 2, "mean": 0, "stddev": 0.006, "min": 0, "max": 1 },
       "500": { "current": 1, "sum": 1, "mean": 0, "stddev": 0.004, "min": 0, "max": 1 }
   },
   "httpd": {
       "clients_requesting_changes": { "current": 0, "sum": 0, "mean": 0, "stddev": 0.033, "min": -2, "max": 2 },
       "temporary_view_reads":       { "current": 4, "sum": 4, "mean": 0, "stddev": 0.008, "min": 0, "max": 1 },
       "requests":                   { "current": 12186, "sum": 12186, "mean": 0.023, "stddev": 0.751, "min": 0, "max": 66 },
       "bulk_requests":              { "current": 1920, "sum": 1920, "mean": 0.004, "stddev": 0.06, "min": 0, "max": 1 },
       "view_reads":                 { "current": 206, "sum": 206, "mean": 0.003, "stddev": 0.062, "min": 0, "max": 2 }
   }
}
```

Add something like below to crontab:

```*/5 * * * * /home/metrics/couch_get_server_stats.sh http://192.168.0.10:5984 http://192.168.0.100:5984/metricsdb```



### Open Databases & Open OS Files

```
WITH couchdb_current AS (
	SELECT (doc->>'ts')::numeric * 1000 AS time,
	(doc->'couchdb'->'open_databases'->>'current')::numeric AS open_databases,
	(doc->'couchdb'->'open_os_files'->>'current')::numeric AS open_os_files
	FROM abtest
	WHERE doc->>'name'='mw-staging.couchdb' 
	AND ( to_timestamp((doc->>'ts')::numeric) > now() - interval '12h')
),

results AS (    
  SELECT '{ "results": [' AS v     
  UNION ALL
  SELECT '{ "series": [{ "name": "mw-staging.couchdb.open_databases", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,open_databases))  || ' }] }'
    AS v FROM couchdb_current 
  UNION ALL
  SELECT ',{ "series": [{ "name": "mw-staging.couchdb.open_os_files", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,open_os_files))  || ' }] }'
    AS v FROM couchdb_current 
  UNION ALL
  SELECT ']}' AS v   
)
SELECT string_agg(v,'') AS ret FROM results
```

![Example couch open dbs and files](/couchdb/pics/couch-open-dbs-files.png)


### Reads / Writes per minute

```
WITH couch_read_writes AS (
  SELECT (doc->>'ts')::numeric * 1000 AS  time,
         ((doc->'couchdb'->'database_reads'->>'current')::numeric - lag((doc->'couchdb'->'database_reads'->>'current')::numeric, 1, '0') OVER w )
           / ((doc->>'ts')::numeric - lag((doc->>'ts')::numeric, 1, '1') OVER w)::numeric AS  database_reads_per_sec,
         ((doc->'couchdb'->'database_writes'->>'current')::numeric - lag((doc->'couchdb'->'database_writes'->>'current')::numeric, 1, '0') OVER w )
           / ((doc->>'ts')::numeric - lag((doc->>'ts')::numeric, 1, '1') OVER w)::numeric AS  database_writes_per_sec 
	FROM abtest
	WHERE doc->>'name'='mw-staging.couchdb' 
	AND ( to_timestamp((doc->>'ts')::numeric) > now() - interval '12h')
	  WINDOW w AS  (ORDER BY (doc->>'ts')::numeric)   
	ORDER BY time
),
results AS (    
  SELECT '{ "results": [' AS v     
  UNION ALL
   SELECT '{ "series": [{ "name": "mw-staging.couchdb.database_reads_per_min", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,ROUND(database_reads_per_sec,2)*60))  || ' }] }'
    AS v FROM couch_read_writes 
  UNION ALL 
   SELECT ',{ "series": [{ "name": "mw-staging.couchdb.database_writes_per_min", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,ROUND(database_writes_per_sec,2)*60))  || ' }] }'
    AS v FROM couch_read_writes 
  UNION ALL
  SELECT ']}' AS v   
 )
 SELECT string_agg(v,'') AS ret FROM results
```

![Example couch read/writes per min](/couchdb/pics/couch-read-write.png)


### Http status codes per sec

```
WITH httpd_status_codes AS (
  SELECT (doc->>'ts')::numeric * 1000 AS  time,<commit_msg>add avg_request_time_ms_per_request to Reads / Writes<commit_after># CouchDB Metrics

There are two scripts to pull metrics from a couchdb server and its databases - both need json npm (npm -i json)

![Example couch dashboard](/couchdb/pics/couch.png)

##Metrics from couchdb server (/_stats)

```couch_get_server_stats.sh couch_host metrics_host_and_db [metric-host-name]```

This will add a single metric document basically made up of the output of /_stats with some bits cleaned up.

Example metric doc:

```
{
   "_id": "3ad893cb4cf1560add7b4caffd4b6126",
   "_rev": "1-1f0ce165e1d210319cf6e9f9c6ff654f",
   "name": "mw-staging.couchdb",
   "type": "couchdb",
   "ts": 1445785730,
   "couchdb": { 
       "auth_cache_misses": { "current": null, "sum": null, "mean":  null, "stddev": null, "min": null, "max": null },
       "database_writes":   { "current": 1955, "sum": 1955, "mean": 0.004, "stddev": 0.061, "min": 0, "max": 1 },
       "open_databases":    { "current": 47, "sum": 47, "mean": 0, "stddev": 0.03, "min": 0, "max": 14 },
       "auth_cache_hits":   { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "request_time":      { "current": 934798.325, "sum": 934798.325, "mean": 247.236, "stddev": 9323.841, "min": 0, "max": 415733 },
       "database_reads":    { "current": 688315, "sum": 688315, "mean": 1.316, "stddev": 69.941, "min": 0, "max": 5497 },
       "open_os_files":     { "current": 101, "sum": 101, "mean": 0, "stddev": 0.061, "min": -1, "max": 28 }
   },
   "httpd_request_methods": {
       "PUT":    { "current": 18, "sum": 18, "mean": 0, "stddev": 0.009, "min": 0, "max": 1 },
       "GET":    { "current": 11172, "sum": 11172, "mean": 0.021, "stddev": 0.747, "min": 0, "max": 66 },
       "COPY":   { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "DELETE": { "current": 2, "sum": 2, "mean": 0, "stddev": 0.003, "min": 0, "max": 1 },
       "POST":   { "current": 1948, "sum": 1948, "mean": 0.004, "stddev": 0.061, "min": 0, "max": 1 },
       "HEAD":   { "current": 1, "sum": 1, "mean": 0, "stddev": 0.004, "min": 0, "max": 1 } 
   },
   "httpd_status_codes": { 
       "200": { "current": 9073, "sum": 9073, "mean": 0.017, "stddev": 0.589, "min": 0, "max": 53 },
       "201": { "current": 1949, "sum": 1949, "mean": 0.004, "stddev": 0.061, "min": 0, "max": 1},
       "202": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "301": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "304": { "current": 81, "sum": 81, "mean": 0, "stddev": 0.026, "min": 0, "max": 3 },
       "400": { "current": 2, "sum": 2, "mean": 0, "stddev": 0.005, "min": 0, "max": 1 },
       "401": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "403": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "404": { "current": 1585, "sum": 1585, "mean": 0.007, "stddev": 0.375, "min": 0, "max": 33 },
       "405": { "current": null, "sum": null, "mean": null, "stddev": null, "min": null, "max": null },
       "409": { "current": 4, "sum": 4, "mean": 0, "stddev": 0.008, "min": 0, "max": 1 },
       "412": { "current": 2, "sum": 2, "mean": 0, "stddev": 0.006, "min": 0, "max": 1 },
       "500": { "current": 1, "sum": 1, "mean": 0, "stddev": 0.004, "min": 0, "max": 1 }
   },
   "httpd": {
       "clients_requesting_changes": { "current": 0, "sum": 0, "mean": 0, "stddev": 0.033, "min": -2, "max": 2 },
       "temporary_view_reads":       { "current": 4, "sum": 4, "mean": 0, "stddev": 0.008, "min": 0, "max": 1 },
       "requests":                   { "current": 12186, "sum": 12186, "mean": 0.023, "stddev": 0.751, "min": 0, "max": 66 },
       "bulk_requests":              { "current": 1920, "sum": 1920, "mean": 0.004, "stddev": 0.06, "min": 0, "max": 1 },
       "view_reads":                 { "current": 206, "sum": 206, "mean": 0.003, "stddev": 0.062, "min": 0, "max": 2 }
   }
}
```

Add something like below to crontab:

```*/5 * * * * /home/metrics/couch_get_server_stats.sh http://192.168.0.10:5984 http://192.168.0.100:5984/metricsdb```



### Open Databases & Open OS Files

```
WITH couchdb_current AS (
	SELECT (doc->>'ts')::numeric * 1000 AS time,
	(doc->'couchdb'->'open_databases'->>'current')::numeric AS open_databases,
	(doc->'couchdb'->'open_os_files'->>'current')::numeric AS open_os_files
	FROM abtest
	WHERE doc->>'name'='mw-staging.couchdb' 
	AND ( to_timestamp((doc->>'ts')::numeric) > now() - interval '12h')
),

results AS (    
  SELECT '{ "results": [' AS v     
  UNION ALL
  SELECT '{ "series": [{ "name": "mw-staging.couchdb.open_databases", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,open_databases))  || ' }] }'
    AS v FROM couchdb_current 
  UNION ALL
  SELECT ',{ "series": [{ "name": "mw-staging.couchdb.open_os_files", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,open_os_files))  || ' }] }'
    AS v FROM couchdb_current 
  UNION ALL
  SELECT ']}' AS v   
)
SELECT string_agg(v,'') AS ret FROM results
```

![Example couch open dbs and files](/couchdb/pics/couch-open-dbs-files.png)


### Reads / Writes per sec + average time per request (ms)

```
WITH couch_read_writes AS (
  SELECT (doc->>'ts')::numeric * 1000 AS  time,
         ((doc->'couchdb'->'database_reads'->>'current')::numeric - lag((doc->'couchdb'->'database_reads'->>'current')::numeric, 1, '0') OVER w )
           / ((doc->>'ts')::numeric - lag((doc->>'ts')::numeric, 1, '1') OVER w)::numeric AS  database_reads_per_sec,
         ((doc->'couchdb'->'database_writes'->>'current')::numeric - lag((doc->'couchdb'->'database_writes'->>'current')::numeric, 1, '0') OVER w )
           / ((doc->>'ts')::numeric - lag((doc->>'ts')::numeric, 1, '1') OVER w)::numeric AS  database_writes_per_sec,
         ((doc->'couchdb'->'request_time'->>'current')::numeric - lag((doc->'couchdb'->'request_time'->>'current')::numeric, 1, '0') OVER w )
         / ((doc->'httpd'->'requests'->>'current')::numeric - lag((doc->'httpd'->'requests'->>'current')::numeric, 1, '1') OVER w)::numeric AS  request_time_per_request
    FROM abtest
    WHERE doc->>'name'='mel-couch.couchdb' 
      WINDOW w AS  (ORDER BY (doc->>'ts')::numeric)   
    ORDER BY time
),
results AS (    
  SELECT '{ "results": [' AS v     
  UNION ALL
   SELECT '{ "series": [{ "name": "database_reads_per_sec", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,ROUND(database_reads_per_sec,2)))  || ' }] }'
    AS v FROM couch_read_writes 
  UNION ALL 
   SELECT ',{ "series": [{ "name": "database_writes_per_sec", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,ROUND(database_writes_per_sec,2)))  || ' }] }'
    AS v FROM couch_read_writes 
  UNION ALL 
   SELECT ',{ "series": [{ "name": "avg_request_time_ms_per_request", "columns": ["time", "value"], "values": ' || json_agg(json_build_array(time,ROUND(request_time_per_request*1000,2)))  || ' }] }'
    AS v FROM couch_read_writes 

  UNION ALL
  SELECT ']}' AS v   
 )
 SELECT string_agg(v,'') AS ret FROM results
```

Note on the ```avg_request_time_ms_per_request``` shows how flexible this can be as you can access other metrics collected to work this out - ie doc->'httpd'->'requests'->>'current' 

![Example couch read/writes per min](/couchdb/pics/couch-read-write.png)


### Http status codes per sec

```
WITH httpd_status_codes AS (
  SELECT (doc->>'ts')::numeric * 1000 AS  time,
{'fraction_non_alphanumeric': 0.23342503438789547, 'fraction_numerical': 0.06801925722145805, 'mean_word_length': 3.6710568583360104, 'pattern_counts': {'":': 492, '<': 3, '<?xml version=': 0, '>': 120, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 4, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6464768', 'n_tokens_mistral': 6396, 'n_tokens_neox': 5517, 'n_words': 1435}