MySQL-ის კავშირების ლიმიტები და pooling: Too many connections

ERROR 1040 (HY000): Too many connections თითქმის არასოდეს ნიშნავს, რომ ბაზას უფრო დიდი max_connections სჭირდება. ის ნიშნავს, რომ რაღაცამ იმაზე მეტი კავშირი გახსნა, ვიდრე მიმდინარე სამუშაოს სჭირდებოდა, და ეს რაღაც ჩვეულებრივ pool-ია ნაგულისხმევი პარამეტრებით, რომლებსაც არავინ შეხედა, გამრავლებული ყველა პროცესსა და worker-ზე, რომელსაც თავისი pool უჭირავს. MySQL 8.4 ნაგულისხმევად 151 კლიენტურ კავშირს უშვებს. პატარა აპლიკაციას, რომელიც რეალურ ტრაფიკს ემსახურება, ერთდროულად 20-ზე მეტი იშვიათად სჭირდება. გამოსავალი თითქმის ყოველთვის კლიენტის მხარესაა: ნაკლები, გაზიარებული და ხელახლა გამოყენებული კავშირები - და სერვერის ლიმიტი, დაყენებული იმაზე, რასაც მეხსიერება სინამდვილეში უძლებს.

ეს პოსტი განიხილავს, როგორ ითვლის MySQL კავშირებს, რა ჯდება თითოეული, როგორ ნახო, ვის უჭირავს ისინი, და როგორ შეარჩიო pool-ის ზომა თითოეულ გავრცელებულ runtime-ში ისე, რომ ჯამი ლიმიტზე დაბლა დარჩეს იმ დღესაც, როცა მასშტაბს ზრდი.

როგორ ითვლის MySQL კავშირებს#

MySQL-თან ყოველი კლიენტური კავშირი session-ია საკუთარი thread-ით სერვერზე. MySQL 8.4 community გამოცემაში ერთ კავშირზე ერთ thread-ს იყენებს, ამიტომ კავშირების რაოდენობა იმავდროულად იმ სერვერული thread-ების რაოდენობაა, რომლებიც სამუშაოს ელოდებიან ან ასრულებენ.

პარამეტრები, რომლებიც წყვეტენ, რამდენი შეიძლება არსებობდეს:

ამ ცხრილში ორი დეტალი სიურპრიზების უმეტესობას ხსნის. პირველი: MySQL max_connections-ის მიღმა ერთ დამატებით კავშირს ინახავს ანგარიშისთვის, რომელსაც CONNECTION_ADMIN პრივილეგია აქვს (ან მოძველებული SUPER). ამიტომ შეგიძლია ჩვეულებრივ მაინც შეხვიდე root-ით და გამოიკვლიო სერვერი, რომელიც შენს აპლიკაციას უარს ეუბნება - თუ root თავად არ არის იმ ანგარიშებს შორის, რომლებიც მას ავსებენ. გამოიყენე ეს: აპლიკაციის მომხმარებელს ეს პრივილეგია არასოდეს უნდა ჰქონდეს.

მეორე: wait_timeout რვა საათია. აპლიკაცია, რომელიც კავშირებს ხსნის და მათ ავიწყდება, მთელი სამუშაო დღის განმავლობაში არ იწმინდება. სერვერზე 151-იანი ლიმიტით სწორედ ასე ავსებს ცხრილს შუადღემდე cron-იდან ყოველ წუთს გაშვებული სკრიპტი, რომელიც კავშირებს ჟონავს.

MySQL 8.0.14-დან ასევე არსებობს ცალკე ადმინისტრაციული ინტერფეისი (admin_address და admin_port, ნაგულისხმევი პორტი 33062), რომელიც პრივილეგირებული ანგარიშებიდან კავშირებს max_connections-ის მიუხედავად იღებს. ის გამორთულია, სანამ admin_address არ დაყენდება, და ჰოსტინგის გეგმაზე ეს პორტი ჩვეულებრივ ხელმისაწვდომი არ არის, ამიტომ ავარიულ კარად დარეზერვებული დამატებითი კავშირი ჩათვალე.

რა ჯდება კავშირი#

