С точки зрения СУБД, соединение с клиентским приложением – ресурс, которым надо тщательно управлять для аккуратного расходования системных ресурсов. Практически все СУБД устанавливают таймауты неактивности соединения, после достижения которых данное соединени разрывается в одностороннем порядке. Обычно, приложения узнают о данном факте уже после того, как соединение разорвано.
В случае с MySQL, клиент получает сообщение The MySQL server has gone away (error 2006). В статье мы рассмотрим подходы, которые позволяют приложениям c долгоживущими соединениям, поддерживать их столько, сколько требуется. Примеры будут приводиться для стандартного интерфейса подключения к СУБД MySQL – mysql.connector. Язык примеров – Python3, в качестве прикладного примера будет использоваться микросервис, реализованный на фреймворке Tornado.
Зачем держать соединение открытым
Итак, этот вопрос является важным, можно с него и начать. Каждое открытие и закрытие соединения занимает время и несет накладные расходы, как на стороне сервера так и на стороне клиента. Особенно заметно этот фактор начинает влиять, если сервер и клиент находятся в географически разнесенных сетях с существенной задержкой прохождения пакетов (RTT > 100 мс+).
К примеру, для одного из наших приложений, у которого сервер СУБД находится в РФ, а сервер приложения в США, время установления соединения составляет 4 секунды. Очевидно, что наивный подход с открытием соединения по требованию не представляется разумным для микросервиса, ответы от которого ожидаются максимально быстро по протоколу HTTP.
Наивный подход с таймаутами
Наивный подход, который заключается в установке таймаутов на стороне клиента и сервера может быть применен довольно редко, например, в случае корпоративной внутренней сети с безопасными приложениями и ограниченным количеством пользователей, как правило в двухуровневой архитектуре (2 tier architecture).
Другой пример данного подхода – приложения, которые просто редко обращаются к СУБД, что требует корректировки значения по-умолчанию, установленного для таймаута соединения.
За таймаут соединения отвечают три переменные, которые можно установить как глобальные переменные:
SET GLOBAL connect_timeout=3600
SET GLOBAL wait_timeout=3600
SET GLOBAL interactive_timeout=3600
или как настроечные переменные:
[mysqld]
connect_timeout=3600
wait_timeout=3600
interactive_timeout=3600
Значения этих переменных:
- connection_timeout – максимальное время ожидания сервером пакета connect для вновь открытого соединения;
- interactive_timeout – максимальное время ожидания сервером на интерактивном соединении;
- wait_timeout – максимальное время ожидания сервером активности на соединении.
Стоит отметить, что данный способ не защищает от перезагрузки сервера и клиентские приложения все равно могут столкнуться с ситуацией разрыва соединения. Мы не рекомендуем применять данный без дополнительных, которые описаны далее.
Вызов MySQL ping перед первым запросом и повторное соединение
Данный подход довольно универсален и должен применяться в большинстве случаев.
connection = None
def init_db():
return = mysql.connector.connect(
mysql_host=db_host,
mysql_user=db_user,
mysql_password=db_passwd,
mysql_db_name=db_dbase,
mysql_port=db_port)
def get_cursor():
try:
connection.ping(reconnect=True, attempts=3, delay=5)
except mysql.connector.Error as err:
# reconnect your cursor as you did in __init__ or wherever
connection = init_db()
return connection.cursor()
connection = init_db()
...
# перед серией запросов
cursor = get_cursor()
# запросы
Периодический вызов ping (Tornado)
Предыдущий метод является универсальным и может применяться повсеместно. Однако, бывают случаи, когда необходимо поддерживать соединение в высокой готовности, чтобы минимизировать время ответа. Рассмотрим простой способ решения для микросервиса Tornado, который использует дополнительный поток.
Необходимо заметить, что соединения с MySQL не потокобезопасны, поэтому нельзя непосредственно вызывать ping из дополнительного потока. Для решения этой проблемы мы реализуем дополнительный API (/keepalive), который будем периодически вызывать из дополнительного потока.
Сначала рассмотрим дополнительный поток. Реализация типовая:
import logging
import threading
import socket
import os
from time import sleep
from http.client import HTTPConnection
class Watchdog(threading.Thread):
def __init__(self, ip, port, url, timeout, pause):
threading.Thread.__init__(self)
self.ip = ip
self.port = port
self.url = url
self.timeout = timeout
self.pause = pause
def run(self):
while True:
logging.debug("Sleeping between KeepAlives for %d seconds" % self.pause)
sleep(self.pause)
try:
logging.debug("Calling for KeepAlive [%s:%d, %s]" % (self.ip, self.port, self.url))
host, port = self.ip, self.port
ip, port = socket.getaddrinfo(host, port)[0][-1]
conn = HTTPConnection(ip, port, timeout=self.timeout)
conn.request('GET', self.url)
resp = conn.getresponse()
data = resp.read()
logging.debug(data)
except socket.timeout:
logging.debug("Keepalive timeout happened. Stop whole app.")
os._exit(1)
В реализации важно отметить только способ соединения с сервером через IP-адрес и порт. Только этот способ обеспечивает корректную обработку таймаута ожидания с генерацией исключения.
В Tornado реализуем обработчик для KeepAlive:
import logging
import tornado.web
from libs.commons import Commons
class KeepaliveHandler(tornado.web.RequestHandler):
def get(self):
logging.debug("Checking the state of connections")
try:
Commons.connection.ping(reconnect=True, attempts=3, delay=5)
except mysql.connector.Error as err:
Commons.connection = init_db()
self.write('OK')
В теле сервиса запустим наш поток:
app = tornado.web.Application([
('/keepalive', KeepaliveHandler),
])
tornado.ioloop.IOLoop.current().start()
watchdog = Watchdog(ip='127.0.0.1', port=Commons.server_port, url='/keepalive', pause=60, timeout=60)
watchdog.start()
app.run(Commons.server_port)
watchdog.join()
Теперь, каждую минуту будет проверяться подключение к БД, а в случае разрыва выполняться переподключение и обеспечиваться высокая готовность соединения. В том случае, если поток watchdog не сможет дождаться активации соединения, он завершает весь сервис, чтобы внешняя система управления могла его заново запустить (например, docker run --restart=always).
Заключение
Мы рассмотрели три способа обработки таймаутов соединения клиента с СУБД MySQL, которые, будучи применены вместе позволяют приложениям поддерживать соединения с СУБД в течение неограниченного времени.
Конечно, если вы используете продвинутые ORM над низкоуровневым драйвером, например, SQLAlchemy, то проблема может эффективно решаться самой ORM. В случае, когда высокоуровневые ORM излишни, используйте практики из данной статьи для надежной работы своих приложений.
Если вам понравилась статья, поделитесь ей с друзьями.