oracledb_exporter
oracledb_exporter copied to clipboard
iamseth
"Unable to convert count value to int" errors when using histograms
I'm submitting a ...
- [x] bug report
- [ ] feature request
What is the current behavior?
When reading histogram data from V$EVENT_HISTOGRAM_MICRO view on a reasonably busy DB the exporter reports errors:
ts=2023-04-19T16:38:08.326Z caller=main.go:375 level=error msg="Unable to convert count value to int" metric=data metricHelp="Histogram - sum total of all values in the data field." value=2.53221949e+08
...
ts=2023-04-19T16:38:08.347Z caller=main.go:375 level=error msg="Unable to convert count value to int" metric=data metricHelp="Histogram - sum total of all values in the data field." value=1.077715588e+09
ts=2023-04-19T16:38:08.351Z caller=main.go:375 level=error msg="Unable to convert count value to int" metric=data metricHelp="Histogram - sum total of all values in the data field." value=6.780668e+06
It looks like the oci8.go Oracle driver decides to treat big numbers as floats (or even as a String ) in scientific notation. And the int converter can not deal with that.
If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem
Added this config to custom-metrics.toml, connection to Oracle must connect to CDB :
[[metric]]
context = "event_histogram"
request = '''
with rowgen as (
SELECT
case when rownum = 24 then 4294967295
else power(2,rownum -1 )
end le
FROM dual
CONNECT BY LEVEL <= 24
), cross as (
select se.con_id,se.event,total_waits, se.time_waited_micro,
le wait_time_micro,
sum(coalesce(eh.wait_count,0)) over (partition by se.con_id,se.event order by r.le) wait_count
from ( v$system_event se
cross join rowgen r)
left join V$EVENT_HISTOGRAM_micro eh
on eh.event=se.event
and eh.con_id = se.con_id
and eh.wait_time_micro = r.le
--where se.event='rdbms ipc message'
)
select sys_context('userenv','CDB_NAME') CDB_NAME,nvl(c.name,'*') pdb_name,con_id,
event,
le_1 ,
le_2 ,
le_4,
le_8,
le_16,
le_32,
le_64,
le_128,
le_256,
le_512,
le_1024,
le_2048,
le_4096,
le_8192,
le_16384,
le_32768,
le_65536,
le_131072,
le_262144,
le_524288,
le_1048576,
le_2097152,
le_4194304,
le_4294967295 as "COUNT",
TIME_WAITED_MICRO as "DATA"
from cross
pivot (
sum(wait_count)
for wait_time_micro in (
1 as le_1 ,
2 as le_2 ,
4 as le_4,
8 as le_8,
16 as le_16,
32 as le_32,
64 as le_64,
128 as le_128,
256 as le_256,
512 as le_512,
1024 as le_1024,
2048 as le_2048,
4096 as le_4096,
8192 as le_8192,
16384 as le_16384,
32768 as le_32768,
65536 as le_65536,
131072 as le_131072,
262144 as le_262144,
524288 as le_524288,
1048576 as le_1048576,
2097152 as le_2097152,
4194304 as le_4194304,
4294967295 as le_4294967295
)
)
left join V$containers c Using(con_id)
'''
labels = ["cdb_name","pdb_name","event"]
metricsdesc = { data = "Histogram - sum total of all values in the data field." }
metricstype = { data = "histogram", count = "counter" }
metricsbuckets = { data = { le_1 = "1", le_2 = "2", le_4 = "4", le_8 = "8", le_16 = "16", le_32 = "32", le_64 = "64", le_128 = "128", le_256 = "256", le_512 = "512", le_1024 = "1024", le_2048 = "2048" , le_4096 = "4096" ,le_8192 = "8192",le_16384 = "16384",le_32768 = "32768",le_65536 = "65536",le_131072 = "131072",le_262144 = "262144",le_524288 = "524288", le_1048576= "1048576", le_2097152 = "2097152", le_4194304 = "4194304" } }
What is the expected behavior?
The exporter should return the results of the Query as Histogram to Prometheus.
What is the motivation / use case for changing the behavior?
I would like to visualize histogram information in grafana from Oracle Events like this:
Please tell us about your environment:
Current git version built locally as 0.4.4-ora21.8.linux-amd64
I did manage to get this working using Go-Ora instead of go-oci8, but I did not want to send a create a PR without discussing the matter first, maybe there are other issues with Go-ora that I'm not aware of. I commited the changes to my fork at https://github.com/blitzkopf/oracledb_exporter , they are quite simple.
Hi @blitzkopf,
Thanks for your feedback. I was already considering to switch to this driver to avoid the driver installation from Oracle.
It seems that @schmikei is already working on this topic in this PR: https://github.com/iamseth/oracledb_exporter/pull/312
Guess we can have a look at how you have done the modification but as far as I can tell, it seems to be quite straighforward :)
@blitzkopf the swith have been made by @schmikei (thanks again !)
I think you should give a try to the last version.
Yannig
I'm unable to run with any custom-metric.toml, I think it's this issue: #321
ts=2023-05-10T12:40:08.780Z caller=collector.go:352 level=info /home/vt368/prome/custom-metrics.toml="has been changed. Reloading metrics..."
panic: assignment to entry in nil map
goroutine 24 [running]:
github.com/observiq/oracledb_exporter/collector.(*Exporter).checkIfMetricsChanged(0xc0002b2000)
/home/vt368/repos/clone/oracledb_exporter/collector/collector.go:353 +0x3e5
github.com/observiq/oracledb_exporter/collector.(*Exporter).scrape(0xc0002b2000, 0xc0001823c0)
/home/vt368/repos/clone/oracledb_exporter/collector/collector.go:268 +0x585
github.com/observiq/oracledb_exporter/collector.(*Exporter).Collect(0xc0002b2000, 0x0?)
/home/vt368/repos/clone/oracledb_exporter/collector/collector.go:180 +0xf4
github.com/observiq/oracledb_exporter/collector.(*Exporter).Describe(0x0?, 0xc0001b0a80)
/home/vt368/repos/clone/oracledb_exporter/collector/collector.go:158 +0xc7
github.com/prometheus/client_golang/prometheus.(*Registry).Register.func1()
/home/vt368/go/pkg/mod/github.com/prometheus/[email protected]/prometheus/registry.go:280 +0x2b
created by github.com/prometheus/client_golang/prometheus.(*Registry).Register
/home/vt368/go/pkg/mod/github.com/prometheus/[email protected]/prometheus/registry.go:279 +0x19b
I also encountered this type of problem "Unable to convert count value to float" when using custom indicators.
After many attempts, I found a solution. Below I will briefly describe the custom indicator I want to implement:
Count the number of rows in the formtable_main_1342 table：
At first I executed SQL in the sqlplus window: select count(*) from formtable_main_1342; It was successful
So my custom toml configuration file looks like this
[[metric]]
context = "formtable_main_1342"
request = "select count(*) from formtable_main_1342"
metricsdesc = {count="formtable_main_1342 table count."}
I applied the custom configuration file and started the container, but found an error message and could not get the data of the custom indicator. The reason was: "Unable to convert count value to float"
Therefore, I am very confused. The data returned by sqlplus sql execution is integer type, not floating point data.
After testing, I found that the title in the sql query needs to be consistent with the definition in metricsdesc. Since the title of my sql query is COUNT()*, and the definition in my metricsdesc is count=, I need to use the as instruction to change the title of the sql output to COUNT
Therefore, the correct configuration should be as follows:
[[metric]]
context = "formtable_main_1342"
request = "select count(*) as count from formtable_main_1342"
metricsdesc = {count="formtable_main_1342 table count."}
Since my custom indicator is completed, I hope it will be helpful to you~
