PostgreSQL。
使用物理复制时,副本数据库中的表中的给定行是否具有与主数据库中相应行相同的主键值?
使用逻辑复制时有同样的问题。
谢谢。
PostgreSQL。
使用物理复制时,副本数据库中的表中的给定行是否具有与主数据库中相应行相同的主键值?
使用逻辑复制时有同样的问题。
谢谢。
数据文件缩小后,硬盘上有大约 180Gb 的可用空间(操作前的缩小文件对话框还显示可以释放大约 180Gb 的空间)。1-2 天后,数据文件会恢复到其原始大小(缩小操作之前),并且可以再次缩小(缩小文件对话框再次显示可以释放大约 180Gb 的空间)。
我严格强制在硬盘上保留一些可用空间,因此避免缩减操作是不可能的。
寻求可能导致此问题的原因以及可能的解决方案的建议。
概括
我创建了一个有 3 个节点的场景,其中 1 个节点与其他节点不同步,当我连接到该节点时,我发现它正在检索不应该检索的数据,因为它正在从另一个节点读取数据(我相信我使用驱动程序连接到的节点是协调器节点)。
我不知道我错过了什么以及为什么Cassandra
要这样做?
步骤:
CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch
docker run --name cass1 -d -p 19001:9042 --network=casscluster -e CASSANDRA_CLUSTER_NAME=chat -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_SEEDS="cass1,cass2" -e CASSANDRA_LISTEN_ADDRESS="cass1" -e CASSANDRA_BROADCAST_ADDRESS="cass1" cassandra:4.1.5
docker run --name cass2 -d -p 19002:9042 --network=casscluster -e CASSANDRA_CLUSTER_NAME=chat -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_SEEDS="cass1,cass2" -e CASSANDRA_LISTEN_ADDRESS="cass2" -e CASSANDRA_BROADCAST_ADDRESS="cass2" cassandra:4.1.5
docker run --name cass3 -d -p 19003:9042 --network=casscluster -e CASSANDRA_CLUSTER_NAME=chat -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_SEEDS="cass1,cass2" -e CASSANDRA_LISTEN_ADDRESS="cass3" -e CASSANDRA_BROADCAST_ADDRESS="cass3" cassandra:4.1.5
nodetool statushandoff
确保hinted_hand_off
已禁用)nodetool statushandoff
Hinted handoff is not running
create keyspace testak WITH REPLICATION = {
'class' : 'NetworkTopologyStrategy',
'dc1' : 3
};
speculative_retry
(这样如果查询花费的时间太长它就不会从其他节点读取)create table testak.mooz
(
userid int,
chatid int,
name text,
primary key (userid, chatid)
);
alter table testak.mooz with speculative_retry = 'none';
insert into testak.mooz (userid, chatid) values (1, 1);
insert into testak.mooz (userid, chatid) values (2, 1);
insert into testak.mooz (userid, chatid) values (3, 1);
insert into testak.mooz (userid, chatid) values (4, 1);
insert into testak.mooz (userid, chatid) values (5, 1);
insert into testak.mooz (userid, chatid) values (6, 1);
insert into testak.mooz (userid, chatid) values (7, 1);
insert into testak.mooz (userid, chatid) values (8, 1);
insert into testak.mooz (userid, chatid) values (9, 1);
insert into testak.mooz (userid, chatid) values (10, 1);
docker run --name cqlsh --rm -it --network=casscluster nuvo/docker-cqlsh cqlsh cass3 9042 --cqlversion=3.4.6
CONSISTENCY ONE
TRACING ON
select * from testak.mooz where userid = 1 and chatid = 1;
因为复制因子是 3 并且我有 3 个节点,所以我期望所有节点都认为它们拥有所有数据并且不需要从另一个节点查询数据,但是通过多次发出查询,我发现有时请求会转到其他节点并实际检索到不应该检索的数据,因为节点 3 存在不一致并且没有真实的记录数据。
注意:大多数时候(~90%)它不会从另一个节点读取数据。
更新
后来我使用设置了日志记录级别nodetool setlogginglevel org.apache.cassandra ALL
并查看了debug.log
,这是当 cassandra 决定从另一个节点读取数据时的日志:
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,109 CoordinatorWarnings.java:49 - CoordinatorTrackWarnings.init()
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,110 Dispatcher.java:164 - Received: QUERY select * from testak.mooz where userid = 1 and chatid = 1; [pageSize = 100] at consistency ONE, v=4/v4
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,110 QueryProcessor.java:251 - Process SelectStatement[aggregationSpecFactory=<null>,bindVariables=[],isReversed=false,limit=<null>,..(truncated long log line)
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,111 ReadCallback.java:90 - Blockfor is 1; setting up requests to org.apache.cassandra.locator.ReplicaPlan$SharedForTokenRead@74da23a8
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,111 MessagingService.java:401 - cass3/172.20.0.4:7000 sending READ_REQ to 2781@/172.20.0.3:7000
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,111 AbstractReadExecutor.java:226 - Decided not to speculate as 9223372036854775807 > 5000000
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,122 CoordinatorWarnings.java:80 - CoordinatorTrackWarnings.done() with state {}
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,122 CoordinatorWarnings.java:61 - CoordinatorTrackWarnings.reset()
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,122 Dispatcher.java:214 - Responding: ROWS [userid(testak, mooz), org.apache.cassandra.db.marshal.Int32Type][chatid(testak, mooz), org.apache.cassandra.db.marshal.Int32Type][name(testak, mooz), org.apache.cassandra.db.marshal.UTF8Type]
| 1 | 1 | null
---, v=4/v4
但是,当从节点本身读取数据时,sending READ_REQ
我看到的不是带有 的行,而是这一行:
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:49,248 EndpointMessagingVersions.java:67 - Assuming current protocol version for cass3/172.20.0.4:7000
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:49,248 AbstractReadExecutor.java:158 - reading data locally
更新2
在cass3上运行nodetool getendpoints testak mooz 1
:
172.20.0.3
172.20.0.4
172.20.0.2
nodetool status testak
:
Datacenter: dc1
===============
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
-- Address Load Tokens Owns (effective) Host ID Rack
UN 172.20.0.3 227.48 KiB 16 100.0% 2a4a84a4-1894-488a-8661-680cf818384b rack1
UN 172.20.0.4 232.56 KiB 16 100.0% 9ed9d3f6-13e0-424f-bf52-1bd359d8a26e rack1
UN 172.20.0.2 281.54 KiB 16 100.0% 68de8980-ab63-4745-85b1-6b98636ea9af rack1
nodetool describecluster
:
Cluster Information:
Name: chat
Snitch: org.apache.cassandra.locator.GossipingPropertyFileSnitch
DynamicEndPointSnitch: enabled
Partitioner: org.apache.cassandra.dht.Murmur3Partitioner
Schema versions:
629e0d12-c942-3eeb-bce3-5616a291ae90: [172.20.0.4, 172.20.0.2, 172.20.0.3]
Stats for all nodes:
Live: 3
Joining: 0
Moving: 0
Leaving: 0
Unreachable: 0
Data Centers:
dc1 #Nodes: 3 #Down: 0
Database versions:
4.1.5: [172.20.0.4:7000, 172.20.0.2:7000, 172.20.0.3:7000]
Keyspaces:
system_auth -> Replication class: SimpleStrategy {replication_factor=1}
testak -> Replication class: NetworkTopologyStrategy {dc1=3}
system_distributed -> Replication class: SimpleStrategy {replication_factor=3}
system_traces -> Replication class: SimpleStrategy {replication_factor=2}
system_schema -> Replication class: LocalStrategy {}
system -> Replication class: LocalStrategy {}
我正在通过重新排序 PostgreSQL 中的复合索引来优化 SQL 查询性能。我需要了解潜在的影响,包括空间使用情况、数据保存开销以及此修改的任何其他影响。
为了解决性能问题,我实施了一种涉及重新排序复合索引的方法。
详细信息:数据库: PostgreSQL表名: readingTable DDL 语句:
CREATE TABLE readingTable (
ts timestamp NOT NULL,
plant_id int8 NOT NULL,
instance_type varchar(30) NOT NULL,
instance_id int8 NOT NULL,
readings jsonb NULL,
CONSTRAINT idx_readingTable_pk PRIMARY KEY (ts, plant_id, instance_type, instance_id));
CREATE INDEX readingTable_ts_idx ON readingTable USING btree (ts DESC);
表格数据来源:
特斯 | 植物编号 | 实例类型 | 实例 ID | 阅读 |
---|---|---|---|---|
2024-05-31 23:59:00.000 | 4 | 警报 | 1765 | [{"c": "ALARM_TAG_1", "d": "NUMBER", "s": "S15", "u": "kW/kWp", "v": null}, {"c": "ALARM_TAG_2", "d": "BOOLEAN", "s": "S00", "u": "AU", "v": "false"}, {"c": "ALARM_TAG_3", "d": "NUMBER", "s": "S00", "u": "kW/kWp", "v": "12.25"}] |
询问:
SELECT *
FROM readingTable, LATERAL jsonb_array_elements(readings) AS readings_data
WHERE plant_id = 2
AND instance_type = 'ALARM'
AND instance_id IN (1765)
AND ts BETWEEN '2024-05-01 00:00:00' AND '2024-05-30 23:59:00.000'
AND readings_data ->> 'c' IN ('ALARM_TAG_1')
ORDER BY ts, instance_id;
如果我对 idx_readingTable_pk(ts, plant_id, instance_type, instance_id) 运行上述查询,那么执行查询需要时间,因此我已将idx_readingTable_pk 从 (ts, plant_id, instance_type, instance_id) 更新为 (plant_id, instance_type, instance_id, ts)。
在更新后的索引中,我重新排序了组合键的序列,因为当条件包括相等性检查(=)和范围/不等性检查(>、>=、<、<=、IN)时,涉及相等性检查的列应首先出现在索引中,不等式列则紧随其后。
查询更新 idx_readingTable_pk:
-- Drop the old primary key constraint
ALTER TABLE readingTable
DROP CONSTRAINT idx_readingTable_pk;
-- Add the new primary key constraint with the updated column order
ALTER TABLE readingTable
ADD CONSTRAINT idx_readingTable_pk PRIMARY KEY (plant_id, instance_type, instance_id,ts);
此项修改已导致性能提升,如以下统计数据所示。在下图中,您可以看到我已在不同日期范围内使用旧索引和新索引测试了查询,您可以观察到性能提升。
问题:
我有几个包含工业数据的连接表:
create table v2.tag (
tag_id integer generated always as identity,
tag text not null,
primary key (tag_id),
unique (tag)
);
create table v2.state (
tag_id integer not null,
"timestamp" timestamp without time zone not null,
value float not null,
primary key (tag_id, timestamp),
foreign key (tag_id) references v2.tag (tag_id)
) partition by range (timestamp);
该state
表包含过去 6 个月的约 5000 万行时间序列数据,我需要使用各种查询对其进行基准测试。该表按月分区。
我尝试的查询只是获取每天的数据点数和标签,任何实际的 TSDB 都可以轻松地在如此小的数据集上完成此操作:
SELECT
count(*) as points,date_trunc('day', timestamp) as timestamp,tag.tag
FROM
v2.state
JOIN
v2.tag USING (tag_id)
GROUP BY
timestamp, tag
ORDER BY
timestamp ASC;
问题是,出于某种原因,此查询使数据库占用了近 3GB 的 RAM 并返回了一堆重复项。如下所示:
2024-02-01 00:00:00 | /Heave | 1
2024-02-01 00:00:00 | /Pitch | 1
2024-02-01 00:00:00 | /Roll | 1
2024-02-01 00:00:00 | /Velocity | 1
2024-02-01 00:00:00 | /Heave | 1
...
等等。在同一天,我无法滚动到下一天,它只是在结果中不断重复这些行,而不是像我预期的那样按标签计数。因此,它似乎并没有计算每天/标签的数据点数量,而是为数据库中约 5000 万行中的每一行生成一个重复项。
因此,聚合中有些地方出了问题。我预计此查询将返回大约 12K 行 ( 65*30*6
),但它却返回了数百万行,导致我尝试将其加载到的 Jupyter 笔记本发生 OOM 故障。
我尝试用 来运行它EXPLAIN ANALYZE
,但是由于我是 Postgres 的新手,所以它并没有真正......解释任何事情:
Sort (cost=700769.72..700798.22 rows=11400 width=78) (actual time=80503.260..83825.211 rows=47499969 loops=1)
Sort Key: (date_trunc('day'::text, state."timestamp"))
Sort Method: external merge Disk: 4703296kB
-> Finalize GroupAggregate (cost=697027.86..700001.55 rows=11400 width=78) (actual time=35609.801..64328.719 rows=47
499969 loops=1)
Group Key: state."timestamp", tag.tag
-> Gather Merge (cost=697027.86..699688.05 rows=22800 width=70) (actual time=35609.453..55143.276 rows=4749996
9 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=696027.84..696056.34 rows=11400 width=70) (actual time=34526.070..42018.956 rows=15833323
loops=3)
Sort Key: state."timestamp", tag.tag
Sort Method: external merge Disk: 1414088kB
Worker 0: Sort Method: external merge Disk: 1446832kB
Worker 1: Sort Method: external merge Disk: 1470664kB
-> Partial HashAggregate (cost=695145.67..695259.67 rows=11400 width=70) (actual time=8690.289..20
138.661 rows=15833323 loops=3)
Group Key: state."timestamp", tag.tag
Batches: 1029 Memory Usage: 8241kB Disk Usage: 1694608kB
Worker 0: Batches: 901 Memory Usage: 8241kB Disk Usage: 1727928kB
Worker 1: Batches: 773 Memory Usage: 8241kB Disk Usage: 1748528kB
-> Hash Join (cost=2.28..652834.40 rows=5641502 width=62) (actual time=138.598..4142.702 row
s=15833323 loops=3)
Hash Cond: (state.tag_id = tag.tag_id)
-> Parallel Append (cost=0.00..599769.83 rows=19794743 width=12) (actual time=138.383.
.2665.699 rows=15833323 loops=3)
-> Parallel Seq Scan on state_y2024m04 state_4 (cost=0.00..221214.31 rows=874583
1 width=12) (actual time=39.308..827.302 rows=6996457 loops=3)
-> Parallel Seq Scan on state_y2024m02 state_2 (cost=0.00..172317.34 rows=680943
4 width=12) (actual time=58.866..1102.604 rows=8171318 loops=2)
-> Parallel Seq Scan on state_y2024m03 state_3 (cost=0.00..78305.04 rows=3095204
width=12) (actual time=0.766..694.493 rows=7428501 loops=1)
-> Parallel Seq Scan on state_y2024m05 state_5 (cost=0.00..28879.42 rows=1141442
width=12) (actual time=180.418..416.467 rows=2739461 loops=1)
-> Parallel Seq Scan on state_y2024m01 state_1 (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
-> Parallel Seq Scan on state_y2024m06 state_6 (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
-> Parallel Seq Scan on state_y2024m07 state_7 (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
-> Parallel Seq Scan on state_y2024m08 state_8 (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.002..0.002 rows=0 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=58) (actual time=0.149..0.268 rows=65 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Seq Scan on tag (cost=0.00..1.57 rows=57 width=58) (actual time=0.031..0.036
rows=65 loops=3)
Planning Time: 2.447 ms
JIT:
Functions: 96
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 14.487 ms, Inlining 105.515 ms, Optimization 203.723 ms, Emission 143.355 ms, Total 467.081 ms
Execution Time: 86164.911 ms
那么我的查询有什么问题?为什么它没有聚合?
我对与此处描述的场景类似的场景有疑问: SQL:实体属于(多对一)两个不同实体之一
假设我了解了表格Party
和所有其他引用它的内容。但我很好奇想知道表格中列的PartyType
需要是什么Name
?我的意思是,它只是保留“Person”或“Organization”varchar?
同样,在Party
表格中,列是做什么Name
用的?
而且,表格PartyType
到底有什么用呢?我的意思是,它只是为了有一个地方插入所有类型,更重要的是确保在插入时,如果有错误PartyTypeCd
(表中不存在PartyType
),我们会收到错误,对吗?
另一个问题是,您将如何插入Organization
或Person
?看到建议的架构,Organization
和Person
表有自己的 ID(分别为PersonId
和OrganizationId
),它们引用表PartyId
中的Party
。那么,这是否意味着我们应该首先在表Person
中插入一个新的(例如)Party
?如果是这种情况,表中的列AUTO INCREMENT
上是否应该有一个,以便我们确保不会得到具有相同 ID 的组织和人员?PartyId
Party
因为,PartyId
不能成为唯一的主键。 表中的主键Party
应该是和 的组合键。PartyTypeCd
PartyId
我的情况与上面链接的问题类似。仅供参考,这里有一个小的架构示例。
基本上,我遇到这样一种情况,即一个Component
(具有自己的列,如component
、和price
)可以与User_Brand
一个或一个相关联System_Brand
(两者都有自己独特的列和字段,我已经从屏幕截图中排除了这些列和字段...因此将它们合并到一个type
带有列的表中不是一个解决方案)。
我们的 MongoDB 中有一个集合,由于用例的改变,到目前为止,一个小查询现在真的很慢。所讨论的聚合有点像这样:
db.collection.aggregate([
{
$match: {
"_id": {
$in: [ list of 1000 ids ]
}
}
},
{
$unwind: "array_field"
},
{
$match: {
"array_field.sub_field": 123456789
}
}
])
{"_id": 1, "array_field.sub_field": 1}
我现在的问题是,这里是否会考虑类似的索引,或者是否有必要让开发人员将第二个$match
索引移到第一个索引?
遗憾的是,在可预见的未来,数据模型的重新设计还未实现。
我们有一个 SQL Server 2016,上面有 20 个不同的数据库,供公司内的不同应用程序和不同团队使用。Active Directory 中有用户组,这些用户组将负责某些项目的员工组合在一起。问题是一名员工可以同时成为多个团队的成员。同一个人可以属于多个不同的 AD 组。
一位用户抱怨他无法访问某个数据库。当他尝试刷新查询该数据库的 Power BI 报告时,他收到一条错误消息“我们无法使用提供的凭据进行身份验证”。
下面是一张简化的图片。一台服务器有两个数据库:DB1
和。SQL Server 中DB2
有两个 AD 组App_Excel_Reporter
和以及两个相应的登录名:DB2_User
CREATE LOGIN [LegacyDomain\App_Excel_Reporter] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
CREATE LOGIN [Domain\DB2_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
不确定这是否重要,但该服务器是主公司收购的另一家公司的旧服务器。因此,这台 SQL Server 计算机位于域中LegacyDomain
。
两个 AD 组LegacyDomain\App_Excel_Reporter
都有Domain\DB2_User
自己的用户集,但一个用户同时Jack.Universal
属于这两个 AD 组。
属于 的用户App_Excel_Reporter
应该有权访问DB1
。 属于 的用户DB2_User
应该有权访问DB2
。
其中DB1
有一个用户映射到相应的登录名:
USE [DB1]
GO
CREATE USER [LegacyDomain\App_Excel_Reporter] FOR LOGIN [LegacyDomain\App_Excel_Reporter] WITH DEFAULT_SCHEMA=[dbo]
GO
其中DB2
有一个用户映射到相应的登录名:
USE [DB2]
GO
CREATE USER [Domain\DB2_User] FOR LOGIN [Domain\DB2_User] WITH DEFAULT_SCHEMA=[dbo]
GO
当用户Jack.Universal
尝试刷新 Power BI 时,他会在笔记本电脑上登录 Windows,并且Domain\Jack.Universal
我在 SQL Server 日志中看到具有相同时间戳的这些消息:
Login succeeded for user 'Domain\Jack.Universal'. Connection made using Windows authentication, [CLIENT: <ip address>]
Error: 18456, Severity: 14, State: 38.
Login failed for user 'Domain\Jack.Universal'. Reason: Failed to open the explicitly specified database 'DB2'. [CLIENT: <ip address>]
当我跑步时
xp_logininfo 'Domain\Jack.Universal', @option = 'all'
表明
+-----------------------+------+-----------+-----------------------+---------------------------------+
| account name | type | privilege | mapped login name | permission path |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
| Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB2_User |
| Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB3_User |
+-----------------------+------+-----------+-----------------------+---------------------------------+
(是的,有两个以上的数据库,并且该用户属于三个 AD 组)
如果我跑
xp_logininfo 'Domain\Jack.Universal'
如果没有选项“all”,则只返回第一行:
+-----------------------+------+-----------+-----------------------+---------------------------------+
| account name | type | privilege | mapped login name | permission path |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------+------+-----------+-----------------------+---------------------------------+
Domain\Jack.Universal
因此,当 Windows 用户登录到 SQL Server 时,SQL Server似乎会选择登录LegacyDomain\App_Excel_Reporter
来让该用户进入,但是当它尝试访问数据库时,DB2
此尝试会失败,因为登录LegacyDomain\App_Excel_Reporter
仅映射到中的用户DB1
。
“明确指定的数据库”一定是由于 Power BI 的连接字符串明确指定了DB2
。
我们如何配置,以便属于两个 AD 组的用户能够访问两个数据库?仅属于一个 AD 组的用户只能访问相应的数据库。
我不是域管理员,但我可以向他们询问任何需要的信息。公司没有 DBA,我只是一名对 SQL Server 略知一二的程序员。
我挖得更深了一点。当我跑
EXEC xp_logininfo 'Domain\DB2_User', @option = 'members'
我获得了该组中的用户列表:
+-----------------------+------+-----------+-----------------------+-----------------+
| account name | type | privilege | mapped login name | permission path |
+-----------------------+------+-----------+-----------------------+-----------------+
| Domain\user1 | user | user | Domain\user1 | Domain\DB2_User |
| Domain\user2 | user | user | Domain\user2 | Domain\DB2_User |
| Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB2_User |
+-----------------------+------+-----------+-----------------------+-----------------+
并且用户也Domain
正如预期的那样。
当我跑步时
EXEC xp_logininfo 'LegacyDomain\App_Excel_Reporter', @option = 'members'
我明白了:
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| account name | type | privilege | mapped login name | permission path |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| LegacyDomain\user3 | user | user | LegacyDomain\user3 | LegacyDomain\App_Excel_Reporter |
| LegacyDomain\user4 | user | user | LegacyDomain\user4 | LegacyDomain\App_Excel_Reporter |
| LegacyDomain\Jack.Universal | user | user | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
这里权限路径在LegacyDomain
,账户名也在LegacyDomain
,但是上面的结果中xp_logininfo 'Domain\Jack.Universal'
账户名在Domain
,但是权限路径在LegacyDomain
。
再次:
EXEC xp_logininfo 'Domain\Jack.Universal', @option = 'all'
+-----------------------+------+-----------+-----------------------+---------------------------------+
| account name | type | privilege | mapped login name | permission path |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
| Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB2_User |
| Domain\Jack.Universal | user | user | Domain\Jack.Universal | Domain\DB3_User |
+-----------------------+------+-----------+-----------------------+---------------------------------+
EXEC xp_logininfo 'LegacyDomain\Jack.Universal', @option = 'all'
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| account name | type | privilege | mapped login name | permission path |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| LegacyDomain\Jack.Universal | user | user | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
Active Directory 中一定存在一些神奇的功能,可以将用户从旧域映射到主域。
我们在 Windows 2022 VM(8 个 vCPU 核心)上安装了 SQL Server 2022 Enterprise(16.0.4131.2),其中一个数据库上的查询存储存在问题。
下面是我在一个特定数据库上重现该问题所采取的步骤顺序以及我对此所做的观察:
1 –服务器处于空闲状态,仅有少量用户生成负载,且 CPU 利用率可忽略不计。
2 –查询存储已关闭,并且有问题的数据库为空,如下面的查询所示。
3 -当我将查询存储切换到 READ_WRITE 模式时,大约 10 - 15 分钟内一切都很正常。然而,在这段时间之后,服务器的 CPU 利用率突然上升到 15-20% 请参阅下面的活动监视器屏幕截图。
等待统计数据显示SOS_SCHEDULLER_YELD和PREEMPTIVE_OS_QUERYREGISTRY等待量大幅增加- 它们成为该时间段内最热门的服务器等待类型。
产生的 CPU 利用率似乎是由内部后台进程 (SPID < 51) 产生的,该进程与有问题的用户数据库上的 Service Broker 活动有关。数据库本身并未启用 Service Broker 功能。
4 –高 CPU 利用率、上述等待和上述 SPID 活动将持续出现,直到数据库上的查询存储被关闭。
如果我保持查询存储处于打开状态,则没有任何潜在“自我恢复”的迹象。一旦关闭,CPU 利用率就会下降,等待就会消失,SPID 会停止生成负载并进入“休眠”状态。将查询存储切换到 READ_ONLY 模式没有帮助。必须关闭查询存储才能解决问题。
当问题触发时,查询存储中没有任何大量数据 - 见下文(QS 关闭后进行的查询)。
其他相关观察
如果我打开(READ_WRITE)查询存储并让它运行一段时间,但时间不足以触发问题,然后将其切换到 READ_ONLY,则不会在上述 10-15 分钟的时间段内触发问题。但是,如果我稍后将查询存储切换到 READ_WRITE,则几乎会立即触发该问题。
当问题触发时,用户负载增加,可以看到在标准条件下需要几毫秒才能处理的数据库使用查询开始变慢,并且还显示不可忽略的 PREEMPTIVE_OS_QUERYREGISTRY 等待(根据下面显示的 sp_WhoIsActive)。这些查询肯定不会对 Windows 注册表进行查询。
我们在几个其他 SQL Server 实例上拥有相同的数据库(即相同的结构),具有相同的负载模式(甚至更大的数据和查询量),并且查询存储在那里运行良好。似乎查询存储中只有这个特定的数据库出现了问题。
重要的是,该数据库的查询存储最初已满,并且触发了“基于大小的清理过程”,并且 SQL Server(可能)在此期间重新启动。我怀疑该数据库中的 QS 中存在与此相关的问题... 我也尝试了sys.sp_query_store_consistency_check过程,但没有成功。
一旦通过关闭 QS 触发并“暂停”该问题,那么当再次打开 QS(未事先进行清理)时,该问题似乎几乎立即再次被触发。
在这种情况下,查询存储对于数据库来说完全不可用。有什么办法可以解决这个问题吗?
更新日期 2024-08-01
根据 Paul White 评论的提示,我对 CE_FEEDBACK 数据库范围选项进行了一些实验,它似乎是真正的触发器。行为如下所述:
我以前尝试的未清除的查询存储(仅几百个捕获查询)作为起点。
工作量类型
由于该问题似乎也与工作负载类型有关,我将尝试在这里描述它,因为模式非常简单。它几乎 99% 都是临时的,具有以下查询模式:
我可以想象,很可能工作量的第一部分对于 CE_FEEDBACK 来说就是有问题的部分。
我们创建了一个扩展事件会话来捕获严重级别为 16 或更高的错误:
CREATE EVENT SESSION [Error_Reporting] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([severity]>(15)))
ADD TARGET package0.event_file(SET filename=N'https://storageaccount.blob.core.windows.net/logs/Errors.xel',max_file_size=(10240), max_rollover_files=1)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
现在,我们可以通过运行以下命令来查询它:
select cast(event_data as xml), timestamp_utc
from sys.fn_xe_file_target_read_file('https://storageaccount.blob.core.windows.net/logs/Errors_0_133667581914690000.xel', NULL, NULL, NULL)
列event_data
是 nvarchar 类型但包含 XML 值,代表一个表。
问题:
在 SSMS 中的文本编辑器中查询时,如何将event_data
列转换(分解)为多个列(例如用户名、错误号、类别、目标、消息等)?
XML 数据示例:https://pastecode.io/s/tjg7jo40