Monthly Archives: August 2016

Using Extended Events to Visualize Availability Group Replication Internals

SQL 2014 Service Pack 2 was recently released by Microsoft and there is a ton of great new features and enhancements in this release.This isn’t just a collection of bug fixes…there’s some serious value in this Service Pack. Check out the full list here. One of the key things added in this Service Pack is an enhancement of the Extended Events for AlwaysOn Availability Group replication.

Why are the new Availability Group Extended Event interesting?

If you’ve used Availability Groups in production systems with high transaction volumes you know that replication latency can impact your availability. If you want to brush up on that check out our blog posts on AG Replication Latency, Monitoring for replication latency, and issues with the DMVs when monitoring. These new extended events add insight at nearly every point inside your Availability Group’s replication. More importantly they also include duration. So using these Extended Events we can pinpoint latency inside our Availability Group replication.

Here’s the list and description of the new Extended Events:

  • hadr_log_block_group_commit – Log block group commit processing duration
  • log_block_pushed_to_logpool – Log block start push to log pool
  • log_flush_start – Asynchronous log write start
  • hadr_log_block_compression – Log block compression processing duration
  • hadr_capture_log_block – Primary has captured a log block
  • hadr_capture_filestream_wait
  • ucs_connection_send_msg – UCS transport connection sent message
  • hadr_log_block_send_complete – After a log block message has been sent. This event is only used for fail points
  • log_flush_complete – Reports that synchronous send is complete
  • hadr_receive_harden_lsn_message – When a new hardened LSN from the secondary
  • hadr_db_commit_mgr_harden – Transaction commit harden result from Hadron Commit management
  • hadr_transport_receive_log_block_message – Fires when we’re receiving new log block message
  • hadr_log_block_decompression – Log block decompression processing duration
  • hadr_apply_log_block – Secondary is going to append a log block to the log
  • hadr_send_harden_lsn_message – Crafting a message to send containing a new hardened LSN on a secondary.  Test only
  • hadr_lsn_send_complete – After an LSN message has been sent.
The source for this list and it’s descriptions is Amit Banerjee’s HADR Virtual Chapter Talk and KB3173156

Using Extended Events to visualize Availability Group Replication Internals

Using these Extended Events, we’re able to get some insight into the replication internals of our Availability Groups. Like I described a second ago, we can use these to measure the duration of each event and sift out performance issues in our replication. But we can also use them to get a better understanding of what goes into Availability Group replication, let’s see how.
 
Here’s the steps I used to generate the chart below:
  1. Let’s add an Event Session with all of these new Events above to the primary and secondary replicas of a two replica synchronous Availability Group
  2. Then with the sessions running, insert only one row on the primary replica. This will cause the AG to replicate the data change event and trigger the new Extended Events on both the primary and secondary replicas
  3. Stop the Event Sessions
  4. Open the Event file in SSMS and View Target Data on both the primary and secondary replicas
  5. Order the Event data by timestamp
  6. Merge the event data together based on the timestamp (I did this in a spreadsheet)
With this list we have the entire replication sequence of Events for that single insert replicating data from the primary to the secondary from the perspective of both the primary and secondary. I took that information and made the following graphic for you visualizing what it takes to replicate data in a synchronous Availability Group.

AG Synchrnous Replication Internals

 Figure 1: Availability Group Replication Extended Events

From Figure 1, let’s walk through the events…

On the primary

  • The first event that fires is hadr_log_block_group_commit starting things off. It’s my understanding that this initializes the needed code and program state for AG replication
  • Then up next is log_block_pushed_to_logpool. This fires when a log block is copied to the log pool. The log pool is a special memory location used to store log blocks that may need to be read again, since it’s in memory it keeps the readers from doing a physical IO on the transaction log
  • Then log_flush_start, this fires when log blocks are flushed to disk, normal write ahead logging
  • Once the log block has been copied into the log pool and flushed to disk, it’s read from the log pool and compressed then the hadr_log_block_compression event fires
  • After it’s compressed the AG “captures” the log block for replication to the secondary with hadr_capture_log_block 
  • Then builds and sends the message to the secondary and ucs_connection_send_msg fires
  • Once the message is sent the hadr_log_block_send_complete event fires
  • We mark the synchronous send complete with log_flush_complete

