Проблема с MYSQL на сервере

[Удален]
2545

На сервере раз в 5 минут (бывает и чаще) постоянно пропадает соединение с базой данных MYSQL, что делать уже не знаю. Сервер мощный (i7+32 GB DDR3). Сайты не сказать, чтобы прям очень мощные. Все сайты открываются очень быстро, ничего не тормозит.

Вот, что показывает mysqltuner:


>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.46-0+deb8u1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MyISAM tables: 4G (Tables: 83)
[--] Data in InnoDB tables: 667M (Tables: 194)
[!!] Total fragmented tables: 202

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 22s (5K q [233.727 qps], 296 conn, TX: 17M, RX: 1M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 1.3G global + 12.2M per thread (151 max threads)
[OK] Maximum possible memory usage: 3.1G (9% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 256.0M/12.7G
[OK] Key buffer hit rate: 98.7% (386K cached / 5K reads)
[OK] Query cache efficiency: 76.7% (2K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 8% (14 temp sorts / 172 sorts)
[!!] Joins performed without indexes: 9
[OK] Temporary tables created on disk: 16% (108 on disk / 646 total)
[OK] Thread cache hit rate: 98% (5 created / 296 connections)
[OK] Table cache hit rate: 64% (256 open / 395 opened)
[OK] Open file limit used: 21% (224/1K)
[OK] Table locks acquired immediately: 99% (2K immediate / 2K locks)
[OK] InnoDB buffer pool / data size: 1.0G/667.1M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 1.0M, or always use indexes with joins)

Менял значение join_buffer_size до 40 мб, все равно пишет увеличить.

А вот мой my.cnf


# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
thread_cache_size = 8
query_cache_size= 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1024M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Будьте добры, помогите советом, что делать?

Jurgen s
На сайте с 21.05.2008
Offline
153
#1

мускуль помирает каждые 5 минут или просто запросы начинают жутко долго исполняться?

скиньте логи мускуля

http://police-ua.com/ (http://police-ua.com/) Форум сотрудников МВД Украины
[Удален]
#2
Jurgen's:
мускуль помирает каждые 5 минут или просто запросы начинают жутко долго исполняться?
скиньте логи мускуля

Просто помирает очень часто.

В папке логов вообще файлы вида error.log.1.gz, но они пустые.

Включил логи, и сразу же в ошибка такое:

160110 00:34:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

160110 0:34:05 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.
160110 0:34:05 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
160110 0:34:05 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.


160110 0:37:01 [Note] Event Scheduler: Purging the queue. 0 events
160110 0:37:04 InnoDB: Starting shutdown...
160110 0:37:05 InnoDB: Shutdown completed; log sequence number 13111929619
160110 0:37:05 [Note] /usr/sbin/mysqld: Shutdown complete

160110 00:37:05 mysqld_safe mysqld from pid file /var/lib/mysql/monavista.pid ended
160110 00:37:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160110 0:37:05 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.
160110 0:37:05 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
160110 0:37:05 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
160110 0:37:05 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0+deb8u1-log) starting as process 13699 ...
160110 0:37:05 [Note] Plugin 'FEDERATED' is disabled.
160110 0:37:05 InnoDB: The InnoDB memory heap is disabled
160110 0:37:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160110 0:37:05 InnoDB: Compressed tables use zlib 1.2.8
160110 0:37:05 InnoDB: Using Linux native AIO
160110 0:37:05 InnoDB: Initializing buffer pool, size = 1.0G
160110 0:37:06 InnoDB: Completed initialization of buffer pool
160110 0:37:06 InnoDB: highest supported file format is Barracuda.
160110 0:37:06 InnoDB: Waiting for the background threads to start
160110 0:37:07 InnoDB: 5.5.46 started; log sequence number 13111929619
160110 0:37:07 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160110 0:37:07 [Note] - '0.0.0.0' resolves to '0.0.0.0';
160110 0:37:07 [Note] Server socket created on IP: '0.0.0.0'.
160110 0:37:07 [Note] Event Scheduler: Loaded 0 events
160110 0:37:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.46-0+deb8u1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)
160110 0:38:01 [Note] /usr/sbin/mysqld: Normal shutdown

160110 0:38:01 [Note] Event Scheduler: Purging the queue. 0 events
160110 0:38:01 [ERROR] /usr/sbin/mysqld: Sort aborted: Server shutdown in progress
160110 0:38:04 InnoDB: Starting shutdown...
160110 0:38:05 InnoDB: Shutdown completed; log sequence number 13111940425
160110 0:38:05 [Note] /usr/sbin/mysqld: Shutdown complete

160110 00:38:06 mysqld_safe mysqld from pid file /var/lib/mysql/monavista.pid ended
160110 00:38:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160110 0:38:07 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.
160110 0:38:07 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
160110 0:38:07 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
160110 0:38:07 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0+deb8u1-log) starting as process 14857 ...
160110 0:38:07 [Note] Plugin 'FEDERATED' is disabled.
160110 0:38:07 InnoDB: The InnoDB memory heap is disabled
160110 0:38:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160110 0:38:07 InnoDB: Compressed tables use zlib 1.2.8
160110 0:38:07 InnoDB: Using Linux native AIO
160110 0:38:07 InnoDB: Initializing buffer pool, size = 1.0G
160110 0:38:07 InnoDB: Completed initialization of buffer pool
160110 0:38:07 InnoDB: highest supported file format is Barracuda.
160110 0:38:07 InnoDB: Waiting for the background threads to start
160110 0:38:08 InnoDB: 5.5.46 started; log sequence number 13111940425
160110 0:38:08 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160110 0:38:08 [Note] - '0.0.0.0' resolves to '0.0.0.0';
160110 0:38:08 [Note] Server socket created on IP: '0.0.0.0'.
160110 0:38:08 [Note] Event Scheduler: Loaded 0 events
160110 0:38:08 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.46-0+deb8u1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)

LEOnidUKG
На сайте с 25.11.2006
Offline
1762
#3

Когда "упадёт", то посмотрите логи, а пока ничего там интересного.

✅ Мой Телеграм канал по SEO, оптимизации сайтов и серверов: https://t.me/leonidukgLIVE ✅ Качественное и рабочее размещение SEO статей СНГ и Бурж: https://getmanylinks.ru/ ✅ Настройка и оптимизация серверов https://getmanyspeed.ru/
[Удален]
#4
LEOnidUKG:
Когда "упадёт", то посмотрите логи, а пока ничего там интересного.

так я скинул логи, когда сервер и упал.

LEOnidUKG
На сайте с 25.11.2006
Offline
1762
#5
riasv:
так я скинул логи, когда сервер и упал.

Вы уверены, что он упал? Как вы определяете, что он упал?

N
На сайте с 06.05.2007
Offline
419
#6

riasv,

riasv:
так я скинул логи, когда сервер и упал.

Больше похоже на контролируемую перезагрузку и только за сегодня.

Показывайте логи за целые сутки, например.

Кнопка вызова админа ()

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий