Mariadb MySQL调谐器报告混乱 -- mysql 领域 和 innodb 领域 和 optimization 领域 和 mariadb 领域 和 mysqltuner 领域 dba 相关 的问题

Mariadb MySQL Tuner report confusing


7
vote

问题

中文

我想请您澄清关于Mariadb数据库的MySQLTuner的报告。 mysqltuner被调用--nogood flag!

   >>  MySQLTuner 1.7.1 - Major Hayden <major@mhtx.net>  >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/  >>  Run with '--help' for additional options and output filtering  [--] Skipped version check for MySQLTuner script [!!] Currently running unsupported MySQL version 10.0.29-MariaDB-0ubuntu0.16.04.1  -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: (0B) [!!] Log file  doesn't exist [!!] Log file  isn't readable.  -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA  [--] Data in InnoDB tables: 380M (Tables: 417)  -------- Security Recommendations ------------------------------------------------------------------ [--] There are 605 basic passwords in the list.  -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined  -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 15s (812 q [54.133 qps], 275 conn, TX: 258K, RX: 108K) [--] Reads / Writes: 100% / 0% [--] Binary logging is disabled [--] Physical Memory     : 31.3G [--] Max MySQL memory    : 10.0G [--] Other process memory: 1.2G [--] Total buffers: 8.4G global + 10.7M per thread (150 max threads) [--] P_S Max memory usage: 34M [--] Galera GCache Max memory usage: 0B [!!] Slow queries: 27% (221/812) [!!] Query cache may be disabled by default due to mutex contention. [!!] Query cache efficiency: 0.0% (0 cached / 521 selects)  -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 35.0M [--] Sys schema isn't installed.  -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 8 thread(s). [--] Using default value is good enough for your version (10.0.29-MariaDB-0ubuntu0.16.04.1)  -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (24M used / 134M cache) [!!] Read Key buffer hit rate: 80.0% (10 cached / 2 reads)  -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (12.5 %): 512.0M * 2/8.0G should be equal 25% [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)  -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled.  -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled.  -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled.  -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled.  -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled.  -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect is disabled.  -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled.  -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server.  -------- Recommendations --------------------------------------------------------------------------- General recommendations:     MySQL started within last 24 hours - recommendations may be inaccurate     Consider installing Sys schema from https://github.com/mysql/mysql-sys Variables to adjust:     query_cache_type (=0)     query_cache_limit (> 256K, or use smaller result sets)     innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=4G) if possible.   

让我混淆是"日志文件建议" 部分。我真的不知道该怎么办。然后也这行:

  [!!] Query cache may be disabled by default due to mutex contention.   

我也非常好奇为什么它会推荐我将query_cache_type更改为0并增加query_cache_limit?

我知道它尚未运行至少24小时,这是因为我已经调整了配置并重新启动我的数据库。我根据我对MariaDB的了解进行了调整,但是我觉得很少有人困惑。

英文原文

I'd like to ask you to clarify the report from mysqltuner regarding MariaDB database. The mysqltuner was called with --nogood flag!

 >>  MySQLTuner 1.7.1 - Major Hayden <major@mhtx.net>  >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/  >>  Run with '--help' for additional options and output filtering  [--] Skipped version check for MySQLTuner script [!!] Currently running unsupported MySQL version 10.0.29-MariaDB-0ubuntu0.16.04.1  -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: (0B) [!!] Log file  doesn't exist [!!] Log file  isn't readable.  -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA  [--] Data in InnoDB tables: 380M (Tables: 417)  -------- Security Recommendations ------------------------------------------------------------------ [--] There are 605 basic passwords in the list.  -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined  -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 15s (812 q [54.133 qps], 275 conn, TX: 258K, RX: 108K) [--] Reads / Writes: 100% / 0% [--] Binary logging is disabled [--] Physical Memory     : 31.3G [--] Max MySQL memory    : 10.0G [--] Other process memory: 1.2G [--] Total buffers: 8.4G global + 10.7M per thread (150 max threads) [--] P_S Max memory usage: 34M [--] Galera GCache Max memory usage: 0B [!!] Slow queries: 27% (221/812) [!!] Query cache may be disabled by default due to mutex contention. [!!] Query cache efficiency: 0.0% (0 cached / 521 selects)  -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 35.0M [--] Sys schema isn't installed.  -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 8 thread(s). [--] Using default value is good enough for your version (10.0.29-MariaDB-0ubuntu0.16.04.1)  -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (24M used / 134M cache) [!!] Read Key buffer hit rate: 80.0% (10 cached / 2 reads)  -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (12.5 %): 512.0M * 2/8.0G should be equal 25% [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)  -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled.  -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled.  -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled.  -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled.  -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled.  -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect is disabled.  -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled.  -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server.  -------- Recommendations --------------------------------------------------------------------------- General recommendations:     MySQL started within last 24 hours - recommendations may be inaccurate     Consider installing Sys schema from https://github.com/mysql/mysql-sys Variables to adjust:     query_cache_type (=0)     query_cache_limit (> 256K, or use smaller result sets)     innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=4G) if possible. 

What makes me confused is the "Log file Recommendations" section. I don't really know what to do with it. Then also this line:

[!!] Query cache may be disabled by default due to mutex contention. 

I am also very curious why it would recommend me to change query_cache_type to 0 and increase query_cache_limit?

I'm aware that it has not been running for at least 24 hours, that's because I already adjusted the config and restarted my database. I did adjustements according to my knowledge about MariaDB, but with these few things I feel confused.

              
 
 

回答列表

7
 
vote

我可以解释这个行

  [!!] Query cache may be disabled by default due to mutex contention.   

innodb存储引擎和查询缓存处于恒定的战争状态 (请参阅我的1.5岁的帖子)

mysqltuner推荐设置 query_cache_type 到0所以你显式禁用查询缓存。请不要忘记设置 query_cache_size 到0也是。否则,无论如何,扰乱行为将被动。

我在我的旧帖子,如果您合理地知道最常见的结果集的大小,则不一定必须禁用查询缓存。如果您可以弄清楚那个大小,那么您可以 query_cache_limit 和 query_cache_min_res_unit 作为结果集尺寸的上限和下限。只有那么你可以设置 query_cache_type 1.

为您的日志文件建议

  [--] Log file: (0B) [!!] Log file  doesn't exist [!!] Log file  isn't readable.   

也许这可能会解释它

  [!!] Currently running unsupported MySQL version 10.0.29-MariaDB-0ubuntu0.16.04.1   

它是可能的,MySQLTuner无法与此版本的MariaDB中的日志文件相交,如它的支持版本。

 

I can explain this line

[!!] Query cache may be disabled by default due to mutex contention. 

The InnoDB storage engine and the Query Cache are in a constant state of war (See my 1.5-year-old post Why query_cache_type is disabled by default start from MySQL 5.6?)

mysqltuner is recommending setting query_cache_type to 0 so you explicitly disable the query cache. Please don't forget to set query_cache_size to 0 as well. Otherwise, the mutexing behavior will passive occur anyway.

As I said in my old post, you don't necessarily have to disable the query cache if your reasonably know the size of your most common result sets. If you can figure out that size, then you can query_cache_limit and query_cache_min_res_unit as the upper and lower bound of result set sizes. Only then can you set query_cache_type to 1.

As for your Log File Recommendations

[--] Log file: (0B) [!!] Log file  doesn't exist [!!] Log file  isn't readable. 

Perhaps this might explain it

[!!] Currently running unsupported MySQL version 10.0.29-MariaDB-0ubuntu0.16.04.1 

It is possible mysqltuner cannot relate to log files in this version of MariaDB like it does with supported versions.

 
 
   
   
3
 
vote

不要信任(依靠它) - 对于MySQLTunner,必须根据实际监控和加载来调整所有设置。

日志文件大小 从一侧 - 它推荐在此期间的所有交易0.5-1小时

但是从其他方面 - 如果它超过1-2GB,则在崩溃后才能需要年龄。正如更大的日志 - 更长的开始。

所以它总是平衡。

从每份文件512米开始(总量1g),如果加载高 - 增加高达1024gb