On the secondary

  • On the other side of the fence, basically the inverse occurs. We mark the receipt of the message with hadr_transport_receive_log_block_message
  • The log block is decompressed and hadr_log_block_decompression fires
  • Then the log block is appended to the local log buffer and hadr_apply_log_block fires
  • Then the log block is copied to the local log pool and hadr_pushed_to_logpool fires
  • Next, the asynchronous log write happens and log_flush_start marks that start of that
  • And once the asynchronous log write finishes, log_flush_complete fires
  • Once hardened locally we need to craft a message to send to the primary marking the the LSN is hardened and hadr_send_harden_lsn_message fires
  • ucs_connection_send_msg then transmits fires when the message is sent to the primary
  • hadr_lsn_send_complete marks the fires marking the end of sending the message

Back on the primary

  • hadr_receive_harden_lsn_message fires marking that the secondary has acknowledging receipt of the send LSN
  • And to wrap it all up hadr_db_commit_mgr_harden marks the end of the log block replication for this sequence

A few important notes…

  • Many of these Extended Events fire at different points and multiple times during replication under different conditions. There’s a field called mode, which is an integer value, that marks various conditions for the events. I have yet to decode what each value of mode is for all of the events but will update this blog as I get some insight.
     
  • It’s very important to note that this example highlights an insert into an Availability Group with a primary and only one secondary configured in synchronous Availability Mode. The code path and the subsequent Extended Events that fire can change based on Availability Mode (sync/async), replication health, and many other conditions that can impact replication.

I hope that I’ve shown you some interesting concepts that you can use to help troubleshoot your Availability Group replication using Extended Events. This post, highlights how you can use Extended Events to get some insight into the the internals of Availability Group replication.

If you have any questions about this or your Availability Group configuration or performance please feel free to email me at aen@centinosystems.com

Please follow me on Twitter @nocentino to keep up with new blog posts

References

Great references on the internals of the log pool – https://sqlactions.com/2014/03/31/how-it-works-logpool/

HADR Virtual Chapter – http://hadrvc.sqlpass.org/Home.aspx?EventID=5304

Setting PowerShell as your default Linux shell

In this post we’re going set PowerShell as your default Linux shell.

What is a shell?

In Linux systems you’re given options, tons of options, you can set, reconfigure, add/remove almost anything that you want. And one of those options is your shell. The shell is the thing that you interact with when you’re typing commands at the command line. Different shells have different behaviors and characteristics. It’s a very personal choice. For ages I’ve been a fan of bash.

This week Microsoft announced that they’ve open sourced PowerShell and made it available on Linux and MacOS. Awesome! Want some tips on getting started check out my post here.

PowerShell is…a shell!

One thing that people seem to forget about PowerShell is that it’s…a shell not just a scripting language! So we can use it as our primary command line interface to interact with our system if we choose. This is the key point as to why this is so significant, one shell…many platforms. Don’t forget Microsoft brought bash to Windows too ;)

Changing Your Default Shell

In this post I’m going to show you how to change your default shell on your Linux system. It’s a pretty simple change, Linux systems of course give you several options, chsh is one of them.

If you want to use cash you’re going to need to add /usr/bin/powershell to the list of shells in /etc/shells…so let’s crack open that file and add that line. /etc/shells is a protected file, so you’ll need to elevate your privileges with vi…

sudo vi /etc/shells

Here’s the content of my /etc/shells on a RHEL 7.2after making the edit.

/bin/sh

/bin/bash

/sbin/nologin

/usr/bin/sh

