у нас есть выделенный сервер, на котором размещен 1 сайт, на котором используется WordPress,
сайт ежедневно посещает около 40-50 тысяч посетителей, это загруженный сайт! проблема в том, что всякий раз, когда веб-сайт получает более 1000-1500 активных пользователей, ответ сайта становится все медленным и медленным, а затем сайт отключается, я связался со службой поддержки Cpanel, они проверили программное обеспечение cPanel, они сказали, что эта проблема не связана или не вызвана с помощью cPanel или базовой конфигурации программного обеспечения из комплекта cPanel. и они рекомендуют меня: «Поскольку на вашем сервере имеется большой объем свободной оперативной памяти, я бы рекомендовал выделить больше ресурсов на Apache и MySQL, поскольку я вижу, что MySQL имеет настройки по умолчанию только внутри /etc/my.cnf», поэтому я ищу лучший вариант .cnf config это моя текущая конфигурация сервера по умолчанию
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
performance-schema=0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
performance-schema=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
Я был бы очень признателен за любую помощь
и это результат mysqltuner:
[root@cpc ~]# ./mysqltuner.pl
>> MySQLTuner 1.7.14 - 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
[OK] Currently running supported MySQL version 5.7.24
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(98K)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 86 warning(s).
[!!] /var/log/mysqld.log contains 177 error(s).
[--] 7 start(s) detected in /var/log/mysqld.log
[--] 1) 2019-01-16T04:40:57.296462Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2019-01-15T23:34:47.207026Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2019-01-07T00:33:57.293154Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2019-01-06T20:10:21.026368Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2019-01-03T00:13:01.078628Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2019-01-03T00:05:49.883857Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2019-01-02T16:03:50.853606Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2019-01-16T04:38:48.819118Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2019-01-15T23:34:44.152444Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2019-01-07T00:25:29.383521Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1.1G (Tables: 86)
[--] Data in InnoDB tables: 19.8M (Tables: 8)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- 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: 20h 6m 21s (29M q [414.302 qps], 474K conn, TX: 450G, RX: 3G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 156.9G
[--] Max MySQL memory : 338.9M
[--] Other process memory: 0B
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 175.8M (0.11% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (0.21% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/29M)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Aborted connections: 0.01% (35/474804)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 27M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (5K temp sorts / 12M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 73% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (6 created / 474K connections)
[OK] Table cache hit rate: 60% (905 open / 1K opened)
[OK] Open file limit used: 7% (711/10K)
[OK] Table locks acquired immediately: 99% (31M immediate / 31M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/267.0M
[OK] Read Key buffer hit rate: 97.6% (22B cached / 543M reads)
[!!] Write Key buffer hit rate: 85.8% (36K cached / 31K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/19.8M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] 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: 100.00% (62131502 hits/ 62131969 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
performance_schema = ON enable PFS
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
[root@cpc ~]#
ОБНОВЛЕНИЕ: Я добавил следующие переменные и не уверен, повлияет ли это на улучшение производительности!
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=17M
max_heap_table_size=17M
performance_schema=ON
и вот текстовые результаты MySQL:
ПОКАЗАТЬ ПОЛНЫЙ СПИСОК ПРОЦЕССОВ; https://pastebin.com/t6cKsLJz
ПОКАЗАТЬ ПЕРЕМЕННЫЕ; https://pastebin.com/vu0KEXdd
ПОКАЗАТЬ ГЛОБАЛЬНЫЕ ПЕРЕМЕННЫЕ; https://pastebin.com/kpvVDU7D
ПОКАЗАТЬ ГЛОБАЛЬНЫЙ СТАТУС; https://pastebin.com/xtH3tD6w
и некоторая дополнительная проверка запросов, выполняющихся большую часть времени! https://pastebin.com/z7AsDjfD
Больше информации! https://pastebin.com/3ZGzXvMT
====================================
ПОКАЗАТЬ СОЗДАТЬ ТАБЛИЦУ wp_posts: -
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
`pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT '0',
`post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`),
KEY `post_name` (`post_name`(191)),
FULLTEXT KEY `wp_greet_box_post_related` (`post_title`,`post_content`)
) ENGINE=MyISAM AUTO_INCREMENT=248367 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SHOW VARIABLES; и SHOW GLOBAL STATUS; для дальнейшего анализа.Подробнее о том, как делать вышеуказанные пункты: http://mysql.rjweb.org/doc.php/mysql_analysis
postmeta индексы, как указано здесь: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmetaНекоторые изменения настроек (другие могут быть в ШОУ выше). Поскольку у вас смехотворный объем оперативной памяти, следующего будет более чем достаточно до или после перехода на InnoDB:
key_buffer_size = 2G
innodb_buffer_pool_size = 20G
Дополнительные вопросы обращайтесь на форум WordPress или на stackoverflow.com; serverfault для прочего.