บีบอัด จัดเรียงข้อมูล และเพิ่มประสิทธิภาพฐานข้อมูล MariaDB/MySQL

ในบทความนี้ เราจะมาสำรวจวิธีการบางอย่างของการบีบอัดตาราง/ฐานข้อมูลและการจัดเรียงข้อมูลใน MySQL/MariaDB ซึ่งจะช่วยให้คุณประหยัดพื้นที่บนดิสก์ที่ฐานข้อมูลตั้งอยู่

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

อีกแง่มุมที่สำคัญของการทำงานของฐานข้อมูลคือความจำเป็นในการจัดเรียงข้อมูลตารางและฐานข้อมูลเป็นครั้งคราวเพื่อปรับปรุงประสิทธิภาพ

การบีบอัดและการเพิ่มประสิทธิภาพตาราง InnoDB

ไฟล์ ibdata1 และ ib_log

โครงการส่วนใหญ่ที่มี InnoDB ตารางมีปัญหา ibdata1 . ขนาดใหญ่ และ ib_log ไฟล์. ในกรณีส่วนใหญ่ เกี่ยวข้องกับการกำหนดค่า MySQL/MariaDB หรือสถาปัตยกรรม DB ที่ไม่ถูกต้อง ข้อมูลทั้งหมดจากตาราง InnoDB ถูกเก็บไว้ในไฟล์ ibdata1 ซึ่งพื้นที่ดังกล่าวจะไม่ถูกเรียกคืนโดยตัวมันเอง ฉันชอบเก็บข้อมูลตารางแยกกัน ibd* ไฟล์. โดยเพิ่มบรรทัดต่อไปนี้ใน my.cnf :

innodb_file_per_table

หรือ

innodb_file_per_table=1

หากเซิร์ฟเวอร์ของคุณได้รับการกำหนดค่าและคุณมีฐานข้อมูลที่มีประสิทธิภาพด้วยตาราง InnoDB ให้ทำดังต่อไปนี้:

1. สำรองข้อมูลฐานข้อมูลทั้งหมดบนเซิร์ฟเวอร์ของคุณ (ยกเว้น mysql และ performance_schema) คุณสามารถรับดัมพ์ฐานข้อมูลโดยใช้คำสั่งนี้: # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. หลังจากสร้างการสำรองฐานข้อมูลแล้ว ให้หยุดเซิร์ฟเวอร์ mysql/mariadb ของคุณ
3. เปลี่ยนการตั้งค่าใน my.cfg;
4. ลบ ibdata1 และ ib_log ไฟล์;
5. เริ่ม mysql/mariadb daemon;
6. กู้คืนฐานข้อมูลทั้งหมดจากข้อมูลสำรอง:# mysql -u [username] –p[password] [database_name] < [dump_file.sql]

หลังจากดำเนินการแล้ว ตาราง InnoDB ทั้งหมดจะถูกจัดเก็บไว้ในไฟล์แยกกัน และ ibdata1 จะหยุดเติบโตแบบทวีคูณ

การบีบอัดตาราง InnoDB

คุณสามารถบีบอัดตารางด้วยข้อมูลข้อความ/BLOB และประหยัดพื้นที่ดิสก์ได้ค่อนข้างมาก

ฉันมีฐานข้อมูล innodb_test ที่มีตารางที่สามารถบีบอัดได้ ดังนั้นฉันจึงสามารถเพิ่มพื้นที่ว่างในดิสก์ได้ ก่อนดำเนินการใดๆ ผมแนะนำให้สำรองฐานข้อมูลทั้งหมด เชื่อมต่อกับเซิร์ฟเวอร์ mysql:

# mysql -u root -p

เลือกฐานข้อมูลที่คุณต้องการในคอนโซล mysql:

# use innodb_test;

หากต้องการแสดงรายการตารางและขนาดตาราง ให้ใช้แบบสอบถามต่อไปนี้:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;

โดยที่ innodb_test คือชื่อฐานข้อมูลของคุณ

บางตารางอาจถูกบีบอัด มาดู b_crm_event_relations . กัน ตารางเป็นตัวอย่าง เรียกใช้แบบสอบถามนี้:

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

หลังจากรันแล้วจะเห็นว่าขนาดของตารางลดลงจาก 26 MB เป็น 11 MB เนื่องจากการบีบอัด

การบีบอัดตารางจะช่วยประหยัดพื้นที่ดิสก์บนโฮสต์ของคุณได้มาก อย่างไรก็ตาม เมื่อทำงานกับตารางที่บีบอัด ภาระของ CPU จะเพิ่มขึ้น ใช้การบีบอัดสำหรับตาราง db หากคุณไม่มีปัญหากับทรัพยากรของ CPU แต่มีปัญหาพื้นที่ดิสก์

การบีบอัดตาราง MyISAM ใน MySQL/MariDB

เพื่อบีบอัด Myisam ตาราง ใช้แบบสอบถามพิเศษในคอนโซลเซิร์ฟเวอร์แทนคอนโซล mysql ในการบีบอัดตาราง ให้ทำดังนี้:

