0019 | สารบัญนั้นสำคัญไฉน… (ว่าด้วย MySQL Index)

ดองไว้ตั้งนานแล้วครับ entry นี้
เคยเกริ่นๆ ไว้ตั้งนานแล้วด้วยแหละ

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

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

อ้อ ลืมไป ยกตัวอย่างไม่ได้ ต้องสร้างตารางก่อน

CREATE TABLE users ( id INT NOT NULL DEFAULT '0' AUTO_INCREMENT, username VARCHAR(24) NOT NULL DEFAULT '', password VARCHAR(32) NOT NULL DEFAULT '', email VARCHAR(64) NOT NULL DEFAULT '', PRIMARY KEY(id) ) Engine=MyISAM;

CREATE TABLE users ( id int NOT NULL DEFAULT '0' auto_increment, username varchar(24) NOT NULL DEFAULT '', password varchar(32) NOT NULL DEFAULT '', email varchar(64) NOT NULL DEFAULT '', PRIMARY KEY(id) ) Engine=MyISAM;

ในการนี้เราจะได้ตาราง users มี 4 field คือ id, username, password และ email
ทีนี้ สมมติเราต้องการดึงค่า email ออกมาจากตาราง โดยหาจาก username ก็ต้อง query แบบนี้

SELECT email FROM users WHERE username = 'xxxxxxx';

SELECT email FROM users WHERE username = 'xxxxxxx';

เงื่อนไขการค้นหาก็จะเป็น ค้นหาจาก ตาราง users โดยดูใน field username ที่มีค่าเท่ากับ ‘xxxxxxx’

กรณีที่เรายังไม่ได้ทำ Index ให้ตารางนี้…
MySQL จะเปิดตารางขึ้นมา(จาก harddisk) จากนั้นก็เริ่มตั้งแต่ record แรกสุด
เรียกข้อมูลใน field username ออกมาเทียบค่า ถ้าไม่ใช่ก็หาต่อไปเรื่อยๆ จนกว่าจะเจอ
ซึ่งกรณีเลวร้ายที่สุด คือค้นหาไม่เจอเลย จะทำให้ MySQL ต้องทำการอ่านข้อมูลจากตารางเยอะมาก
กลายเป็นว่า ต้องอ่านข้อมูลทั้งตารางเพื่อหาว่า user นี้อยู่ที่ record ไหน

สมมติพอทำ Index ปุ๊ป MySQL ก็จะเข้ามาอ่าน Index ที่มีอยู่
ซึ่งโดยปกติ index จะพยายามเก็บในแรมครับ แต่ก็มี file ที่เก็บบน harddisk เหมือนกัน
แล้วก็เทียบค่าไปเรื่อยๆ แต่แรมมันเร็วกว่า hdd เยอะครับ…
จากนั้น พอเจอแล้ว เจ้า index นี่ก็จะเป็นตัวบอกว่า ข้อมูลนี้อยู่ที่ตำแหน่งตรงไหนของแฟ้ม
ก็เลยทำให้ MySQL กระโดดไปเอาข้อมูลมาได้เลยครับ

คำสั่งเพิ่ม index ให้ตารางที่มีอยู่แล้วใช้อย่างนี้ครับ

ALTER TABLE users ADD INDEX (username)

ALTER TABLE users ADD INDEX (username)

.

เดี๋ยวจะมาอธิบายการตรวจสอบ query อีกที ง่วงมากเลยตอนนี้ T_T

Tags: mysql, optimize, query

0014 | MySQL Replicate

* บทความนี้อ้างอิงและประยุกต์มาจากเว็บไซต์ต่อไปนี้
DNSThailand.com – เทคนิคการใช้ Replication ระดับสูงใน MySQL
CATTelecom.com – กิตติชน แม้นสมุทร [ejeepss] » Replicate MySQL 5.x

