Can it support the collection of cloud databases

[ghost]

I understand whether the sql_exporter must be deployed together with the server where the database is located, But can I use this component for the cloud database I am using

[burningalchemist]

Hi @hunterzhang0909, yeah, it supports cloud databases too. Just make sure that your cloud database is accessible for sql_exporter.

I had no issues using sql_exporter with AWS or Google Cloud. I bet any other clouds should work just fine.

Do you have any specific database/cloud in mind? :)

[ghost]

Error: ts=2024-05-27T02:29:43.585Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [collector=mysql,query=mysql_processlist_query] sql: Scan error on column index 1, name "user": converting driver.Value type []uint8 ("admin") to a float64: invalid syntax"

May I ask how to set field types

[burningalchemist]

Hi @hunterzhang0909,

Prometheus metric format is pretty strict (link): values have to be of float64 type, the string values (e.g. user) can only exist as metric labels.

So in your case, you need to make sure that all columns that do not return numbers are defined in the key_labels list. You also might want adjust your query to return less unnecessary columns.

I'd suggest starting with a small query, and then add more as needed. Also have a look at the examples here and here. They are for MSSQL, but the syntax is the same.

If there is no particular metric value you'd like to extract (e.g. only collecting string data), you can use static_value parameter and set it to your desired value (say 1).

[ghost]

I want to store connection information for database execution. Is it supported, json or string

[burningalchemist]

If you want to store any information in Prometheus, you have to follow its format (I posted the link above). So try maybe this snippet (change it accordingly to your requirements):

# Metric with a static value to retrieve string data.
- metric_name: mysql_data
  type: gauge
  help: 'Database server data'
  key_labels:
    # Populated from the columns of the result.
    - id
    - user
    - host
    - db
    - command
    - time
    - state
    - sql_text
    - trx_time
  # Static value, always set to `1`.
  static_value: 1
  query: |
    SELECT
    pl.ID AS id,
    pl.USER AS user,
    SUBSTRING_INDEX(pl.HOST, ':', 1) AS host,
    pl.DB AS db,
    pl.COMMAND AS command,
    pl.TIME AS time,
    pl.STATE AS state,
    IFNULL(pl.INFO, '') AS sql_text,
    IFNULL(TIMESTAMPDIFF(SECOND, it.trx_started, CURRENT_TIMESTAMP), 0) AS trx_time
    FROM information_schema.processlist pl
    LEFT JOIN information_schema.innodb_trx it ON pl.id = it.trx_mysql_thread_id

This is the best thing you can do if you don't actually need any data to be really counted/aggregated, and just want to store text - you have to store it in labels, there is no other way.