SQLFrontline Case study: NUMA Configuration

A little while ago, I was doing SQL Server consultancy work for a large organisation here in Perth, Western Australia. They had a sizeable physical SQL Server machine for their business reporting needs: 48 cores (4 sockets of 12 cores each and 384GB of RAM).

I discovered that despite the company having paid for Enterprise licenses for 48 cores, the licensing had not been applied, and so only 40 cores were actually in use! It had been that way for almost 2 years…

In fact, the situation is worse than it first seems: not only were 20% of the cores not being used, but the cores were partitioned into 4 NUMA groups, having 3 groups of 12 cores and one group of 4 cores. With the workload distributed equally over the 4 NUMA groups, this was obviously very unbalanced, and detrimental to the server’s throughput. 

In addition, ‘max degree of parallelism’ was set to its default value, which means that a parallel query that spans the 12 core and 4 core NUMA groups will very likely have threads waiting in the 12 core NUMA group and possibly incur foreign memory accesses.

This is just one of the NUMA configuration checks that SQLFrontline runs.

C#: Simple Read/Write Application Settings

I needed a simple C# class for saving writable application settings. The first attempt was to use Project -> Properties -> Settings, but only user scoped settings are writable. Here’s a simple solution: you will need to add Newtonsoft’s Json.NET (Nuget Json.Net) to your project, if you are not already using it.

using System.IO;
using Newtonsoft.Json;
...

public class AppSettingsBase<T> where T : new()
{
    private const string DefaultSettingsFilename = "settings.json";

    public void Save(string fileName = DefaultSettingsFilename)
    {
        File.WriteAllText(fileName, JsonConvert.SerializeObject(this));
    }

    public static void Save(T settings, string fileName = DefaultSettingsFilename)
    {
        File.WriteAllText(fileName, JsonConvert.SerializeObject(settings));
    }

    public static T Load(string fileName = DefaultSettingsFilename)
    {
        return (File.Exists(fileName)) 
            ? JsonConvert.DeserializeObject<T>(File.ReadAllText(fileName)) : new T();
    }
}

Add your application defined settings to your settings class (derived from the base template):

public class AppSettings : AppSettingsBase<AppSettings>
{
    public Guid ClientId = Guid.Empty;
    public List<string> ServerList = new List<string>();
    public List<string> DatabasesToExclude = new List<string>();

    public bool IsAutoUpdate = true;
    public int AutoUpdaterCheckIntervalHours = 6; 
    public DateTime LastUpdateCheckTime = DateTime.MinValue;

    ...
}

Using your settings is simple:

    AppSettings settings = AppSettings.Load();

    settings.LastUpdateCheckTime = DateTime.UtcNow;
    settings.Save();

Postgres Configuration

Configuration file locations:

Where are my postgres *.conf files?

Where is the Postgresql config file: ‘postgresql.conf’ on Windows?

  • Windows: C:\Program Files\PostgreSQL\x.x\data\postgresql.conf
  • Linux: /etc/postgresql/x.x/main/postgresql.conf

Go to bottom of .conf file, and add this line:

include postgresql.custom.conf

Then create file ‘postgresql.custom.conf’ in the same directory and place your customised configuration settings in it. Any settings set in the custom file will override those in the main config.

Navigate to pgtune and enter the required information, and pgtune will generate custom settings based upon total RAM size and intended use etc:

image

Copy the generated settings into file ‘postgresql.custom.conf’:

max_connections = 100
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 83886kB
maintenance_work_mem = 2GB
min_wal_size = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1

Restart Postgres.

Further reading on Postgres performance: http://www.craigkerstiens.com