Most transactional applications have a 70:30 RW profile. However, some special cases have extremely high write IO profiles.

I ran storage IO tests in the ap-southeast-2 (Sydney) AWS region to simulate IRIS database IO patterns and throughput similar to a very high write rate application.

The test aimed to determine whether the EC2 instance types and EBS volume types available in the AWS Australian regions will support the high IO rates and throughput required.

Minimal tuning was done in the operating system or IRIS (see Operating System and IRIS configuration below).

A short post for now to answer a question that came up. In post two of this series I included graphs of performance data extracted from pButtons. I was asked off-line if there is a quicker way than cut/paste to extract metrics for mgstat etc from a pButtons .html file for easy charting in Excel.

See: - Part 2 - Looking at the metrics we collected

pButtons compiles data it collects into a single html file to make it easier to send to WRC and review the collated data.

In the previous parts (1, 2) we talked about globals as trees. In this article, we will look at them as sparse arrays.

A sparse array - is a type of array where most values assume an identical value.

In practice, you will often see sparse arrays so huge that there is no point in occupying memory with identical elements. Therefore, it makes sense to organize sparse arrays in such a way that memory is not wasted on storing duplicate values.

In some programming languages, sparse arrays are part of the language - for example, in J, MATLAB. In other languages, there are special libraries that let you use them. For C++, those would be Eigen and the like.

Globals are good candidates for implementing sparse arrays for the following reasons:

Continuing on with providing some examples of various storage technologies and their performance profiles, this time we looked at the growing trend of leveraging internal commodity-based server storage, specifically the new HPE Cloudline 3150 Gen10 AMD processor-based single socket servers with two 3.2TB Samsung  PM1725a NVMe drives.  

While reviewing our documentation for our ^pButtons (in IRIS renamed as ^SystemPerformance) performance monitoring utility, a customer told me: "I understand all of this, but I wish it could be simpler… easier to define profiles, manage them etc.".

After this session I thought it would be a nice exercise to try and provide some easier human interface for this.

The first step in this was to wrap a class-based API to the existing pButtons routine.

I was also able to add some more "features" like showing what profiles are currently running, their time remaining to run, previously running processes and more.

The next step was to add on top of this API, a REST API class.

With this artifact (a pButtons REST API) in hand, one can go ahead and build a modern UI on top of that.

For example -

I saw someone recently refer to ECP as magic. It certainly seems so, and there is a lot of very clever engineering to make it work. But the following sequence of diagrams is a simple view of how data is retrieved and used across a distributed architecture.

For more more on ECP including capacity planning follow this link: Data Platforms and Performance - Part 7 ECP for performance, scalability and availability

To start

• There are three globals on disk ^A, ^B and ^C.
• Global ^B equals "B"
• There is one Data server and two or more Application servers.

There are three things most important to any SQL performance conversation:  Indices, TuneTable, and Show Plan.  The attached PDFs includes historical presentations on these topics that cover the basics of these 3 things in one place.  Our documentation provides more detail on these and other SQL Performance topics in the links below.  The eLearning options reinforces several of these topics.  In addition, there are several Developer Community articles which touch on SQL performance, and those relevant links are also listed.

There is a fair amount of repetition in the information listed below.  The most important aspects of SQL performance to consider are:

1. The types of indices available
2. Using one index type over another
3. The information TuneTable gathers for a table and what it means to the Optimizer
4. How to read a Show Plan to better understand if a query is good or bad

A practical guide to using the tools PERFMON and MONLBL.

Introduction

When investigating performance problems, I often use the utilities ^PERFMON and ^%SYS.MONLBL to identify exactly where in the application pieces of code are taking a long time to execute. In this short paper I will describe an approach that first uses ^PERFMON to identify the busiest routines and then uses ^%SYS.MONLBL to analyze those routines in detail to show which lines are the most expensive.

The details of ^PERFMON and ^%SYS.

I am often asked to review customers' IRIS application performance data to understand if system resources are under or over-provisioned.

This recent example is interesting because it involves an application that has done a "lift and shift" migration of a large IRIS database application to the Cloud. AWS, in this case.

A key takeaway is that once you move to the Cloud, resources can be right-sized over time as needed. You do not have to buy and provision on-premises infrastructure for many years in the future that you expect to grow into.

Continuous monitoring is required. Your application transaction rate will change as your business changes, the application use or the application itself changes. This will change the system resource requirements. Planners should also consider seasonal peaks in activity. Of course, an advantage of the Cloud is resources can be scaled up or down as needed.