max_connections-ის 2,000-მდე აწევა კონფიგურაციის ცვლილებაა და არა ტევადობა. ყოველი კავშირი სერვერზე მეხსიერებას მოიხმარს, და ამ მეხსიერების ნაწილი მხოლოდ მაშინ გამოიყოფა, როცა query-ს სჭირდება - რაც ნიშნავს, რომ სერვერი კარგად გამოიყურება ზუსტად იმ მომენტამდე, როცა ბევრი კავშირი ერთდროულად მძიმე query-ებს უშვებს.

session-ის ბუფერები, რომლებიც მნიშვნელოვანია:

უმოქმედო კავშირი ერთ-ორ მეგაბაიტს ჯდება. კავშირს, რომელიც ცუდად ინდექსირებულ ანგარიშს უშვებს sort-ით, ორი ინდექსის გარეშე join-ით და დროებითი ცხრილით, შეიძლება მოკლე დროით ათეულობით მეგაბაიტი დაუჯდეს. გაამრავლე უარესი შემთხვევა max_connections-ზე და მიიღებ რიცხვს, რომელიც innodb_buffer_pool_size-ის შემდეგ დარჩენილ RAM-ს უნდა შეადარო.

ეს არის ზომის შერჩევის ნამდვილი წესი. 1 GB სერვერზე buffer pool დაახლოებით ნახევარს იღებს, სერვერის საკუთარი overhead - რამდენიმე ასეულ მეგაბაიტს, და დარჩენილი ალბათ 50-დან 100-მდე აქტიურ კავშირს უძლებს ჩვეულებრივ სამუშაოზე. 4 GB-ზე რამდენიმე ასეულისთვის გაქვს ადგილი, ხოლო 8 GB-ზე ლიმიტი უფრო CPU იქნება, ვიდრე მეხსიერება - 300 კავშირი, რომლებიც ოთხ ბირთვზე query-ებს ასრულებენ, უბრალოდ ბირთვების რიგში დგანან. MySQL InnoDB-ის მორგება პატარა სერვერებისთვის ამ ჯამის buffer pool-ის მხარეს განიხილავს.

როგორ გაიგო, ვის უჭირავს კავშირები#

სანამ რომელიმე რიცხვს შეცვლი, შეხედე. შედი root-ით (დარეზერვებული კავშირი შეგიშვებს მაშინაც, როცა აპლიკაცია ვერ შედის) და ჰკითხე სერვერს.

SHOW GLOBAL STATUS WHERE Variable_name IN  ('Threads_connected', 'Threads_running', 'Max_used_connections',   'Max_used_connections_time', 'Aborted_connects', 'Aborted_clients',   'Connection_errors_max_connections');

რას გეუბნება თითოეული:

• Threads_connected არის, რამდენი კავშირი არსებობს ახლა. Threads_running - რამდენი ასრულებს სინამდვილეში ბრძანებას. ჯანსაღ აპლიკაციაში დიდი სხვაობაა: 40 დაკავშირებული, 2 მომუშავე.
• Max_used_connections ბოლო restart-ის შემდეგ მაქსიმალური ნიშნულია, ხოლო Max_used_connections_time ამბობს, როდის მოხდა. თუ პიკი 151-ია და დრო გათიშვას ემთხვევა, ინციდენტი ნაპოვნია.
• Connection_errors_max_connections ლიმიტის გამო უარების რაოდენობას ითვლის.
• Aborted_clients იზრდება, როცა კლიენტები სუფთა დახურვის გარეშე ქრებიან - ჩვეულებრივ მოთხოვნის შუაში მოკლული პროცესები ან wait_timeout-ით დახურული კავშირები.

შემდეგ დაშალე კავშირები იმის მიხედვით, ვინ და საიდან:

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, db, command,       COUNT(*) AS conns, MAX(time) AS longest_sFROM information_schema.processlistGROUP BY user, client, db, commandORDER BY conns DESC;

ასი ხაზი command = 'Sleep'-ით ერთი კლიენტის მისამართიდან ან ზედმეტად დიდი pool-ია, ან პროცესი, რომელიც ჟონავს. ათიოდე ხაზი command = 'Query'-ით და დიდი time-ით ნელი query-ებია, რომლებსაც კავშირები უჭირავთ, და ეს სხვა პრობლემაა: pool ივსება, რადგან ყოველი კავშირი lock-ის ან სრული table scan-ის უკან გაჭედილა. ამისთვის ინსტრუმენტი MySQL-ის slow query log-ია.

