PostgreSQL App Massive FEED LOG Real-time Quality Statistics

Background

Service quality control is an important part of refined quality management. For example, we could count the water level value, such as avg, sum, and count, in certain groups and at certain fixed intervals (1 minute, 5 minutes, 10 minutes) in real time.

This article introduces a case of real-time quality monitoring of a service.

Multiple point values are written in batches as arrays. For example, we write 2,500 points in a batch.

For efficient statistics and efficient cleaning of FEED LOG details, we can use the “list” partitioned table.

At the same time, the intarray plug-in is also needed to sort the values of the array, and the aggs_for_arrays plug-in is used to calculate the water level values of the ordered array.

Even if we do not use these two plug-ins, we can also use the PG quantile aggregate function to achieve the same effect.

Hardware Environment:

ECS:
56Core
224G memory
2*Local SSD cloud disk

DB:
PostgreSQL

Install the aggs_for_arrays Plug-In

Load the Plug-In

Customize the Array Aggregate Function to Aggregate Multiple Arrays into One-Dimensional Arrays

The built-in array_agg aggregate function of PostgreSQL aggregates multiple arrays into multi-dimensional arrays, but our goal is to aggregate multiple arrays into one-dimensional arrays. Therefore, we need to customize an aggregate function.

The aggregate function uses array_cat, which has a large number of MEMCOPY, so when the amount involved is relatively large, the performance is not very optimistic (much worse than the array_agg). We can customize an arragg with better performance later.

PostgreSQL multiple arrays are aggregated into one-dimensional arrays (array_agg)

Define a function that generates an array of random values

Define a Feed Log Partitioned Detail Table

The streamed data is non-critical and does not need to be persisted, so you can choose the unlogged table, which greatly improves performance.

Example:

There are many FEED dimensions, so each FEED dimension defines a partitioned table. We assume there are 1,024 FEED dimensions and use the following method to quickly define the 1,024 partitioned tables.

Create 1,024 partitioned tables at a time, with six partitions per partitioned table.

Define the Statistical Partitioned Table Corresponding to the Feed Detail Table

Example:

Create 1,024 partitioned tables at a time, with 24 partitions per partitioned table.

Detail tables + statistical tables, a total of 30,720 tables.

For maintaining data, just truncate the earliest partition and the partition is recycled.

Define a Dynamic Write Function

For ease of stress testing, dynamic SQL is used to write data into the corresponding FEED detail table.

Example:

Stress Testing Design 1

Define Write Stress Testing 1

Dynamic; write multiple tables; array data is dynamic data

1,024 random tables, 1,500 random groups and 2 random arrays of 2,500 elements

Define Write Stress Testing 2

Dynamic; write multiple tables; array data is static data

(The overhead of the database is gen_randarr, but this array is actually passed from the application, so using a static array here can avoid the extra overhead of the database and the performance is more realistic.)

Define Write Stress Testing 3

Static; write a single table; array data is static data

Define Statistical SQL

For example, the SQL for 1-minute statistics is as follows:

It can be changed to string_agg statistics, as follows:

Define a Dynamic Statistical Function

The statistical SQL of 1 minute, 5 minutes and 10 minutes is defined as dynamic statistical SQL. For stress testing convenience, enter the table name suffix of FEED LOG, the statistical interval, and the LIMIT of the number of statistical records in each interval. For example, LIMIT can satisfy the demand of 6 million points per minute per dimension, or the demand of any number of points, playing the role of setting the stress testing threshold.

Similarly, it can also be changed to string_agg statistics.

Display statistical results

Stress Testing Design 2

Write and statistics tests are performed in parallel.

1. Statistical Stress Testing

For example, with the goal of writing 6 million elements per minute, the statistical indicators can be satisfied. Therefore, each interval set here is 2,400, 12,000, and 240,000 respectively. They represent 2400 * 2500, 12000 * 2500, and 24000 * 2500 respectively. That is, 6 million, 30 million, and 60 million.

2. Write Stress Testing

The results of the parallel test for write and statistics

1. Write 27.73 million points/s. This is equivalent to 1.664 billion elements per minute. After dividing it by 1,024 tables, each table has about 1.625 million elements per minute.

If you want to meet the write expectation of 6 million elements per minute for a single table, you need to adjust the table to 256.

2. For 1-minute statistics, the count is 383 tables per second and 23,000 tables per minute.

This exceeds expectations.

3. For 5-minute statistics, the count is 3.82 tables per second and 1,146 tables every 5 minutes.

This exceeds expectations.

4. For 10-minute statistics, the count is 1.71 tables per second and 1,126 tables every 10 minutes.

This exceeds expectations.

Combining the three indicators above, if you want to meet the writing expectation of 6 million elements per minute for a single table, you need to adjust the number of tables to 256. In other words, PG10 under the hardware specifications involved in this example can support 256 tables, and each table can write and count 6 million elements per minute.

Both statistics and writing can meet the requirements.

Stress Testing Design 3

Adjust the suffix to 256, which means that the interval for writing and statistics is 256 tables, and the test results are as follows:

Write

Statistics

Both the writing and statistics have met the indicator of 6 million per minute.

Data Maintenance and Scheduling

1. Clean up details

The next partition is truncated at a specific time. For example, partition 1 is truncated at 0 minutes. Partition 2 is truncated at 10 minutes. Partition 0 is truncated at 50 minutes.

For example:

2. Clean up statistical data

The next partition is truncated at a specific time. For example, partition 01 is truncated at 00:00. Partition 02 is truncated at 01:00. Partition 00 is truncated at 23:00.

For example:

The truncation operation cleans the file directly, which is very fast, and the cleaned data does not generate a WAL log.

Summary

1. A 56 Core RDS PG 10 can satisfy the writing and statistics of 256 feed logs. (Each feed log table has 6 million write points per minute. That is, the write and statistical throughput of the entire library is about 1.5 billion points/minute.)

2. When cleaning up historical data, you can enable scheduling tasks to truncate partitions.

3. The function of “list” partition is used. Note that in the partition of PG 10, when an operation is performed on the primary table, the corresponding locks are applied to all the sub-tables, regardless of which sub-table you finally query, write, or update. As such, there will be conflicts between writing and truncating the sub-tables. Be sure to add LOCK_TIMEOUT to truncate the sub-table.

Later, a detailed description will be provided of the difference between the native partition and pg_pathman in lock granularity.

Lock granularity difference of partition tables — pg_pathman VS native partitioned table

4. PostgreSQL multiple arrays are aggregated into one-dimensional arrays (array_agg)

Reference:https://www.alibabacloud.com/blog/postgresql-app-massive-feed-log-real-time-quality-statistics_594810?spm=a2c41.12889682.0.0

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store