สืบเนื่องจาก entry ที่แล้ว
ก็เลยลองไปค้นๆ ข้อมูลเพิ่มเติมดูอีกหน่อยว่าเราลืมอะไรไปบ้าง
เพราะนึกได้ว่ายังไม่ได้ลองทำ MySQL Replicate เลย (ฮาๆ)
ประเด็นคือไม่รู้ว่ามันช้าที่ฐานข้อมูลรึเปล่า เพราะ debug ไม่เจอสาเหตุ
ก็เลยต้องลองทำเท่าที่ทำได้ไปก่อน

ขั้นต้นก็ต้องมี server 2 เครื่องขึ้นไปครับ
โดยสมมติเครื่องแรก ชื่อ web-slave
เครื่องที่สอง ชื่อ db-master

ก่อนอื่นมาที่เครื่อง web-slave สั่งติดตั้ง mysql server ตามนี้ครับ
* อ้างอิง Fedora 8 ครอบคลุม Fedora 4 – 9, CentOS 5.x รวมถึงสาย Redhat ทั้งหมดครับ

# yum install mysql-server

เสร็จแล้วเปิดแฟ้ม /etc/my.cnf เพิ่มเติมข้อมูลต่อไปนี้ครับ

server-id=2
master-host=10.0.0.2
master-port=3306
master-user=replicate
master-password=slavepassword
master-connect-retry=30
replicate-wild-do-table= %.%
report-host=10.0.0.1

(มันมาอีกละ สีฟ้าแดงเขียวนี่)
แก้ ip อะไรพวกนี้ให้ถูกนะครับ แล้วแก้ตัวแดงๆ ด้วย ตามที่ตั้งค่า

เครื่องนี้เสร็จและ กลับไปที่เครื่อง db-master ต่อ ก่อนอื่นต้องตั้งค่า username / password ก่อนครับ
โดยเข้าไปใน mysql query console (หรือที่ไหนก็ได้ที่สามารถตั้ง user ได้) แล้วรัน query ต่อไปนี้

mysql> GRANT REPLICATION SLAVE ON *.*
TO ‘replicate‘@’10.0.0.1‘ IDENTIFIED BY ‘slavepassword‘;

เสร็จแล้วออกมาสั่ง stop service mysql ได้เลยครับ แล้วก็แก้แฟ้ม /etc/my.cnf อีกนิดหน่อย ตามนี้ครับ

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
expire_logs_days=3
max_binlog_size=50M

ตรงนี้ปรับค่าตามความเหมาะสมนะครับ แล้วแต่ว่าจะเก็บ log กี่วันยังไง
โดยบรรทัดแรกสุดจะเป็น path + ชื่อแฟ้มที่ใช้เก็บ log (สามารถใส่ full path ได้ครับ เช่น /var/log/mysqld/binlog)
แล้วก็ย้ำว่าควรจะใส่ option สองบรรทัดด้านล่าง เพื่อป้องกัน hdd เต็ม (ฮา)
เคยเจอมาแล้วครับ เก็บ log เพลินไปหน่อย เศร้าเลย

ต่อจากนี้ก็ copy ข้อมูลจาก db-master ไปยังเครื่อง web-slave ครับ
ถ้าใน fedora/centos default install ก็อยู่ใน /var/lib/mysql
สั่ง tar ไปทั้ง folder เลยก็ยังไหวครับ (แต่ข้อแม้คือต้องเป็น mysql version เดียวกันนะ)

จากนั้นก็สั่ง service mysqld start ทั้งสองเครื่อง
แล้ว connect เข้า mysql ของเครื่อง db-master สั่ง query ต่อไปนี้ครับ

mysql> FLUSH TABLES WITH READ LOCK;

แล้วก็ connect เข้า mysql ของเครื่อง web-slave สั่ง query ต่อไปนี้ครับ

mysql> START SLAVE;

เท่านี้ก็เรียบร้อย ทีนี้ก็รอดูผลงาน… ถ้าเวิร์กจะเอาไปใส่เว็บ bitthai ด้วย (ฮาๆ)

Tags: load balance, mysql, optimize, postmunlife, replicate