sys.session უფრო მდიდარ ხედს გაძლევს, თუ ყოველი session-ისთვის მიმდინარე ბრძანება, მეხსიერება და lock-ების ინფორმაცია გინდა, ხოლო performance_schema.threads-ში იგივე მონაცემებია უფრო დაბალ დონეზე. ავარიულ სიტუაციაში KILL <id> კავშირს ამთავრებს, ხოლო KILL QUERY <id> მხოლოდ მასზე გაშვებულ ბრძანებას აჩერებს.

სერვერის ლიმიტების შეცვლა#

root პაროლით ლიმიტების შეცვლა პირდაპირ, გაშვებულ სერვერზე შეგიძლია. SET GLOBAL შემდეგ restart-მდე მოქმედებს; SET PERSIST მნიშვნელობას data დირექტორიაში mysqld-auto.cnf-შიც წერს, ამიტომ ის restart-ს გადაურჩება.

-- Look firstSELECT @@max_connections, @@wait_timeout, @@max_user_connections;-- A realistic limit for a 2 GB server, kept across restartsSET PERSIST max_connections = 200;-- Close idle connections after ten minutes instead of eight hoursSET PERSIST wait_timeout = 600;-- Cap one application account so it cannot starve the othersALTER USER 'app'@'%' WITH MAX_USER_CONNECTIONS 120;

უფრო მოკლე wait_timeout სამიდან ყველაზე სასარგებლოა სერვერზე, რომელსაც რამდენიმე აპლიკაცია იზიარებს, რადგან ის აგროვებს კავშირებს, რომლებიც ჩავარდნილმა ან დაუდევარმა კლიენტებმა მიატოვეს. მას ერთი პირობა ახლავს: შენმა pool-ებმა კავშირები უნდა განაახლონ, სანამ სერვერი მათ მოკლავს, თორემ აპლიკაცია დროდადრო მკვდარ კავშირს აიღებს და მასზე პირველი query ჩავარდება MySQL server has gone away (შეცდომა 2006) ან Lost connection to MySQL server during query (შეცდომა 2013) შეტყობინებით. ქვემოთ ყველა runtime-ს აქვს ამის პარამეტრი, ჩვეულებრივ maximum lifetime ან recycle time ჰქვია. დააყენე wait_timeout-ზე ერთი წუთით ან მეტით ნაკლებზე.

ანგარიშზე ლიმიტი მეორე ნაკლებად გამოყენებული ინსტრუმენტია. თუ ერთ ბაზის სერვერს ანგარიშების job-ი, cron სკრიპტი და ვებ აპლიკაცია იზიარებენ, თითოეულს საკუთარი მომხმარებელი მიეცი MAX_USER_CONNECTIONS-ით. როცა cron job-ი ცუდად მოიქცევა, ის საკუთარ ჭერს ეჯახება (შეცდომა 1203, User already has more than 'max_user_connections' active connections) და საიტი აგრძელებს მუშაობას. MySQL-ის მომხმარებლები და პრივილეგიები ამ ცალკეული ანგარიშების შექმნას განიხილავს.

pooling-ის არითმეტიკა#

pool არის კავშირების ნაკრები, რომლებიც ერთხელ იხსნება და მოთხოვნებს სესხად ეძლევა. ის იმიტომ არსებობს, რომ MySQL კავშირის გახსნა ჯდება TCP handshake, TLS handshake (თუ იყენებ) და ავთენტიფიკაციის გაცვლა - რამდენიმე round trip, რაც ქსელზე თითო მოთხოვნაზე მილიწამებად გროვდება. კავშირის ხელახლა გამოყენება არაფერი ჯდება.

მახე ის არის, რომ ყოველ პროცესს საკუთარი pool აქვს. კავშირების ჯამური რაოდენობა, რომელიც შენს აპლიკაციას შეუძლია გახსნას:

total = pool size  x  processes per instance  x  instances (+ cron, workers, shells)

Node.js აპლიკაციას mysql2-ის ნაგულისხმევი 10 კავშირით, cluster რეჟიმში 4 worker-ით გაშვებულს და 3 სერვერზე deploy-ირებულს, შეუძლია 120 კავშირი გახსნას, სანამ ვინმე migration-ს გაუშვებს. დაამატე რიგის worker-ი საკუთარი 10-იანი pool-ით და staging გარემო, რომელიც იმავე ბაზას უყურებს, და 151-ზე ხარ.

სასარგებლო რიცხვი მაქსიმუმი კი არა, ის პარალელურობაა, რომელიც სინამდვილეში გჭირდება. pool-ს იმდენი კავშირი სჭირდება, რამდენი query-ც ერთსა და იმავე წამს მიმდინარეობს. თუ ყოველი მოთხოვნა ბაზაში 5 ms-ს ატარებს და ინსტანსი წამში 200 მოთხოვნას ემსახურება, ეს წამში ბაზის ერთი წამის დროა - დაახლოებით ერთი დაკავებული კავშირი. ათი უხვია. HikariCP პროექტის ფართოდ ციტირებული საწყისი წერტილი ბაზის სერვერისთვის connections = (cores x 2) + effective spindle count-ია, და 2 vCPU ბაზაზე ეს ყველა კლიენტზე ჯამში ერთნიშნა რიცხვიდან რამდენიმე ათეულამდე გამოდის - იმაზე ნაკლები, ვიდრე უმეტესობა ელის, და უფრო სწრაფი, რადგან ბაზა ნაკლები ერთდროული query-ით ნაკლებ დროს ხარჯავს მათ შორის გადართვაზე.

ჩაიწერე ეს ჯამი სადმე max_connections-ის გვერდით და დატოვე 20% მარაგი migration-ებისთვის, shell-ისა და დროდადრო backup-ისთვის.

pool-ის პარამეტრები runtime-ების მიხედვით#

თითოეული runtime-ის ნაგულისხმევი მნიშვნელობები და რა უნდა შეცვალო.

PHP

PHP-FPM-ს გაზიარებული pool არ აქვს. ყოველი FPM worker ცალკე პროცესია, რომელიც თითო მოთხოვნაზე კავშირს ხსნის და ბოლოს ხურავს, ამიტომ კავშირების რაოდენობას pm.max_children ზღუდავს. pool-ს pm.max_children = 50-ით შეუძლია 50 კავშირი გახსნას. ეს ჩვეულებრივ ნორმალურია; მოკლე ქსელურ გზაზე კავშირების გახსნა იაფია. PDO::ATTR_PERSISTENT => true თითო worker-ზე ერთ კავშირს ღიად ინახავს მოთხოვნებს შორის - ის handshake-ს ზოგავს, მაგრამ session-ის მდგომარეობას (დროებით ცხრილებს, SET ცვლადებს, fatal შეცდომის შემდეგ ღია ტრანზაქციას) შემდეგ მოთხოვნას უტოვებს და უმოქმედო worker-ებს უმოქმედო კავშირებად აქცევს. გამოიყენე მხოლოდ მაშინ, თუ handshake-ის ფასი გაზომე. PHP PDO და MySQL კავშირის პარამეტრებს დეტალურად განიხილავს.

Node.js

