tl;dr: In my last eight years working mainly on the scalability and reliability of trivago, I was involved in many outages where overloaded databases have been the root cause. Debugging these incidents is often not straightforward, primarily when multiple apps interact with the same database. Assigning a name to each database connection make a difference here. It will reduce the time to debug by multiple hours and finding the root cause faster. From the perspective of the database, you can differentiate the apps and their commands to identify the bad client.
➡️ Want to see how it works? Checkout examples for MongoDB, MSSQL / SQL-Server, MySQL, NATS, Oracle database, PostgreSQL, redis, and non-database systems like RabbitMQ or HTTP.
Why does naming your database connection make sense?
Many of the applications on this planet interact with some kind of database. In a perfect world:
- Every application has its own database
- Several applications do not share the same database
- Direct access to the stored data is shielded by an application via an API
The thing is: We don’t live in a perfect world. The reality often is:
- Several applications share one or multiple databases
- These applications are developed independently
- and receive different types of traffic patterns
This may lead to a situation where one application requests many compute resources from the database by inefficient queries. At the same time, other applications might suffer from unexpected behavior or a partial outage due to limited resources available on the database to serve the requests.
This situation is typically hard to debug because the root cause is not that obvious: Application B is failing because the database cannot answer in time due to Application A sending expensive queries.
In my last eight years working mainly on the scalability and reliability of trivago, I have seen many outages that contained similar things like:
- blocked and unresponsive Redis instances
- blocked database tables due to inefficient queries
- overloaded database servers due to raising traffic and query volumes
- services that receive a large number of HTTP requests from unknown sources
In all cases, identifying the client responsible for the trouble was the main entry point for the final solution. Connection naming has helped us multiple times during an incident to find the root cause faster.
Assigning a name to each client database connection can help to
- lower the debugging pain and reduce the time to recover in an outage
- implement rate limiting per connection name
- collect usage/resource metrics from the perspective of the database over each application
Furthermore, it is beneficial if you plan to deprecate and shut down a “legacy” application that still receives traffic. Identifying all clients can help you achieve your goal quicker. In this case, the alternatives to connection naming would be:
- Asking around “Who is using the old currency conversion service?” in the company chat
- Shutting down the service and wait until other applications crash or people start to scream why something is not working as expected
Both alternatives can be professionally solved with connection naming.
What is a good connection name?
In general, everything to identify the client application that sends the request or query. Two quick suggestions:
If you control all applications and databases, choose the name of the application itself.
If the application opens several connections, add another identifier like a package or a class name.
Like currency-conversion-app or stock-exchange-rate-importer.
If you don’t own the database or operate the external service, use the application name followed by some contact information.
Like currency-conversion - [email protected].
In case of trouble, the administrator can reach out.
How to assign a name to a database connection
This is different for each system. Below you find instructions for
Non-database systems like Message Queues or HTTP based APIs also support connection naming:
Keep in mind: Not every database supports connection naming. In such cases, often there are workarounds. For systems that support user authentication, using separate user accounts for each application can have a similar effect.
Once connection naming is supported, it is usually straightforward to implement without any engineering overhead.
In andygrunwald/your-connection-deserves-a-name @ Github I provide complete examples for different programming languages and systems on how to assign a name to a connection.
How to assign a name to your MongoDB connection
While creating a connection to MongoDB, you can provide an appName in the connection string.
dsn := "mongodb://root:[email protected]:27017/?appName=currency-conversion-app"
client, err := mongo.Connect(ctx, options.Client().ApplyURI(dsn))
To see which clients are connected, you can query db.currentOp():
or check the server logs:
{
"t": {
"$date": "2021-07-30T16:30:18.042+00:00"
},
"attr": {
"application": {
"name": "currency-conversion-app"
}
}
}
}
➡️ Checkout screenshots and code examples for MongoDB at Github.
🙏 Thanks to Andreas Braun, who pointed me to this feature in MongoDB.
How to assign a name to your MSSQL / SQL-Server connection
While creating a connection to SQL-Server, you can set an Application Name.
This is (mostly) part of the data source name (dsn)/connection string.
Here is an example in Go:
query := url.Values{}
query.Add("app name", "currency-conversion-app")
u := &url.URL{
Scheme: "sqlserver",
User: url.UserPassword("sa", "yourStrong(!)Password"),
Host: fmt.Sprintf("%s:%d", "127.0.0.1", 1433),
Path: "/",
RawQuery: query.Encode(),
}
dsn := u.String()
client, err := sql.Open("sqlserver", dsn)
To see which clients are connected (incl. their application name), you can query the sys.sysprocesses table:
SELECT
hostname,
program_name,
loginame,
cmd
FROM sys.sysprocesses
WHERE program_name != \"\";
The result should look similar to:
hostname program_name loginame cmd
-------------- ----------------------------- -------------- ----------------------
lap-dev currency-conversion-app sa AWAITING COMMAND
➡️ Checkout the code examples for MSSQL / SQL-Server at Github.
How to assign a name to your MySQL connection
While creating a connection to MySQL, you can set connection attributes.
Depending on the client library, this is either part of the data source name (DSN) or provided via a function call (mostly setting a kind of set_option).
For the application name, the connection attribute program_name is suggested.
Applications like ProxySQL and MySQL Workbench using the same feature to identify themselves against the MySQL server.
Here is an example in Python with the PyMySQL library:
client = pymysql.connect(
host='127.0.0.1',
user='root',
password='secret',
database='dummy',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor,
program_name='unit-conversion-app',
)
To see which clients are connected, you can query the performance_schema and information_schema schema:
SELECT
session_connect_attrs.ATTR_VALUE AS program_name,
processlist.*
FROM information_schema.processlist
LEFT JOIN performance_schema.session_connect_attrs ON (
processlist.ID = session_connect_attrs.PROCESSLIST_ID
AND session_connect_attrs.ATTR_NAME = "program_name"
)
The result should look similar to:
program_name | ID | USER | HOST | DB | [...]
--------------------+----+------+------------------+-------+------
unit-conversion-app | 11 | root | 172.17.0.1:56382 | dummy | [...]
➡️ Checkout screenshots and code examples for MySQL at Github.
🙏 Thanks to Johannes Schlüter, who pointed me to this feature in MySQL.
PS: This feature was added in MySQL v5.6.6 (2012-08-07).
How to assign a name to your Oracle connection
While executing a query on the Oracle database, you can provide a client name (and other client information) as additional query information.
This is called DBMS_APPLICATION_INFO.
Here is an example of how it works in Go (using godror/godror):
// Creating a connection to the oracle database
[...]
// Adding DBMS_APPLICATION_INFO
ctx := godror.ContextWithTraceTag(context.Background(), godror.TraceTag{
ClientIdentifier: "currency-conversion-app",
ClientInfo: "Demo showcase",
DbOp: "ping",
Module: "oracle/go",
Action: "main",
})
// Sending DBMS_APPLICATION_INFO
rows, err := client.QueryContext(ctx, "SELECT sysdate FROM dual")
To see which clients are connected (incl. client information) and executed query statements, you can ask the v$session and v$sqlarea tables:
SQL> SELECT sess.username, sess.client_identifier, sess.module, sess.action, area.sql_text
FROM v$session sess, v$sqlarea area
WHERE
sess.sql_address = area.address
AND sess.username = 'DEMO';
USERNAME CLIENT_IDENTIFIER MODULE ACTION SQL_TEXT
--------------- ------------------------- --------------- --------------- ---------------------------
DEMO currency-conversion-app oracle/go main SELECT sysdate FROM dual
➡️ Checkout code examples for Oracle at Github.
🙏 Thanks to Gerald Venzl, who pointed me in the right direction with his information.
How to assign a name to your PostgreSQL connection
While creating a connection to PostgreSQL, you can provide a application_name in the data source name (DSN):
dsn := "postgres://user:[email protected]/database?application_name=currency-conversion-app"
client, err := sql.Open("postgres", dsn)
To see which clients are connected with their application name, you can query the pg_stat_activity table:
postgres=# SELECT usename, application_name, client_addr, backend_type FROM pg_stat_activity;
usename | application_name | client_addr | backend_type
----------+--------------------------+-------------+-----------------
postgres | stock-exchange-rates-app | 172.17.0.1 | client backend
postgres | currency-conversion-app | 172.17.0.1 | client backend
➡️ Checkout screenshots and code examples for PostgreSQL at Github.
How to assign a name to your redis connection
After creating a connection to redis, send the CLIENT SETNAME command:
CLIENT SETNAME currency-conversion-app
Via CLIENT LIST you can see all clients, including their name:
$ CLIENT LIST
id=3 addr=172.17.0.1:62668 name=currency-conversion-app [...]
id=4 addr=172.17.0.1:62676 name=stock-exchange-rates-app [...]
➡️ Checkout screenshots and code examples for redis at Github.
How to assign a name to your NATS connection
While creating a connection to NATS, you can provide client connection name.
This is how it looks like in Go:
client, err := nats.Connect("nats://127.0.0.1:4222", nats.Name("currency-conversion-app"))
Via the NATS monitoring endpoint, you can see all connected clients, including their names:
$ curl http://127.0.0.1:8222/connz
{
[...]
"connections": [
{
"ip": "172.17.0.1",
"port": 57054,
[...]
"name": "currency-conversion-app",
"lang": "go",
"version": "1.11.0"
}
]
}
➡️ Checkout screenshots and code examples for NATS at Github.
🙏 Thanks to Waldemar Quevedo, who integrated NATS via Pull Request.
How to assign a name to your RabbitMQ connection
While creating a connection to RabbitMQ, you can provide AMQP options.
One of the options is connection_name.
This is how it looks like in Go:
config := amqp.Config{
Properties: amqp.Table{
"connection_name": "currency-conversion-app",
},
}
client, err := amqp.DialConfig("amqp://guest:[email protected]:5672/", config)
In the UI of Rabbit under the Connection tab, you can see all connected clients, including their names:
➡️ Checkout screenshots and code examples for RabbitMQ at Github.
How to assign a name to your HTTP connection
The User-Agent HTTP header field was explicitly created for this use-case:
[…] string that lets servers and network peers identify the application, operating system, vendor, and/or version […]
How the value looks is not specified. Here are a few suggestions:
- Mozilla Developer Network:
<product> / <product-version> <comment> - MediaWiki:
clientname/version (contact information e.g. username, email) - GitHub API: your GitHub username, or the name of your application
You know a system that supports connection naming?
I would love to hear from you. Contribute to andygrunwald/your-connection-deserves-a-name @ GitHub via an Issue or Pull Request.