Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

When using JSON output, all "setttings" are strings #12

Open
gclough opened this issue May 24, 2018 · 1 comment
Open

When using JSON output, all "setttings" are strings #12

gclough opened this issue May 24, 2018 · 1 comment

Comments

@gclough
Copy link

gclough commented May 24, 2018

In the json output created by:

pgmetrics --no-password --format=json

All of the setting values are stored as strings in the JSON output:

    "maintenance_work_mem": {
      "setting": "65536"
    },
    "max_connections": {
      "setting": "100"
    },
    "max_files_per_process": {
      "setting": "1000"
    },

Would it be possible (or sensible) to output parameters that we "know" will be numbers as actual numbers in JSON? That just makes it easier later on to do mathematics on the result.

We can automatically get the three main groupings of string/integer/real with:

string

SELECT name, setting, vartype, COALESCE(boot_val,'') AS boot_val, source,
       COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
  FROM pg_settings
  WHERE vartype NOT IN ('integer','real')
  ORDER BY name ASC;

integer

SELECT name, setting::integer, vartype, COALESCE(boot_val,'')::integer AS boot_val, source,
       COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
  FROM pg_settings
  WHERE vartype = 'integer'
  ORDER BY name ASC;

real

SELECT name, setting::real, vartype, COALESCE(boot_val,'')::real AS boot_val, source,
       COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
  FROM pg_settings
  WHERE vartype = 'real'
  ORDER BY name ASC;

I guess we don't really need vartype in there, but I thought it may be helpful (or needed) when the JSON is created.

Also, I've added column aliases "AS boot_val", etc. just so that the SQL output is human readable, rather than columns being called "coalesce".

I did a basic test of the theory here:

integer

postgres=# select array_to_json(array_agg(row_to_json(t)))
postgres-# from (
postgres(# SELECT name, setting::integer
postgres(#   FROM pg_settings
postgres(#   WHERE vartype = 'integer'
postgres(#     AND name IN ('maintenance_work_mem','max_connections','max_files_per_process')
postgres(#   ORDER BY name ASC
postgres(# )t
postgres-# ;
                                                               array_to_json
--------------------------------------------------------------------------------------------------------------------------------------------
 [{"name":"maintenance_work_mem","setting":65536},{"name":"max_connections","setting":100},{"name":"max_files_per_process","setting":1000}]
(1 row)

real

postgres=# select array_to_json(array_agg(row_to_json(t)))
postgres-# from (
postgres(# SELECT name, setting::real
postgres(#   FROM pg_settings
postgres(#   WHERE vartype = 'real'
postgres(#   ORDER BY name ASC
postgres(#   LIMIT 3
postgres(# )t
postgres-# ;
                                                                           array_to_json
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"name":"autovacuum_analyze_scale_factor","setting":0.1},{"name":"autovacuum_vacuum_scale_factor","setting":0.2},{"name":"bgwriter_lru_multiplier","setting":2}]
(1 row)
@mdevan
Copy link
Contributor

mdevan commented May 24, 2018

Thanks for your input!

Right now, the situation is that you need to refer to pg_settings inorder to interpret the "settings" field of the JSON output. Additionally, the pg_settings has to be of the same major version of Postgres as the server you ran pgmetrics on. This is because (1) values have unintuitive units (2) these units differ across versions for the same setting. To illustrate, if the settings says "max_wal_size" is "1", the actual value is either 16777216 bytes (if pg 9.5, 9.6) or 1048576 bytes (if pg 10).

For the settings value to be actually useful -- as in be able to be directly used for processing by a script without needing to consult pg_settings -- would require that the units also be considered while representing the value as a number. Byte-dimension values with non-byte units ("8kB", "MB", "kB" in pg10) should be normalized to bytes and time-dimension values ("min", "ms" in pg10) to seconds.

So, if we have a query or two (and/or Go code) that can do this, then we can add a new field (because we don't want to break the existing JSON schema) of float type (because JSON has only a "number" type, which is 64-bit float) at the same level as "setting", like this:

"settings": {
	//...
	"max_wal_size": {
		"setting": "1",
		"actual": 16777216
	},
	//...
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants