jonny.donker@gmail.com

Qlik Replicate – The saga of replicating to AWS Part 4 – Does stream size matter?

Continuing our ongoing Qlik Replicating story of trying to replicate a DB2/zOS database to AWS RDS Postgres.

We made small improvements; but nothing substantial to reach the TPS for which we were aiming. I was at my experience end of what I knew and decided to reach for professional help.

We have a support relationship with IBT; who helped us out with the initial set up QR in our organisation. But recently we have been self-resolving our own problems and have not been using their help. Now this was suitable time to ask for their help.

IBT has always been helpful when we have asked for assistance. Another handy aspect with the relationship is that IBT has a quick support relationship with Qlik. If they don’t know the answer; they can get the answer easily from Qlik.

IBT asked us to collect the usual data; diagnostic logs, source and target DB metrics and QR server core metrics. Nothing looked under duress, so IBT dived into the nitty and gritty details of the diagnostic packs.

Their techs noticed that our outgoing stream buffers were full. This means that the changes were coming in faster than were getting sent out to the destination. IBT suggested to try increasing the size of the outgoing stream.

Without going through the details of this step; here is a Qlik knowledge base article on Increasing the outgoing stream queue.

“She’s breaking up – I can’t hold her”

We started off with:

"stream_buffers_number" : 5,
"stream_buffer_size" : 10,

It was a marginal improvement. Measured in a thimble full of performance improvement. Still nowhere near the TPS we needed.

IBT asked us to increase the two variables in small increments of “stream_buffers_number” + 5 and “stream_buffer_size” + 10. With each increase there was a minuscule improvement.

But more worrying with each increase; the QR task was using more memory to the point that increasing the buffer size was unsustainable with the resources on the server. Even if increasing the buffer variables and the gained TPS was linear relationship; we would need a very beefy server to reach 300 TPS.

So again, it was a little gain; and with all the added “Little gains” over the past few fix iterations we were still no closer to our needed 300 TPS.

Increasing the buffer variables might be helpful if you are close to your TPS and trying to get over the last hurdle. But since we’re so far behind; we had to look for another solution.

May 15, 2024 by jonny.donker@gmail.com Qlik Replicate 0

Qlik Replicate – The saga of replicating to AWS Part 3 – Wireshark!

Continuing on the story

After concluding that the low TPS is not resulting from poor query performance; our attention was turned to the network latency between our OnPrem Qlik system and the AWS RDS database.

First, I asked the networks team if there were any suspect networking components between our on-premise’s Qlik server and the AWS DB. Anything like IPS, QOS, bandwidth limitation components that could explain the slowdown.

I also asked the cloud team if they can find anything as well.

It was a high hope for them to find anything; but since they are the SMEs in the area, it was worth asking the question.

As expected, they did not find anything.

But the Network team did come back with a couple of pieces of information:

The network bandwidth to the AWS was wide enough and we were not reaching its capacity.
It is a 16ms – 20ms round trip from our Data centre to the AWS data centre.

Loaction… Location…

Physically the distance to the AWS data centre is 700Km.

Unfortunately, AWS set up a closer data centre in the past few years, which is only 130Km away. We are not currently set up to use this new region yet.

The Network team gave me permission to install wire shark on our OnPrem Qlik server and our AWS EC2 Qlik server.

From both servers with psql I connected to the AWS RDS database and updated one row; capturing the traffic using Wireshark.

I lined up the two results from the different servers to see if there was anything obvious

Wireshark results

(ip.src == ip.of.qlik.server and ip.dst == ip.of.aws.rds) or (ip.src == ip.of.aws.rds and ip.dst == ip.of.qlik.server)

SEQ	Source	Destination	Protocol	Length	Info	On Prem 2 RDS	EC2 2 RDS	Difference (sec)	% of difference
1	Qlik server	RDS DB	TCP	66	58313 > 5432 [SYN, ECE, CWR] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM	0	0	0.000	0%
2	RDS DB	Qlik server	TCP	66	5432 > 58313 [SYN, ACK] Seq=0 Ack=1 Win=26883 Len=0 MSS=1460 SACK_PERM WS=8	0.019	0.001	0.018	10%
3	Qlik server	RDS DB	TCP	54	58313 > 5432 [ACK] Seq=1 Ack=1 Win=262656 Len=0	0.000	0.000	0.000	0%
4	Qlik server	RDS DB	PGSQL	62	>?	0.000	0.005	-0.005	-3%
5	RDS DB	Qlik server	TCP	60	5432 > 58313 [ACK] Seq=1 Ack=9 Win=26888 Len=0	0.018	0.000	0.018	10%
6	RDS DB	Qlik server	PGSQL	60	<	0.001	0.001	0.000	0%
7	Qlik server	RDS DB	TLSv1.3	343	Client Hello	0.004	0.004	0.001	0%
8	RDS DB	Qlik server	TLSv1.3	220	Hello Retry Request	0.021	0.001	0.021	12%
9	Qlik server	RDS DB	TLSv1.3	455	Change Cipher Spec, Client Hello	0.003	0.001	0.002	1%
10	RDS DB	Qlik server	TLSv1.3	566	Server Hello, Change Cipher Spec	0.023	0.005	0.019	11%
11	RDS DB	Qlik server	TCP	1514	5432 > 58313 [ACK] Seq=680 Ack=699 Win=29032 Len=1460 [TCP segment of a reassembled PDU]	0.000	0.000	0.000	0%
12	RDS DB	Qlik server	TCP	1514	5432 > 58313 [ACK] Seq=2140 Ack=699 Win=29032 Len=1460 [TCP segment of a reassembled PDU]	0.000	0.000	0.000	0%
13	RDS DB	Qlik server	TCP	1514	5432 > 58313 [ACK] Seq=3600 Ack=699 Win=29032 Len=1460 [TCP segment of a reassembled PDU]	0.000	0.000	0.000	0%
14	RDS DB	Qlik server	TLSv1.3	394	Application Data	0.000	0.000	0.000	0%
15	Qlik server	RDS DB	TCP	54	58313 > 5432 [ACK] Seq=699 Ack=5400 Win=262656 Len=0	0.000	0.000	0.000	0%
16	Qlik server	RDS DB	TLSv1.3	112	Application Data	0.003	0.002	0.001	1%
17	Qlik server	RDS DB	TLSv1.3	133	Application Data	0.000	0.000	0.000	0%
18	RDS DB	Qlik server	TCP	60	5432 > 58313 [ACK] Seq=5400 Ack=836 Win=29032 Len=0	0.018	0.000	0.018	10%
19	RDS DB	Qlik server	TLSv1.3	142	Application Data	0.001	0.008	-0.007	-4%
20	RDS DB	Qlik server	TLSv1.3	135	Application Data	0.006	0.003	0.003	2%
21	Qlik server	RDS DB	TCP	54	58313 > 5432 [ACK] Seq=836 Ack=5569 Win=262400 Len=0	0.000	0.001	-0.001	0%
22	Qlik server	RDS DB	TLSv1.3	157	Application Data	0.005	0.007	-0.002	-1%
23	RDS DB	Qlik server	TLSv1.3	179	Application Data	0.018	0.001	0.018	10%
24	Qlik server	RDS DB	TLSv1.3	251	Application Data	0.011	0.000	0.011	6%
25	RDS DB	Qlik server	TLSv1.3	147	Application Data	0.018	0.000	0.018	11%
26	RDS DB	Qlik server	TLSv1.3	433	Application Data, Application Data	0.000	0.000	0.000	0%
27	RDS DB	Qlik server	TLSv1.3	98	Application Data	0.000	0.000	0.000	0%
28	Qlik server	RDS DB	TCP	54	58313 > 5432 [ACK] Seq=1136 Ack=6210 Win=261888 Len=0	0.000	0.000	0.000	0%
29	Qlik server	RDS DB	TLSv1.3	93	Application Data	0.001	0.001	0.001	0%
30	RDS DB	Qlik server	TLSv1.3	148	Application Data	0.020	0.001	0.018	11%
31	RDS DB	Qlik server	TLSv1.3	98	Application Data	0.000	0.000	0.000	0%
32	Qlik server	RDS DB	TCP	54	58313 > 5432 [ACK] Seq=1175 Ack=6348 Win=261632 Len=0	0.000	0.000	0.000	0%
33	Qlik server	RDS DB	TLSv1.3	81	Application Data	0.000	0.000	0.000	0%
34	Qlik server	RDS DB	TLSv1.3	78	Application Data	0.000	0.000	0.000	0%
35	Qlik server	RDS DB	TCP	54	58313 > 5432 [FIN, ACK] Seq=1226 Ack=6348 Win=261632 Len=0	0.000	0.000	0.000	0%
36	RDS DB	Qlik server	TCP	60	5432 > 58313 [ACK] Seq=6348 Ack=1226 Win=30104 Len=0	0.019	0.000	0.018	11%
37	RDS DB	Qlik server	TCP	60	5432 > 58313 [FIN, ACK] Seq=6348 Ack=1227 Win=30104 Len=0	0.000	0.000	0.000	0%
38	Qlik server	RDS DB	TCP	54	58313 > 5432 [ACK] Seq=1227 Ack=6349 Win=261632 Len=0	0.000	0.000	0.000	0%

