Design and get approval for database schema
Closed, ResolvedPublic5 Estimated Story Points
Actions

Assigned To

Authored By

	phuedx
	Mar 8 2023, 10:54 AM

Project Tags

Referenced Files

None

Subscribers

Description

The database schema must accommodate the following fields:

Stream name
Stream creator (actor)
Schema title
Stream created date
Stream updated date
[Optional] Stream expiry date
[Optional] Sampling unit and rate
[Optional] Stream producer configuration
- This is a map of producer name (string) to arbitrary producer configuration
  - NOTE: We can validate producer configuration before reading from/writing it to the database
Has been reviewed by the Legal department?
Legal reviewer (actor)

~~We also need to provide an estimate for read and write frequencies for the expected access patterns.~~

Estimates

Read frequency: Once per pageview
Write frequency: Very infrequent
Table size: 1-10 MiB

Proposed Database Schema

schema.json

{
  "name": "event_stream_configs",
  "columns": [
    {
      "name": "escs_id",
      "comment": "The analytics instrumentation stream ID for referring to for updating and deleting.",
      "type": "integer",
      "options": { "unsigned": true, "notnull": true, "autoincrement": true }
    },
    {
      "name": "escs_creator",
      "comment": "actor_id of the analytics instrumentation stream creator.",
      "type": "bigint",
      "options": { "notnull": true, "unsigned": true }
    },
    {
      "name": "escs_created_at",
      "type": "mwtimestamp",
      "options": { "notnull": true }
    },
    {
      "name": "escs_updated_at",
      "type": "mwtimestamp",
      "options": { "notnull": true }
    },
    {
      "name": "escs_expires_at",
      "type": "mwtimestamp"
    },
    {
      "name": "escs_name",
      "type": "binary",
      "options": { "notnull": true, "length": 255 }
    },
    {
      "name": "escs_params",
      "comment": "Serialised stream producer configuration.",
      "type": "blob",
      "options": { "notnull": false, "length": 65535 }
    },
    {
      "name": "escs_disabled",
      "comment": "",
      "type": "mwtinyint",
      "options": { "notnull": true, "unsigned": true, "default": 0 }
    },
    {
      "name": "escs_reviewer",
      "comment": "actor_id of the person who last reviewed the analytics instrumentation stream configuration",
      "type": "bigint",
      "options": { "notnull": true, "unsigned": true }
    }
  ],
  "indexes": [
    {
      "name": "escs_created_at",
      "comment": "Special:InstrumentationStreams created sort",
      "columns": [ "escs_created_at" ],
      "unique": false
    },
    {
      "name": "escs_created_by",
      "comment": "Special:InstrumentationStreams creator sort",
      "columns": [ "escs_created_by", "escs_updated_at" ],
      "unique": false
    },
    {
      "name": "escs_updated_at",
      "comment": "Special:InstrumentationStreams default sort",
      "columns": [ "escs_updated_at" ],
      "unique": false
    },
  ],
  "pk": [ "escs_id" ]
}

Details

Related Changes in Gerrit:

Subject	Author	Repo	Branch	Lines +/-
Introduce Metrics Platform dynamic stream configs tables	Clare Ming	mediawiki/extensions/WikimediaEvents	master	+870 -1
Introduce event_stream_configs table	Clare Ming	mediawiki/extensions/EventStreamConfig	master	+308 -0
createExtensionTables: Add extension EventStreamConfig	Clare Ming	mediawiki/extensions/WikimediaMaintenance	master	+5 -0
Introduce event_stream_configs table	Clare Ming	mediawiki/core	master	+269 -0

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
Resolved	cjming	T370880 [EPIC] FY 24/25 SDS 2.1.7 \| Alpha Release of Instrument Configuration System (MPIC)
Resolved	cjming	T373715 MPIC (aka EPIC): Create a plan for dogfooding the alpha release
Resolved	JVanderhoop-WMF	T330354 [Goal] Build the Metrics Platform control plane
Resolved	phuedx	T331514 [Goal] M1: Metrics Platform: Control Plane: Analytics instrumentation stream management UI
Resolved	cjming	T331516 Design and get approval for database schema

Event Timeline

phuedx created this task.Mar 8 2023, 10:54 AM

phuedx updated the task description. (Show Details)

