Press "Enter" to skip to content

Category: Administration

Methods for Monitoring MongoDB

Published 2023-06-15 by Kevin Feasel

Hadi Fadlallah does some watching:

Several utilities included with the MongoDB distribution provide statistics about instances’ performance and activity. Usually, they are used to diagnose problems and assess the functioning of a system.

The MongoDB database tools are a suite of command-line utilities for working with MongoDB. Starting with MongoDB 4.4, these tools are released separately from the MongoDB Server. To install them, we should follow the guidelines provided in the following documentation.

Click through for information on command-line tools, built-in web services, and one third-party tool for the job.

Comments closed

Clearing Backup History on SQL Server

Published 2023-06-14 by Kevin Feasel

Rich Benner reminds us to clear that backup history:

One thing we regularly come across with a new client is large system databases. Something that SQL Server should do but doesn’t is clearing up system tables. One of the more common issues is a very large backup history which causes bloat in the msdb database. Let’s find out how to clear backup history.

These tables store information about backups that you have taken, which is extremely useful … for a time. After that useful time, it’s just unnecessary data that is taking up space on your server. There’s a number of related tables that store backup history, and it’s not simple to delete data from them all. Most don’t have dates stored in the table so you have to join them together and make sure you delete in the correct order.

Read on for a built-in procedure, as well as a warning. It’s interesting to see, sometimes, just how much space msdb is taking up with stuff people don’t realize. Backup history is usually one of the offenders, along with SQL Agent history and (especially on pre-2016 instances) SSIS history.

Comments closed

Missing Index Hints and Index Rebuilds

Published 2023-06-13 by Kevin Feasel

Forrest McDaniel abuses that poor server:

The prod version of this issue manifested as 1-3 minutes of SCH-M blocking for index ops, yet only on a single server; I couldn’t replicate it anywhere else. Of course, bad blocking meant nasty timeouts, and that in turn meant I was reluctant to run live diagnostics. Better to just stop index rebuilds. One does not take down production in order to test weird SQL behavior.

An important clue came from a tiny table with a rebuild, and is visible in the whoisactive screenshot above. Why would such a small rebuild need so much CPU? That worker was definitely doing something – the question was what.

Read on to find out the answer, a repro script (that you should not run in your own production environment!), and what you can do about it.

Comments closed

Roles and Domains in Microsoft Fabric

Published 2023-06-09 by Kevin Feasel

Marc Lelijveld explains two key concepts:

Microsoft Fabric is out there for a few weeks now. With the release of Fabric, a new concept in line with data-mesh architectures became available in Fabric, or Power BI if you will. With the introduction of Domains, we have a new level of controls added next to existing roles. In this blog I will further elaborate on the levels of control that are available today and provide a clear overview of these different levels.

There’s going to be a bit of nomenclature adjustment for people who have spent most of their time in Synapse or other platforms moving to Fabric. If you’ve already spent most of your time in Power BI, this shift is probably a little easier.

Comments closed

Automating Azure SQL DB Maintenance Tasks

Published 2023-06-09 by Kevin Feasel

Tracy Boggiano reminds us that we still need to administer Azure SQL DB databases:

I’ve been using Azure SQL Database for quite some while and have set up it in many various ways to run Ola’s Index Optimize and Statistics Updates on them.  All of these have seemed way too complicated probably because I was setting them up once, not again for several more months or a year.  Well with my new job, I have over 20 subscriptions with various Azure SQL Servers in them so it was time to streamline at least knowing what I was doing.  No matter what I googled on the Internet I never did find one source that walked me step by step on each thing I needed to know to set this up.  So hopefully this will cover everything.

Click through for the step-by-step process.

Comments closed

Updating Database Mail Settings via SP

Published 2023-06-08 by Kevin Feasel

Chad Callihan doesn’t have time for the UI:

If you need to make changes to multiple servers, you may want to avoid the GUI approach and all of the clicks that come with it. In that case, msdb contains a stored procedure called sysmail_update_account_sp that might be a more efficient approach. Let’s take a quick look at sysmail_update_account_sp and what it can do for you.

Read on to see how the procedure works and what you can do.

Comments closed

Killing Multiple YARN Applications at Once

Published 2023-06-07 by Kevin Feasel

The Big Data in Real World team doesn’t have time to mess around:

If you work with Apache Hadoop, you may find yourself needing to kill multiple YARN applications at once. While you can kill them one by one using the yarn application -kill command, this can be a tedious and time-consuming process. Fortunately, there is a faster way to kill multiple YARN applications at once using the yarn application command in combination with awk.

Click through to see how. I will say, though, remembering some of these sed+grep+awk solutions I’ve written in the past makes me happy that Powershell is object-based…

Comments closed

Three-Node Postgres HD Cluster with pg_cirrus

Published 2023-06-07 by Kevin Feasel

Salman Ahmed wants to be highly available:

We are thrilled to announce the release of pg_cirrus! First of all, you might be wondering what “cirrus” means. The term refers to the thin and wispy clouds that are often seen at high altitudes.

pg_cirrus is a simple and automated solution to deploy highly available 3-node PostgreSQL clusters with auto failover. It is built using Ansible and to perform auto failover and load balancing we are using pgpool.

Read on to see how it works. It’s also licensed under GPLv3, so it’s not only highly available but also freely available.

Comments closed

Enabling Postgres Auditing

Published 2023-06-05 by Kevin Feasel

Athar Ishteyaque enables an extension:

The PostgreSQL Audit Extension (or pgaudit) provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL.

The goal of a PostgreSQL audit is to provide the tools needed to produce audit logs. These logs are often required to pass certain government, financial, or ISO certification audits.

I am kind of curious what the performance impact of this extension is.

Comments closed

Data Inconsistency in Postgres HA Clusters

Published 2023-06-01 by Kevin Feasel

Umair Shahid gives us an overview:

While PostgreSQL is known for its robustness, scalability, and reliability, data inconsistency can occur in PostgreSQL clusters, which can cause issues and impact the overall performance of the system. In this blog, we’ll define data inconsistency in PostgreSQL clusters, discuss the challenges it poses, its causes, and provide some tips on how to prevent and resolve it if it occurs.

Click through for the article.

Comments closed