[Feature] Support collecting database connection pool metric

[sunOnly]

Search before asking

• I had searched in the issues and found no similar feature requirement.

Description

Support the collection of Druid, hikaricp and other database connection pool measurement data, and support multiple instances

Use case

Used to monitor the usage of database connection pool

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[wu-sheng]

Please follow #8433 and finish local POC.
We prefer labeled value metrics rather than multiple metrics.

Once you did, please provide MAL script, UI configuration and UI preview here before submitting any pull request.

[sunOnly]

package org.apache.skywalking.apm.agent.core.pool.druid;


import org.apache.skywalking.apm.agent.core.logging.api.ILog;
import org.apache.skywalking.apm.agent.core.logging.api.LogManager;
import org.apache.skywalking.apm.agent.core.meter.MeterFactory;
import org.apache.skywalking.apm.agent.core.pool.DatabaseParserHelper;

import javax.management.MBeanServer;
import javax.management.ObjectName;
import java.lang.management.ManagementFactory;
import java.util.Hashtable;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;

public class DruidRegister {
    private static final ILog LOGGER = LogManager.getLogger(DruidRegister.class);

    protected MBeanServer mbeanServer = ManagementFactory.getPlatformMBeanServer();
    protected DatabaseParserHelper databaseParser = new DatabaseParserHelper();
    private static final String ERROR_ATTRIBUTE = "unknown";