For more background information, there are several in-depth posts on AWS and IRIS in the community. A search for "AWS reference" is an excellent place to start. I have also added some helpful links at the end of this post.

AWS services are like Lego blocks, different sizes and shapes can be combined. I have ignored networking, security, and standing up a VPC for this post. I have focused on two of the Lego block components;

• Compute requirements.
• Storage requirements.

Quotes (1NF/2NF/3NF)^ru:

Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else). The same value can be atomic or non-atomic depending on the purpose of this value. For example, “4286” can be

• atomic, if its denotes “a credit card’s PIN code” (if it’s broken down or reshuffled, it is of no use any longer)
• non-atomic, if it’s just a “sequence of numbers” (the value still makes sense if broken down into several parts or reshuffled)

This article explores the standard methods of increasing the performance of SQL queries involving the following types of fields: string, date, simple list (in the $LB format), "list of <...>" and "array of <...>".

Not everyone knows that InterSystems Caché has a built-in tool for code profiling called Caché Monitor.

Its main purpose (obviously) is the collection of statistics for programs running in Caché. It can provide statistics by program, as well as detailed Line-by-Line statistics for each program.

Using Caché Monitor

Let’s take a look at a potential use case for Caché Monitor and its key features. So, in order to start the profiler, you need to go to the terminal and switch to the namespace that you want to monitor, then launch the %SYS.MONLBL system routine:

zn "<namespace>"
do ^%SYS.

In this short article we talk about how to get Yape running in a docker container to avoid having to setup python on your machine.

It's been a while since the last article in this series, so let's recap quickly.

We talked about using matplotlib to create a basic graph. Afterwards we introduced dynamic graphs using bokeh. In the 3rd part we talked about generating heatmaps using monlbl data.

A common theme in the feedback I got over various channels was the difficulty setting up an environment to run any of these.

It seems like yesterday when we did a small project in Java to test the performance of IRIS, PostgreSQL and MySQL (you can review the article we wrote back in June at the end of this article). If you remember, IRIS was superior to PostgreSQL and clearly superior to MySQL in insertions, with no big difference in queries.

Well, shortly after @Dmitry Maslennikov told me "Why don't you test it from a Python project?" Well, here is the Python version of the tests we previously performed using the JDBC connections.

A More Industrial-Looking Global Storage Scheme

In the first article in this series, we looked at the entity–attribute–value (EAV) model in relational databases, and took a look at the pros and cons of storing those entities, attributes and values in tables. We learned that, despite the benefits of this approach in terms of flexibility, there are some real disadvantages, in particular a basic mismatch between the logical structure of the data and its physical storage, which causes various difficulties.

This is a follow-up to Murray's article for scripting pButtons data extraction on Unix/MacOS:     Extracting pButtons data to CSV for UNIX/MacOS

###Extracting pButtons data to a CSV file on Windows

PowerShell scripting was used in this article because it is available by default on Windows platforms.

The script, Extract_pButtons.ps1, will search the given pButtons file until it finds the marker for the beginning of the section. The section will be printed line-by-line, from the header to the line before the section end marker.

YASPE is the successor to YAPE (Yet Another pButtons Extractor). YASPE has been written from the ground up with many internal changes to allow easier maintenance and enhancements.

YASPE functions:

• Parse and chart InterSystems Caché pButtons and InterSystems IRIS SystemPerformance files for quick performance analysis of Operating System and IRIS metrics.
• Allow a deeper dive by creating ad-hoc charts and by creating charts combining the Operating System and IRIS metrics with the "Pretty Performance" option.
• The "System Overview" option saves you from searching your SystemPerformance files for system details or common configuration options.

YASPE is written in Python and is available on GitHub as source code or for Docker containers at:

• https://github.com/murrayo/yaspe

A customer recently asked if IRIS supported OpenTelemetry as they where seeking to measure the time that IRIS implemented SOAP Services take to complete.

[Background]

InterSystems IRIS family has a nice utility ^SystemPerformance (as known as ^pButtons in Caché and Ensemble) which outputs the database performance information into a readable HTML file. When you run ^SystemPerformance on IRIS for Windows, a HTML file is created where both our own performance log mgstat and Windows performance log are included.

What is APM?

I am talking about Application Performance Management at global summit, and several people have asked what that means so it is time for a bit of an explanation.

APM or Application Performance Management (sometimes referred to as Application Performance Monitoring) has a very good (if complicated) explanation on Wikipedia but to me it just means looking at performance from the users’ point of view and the level of service provided to them.

Using the CSP Page Statistics

Application Performance Management

Introduction

