Today I’ll demonstrate another feature in OpenResty Edge. That is, using the Metric SQL language to create brand new dynamic metrics.

Embeded image

Dynamic metrics allow the user to create custom metrics by writing SQL-like queries on the fly. OpenResty Edge automatically generates efficient code to collect and aggregate the metric data in each gateway server’s memory. The data is then reported to the web console and displayed in pretty charts in real-time.

Embeded image

Don’t worry. It uses fixed-size memory for streaming aggregation and statistics. The system uses our own optimizing compiler to generate efficient native code for each user-defined metric.

Embeded image

Let’s go to the OpenResty Edge’s Admin web console. It is our sample deployment of the console. Every user has her local deployment.

Screenshot

We use our continuing sample application for the test-edge.com domain.

Screenshot

Let’s enter this application.

Screenshot

Click this menu to enter Dynamic Metrics page.

Screenshot

There are two types of dynamic metrics. One is custom dynamic metrics defined by users.

Screenshot

The other one is standard dynamic metrics predefined by OpenResty Edge.

Screenshot

We will focus on the custom ones today. Standard ones get covered in another tutorial.

Click this button to create a new dynamic metric. This metric computes the top ten cities that send the most requests.

Screenshot

Enter a name for this metric: Client Cities.

Screenshot

Add a description: Shows the top 10 client cities.

Screenshot

Specify the data reporting interval. It is the time period for reporting aggregated statistics from the memory of the gateway servers to the Edge Log Servers. The data then gets displayed in the Edge Admin console. Just for convenience, we use 10 seconds here. Such short reporting intervals have a relatively high overhead though.

Screenshot

We use the Metric SQL language provided by the Edge product. It uses the popular SQL language syntax.

You can find the full user manual on our documentation site.

Screenshot

You will find a detailed description of this language, from Lexical Constructs, to Operators and Expressions, and the Select Query Statement.

Screenshot

We type in the Metric SQL statement for this metric. We select client cities, from all requests, grouping by cities that send requests, sorting cities in descending order. And we only keep the top 10 cities.

select client_city, count(*) as count
from reqs
group by client_city
order by count desc
limit 10

Screenshot

Save this metric.

Screenshot

This metric is saved correctly.

Screenshot

New dynamic metrics take effect immediately and do not require separate configuration releases. Now let’s send requests and see how it works.

On the terminal, we log onto a remote server in the Frankfurt city of Germany.

Screenshot

Send an HTTP request with the curl command-line utility.

curl http://test-edge.com

Screenshot

Quit the server.

Then we log onto another server in the San Jose city of the US.

Screenshot

Send a request as well.

curl http://test-edge.com

Screenshot

Exit the server.

Now let’s do this on a third server in London, UK. This time we are going to send four requests.

Screenshot

Send the first one.

curl http://test-edge.com

Screenshot

We wait for 10 seconds and then send the second and third ones. After another 10 seconds, send the fourth request.

Check out all the requests we just sent. Here is one request from Frankfurt,

Screenshot

one request from San Jose,

Screenshot

and four from London.

Screenshot

Data can be shown in a pie chart as well.

Screenshot

And also in a line chart.

Screenshot

It is what we’d expect.

You can click this button to display the Metric SQL query for this dynamic metric on this card.

Screenshot

The Metric SQL statement is shown here.

Screenshot

You can edit the existing metric using the Edit button.

Screenshot

Or remove it using the Delete button.

Screenshot

If you think the chart is too small, click the expand button to display it in a larger window.

Screenshot

We can display metrics for different time intervals, from 1 minute to 1 year.

Screenshot

Or just select an arbitrary time interval.

Screenshot

Click the Close button to shut this larger window.

Screenshot

There are also time range options on the main card. The options are fewer.

Screenshot

When enabling Auto Refresh, the metric is updated automatically with the ten-second interval we previously specified.

Screenshot

The “Clear Data” button is for clearing all the historical data and restarting the data collection.

Screenshot

Now let’s check out some dynamic metrics using real traffic. We’ll use one of our own websites powered by OpenResty Edge.

Screenshot

Type openresty.org into the search box.

Screenshot

Enter this application.

Screenshot

Enter the Dynamic Metrics page.

Screenshot

Many custom dynamic metrics already exist here. The data is displayed in various ways, like pie charts, bar charts, and line charts.

Screenshot

This one looks interesting. It shows all the requests grouped by the client IP addresses.

Screenshot

Click this button and see what the Metric SQL definition looks like.

Screenshot

Screenshot

We may tell whether our website is experiencing Denial-of-Service attacks.

Screenshot

Let’s check out this metric. It shows the top 10 countries in North America which sent the most requests.

Screenshot

Click and see its Metric SQL definition.

Screenshot

Screenshot

Here goes the chart.

Screenshot

Hopefully, you’ve found these examples helpful and fun. This is what I’d like to cover today.

If you like this tutorial, please subscribe to this blog site and/or our Vimeo account and/or our YouTube channel. Thank you!

About This Article and Associated Video

This article and its associated video are both generated automatically by our OpenResty Showman product from a simple screenplay file.

About The Author

Yichun Zhang is the creator of the OpenResty® open source project. He is also the founder and CEO of the OpenResty Inc. company. He contributed a dozen open source Nginx 3rd-party modules, quite some Nginx and LuaJIT core patches, and designed products like OpenResty Edge, OpenResty XRay, and OpenResty Showman.

Translations

We provide the Chinese translation for this article on blog.openresty.com.cn. We welcome interested readers to contribute translations in other natural languages as long as the full article is translated without any omissions. We thank them in advance.

We are hiring

We always welcome talented and enthusiastic engineers to join our team at OpenResty Inc. to explore various open source software’s internals and build powerful analyzers and visualizers for real world applications built atop the open source software. If you are interested, please send your resume to talents@openresty.com . Thank you!