Я много читал об этом и не нашел решения, поэтому создаю эту тему.
Сначала я дам вам спецификации своего выделенного сервера
root@h2k.how2kill.pl
OS: CloudLinux 7.6 How2Kill.pl
##### Uptime: 3d 17h 0m
####### Packages: Unknown
##O#O## Shell: bash 4.2.46
####### CPU: Intel Core i7-4790K @ 8x 4.4GHz [43.0°C]
########### RAM: 6994MiB / 15846MiB
#############
###############
################
#################
#####################
#####################
#################
Я хочу попросить кого-то, кто является экспертом по mysql, помочь мне с этой проблемой
У меня есть система Centos 7 с cPanel и cloudlinux. Я использую базы данных для программного обеспечения сообщества invision, и оно отлично работает
Я также использую свои базы данных для SourceBans ++. Это приложение для расширенной системы банов для серверов CSGO.
Итак, сервер csgo подключается к mysql, и если кто-то забанит кого-то на сервере, он просто перейдет на mysql, а затем отобразится на сайте.
Так что просто удаленные серверы, которые подключаются к моему выделенному серверу mysql, иногда теряют связь, я понятия не имею, почему, поэтому я прошу помощи
L 08/01/2019 - 00:49:37: [sbpp_main.smx] Verify Ban Query Failed: Lost connection to MySQL server during query
L 08/03/2019 - 00:57:02: [sbpp_main.smx] Verify Ban Query Failed: Can't connect to MySQL server on 'how2kill.pl' (4)
L 08/03/2019 - 00:58:02: [sbpp_main.smx] Verify Ban Query Failed: Can't connect to MySQL server on 'how2kill.pl' (4)
L 08/03/2019 - 01:18:14: [sbpp_main.smx] Verify Ban Query Failed: Can't connect to MySQL server on 'how2kill.pl' (4)
L 08/03/2019 - 01:45:36: [sbpp_main.smx] Query Failed: Lost connection to MySQL server during query
L 08/10/2019 - 06:53:13: [sbpp_main.smx] Query Failed: Lost connection to MySQL server during query
L 08/10/2019 - 08:09:33: [sbpp_main.smx] Query Failed: Lost connection to MySQL server during query
Ну, я попытался даже изменить хост на IP-адрес выделенного сервера, но все еще не решил проблему, через некоторое время он просто потерял связь.
Я ХОЧУ УМЕНИТЬ, ЧТО ВСЕ НА ЛОКАЛЬНОМ ХОЗЯЙСТВЕ СОВЕРШЕННО ТОЛЬКО С ДРУГИМ СЕРВЕРОМ, УДАЛЕННЫМ ОБМЕНАМИ К БАЗЕ ДАННЫХ.
Но если я использую другую базу данных на другом хостинге, например blazingfast.io или hekko.pl, их база данных работает нормально, без проблем, без потери соединений.
И вот какие журналы я получаю из файла ошибок mysql
2019-08-27 0:34:19 470238 [Warning] Aborted connection 470238 to db: 'csowicze_sourcebans' user: 'csowicze_sourcebans' host: 'node52.pukawka.pl' (Got timeout reading communication packets)
2019-08-27 0:34:19 470241 [Warning] Aborted connection 470241 to db: 'csowicze_sourcebans' user: 'csowicze_sourcebans' host: 'node52.pukawka.pl' (Got timeout reading communication packets)
2019-08-27 0:34:19 470239 [Warning] Aborted connection 470239 to db: 'csowicze_sourcebans' user: 'csowicze_sourcebans' host: 'node52.pukawka.pl' (Got timeout reading communication packets)
2019-08-27 0:34:20 470242 [Warning] Aborted connection 470242 to db: 'csowicze_sourcebans' user: 'csowicze_sourcebans' host: 'node52.pukawka.pl' (Got timeout reading communication packets)
Тем не мение
Это файл my.cnf
[mysqld]
long_query_time = 1
performance_schema = on
log_output = FILE
slow_query_log = ON
log_slow_admin_statements = ON
log_queries_not_using_indexes = OFF
log-error = /var/lib/mysql/h2k.how2kill.pl.err
wait_timeout = 1000
max_connections = 500
max_allowed_packet=256M
interactive_timeout = 30
tmp_disk_table_size = 1G
max_heap_table_size = 128M
tmp_table_size = 128M
join_buffer_size = 128M
innodb_buffer_pool_size = 3GB
innodb_log_file_size = 4G
innodb_buffer_pool_instances = 3
innodb_lru_scan_depth = 100
innodb_log_buffer_size = 1G
innodb_io_capacity = 1000
innodb_change_buffer_max_size = 10
innodb_file_per_table = 1
table_open_cache = 128
query_cache_type = 0
query_cache_limit = 128M
query_cache_min_res_unit = 2K
query_cache_size = 0
thread_cache_size = 100
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 1M
default-storage-engine = MyISAM
local-infile = 0
log_warnings = 2
max_write_lock_count = 16
А это журнал mysqltuner
[root@h2k ~]# ./mysqltuner.pl
>> MySQLTuner 1.7.15 - Major Hayden
>> Bug reports, feature requests, and downloads at
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.3.17-MariaDB-log-cll-lve
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/h2k.how2kill.pl.err(7M)
[OK] Log file /var/lib/mysql/h2k.how2kill.pl.err exists
[OK] Log file /var/lib/mysql/h2k.how2kill.pl.err is readable.
[OK] Log file /var/lib/mysql/h2k.how2kill.pl.err is not empty
[OK] Log file /var/lib/mysql/h2k.how2kill.pl.err is smaller than 32 Mb
[!!] /var/lib/mysql/h2k.how2kill.pl.err contains 32113 warning(s).
[!!] /var/lib/mysql/h2k.how2kill.pl.err contains 26397 error(s).
[--] 17 start(s) detected in /var/lib/mysql/h2k.how2kill.pl.err
[--] 1) 2019-08-25 2:28:38 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2019-08-25 1:57:28 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2019-08-25 1:40:27 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2019-08-25 1:37:24 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2019-08-25 1:30:14 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2019-08-25 1:28:23 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2019-08-25 1:26:14 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2019-08-25 1:24:23 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2019-08-25 1:22:23 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2019-08-25 1:20:22 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7 shutdown(s) detected in /var/lib/mysql/h2k.how2kill.pl.err
[--] 1) 2019-08-25 1:34:22 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2019-08-25 1:24:46 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2019-08-25 1:12:40 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2019-08-20 22:11:17 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2019-08-13 4:36:42 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2019-08-12 0:23:38 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2019-08-12 0:23:36 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 2.3G (Tables: 610)
[--] Data in InnoDB tables: 1.4G (Tables: 586)
[!!] Total fragmented tables: 1
-------- 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
[!!] User 'csowicze@%' does not specify hostname restrictions.
[!!] User 'csowicze_amxbans@%' does not specify hostname restrictions.
[!!] User 'csowicze_forum@%' does not specify hostname restrictions.
[!!] User 'csowicze_gagdd22@%' does not specify hostname restrictions.
[!!] User 'csowicze_gomodklany@%' does not specify hostname restrictions.
[!!] User 'csowicze_sourcebans@%' does not specify hostname restrictions.
[!!] User 'csowicze_usersmsgo@%' does not specify hostname restrictions.
[!!] User 'gocode@%' does not specify hostname restrictions.
[!!] User 'gocode_dev@%' does not specify hostname restrictions.
[!!] User 'gocode_doc@%' does not specify hostname restrictions.
[!!] User 'gocode_forum@%' does not specify hostname restrictions.
[!!] User 'how2kill@%' does not specify hostname restrictions.
[!!] User 'how2kill_amxx@%' does not specify hostname restrictions.
[!!] User 'how2kill_amxxbans@%' does not specify hostname restrictions.
[!!] User 'how2kill_arena@%' does not specify hostname restrictions.
[!!] User 'how2kill_csgoshop@%' does not specify hostname restrictions.
[!!] User 'how2kill_csowiczesb@%' does not specify hostname restrictions.
[!!] User 'how2kill_h2k@%' does not specify hostname restrictions.
[!!] User 'how2kill_ignuser@%' does not specify hostname restrictions.
[!!] User 'how2kill_ignwebuser@%' does not specify hostname restrictions.
[!!] User 'how2kill_sklepsms@%' does not specify hostname restrictions.
[!!] User 'how2kill_sourcebans@%' does not specify hostname restrictions.
[!!] User 'how2kill_testips@%' does not specify hostname restrictions.
[!!] User 'naxe@%' does not specify hostname restrictions.
[!!] User 'naxe_ips@%' does not specify hostname restrictions.
[!!] User 'shooted@%' does not specify hostname restrictions.
[--] There are 618 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 16h 34m 9s (66M q [209.267 qps], 868K conn, TX: 485G, RX: 33G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 69.1G
[--] Other process memory: 0B
[--] Total buffers: 4.4G global + 131.5M per thread (500 max threads)
[--] P_S Max memory usage: 501M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 35.1G (226.70% of installed RAM)
[!!] Maximum possible memory usage: 69.1G (446.66% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (48/66M)
[OK] Highest usage of available connections: 47% (235/500)
[OK] Aborted connections: 0.73% (6380/868903)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 23335
[!!] Temporary tables created on disk: 67% (735K on disk / 1M total)
[OK] Thread cache hit rate: 99% (772 created / 868K connections)
[!!] Table cache hit rate: 0% (128 open / 435K opened)
[OK] Open file limit used: 5% (147/2K)
[OK] Table locks acquired immediately: 99% (53M immediate / 53M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 501.7M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.3.17-MariaDB-log-cll-lve)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 25.0% (67M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/606.1M
[OK] Read Key buffer hit rate: 99.8% (3B cached / 5M reads)
[!!] Write Key buffer hit rate: 55.2% (3M cached / 2M writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 3.0G/1.4G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (266.666666666667 %): 4.0G * 2/3.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 3
[--] Number of InnoDB Buffer Pool Chunk : 24 for 3 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% (1393348378 hits/ 1393405704 total)
[!!] InnoDB Write Log efficiency: 51.46% (1870504 hits/ 3634621 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1764117 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (396M cached / 733K 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
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/h2k.how2kill.pl.err file
Control error line(s) into /var/lib/mysql/h2k.how2kill.pl.err file
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `csowicze_forum`.`core_output_cache`; -- can free 192.630504608154 MB
Total freed space after theses OPTIMIZE TABLE : 192.630504608154 Mb
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Read this before increasing for MariaDB
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here:
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (2505) variable
should be greater than table_open_cache (128)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 128.0M, or always use indexes with JOINs)
tmp_table_size (> 200M)
max_heap_table_size (> 32M)
table_open_cache (> 128)
innodb_log_file_size should be (=384M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
Возможно, проблема в конфигурации, я не являюсь экспертом по MySQL, поэтому не знаю, что должно быть лучшим и наиболее стабильным для моего сервера.
Спасибо за любые советы и помощь.