Ошибка подключения к MySQL. Как исправить (2)?

Question

[Вячеслав Грачунов]

В общем, в продолжение этого вопроса Ошибка подключения к MySQL. Как исправить?
Одна ошибка ушла, другая появилась - (2006, 'MySQL server has gone away')
Как от нее избавиться? Может в my.cnf надо что-то поменять?
Подскажите, пожалуйста.

ОС: Ubuntu 18.04
База: MariaDB 10.4.7

Python 3.6.8

К Django это уже не имеет отношения, т.к. проявляется и в скриптах без него.
Запросов к базе идет много и постоянно.
Что можно с этим сделать?

Answer 1

[Vitaly Karasik]

другая появилась - (2006, 'MySQL server has gone away')

Время от времени или совсем не работает?
Если первое то надо смотреть на сервер и конфигурацию MySQL.

Comments

[Вячеслав Грачунов]

Периодически
Сервер - впс на хецнере, 2 ядра, 8 ГБ RAM
Конфиг:
my.cnf

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc_messages_dir	= /usr/share/mysql
lc_messages	= en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
max_connections		= 500
connect_timeout		= 15
wait_timeout		= 1200
max_allowed_packet	= 64M
thread_cache_size       = 128
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 32M
max_heap_table_size	= 32M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size		= 128M
#open-files-limit	= 2000
table_open_cache	= 400
myisam_sort_buffer_size	= 512M
concurrent_insert	= 2
read_buffer_size	= 2M
read_rnd_buffer_size	= 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit		= 128K
query_cache_size		= 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type		= DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings		= 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file	= /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit	= 1000
log_slow_verbosity	= query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#report_host		= master1
#auto_increment_increment = 2
#auto_increment_offset	= 1
log_bin			= /var/log/mysql/mariadb-bin
log_bin_index		= /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog		= 1
expire_logs_days	= 10
max_binlog_size         = 100M
# slaves
#relay_log		= /var/log/mysql/relay-bin
#relay_log_index	= /var/log/mysql/relay-bin.index
#relay_log_info_file	= /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode		= NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine	= InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size	= 50M
innodb_buffer_pool_size	= 256M
innodb_log_buffer_size	= 8M
innodb_file_per_table	= 1
innodb_open_files	= 400
innodb_io_capacity	= 400
innodb_flush_method	= O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completion

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/

mariadb.cnf

# MariaDB-specific config file.
# Read by /etc/mysql/my.cnf

[client]
# Default is Latin1, if you need UTF-8 set this (also in server section)
#default-character-set = utf8 

[mysqld]
#
# * Character sets
# 
# Default is Latin1, if you need UTF-8 set all this (also in client section)
#
#character-set-server  = utf8 
#collation-server      = utf8_general_ci 
#character_set_server   = utf8 
#collation_server       = utf8_general_ci 
# Import all .cnf files from configuration directory
!includedir /etc/mysql/mariadb.conf.d/

/etc/mysql/conf.d/mysqld_safe_syslog.cnf

[mysqld_safe]
skip_log_error
syslog

/etc/mysql/mariadb.conf.d/ - пусто

[Vitaly Karasik]

Вячеслав Грачунов,

Сервер - впс на хецнере, 2 ядра, 8 ГБ RAM

А на этом сервере бежит только база или апликация тоже?
Если второе, то надо посмотреть сколько памяти берет апликация (и вебсервер если есть). Можно запустить atop, он умеет сохранять историю.
Если весь сервер для базы,
1) то 4-5ГБ можно дать ей

innodb_buffer_pool_size = 4G

2) убедиться что MyISAM не используется (https://dataedo.com/kb/query/mysql/list-myisam-tables) и закоментировать
key_buffer_size = 128M
myisam_sort_buffer_size = 512M

3) активировать slow query log
slow_query_log=1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1

тогда можно будет проанализировать и начать оптимизировать проблематичные запросы

4) пришлите нам вывод
show global status;

[Вячеслав Грачунов]

Vitaly Karasik, приложения там тоже крутятся, но никогда не видел этот сервер перегруженным.
Вот такую инфу выдает при заходе через ssh:

System load:  0.22               Processes:             225
  Usage of /:   35.9% of 75.06GB   Users logged in:       1
  Memory usage: 18%                IP address for eth0:   116.*.*.*
  Swap usage:   0%                 IP address for lxcbr0: 10.0.3.1

Вот вывод uptime:

23:28:59 up 146 days, 13:41,  3 users,  load average: 0.08, 0.14, 0.10

show global status
https://pastebin.com/up5JebYw

atop попробую, но не думаю, что он покажет перегрузку

[Лев Забудкин]

В первую очередь убрать конфиг mysql (mariadb) совсем куда-нибудь, перезапустить службу /etc/mysql restart и смотреть.
При обычном конфиге даже 150 коннектов это держит легко.

[Vitaly Karasik]

Вячеслав Грачунов, @zabudkin
Насколько вижу, проблема не в кол-ве коннектов как таковом (да и лимит коннектов редко бывает проблемой сам по себе)

В настройке у нас
max_connections = 500

status:
Max_used_connections 298
Connection_errors_max_connections 0

Я советую сделать изменения которые я советовал, плюс прислать /var/log/mysql/mysqld.log - или как он называется у mariadb.

[Вячеслав Грачунов]

Vitaly Karasik, извиняюсь, что долго не писал - был занят другими делами, вернулся к этому.
innodb_buffer_pool_size увеличил до 4 ГБ
Отключение MyISAM даст большой прирост?
Сделал лог долгих запросов, там полно такого:

# Time: 200201 17:47:26
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 71841  Schema: tgadmin_test  QC_hit: No
# Query_time: 72.940765  Lock_time: 0.000165  Rows_sent: 51075  Rows_examined: 51075
# Rows_affected: 0  Bytes_sent: 28192695
SET timestamp=1580575646;
SELECT * FROM tgmanager_delayedtasks WHERE completed = 0 AND dt < '2020-02-01 17:46:13.893177';

И все на одну и ту же таблицу. И запросы эти не из Django, а из другого скрипта. Запрос возвращает десятки тысяч строк, и , к сожалению, все поля нужны, исключить могу максимум одно. Проставил на эту таблицу индексы на dt и completed - не помогло.
Что-то можно сделать, чтобы такой запрос проходил быстрее? Может ли это быть причиной отвала соединения?

[Vitaly Karasik]

Вячеслав Грачунов,

Отключение MyISAM даст большой прирост?

не даст напрямую, но позволит не выделять лишнию память

Query_time: 72.940765 Lock_time: 0.000165 Rows_sent: 51075 Rows_examined: 51075

Индексы уже используются, поскольку Rows_sent == Rows_examined.
Улучшить ситуацию можно уменьшением кол-ва записей, которые мы вытаскиваем и/или уменьшением частоты запроса.

Может ли это быть причиной отвала соединения?

посмотрите/пришлите что в  error.log.

[Вячеслав Грачунов]

https://pastebin.com/W0unHR1u

[Vitaly Karasik]

И в этот период времени были проблемы с соединениями?

Попробуйте раскомментировать строку sql_mode в my.cnf - многие пишут что strict mode может быть причиной ошибок.