问题[debugging](dba)

Ubuntu 18.04 PostgreSQL 10.10 Mattermost 5.15

在最重要的启动过程中永远保持

{"level":"info","ts":1570609055.2422519,"caller":"sqlstore/supplier.go:223","msg":"Pinging SQL 主数据库"}

运行此查询

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

给我

 pid  |      age       | usename |         query         | state
------+----------------+---------+-------------------------------
 8700 | 00:08:54.84669 | mmuser  | SELECT * FROM Systems | idle

从那里，我如何诊断是什么让这个查询永无止境？

我在 1gb DigitalOcean 液滴上运行 MySQL，并且通过运行的 Laravel 框架有许多计划任务。特别是一项任务（似乎）随机导致 MySQL 处于 100% CPU，我不确定从哪里开始追踪原因。

该作业下载一个包含 360 个值的 JSON 文件，并将结果存储（或更新）到数据库中。完成后，它会调用另一个函数来获取过去 4 小时（大约 240 行）的所有结果并将其转换为静态图像。这从 cron 作业每两分钟运行一次，如果作业失败，它会在放弃之前重试作业最多 10 次。

根据show processlist;，查询是：

mysql> show processlist;
+-----+-------+-----------+---------------------+---------+------+--------------+-------------------------------------------------------------+
| Id  | User  | Host      | db                  | Command | Time | State        | Info                                                        |
+-----+-------+-----------+---------------------+---------+------+--------------+-------------------------------------------------------------+
| 271 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 297 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 303 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 308 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 311 | dbusr | localhost | sample_database_com | Execute |    1 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 317 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 318 | dbusr | localhost | sample_database_com | Sleep   |    1 |              | NULL                                                        |
| 324 | dbusr | localhost | NULL                | Query   |    0 | starting     | show processlist                                            |
| 325 | dbusr | localhost | sample_database_com | Sleep   |    3 |              | NULL                                                        |
+-----+-------+-----------+---------------------+---------+------+--------------+-------------------------------------------------------------+
9 rows in set (0.00 sec)

为了解决这个问题，我必须清除 Laravel 中所有排队的作业，然后重新启动 MySQL，我最终每天都会这样做一两次。

我还有其他工作做类似的事情，但数据要多得多。例如，一项作业在数据库中插入（最多）500,000 个项目，然后将这些值拉出并制作成静态图像。这些作业运行得很好，所以我不确定为什么select240 行（看似）简单会导致问题。

我已经尝试将我的数据库繁重的工作推到列表的末尾，以防数十万行导致事情中断。我已经使用 Laravel 的lockForUpdate()函数来锁定kp_minutes行，我在存储数据和生成静态图像之间添加了延迟，我已经阅读了一堆 MySQL 问题试图找出发生了什么，但我已经一无所获。

我将如何追踪 100% CPU 的原因，我能做些什么来使此类“发送数据”进程超时？我的 MySQL 安装是 DigitalOcean LAMP 图像提供的默认设置，所以我认为我没有通过调整值搞砸任何事情。

编辑：回答几个问题：

• Laravel 应用程序与 MySQL 服务器在同一台服务器上运行。我知道我不应该这样做，但这是（希望是临时的）成本节约措施。
• Laravel 的任务调度系统使用单个 cron 作业来检查要运行的任务。然后它将这些任务发送到运行它们的队列（例如 Amazon SQS，或者在我的情况下，作为数据库中的一行）。这个“kp_minutes”任务每两分钟运行一次。
• 有问题的表包含大约 175,000 行，但我只选择了大约 240 个带有 where 子句的行
• lockForUpdate()内部使用for update：select * fromkp_minutes where (updated_at= ?) limit 1 for update
• 运行show full processlist给出的结果与我发布的相同。

我使用此代码段来检测丢失的索引：

https://stackoverflow.com/a/12818168/633961

例子：

SELECT
   relname                                               AS TableName,
   to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
   to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
   to_char(n_live_tup, '999,999,999,999')                AS TableRows,
   pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
 FROM pg_stat_all_tables
 WHERE schemaname = 'public'
       AND 50 * seq_scan > idx_scan -- more then 2%
       AND n_live_tup > 10000
       AND pg_relation_size(relname :: regclass) > 5000000
 ORDER BY relname ASC;

结果：

tablename | totalseqscan | totalindexscan | tablerows | tablesize
----------+--------------+----------------+-----------+----------
 mytable  |      112,479 |      2,978,344 | 1,293,536 |   1716 MB

我很好奇 - 我想看看哪些 SQL 语句实际上对表进行了序列扫描mytable。

有没有办法让 PostgreSQL 在对该表进行顺序扫描时发出警告？

我有 3 个模式：公共、元数据、数据。

在 PgAdmin III 或 SQL Manager for PostgreSQL 中从“数据”模式调试大型函数时，调试器看不到来自同一模式的任何表或函数。

raise notice原因函数很大并且依赖于其他函数，使用或编写模式前缀无效。

如何设置默认架构以正确调试它？我猜公共架构是默认的。

如何确定 MongoDB 文档插入验证失败的原因？我得到的只是一个 writeError，上面写着“文档验证失败”，这不是很有帮助。

（这种情况经常发生，我想了解如何正确调试这些，而不是寻求特定示例的帮助。）

我有一个应用程序，它的 HSQL 数据库对于某些用户来说变得非常大，但对其他用户则不然。当我收到一个发生这个问题的例子时，我发现数据库几乎是空的——它几乎是 16GB 的零，只有几十兆字节的真实数据。在执行CHECKPOINT DEFRAG时，它会缩小到真实的小尺寸。

不幸的是，我对这个数据库引擎不是很熟悉，而且我的在线搜索也没有产生什么有用的信息。一件有趣的事情是，有一个选项可以在达到一定大小时自动执行碎片整理，我没有使用过。但是，我希望数据库重用死行而不是保留它们，并且绝对不会将其清零，因为这肯定会对性能产生不良影响。

为什么数据库不断产生如此多的空白空间，这些空间只是被零散的数据打断？我怎样才能知道发生了什么？

我正在使用 SQL Server 2008 R2，它会根据我的查询自动锁定数据库项目。

有时这种死锁 - 具有不同查询的两个线程永远不会结束。

有什么方法可以调试 SQL Server 如何锁定和解锁资源？我试过 SQL Trace，但它没有关于资源锁的信息。