Press "Enter" to skip to content

Category: Administration

Scenarios Leading to Autovacuum in Postgres

Published 2024-11-11 by Kevin Feasel

Semab Tariq covers autovacuum:

However, one side effect of MVCC is the creation of dead tuples—old versions of data rows that are no longer needed but still occupy space. 

Dead tuples also lead to a phenomenon known as table bloat, which refers to the excessive unused space in a table caused by dead tuples that haven’t been cleaned up, resulting in inefficient storage and reduced performance

To address the issues of dead tuples and table bloat, autovacuum comes into play. It’s an automatic process designed to clean up these dead tuples and maintain optimal database performance.

Read on for a list of scenarios that can trigger autovacuum.

Comments closed

Configuring Azure Database Watcher

Published 2024-11-08 by Kevin Feasel

Rod Edwards configures Azure Database Watcher to watch databases in Azure:

First off, at the time of writing, this is still in Preview, and is only for Azure SQL PaaS offerings, namely Azure SQL DB and SQL Managed Instance, so if you’re out of luck if you’re using SQL on VM. Expect this to be added at some point in future, its number 2 on the published roadmap.

Preview or GA…the long and short of it is that it allows collection of performance AND config data into a central datastore for ALL of your SQL MI and Azure DB estate. With all of the data in one place, then dashboards are connected to here for easier estate-wide visualisations.

Read on for a step-by-step guide on configuring it. But also pay attention to Rod’s note near the end that troubleshooting setup is a pain—there aren’t many useful logs that show exactly why it isn’t working.

Comments closed

Verification of PostgreSQL Checksums with WAL-G

Published 2024-11-08 by Kevin Feasel

Marat Bogatyrev talks checksums with us:

A key aspect of maintaining backup integrity is understanding data checksums. Without proper checksum validation, detecting data corruption becomes virtually impossible. Therefore, we will start with The Importance of Data Checksums.

Read on to learn more about how PostgreSQL, in particular, deals with checksums. SQL Server has similar functionality for pages and backup files and it’s worth knowing about and enabling checksum capabilities in both database platforms.

Comments closed

Configuring SQL Server Alerts in SQLMonitor

Published 2024-11-07 by Kevin Feasel

Ajay Dwivedi has an update to SQLMonitor:

If you are responsible for managing & monitoring SQLServers, then it is mandatory to have monitoring and alerting for critical issues of your SQL Servers.

For this purpose, open source SQLMonitor now has built-in Alert Engine. With the capability of this alert engine, alerts can be sent to Slack & Email to various teams. The following are some advantages –

Read on to see some of those capabilities and how to set things up.

Comments closed

Converting SQL Audit FileTime to DateTime Format

Published 2024-11-06 by Kevin Feasel

Patrick Keisler helps a customer:

One of my customers recently wanted to rename each of the SQL audit files will the datetime stamp of when it was created. I explained to them the filename already contains a datetime stamp. While it does not look like a typical timestamp, it is based on the Windows Filetime data structure that is a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (UTC). Nonetheless, they still wanted a traditional datetime stamp in the file name.

Read on to see how. I can understand the displeasure in adding redundancy to a filename, though I also understand the reasoning from the customer’s point of view: FileTime isn’t human-readable in any meaningful way.

Comments closed

Administrative Tasks in Azure Database for MySQL Flexible Server

Published 2024-11-04 by Kevin Feasel

Rajendra Gupta gives us a checklist:

The tip, Azure Database for MySQL, explored various deployment models for Azure MySQL and their features. Further, we deployed an Azure MySQL flexible server using the Azure portal. This tip will explore the tasks and operation items required for a MySQL flexible server. Let’s check it out.

Read on for notes regarding what Microsoft gives you up-front as well as what you, as an administrator, would still need to cover.

Comments closed

Installing PostgreSQL Offline

Published 2024-11-01 by Kevin Feasel

Semab Tariq performs an installation:

Many companies, choose to store their databases in secure, closed environments—machines without internet access or outside the cloud. This is often done to maintain tight control over sensitive data and to meet strict security requirements. However installing PostgreSQL in a restricted, offline environment can be a real challenge, as it limits access to typical installation tools. 

Recently, I worked on a client project with a similar setup—a secure, offline environment without internet access—where we needed to install and configure PostgreSQL from scratch. If you’re facing the challenge of setting up PostgreSQL in a closed environment, this blog will guide you through the process step-by-step.

It turns out to be pretty straightforward, so long as you can start from a machine with internet access.

Comments closed

Timeouts Attempting to Open Connections from High-Thread Applications

Published 2024-10-31 by Kevin Feasel

Jose Manuel Jurado Diaz works through a customer issue:

Recently, I worked on a service request that a customer application reported the following error connecting to the database: “Timeout attempting to open the connection. The time period elapsed prior to attempting to open the connection has been exceeded. This may have occurred because of too many simultaneous non-pooled connection attempts.“. 

Following, I would like to share the experience learned here.

The issue isn’t extremely common, but it does happen, especially when applications don’t use connection pooling.

Comments closed