This document will help us set up a Flask application to measure the latency of a MySQL query.
- Python and pip installed on your system.
- Flask and MySQL connector libraries installed.
- MySQL server installed and running.
- Prometheus and Grafana installed and running.
Running a MySQL Docker container involves using Docker commands to download the MySQL image and start a container based on that image. Below are the steps to achieve this:
First, you need to pull the MySQL Docker image from Docker Hub. Open your terminal and run the following command:
docker pull mysql:latest
This command will download the latest version of the MySQL image.
After pulling the image, you can run the MySQL container. Use the following command:
docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=my_db -p 3307:3306 -d mysql:latest
To verify that the MySQL container is running, use the following command:
docker ps
You should see an output listing the running containers, including mysql-container
.
You can connect to the MySQL server running in the container using a MySQL client. For example, you can use the MySQL command-line client:
docker exec -it mysql-container mysql -u root -proot
Enter the root password you set earlier when prompted.
Use the following commands to create table:
USE my_db;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Add users in the table:
INSERT INTO users (username, email) VALUES ('user1', '[email protected]');
INSERT INTO users (username, email) VALUES ('user2', '[email protected]');
INSERT INTO users (username, email) VALUES ('user3', '[email protected]');
Use the following commands to see the created users:
SELECT * FROM users;
Expected result:
mysql> DROP TABLE IF EXISTS users;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM users;
ERROR 1146 (42S02): Table 'my_db.users' doesn't exist
mysql>
mysql> CREATE TABLE users (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(50) NOT NULL,
-> email VARCHAR(50) NOT NULL,
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> SELECT * FROM users;
Empty set (0.00 sec)
mysql> INSERT INTO users (username, email) VALUES ('user1', '[email protected]');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO users (username, email) VALUES ('user2', '[email protected]');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO users (username, email) VALUES ('user3', '[email protected]');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM users;
+----+----------+-------------------+---------------------+
| id | username | email | created_at |
+----+----------+-------------------+---------------------+
| 1 | user1 | [email protected] | 2024-06-11 17:11:00 |
| 2 | user2 | [email protected] | 2024-06-11 17:11:08 |
| 3 | user3 | [email protected] | 2024-06-11 17:11:12 |
+----+----------+-------------------+---------------------+
3 rows in set (0.00 sec)
python -m venv venv
venv/Scripts/activate
pip install Flask prometheus_client mysql-connector-python
from flask import Flask
import mysql.connector
import time
from prometheus_client import start_http_server, Summary
from prometheus_client import Counter, generate_latest
app = Flask(__name__)
# Create a metric to track time spent and requests made
REQUEST_TIME = Summary('request_processing_seconds', 'Time spent processing request')
REQUEST_COUNTER = Counter('request_processing_count', 'Number of requests processed')
@app.route('/metrics')
def metrics():
return generate_latest()
def get_db_connection():
connection = mysql.connector.connect(
host='localhost',
user='root',
password='root',
database='my_db'
)
return connection
@REQUEST_TIME.time()
@app.route('/')
def query():
start_time = time.time()
REQUEST_COUNTER.inc()
connection = get_db_connection()
cursor = connection.cursor()
cursor.execute("SELECT * FROM users")
result = cursor.fetchall()
cursor.close()
connection.close()
end_time = time.time()
latency = end_time - start_time
REQUEST_TIME.observe(latency)
return f"Query latency: {latency} seconds. Result: {result}"
if __name__ == '__main__':
# Start up the server to expose the metrics.
start_http_server(8000)
# Run the Flask app
app.run(port=5000)
python app.py
Create or modify the Prometheus configuration file to scrape the Flask application's metrics endpoint.
global:
scrape_interval: 15s
scrape_configs:
- job_name: 'flask_app'
metrics_path: '/metrics'
static_configs:
- targets: ['localhost:8000']
./prometheus --config.file=prometheus.yml
- Open Grafana in your web browser (default: http://localhost:3000).
- Log in (default credentials: admin/admin).
- Go to Configuration -> Data Sources -> Add data source.
- Select Prometheus.
- Set the URL to http://localhost:9090 (default Prometheus address).
- Click Save & Test.
- Go to Create -> Dashboard.
- Add a new panel.
- In the panel configuration, select Prometheus as the data source.
- Use the metric request_processing_seconds to visualize the latency.
- Open http://localhost:5000 in browser.
- Open http://localhost:9090 in your browser.
- Query request_processing_seconds to verify that the metrics are being scraped.
- Open your Grafana dashboard.
- Ensure that the panel displays the latency of the MySQL queries.
Expected results:
By following these steps, you will have a fully functioning monitoring setup for the latency of MySQL queries in a Flask application using Prometheus and Grafana without using Docker containers.