MoreMeng.in.th

แนวทางการเก็บขนาดฐานข้อมูลเพื่อวิเคราะห์และแสดงผลบน Metabase

แนวทางการเก็บขนาดฐานข้อมูลเพื่อวิเคราะห์และแสดงผลบน Metabase

Thanikul Sriuthis Life of Dev 21 views

1. วัตถุประสงค์และแนวคิด

องค์กรส่วนใหญ่มักมีฐานข้อมูลหลายระบบ เช่น PostgreSQL, MariaDB/MySQL, และ MSSQL ซึ่งกระจายอยู่บนหลายเครื่อง การติดตามขนาดฐานข้อมูลเป็นประจำจะช่วยให้สามารถวางแผนการขยายระบบ, ป้องกันปัญหาพื้นที่เต็ม, และวิเคราะห์แนวโน้มการใช้งานได้อย่างมีประสิทธิภาพ
แนวคิดหลัก:

  • ให้แต่ละเครื่องส่งขนาดฐานข้อมูลของตนเองขึ้น API กลาง
  • ข้อมูลจะถูกเก็บในฐานข้อมูลกลาง
  • นำข้อมูลไปแสดงผลและวิเคราะห์ผ่าน Metabase Dashboard

2. การสร้างตารางเก็บข้อมูล

ก่อนจะรับข้อมูลจาก API ต้องสร้างตารางกลางเพื่อเก็บข้อมูลขนาดฐานข้อมูลที่ส่งเข้ามา ตัวอย่างเช่น

PostgreSQL:

CREATE TABLE db_log_size (
    id        SERIAL PRIMARY KEY,
    host      VARCHAR(100) NOT NULL,
    dbname    VARCHAR(100) NOT NULL,
    size      BIGINT NOT NULL,
    timestamp TIMESTAMP NOT NULL
);

MSSQL:

CREATE TABLE db_log_size (
    id        INT IDENTITY(1,1) PRIMARY KEY,
    host      VARCHAR(100) NOT NULL,
    dbname    VARCHAR(100) NOT NULL,
    size      BIGINT NOT NULL,
    timestamp DATETIME NOT NULL
);

MariaDB/MySQL:

CREATE TABLE db_log_size (
    id        BIGINT AUTO_INCREMENT PRIMARY KEY,
    host      VARCHAR(100) NOT NULL,
    dbname    VARCHAR(100) NOT NULL,
    size      BIGINT NOT NULL,
    timestamp DATETIME NOT NULL
);

หมายเหตุ:

  • ฟิลด์ host สำหรับระบุชื่อเครื่อง
  • ฟิลด์ dbname สำหรับชื่อฐานข้อมูล
  • ฟิลด์ size สำหรับขนาดฐานข้อมูล (หน่วยเป็น bytes)
  • ฟิลด์ timestamp สำหรับเวลาที่บันทึกข้อมูล

3. การสร้าง API สำหรับรับค่าไปเก็บ

เพื่อความปลอดภัยและความยืดหยุ่นในการรับข้อมูลจากหลายเครื่อง ควรสร้าง API กลางสำหรับรับข้อมูลขนาดฐานข้อมูล
เหตุผลที่ใช้ API แทนการดึงข้อมูลโดยตรง:

  • ไม่ต้องเปิดสิทธิ์เข้าถึงฐานข้อมูลจากภายนอก ลดความเสี่ยงด้านความปลอดภัย
  • สามารถควบคุม ตรวจสอบ และ validate ข้อมูลที่รับเข้ามา
  • รองรับการขยายระบบในอนาคต

ตัวอย่าง API (Node.js/Express):

router.post('/push_db_size', async (req, res) => {
  const { host, dbname, size, timestamp } = req.body;
  if (!host || !dbname || !size || !timestamp || size <= 0) {
    return res.status(400).json({ success: false, error: 'Validation failed' });
  }
  await db('db_log_size').insert({ host, dbname, size, timestamp });
  res.json({ success: true });
});
  • API นี้จะรับข้อมูล JSON จากแต่ละเครื่อง แล้วบันทึกลงตาราง db_log_size
  • มีการตรวจสอบความถูกต้องของข้อมูลก่อนบันทึก

4. ฝั่ง Linux: การส่งข้อมูลจาก PostgreSQL และ MariaDB/MySQL