phuedx mentioned this in T331514: [Goal] M1: Metrics Platform: Control Plane: Analytics instrumentation stream management UI.Mar 8 2023, 11:17 AM

phuedx updated the task description. (Show Details)Mar 8 2023, 11:27 AM

phuedx updated the task description. (Show Details)Mar 8 2023, 1:58 PM

phuedx moved this task from Metrics Platform Kanban to Metrics Platform Instrument Configurator on the Metrics Platform board.Mar 16 2023, 2:41 PM

phuedx edited projects, added: Metrics Platform; removed: Metrics Platform (Metrics Platform Kanban).

Optional] Stream producer configuration
This is a map of producer name (string) to arbitrary producer configuration

NOTE: We can validate producer configuration before reading from/writing it to the database

Question: Could serialised stream producer configuration could be stored in a blob similar to the logging.log_params and change_tag.ct_params fields?

{
  "name": "event_streams",
  "columns": [
    {
      "name": "es_id",
      "comment": "The analytics instrumentation stream ID for referring to for updating and deleting.",
      "type": "integer",
      "options": { "unsigned": true, "notnull": true, "autoincrement": true }
    },
    {
      "name": "es_creator",
      "comment": "actor_id of the analytics instrumentation stream creator.",
      "type": "bigint",
      "options": { "notnull": true, "unsigned": true }
    },
    {
      "name": "es_created_at",
      "type": "mwtimestamp",
      "options": { "notnull": true }
    },
    {
      "name": "es_updated_at",
      "type": "mwtimestamp",
      "options": { "notnull": true }
    },
    {
      "name": "es_expires_at",
      "type": "mwtimestamp"
    },
    {
      "name": "es_name",
      "type": "binary",
      "options": { "notnull": true, "length": 128 }
    },
    {
      "name": "es_params",
      "comment": "Serialised stream producer configuration.",
      "type": "blob",
      "options": { "notnull": false, "length": 65535 }
    },
    {
      "name": "es_disabled",
      "comment": "",
      "type": "mwtinyint",
      "options": { "notnull": true, "unsigned": true, "default": 0 }
    },
    {
      "name": "es_reviewer",
      "comment": "actor_id of the person who last reviewed the analytics instrumentation stream configuration",
      "type": "bigint",
      "options": { "notnull": true, "unsigned": true }
    }
  ],
  "indexes": [
    {
      "name": "es_created_at",
      "comment": "Special:InstrumentationStreams created sort",
      "columns": [ "es_created_at" ],
      "unique": false
    },
    {
      "name": "es_created_by",
      "comment": "Special:InstrumentationStreams creator sort",
      "columns": [ "es_created_by", "es_created_at" ],
      "unique": false
    },
    {
      "name": "es_updated_at",
      "comment": "Special:InstrumentationStreams default sort",
      "columns": [ "es_created_at" ],
      "unique": false
    },
  ],
  "pk": [ "es_id" ]
}

phuedx updated the task description. (Show Details)Apr 17 2023, 1:26 PM

phuedx updated the task description. (Show Details)Apr 17 2023, 1:29 PM

@Ladsgroup: Sorry for not getting back to you sooner. This task that tracks the design and approval of the database schema to store analytics instrumentation stream configurations to be delivered to front-end clients.

Thanks. Some random notes:

es_name length seems a bit too short, Are you sure that'd be enough? 255 is more standard
Indexes look good but column of es_updated_at is wrong, it shouldn't be es_created_at
The 'es' prefix is a bit too generic, while I couldn't find any clashes, I feel a better prefix would be 'escs' ("event streams config" as prefix of the extension and s for streams). I can go with 'ecs' as well. To avoid clashes in the future.

Last: How many rows do you expect it to be? You said not much but just to have it in mind.

phuedx updated the task description. (Show Details)Apr 19 2023, 10:22 AM

In T331516#8791668, @Ladsgroup wrote:

Thanks. Some random notes: <snip />

Ta! I've added the revised schema to the task description.

Last: How many rows do you expect it to be? You said not much but just to have it in mind.

Initially, fewer than 50. There's some preliminary discussion about whether the Control Plane could be extended to cover all event streams, at which point the number lies between 100 and 500.

cd /path/to/operations/mediawiki-config
composer buildConfigCache
jq '.wgEventStreams | length' tests/data/config-cache/conf-production-metawiki.json
> 165