The data from the two captures showed a couple of things:

Firstly, both systems had the same number of events captured by Wireshark. This gives me an indication that there are no networking components from source to destination that is dropping traffic; or doing anything extra unexpected actions to the packet requests.

I cannot say for sure what is happening on the return trip if there is anything timing out from the AWS side back.

Also, when taking the difference between the OnPrem vs the EC2 server I can see the difference of 18ms keep popping up. I believe this is the round trip of the connection. Since this happens multiple times; our latency is compounded into quite a significant value.

What’s next?

I am not a network engineer, so I do not have the knowledge to dive deeper into the Wireshark packets.

It would be interesting to try the closer AWS data centre to see if the physical distance can help the latency. But to do this will require effort from the cloud team and the project budget wouldn’t extend to this piece of work.

Our other option is to reduce the number of round trips from our OnPrem server to the AWS datacentre as much as possible.

May 6, 2024 by jonny.donker@gmail.com Qlik Replicate 0

Qlik Replicate – The saga of replicating to AWS Part 2 – Lions and Tigers and Primary Keys (Oh My!)

The Story so far:

For those who are following from home:

We deduced that replicating through the AWS RDS proxy was a bad idea for performance; but even going directly to the AWS RDS Postgres database from our on-prem system did not give us any noticeable performance gains.

It was time to try looking in different area.

Raw SQL

In the squad investigating the problem, someone asked, “What SQL is Qlik running on the Postgres server? Is it poorly optimised?”

To find out what Qlik Replicate is running; I bumped the TARGET_APPLY up to Verbose, let transactions run through for a couple of minutes and then assessed the log file. Since 99% of the changes are updates on the source database (we know this from a production job that is running on the same source database); our logs were full of update statements like this:

UPDATE dbo.my_dest_table
SET	key_1 = ?,
	key_2 = ?,
	key_3 = ?,
	field_1 = ?,
	field_2 = ?,
	field_3 = ?
WHERE
	key_1 = ? AND
	key_2 = ? AND
	key_3 = ?;

The first thing I noticed was – Qlik was updating ALL the columns, even if only one column’s data changed on the source database.

This also included the Primary key.

Primary keys are computationally expensive updating compared to just your regular day to day columns. As for an example, I wrote a simple POC in t-sql:

/*
CREATE TABLE dbo.my_dest_table
(
	key_1 SMALLDATETIME,
	key_2 INT,
	key_3 VARCHAR(10),

	field_1 INT,
	field_2 VARCHAR(256),
	field_3 SMALLDATETIME,
	PRIMARY KEY(key_1, key_2, key_3)
)
*/

/*
UPDATE dbo.my_dest_table
SET	key_1 = ?,
	key_2 = ?,
	key_3 = ?,
	field_1 = ?,
	field_2 = ?,
	field_3 = ?
WHERE
	key_1 = ? AND
	key_2 = ? AND
	key_3 = ?;
*/

/*
INSERT INTO dbo.my_dest_table VALUES ('2024-01-01', 1, 'key 3', 1, 2, '2023-03-03');
INSERT INTO dbo.my_dest_table VALUES ('2024-01-01', 2, 'key 3', 2, 3, '2024-04-04');
*/