/usr/bin/bash

/usr/sbin/nologin

/usr/bin/powershell


Now with that out of the way we can use chsh to change our shell.

chsh -s /usr/bin/powershell


chch will ask for your password, enter that then log out and back in, and you’ll go right into PowerShell.

mbp:~ aen$ ssh psdemo@172.16.94.150

psdemo@172.16.94.150’s password: 

Last login: Sat Aug 20 11:42:23 2016 from 172.16.94.1

Windows PowerShell 

Copyright (C) 2016 Microsoft Corporation. All rights reserved.

 

PS /home/psdemo> 

What Just Happened?

So what did cash to under the hood? Well, it changed the settings in /etc/passwd for you. A single line of your /etc/passwd file represents one user’s information. In here from left to right, separated by colons, you’ll find username, shadow password, user id, group id, home directory then finally shell. 

Here’s a snippet from mine:

PS /home/aen> Get-Content /etc/passwd                                                                                                                                       

root:x:0:0:root:/root:/bin/bash

demo:x:1000:1000:demo:/home/demo:/bin/bash

psdemo:x:1001:1001:psdemo:/home/psdemo:/usr/bin/powershell


You can see for the user psdemo the shell is now /usr/bin/powershell. Our demo user’s shell is set to /bin/bash. 

If you’re up for it you can edit /etc/passwd directly, to do that you’ll need super user rights. Which you may or may not have. Using chsh you won’t need super user rights. It uses a concept called setuid which allows a user to execute a command with escalated privileges.

If you want to learn more about shells, setuid and command execution on Linux systems check out my Pluralsight course on Understanding and Using Enterprise Linux 7

Getting Started With PowerShell on Linux

Getting PowerShell on Linux

Well it’s not just an announcement, you can actually get PowerShell on Linux and MacOS right now from GitHub – here!

Installing PowerShell

Once you’ve downloaded an installation file you can use RPM or apt to install the package. If you’re on a Mac…well just double click on the package!

yum install powershell-6.0.0_alpha.9-1.el7.centos.x86_64.rpm

If you’re on Ubuntu, you’ll need a little more stuff read this.

Running PowerShell

We’ll there’s really not much to that, just type powershell at the command prompt an you’re off and running!

[root@localhost ~]# powershell 

Windows PowerShell 

Copyright (C) 2016 Microsoft Corporation. All rights reserved.

 

PS /root> Get-Content /etc/redhat-release                                                                                   

Red Hat Enterprise Linux Server release 7.2 (Maipo)

PS /root> 


The implications of this are huge, but to boil it down to it’s simplest essecense, we now can use one language to manage our data centers…and that’s awesome!

I’ll be blogging a lot more about this topic in the near future, but for now enjoy!

SQL Server, Persistent Memory on NVDIMMs and DAX

Paradigm Shift!

What do I mean by that? Every once in a while a technology comes along and changes the way things are done, moves the bar…well last week Microsoft released a Channel 9 video on persistent memory using NVDIMMs and DAX on Windows 2016…then combining it with SQL Server! This is one of those technologies that moves the bar! Check it out here.

Why is this important?

Relational databases like SQL Server use a transaction log to ensure the durability of the transactional operations to the database. This is so it can ensure its data is consistent in the event of a system failure. SQL Server uses a logging protocol called write ahead logging (WAL). This means that the data change operations must be written to stable, persistent storage before the process can change data in the database file…so this means our database throughput is at the mercy of the performance of the device the changes (log records) are written to.

Another facet of this is disk latency. Historically this has been terribly inefficient, especially in the case of spinning HDDs due to seek time and write latency. Disk latency has gotten much better recently with SSDs and NVMe. To combat disk latency, SQL Server doesn’t perform a disk write for each and every log record. SQL Server writes log records to what’s called a log buffer in main memory, once the log buffer is full it or a transaction commits (or aborts) it flushes that buffer to disk in sector aligned IO operations. This means your transactions have to wait for the disk IO to complete before they’re considered hardened to disk. This is a core bottleneck in RDBMS systems.

