I’m new with influx and I want to ask you for help. I think that i have some problems with configurtion or sql server plugin for telegraf have bug.
Few days ago i saw a lot of errors in telegraf log: 2019-11-08T13:36:50Z E! [outputs.influxdb] when writing to xxx: received error partial write: max-values-per-tag limit exceeded (100000/100000): measurement="sqlserver_requests"
I’m started to dig deeper and i found that sqlserver_request measurement have very high cardinality:
name: sqlserver_requests
count
-----
194552
from 204K of Total! And the most of it is command tag(or column, tag key - i don’t know yet how to name it)
I’m use influx only to monitor VM’s with MS SQL server.
7 vm’s
20 mayby 30 DB’s
I’m know that i can change limit for max-values-per-tag but it’s not a solution for me.
Can I do something to reduce series cardinality for sqlserver_requests??
Hello @Skiba01,
Welcome and thanks for your question. Have you come across this post yet?
In it there is:
This is a configuration setting . It does not cause the database to stop, it causes points to stop being written that have new tag values. Its important to understand the number of series being written.
The major difference between tags and fields is that tags are indexed and fields are not. I would suggest this article on line protocol as an introduction to our data model.
I’m read a lot about tags, series, keys, sets etc. and i think that i start understand it.
I know that i can change max-values-per-tag, but this is not a point.
I think that 194 000 series for only one measurement is to much (Totaly i have 204 000 for only 7 virtual machines).
I want try to reduce series cardinality beacuse we want to use influx for long term
To be precise, error log saying:
received error partial write: max-values-per-tag limit exceeded (100000/100000): measurement=“sqlserver_requests” tag=“statement_text”
SqlServer plugin creates series for tag “statement_text” for wchich values are queries from ms sql.
The question is, is it posible to reduce series (maybe dont create tag for queries in ms sql)?
I know that i can exclude this measuremnt in telegraf config file, but this is just a workaround
You could use tagexclude to filter the statement_text tag, however sometimes reducing tags can cause multiple metrics to be merged together in the database.
I might be able to give better advice if I see the metrics, would you be able to run this and attach the output, maybe no more than 10 lines:
Yeah sure, I am sending as a file because the result is not readable (long lines) input-filter.txt (14.8 KB)
I try to explore data by chronograf and I don’t see applications for results taged as statement_text.
I attach lines from SqlServer plugin that are responsible for Statement_text tag.
1401 , (SUBSTRING(qt.text, r.statement_start_offset / 2 + 1,
1402 (CASE WHEN r.statement_end_offset = -1
1403 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
1404 ELSE r.statement_end_offset
1405 END - r.statement_start_offset) / 2)
1406 ) AS statement_text
I opened and issue for the statement_text tag (#6678) on GitHub, based on your data. I also see that the query_plan field is going to be a problem (#6679). I’ll ping the authors of the query and see if we can come to a decision on how to resolve the issue.
I recommend excluding this query until they are resolved, without statement_text the queryies will overwrite each other.