SET STATISTICS TIME ON;

UPDATE dbo.my_dest_table
SET key_1 = '2024-01-01',
	key_2 = 1,
	key_3 = 'key 3',

	field_1 = 10,
	field_2 = 2,
	field_3 = '2023-03-03'
WHERE
	key_1 = '2024-01-01' AND
	key_2 = 1 AND
	key_3 = 'key 3';
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 41 ms.  <------- Oh dear

(1 row affected)
*/

UPDATE dbo.my_dest_table
SET field_1 = 10,
	field_2 = 3,
	field_3 = '2024-04-04'
WHERE
	key_1 = '2024-01-01' AND
	key_2 = 2 AND
	key_3 = 'key 3';
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 6 ms.

(1 row affected)
*/

As demonstrated updating the PK columns (even with the same data) is eight times more expensive than just updating the other data columns.

Asking for help

I asked for help on the Qlik Community forum. I got a response back from the specialists that there is a setting in Qlik’s endpoints that will NOT update the Primary key.

The setting is:

$info.query_syntax.pk_segments_not_updateable

Set to TRUE

I made this change in the endpoint, restarted the task and captured a few more update sql samples. With the change in the settings; the updates now discarded the updates to the PK.

BUT…

This might be a helpful solution and worthy to note for the future; but in this case the solution did not really help us out. After making the change we noticed no noticeable change in our TPS throughput; confirming increasingly it was a network issue.

I could see also other problems with this.

The source system that we are reading from is exceptionally large, have been entrenched in our organisation for 30 years and there is constant work done in the background as other core system are merged into it. We as a downstream user; cannot guarantee the rules around updating Primary Keys. Gut feeling in day-to-day operation that once the PKs are set, they are set in stone in the database. But to base our data integrity for critical reporting on accuracy of data on a “Gut feeling” is not a good data integrity model.

Who to say that the owners of the source system perform an update to a PK to fix an incident; or a new source system consolidation have updates in the steps? Our change management in the organisation is not tight and detailed enough for us to be forewarned of changes like this.

If data integrity were not so critical; we could run a full refresh periodically to reset the data to a baseline to capture updates of PK. But this will require significant rework of the solution to manage these periodical refreshes.

So; we did learn. We did assess. But this didn’t help us out.

May 5, 2024 by jonny.donker@gmail.com Qlik Replicate 0

The Mystery of Ker-Polik (Rough English Translation)

“The Mystery of Ker-Polik” is a French comic book 1952 by Frédéric-Antonin Breysse.

We had the Dutch version as kids and use to look over the comic; trying to work out what was going on in the story. Oma read a few pages to us; other than that the story itself was (like the title) a Mystery.

Today in my spare time; I used google translate to finally translate the comic into something comprehendible that I can read. The translation is rough and my next stage is to extract the text out and reword it to align better with the story.

Warning: Google did translate some words into swear words; so read at your own discretion

April 24, 2024 by jonny.donker@gmail.com Comics 0

Qlik Replicate – The saga of replicating to AWS Part 1 – pg_bench

If you are new to this story; pick up the introduction from here

50 TPS – that’s not good…

Our Stress and Volume Testing (SVT) did not turn out that great; which was an understatement. We were aiming for a minimum of 300tps, but we were only achieving 50tps.

Because we could not run the SVT process within business hours as the DB2 databases for production and development shares the same hardware; I had to write a script to simulate the changes on a MS-SQL server.

This means we could use an idle Dev MS-SQL box and run SVT testing whenever we needed it. With the new testing script, I tried various iterations of settings under Change Processing Tuning to see if there was an optimal setting. I could not get above 60tps.

Meanwhile the rest of the team contacted AWS support to see if they can see anything obvious.

AWS Support recommended using the Postgres utility “pg_bench” from various locations to work out if there is a bottle neck between On Prem and AWS RDS.

They recommend running the benchmark for 15 minutes with different numbers of clients.

Armed with the pg_bench utility; I started performing benchmark runs against the AWS RDS database from various locations in our network, trying to work out where the bottle neck is.