更好地检查内部:

  Slow queries: 27% (221/812)   
  • 哪个查询?
  • 为什么慢?
  • 是数据大小?或错误的索引?

这可以给出更好的性能

 

Do not trust (rely only on it) - for mysqltunner, all settings must be adjusted based on real monitoring and loading.

Log File size from one side - it recommended size 0.5-1Hr of all transactions during this period

but from other side - if it more than 1-2Gb restart after crash could take ages. As bigger logs - as longer start.

So it always balance between.

Start from 512M per file (Total 1G), then if loading high - increase up to 1024Gb

better to check what inside:

Slow queries: 27% (221/812) 
  • which queries?
  • why slow?
  • is it by data size? or by wrong indexes?

this is could give more for performance

 
 
 
 
2
 
vote

输出几乎没用,因为系统已运行15秒。等待至少一天。

然而,"慢" 的查询百分比是可怕的。打开慢朗,等待一天,使用PT-Query-Digest找到最糟糕的查询。然后让我们讨论它们。

我没有认为Innodb可以在没有日志文件的情况下运行! (或者也许它指的是不同的日志文件?)稍后它表示 innodb_log_file_size 是512米。这应该是现在的体面。 (您现在有其他问题要关注。)

为具有大量写入的生产系统,这是最好的QC设置:

  query_cache_type = OFF query_cache_size = 0   

如果您想要一个不同的审核,请提供 SHOW VARIABLES; SHOW GLOBAL STATUS; (在至少有一天之后)。

 

The output is virtually useless because the system has been running only 15 seconds. Wait at least a day.

Nevertheless, the percent of queries that are "slow" is terrible. Turn on the slowlog, wait a day, use pt-query-digest to find the worst couple of queries. Then let's discuss them.

I did not think that InnoDB could run without a log file! (Or maybe it is referring to a different log file??) Yet later it says innodb_log_file_size is 512M. That should be a decent size for now. (You have other issues to focus on for now.)

For production systems with lots of writes, this is the best QC setting:

query_cache_type = OFF query_cache_size = 0 

If you would like a different review, please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; (after being up at least a day).

 
 
 
 

相关问题

0  需要使用mysql [复制]同步Oracle DB表数据  ( Need to sync oracle db table data with mysql ) 
这个问题已经存在: 如何用mysql db表复制Oracle DB表[关闭] 关闭 5年前。 我有一个表格中的oracle中的一些数据,我想在一天内与mysql表同步,如cron作业。 有没有任...

0  使用 - 可以是MySQL用户自己的表吗? [关闭]  ( Using can a mysql user own tables ) 
关闭。这个问题需要详细信息或清晰度。它目前不接受答案。 想要改进这个问题?添加详细信息并阐明编辑此帖的问题。 closed 4年前。 ...

0  将项目分配给容器  ( Assigning items to containers ) 
我有两个表格分开的东西: containers ,一个表 items 。他们都有描述描述,标题和当然ID字段: container_ID 和 item_ID 。 我的容器需要有分配给它们的物品。说每个容器都将持5 - 25件物品。然而,一些物品可能是多个容器。 我正在思考最简单的是制作一个表,称它为例子,它具有自...

3  Information_Schema在数据库导入后充满了空  ( Information schema is full of nulls after database import ) 
我正在使用mysql workbench转储4个数据库,其中表格相互引用。作为一个备注,所有数据库都没有目前的问题 - 一切都按预期工作。当我尝试从MySQL Workbench尝试各种数据导出时,然后使用MySQL工作台将其导入另一台PC时,除了<代码> ABCDefghijklmn0 查询时,所有似乎都似乎很精细...

