«เว็บไซต์ช้า» เกือบทุกครั้งไปจบที่ฐานข้อมูล และถ้าตรวจตามลำดับที่ถูกต้องก็ใช้เวลาไม่กี่นาที: ก่อนอื่นคือสิ่งที่เห็นได้ระหว่างที่มันช้าอยู่ แล้วจึงเป็นสิ่งที่สะสมมาหลายสัปดาห์

ดูตอนที่ปัญหากำลังเกิดขึ้น

กฎพื้นฐาน: ข้อมูลที่มีค่าที่สุดหาได้ในตอนที่ทุกอย่างกำลังช้าพอดี คำสั่งแรกสำหรับ 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» เป็นระยะ และการเพิ่มขีดจำกัดไม่ควรเป็นสิ่งแรกที่ทำ: การเชื่อมต่อแต่ละอันกินหน่วยความจำ และบนเซิร์ฟเวอร์ที่หน่วยความจำน้อยอยู่แล้ว การเพิ่มมันมีแต่จะเร่งการเดินทางไปสู่ swap ก่อนอื่นให้หาว่าทำไมการเชื่อมต่อจึงไม่ถูกปล่อย: ปกติคือคิวรียาวสักตัวหรือการไม่มี connection pool ทางฝั่งแอปพลิเคชัน

และควรมองอีกด้านหนึ่งด้วย: การเพิ่มขึ้นอย่างรวดเร็วของการเชื่อมต่ออาจไม่ได้มาจากความนิยมที่เพิ่มขึ้น แต่มาจากบอตที่กระหน่ำหน้าหนัก ๆ ก็ได้ — การค้นหา ตัวกรองรายการ ตัวสร้างรายงาน

คิวรีที่ช้า

ถ้าไม่มีล็อกคิวรีที่ช้าก็ไปต่อไม่ได้ 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 นั่นคือขนาดของ buffer pool ของ InnoDB ค่าเริ่มต้นคือ 128 เมกะไบต์และไม่เปลี่ยนมาหลายทศวรรษ และบนเซิร์ฟเวอร์ที่ฐานข้อมูลกินพื้นที่หลายกิกะไบต์ นั่นแปลว่าการอ่านจากดิสก์อย่างต่อเนื่องแทนที่จะอ่านจากหน่วยความจำ

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

เกณฑ์ที่สมเหตุสมผลสำหรับเซิร์ฟเวอร์ฐานข้อมูลโดยเฉพาะคือครึ่งหนึ่งของ RAM และสำหรับเซิร์ฟเวอร์ที่มีเว็บเซิร์ฟเวอร์และ PHP ทำงานอยู่ด้วยคือหนึ่งในสี่ โดยคำนึงว่าอย่าให้ไปถึง swap และการตั้งค่าเพียงข้อเดียวนี้มักให้ผลมากกว่าการปรับปรุงคิวรีทั้งสัปดาห์

สองสิ่งที่กินดิสก์อย่างเงียบ ๆ

ไบนารีล็อกของ MySQL พวกมันจำเป็นสำหรับการทำสำเนาและการกู้คืนถึงจุดเวลาหนึ่ง โตขึ้นเรื่อย ๆ และโดยค่าเริ่มต้นบางครั้งก็ไม่เคยถูกลบเลย ตรวจได้ด้วย:

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

ถ้าคุณไม่ได้ทำสำเนาและไม่ได้ใช้การกู้คืนถึงจุดเวลา ให้ตั้งระยะเวลาเก็บเป็นไม่กี่วันที่สมเหตุสมผล

ไฟล์ WAL ของ PostgreSQL พร้อมสล็อตทำสำเนาที่ถูกลืม สถานการณ์ที่รู้จักน้อยกว่าและอันตรายกว่า: ถ้ามีสล็อตทำสำเนาอยู่แต่ผู้ใช้ของมันไม่เชื่อมต่อมาอีกแล้ว 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 = '%' หมายถึง «เชื่อมต่อจากทุกที่» และถ้าพอร์ตก็เปิดอยู่ด้วย ระหว่างฐานข้อมูลกับอินเทอร์เน็ตก็มีเพียงรหัสผ่านเดียวกั้นอยู่

การสำรองข้อมูล

เรื่องสุดท้าย การมีไฟล์สำรองข้อมูลไม่ได้รับประกันอะไร สิ่งที่รับประกันคือการกู้คืนที่ผ่านการทดสอบแล้วเท่านั้น สำเนาที่ไม่เคยถูกกู้คืนมีโอกาสพอ ๆ กันที่จะขาดกลางคัน ถูกถ่ายมาพร้อมความผิดพลาดของล็อก หรือมีฐานข้อมูลผิดตัวอยู่ข้างใน และควรตรวจล่วงหน้าบนเครื่องอื่น ไม่ใช่ในวันที่ต้องใช้สำเนานั้น

สำหรับการเฝ้าดูประจำวัน ตัวเลขสามตัวก็เพียงพอ: จำนวนการเชื่อมต่อ จำนวนคิวรีที่ช้าต่อวัน และขนาดของฐานข้อมูล ทั้งสามเปลี่ยนแปลงอย่างช้า ๆ และคาดการณ์ได้ และการขยับอย่างรวดเร็วของตัวใดตัวหนึ่งก็ควรค่าแก่การเหลือบมอง ภาพบนหน้าเดียวเป็นอย่างไรดูได้ที่เดโมด้านล่าง