4.1 การสร้าง Shell Script

PostgreSQL:

#!/bin/bash
DBS=("db1" "db2")
for DB in "${DBS[@]}"; do
  SIZE=$(psql -d $DB -t -c "SELECT pg_database_size('$DB');")
  curl -X POST http://api-url/storage/push_db_size \
    -H "Content-Type: application/json" \
    -d "{\"host\":\"$(hostname)\",\"dbname\":\"$DB\",\"size\":$SIZE,\"timestamp\":\"$(date '+%F %T')\"}"
done

MariaDB/MySQL:

#!/bin/bash
DBS=("db1" "db2")
for DB in "${DBS[@]}"; do
  SIZE=$(mysql -N -e "SELECT IFNULL(SUM(data_length+index_length),0) FROM information_schema.tables WHERE table_schema='$DB';")
  curl -X POST http://api-url/storage/push_db_size \
    -H "Content-Type: application/json" \
    -d "{\"host\":\"$(hostname)\",\"dbname\":\"$DB\",\"size\":$SIZE,\"timestamp\":\"$(date '+%F %T')\"}"
done

4.2 การกำหนดสิทธิ์ไฟล์ .pgpass และ my.cnf

เพื่อความปลอดภัยในการเก็บรหัสผ่านสำหรับเชื่อมต่อฐานข้อมูล

  • ไฟล์ .pgpass (Postgres) และ .my.cnf (MySQL) ควรตั้ง permission ให้เฉพาะเจ้าของอ่านได้เท่านั้น
  chmod 600 ~/.pgpass
  chmod 600 ~/.my.cnf
  • หลีกเลี่ยงการใส่รหัสผ่านไว้ในสคริปต์โดยตรง

4.3 การตั้ง crontab

ตั้ง crontab ให้สคริปต์ทำงานอัตโนมัติ เช่น ทุกวันเวลา 21:15

15 21 * * * /path/to/push_db_size.sh

5. ฝั่ง Windows: การส่งข้อมูลจาก MSSQL

5.1 การสร้าง PowerShell Script (.ps1)

$Databases = @("db1", "db2")
foreach ($DbName in $Databases) {
  $Query = "SELECT SUM(size)*8*1024 AS SizeBytes FROM sys.master_files WHERE database_id = DB_ID('$DbName')"
  $DbSize = Invoke-Sqlcmd -ServerInstance "localhost" -Query $Query
  $SizeBytes = [int64]$DbSize.SizeBytes
  $Body = @{
    host = $env:COMPUTERNAME
    dbname = $DbName
    size = $SizeBytes
    timestamp = (Get-Date -Format "yyyy-MM-dd HH:mm:ss")
  } | ConvertTo-Json
  Invoke-RestMethod -Uri "http://api-url/storage/push_db_size" -Method Post -Body $Body -ContentType "application/json"
}

5.2 การกำหนด User Authenticate

  • สามารถใช้ Windows Authentication (ต้องให้ DBA เพิ่มสิทธิ์ user ใน SQL Server)
  • หรือใช้ SQL Authentication โดยระบุ -Username และ -Password ใน Invoke-Sqlcmd
  • ควรเก็บรหัสผ่านอย่างปลอดภัย ไม่ควรเขียนไว้ในสคริปต์โดยตรง

5.3 การตั้ง Task Scheduler

  • เปิด Task Scheduler
  • สร้าง Task ใหม่
  • ตั้งเวลาให้รันสคริปต์อัตโนมัติ เช่น ทุกวันเวลา 21:15
  • ในช่อง Program/script ใส่ powershell
  • ในช่อง Add arguments ใส่
  -ExecutionPolicy Bypass -File "C:\path\to\push_db_size_mssql.ps1"

6. การแสดงผลบน Metabase

  • เชื่อมต่อ Metabase เข้ากับฐานข้อมูลกลางที่มีตาราง db_log_size
  • สร้าง Dashboard เพื่อแสดงขนาดฐานข้อมูลแต่ละเครื่อง/แต่ละฐานข้อมูล
  • สามารถวิเคราะห์แนวโน้มการใช้งาน, ตรวจสอบการเติบโตของข้อมูล, และตั้งแจ้งเตือนเมื่อขนาดฐานข้อมูลเกิน threshold ได้

สรุป

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

Full-Stack Developer, Health Informatician