What changed?

Well, in NVDIMMs we have a DIMM, which is normally used for main memory, is now backed with a battery backed flash array. This means we can have access to stable storage at main memory speeds, think less than 10 microseconds, not milliseconds. An order of magnitude performance gain! NVDIMMs have two modes of operation, storage mode and memory mode. Storage mode will present the device as a block device to your OS. Memory mode will present this as a memory mapped device. Windows Server 2016 has the drivers to deal with both modes. In the first case you’ll get a block device and all the code that goes along with making a block IO request, the advantage of this mode is no application code changes, it’s just a block device like any other. In the second mode, memory mode, it presents byte addressable memory to the OS as storage, but you may need to change code to take full advantage of this. So, Microsoft has developed a feature called DirectAcces (DAX) in Windows Server 2016 to present memory mode storage to the OS bypassing traditional block IO code. This is where the insanity ensues! You’ll literally have stable storage available to your applications at main memory speeds, committing to stable storage is now just a memory copy!!! Watch the video for performance numbers!

How this will impact you?

Well, in the video Lindsey Allen highlights a code change in SQL Server that modifies how the log buffer writes data. Log writes are immediately considered hardened when committed to the log buffer. We no longer have to wait for the disk IO to complete, side stepping disk latency and IO altogether. This leads to a 2x improvement in the demo application in the video. These tests compare NVMe and DAX, and NVMe is PCI-express attached flash which is as good as it gets for persistent storage today (well until memory mode NVDIMMS take over). You’ll notice that CPU spikes during their load tests, this isn’t an uncommon issue when you remove your storage bottlenecks from your system.

What’s it going to cost?

Looking online, HPE has a 8GB NVDIMM available for $800 at online wholesalers, this is in DIMM form factor. Some are built directly into the motherboard, others are on cards.

Questions that I have?

  1. Foreign Memory Access – In a NUMA system is there be a performance penalty or inconsistent performance when threads need to read or write to a foreign memory area. Log writing is single threaded, so this should be something that we can manage. It does go multithreaded in 2016, so the best thing to do here is test your workload and measure.
  2. Hypervisor Support – can we leverage this awesomeness in a virtual machine?
  3. Error Detection – how to errors percolate back up into the OS/SQL so we know when something goes wrong. Likely using standard disk IO alerts will apply here.

Well hopefully I’ve convinced you how big of a deal this is and you’re as excited as I am!

Please let reach out to me if you like to know more about this – aen@centinosystems.com

New Pluralsight Course – LFCE: Advanced Linux Networking

My new course LFCE: Advanced Linux Networking in now available on Pluralsight here!

This course targets IT professionals that design and maintain RHEL based enterprises. It aligns with the Linux Foundation Certified System Administrator (LFCS) and Linux Foundation Certified Engineer (LFCE) and also Redhat’s RHCSA and RHCE certifications The course can be used by both the IT pro learning new skills and the senior system administrator preparing for the certification exam

This course will dive deeper into the internals of networking, giving the you insight into how things work under the hood in Linux based networks.

Let’s take your LINUX sysadmin skills to the next level and get you started on your LFCS/LFCE learning path.

If you’re in the SQL Server community and want to learn how to use the command line on Linux, this course is for you too! You heard that Microsoft is going to release a version of SQL Server for Linux right, if not…read this!

The modules of the course are:

  • Network Topology Fundamentals and the OSI Model
  • Internet Protocol – Addressing and Subnetting Fundamentals
  • Internet Protocol – ARP and DNS Fundamentals
  • Internet Protocol – Routing Packets
  • Routing Packets with Linux
  • Investigating TCP Internals
  • Troubleshooting Network Issues

Pluralsight Redhat Linux

Check out the course at Pluralsight!