A key part of Application Performance Management (APM) is recording the activity and performance of user activity. For many web applications the closest you can get to this is to record the CSP pages or CSP based services being dispatched.

If the pages or service names are meaningful and they indicate the business activity being performed the CSP page statistics can be very useful in building up a historical record of activity, performance and resource usage.

A few years ago, I was teaching the basics of our %UnitTest framework during Caché Foundations class (now called Developing Using InterSystems Objects and SQL). A student asked if it was possible to collect performance statistics while running unit tests. A few weeks later, I added some additional code to the %UnitTest examples to answer this question. I’m finally sharing it on the Community.

What is %SQLRESTRICT

%SQLRESTRICT is a special %FILTER clause for use in MDX queries in InterSystems IRIS Business Intelligence. Since this function begins with %, it means this is a special MDX extension created by InterSystems. It allows users to insert an SQL statement that will be used to restrict the returned records in the MDX Result Set. This SQL statement must return a set of Source Record IDs to limit the results by. Please see the documentation for more information.

Why is this useful?

This is useful because there are often times users want to restrict the results in their MDX Result Set based on information that is not in their cubes. It may be the case that this information may not make sense to be in the cube. Other times this can be useful when there is a large set of values you want to restrict. As mentioned before, this is not a standard MDX function, it was created by InterSystems to handle cases were queries were not performing well or cases that were not easily solved by existing functions.

Motivation

I didn't know about ObjectScript until I started my new job. Objectscript isn't actually a young programming language. Compared to C++, Java and Python, the community isn't as active, but we're keen to make this place more vibrant, aren't we?

I've noticed that some of my colleagues are finding it tricky to get their heads around the class relationships in these huge projects. There aren't any easy-to-use modern class diagram tool for ObjectScript.

Related Work

I have tried relavant works:

- InterSystems class view:
1. https://github.

Code coverage and performance optimization of code has come up a bunch of times already, so most of you should already be aware of the SYS.MONLBL utility. Often a visual approach to looking at code is much more intuitive than pure numbers, which is pretty much the whole point of this article series. This time we will take a slight excursion away from python and its tools and are going to explore generating heatmaps from ^%SYS.MONLBL reports.

As a quick reminder a heatmap is just a specific visualization tool, which gives us an overview of data where colors represent a certain value.

Dynamic PoolSize (DPS) Experiment

Purpose:

Enhance Ensemble or IRIS production so it can dynamically allocate pool size for adapter-based components based on their utilization.

Sometimes, an unexpected traffic volume occurs, and default pool size allocated to production components may become a bottleneck. To avoid such situations, I created a demonstrator project some 2 years ago to see, whether it would be possible and feasible to modify production, so it allowed for dynamically modifying its components per their load.

InterSystems has been at the forefront of database technology since its inception, pioneering innovations that consistently outperform competitors like Oracle, IBM, and Microsoft. By focusing on an efficient kernel design and embracing a no-compromise approach to data performance, InterSystems has carved out a niche in mission-critical applications, ensuring reliability, speed, and scalability.

Our team is reworking an application to use REST services that use the same database as our current ZEN application. One of the new REST endpoints uses a query that ran very slowly when first implemented. After some analysis, we found that an index on one of the fields in the table greatly improved performance (a query that took 35 seconds was now taking a fraction of a second).

We saw this improvement on our development system and our test system. However, when we moved the code to the production system, the query still took “forever”. What went wrong?

When there's a performance issue, whether for all users on the system or a single process, the shortest path to understanding the root cause is usually to understand what the processes in question are spending their time doing.  Are they mostly using CPU to dutifully march through their algorithm (for better or worse); or are they mostly reading database blocks from disk; or mostly waiting for something else, like LOCKs, ECP or database block collisions?

Tools to help answer the questions above have always been available in various forms.

Like hardware hosts, virtual hosts in public and private clouds can develop resource bottlenecks as workloads increase. If you are using and managing InterSystems IRIS instances deployed in public or private clouds, you may have encountered a situation in which addressing performance or other issues requires increasing the capacity of an instance's host (that is, vertically scaling).

One common reason to scale is insufficient memory.

I was benchmarking the populate utils when I noticed something strange. Consider this simple benchmarking method:

ClassMethod runBench(count As %Integer = 300000)
{
		s types=["name","ssn","company","title","phone","city","street","zip","mission","state","color","product","string","integer","float"]
		//s types=["name","ssn"]
		s iter=types.%GetIterator()
		while iter.%GetNext(.key,.value){
			s start=$P($ZTS,",",2)
			s opt={}
			s opt.type=value
			for i=1:1:count{
				s res=##class(DataGen.Generator).