Running the tests

Our network had several locations that we could run pg_bench on to assess the tps to AWS RDS. To be more specific; to an AWS RDS Proxy.

To start off we had to learn how to use the command first. The benchmark database must be initialised with some tables and data. This is done with the following command:

pgbench --host rds-database-proxy.aws.com --port 5432 --username qlikreplicate -i -s 50 dest_db

Once done; a 15min benchmark was run with the following command:

pgbench --host rds-database-proxy.aws.com --port 5432 --username qlikreplicate -l -c 2 -j 2 -T 900 dest_db

Parameters:

Parameter	Description
`--host`	Address of the database/proxy
`--port`	Database port
`--username`	Username to connect. Must have permissions to create objects and insert data. Since it was a Dev database I used the Qlik Replicate username
`-l`	Write information about each transaction to a log. not really useful and in future I would omit it
`-c`	Number of clients to simulate. I used 2 to replicate a couple of QR tasks
`-j`	Number of threads to use
`-T`	Time in seconds to run the test for. 900 seconds = 15 minutes

Four tests were conducted:

The OnPrem Qlik Replicate server to AWS RDS Proxy
An EC2 machine to AWS RDS Proxy
The OnPrem Qlik Replicate server to an OnPrem Postgres database. (Actually to our Enterprise manager analytic database. So it is not a fancy highly optimised database)
An EC2 machine directly to the AWS RDS database

Why a AWS RDS Proxy? I’m not sure of the story behind why there was a proxy between Qlik and RDS; rumours it was because the security team didn’t initially allow our On Prem services to connect directly to cloud database. They insisted that a proxy between the services and databses.

Results

The pg_bench tests revealed several learnings to the team. But unfortunately, there were no answers to our QR latency problem.

It was a bit of grim relief it proved that QR was not the cause of our latency problem; there is a component between us and AWS RDS database that is causing the issue.

Even more unfortunate when I raise the issue to the network team thinking that there might be an IPS, QOS device that might be slowing us down; they could not find anything. I have been informed that the bandwidth between our On Prem and AWS is broad and fast and there should not be any obvious chocking the tps. Grumpily I mused that households can now stream multiple 4K videos at home without a frame dropped while we can’t send a few database changes from our datacentre to an AWS database at reasonable speeds

Interestingly the RDS proxy was a significant bottleneck when we started questioning its purpose in the pipeline. Even the AWS support team was scratching their heads of the purpose of it in the use case we had. Later on, I tried OnPrem to the direct RDS; but that made no difference to the tps.

Lessons learnt

Validate poor latency through Qlik Replicate via another tool. In this instance pg_bench took QR right out of the equation
Don’t use AWS RDS proxy for Qlik Replicate unless you have a critical use case. Performance test thoroughly your design choices

February 24, 2024 by jonny.donker@gmail.com Qlik Replicate 0

Qlik Replicate – The saga of replicating to AWS (Intro)

I am exhausted from the past couple of weeks; even getting close to a month of working on a new Qlik Replicate tasks that we are getting no luck with performance.

The upside I have learned many new concepts and techniques in Qlik Replicate, Postgres, MS-SQL and networking.
The downside is that (at the time of writing) we have not solved the problem, and a lot of effort hasn’t returned anything tangible for the business to use.

This post will be an introduction to the problem and the next few posts will dive into the individual areas of QR quirks, diagnostic methods and performance turning that I used.

Background

We have a big expensive DB2 Z/OS database running on premise that have our core account data in hosted in it. A logical account will be divided to two different tables; slowly changing data (like open date, close date) in one table and constant changing data (like account balance) in another table.

Downstream users want these two tables stitched together and “Change Data Capture” implemented on this combined table. The initial thought was to create triggers in DB2 so when data is updated on the “Slowly Changing Data Table” (SCDT) or the “Constant Changing Data Table” (CCDT); the triggers act on a “Combined Table” (CT).

This solution was deemed too expensive by the business; the triggers would drag down the performance of an already stressed DB2 system and to purchase more processing power was not in the viable.

The architects thought, “OK – why don’t we replicate the SCDT and CCDT to AWS RDS Postgres and build the triggers in there? We can then scale the Postgres database cheaply.”