import mysql from "mysql2/promise";export const pool = mysql.createPool({  host: process.env.DB_HOST,  port: Number(process.env.DB_PORT),  user: process.env.DB_USER,  password: process.env.DB_PASSWORD,  database: process.env.DB_NAME,  connectionLimit: 10,     // default 10  waitForConnections: true, // queue instead of failing  queueLimit: 0,           // 0 = unbounded queue  maxIdle: 5,              // idle connections kept open  idleTimeout: 60000,      // ms before an idle connection is closed  enableKeepAlive: true,});

შექმენი pool ერთხელ თითო პროცესზე, მოდულის დონეზე. კლასიკური გაჟონვა createPool-ის მოთხოვნის handler-ის შიგნით გამოძახებაა, რაც ყოველ მოთხოვნაზე ახალ ათკავშირიან pool-ს ქმნის. serverless deploy-ებში ყოველ ფუნქციის ინსტანსს საკუთარი pool აქვს, ამიტომ იქ connectionLimit დაბლა დააყენე (1-2).

Python

pool-ს SQLAlchemy-ის engine ინახავს: ნაგულისხმევად pool_size=5 და max_overflow=10, ანუ თითო პროცესზე 15-მდე. დაამატე pool_recycle wait_timeout-ზე ნაკლები და pool_pre_ping=True, რომელიც კავშირს გაცემამდე ამოწმებს.

from sqlalchemy import create_engineengine = create_engine(    "mysql+pymysql://app:secret@db.example.com:3306/app?charset=utf8mb4",    pool_size=5, max_overflow=5, pool_recycle=540, pool_pre_ping=True,)

Gunicorn-ს 4 worker-ით და ამ engine-ით შეუძლია 40 გახსნას. Django საერთოდ არ აკეთებს pooling-ს: CONN_MAX_AGE-ის ნაგულისხმევი 0-ია, რაც ყოველ მოთხოვნაზე ახალ კავშირს ნიშნავს; დააყენე wait_timeout-ზე ნაკლებ მნიშვნელობაზე (და CONN_HEALTH_CHECKS = True), რომ კავშირები worker-ის თითო thread-ზე ხელახლა გამოიყენო.

Java და .NET

HikariCP-ის ნაგულისხმევია maximumPoolSize = 10 და maxLifetime = 1800000 ms (30 წუთი) - რაც ნაგულისხმევ რვასაათიან wait_timeout-თან ნორმალურია და უნდა შემცირდეს, თუ მას დაამოკლებ. MySqlConnector .NET-ისთვის ნაგულისხმევად pool-ავს Maximum Pool Size=100-ით, რაც დიდია; პატარა სერვერისთვის connection string-ში ჩაწერე Maximum Pool Size=20;Connection Lifetime=540. Go-ს database/sql-ს ღია კავშირებზე ლიმიტი არ აქვს, სანამ SetMaxOpenConns-ს არ გამოიძახებ, და ნაგულისხმევად ორ უმოქმედოს ინახავს; ყოველთვის დააყენე SetMaxOpenConns, SetMaxIdleConns და SetConnMaxLifetime.

Connection pool-ები და ლიმიტები იმავე იდეებს PostgreSQL-ის მხრიდან განიხილავს, სადაც კავშირები პროცესებია და არითმეტიკა კიდევ უფრო მკაცრია.

შეცდომები, რომლებსაც რეალურად ნახავ, და მათი მოგვარება#

`ERROR 1040: Too many connections`. გლობალური ლიმიტი მიღწეულია. შედი root-ით, გაუშვი ზემოთ მოცემული processlist query და იპოვე კლიენტი, რომელსაც მათი უმეტესობა უჭირავს. თუ აპლიკაცია დაუყოვნებლივ გჭირდება, მოკალი მძინარე კავშირები, შემდეგ კი გაასწორე pool-ის ზომა ან გაჟონვა. max_connections ასწიე მხოლოდ მაშინ, თუ ლეგიტიმური pool-ების ჯამი ნამდვილად აღემატება მას და მეხსიერება იძლევა.

`ERROR 1203: User already has more than 'max_user_connections' active connections`. ანგარიშის ლიმიტი. ან ლიმიტია ზედმეტად დაბალი იმ pool-ებისთვის, რომლებიც ამ ანგარიშს იყენებენ, ან ერთი პროცესი ჟონავს.

`MySQL server has gone away` (2006) ან `Lost connection to MySQL server during query` (2013) უმოქმედობის პერიოდების შემდეგ. სერვერმა უმოქმედო კავშირი wait_timeout-ზე დახურა და pool-მა ის მაინც გასცა. დააყენე pool-ის maximum lifetime wait_timeout-ზე ნაკლებზე ან ჩართე pre-ping. თუ ეს დიდი insert-ის დროს ხდება, მეორე კლასიკური მიზეზი max_allowed_packet-ზე დიდი პაკეტია (8.4-ში ნაგულისხმევად 64 MB).

`Host 'x' is blocked because of many connection errors`. ერთი ჰოსტიდან max_connect_errors (ნაგულისხმევად 100) ზედიზედ წარუმატებელი handshake-ის შემდეგ MySQL მას ბლოკავს. ჩვეულებრივ ეს health check-ია, რომელიც TCP კავშირს ხსნის და შესვლის გარეშე ხურავს. FLUSH HOSTS ამოიღეს TRUNCATE TABLE performance_schema.host_cache-ის სასარგებლოდ, რომელიც ბლოკს ხსნის; შემდეგ health check გაასწორე.

pool კავშირის მოლოდინში timeout-ს იძლევა, მაგრამ MySQL ცოტა კავშირს აჩვენებს. pool ზედმეტად პატარაა პარალელურობისთვის, ან კავშირები ნელი სამუშაოს განმავლობაში უჭირავთ - მაგალითად, HTTP გამოძახება, რომელიც ღია ტრანზაქციის დროს კეთდება. გაათავისუფლე კავშირები, სანამ რაიმეს გააკეთებ, რაც query არ არის.

კავშირები მთელი დღე ნელა იზრდება. გაჟონვა: კოდი, რომელიც კავშირს იღებს და შეცდომის გზაზე ადრე ბრუნდება, ისე რომ არ ათავისუფლებს. Node-ში, სადაც შეგიძლია, getConnection()-ის ნაცვლად pool.query() გამოიყენე, რადგან ის ავტომატურად ათავისუფლებს.

FAQ#

რამდენი უნდა იყოს max_connections?

საკმარისად მაღალი შენი pool-ების ჯამისთვის პლუს 20% მარაგი, და საკმარისად დაბალი, რომ ყველა კავშირი, რომელიც ერთდროულად ზომიერად მძიმე query-ს უშვებს, მაინც დაეტიოს მეხსიერებაში. აპლიკაციების უმეტესობისთვის 1-4 GB MySQL სერვერზე ეს სადღაც 100-დან 300-მდეა. ნაგულისხმევი 151 ერთი აპლიკაციისთვის გონივრულია.

connection pool ყოველთვის უფრო სწრაფია?

ყველაფრისთვის, რაც წამში რამდენიმე query-ზე მეტს უშვებს, კი - ის ყოველი მოთხოვნიდან რამდენიმე ქსელურ round trip-ს აშორებს. cron სკრიპტისთვის, რომელიც საათში ერთხელ ეშვება, pool არაფერს მატებს; გახსენი ერთი კავშირი, შეასრულე სამუშაო, დახურე.

მჭირდება ProxySQL ან სხვა connection proxy?

მხოლოდ მაშინ, როცა იმაზე მეტი კლიენტი გყავს, ვიდრე გონივრულ max_connections-ს შეუძლია მოემსახუროს - ასობით serverless ფუნქცია ან ათობით ინსტანსი, თითოეული საკუთარი pool-ით. proxy ბევრ კლიენტურ კავშირს რამდენიმე სერვერულ კავშირზე ამულტიპლექსებს. ერთიდან ხუთამდე აპლიკაციის სერვერისთვის სწორად შერჩეული pool-ები უფრო მარტივია და საკმარისი.

რატომ აჩვენებს ჩემი ბაზა ასობით მძინარე კავშირს?

თითოეული კავშირია, რომელიც კლიენტმა გახსნა და ახლა არ იყენებს. ზომიერად ეს ნორმალურია - pool ასე გამოიყურება, როცა ტრაფიკი დაბალია. ასობით ერთი წყაროდან ნიშნავს, რომ pool-ის მაქსიმუმი ზედმეტად დიდია ან კავშირები არასოდეს ბრუნდება. wait_timeout-ის შემცირება დაუდევარი კლიენტების შემდეგ ალაგებს, მაგრამ მათ არ ასწორებს.

მოქმედებს wait_timeout ხანგრძლივ query-ებზე?

არა. ის მხოლოდ უმოქმედო კავშირებზე ვრცელდება. query, რომელიც ოც წუთს მიმდინარეობს, უმოქმედო არ არის. გრძელ query-ებს SELECT ბრძანებებისთვის max_execution_time ზღუდავს (მილიწამებში, ნაგულისხმევად 0, ანუ ლიმიტის გარეშე) და კლიენტის საკუთარი read timeout.