我在私有云环境中部署了CoC版本的PieCloudDB。我的 S3 服务由三台机器组成的集群。在部署PieCloudDB数据库的过程中,我指定其中一台机器作为S3主机。现在,在查询过程中,我发现该特定主机上出现了 S3 网络瓶颈。如何解决这个瓶颈问题?
我有一个查询从表中选择不同的值,并且我注意到初始执行后执行时间显着减少。
这是我第二次执行查询时得到的查询和相应的执行计划。
EXPLAIN ANALYZE SELECT DISTINCT integration_type FROM my_schema.my_table;
Unique (cost=0.43..576843.69 rows=7 width=2) (actual time=0.032..2826.863 rows=8 loops=1)
-> Index Only Scan using my_index on my_schema.my_table (cost=0.43..538707.38 rows=15254521 width=2) (actual time=0.031..1881.219 rows=14730886 loops=1)
Heap Fetches: 1381786
Planning Time: 0.557 ms
Execution Time: 2826.897 ms
我第一次执行查询,花了30多秒。然而,即使堆获取数量保持不变且没有变化(仍然是 1,381,786),连续执行的时间始终少于 3 秒。
我很好奇为什么尽管堆获取没有变化,但连续执行的执行时间却减少了。这是否与 PostgreSQL 采用的缓存机制或其他优化有关?
我使用的是 PostgreSQL 版本 14.9
任何关于为什么会发生这种行为以及如何进一步优化查询或数据库设置的见解或建议将不胜感激。
谢谢。
我正在设计一个 Web 应用程序,卖家可以在其中提供他们的汽车,银行提供各种融资优惠(例如 36 个月、25% 首付、25% 最终付款)。买家来到这个网络应用程序,并根据各种搜索条件搜索汽车:例如,年龄小于 5 岁,月付款低于 500 美元,红色汽车每月费用低于 350 美元,合同期限为 36 或 48 个月。
在我的系统中,我有列表,每个列表可能最多有 18 个计算。
清单就是一辆汽车。为简洁起见,列表具有以下属性:id、颜色、里程。
计算是融资要约。每个计算都有以下属性:id、listingId、financeProviderId、months、downPayment、finalPayment、monthlyRate。
在数据库中我有两个表:列表和计算。
CREATE TABLE IF NOT EXISTS public.calculation
(
id uuid NOT NULL,
"listingId" uuid NOT NULL,
"financeProviderId" smallint NOT NULL,
"downPayment" numeric(10,2) NOT NULL,
"finalTerm" numeric(10,2) NOT NULL,
rate numeric(10,2),
CONSTRAINT calculation_pkey PRIMARY KEY (id),
CONSTRAINT "calculation_listingId_fkey" FOREIGN KEY ("listingId")
REFERENCES public.listing (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
CREATE INDEX IF NOT EXISTS "calculation_listingId"
ON public.calculation USING btree
("listingId" ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS "calculation_downPayment"
ON public.calculation USING btree
("downPayment" ASC NULLS LAST)
TABLESPACE pg_default;
-- similar indices for all the other fields
CREATE TABLE IF NOT EXISTS public.listing
(
id uuid NOT NULL,
color integer,
mileage integer,
CONSTRAINT listing_pkey PRIMARY KEY (id)
)
CREATE INDEX IF NOT EXISTS listing_mileage
ON public.listing USING btree
(mileage ASC NULLS LAST)
TABLESPACE pg_default;
-- similar indices for constructionYear and other attributes
当用户搜索要购买的汽车时,他们希望看到符合其搜索条件的汽车分页列表以及匹配汽车的总数。
获取列表通常不成问题,因为列表页面最多只显示 20 辆汽车。
但是每个 COUNT 查询都非常慢(2-20 秒),尽管数据库上还没有负载(产品在发布之前)。下面是一个这样的查询,它想要计算颜色 ID 7 且里程数少于 75000 英里、首付 0%、最终付款 25% 且月费低于 350 美元的列表数量。
SELECT COUNT(DISTINCT "l"."id")
FROM "listing" as "l"
INNER JOIN "calculation" as "ca" ON "l"."id" = "ca"."listingId"
WHERE
"l"."color" = 7 AND "mileage" < 75000
AND "ca"."downPayment" = 0 AND "ca"."finalTerm" = 25 AND monthlyRate < 350
在系统中我有大约。30 万条列表和 150 万次计算。(并非每个列表都有所有 18 种可能的计算,例如旧车在 60 或 72 个月内不会收到报价。)
我正在使用 AWS Aurora Postgres Serverless V2。但我猜 COUNT 查询速度慢是 Postgres 的一个普遍问题。另外,我很惊讶如此少量的数据却会导致如此糟糕的性能。
现在我问我能做些什么来加快计数查询。我的目标是让 COUNT 查询的运行时间低于 100 毫秒,但我可以忍受低于 350 毫秒。
在 Postgres 上快速执行 COUNT 次查询有什么秘诀吗?
假设我们有以下数据库:
或者伪sql格式(所有列都不为空):
create table road (
id int PK
)
create table item_on_road (
id int PK
road_id int FK road.id
position_order int
UNIQUE (road_id, position_order)
)
create table rock (
id int PK FK item_on_road.id
size float
)
// and so on
我们有一辆road
可以朝一个方向行驶的车。
沿着这条路,我们可以找到不同的东西,item_on_road
以便我们遇到它们,position_order
例如1,2,3
我们在路上发现的东西可能会有所不同,从sign
到gas_station
。
现在我想问一个问题:
这个数据库设计是否符合db 1-5th范式?我认为不会,因为我们可以
item_on_road
在没有相应的情况下创建条目rock
如果它不符合第五范式,该怎么办?
可选问题:
如果1个问题的答案是肯定的,如何确保
rock
ifitem_on_road
create的存在?如果 1 个问题的答案是肯定的,如何确保我们无法创建
rock
并sign
指向相同的 id?
PROD
我们在和之间设置了数据库镜像,SECONDARY
并且已故障转移到辅助副本 ( SECONDARY
)。现在,我们在服务器日志中看到许多几乎持续失败的登录PROD
。
应用程序使用连接字符串的故障转移伙伴属性:
Data Source=PROD;Failover Partner=SECONDARY;Initial Catalog=myDataBase;
我们已经确认它们正在按预期工作。
这是有道理的,显式指定的数据库PROD
无法打开,因为它由于故障转移而处于恢复状态,但应用程序已成功连接到SECONDARY
。此错误消息是否是故障转移数据库镜像主副本上的预期行为?
下面使用一个简单的、基于 ODBC 的场景复制了该问题
设置镜像数据库:
在主服务器上创建登录名
USE Mirroring
CREATE LOGIN MirrorUser WITH PASSWORD = 'MyPassHere', DEFAULT_DATABASE = 'Mirroring'
CREATE USER MirrorUser
ALTER ROLE db_datareader ADD MEMBER MirrorUser
和次要的:
CREATE LOGIN MirrorUser WITH PASSWORD = 'MyPassHere',SID = <Sid from primary here>
在 Access 数据库中设置链接表
将数据库故障转移到辅助数据库:
ALTER DATABASE Mirroring SET PARTNER FAILOVER
打开Access链接表并验证我们可以看到故障转移后的数据:
观察 .\PROD 上的错误
我的任务是将数据插入到几个表中,并在一个查询中从它们的交集返回数据。这些表通过特定字段链接(solditems
指products
和invoices
、invoices
指customers
等)。这里的技巧是id
ininvoices
是在行插入时自动设置的。以下查询返回空结果,除非我删除JOIN
on invoices
。如果SELECT
在单独的查询中完成,则一切正常。怎么了?
WITH newid AS (
INSERT INTO invoices (customer,idate)
VALUES (777,(SELECT now()::date))
RETURNING id
),
sold AS (
INSERT INTO solditems (invoiceid,prod,qty)
VALUES ((SELECT id FROM newid), 888, 1),
((SELECT id FROM newid), 999, 2)
RETURNING *
)
SELECT * FROM sold AS s
JOIN products AS p ON p.id=s.prod
JOIN invoices AS i ON i.id=s.invoiceid;
我认为执行此操作的方法是转到 SSMS 中的现有数据库(Sql Server 16.0.1105.1 开发人员版)并运行 Tasks | 生成脚本...
这包括一个 FullTextTable。所以我得到了以下脚本(仅显示开始):
USE [master]
GO
/****** Object: Database [LouisHowe] Script Date: 11/15/2023 5:56:31 PM ******/
CREATE DATABASE [LouisHowe]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'LouisHowe', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\LouisHowe.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'LouisHowe_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\LouisHowe_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [LouisHowe] SET COMPATIBILITY_LEVEL = 160
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [LouisHowe].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [LouisHowe] SET ANSI_NULL_DEFAULT OFF
看来数据库已经创建好了。但我收到了近 200 条错误消息(仅显示前几条):
Msg 40508, Level 16, State 1, Line 1
USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'ON'.
Msg 319, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 5011, Level 14, State 5, Line 12
User does not have permission to alter database 'LouisHowe', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 12
ALTER DATABASE statement failed.
我需要做什么来修复脚本?
我为一个应用程序创建了一个数据库,其中有多个网络,其中有多个节点和元素以及多个场景,所有这些都与网络相关。[见下图]
我的问题是:由于sce_results
andsce_paramaters
将具有网络的结果和参数信息nodes
,elements
我是否还应该通过外键在nodes
&elements
表和sce_results
&表之间建立这种关系?sce_paramaters
或者因为它变得复杂,我创建表格的方式很好?也许更普遍的问题是,什么时候通过外键建立这些关系比较好?
到目前为止,我虽然因为nodes
&有了它们的名字,但在我下载了所有必要的数据以进行进一步计算或创建结果图等之后,我可以将elements
它们与&表name
中的列进行匹配......因此我并不真正需要这种关系我的观点。你怎么认为?sce_results
sce_paramaters
例子:
Network1: Nodes: [Node1, Node2, Node3]
Elements: [Element1, Element2]
Scenarios: [ScenarioX]
ScenarioX: Paramater: [Pressure_Node1 = 5, Pressure_Node2 = 10, ...]
Result: [Flow_Node1 = 100, ...]
# In this case nodes table looks like:
id | net_id | name | ... | x | y
1 1 Node1
2 1 Node2
...
# In this case sce_parameter table looks like:
id | sce_id | name | extension | value | unit
1 1 Node1 Pressure 5
2 1 Node2 Pressure 10
...