Friday, September 1, 2023

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

 

ConfigurationDML DurationDatafile (Allocated MB)Datafile (Free MB)Tlog (Allocated MB)Tlog Size (Free MB)
No Replication00:07:1984.4392146.4
MS-Replication00:07:2283.2392214
MS-CDC00:07:1113617.845624.3
MS-Replication + MS-CDC00:09:2713622.7456190

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:

  1. 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.
  2. 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)
  3. plan_cache_mode only affects prepared statements, has no effect on literal queries.
  4. 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

Config123456100071000
1.PrepareThreshold5CacheModeAutoBISISISISISISSEQISSEQ
2.PrepareThreshold5CacheModeForceCustomISISISISISISSEQISSEQ
3.PrepareThreshold5CacheModeForceGenericISISISISISISISISIS
4.PrepareThreshold10CacheModeAutoISISISISISISSEQISSEQ
5.PrepareThreshold10CacheModeForceCustomISISISISISISSEQISSEQ
6.PrepareThreshold10CacheModeForceGenericISISISISISISISISIS
7.PrepareThreshold1CacheModeAutoISISISISISISISISIS
8.PrepareThreshold1CacheModeForceCustomISISISISISISSEQISSEQ
9.PrepareThreshold1CacheModeForceGenericISISISISISISISISIS
  • 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

Config100023456100071000
10.PrepareThreshold5CacheModeAutoSEQISISISISISSEQISSEQ
11.PrepareThreshold5CacheModeForceCustomSEQISISISISISSEQISSEQ
12.PrepareThreshold5CacheModeForceGenericISISISISISISISISIS
13.PrepareThreshold10CacheModeAutoSEQISISISISISSEQISSEQ
14.PrepareThreshold10CacheModeForceCustomSEQISISISISISSEQISSEQ
15.PrepareThreshold10CacheModeForceGenericISISISISISISISISIS
16.PrepareThreshold1CacheModeAutoSEQISISISISISISISIS
17.PrepareThreshold1CacheModeForceCustomSEQISISISISISSEQISSEQ
18.PrepareThreshold1CacheModeForceGenericISISISISISISISISIS
  • 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();
Config123456100071000
19.PrepareThreshold5CacheModeAutoISISISISISISSEQISSEQ
20.PrepareThreshold5CacheModeForceCustomISISISISISISSEQISSEQ
21.PrepareThreshold5CacheModeForceGenericISISISISISISSEQISSEQ
22.PrepareThreshold10CacheModeAutoISISISISISISSEQISSEQ
23.PrepareThreshold10CacheModeForceCustomISISISISISISSEQISSEQ
24.PrepareThreshold10CacheModeForceGenericISISISISISISSEQISSEQ
25.PrepareThreshold1CacheModeAutoISISISISISISSEQISSEQ
26.PrepareThreshold1CacheModeForceCustomISISISISISISSEQISSEQ
27.PrepareThreshold1CacheModeForceGenericISISISISISISSEQISSEQ
  • 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();
Config100023456100071000
28.PrepareThreshold5CacheModeAutoSEQISISISISISSEQISSEQ
29.PrepareThreshold5CacheModeForceCustomSEQISISISISISSEQISSEQ
30.PrepareThreshold5CacheModeForceGenericSEQISISISISISSEQISSEQ
31.PrepareThreshold10CacheModeAutoSEQISISISISISSEQISSEQ
32.PrepareThreshold10CacheModeForceCustomSEQISISISISISSEQISSEQ
33.PrepareThreshold10CacheModeForceGenericSEQISISISISISSEQISSEQ
34.PrepareThreshold1CacheModeAutoSEQISISISISISSEQISSEQ
35.PrepareThreshold1CacheModeForceCustomSEQISISISISISSEQISSEQ
36.PrepareThreshold1CacheModeForceGenericSEQISISISISISSEQISSEQ
  • BIS: Bitmap Index Scan
  • IS: Index Scan
  • SEQ: Seq Scan