如果某列有索引,如果条件是,是否会加快搜索速度... WHERE column IS NULL
?实际上它是 2 列的复合索引,第二列可以为 NULL ( WHERE col1 = X AND col2 IS NULL
)。
最近,我的 mariadb 崩溃了,这引发了一场噩梦。当我恢复转储时,有时守护进程会在批量插入期间挂起。这种情况只发生在少数“大”innodb 表上(我有许多小型数据库,它们实际上不包含太多数据,而大多数其他数据库仅在少数表中包含大量数据)。数据库通常最终会恢复(有时在几个小时后),但有时会崩溃。我在几分钟之内就在另一台机器上恢复了数据库,没有出现任何问题,然后将数据目录移动到生产机器上。两者之间唯一的严重区别是成功的机器不是虚拟机和运动 SSD 存储。
从那时起,所描述的问题仍然在随机时间发生,并且总是在访问这些大表时发生。一般来说,我会冻结多个查询,但有时只有一个查询就足以触发该行为。
我发现实际上很难相信的是,iotop 报告了一个 mariadb 线程在查询挂起的整个过程中每秒稳定地写入千兆字节。这在物理上是不可能的,因为磁盘的性能甚至无法接近这些数字。我还搜索了互联网以找到可以优化设置的方法,直到几天前,这些设置似乎非常适合我的环境,我调整了一些变量,但没什么大不了的,“导入成功”机器上的默认值也没有太大不同首先。
这个问题与https://jira.mariadb.org/browse/MDEV-30884类似,但是自从我在 Gentoo 上使用 10.6.14(我也测试了 10.6.15)以来,这个问题应该已经得到解决。
我很茫然,这可能是什么原因造成的?机械硬盘存储能否以某种方式证明正在发生的事情是合理的?
我们被分配了一个新的应用程序,供应商预计数据库将增长到 100 TB 左右。他们提到 SQL Server 标准版是兼容的。
我的问题是 SQL Server 标准版是否能够充分处理这种大小的数据库?
企业版可以处理在线索引重建,但是SQL Server总体上可以处理这么大的数据库吗?
我管理的最大数据库是 3 TB,我担心 100 TB 的数据库在 SQL Server 2019 上的表现如何。
专家的任何建议将不胜感激。
我的基本问题是:什么影响了性能\d
?
我有一个单独的(小)模式,在数据库服务器上管理,否则我没有更高的权限。服务器在其公共模式中拥有一个巨大的(十亿多行)数据库。我的单独模式提供了一些辅助信息,仅用于公共模式中数据的网页呈现。
编辑:实际上有超过 55 亿行,而且这只是最大的表之一(并且只花了 1.5 小时就完成了count(*)
!)。
当我\d
在 psql 提示符下执行一个简单的操作时,它真的很慢 - 当我计时时大约需要 6.5 秒。显然,数据库/数据库服务器负载很重,但是我应该让\d
系统管理员(他们通常不想被打扰)注意这种性能吗?(这是否意味着系统中存在明显错误?)我的架构中的其他所有内容都以毫秒为单位运行,因此它不会影响我的架构本身的性能。
无论我是否引起他们的注意,什么会影响 的性能\d
?公共模式有 985 个表。即便如此,这似乎是一个相当简单的查询;我希望它只查看表名称,而不查看表内容。
我在 Windows Server 2019 标准核心上有一台 SQl server 2019,我管理两种类型的备份,一种带有维护计划,另一种带有 Veeam Backup。维护计划没问题,Veeam 的备份每天都会失败,并出现 SQL 错误,使 SQLServerWriter 处于失败状态,最后一个错误为不可重试错误。我已经在 Veeam 支持上开具了一张票,我们已经了解了变化日志,但问题与 Veeam 无关,因为即使使用 vss 命令创建备份也会出现错误。根据日志,备份作业失败并出现错误:
[11.09.2023 00:07:12] <01> Error Failed to create snapshot: Backup job failed.
[11.09.2023 00:07:12] <01> Error Cannot create a shadow copy of the volumes containing writer's data.
[11.09.2023 00:07:12] <01> Error A VSS critical writer has failed. Writer name: [SqlServerWriter].
在VSS编写器中,可以观察到错误:
Writer name: 'SqlServerWriter'
Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
Writer Instance Id: {1fb6de2a-c593-45c2-b592-e90ff6aad393}
State: [8] Failed
Last error: Non-retryable error
在 Windows 应用程序事件中可以观察到以下错误:
Log Name: Application
Source: SQLWRITER
Date: 11.09.2023 0:07:05
Event ID: 24583
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: SQL03Core
Description:
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3224
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: Cannot create worker thread.
Log Name: Application
Source: SQLWRITER
Date: 11.09.2023 0:07:05
Event ID: 24583
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: SQL03Core
Description:
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3202
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: Write on "{7F86B757-DC6E-4B76-B35D-F382797EB665}414" failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.)
服务器有 536 个数据库和 8 个核心,因此 Veeam 支持认为问题与工作线程数量不足有关,因此根据 https://learn.microsoft.com/en-us/sql/database-engine /configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-ver16,我们已将工作线程数更改为 1000,但错误继续出现,因此 Veeam 支持建议帮助根据负载优化机器和线程。
任何人都可以建议一种处理方法吗?抱歉我的英语不好,提前谢谢...斯特凡诺
在具有远程 ReportServer 数据库的 SSRS 实例上,处理在哪里完成?SSRS 实例虚拟机是否应该获得额外的 CPU 和内存,或者 ReportServer 数据库所在的 SQL 实例?报告的目标 SQL 实例在 CPU 和内存方面表现良好。谢谢!
我在 MySQL 文档中读到,可以启用不可见主键的生成(https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html)。
我是数据库管理方面的新手,因此我想了解如果生成的不可见列未在此类查询的 WHERE 子句中使用,添加不可见主键如何可以提高 SELECT 查询的性能。如果它不能提高性能,那么启用此功能有什么好处呢?
我有一个 MariaDB 10.5.10 数据库,其中有一个由 150,000 行组成的表activity
。它拥有:
- 字段
id
(整数、主键) - 一个
tenant
。大约有 85,000 行,租户 = 12 - an
activity_type
(整数,充当另一个表的键activity_type
)
另一个表 ,activity_type
有 6 行,带有id
(主键,整数)
这个查询是从 ORB 生成的(我几乎无法修改),需要大约 4-5 秒来执行,我想让它运行得更快:
select count(activity.id) from activity
left join activity_type on activity.type = activity_type.id
where tenant = 12 ;
当我删除该left join
行时,它运行得更快,大约 0.7 秒。
使用explain
,我可以看到没有left join
,该extra
列提到using index
(实际上是该列上的索引tenant
):
explain select SQL_NO_CACHE count(activity.id) from activity
where tenant = 12;
但left join
该extra
列为空:
explain select SQL_NO_CACHE count(activity.id) from activity
left join activity_type on activity.type = activity_type.id
where tenant = 12;
并且使用left join
but NOwhere
子句,它还显示Using index
:
explain select SQL_NO_CACHE count(activity.id) from activity
left join activity_type on activity.type = activity_type.id;
LEFT JOIN
当和WHERE
语句都存在时,您能帮助我提高性能吗?
请注意:
- 不会
LEFT JOIN
向结果添加任何行,因为每个活动只有一行activity_type
(我也尝试UNIQUE
在 上添加索引activity_type.id
,但它不会改变任何性能) - 但是我需要该
LEFT JOIN
语句,因为在某些情况下,用户可能希望添加where
与表中字段相关的更多条件(在子句中)activity_type
。
非常感谢!
我目前正在致力于优化 MariaDB 数据库的性能,并且遇到了一个名为 mysqltuner 的工具。我已经在我的服务器上安装并运行了 mysqltuner,它为我提供了一些建议和有关 MariaDB 配置当前状态的见解。
但是,我正在寻求有关如何有效解释和实施 mysqltuner 提供的建议的建议和额外指导。
我仅将服务器用于此数据库,但用户告诉我查询速度较慢,因为服务器未正确调整 MariaDB 10.6、Apache 2.4.52。
如何准确解读mysqltuner给出的建议?各种指标和值的含义是什么?它们与我的 MariaDB 数据库的性能有何关系?
服务器要求
CPU(s) 32 x Intel(R) Xeon(R) Silver 4314 CPU @ 2.40GHz
RAM 32 GB
MYSQL 调优器输出
>> MySQLTuner 2.2.6
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics --------------------------------------------- --------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in Aria tables: 32.0K (Tables: 1)
[--] Data in MyISAM tables: 13.3M (Tables: 165)
[--] Data in InnoDB tables: 33.4G (Tables: 2578)
[--] Data in MEMORY tables: 0B (Tables: 6)
[OK] Total fragmented tables: 0
[OK] Currently running supported MySQL version 10.6.14-MariaDB-1:10.6.14+maria~u bu2204
-------- Log file Recommendations ---------------------------------------------- --------------------
[!!] Log file doesn't exist
-------- Analysis Performance Metrics ------------------------------------------ --------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
Use of uninitialized value $opt{"structstat"} in numeric eq (==) at
./mysqltuner.pl line 5810 (#1)
(W uninitialized) An undefined value was used as if it were already
defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.
To help you figure out what was undefined, perl will try to tell you
the name of the variable (if any) that was undefined. In some cases
it cannot do this, so it also tells you what operation you used the
undefined value in. Note, however, that perl optimizes your program
and the operation displayed in the warning may not necessarily appear
literally in your program. For example, "that $foo" is usually
optimized into "that " . $foo, and the warning will refer to the
concatenation (.) operator, even though there is no . in
your program.
-------- CVE Security Recommendations ------------------------------------------ --------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics --------------------------------------------------- --------------------
[--] Up for: 3d 0h 50m 58s (27M q [106.285 qps], 5M conn, TX: 29G, RX: 3G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory : 31.3G
[--] Max MySQL memory : 86.1G
[--] Other process memory: 0B
[--] Total buffers: 15.3G global + 480.6M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 86.6G (276.42% of installed RAM)
[!!] Maximum possible memory usage: 86.1G (274.92% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (27/27M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 0.21% (12101/5634183)
[!!] Name resolution is active: a reverse name resolution is made for each new c onnection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocesso r machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 30037
[OK] Temporary tables created on disk: 2% (28K on disk / 997K total)
[OK] Thread cache hit rate: 99% (253 created / 5M connections)
[OK] Table cache hit rate: 99% (26M hits / 27M requests)
[!!] table_definition_cache (400) is less than number of tables (3041)
[OK] Open file limit used: 0% (121/16K)
[OK] Table locks acquired immediately: 100% (189K immediate / 189K locks)
-------- Performance schema ---------------------------------------------------- --------------------
[!!] Performance_schema should be activated.
[--] Sys schema is installed.
-------- ThreadPool Metrics ---------------------------------------------------- --------------------
[--] ThreadPool stat is disabled.
Use of uninitialized value $opt{"myisamstat"} in numeric eq (==) at
./mysqltuner.pl line 3860 (#1)
-------- InnoDB Metrics -------------------------------------------------------- --------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDb Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (1 7179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 33.4G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128 .0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb _buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.87% (157285991602 hits / 157486582048 tot al)
[!!] InnoDB Write Log efficiency: 49.38% (563905 hits / 1141959 total)
[OK] InnoDB log waits: 0.00% (0 waits / 578054 writes)
-------- Aria Metrics ---------------------------------------------------------- --------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[OK] Aria pagecache hit rate: 99.9% (27M cached / 27K reads)
-------- TokuDB Metrics -------------------------------------------------------- --------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics -------------------------------------------------------- --------------------
[--] XtraDB is disabled.
-------- Galera Metrics -------------------------------------------------------- --------------------
[--] Galera is disabled.
-------- Replication Metrics --------------------------------------------------- --------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
mysqltuner推荐
-------- Recommendations ------------------------------------------------------- --------------------
General recommendations:
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Performance schema should be activated for better diagnostics
Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
skip-name-resolve=ON
join_buffer_size (> 256.0K, or always use indexes with JOINs)
table_definition_cache (400) > 3041 or -1 (autosizing if supported)
performance_schema=ON
innodb_buffer_pool_size (>= 33.4G) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
我的cnf请给建议请需要添加什么
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
我非常感谢任何与使用 mysqltuner 进行 MariaDB 性能调整相关的见解、解释或资源。预先感谢您的宝贵帮助!