我正在调试 Azure PostgreSQL 灵活服务器的高 CPU 负载问题。在 CPU 使用率激增的时间窗口中,我看到大量实用程序查询。处理它们的时间并不长,但数量却有数十万个。
查询都是DISCARD ALL
查询。这些查询的目的是什么?出现这么多查询是否正常?
我有 3 个数据表(都包含更多列,但这些是我需要的每个表):
t1:表1
工作ID1 | h1Amt | h1Dt | 地位 |
---|---|---|---|
101 | 73.00 | 12/1/2023 | A |
124 | 0.00 | 2023年10月30日 | D |
第251章 | 0.00 | 2023 年 3 月 1 日 | A |
255 | 24:00 | 11/30/2023 | A |
256 | 0.00 | 12/1/2023 | A |
t2:表2
工作ID2 | h2Amt | h2Dt | 类型1 |
---|---|---|---|
101 | 第0073章 | 202312 | 2 |
101 | 0000 | 202308 | 13 |
101 | 0000 | 202307 | 1 |
124 | 0000 | 202310 | 3 |
124 | 0000 | 202306 | 2 |
124 | 0000 | 202304 | 13 |
124 | 第0079章 | 202301 | 3 |
第251章 | 0000 | 202303 | 2 |
第251章 | 第1154章 | 202301 | 3 |
第251章 | 0000 | 202212 | 3 |
255 | 第0024章 | 202311 | 2 |
255 | 0000 | 202212 | 3 |
255 | 0000 | 202210 | 13 |
255 | 第0175章 | 202209 | 3 |
256 | 0000 | 202312 | 1 |
256 | 0000 | 202309 | 13 |
256 | 0000 | 202307 | 3 |
256 | 第0583章 | 202305 | 2 |
t3:表3
工作ID3 | h3Dt | 类型2 |
---|---|---|
101 | 12/1/2023 | 2 |
101 | 2023 年 8 月 13 日 | 13 |
101 | 2023 年 7 月 1 日 | 1 |
124 | 2023年10月1日 | 3 |
124 | 2023 年 6 月 1 日 | 2 |
124 | 2023 年 4 月 11 日 | 13 |
124 | 2023 年 1 月 1 日 | 3 |
第251章 | 2023 年 3 月 1 日 | 2 |
第251章 | 2023 年 1 月 1 日 | 3 |
第251章 | 2022年12月1日 | 3 |
255 | 11/1/2023 | 2 |
255 | 20022年12月1日 | 3 |
255 | 2022年10月7日 | 13 |
255 | 2022 年 9 月 1 日 | 3 |
256 | 12/1/2023 | 1 |
256 | 2023年9月5日 | 13 |
256 | 2023 年 7 月 1 日 | 3 |
256 | 2023 年 5 月 1 日 | 2 |
在 t1 中,每个 workID 只有一行,这是该 workID 的当前事务。随着数据的添加,所有 3 个表都会更新,其中 t1 仅显示当前事务。在 t2 和 t3 中,有多行,因为它显示每个 workID 的历史事务。
h1Amt 和 h2Amt 列显示相同的成本,但格式不同。此外,h1Dt、h2Dt 和 h3Dt 都反映相同的日期,只是格式不同。
我需要找到特定的当前值以及该值上次更改的时间。就我而言,我正在搜索 0.00 或 0000 的金额以及最后一次更改为该值的时间。此外,状态 (t1) 必须为“A”,类型(t2 或 t3)必须为 1、2 或 3。
这是我正在寻找的输出:
工作ID | 数量 | 日期 | 类型 |
---|---|---|---|
第251章 | 0.00 或 0000 | 202303 或 3/1/2023 | 2 |
256 | 0.00 或 0000 | 202307 或 7/1/2023 | 3 |
这是我到目前为止想到的,但它返回的金额当前不是 0000 (0.00),或者没有显示当前为 0000 (0.00) 的 workID。
select t2.workID2
,min(t2.h2Dt)
,min(t2.h2Amt)
from table2 t2
inner join table1 t1 on t2.workID = t1.workID
where not exists (
select 1
from table2
where workID2 = t2.workID2
and h2Amt <> t2.h2Amt
and h2Dt > t2.h2Dt
)
and t2.h2Amt = '0000'
and t1.status = 'A'
and t2.type1 in ('1','2','3')
group by t2.workID2
我没有使用table3,主要是因为table2包含需要的h2Amt。我认为使用 table1 中的 h1Amt 对我来说并不明智,因为它只显示当前的内容。由于状态列,我至少需要使用 table1 。如果我确实使用 table3,则 table1 将是一个要求,因为它是唯一具有状态列的表,但我认为我会遇到查找历史金额的问题,因为它没有记录在 table1 或 table3 中。另外,我遇到了在我尝试过的其他查询中获取重复行的问题,包括使用所有 3 个表。
上面是我在大量搜索类似情况后看到的最接近的查询。
希望我已经提供了足够的细节来找到解决方案。提前致谢!
Postgres 的查询规划器是否能够WHERE
根据交换性和结合性规则重新排列子句中的术语?
例如,以下WHERE
子句是否都会产生具有大致相同时间复杂度的查询计划?:
WHERE a AND b AND c
WHERE (a AND b) AND c
WHERE a AND (b AND c)
WHERE (c AND b) AND a
我有一个 Postgres 表,其中包含大量索引列(总共大约有 100 个索引列,是的,我需要全部索引列,是的,它们都需要单独索引)。任何行更新都会导致所有索引被更新,这对于数据库引擎来说是大量的工作。
我想了解 Postgres 文档页面上标题为“索引锁定注意事项”的讨论的并发含义,以及Postgres 是单线程(多进程)的事实,即当前设计如何影响读取器和写入器的性能。考虑到我有这么多的列索引,大量的并发查询。
我对这些事情的解释如下(如有错误,请指正):
我的问题是:
给定一个这样的表,
我想通过 SQL 创建一个表而不执行窗口函数
id | time| attribute1| attribute1atFirstRecordedTimeperID
1 | 0 | a | a
1 | 2 | b | a
2| 0 | c | c
2| 4 | d | c
我试过
SELECT
t1.id,
t1.time,
t1.attribute1,
attribute1fixedattime0 = t2.attribute1
FROM s t1
INNER JOIN (
SELECT attribute1 AS attribute1fixedattime0
FROM s
WHERE time = 0
) t2 ON t1.id = t2.id
列别名是否正确?
如果我修复此代码,我想让 SQL 代码适用于 id=2 没有在时间 t=0 记录其第一个 attribute1 的情况,但适用于 t>0。那么我是在连接条件上还是在连接内部做一分钟的操作呢?
我想通过 SQL 创建一个表,而不需要执行窗口函数来转
进入
去从
编号 | 时间| 属性1
1 | 0 | a
1 | 2 | b
2| 42 | c
2| 69 | d
到餐桌上
id | time| attribute1| attribute1atFirstRecordedTimeperID
1 | 0 | a | a
1 | 2 | b | a
2| 42 | c | c
2| 69 | d | c
如果你使用 row_number() 窗口函数来标记每个 id 中第一次出现的位置,那么你如何加入或者你不会使用加入而是使用其他东西?
SELECT
id,
time,
attribute1,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY time) AS rn
FROM your_table
SELECT
id,
time,
attribute1,
FIRST_VALUE(attribute1) OVER (PARTITION BY id ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS attribute1atFirstRecordedTimeperID
FROM (
-- Previous query here
) AS subquery
我注意到导入截断的数据只会引发警告。T 认为通过处理程序捕获该警告并发出错误信号会很简单,但我似乎无法做到这一点。有什么建议么?
CREATE OR REPLACE PROCEDURE FILE_IMPORT_HANDLER(filename VARCHAR(255), tablename
VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '22001'
SIGNAL SQLSTATE '75002'
SET MESSAGE_TEXT = '...';
CALL ADMIN_CMD('import from ' || filename || ' of del insert into ' || tablename);
END @
如果遇到22001我愿意努力失败。从某种意义上说,提高导入过程中数据截断的严重性
摘要:我有一个 2 节点集群,如果其中一个节点发生故障,我将无法登录。我收到错误。
Connection error: ('Unable to connect to any servers', {'192.168.1.104:9042': AuthenticationFailed('Failed to authenticate to 192.168.1.104:9042: Error from server: code=0100 [Bad credentials] message="Unable to perform authentication: Cannot achieve consistency level LOCAL_QUORUM"')})
这是一个屏幕截图,显示我没有使用 cassandra SUPERUSER 但收到错误:
我希望即使一个节点发生故障,登录也能继续工作。
我在内联表值函数中有一个递归 CTE。ITVF 返回一个 ID 列表,其中包含一个人的一长串祖先序列,它通常会循环大约 12 到 18 次才能到达末尾。它的速度相当快,但在许多人使用时,估计会出现错误,因此它变得非常慢。
CTE 看起来像这样
WITH ancestors AS (
SELECT
IndID,
AncestorID
FROM
dbo.persons
UNION ALL
SELECT
IndID,
AncestorID
FROM
ancestors a
INNER JOIN dbo.persons p ON p.IndID = a.AncestorID
)
SELECT IndID, AncestorID FROM ancestors
我有一千万行,所以这是一个相当大的表。当我请求一个 IndID 时,执行计划说它估计有 7 行,但实际得到了 1300 行。对于单个请求来说,它是可以接受的(运行时间不到一秒),但如果我将它加入到另一个请求中,那么它就会被调用,比方说 100 次,速度就会下降,因为估计越来越差。
需要明确的是,即使在 IVTF 之外也存在估计误差。我只是为了清楚地表明我不能只使用临时表。它需要保留在 IVTF 中,以便我可以将其加入更大、更复杂的请求中,并且它保持可并行性。我可以做什么来更好地估计行数?
更新:粘贴计划
更新 2:不太简化
我有点陷入两个问题之间。要么我使用 MSTVF 并且所有查询都无法并行化,要么我使用 ITVF 并希望 SQL 之神慷慨,不要严重低估行数,因此现在所有内容都在硬盘驱动器而不是 RAM 上交换。我希望这只是我的愚蠢,而且这是一个愚蠢的简单解决办法。
更新 3 据我所知回答所提出的问题。
uno)更新到最新的累积更新。没有按预期改变任何内容,但正如您所说,保持最新状态是件好事:)
dos)我们使用标准版,但我确实有一个列存储,但我不记得为什么这样做。它位于 IndID、FirstNameID、LastNameID 上。我会尝试删除它,今天我们只有 2 个数据库用户,如果它崩溃了其他问题,我们可以管理停机时间。
去掉ColumnStore后,确实节省了大约30秒!虽然还是慢,但已经好多了。我必须检查我的笔记以找出为什么要使用该列存储。
dos:第2部分)你所感受到的“动力不足的盒子”的感觉正是让我到现在为止的原因。我以为我们的机器动力不足,但与这里的 IT 人员交谈后,他们说我们没有使用超过 25% 的可用资源,因此瓶颈肯定是在 SQL 级别。因此,我上个月要求将 SQL 2017 更新到 2022,然后,现在我发现我的大多数繁重查询总是以序列化方式运行,所以开始优化,直到我遇到了这一点。我尝试过OPTION(USE HINT('DISALLOW_BATCH_MODE'), MAXDOP 8);
,但没有看到速度有任何变化。
tres) 该请求确实应该返回大约 1400 万行,所以这方面不用担心。但是,资源预留中只估计了 8 行,这难道不是它比应有的速度慢得多的原因吗?
更多上下文)我在本月所有工作之前使用的是 MSTVF,当我切换到 IVTF 时,速度更快,但如果您明白我的意思,则花费的时间与请求的行数的曲线是指数而不是线性的。我愿意重新思考这一切是如何完成的。
我在一个研究小组工作,我的部分工作是为研究人员提取数据集。我几乎是数据库上唯一的重度用户,我的同事更多地从事工作的“插入和清理数据”部分。所以我几乎可以用索引、函数等做我想做的事,只要表结构本身不改变太多。
更新 4 - 什么? 我不明白,我试图制作一个漂亮的图形来显示“花费的时间与询问的行数”指数曲线,所以我更改了查询以获得漂亮的平方数。
select
count(*)
FROM
(SELECT TOP 10000 * FROM individus.Individus WHERE AnneeNaissance > 1901 AND AnneeDeces < 1911) i CROSS APPLY
individus.GetAscendanceSimple(i.IndID) a
它在 10 秒内运行...即使尝试了 TOP 10,000,000 并且仍然很快,所以我只需要输入一个任意大的数字,这样我的所有案例都被覆盖,并且它运行得像我希望的那样快(TOP 很重要)。在将其作为解决方案之前,我一定是错的不是吗?如果我们需要做的就是修复规划,那么这真是一个愚蠢的修复。
我在 Azure 中设置了两个 SQL Server VM,其中包含可用性组和透明数据加密,密钥驻留在 keyvault 中。我认为 SQL 服务器只需在启动/服务重新启动时联系 keyvault 即可获取密钥,但它似乎每隔 15 分钟左右就会发出一次“展开”请求,这对我来说似乎很奇怪。keyvault 的 SQL Server 连接器实际上需要多久联系一次 keyvault?为什么要这样做?是否有什么东西可以触发它发生?
感谢所提供的任何帮助。🙂
我正在尝试通过删除然后重新创建它来修复作业。但我不能,因为当我尝试删除时它似乎不存在,而当我尝试创建时它似乎已经存在
Begin
dbms_scheduler.drop_job(job_name => 'add_error_rate_sig');
End;
[2024-01-24 13:12:33] [99999][27475]
[2024-01-24 13:12:33] ORA-27475: unknown job "SREAPP"."ADD_ERROR_RATE_SIG"
[2024-01-24 13:12:33] ORA-06512: at "SYS.DBMS_ISCHED", line 278
[2024-01-24 13:12:33] ORA-06512: at "SYS.DBMS_SCHEDULER", line 751
[2024-01-24 13:12:33] ORA-06512: at line 2
[2024-01-24 13:12:33] Position: 0
SREAPP> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'add_error_rate_sig',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SREAPP.add_error_rate_sig(null); END;',
number_of_arguments => 0,
start_date => TO_TIMESTAMP_TZ('2024-01-24 00:00:00.000000000 AMERICA/LOS_ANGELES',
'YYYY-MM-DD HH24:MI:SS.FF TZR'),
repeat_interval => 'FREQ=WEEKLY;BYHOUR=01;BYMINUTE=00',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'push to error rate summary'
);
END;
[2024-01-24 13:12:44] [99999][27477]
[2024-01-24 13:12:44] ORA-27477: "SREAPP"."ADD_ERROR_RATE_SIG" already exists
[2024-01-24 13:12:44] ORA-06512: at "SYS.DBMS_ISCHED", line 175
[2024-01-24 13:12:44] ORA-06512: at "SYS.DBMS_SCHEDULER", line 286
[2024-01-24 13:12:44] ORA-06512: at line 2
[2024-01-24 13:12:44] Position: 0