比较来自世界各地的卖家的域名和 IT 服务价格

服务器每天都在同一时间下降。 有关联 MYSQL?

我们有一个具有多个运行应用程序的专用服务器,包括论坛 Vbulletin 和现场 Wordpress. 主要资源密集型应用 - 这是一个聊天应用程序 PHP, 这也使用 MYSQL.

似乎每天都在相同的时间,服务器关闭或挂起。 处理器负载较高,但仍在有效范围内。 我没有看到正在运行的活动。 我怀疑有些事情发生了 MYSQL, 它关闭了,因为每当我重新启动时 MYSQL, 一切似乎都被纠正,但它可能是巧合。

在安装的服务器上 ​​Cpanel. 插入 Engintron 为了 Nginx 和 MariaDB 10.1 对于数据库。 CentOS 7.

这是我的结果 MYSQLTuner:

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.33-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 2G (Tables: 310)
[--] Data in InnoDB tables: 28G (Tables: 308)
[--] Data in MEMORY tables: 16M (Tables: 3)
[!!] Total fragmented tables: 111
[!!] failed to execute: SHOW TABLE STATUS FROM \`#mysql50#.ssh\`
[!!] FAIL Execute SQL / return code: 256

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 9h 41m 49s (37M q [182.400 qps], 12M conn, TX: 116G, RX: 12G)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is disabled
[--] Physical Memory : 62.7G
[--] Max MySQL memory : 51.0G
[--] Other process memory: 2.5G
[--] Total buffers: 35.1G global + 16.3M per thread (1000 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 35.9G (57.19% of installed RAM)
[OK] Maximum possible memory usage: 51.0G (81.37% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (518/37M)
[OK] Highest usage of available connections: 4% (46/1000)
[OK] Aborted connections: 0.17% (20794/12263809)
[!!] Query cache efficiency: 10.6% (2M cached / 24M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (22 temp sorts / 6M sorts)
[!!] Joins performed without indexes: 1016
[OK] Temporary tables created on disk: 0% (5K on disk / 15M total)
[OK] Table cache hit rate: 74% (1K open / 1K opened)
[OK] Open file limit used: 0% (787/1M)
[OK] Table locks acquired immediately: 99% (52M immediate / 52M locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 12 thread(s).
[--] Using default value is good enough for your version (10.1.33-MariaDB)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 20.2% (867M used / 4B cache)
[OK] Key buffer size / total MyISAM indexes: 4.0G/617.7M
[OK] Read Key buffer hit rate: 100.0% (1B cached / 82K reads)
[!!] Write Key buffer hit rate: 42.3% (111K cached / 64K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.9% (253K cached / 5K reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 30.0G/28.9G
[!!] InnoDB buffer pool instances: 20
[!!] InnoDB Used buffer: 54.49% (1071288 used/ 1966060 total)
[OK] InnoDB Read buffer efficiency: 99.99% (13800614528 hits/ 13801405527 total)
[OK] InnoDB Write log efficiency: 95.40% (68361750 hits/ 71657060 total)
[!!] InnoDB log waits: 0.00% (39 waits / 3295310 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB 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:

Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Variables to adjust:

query_cache_limit (> 1M, or use smaller result sets)

join_buffer_size (> 4.0M, or always use indexes with joins)

innodb_buffer_pool_instances(=30)

innodb_log_buffer_size (>= 8M)

预先感谢您的任何帮助。

更新:看起来问题在服务器上有很大的负载。 我被安装了 csf, 他扫描太多了,所以我降低了它。 我也做了一些改变 my.cnf. 似乎它有助于很多。
已邀请:

裸奔

赞同来自:

以下是一些设置后的新结果: (在基本上使用某些设置后,我不再获得这些故障 csf, 要控制其扫描服务器的时间量)

一般性建议:运行 OPTIMIZE TABLE 用于碎片整理表以提高性能 MySQL, 在后者推出 24 手表 - 建议可能不准确。 配置连接请求始终使用索引。 设置变量: query_cache_size (> = 8M) join_buffer_size (> 512.0K 或者始终使用与关联的索引)

知食

赞同来自:

为您的部分提供 my.cnf-ini [mysqld}

key_buffer_size=3G  # from 4G - only 20% used
key_cache_age_threshold=64800 # from 300 second purge to reduce key_reads
key_cache_division_limit=50 # from 100 for Hot/Warm split of RAM
key_cache_block_size=16384 # from 1024 to reduce CPU usage to manage discards
query_cache_type=0 # for No QC that is only 10% efficient
query_cache_size=0 # from ? to use RAM for more useful purpose
query_cache_limit=0 # from 1M to use RAM for more useful purpose
innodb_buffer_pool_size=38G # from 30G for 60% of RAM remember CHG % setaside
innodb_buffer_pool_instances=8 # from 20 to continue to avoid mutex contention
innodb_lru_scan_depth=100 # from 1024 to save CPU every second - see refman
innodb_log_file_size=32M # from ? for about 1hr before rotation
innodb_log_buffer_size=16M # from 8M indicated by Mysqltuner
max_connections=100 # from 1000 - max_used_connections was 46 over 2 days

. , 为了使默认设置为您工作并保存RAM的数量。 输入以下S. # 和默认值有效的空间。

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size

请查看我的个人资料以获取联系信息。

我们希望不时了解您的成功。

要回复问题请先登录注册