Than it looks good to me. Make sure you add APCu cache (one hour TTL would be good?) and I think we are good to go.

cjming claimed this task.May 8 2023, 11:08 PM

cjming triaged this task as Medium priority.

cjming moved this task from Metrics Platform Instrument Configurator to Metrics Platform Kanban on the Metrics Platform board.

cjming edited projects, added: Metrics Platform (Metrics Platform Kanban); removed: Metrics Platform.

cjming moved this task from Ready to In Progress on the Metrics Platform (Metrics Platform Kanban) board.

Change 919930 had a related patch set uploaded (by Clare Ming; author: Clare Ming):

[mediawiki/core@master] Introduce event_stream_configs table

https://gerrit.wikimedia.org/r/919930

gerritbot added a project: Patch-For-Review.May 15 2023, 10:53 PM

cjming moved this task from In Progress to In Review on the Metrics Platform (Metrics Platform Kanban) board.May 15 2023, 10:57 PM

Change 919930 abandoned by Clare Ming:

[mediawiki/core@master] Introduce event_stream_configs table

Reason:

wrong repo

https://gerrit.wikimedia.org/r/919930

Maintenance_bot removed a project: Patch-For-Review.May 16 2023, 1:29 AM

Change 920329 had a related patch set uploaded (by Clare Ming; author: Clare Ming):

[mediawiki/extensions/EventStreamConfig@master] Introduce event_stream_configs table

https://gerrit.wikimedia.org/r/920329

gerritbot added a project: Patch-For-Review.May 16 2023, 10:37 PM

Change 920390 had a related patch set uploaded (by Clare Ming; author: Clare Ming):

[mediawiki/extensions/WikimediaMaintenance@master] createExtensionTables: Add extension EventStreamConfig

https://gerrit.wikimedia.org/r/920390

Change 920390 abandoned by Clare Ming:

[mediawiki/extensions/WikimediaMaintenance@master] createExtensionTables: Add extension EventStreamConfig

Reason:

not necessary

https://gerrit.wikimedia.org/r/920390

Change 931704 had a related patch set uploaded (by Clare Ming; author: Clare Ming):

[mediawiki/extensions/WikimediaEvents@master] Introduce Metrics Platform dynamic stream configs tables

https://gerrit.wikimedia.org/r/931704

Change 920329 abandoned by Clare Ming:

[mediawiki/extensions/EventStreamConfig@master] Introduce event_stream_configs table

Reason:

in favor of https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaEvents/+/931704

https://gerrit.wikimedia.org/r/920329

New proposed database schema:

schema.json