3  插入如果不存在不起作用  ( Insert if not exists not working ) 
我试图将数据从表复制到两个不同的数据库中的另一个mysql表 首先,我从第一表获取所有数据并将其存储到PHP变量中 然后我循环var以将数据插入另一个表格 使用此查询 INSERT INTO `users` (`nUserId`, `vLoginName`, `vFirstName`, `vLastName`, ...

0  Amazon RDS CPU使用率在显示流行列表中没有任何长时间运行的流程  ( Amazon rds cpu usage keep increasing without any long running process in show pr ) 
我们正在使用亚马逊RDS(db.m2.2xlarge),即使在重新启动DB服务器后,CPU使用率也逐渐增加。这只是过去2天发生,我们最近没有给出任何新的版本。 我试图使用"show processlist" 进行调试并识别长期运行过程。我杀了它,但仍然没有减少CPU%。实际上我们一次只有10个进程 ...

4  Oracle对MySQL的影响  ( Effect of oracle on mysql ) 
虽然MySQL的未来是未知的,但我没有时间跟上甲骨文在MySQL上的已知效果,因为它是从太阳购买的。 自交易成为公众以来的概述(或MYSQL社区)有没有变化? ...

1  财务内部 - 模型角色+关系B / W人和帐户  ( Financial inst model rolerelationships b w person and account ) 
a Person 可以在一个或多个 Accounts 上。对于每个 abcdefghijklmncdefghijklmn2 a Person 是开启的, Person 在该帐户上有一个角色。 Person 1 是 Account 1 上的 primary Person 1 是 Account 2 ...

1  我需要为休息提供加密,以获得在CentOS 6.5上运行的50GB MySQL实例  ( I need to provide encryption at rest for a 50gb mysql instance running on centos ) 
我不能使用gusterfs,因为他们不推荐它和ceph,因为它很清楚他们无法保证性能。 我不能只是加密某些数据库对象,因为它会花太多的工程 我有很多磁盘。我需要一种有效的方法,可以确保没有人可以访问数据,如果他们无法访问使用它的平台。 我打开硬件设备和软件解决方案。任何想法? ...

1  无法在mysql innodb群集上添加新实例  ( Unable to add new instance on mysql innodb cluster ) 
我正在设置新的mysql innodb群体8.0.16由三个实例组成: srv-mysql-01 srv-mysql-02 srv-mysql-03 我在mysql shell中运行js-script for build cluster: mysqlsh --file=/tmp/MakeClust...

0  如何从转储文件加快MySQLL加载?  ( How to speed up mysqll loading from dump file ) 
我在Ubuntu 16.04中使用MySQLD Ver 5.7.25-0。我从AWS关系数据库服务(RDS)倾倒了 mysqldump --single-transaction --quick --max_allowed_packet 1G ......现在我试图用命令加载转储: mysql -u <mu_user...

2  从多值列中提取两个特定值  ( Extracting two particular values from a multi valued column ) 
我有一个表 SUBSCRIBER ,并且在它中,数据存储在某种程度上,即 bars 是一个多值列。使用 ID 如下: CREATE DATABASE mirror GO ALTER DATABASE mirror SET RECOVERY FULL GO USE mirror GO CREATE TABLE...

0  MySQL使用3个表和案例陈述来获得结果  ( Mysql getting result using 3 tables and case statements ) 
我有3个名为person,person1,person2的表。所有3表都有2列名称和PHNO。现在我的要求是,如果我给出任何特定的phno,它必须显示是或否 对于相应的表。 我的样本表是: Person Table: Name phno abc 1234 def 2345 ghi...

1  更新FTS索引列返回“无效InnoDB FTS Doc ID”  ( Updating an fts indexed column returns invalid innodb fts doc id ) 
环境: ubuntu 12.04(和13.04) mysql 5.6.11 我有一个表上有一个完整的文本索引(真实表有更多的列和行): DROP TABLE IF EXISTS articles; CREATE TABLE articles ( FTS_DOC_ID BIGINT UNSIGNED ...

15  一个大数据库与几个较小的数据库  ( One big database vs several smaller ones ) 
我们有一个情况我们可以(a)使用表前缀或(b)在一个mysql数据库中部署一个MySQL数据库中的应用程序,为e,例如应用程序的每个实例使用不同的mysql数据库。 设置"a" : central_database app1_table1 app1_table2 app1_tablen ... a...




© 2021 it.wenda123.org All Rights Reserved. 问答之家 版权所有


Licensed under cc by-sa 3.0 with attribution required.