Sound thinking. We did a quick POC that a downstream database can be triggered by data flowing into tables from Qlik Replicate and it worked as expected.

The project jumped into build mode and soon we had development done and testing on the way.

Then it was time to do the “Stress and Volume” testing.

That is when things started to unravel…

High Hopes but low TPS

Since the Development DB2 Z/OS database shares the same hardware as Production; we had to conduct our Stress and Volume Testing (SVT) on a weekend, so our customers were not adversely affected.

Two test runs were conducted; on SVT run without the database triggers enabled and another with them enabled.

The results were not good. Our SVT SME wrote in their summary:

We ran two load test on Dec 9^th Saturday – first test with database trigger OFF, second test with trigger ON.

Each test ran for about 1 hour with total 700k+ transactions at throughput 170tps to 190tps.

During first test with trigger OFF, Qlik process T007 had throughput at 50tps, well below other Qlik processes at 180tps. This suggests the performance bottleneck might not be on the database trigger.During second test with trigger ON, Qlik process T007/T008 had a slightly lower throughput at 40tps, which means the database trigger adds a little bit more latency.
In the middle of 2^nd test, Qlik Enterprise Manager became broken and did not display performance graphs properly, hence I can’t provide you graphs till Jon fixes that.

After the 1-hour test stopped, it took another 3.5 hours for Qlik process T007/T008 to finish its backlog. That means T007/T008 need 4.5 hour in total to complete DB2 data produced in each hour of test. Maths tells us 40:180 = 1:4.5. In other words, the “producer” (DB2 Database) works 4.5 times faster than the “consumer” (Qlik T007/T008).

I notice the memory usage of DB2 related Qlik processes kept going up during test. Not sure if that is the expected behaviour.

Since our bare baseline that we decided internally was at 300tps; this was not looking good.

What lead next was a journey of performance diagnosing with may trials, tribulations, dead ends and a final way forward.

Header Illustration Credit

February 23, 2024 by jonny.donker@gmail.com Qlik Replicate 3

Qlik Replicate – Where’s my slots gone?

Qlik Replicate is growing rapidly in our organisation.

Starting with one “Proof of concept” server in DTL; we are now up to seventeen. A mixture of development, test, performance testing servers on OnPrem and in the AWS and GCS cloud; depending where the source database is located.

With trying to keep things neat and organised; we are constantly moving QR tasks from one server to another.

Anyway the Juniors came to me with the following error message when they were shifting a QR task with a AWS RDS postgres source:

2024-01-31T16:39:38 [SOURCE_CAPTURE ]E: Can't resume task after replication slot was dropped. [1020101] (postgres_endpoint_capture.c:444)

From my understanding (I wasn’t babysitting their process); they exported the old task out with Endpoints and then reimported it into the new server. They re-entered the passwords for the end points and then try to resume from the stream position of the old task.

I couldn’t fault that logic.

As a test we cloned that task on the same server and made the target endpoint as a NULL.

With this test task we could

Start the task from scratch
Resume it from the stream position that the old task got up to

This proved that the new server had no issues contacting the database and resuming from a position on the postgres database.

In the end I guessed that there was a problem with the replication slot (or lack of) for that task on the postgres server; not sure specifically but it was looking for something that it didn’t exist.

I worked out the following steps to resolve the problem:

Change the task’s target endpoint to a NULL connector.
Start the task with the “Reload Target” option.
Wait until the task has done the “Full load” and has entered CDC mode.
Stop the task and change the target endpoint back to the original end point.
Start the task again with “Advance Run Options” and resume from the “Source change position” of the original taks.

The task should now be happy

January 31, 2024 by jonny.donker@gmail.com Qlik Replicate 0

Qlik Replicate – The million row challenge with

It seems every new project; the project teams come with new and innovative ways to make my life “interesting” while managing Qlik Replicate.

The latest idea they have is to pretty much request that one of our major Qlik Replicate tasks that is sucking data our of a DB2 database and delivering data to AWS S3 be duplicated and deliver pretty much the same data to GCS.

