Monitoring Database Connections
The connection monitoring function can monitor the following JDBC metrics: number of connections enabled by applications, number of connections disabled by applications, number of abnormal disconnections, database access volume, client CPU usage, memory usage, uplink and downlink transmission rates, and network delay, jitter, and packet loss rate between applications and databases. This example demonstrates how to use the connection monitoring function of the JDBC driver.
Prerequisites for code running: Add the gaussdbjdbc.jar package as required. For example, if you use an IDE to run code, you need to add the gaussdbjdbc.jar package to the local IDE.
// There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security. // In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation). // You need to change the values of $ip, $port, and database. import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBMonitorTest { public static void main(String[] args){ String driver = "com.huawei.gaussdb.jdbc.Driver"; String username = System.getenv("EXAMPLE_USERNAME_ENV"); String passwd = System.getenv("EXAMPLE_PASSWORD_ENV"); String sourceURL = "jdbc:gaussdb://$ip:$port/database?dbMonitor=true&loggerLevel=debug&loggerFile=dbMonitor.log"; try { // Load the database driver. Class.forName(driver).newInstance(); } catch (Exception e) { e.printStackTrace(); } Connection conn = null; Statement statement = null; try { // Connect to the database in non-encryption mode. conn = DriverManager.getConnection(sourceURL, username, passwd); System.out.println("Connection succeed!"); // Create a table. statement = conn.createStatement(); String createTableQuery = "CREATE TABLE IF NOT EXISTS mytable (id INT PRIMARY KEY, name VARCHAR(50))"; statement.executeUpdate(createTableQuery); // Insert data. String insertQuery = "INSERT INTO mytable (id, name) VALUES (1, 'John')"; statement.executeUpdate(insertQuery); // Query data. String selectQuery = "SELECT * FROM mytable "; ResultSet resultSet = statement.executeQuery(selectQuery); while (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); System.out.println("id: " + id + ", name: " + name); } // Delete the table. String dropTableQuery = "DROP TABLE IF EXISTS mytable"; statement.executeUpdate(dropTableQuery); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } try { Thread.sleep(10000); } catch (InterruptedException e) { throw new RuntimeException(e); } } }
The execution result of the preceding example is as follows:
Connection succeed! id: 1, name: John
You can view the following connection monitoring information in the dbMonitor.log file:
Nov 23, 2023 10:30:54 AM com.huawei.gaussdb.jdbc.qos.DataProcess saveQosResult FINE: { "Destination host:port" : "ip:port",-- Server IP address and port. "Delay" : "1.00 ms",-- Network delay. "Jitter" : "0.04ms",-- Network jitter. "Loss" : "0%",-- Network packet loss rate. "DownloadSpeed" : "0.395Mbps",-- Downlink rate of the network. "UpLoadSpeed" : "0.498Mbps"-- Uplink rate of the network. } Nov 23, 2023 10:30:56 AM com.huawei.gaussdb.jdbc.CollectDBData$CollectDBThread run FINE: { "openCount": "1",-- Number of times that the application enables database connections. "closeCount": "1",-- Number of times that the application disables database connections. "abortedCount": "0",-- Number of abnormal disconnections. "visitCount": "12",-- Number of access requests from applications to the database. "cpuUsage": "20.39%",-- CPU usage of the client. "memoryUsage": "98.32%"-- Memory usage of the client. }
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot