这涉及如何在SSIS中调试脚本组件,该组件要求2008版本,现在已经过时了。这就是为什么这个问题需要在当今的版本中重新提出。
我在 SSIS 数据流任务的两个脚本组件中放置了一个断点。只有第一个脚本组件被调试,当我到达第二个脚本组件时,脚本就会运行到最后,不会在任何断点处停止。
如何调试第二个脚本组件以便调试器在断点处停止?
这涉及如何在SSIS中调试脚本组件,该组件要求2008版本,现在已经过时了。这就是为什么这个问题需要在当今的版本中重新提出。
我在 SSIS 数据流任务的两个脚本组件中放置了一个断点。只有第一个脚本组件被调试,当我到达第二个脚本组件时,脚本就会运行到最后,不会在任何断点处停止。
如何调试第二个脚本组件以便调试器在断点处停止?
很明显,在会话上设置synchronous_commit OFF
- 其超级罕见的缺点(据称在服务器崩溃的情况下会丢失提交的事务) - 很好,可以提高这些会话的性能,因为它们不必等待 WAL刷新到磁盘。
但是,我无法理解的是这如何影响在同一时间窗口内提交的所有会话synchronous_commit ON
。
据我了解,synchronous_commit OFF
产生 WAL 的会话与拥有 WAL 的会话一样多ON
(甚至更多,因为它们在同一时间完成更多工作,因为它们不必等待 WALWrite)。那么拥有它的会话ON
会像所有会话都拥有它一样慢吗ON
?
场景 1:10 个执行大量 DML 的会话拥有它ON
,10 个会话拥有它OFF
。
场景 2:20 个执行大量 DML 的会话ON
。
我认为会发生什么:在场景 1 中,10 个会话OFF
产生的 WAL 比场景 2 中更多,因此会话ON
等待 WALWrite 的时间比场景 1 中更长。
我是否正确理解每次提交总是等待所有当前(xid
<=自己的xid
)WAL 数据被写入而不仅仅是“它自己的”WAL 数据?
这占用:
是否可以在数据流源中使用临时表?; 答案是:
“不”,因为如果不立即丢弃它就无法通过。
(请注意,即使下面的工作答案也无法解决数据源项的这个问题,它不给您选择是要关闭还是释放连接,它总是关闭连接,并且临时表将被删除。 )
由此,我认为我应该测试保持两个 DFT 项之间的连接,以便临时表不会被删除。
使用“tempdb”数据库的连接管理器,我可以在控制流中创建临时表,这些表在控制流内的下一步中仍然存在,请参阅在 SSIS 中使用临时表?。你可能会想,如何在SSIS控制流任务中创建临时表,然后在数据流任务中使用它?已经回答了这个问题,我发现那里的答案并没有解决 DFT 中一步后删除临时表的问题。
我使用 ADO.NET 连接管理器测试了 DFT,临时表在脚本组件之后仍然被删除。
我在一个依次放置的脚本组件上测试了它:
RetainSameConnection
为 并True
不能修复它我设置RetainSameConnection
为True
其他链接中的那样,但这并没有解决问题。
我尝试通过不在第二个脚本组件中建立新连接而只是获取它来进行尝试,但无济于事:
base.PostExecute();
和conn.Close();
都被注释掉,代码取自如何在不离开 SSIS 数据流任务 C# 脚本组件的情况下使用来自数据源项的传入数据创建并填充临时表?: public override void PostExecute()
{
//base.PostExecute();
// here we would bulk copy data into the temp table
// you may need to batch this operation or handle it differently based on your situation
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
// Now write that DataTable to the database
bulkCopy.DestinationTableName = "##tmpTable";
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
//conn.Close();
}
(SqlConnection)Connections.Connection.AcquireConnection(Transaction);
: public override void AcquireConnections(object Transaction)
{
base.AcquireConnections(Transaction);
conn = (SqlConnection)Connections.Connection.AcquireConnection(Transaction);
}
在第一个脚本组件结束后,临时表已被删除,因为即使我不关闭连接,默认情况下连接似乎也会丢失。
我希望找到一个答案,通过某种技巧使临时表保持活动状态。它必须只能在 SSIS 中工作,我不能只是在 SSMS 中创建临时表以使其在 SSIS 中保持活动状态。
如果 DFT 在留下任何 DFT 项后删除临时表,我如何在工作连接管理器中保持临时表处于活动状态,或者解决方法是什么?
在架构 2 中创建视图表以从架构 1 上的表中提取数据是否会影响数据库或导致任何其他问题?我的代码:
CREATE VIEW "test"."Excavation" AS
SELECT *
FROM "public"."Distribution_UG";
假设我们有两个表user
和book
。
在应用程序级别,为了确保数据完整性并避免竞争条件,我们在事务开始时锁定实体。
假设在应用程序级别我们有两个请求同时运行:
请求 1(应用程序级别):
start trx
lock for update user with id 1
lock for update book with id 2
do changes
commit
请求 2(应用程序级别):
start trx
lock for update book with id 2
lock for update user with id 1
do changes
commit
可能会出现两个请求第一个锁都先于第二个锁申请的情况,即用户 1 的请求 1 锁和图书 2 的请求 2 锁,这将导致死锁。
我们怎样才能避免这种情况呢?我唯一的想法是在某个地方写下所有表的锁定顺序,user->book
并确保我们遵循它。
如果我们在不同的请求中执行两个查询,以在突变之前锁定实体
SELECT * FROM user WHERE id IN [1,2] FOR UPDATE
SELECT * FROM user WHERE id IN [2,1] FOR UPDATE
这会导致僵局吗?例如,如果出于某种原因,一个查询将首先锁定用户 1,然后切换到请求 2,然后再返回锁定用户 2。
我正在使用 python 脚本将一些数据加载到我的 postgres 数据库中。这些数据都在巨大的csv文件中,所以我懒洋洋地读取它们并对批量数据调用INSERT语句。然而,这些数据在某种意义上都是“连接的”,因此将整个数据加载管道维护为单个事务是很好的,这样如果其中的某些部分失败,则可以全部回滚。
我的问题是:我正在努力让这些巨大的 csv 文件在我的 python 脚本中保持在内存不足的状态,但是,由于整个数据加载是一个事务,所有数据最终都会在 postgres 端内存中吗东西的?单个 INSERT 事务是否必须将其所有数据完全放入内存中?
文档说
如果订阅已配置 run_as_owner = true,则不会发生用户切换。相反,所有操作都将在订阅所有者的权限下执行。在这种情况下,订阅所有者只需要对目标表进行 SELECT、INSERT、UPDATE 和 DELETE 的权限,而不需要对表所有者设置 ROLE 的权限。但是,这也意味着拥有正在复制的表的任何用户都可以使用订阅所有者的权限执行任意代码。例如,他们可以通过简单地将触发器附加到他们拥有的表之一来完成此操作。由于通常不希望允许一个角色自由地承担另一个角色的权限,因此应避免使用此选项,除非不关心数据库内的用户安全。
我不确定我是否理解为什么这是一个安全风险。表所有者不是总是可以强制使用其表的角色执行代码吗?我天真的直觉是,表所有者是一个敏感角色,正是因为它具有这些权限,而订阅所有者角色可以具有非常严格的权限(仅对其表进行 CRUD),为什么这种直觉是错误的?
我的系统(OpenMage / Magento 1)正在运行一些如下所示的查询,尽管它没有使用任何大型表,但它们似乎需要异常长的执行时间。有时他们需要几分钟,有时他们会在一天后超时。
例如
# Time: 240405 7:37:19
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8345298 Schema: db_example3 QC_hit: No
# Query_time: 140.075668 Lock_time: 0.000176 Rows_sent: 1 Rows_examined: 15
# Rows_affected: 0 Bytes_sent: 148
# Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 253976
# Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
#
# explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
# explain: 1 SIMPLE ram_idx ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE 4 const 2 2.00 100.00 100.00 Using where; Using index; Using temporary; Using filesort
# explain: 1 SIMPLE gdiktia_idx ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID 8 db_example3.ram_idx.entity_id,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE primary_camera_idx range PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE 4 NULL 2 2.00 75.00 25.00 Using where; Using index; Using join buffer (flat, BNL join)
# explain: 1 SIMPLE screen_resolution_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.ram_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE memory_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.ram_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE second_camera_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.ram_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE nfcsosto_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.ram_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE price_index eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE PRIMARY 8 db_example3.ram_idx.entity_id,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE core_count_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.ram_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE megethossim_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.ram_idx.entity_id,const,const,const 1 1.00 100.00 100.00 Using index
# explain: 1 SIMPLE cat_index eq_ref PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC PRIMARY 10 const,db_example3.ram_idx.entity_id,const 1 1.00 100.00 100.00 Using where
# explain: 1 SIMPLE e eq_ref PRIMARY PRIMARY 4 db_example3.ram_idx.entity_id 1 1.00 100.00 100.00 Using index
#
SET timestamp=1712291839;
SELECT `gdiktia_idx`.`value`, COUNT(gdiktia_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '17'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `catalog_product_index_eav` AS `screen_resolution_idx` ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '234'
INNER JOIN `catalog_product_index_eav` AS `core_count_idx` ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND core_count_idx.store_id = 1 AND core_count_idx.value = '41'
INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '54'
INNER JOIN `catalog_product_index_eav` AS `memory_idx` ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = 197 AND memory_idx.store_id = 1 AND memory_idx.value = '62'
INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '315'
INNER JOIN `catalog_product_index_eav` AS `second_camera_idx` ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '90'
INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
INNER JOIN `catalog_product_index_eav` AS `gdiktia_idx` ON gdiktia_idx.entity_id = e.entity_id AND gdiktia_idx.attribute_id = 232 AND gdiktia_idx.store_id = '1' GROUP BY `gdiktia_idx`.`value`;
或者
# Time: 240405 7:34:29
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8344334 Schema: db_example3 QC_hit: No
# Query_time: 74.418149 Lock_time: 0.000100 Rows_sent: 0 Rows_examined: 0
# Rows_affected: 0 Bytes_sent: 142
# Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
#
# explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
# explain: 1 SIMPLE extra_specs_idx ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID 2 const 1 0.00 100.00 100.00 Using where; Using index; Using temporary; Using filesort
# explain: 1 SIMPLE manufacturer2_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.extra_specs_idx.entity_id,const,const,const 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE primary_camera_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.extra_specs_idx.entity_id,const,const,const 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE second_camera_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.extra_specs_idx.entity_id,const,const,const 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE ram_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.extra_specs_idx.entity_id,const,const,const 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE nfcsosto_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.extra_specs_idx.entity_id,const,const,const 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE operating_system_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.extra_specs_idx.entity_id,const,const,const 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE megethossim_idx eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity PRIMARY 12 db_example3.extra_specs_idx.entity_id,const,const,const 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE price_index eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE PRIMARY 8 db_example3.extra_specs_idx.entity_id,const,const 1 NULL 100.00 NULL Using where
# explain: 1 SIMPLE cat_index eq_ref PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC PRIMARY 10 const,db_example3.extra_specs_idx.entity_id,const 1 NULL 100.00 NULL Using where
# explain: 1 SIMPLE e eq_ref PRIMARY PRIMARY 4 db_example3.extra_specs_idx.entity_id 1 NULL 100.00 NULL Using index
# explain: 1 SIMPLE screen_type_idx range PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE 4 NULL 2 NULL 75.00 NULL Using where; Using index; Using join buffer (flat, BNL join)
#
SET timestamp=1712291669;
SELECT `extra_specs_idx`.`value`, COUNT(extra_specs_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '3'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `catalog_product_index_eav` AS `manufacturer2_idx` ON manufacturer2_idx.entity_id = e.entity_id AND manufacturer2_idx.attribute_id = 186 AND manufacturer2_idx.store_id = 1 AND manufacturer2_idx.value = '6'
INNER JOIN `catalog_product_index_eav` AS `screen_type_idx` ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND screen_type_idx.store_id = 1 AND screen_type_idx.value = '37'
INNER JOIN `catalog_product_index_eav` AS `operating_system_idx` ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = 195 AND operating_system_idx.store_id = 1 AND operating_system_idx.value = '48'
INNER JOIN `catalog_product_index_eav` AS `primary_camera_idx` ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309'
INNER JOIN `catalog_product_index_eav` AS `second_camera_idx` ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '87'
INNER JOIN `catalog_product_index_eav` AS `megethossim_idx` ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
INNER JOIN `catalog_product_index_eav` AS `ram_idx` ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '52'
INNER JOIN `catalog_product_index_eav` AS `nfcsosto_idx` ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
INNER JOIN `catalog_product_index_eav` AS `extra_specs_idx` ON extra_specs_idx.entity_id = e.entity_id AND extra_specs_idx.attribute_id = 213 AND extra_specs_idx.store_id = '1' WHERE ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END)) >= 99.995000) AND ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN 0.2400 ELSE 0 END)) < 199.995000) GROUP BY `extra_specs_idx`.`value`;
据我所见,表正在使用索引,并且通常没有任何内容写入磁盘上,一切都发生在内存中,因此我不确定如何优化并使它们运行得更快。
我的 mariadb 版本是
Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004-log mariadb.org binary distribution
这是表定义
create table db_example3.catalog_category_product_index
(
category_id int unsigned default 0 not null comment 'Category ID',
product_id int unsigned default 0 not null comment 'Product ID',
position int null comment 'Position',
is_parent smallint unsigned default 0 not null comment 'Is Parent',
store_id smallint unsigned default 0 not null comment 'Store ID',
visibility smallint unsigned not null comment 'Visibility',
primary key (category_id, product_id, store_id),
constraint FK_CATALOG_CATEGORY_PRODUCT_INDEX_STORE_ID_CORE_STORE_STORE_ID
foreign key (store_id) references db_example3.core_store (store_id)
on update cascade on delete cascade,
constraint FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID
foreign key (category_id) references db_example3.catalog_category_entity (entity_id)
on update cascade on delete cascade,
constraint FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID
foreign key (product_id) references db_example3.catalog_product_entity (entity_id)
on update cascade on delete cascade
)
comment 'Catalog Category Product Index' row_format = COMPRESSED;
create index `15D3C269665C74C2219037D534F4B0DC`
on db_example3.catalog_category_product_index (store_id, category_id, visibility, is_parent, position);
create index IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY
on db_example3.catalog_category_product_index (product_id, store_id, category_id, visibility);
create table db_example3.catalog_product_index_eav
(
entity_id int unsigned not null comment 'Entity ID',
attribute_id smallint unsigned not null comment 'Attribute ID',
store_id smallint unsigned not null comment 'Store ID',
value int unsigned not null comment 'Value',
primary key (entity_id, attribute_id, store_id, value),
constraint FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID
foreign key (store_id) references db_example3.core_store (store_id)
on update cascade on delete cascade,
constraint FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID
foreign key (attribute_id) references db_example3.eav_attribute (attribute_id)
on update cascade on delete cascade,
constraint FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
on update cascade on delete cascade
)
comment 'Catalog Product EAV Index Table' row_format = COMPRESSED;
create index IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID
on db_example3.catalog_product_index_eav (attribute_id);
create index IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID
on db_example3.catalog_product_index_eav (entity_id);
create index IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID
on db_example3.catalog_product_index_eav (store_id);
create index IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE
on db_example3.catalog_product_index_eav (value);
create index idx_attribute_store_value_entity
on db_example3.catalog_product_index_eav (attribute_id, store_id, value, entity_id);
create table db_example3.catalog_product_index_price
(
entity_id int unsigned not null comment 'Entity ID',
customer_group_id smallint unsigned not null comment 'Customer Group ID',
website_id smallint unsigned not null comment 'Website ID',
tax_class_id smallint unsigned default 0 null comment 'Tax Class ID',
price decimal(12, 4) null comment 'Price',
final_price decimal(12, 4) null comment 'Final Price',
min_price decimal(12, 4) null comment 'Min Price',
max_price decimal(12, 4) null comment 'Max Price',
tier_price decimal(12, 4) null comment 'Tier Price',
group_price decimal(12, 4) null comment 'Group price',
primary key (entity_id, customer_group_id, website_id),
constraint FK_CAT_PRD_IDX_PRICE_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID
foreign key (customer_group_id) references db_example3.customer_group (customer_group_id)
on update cascade on delete cascade,
constraint FK_CAT_PRD_IDX_PRICE_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
on update cascade on delete cascade,
constraint FK_CAT_PRD_IDX_PRICE_WS_ID_CORE_WS_WS_ID
foreign key (website_id) references db_example3.core_website (website_id)
on update cascade on delete cascade
)
comment 'Catalog Product Price Index Table' row_format = COMPRESSED;
create index IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID
on db_example3.catalog_product_index_price (customer_group_id);
create index IDX_CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE
on db_example3.catalog_product_index_price (min_price);
create index IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID
on db_example3.catalog_product_index_price (website_id);
create index IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
on db_example3.catalog_product_index_price (website_id, customer_group_id, min_price);
每个表的行数
select count(*) from catalog_product_index_eav; #418
select count(*) from catalog_product_index_price; #84
select count(*) from catalog_category_product_index; # 314
该服务器配备 AMD Ryzen 9 3900 12 核处理器、128GB RAM 和 Nvme 磁盘。
任何帮助表示赞赏
我在 x86_64-pc-linux-gnu 上使用 PostgreSQL 13.3,我试图找到以下问题的根本原因:
ERROR: out of shared memory
HINT: You might need to increase max_pred_locks_per_transaction.
所以我使用 BEGIN ISOLATION LEVEL SERIALIZABLE; 进行测试 然后带条件查询,问题是即使SIReadLock的数量大于max_pred_locks_per_transaction*max_connections,我仍然可以查询,根本不存在“共享内存不足”:
smart=*# show max_pred_locks_per_transaction;
max_pred_locks_per_transaction
--------------------------------
10
(1 row)
smart=*# show max_connections;
max_connections
-----------------
3
(1 row)
smart=*# SELECT count(*) FROM pg_locks WHERE mode = 'SIReadLock';
count
-------
250
(1 row)
那么请帮我指出 SIReadLock 受哪个配置的限制?并且,哪些配置和锁与“您可能需要增加 max_pred_locks_per_transaction”相关?
谢谢。