When I got wind of this proposal it was already too far into the solution design to propose another solution; like copying the data from GCS to AWS S3. So, it was left up to me to go and tell the owners of the DB2 system that there is another massive feed will be built of their database – basically doing the same thing.

With some persuasion I could get internal funding to research and implement Log Streaming to consolidate our reads from the DB2 system. Hardware wise this required adding more memory and disk space to the QR servers to handle the log streaming.

As for the Log stream component of the solution, I started looking into the settings. Since we are drawing a lot of data from DB2 – I am concerned about disk space and what performance impact the compression setting has on the task.

To test the impact; I set up the following test scenario:

The "Million row" challenge

To test – I created a very wide table in a test MS-SQL (a database I had access to); which contained integers and varchars.

With a python script, a million rows were inserted into the table in batches of 10,000. In the end the million rows equated to about 1.3Gb on the database.

I did ten test runs, increasing the compression in the log stream endpoint with each individual run.

Using Qlik Replicate’s analytics I could then get the metrics for the Log Stream’s task; and a task that is reading from the log stream and outputting the data to a NULL endpoint.

I also grabbed the file sizes that the log stream created on the server’s drive.

Here are the results:

	Log Stream File Size (Mb)	LS – CPU %	LS – Memory (MB)	LS – Source latency (sec)	LS – Target latency (sec)	Target – CPU %	Target – Memory (MB)	Target – Source latency (sec)	Target – Target latency (sec)
No compression	1,974	7	134	1	3	4	126	3	4
Level 2	718	15	169	2	4	4	105	3	3
Level 3	690	14	156	0	3	4	118	2	2
Level 4	683	16	155	1	2	6	127	2	2
Level 5	675	21	281	1	5	6	156	3	3
Level 6	665	24	224	1	3	5	176	4	4
Level 7	663	23	182	1	3	4	160	4	4
Level 8	657	38	394	1	9	4	181	9	9
Level 9	657	46	345	1	10	6	189	12	12

Some notes on the results

The run was done on a shared server; so other processes on the server running at the same time could impact the CPU and memory metrics
Analytics only takes a snapshot of the metrics every 5min and the million rows are inserted into the database in less than <10min so we can only get one snapshot of the metrics for the run. Depending where the run was up to; this could impact the metrics

Conclusions

Like most things in IT; there is no magical setting that will cover all scenarios. Everything is a compromise, and the settings will need to be adjusted to meet one’s particular source endpoint, nature of the data being replicated, and the latency can be tolerated to disk space usage.

Here is what I learnt.

If you require absolute minimum latency as possible – log steaming is not your answer for a couple of reasons.
1. There is an inherited one second delay writing batches from the source system to the log stream.
2. All the data is written to disk and then read by the target QR task. This means that the changes do not reside in memory and need to be read from disk.
Level 2 significantly reduces disk space usage with little compromise to speed and resource usage.
Level 4 is the best compromise of disk space, speed, and resource usage. Anything above level 4 starts to strain the speed and resource usage with little improvement to disk space.

Implementing Log streaming plan

For my scenario – I think Level 2 is the way to go and will stem my testing out from there.

My implementation plan is to implement the log streaming task into production and let it run by itself for a couple of weeks.

During that time, I will monitor how the log stream task behaves with large batches and how much data it writes to disk as we cannot generate that amount data in our performance testing environments.

Once the log stream task is settled; then I will start migrating the existing Qlik Replicate tasks to read from the log stream task stream instead of directly from the database.

November 19, 2023 by jonny.donker@gmail.com Qlik Replicate 0

Easter-lily Cacti – Beauty amongst the spines

Years ago – my sister gave me an Easter Lilly cactus (Echinopsis oxygona) in a pot. It was one of the first cactus I owned.

Then I forgot about it and neglected it over winter.

A year later I had a closer look at it; and the little neglected cactus has grown and there were heaps of cacti pups growing off the original plant.

So with tongs, welding gloves and a towel; I divided the pups off the mother plant and gave them their own individual pots.

The rest is history and after 10 years of growing and dividing cacti – I have over hundred individual cacti plants. It has become an prickly issue with my wife as she claims I have a hording issue.

But I think there are definitely worse things to to “hoard” than cacti.