    public boolean register() {
        try {
            LOGGER.info("DruidRegister register start");
            Hashtable<String, String> table = new Hashtable<String, String>();
            table.put("type", "DruidDataSource");
            table.put("id", "*");
            ObjectName pooledDataSourceObjectName = new ObjectName("com.alibaba.druid", table);
            Set<ObjectName> objectNameSet = mbeanServer.queryNames(pooledDataSourceObjectName, null);

            if (objectNameSet == null || objectNameSet.isEmpty()) {
                return false;
            }

            Map<String, Integer> datasources = new LinkedHashMap<String, Integer>();

            int index = 0;
            for (ObjectName objectName : objectNameSet) {
                String jdbcUrl = getStringAttribute(objectName, "Url");
                DatabaseParserHelper.Database datasource = databaseParser.parseDatabase(jdbcUrl);
                String key = getConnection(datasources, datasource.toString());
                LOGGER.debug("key {}", key);

                MeterFactory.gauge("active_count", () -> getIntegerAttribute(objectName, "ActiveCount").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                MeterFactory.gauge("pooling_count", () -> getIntegerAttribute(objectName, "PoolingCount").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                MeterFactory.gauge("idle_connection", () -> getIntegerAttribute(objectName, "ActiveCount").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                MeterFactory.gauge("lock_queue_length", () -> getIntegerAttribute(objectName, "LockQueueLength").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                MeterFactory.gauge("max_wait_thread_count", () -> getIntegerAttribute(objectName, "MaxWaitThreadCount").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                MeterFactory.gauge("commit_count", () -> getLongAttribute(objectName, "CommitCount").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                MeterFactory.gauge("connect_count", () -> getLongAttribute(objectName, "ConnectCount").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                MeterFactory.gauge("connect_error_count", () -> getLongAttribute(objectName, "ConnectErrorCount").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                MeterFactory.gauge("create_error_count", () -> getLongAttribute(objectName, "CreateErrorCount").doubleValue()).tag("index", String.valueOf(index)).tag("datasource", key).build();
                index++;
            }
            LOGGER.info("DruidRegister register end");
        } catch (Exception e) {
            LOGGER.error("Druid Register fail", e);
        }
        return true;
    }

    protected String getConnection(Map<String, Integer> datasources, String key) {
        Integer index = datasources.get(key);

        if (index == null) {
            datasources.put(key, 0);

            return key;
        } else {
            index++;

            datasources.put(key, index);
            return key + '[' + index + ']';
        }
    }

    protected Integer getIntegerAttribute(ObjectName objectName, String attribute) {
        try {
            Integer value = (Integer) mbeanServer.getAttribute(objectName, attribute);
            return value;
        } catch (Exception e) {
            return 0;
        }
    }

    protected Long getLongAttribute(ObjectName objectName, String attribute) {
        try {
            Long value = (Long) mbeanServer.getAttribute(objectName, attribute);
            return value;
        } catch (Exception e) {
            return 0L;
        }
    }

    protected String getStringAttribute(ObjectName objectName, String attribute) {
        try {
            return (String) mbeanServer.getAttribute(objectName, attribute);
        } catch (Exception e) {
            return ERROR_ATTRIBUTE;
        }
    }

}

package org.apache.skywalking.apm.agent.core.pool;

import org.apache.skywalking.apm.agent.core.boot.BootService;
import org.apache.skywalking.apm.agent.core.boot.DefaultImplementor;
import org.apache.skywalking.apm.agent.core.boot.DefaultNamedThreadFactory;
import org.apache.skywalking.apm.agent.core.logging.api.ILog;
import org.apache.skywalking.apm.agent.core.logging.api.LogManager;
import org.apache.skywalking.apm.agent.core.pool.druid.DruidRegister;
import org.apache.skywalking.apm.util.RunnableWithExceptionProtection;

import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledFuture;
import java.util.concurrent.TimeUnit;

/**
 * The <code>PoolService</code> represents a timer, which register Druid monitor metrics
 * {@link DruidRegister}
 */
@DefaultImplementor
public class PoolService implements BootService, Runnable {
    private static final ILog LOGGER = LogManager.getLogger(PoolService.class);
    private volatile ScheduledFuture<?> registerMetricFuture;
    private DruidRegister druidRegister;

    @Override
    public void prepare() throws Throwable {
        druidRegister = new DruidRegister();
    }

    @Override
    public void boot() throws Throwable {
        registerMetricFuture = Executors.newSingleThreadScheduledExecutor(
                new DefaultNamedThreadFactory("PoolService-Register"))
                .scheduleAtFixedRate(new RunnableWithExceptionProtection(
                        this,
                        new RunnableWithExceptionProtection.CallbackWhenException() {
                            @Override
                            public void handle(Throwable t) {
                                LOGGER.error("PoolService Register metrics failure.", t);
                            }
                        }
                ), 0, 30, TimeUnit.SECONDS);
    }

    @Override
    public void onComplete() throws Throwable {

    }

    @Override
    public void shutdown() throws Throwable {
        registerMetricFuture.cancel(true);
    }

    @Override
    public void run() {
        druidRegister.register();
    }
}

[wu-sheng]

You need to follow that discussion. We prefer the final metrics as labeled values. Then you could list all metrics on the dashboard by using one metric name, which means we only need one row in the database per timebucket + entity.

Right now, if you have 10 metrics, then one service causes 10 rows in the database per timebucket.

[sunOnly]

You need to follow that discussion. We prefer the final metrics as labeled values. Then you could list all metrics on the dashboard by using one metric name, which means we only need one row in the database per timebucket + entity.

Right now, if you have 10 metrics, then one service causes 10 rows in the database per timebucket.

final metrics as labeled values，. Now multiple data sources use this scheme. How to deal with multiple data sources in this way?

[wu-sheng]

Why do you set labels as 0,1,2,3,4,5? Have no clue what do you mean to do so. Counts of idle, using, max are as same as undertow discussion. I could recommend you to take a look at the issue I pointed to you, it has shown a good demo to you.

[sunOnly]

Why do you set labels as 0,1,2,3,4,5? Have no clue what do you mean to do so. Counts of idle, using, max are as same as undertow discussion. I could recommend you to take a look at the issue I pointed to you, it has shown a good demo to you.

It is modified as follows:
instance add global Datasource metric

View specific detail of Datasource metric

[wu-sheng]

For the database, I m not sure whether it is a good idea to provide a whole dashboard.
I think we should include datasource name in the label, then one item component should be enough.

[wu-sheng]

The final label should be datasource name + status(idle/max) in the instance page.

[sunOnly]

The final label should be datasource name + status(idle/max) in the instance page.
Is that the effect？