主页 / user-10371

Anentropic's questions

Martin Hope
Anentropic
Asked: 2024-04-22 21:08:03 +0800 CST
  • 5

AFAICT 当您从任何客户端向 MySQL 发送查询时，它总是阻塞并等待服务器响应成功或失败。

当执行缓慢的操作（例如在大型表上添加新索引）时，这可能是一个问题，因为它会使客户端处于空闲状态，等待很长时间才能响应。

就我而言，我特别想从 AWS Lambda 函数发送请求。

我在这里找到了一些关于如何分离客户端进程并将客户端进程置于后台的提示https://stackoverflow.com/a/41371255/202168mysql，还有一些建议，如果客户端关闭连接，MySQL服务器可能会取消请求。但启动客户端的主机mysql仍然必须保持运行。

感觉这还不是全部？

PostgreSQLCREATE INDEX CONCURRENTLY为此目的，即查询快速返回并且索引构建在服务器上继续。

我知道MySQL不支持CREATE INDEX CONCURRENTLY

但感觉很奇怪，没有非阻塞的客户端方法？（好吧......有“异步”或“非阻塞”客户端，但它们仍然必须保持相同的连接打开）

我想做的是这样的：

  • 发送ALTER TABLE ... ADD KEY查询
  • 获取查询的服务器端进程 ID
  • 注销 mysql 客户端（并结束我的 Lambda 执行）
  • 继续ADD KEY在服务器上运行
  • 从新进程中，定期轮询 DDL 查询的进程 ID，看看它是完成还是失败

有什么办法可以实现这一点吗？

相关或替代问题：如果我在发送查询后断开客户端连接，是否会ADD KEY继续运行直至完成/失败而不是被取消？

服务器是AWS RDS上的MySQL 8.0.35，InnoDB表。

mysql
Martin Hope
Anentropic
Asked: 2024-04-20 16:15:39 +0800 CST
  • 5

有问题的数据库：AWS RDS、MySQL 8、InnoDB。GP3 存储。

我正在尝试将行批量插入到数据库中的多个表中。

我省略了目标表中的所有二级索引，它只有 PK。目标表未分区。

导入的源数据（不在 MySQL 中）按日期范围分区。对于每个分区，我都有一个脚本，它选择一批数据并将其插入到 MySQL。每个分区的批处理循环脚本同时并行运行。

每个批次作为每个表的 pandas 数据帧加载，完成各种转换，然后使用 pandas to_sql“multi”插入方法将该批次的数据帧插入到 MySQL（在一个数据库事务中）。

我可以想出各种方法来改进这一点。所有 MySQL 批量插入建议中出现的两个建议是 a) 按 PK 顺序插入和 b) 使用LOAD DATA IN FILE. 我目前两者都不做。但在我彻底重写所有代码之前，我想了解运行当前代码时看到的症状：

在此输入图像描述

我们可以看到导入运行了约 1 小时。并行批处理脚本的数量始终保持一致。批量大小始终保持一致。每个批次需要约 60 秒的时间来处理和插入。在上图中，约 200 个批次（数百万行）已成功处理。但 IOPS 在 30 分钟内大致线性增加，直到达到预配置限制并受到限制。

我的问题是：如果我的插入速率恒定，为什么 IOPS 不断线性增加？

mysql
Martin Hope
Anentropic
Asked: 2024-04-20 15:48:57 +0800 CST
  • 4

我正在将约 50M 行导入 MySQL 8、InnoDB。它位于具有 GP3 存储的 AWS RDS 上。

行的唯一键是类似 uuid 的字符串。

查询时，我们永远不会关心这个唯一键，除非从主源更新插入新的/修改的行。

通常，唯一的 id 就是 PK。但我读到 MySQL 中的 PK 索引很特别，因为它旨在将相似值的数据“聚集”，以提高性能。

看起来，通过使用类似于 uuid 的字符串作为 PK，聚类不会对我们的查询有帮助。

如果我要对表进行分区，我会按日期范围进行分区。

我可以想象定义一个合成 PK 或复合 PK，它将日期字段和 uuid 结合起来以获得更有可能支持我们实际执行的查询的集群。

我的问题是：拥有一个支持典型查询（即获取的结果可能在索引中“接近”）的 PK 集群有多重要？

据推测，PK 的自动递增 id 的典型情况也会导致与典型查询关系不大的聚类（通常没有理由选择相邻的 id）。

我正在特别思考现代 SSD 存储是否使这种类型的优化变得不那么重要、过时​​......甚至更重要？

更多背景信息

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

聚集索引如何加速查询

通过聚集索引访问行的速度很快，因为索引搜索直接指向包含行数据的页面。如果表很大，则与使用与索引记录不同的页存储行数据的存储组织相比，聚集索引架构通常可以节省磁盘 I/O 操作。

看来 PK 索引的“聚集”性仅对于通过 PK 选择的查询有价值。

这是关于将行数据与索引（？）

因此，如果我关心的所有应用程序查询都使用二级索引，我想 PK 的属性是什么并不重要？例如，在 PK 中包含日期分区列不会神奇地加快使用不同索引的查询速度。

是对的吗？

mysql
Martin Hope
Anentropic
Asked: 2020-10-28 04:15:58 +0800 CST
  • 3

假设您有一个包含数千万行的大表。

您想要UPDATE large_table SET col=value WHERE col=other_value...但未col编入索引，并且EXPLAIN显示此查询将对整个表执行 seq 扫描。

这里的锁定行为是什么？根据大多数帐户，Postgres 仅锁定 UPDATE 查询的受影响行并且没有锁定升级。那么它是否首先搜索要更新的行，然后只锁定找到的行？不过，在这种情况下，似乎可能存在其他查询同时更新行的问题。它是否“在找到它们时”锁定每一行，即在通过 seq 扫描时逐步锁定行？

所以我认为这里最好的情况是它在找到行时锁定行，并且只要 UPDATE 查询需要完成，受影响的行（仅）将被锁定。

但我担心这个查询最终可能会阻止所有对表的写入，直到它完成。

我读过这个：https ://habr.com/en/company/postgrespro/blog/503008/我认为最坏的情况不会发生，但这里https://blog.heroku.com/curious-case-table -locking-update-query可能是对类似信息的不准确表示，这让我有些怀疑。

该应用程序仅使用SELECT,SELECT FOR UPDATE和UPDATE查询（即除这些之外没有其他显式锁）。该表具有其他表的外键，其他表具有该表的外键。

我们在 Postgres 11 上。

postgresql lock-escalation
Martin Hope
Anentropic
Asked: 2018-09-14 07:17:43 +0800 CST
  • 0

我正在尝试获取pg_locks与当前交易相关的列表

例如

> BEGIN;
BEGIN
> ALTER TABLE ... ;
ALTER TABLE
> select locktype,virtualxid,transactionid,mode,relation from pg_locks;
  locktype  | virtualxid | transactionid |        mode         | relation
------------+------------+---------------+---------------------+----------
 relation   | [NULL]     |        [NULL] | AccessShareLock     |    11695
 virtualxid | 2/24699    |        [NULL] | ExclusiveLock       |   [NULL]
 relation   | [NULL]     |        [NULL] | AccessExclusiveLock |   801091
(3 rows)

好吧，我认为这里的“我的”锁是最后一个，AccessExclusiveLock

但它没有transactionid或virtualxid。

如果数据库中有其他活动，我也会在此列表中看到其他锁。

如果它们的事务 ID 为空，我如何过滤掉属于我当前事务的锁？

postgresql