# myisampack -b /var/lib/mysql/test/modx_session

โดยที่ /var/lib/mysql/test/modx_session คือเส้นทางไปยังตารางของคุณ น่าเสียดายที่ฉันไม่มีโต๊ะใหญ่และต้องบีบอัดโต๊ะเล็ก ๆ แต่ก็ยังเห็นผล (ไฟล์ถูกบีบอัดจาก 25 MB เป็น 18 MB):

# du -sh modx_session.MYD

25 ล้าน modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

กำลังบีบอัด /var/lib/mysql/test/modx_session.MYD:(4933 ระเบียน)- กำลังคำนวณสถิติ- กำลังบีบอัดไฟล์29.84%อย่าลืมเรียกใช้ myisamchk -rq บนโต๊ะที่บีบอัด

# du -sh modx_session.MYD

18M modx_session.MYD

ฉันใช้ -b ที่สำคัญในคำสั่ง เมื่อคุณเพิ่ม ตารางจะได้รับการสำรองข้อมูลก่อนการบีบอัดและทำเครื่องหมายด้วยป้ายกำกับ OLD:

# ls -la modx_session.OLD

-rw-r ----- 1 mysql mysql 25550000 17 ธันวาคม 15:20 modx_session.OLD

# du -sh modx_session.OLD

25M modx_session.OLD

การเพิ่มประสิทธิภาพตารางและฐานข้อมูลใน MySQL และ MariaDB

ในการเพิ่มประสิทธิภาพตารางและฐานข้อมูล ขอแนะนำให้จัดเรียงข้อมูลเหล่านี้ ตรวจสอบว่ามีตารางใดในฐานข้อมูลที่ต้องการการจัดเรียงข้อมูล

เปิดคอนโซล MySQL เลือกฐานข้อมูลและเรียกใช้แบบสอบถามนี้:

select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;

ดังนั้น คุณจะแสดงตารางทั้งหมดที่มีพื้นที่ว่างที่ไม่ได้ใช้อย่างน้อย 50 MB:

data_length_mb — ขนาดโต๊ะรวม

data_free_mb — พื้นที่ว่างในตาราง

นี่คือตารางที่เราจัดเรียงข้อมูลได้ ตรวจสอบพื้นที่ว่างบนดิสก์:

# ls -lh /var/lib/mysql/innodb_test/ | grep b_

-rw-r----- 1 mysql mysql 402M 17 ต.ค. 12:12 b_disk_deleted_log_v2.MYD-rw-r----- 1 mysql mysql 828M 17 ต.ค. 13:23 b_crm_timeline_bind.MYD-rw-r- ---- 1 mysql mysql 981M 17 ต.ค. 11:54 b_disk_object_path.MYD

ในการเพิ่มประสิทธิภาพตารางเหล่านี้ ให้รันคำสั่งต่อไปนี้ในคอนโซล mysql:

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

หลังจากการจัดเรียงข้อมูลสำเร็จ คุณจะเห็นผลลัพธ์ดังนี้:

อย่างที่คุณเห็น data_free_mb เท่ากับ 0 ตอนนี้ และขนาดตารางลดลงอย่างมาก (3 – 4 เท่า)

คุณยังสามารถเรียกใช้การจัดเรียงข้อมูลโดยใช้ mysqlcheck ในคอนโซลเซิร์ฟเวอร์ของคุณ:

# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file

โดยที่ innodb_test เป็นฐานข้อมูลของคุณ
และ b_workflow_file เป็นชื่อโต๊ะ

ในการเพิ่มประสิทธิภาพตารางทั้งหมดในฐานข้อมูล ให้รันคำสั่งนี้ในคอนโซลเซิร์ฟเวอร์ของคุณ:
# mysqlcheck -o innodb_test -u root -p

โดยที่ innodb_test เป็นชื่อฐานข้อมูล

หรือเรียกใช้การเพิ่มประสิทธิภาพของฐานข้อมูลทั้งหมดบนเซิร์ฟเวอร์:

# mysqlcheck -o --all-databases -u root -p

หากคุณตรวจสอบขนาดฐานข้อมูลก่อนและหลังการปรับให้เหมาะสม คุณจะเห็นว่าขนาดรวมลดลง:

# du -sh

2.5G

# mysqlcheck -o innodb_test -u root -p

innodb_test.b_admin_notifynote :ตารางไม่รองรับการเพิ่มประสิทธิภาพ กำลังสร้างใหม่ + วิเคราะห์สถานะแทน :OKinnodb_test.b_admin_notify_langnote :ตารางไม่รองรับการเพิ่มประสิทธิภาพ กำลังสร้างใหม่ + วิเคราะห์แทนสถานะ :OKinnodb_test.b_adv_bannernote :ตารางไม่รองรับการเพิ่มประสิทธิภาพแทน ทำการสร้างใหม่ + สถานะ :ตกลง

# du -sh

1.7G

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