Donghua's Blog - DBAGlobe

Friday, September 1, 2023

Impacts on MS-Replication or MS-CDC to enable CDC solution

Configuration	DML Duration	Datafile (Allocated MB)	Datafile (Free MB)	Tlog (Allocated MB)	Tlog Size (Free MB)
No Replication	00:07:19	8	4.4	392	146.4
MS-Replication	00:07:22	8	3.2	392	214
MS-CDC	00:07:11	136	17.8	456	24.3
MS-Replication + MS-CDC	00:09:27	136	22.7	456	190

Instance Configuration

/*
To avoid error: Update mask evaluation will be disabled in net_changes_function because the CLR configuration option is disabled.
*/
EXEC sp_configure 'clr enabled';  
EXEC sp_configure 'clr enabled' , '1';  
RECONFIGURE;

Database Creation

CREATE DATABASE [db_msrepll]
 ON  PRIMARY ( NAME = N'db_msrepl', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db_msrepll.mdf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON ( NAME = N'db_msrepll_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db_msrepll_log.ldf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB );
-- repeat this for database db_mscdc, db_msrepl_cdc and db_norep

Table Creation

create table [db_msrepll].dbo.T (i int identity primary key, c text);
create table [db_mscdc].dbo.T (i int identity primary key, c text);
create table [db_msrepl_cdc].dbo.T (i int identity primary key, c text);
create table [db_norep].dbo.T (i int identity primary key, c text);

Enable CDC requirement with MS-Replication

For replication tool, it only requires additional information in the TLog, thus there is a filter 1=2, so no extra data writing to DistributionDB.

use [db_msrepl]
exec sp_replicationdboption @dbname = N'db_msrepl', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [db_msrepl]
exec sp_addpublication @publication = N'msrep', @description = N'Transactional publication of database ''db_msrepl'' from Publisher ''EC2AMAZ-PPQ3EA9''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'msrep', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [db_msrepl]
exec sp_addarticle @publication = N'msrep', @article = N'T', @source_owner = N'dbo', @source_object = N'T', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'T', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboT', @del_cmd = N'CALL sp_MSdel_dboT', @upd_cmd = N'SCALL sp_MSupd_dboT', @filter_clause = N'1=2'

-- Adding the article filter
exec sp_articlefilter @publication = N'msrep', @article = N'T', @filter_name = N'FLTR_T_1__51', @filter_clause = N'1=2', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

-- Adding the article synchronization object
exec sp_articleview @publication = N'msrep', @article = N'T', @view_name = N'SYNC_T_1__51', @filter_clause = N'1=2', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO

-- repeat this for [db_msrep_cdc]

Enable CDC requirement with MS-CDC

In this case, MS-CDC automatically populates CDC related table (such as dbo_T_CT) along the DML changes, that explains why data file size increases.

USE [db_mscdc]  
GO  
EXEC sys.sp_cdc_enable_db  
GO  

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'T',  
@role_name     = NULL,  
@supports_net_changes = 1  
GO  
-- repeat this for [db_msrep_cdc]

Simulate DML workloads

use [db_msrepl];

DECLARE
	@Counter int= 1
WHILE @Counter< = 100000
BEGIN
	insert into T (c) values (replicate('x',100));
	update T set c=replicate('y',100);
	delete from T;
	SET @Counter= @Counter + 1
END;

-- repeat this for database db_mscdc, db_msrepl_cdc and db_norep

Check the data file and Tlog size

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128 AS CurrentSizeMB,  
    size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

Saturday, August 12, 2023

PostgreSQL JDBC prepareThreshold Behaviour with Database setting plan_cache_mode

Source code: https://raw.githubusercontent.com/luodonghua/PostgreSQL/main/Development/Java/JdbcPreparedPlanCacheModeDemo.java

Summary

Observations:

During testing, the first time execution with id=1000 is much more expensive comparing to subsequent execution (483 ms vs 0.7 ms), although same plan used.
In the situation with "prepared statements", the value for prepareThreshold=n and plan_cache_mode=auto, after n+5-1 executions, generic plan could be used if " its cost is not so much higher than the average custom-plan cost". (Ref: https://www.postgresql.org/docs/current/sql-prepare.html)
plan_cache_mode only affects prepared statements, has no effect on literal queries.
To ensure optimizer always pick the best plan, plan_cache_mode=force_custom_plan produces consistent plans.

Prepare Statements:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM t WHERE id = ?");

ps.setInt(1, 1);
ps.executeQuery().close();
ps.setInt(1, 2);
ps.executeQuery().close();
ps.setInt(1, 3);
ps.executeQuery().close();
ps.setInt(1, 4);
ps.executeQuery().close();
ps.setInt(1, 5);
ps.executeQuery().close();
ps.setInt(1, 6);
ps.executeQuery().close();
ps.setInt(1, 1000);
ps.executeQuery().close();
ps.setInt(1, 7);
ps.executeQuery().close();
ps.setInt(1, 1000);
ps.executeQuery().close();

Result

Config	`1`	`2`	`3`	`4`	`5`	`6`	`1000`	`7`	`1000`
1.PrepareThreshold5CacheModeAuto	BIS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
2.PrepareThreshold5CacheModeForceCustom	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
3.PrepareThreshold5CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	IS	IS	IS
4.PrepareThreshold10CacheModeAuto	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
5.PrepareThreshold10CacheModeForceCustom	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
6.PrepareThreshold10CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	IS	IS	IS
7.PrepareThreshold1CacheModeAuto	IS	IS	IS	IS	IS	IS	IS	IS	IS
8.PrepareThreshold1CacheModeForceCustom	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
9.PrepareThreshold1CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	IS	IS	IS

BIS: Bitmap Index Scan
IS: Index Scan
SEQ: Seq Scan

Prepare Statements Starts with 1000

PreparedStatement ps = conn.prepareStatement("SELECT * FROM t WHERE id = ?");

ps.setInt(1, 1000);
ps.executeQuery().close();
ps.setInt(1, 2);
ps.executeQuery().close();
ps.setInt(1, 3);
ps.executeQuery().close();
ps.setInt(1, 4);
ps.executeQuery().close();
ps.setInt(1, 5);
ps.executeQuery().close();
ps.setInt(1, 6);
ps.executeQuery().close();
ps.setInt(1, 1000);
ps.executeQuery().close();
ps.setInt(1, 7);
ps.executeQuery().close();
ps.setInt(1, 1000);
ps.executeQuery().close();

Result

Config	`1000`	`2`	`3`	`4`	`5`	`6`	`1000`	`7`	`1000`
10.PrepareThreshold5CacheModeAuto	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
11.PrepareThreshold5CacheModeForceCustom	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
12.PrepareThreshold5CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	IS	IS	IS
13.PrepareThreshold10CacheModeAuto	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
14.PrepareThreshold10CacheModeForceCustom	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
15.PrepareThreshold10CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	IS	IS	IS
16.PrepareThreshold1CacheModeAuto	SEQ	IS	IS	IS	IS	IS	IS	IS	IS
17.PrepareThreshold1CacheModeForceCustom	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
18.PrepareThreshold1CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	IS	IS	IS

BIS: Bitmap Index Scan
IS: Index Scan
SEQ: Seq Scan

Statements:

Statement stmt = conn.createStatement();
stmt.execute("SELECT * FROM t WHERE id = 1");  
stmt.execute("SELECT * FROM t WHERE id = 2");  
stmt.execute("SELECT * FROM t WHERE id = 3");
stmt.execute("SELECT * FROM t WHERE id = 4");
stmt.execute("SELECT * FROM t WHERE id = 5");
stmt.execute("SELECT * FROM t WHERE id = 6");
stmt.execute("SELECT * FROM t WHERE id = 1000");
stmt.execute("SELECT * FROM t WHERE id = 7");
stmt.execute("SELECT * FROM t WHERE id = 1000");
stmt.close();

Config	`1`	`2`	`3`	`4`	`5`	`6`	`1000`	`7`	`1000`
19.PrepareThreshold5CacheModeAuto	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
20.PrepareThreshold5CacheModeForceCustom	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
21.PrepareThreshold5CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
22.PrepareThreshold10CacheModeAuto	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
23.PrepareThreshold10CacheModeForceCustom	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
24.PrepareThreshold10CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
25.PrepareThreshold1CacheModeAuto	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
26.PrepareThreshold1CacheModeForceCustom	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ
27.PrepareThreshold1CacheModeForceGeneric	IS	IS	IS	IS	IS	IS	SEQ	IS	SEQ

BIS: Bitmap Index Scan
IS: Index Scan
SEQ: Seq Scan

Statements:

Statement stmt = conn.createStatement();
stmt.execute("SELECT * FROM t WHERE id = 1000");  
stmt.execute("SELECT * FROM t WHERE id = 2");  
stmt.execute("SELECT * FROM t WHERE id = 3");
stmt.execute("SELECT * FROM t WHERE id = 4");
stmt.execute("SELECT * FROM t WHERE id = 5");
stmt.execute("SELECT * FROM t WHERE id = 6");
stmt.execute("SELECT * FROM t WHERE id = 1000");
stmt.execute("SELECT * FROM t WHERE id = 7");
stmt.execute("SELECT * FROM t WHERE id = 1000");
stmt.close();

Config	`1000`	`2`	`3`	`4`	`5`	`6`	`1000`	`7`	`1000`
28.PrepareThreshold5CacheModeAuto	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
29.PrepareThreshold5CacheModeForceCustom	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
30.PrepareThreshold5CacheModeForceGeneric	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
31.PrepareThreshold10CacheModeAuto	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
32.PrepareThreshold10CacheModeForceCustom	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
33.PrepareThreshold10CacheModeForceGeneric	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
34.PrepareThreshold1CacheModeAuto	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
35.PrepareThreshold1CacheModeForceCustom	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ
36.PrepareThreshold1CacheModeForceGeneric	SEQ	IS	IS	IS	IS	IS	SEQ	IS	SEQ

BIS: Bitmap Index Scan
IS: Index Scan
SEQ: Seq Scan

Donghua's Blog - DBAGlobe

Friday, September 1, 2023

Impacts on MS-Replication or MS-CDC to enable CDC solution

Instance Configuration

Database Creation

Table Creation

Enable CDC requirement with MS-Replication

Enable CDC requirement with MS-CDC

Simulate DML workloads

Check the data file and Tlog size

Saturday, August 12, 2023

PostgreSQL JDBC prepareThreshold Behaviour with Database setting plan_cache_mode

Summary

Observations:

Prepare Statements:

Result

Prepare Statements Starts with 1000

Result

Statements:

Statements:

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me