[
	{
		"name": "metrics_platform_stream_configs",
		"columns": [
			{
				"name": "mps_id",
				"comment": "The analytics instrumentation stream ID for referring to for updating and deleting.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "mps_creator",
				"comment": "actor_id of the analytics instrumentation stream creator.",
				"type": "bigint",
				"options": { "notnull": true, "unsigned": true }
			},
			{
				"name": "mps_created_at",
				"type": "mwtimestamp",
				"options": { "notnull": true }
			},
			{
				"name": "mps_updated_at",
				"type": "mwtimestamp",
				"options": { "notnull": true }
			},
			{
				"name": "mps_expires_at",
				"type": "mwtimestamp",
				"options": { "notnull": false }
			},
			{
				"name": "mps_name",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "mps_disabled",
				"comment": "",
				"type": "mwtinyint",
				"options": { "notnull": true, "unsigned": true, "default": 0 }
			},
			{
				"name": "mps_reviewer",
				"comment": "actor_id of the person who last reviewed the analytics instrumentation stream configuration",
				"type": "bigint",
				"options": { "notnull": true, "unsigned": true }
			},
			{
				"name": "mps_sample_rate",
				"type": "mwtinyint",
				"options": { "notnull": true, "unsigned": true, "default": 0 }
			},
			{
				"name": "mps_sample_unit",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			}
		],
		"indexes": [
			{
				"name": "mps_created_at",
				"comment": "Special:InstrumentationStreams created sort",
				"columns": [ "mps_created_at" ],
				"unique": false
			},
			{
				"name": "mps_created_by",
				"comment": "Special:InstrumentationStreams creator sort",
				"columns": [ "mps_creator", "mps_updated_at" ],
				"unique": false
			},
			{
				"name": "mps_updated_at",
				"comment": "Special:InstrumentationStreams default sort",
				"columns": [ "mps_updated_at" ],
				"unique": false
			}
		],
		"pk": [ "mps_id" ]
	},
	{
		"name": "metrics_platform_stream_sample",
		"columns": [
			{
				"name": "mpss_id",
				"comment": "The analytics stream sample override ID for referring to for overriding sample rate.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "mpss_stream_id",
				"comment": "The analytics instrumentation stream ID for referring to for foreign key constraint.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true }
			},
			{
				"name": "mpss_wiki",
				"comment": "Wiki that the stream applies to for overriding sample rate/unit.",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "mpss_sample_rate",
				"type": "mwtinyint",
				"options": { "notnull": true, "unsigned": true, "default": 0 }
			},
			{
				"name": "mpss_sample_unit",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			}
		],
		"indexes": [],
		"pk": [ "mpss_id" ]
	},
	{
		"name": "metrics_platform_stream_provide_values",
		"columns": [
			{
				"name": "mpspv_id",
				"comment": "The analytics stream provide values ID for referring to contextual attributes.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "mpspv_stream_id",
				"comment": "The analytics instrumentation stream ID for referring to for foreign key constraint.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true }
			},
			{
				"name": "mpspv_contextual_attribute_id",
				"type": "mwtinyint",
				"options": { "notnull": true, "unsigned": true }
			}
		],
		"indexes": [],
		"pk": [ "mpspv_id" ]
	},
	{
		"name": "metrics_platform_stream_contextual_attributes",
		"columns": [
			{
				"name": "mpsca_id",
				"comment": "The analytics stream provide values ID for referring to contextual attributes.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "mpsca_contextual_attribute_name",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "mpsca_contextual_attribute_type",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			}
		],
		"indexes": [],
		"pk": [ "mpsca_id" ]
	},
	{
		"name": "metrics_platform_stream_curation",
		"columns": [
			{
				"name": "mpsc_id",
				"comment": "The analytics stream curation ID for referring to curation config.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "mpsc_contextual_attribute_id",
				"comment": "The analytics instrumentation stream ID for referring to for updating and deleting.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true }
			},
			{
				"name": "mpspv_curation_rule",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			}
		],
		"indexes": [],
		"pk": [ "mpsc_id" ]
	},
	{
		"name": "metrics_platform_stream_events",
		"columns": [
			{
				"name": "mpse_id",
				"comment": "The analytics stream event ID for referring to interested events.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "mpse_stream_id",
				"comment": "The analytics instrumentation stream ID for foreign key constraint.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true }
			},
			{
				"name": "mpse_event_name_prefix",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			}
		],
		"indexes": [],
		"pk": [ "mpse_id" ]
	}
]

Sorry it took me some time, I will get to it now.

Hi, Can you provide me with more context on the new tables? Why they are added? What data is going to be in them? What size do you think they will end up to be (any sort of rough estimate is fine) and last but not least, examples of select queries on them.

Thanks, even if you point to some documents I could go and read, I'd really appreciate it.

hi @Ladsgroup - i'll try to provide more context:

Why they are added?

I believe the preferred approach is to capture all the config data as discrete fields/columns rather than having to serialize/deserialize a blob.

What data is going to be in them?

all the info that can be provided in a given Metrics Platform stream configuration

What size do you think they will end up to be (any sort of rough estimate is fine)

100-1000 rows? Maybe a few KB?

examples of select queries on them

So for reads, I imagine having to join all the tables to reconstruct the stream config info -- they are all primary foreign key'd by id so a sample query might be:

SELECT *
FROM metrics_platform_stream_configs AS mps
   INNER JOIN
   metrics_platform_stream_sample AS mpss
   ON mps.id = mpss.mpss_stream_id
   INNER JOIN
   metrics_platform_stream_provide_values AS mpspv
   ON mps.id = mpspv.mpspv_stream_id
   INNER JOIN
   metrics_platform_stream_events AS mpse
   ON mps.id = mpse.mpse_stream_id
   LEFT JOIN
   metrics_platform_stream_contextual_attributes as mpsca
   ON mpspv.mpspv_contextual_attribute_id = mpsca.mpsca_id
   LEFT JOIN
   metrics_platform_stream_curation as mpsc
   ON mpsca.mpsca_id = mpsc.mpsc_contextual_attribute_id
WHERE mps.mps_name = "mediawiki.web_ui.interactions";

The tables metrics_platform_stream_contextual_attributes and metrics_platform_stream_curation are lookup tables for contextual attributes and their related curation rules.

For writes, we need to update the mps_updated_at value in the metrics_platform_stream_configs table at the same time any MP stream config values are being changed. So sample SQL statements might be:

UPDATE metrics_platform_stream_sample
SET mpss_sample_rate = 1
WHERE mpss_stream_id = (
   SELECT mps_id
   FROM metrics_platform_stream_configs
   WHERE mps_name = "mediawiki.web_ui.interactions"
   )
AND mpss_wiki = 108;

UPDATE metrics_platform_stream_configs
SET mps_updated_at = now()
WHERE mpss_stream_id = (
   SELECT mps_id
   FROM metrics_platform_stream_configs
   WHERE mps_name = "mediawiki.web_ui.interactions"
   );

cc: @phuedx

Hi, thanks for the info. I want to acknowledge that I saw this but currently I'm in Wikimania and won't be around until Monday to properly read and try to understand it. Generally speaking it should be fine as it's small and it's not growing so subsequent alters in future wouldn't be hard.

xcollazo moved this task from In Review to Blocked/Paused on the Metrics Platform (Metrics Platform Kanban) board.Aug 16 2023, 12:27 PM

@Ladsgroup We're going to move forward for now to not block and given that this is low risk and is not going to production immediately. There will still be time for your review to have impact so please just take a look when you are back!

We're going to pause the patch for now and put it in our backlog.

cjming moved this task from Blocked/Paused to Current Data Products Sprint on the Metrics Platform (Metrics Platform Kanban) board.Aug 22 2023, 2:19 PM

cjming added a project: Test Kitchen (Sprint 00).

cjming moved this task from Sprint Backlog to BLOCKED on the Test Kitchen (Sprint 00) board.

I looked at it, it generally looks good as long as it's small. One thing on top: don't do set with where doing select inside, simply do select and then programmatically set the value in the update query (i.e. do two queries in the code instead of one).

• WDoranWMF moved this task from BLOCKED to Done on the Test Kitchen (Sprint 00) board.Aug 24 2023, 2:30 PM

cjming mentioned this in T344820: [SPIKE] Gather event stream management discovery into a project plan.Sep 6 2023, 9:53 PM

• VirginiaPoundstone set the point value for this task to 5.Sep 14 2023, 1:09 PM

• VirginiaPoundstone set Final Story Points to 5.

• VirginiaPoundstone closed this task as Resolved.Sep 19 2023, 2:35 PM

• VirginiaPoundstone moved this task from Done to Sign Off on the Test Kitchen (Sprint 00) board.

• VirginiaPoundstone moved this task from Sign Off to Done on the Test Kitchen (Sprint 00) board.

cjming mentioned this in T360647: [Sprint 11 GOAL] SDS 2.5.5 Deliver working prototype for MP Instrumentation Configuration.Mar 21 2024, 3:54 PM

cjming mentioned this in T360748: [User Story] Create the MPIC database schema.Mar 22 2024, 5:22 AM

brouberol mentioned this in T361335: Deploy the MP Instrumentation Configuration application to the DSE k8s cluster.Mar 29 2024, 8:03 AM

BTullis mentioned this in T361955: Create an `mpic` MariaDB database.Apr 8 2024, 8:59 AM

Change #931704 abandoned by Clare Ming:

[mediawiki/extensions/WikimediaEvents@master] Introduce Metrics Platform dynamic stream configs tables

Reason:

no longer relevant

https://gerrit.wikimedia.org/r/931704

Maintenance_bot removed a project: Patch-For-Review.Nov 13 2024, 9:31 PM

Design and get approval for database schemaClosed, ResolvedPublic5 Estimated Story PointsActions

Description

Estimates

Proposed Database Schema

Details

Related ObjectsSearch...

Event Timeline

Design and get approval for database schema
Closed, ResolvedPublic5 Estimated Story Points
Actions

Related Objects
Search...