Plus they have absolutely beautiful flowers; with a very subtle yet pleasant perfume.

The bees are also on team cacti and early in the morning when the lily type flowers open; they get in early.

Caring for Easter Lilly Cacti

These cacti is pretty forgiving and simple to care for in South Eastern Australia:

Place the pot in a warm sunny position that get about 6+ hours a day
For watering:
- In the peak of summer; water once a week with a quick shower of water from the hose.
- In the milder months – once every two to three weeks
- In the winter; let the natural rain rain water them; unless it is a particularly dry winter
Once a year in the spring; give them a feed of slow release fertiliser.
When the cactus starts to outgrow its pot; transplant into a larger pot using well draining soil. You can buy cacti potting mix from nurseries.

October 23, 2023 by jonny.donker@gmail.com Gardening 0

Pi-Hole: Metrics from a Postgres database

I love Pi-Hole.

It is one of my favourite services that I have running on my home network.

I definitely take Pi-Hole for granted at home and only really appreciate how effective it is when I travel and connect to other networks.

While the function of Pi-Hole is brilliant – my Data warehouse Analyst and reporting background craves for more power from the metrics behind Pi-Hole.

By default Pi-Hole offers a simple dashboard with a few catchy graphs and metrics. This can be hypnotizing to watch when first installing Pi-Hole; watching shock of how many DNS queries are blocked visiting different websites.

Behind the scenes is a sqlite database located on the server for more power users to query.

We need more power!

For me – I have a couple of problems with the Pi-Hole dashboard and crave for more functionality and power from what is on offer.

Some issues I have:

On my home network – I have two Pi-Hole servers working in a redundancy. The main Pi-Hole server is sitting on a hypervisor while the backup one is on a Raspberry Pi. The main rational of this is that if we have a power outage that knocks the hypervisor offline while I am not at home; the backup Pi-Hole server will take over and save an angry phone call from the wife on why she can’t access the internet.

One of my wishes is to see the combined Pi-Hole metrics across both servers in one dashboard.
A couple of items on the network creates a large amount of noise that skews the metrics. For instance the security cameras use to query a NTP server every second until I routed them through a NTP server at home. My work computer when it drops off the VPN will flood particular addresses with traffic.

I would like to exclude these addresses from the metrics; but not exclude them from the P-Hole functionality.
Access to better analytics from the Pi-Hole data.
- How about a heat map of blocked queries on devices?
- Or weekly trends with a moving average?
- Or ability to drill into a time where there is a high amount of blocked traffic to determine the device and application is behind it? (Answer: My kid playing mobile app games)

Napkin Solution

This is what I am working on at the moment to extract and load the data from my two Pi-Hole servers into a Postgres database to query from.

A NFS share is set up to share out the pihole-FLT.db to a linux server on both Pi-Hole servers
A cron job runs every x minutes to run a python script. The python scrip will query the pihole-FTL.db to get the records from the last timestamp it ran to the current timestamp in the Pi-Hole database and land the data in to a “landing” schema on the postgres database
A stored procedure will run to transfer and normalise the data into an ods schema
A reporting tool will then query off the ods schema. In this case I am using Apache Superset; just because it was freely available and the web nature means I can share dashboards to multiple devices without installing client software.

Performance of querying sqlite through a NFS share

Initially I was worried about querying the pihole-FTL.db over the NFS share; thinking it will be too slow to be practical.

I was thinking of a few clunky solutions:

Copy the entire DB over to the postgres host and load it from there
Write a script that runs on the Pi-Hole server to extract the incremental data from the pihole-FLT.db into files and load the files

Thankfully from the volume of traffic we produce; it is not a concern.

The initial load of copying all the data out of the Pi-Hole database does take a long time if there is a large amount of history to transfer across.

But once the initial load is done; small incremental bites of data only take a few seconds

Current state

I have a pilot running on my home network as a proof of concept.

Fundamentally it works fine and I can gleam some interesting metrics from my Pi-Hole servers.

At the moment the security is ~~not great~~ atrocious as I am just using a super username and password throughout the pipeline.

Once I have cleaned up the postgres sql, python code I can share the complete solution.

September 18, 2023 by jonny.donker@gmail.com Pihole 0