«साइट धीमी है» लगभग हमेशा डेटाबेस तक पहुँचता है। और अगर जाँच सही क्रम से की जाए तो कुछ मिनट लेती है: पहले वह जो ख़ुद धीमेपन के दौरान दिखता है, फिर वह जो हफ़्तों से जमा हो रहा था।

जब समस्या चल रही हो तभी देखें

बुनियादी नियम: सबसे क़ीमती जानकारी ठीक उसी वक़्त उपलब्ध होती है जब सब कुछ धीमा हो। MySQL के लिए पहली कमांड:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

PostgreSQL के लिए:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

आप देखेंगे कि इस समय क्या चल रहा है और कितनी देर से। और जवाब आमतौर पर तुरंत मिलता है: कोई एक भारी क्वेरी बाकी सबको रोके हुए है, या सौ एक जैसी क्वेरी किसी ताले के खुलने की प्रतीक्षा में हैं।

कनेक्शन

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

अगर Max_used_connections max_connections के क़रीब पहुँच रहा हो, तो एप्लिकेशन समय-समय पर «too many connections» का इनकार पा रही है। और सीमा बढ़ाना पहला काम नहीं होना चाहिए: हर कनेक्शन मेमोरी लेता है, और जिस सर्वर में मेमोरी पहले ही कम हो वहाँ उसे बढ़ाना सिर्फ़ स्वैप तक के सफ़र को तेज़ करता है। पहले यह पता करें कि कनेक्शन मुक्त क्यों नहीं हो रहे: आमतौर पर कोई लंबी क्वेरी या एप्लिकेशन की तरफ़ कनेक्शन पूल का न होना।

और इसे दूसरी तरफ़ से भी देखना चाहिए: कनेक्शन में तेज़ बढ़ोतरी बढ़ती लोकप्रियता से नहीं, बल्कि किसी भारी पन्ने को पीटते बॉट से भी आ सकती है — कोई खोज, सूची का फ़िल्टर, रिपोर्ट बनाने वाला।

धीमी क्वेरी

धीमी क्वेरी के लॉग बिना आगे जाने की कोई जगह नहीं। MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

एक दिन बाद:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, postgresql.conf में:

log_min_duration_statement = 1000

फिर विस्तार pg_stat_statements, जो प्रति क्वेरी कुल समय सीधे दिखाता है:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

ध्यान रहे: अहम सबसे लंबी क्वेरी नहीं, बल्कि वह है जिसका कुल समय सबसे ज़्यादा हो। 50 मिलीसेकंड की क्वेरी जो मिनट में हज़ार बार चले, दिन में एक बार चलने वाली दस सेकंड की रिपोर्ट से ज़्यादा नुक़सान करती है।

वह सेटिंग जो सबसे ज़्यादा भुलाई जाती है

MySQL और MariaDB के लिए यह InnoDB के बफ़र पूल का आकार है। डिफ़ॉल्ट क़ीमत 128 मेगाबाइट है और दशकों से नहीं बदली। और ऐसे सर्वर पर जहाँ डेटाबेस कई गीगाबाइट घेरता हो, इसका मतलब मेमोरी के बजाय डिस्क से लगातार पढ़ना है।

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

समर्पित डेटाबेस सर्वर के लिए समझदार पैमाना RAM का आधा है; और ऐसे सर्वर के लिए जिस पर वेब सर्वर और PHP भी चल रहे हों, एक चौथाई, इस ख़याल के साथ कि स्वैप में न जाए। और यह एक सेटिंग आमतौर पर एक हफ़्ते की क्वेरी सुधारने से ज़्यादा देती है।

दो चीज़ें जो चुपचाप डिस्क खा जाती हैं

MySQL के बाइनरी लॉग। ये प्रतिकृति और समय के किसी बिंदु तक बहाली के लिए चाहिए, लगातार बढ़ते हैं, और डिफ़ॉल्ट रूप से कभी-कभी कभी नहीं मिटते। जाँचने के लिए:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

अगर आपके यहाँ प्रतिकृति नहीं है और आप समय के बिंदु तक बहाली इस्तेमाल नहीं करते, तो रखने की अवधि कुछ समझदार दिन कर दें।

भुलाए गए प्रतिकृति स्लॉट के साथ PostgreSQL की WAL फ़ाइलें। कम मशहूर और ज़्यादा ख़तरनाक स्थिति: अगर प्रतिकृति का स्लॉट मौजूद हो पर उसका उपभोक्ता अब न जुड़ रहा हो, तो PostgreSQL कटने के क्षण से हर लॉग रखने को बाध्य है — और डिस्क ख़त्म होने तक रखेगा।

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

ऐसा निष्क्रिय स्लॉट जिसकी किसी को ज़रूरत नहीं, मिटा देना चाहिए। और यह उन गिनी-चुनी स्थितियों में से एक है जहाँ डेटाबेस निश्चित रूप से डिस्क को शून्य तक ले आता है।

आकार और बढ़ोतरी

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

और काम का मौजूदा आँकड़ा नहीं, बल्कि उसके बदलने की रफ़्तार है। ऐसा डेटाबेस जो ट्रैफ़िक में आनुपातिक बढ़ोतरी के बिना एक महीने में दोगुना हो गया हो, आमतौर पर इसका मतलब है कि कोई प्रविष्टियों की तालिका लिखी जा रही है और कभी साफ़ नहीं होती — सत्र, कार्यों का इतिहास, किसी प्लगइन की घटनाओं का लॉग।

दो पंक्तियों में सुरक्षा

और जब डेटाबेस का कंसोल खुला ही है तो दो चीज़ें जाँच लेने लायक हैं। पहली, क्या डेटाबेस बाहर से पहुँच में है (ss -tulpn | grep 3306): उसे लगभग कभी बाहर की ओर नहीं होना चाहिए। और दूसरी, वे खाते जो किसी भी पते से जुड़ सकते हैं:

mysql -e "SELECT user, host FROM mysql.user"

host = '%' वाली प्रविष्टि का मतलब है «हर जगह से कनेक्शन»। और अगर पोर्ट भी खुला हो, तो डेटाबेस और इंटरनेट के बीच सिर्फ़ एक पासवर्ड खड़ा है।

बैकअप

आख़िरी बात। बैकअप फ़ाइल का होना कुछ गारंटी नहीं देता; गारंटी सिर्फ़ आज़माई गई बहाली देती है। ऐसी प्रति जो कभी बहाल न की गई हो, बराबर संभावना रखती है कि कटी हुई निकले, ताले की ख़राबी के साथ ली गई हो, या उसमें ग़लत डेटाबेस हो। और यह पहले से और किसी अलग मशीन पर जाँचना चाहिए, न कि उस दिन जब प्रति की ज़रूरत पड़े।

रोज़मर्रा की निगरानी के लिए तीन आँकड़े काफ़ी हैं: कनेक्शन की संख्या, रोज़ाना धीमी क्वेरी की संख्या, और डेटाबेस का आकार। तीनों धीरे और पूर्वानुमेय ढंग से बदलते हैं, और इनमें से किसी एक की तेज़ हरकत नज़र डालने लायक है। एक पन्ने पर ये कैसे लगते हैं, नीचे डेमो दिखाता है।