เทคนิค Database Performance Tuning

[teacher_a]

เทคนิค Database  Performance Tuning


ในการทำงานเกี่ยวกับฐานข้อมูลไม่ว่าที่ใดก็ตามย่อมต้องการความรวดเร็วในการตอบสนองมากที่สุด ยิ่งฐานข้อมูลมีจำนวนข้อมูลมาก การที่จะทำงานกับข้อมูลนั้นก็จะใช้เวลานานมากขึ้นทั้งในเรื่องการดึงและการจัดการข้อมูล จึงเป็นสาเหตุหลักที่ทำให้มีวิธีการปรับแต่งฐานข้อมูลเพื่อที่จะแก้ไขปัญหาเหล่านี้ โดยใช้ต้นทุนน้อยที่สุดแต่ะเกิดผลลัพธ์มากที่สุด
ทำไมต้องมีการทำ Performance Tuning
โดยส่วนใหญ่แล้วจะมีอยู่ 3 กรณี ได้แก่
   1. เมื่อต้องการให้ระบบมีความเร็วในการ Access ข้อมูลภายใน Database เพิ่มขึ้น
   2. เมื่อต้องการให้ระบบสามารถรับ Load ของ Request ที่เข้ามาได้มากขึ้น ตลอดจนถึง Connection ด้วย
   3. เมื่อต้องการให้ระบบมีความเสถียรภาพที่ดีขึ้น ไม่ Hang กรณีเจอ Peak load[/li][/list]

สาเหตุที่เกิดปัญหา Design Database ไม่ดี หรือดีเกินไป
ในการออกแบบฐานข้อมูล จะมีการ Normalize Table แตกตารางเป็นหลายตาราง ให้มีความซ้ำซ้อนน้อยที่สุด เพื่อให้สะดวกต่อการ Insert, Update, Delete แต่เมื่อเราต้องการดึงข้อมูล (Select) จากตารางต่าง ๆ ขึ้นมาใช้ ทำให้ต้อง Join, Subquery หรือแม้กระทั่ง Union จากหลายตารางมากเกินไป เพื่อให้ได้ผลลัพท์ที่ต้องการ ซึ่งทำให้ช้าเกินกว่าที่รับได้ ถ้าข้อมูลมีจำนวน Record มากขึ้นเกินระดับหนึ่ง

Application Tuning
สาเหตุอันดับหนึ่งของความช้า เนื่องมากจากการเขียน SQL ไม่ถูกต้อง ทำให้เกิดการ Full Table Scan (ไล่ดูข้อมูลทั้งตารางทีละ Record ตั้งแต่ 1 ไปถึง 100), การจัดเรียงข้อมูล (Sorting) เพื่อ Matching หรือแสดงผลทุกครั้งที่ Query, Join หลาย Table เกินไป, ดึงข้อมูลมากเกินกว่าที่จะแสดงผลให้ User ดูได้ในคราวเดียว, Caching, การใช้ Like, การใช้ OR, Union, Order by, Distinct, Group by, Implicit Cast ที่ไม่เหมาะสม, ไม่ใช้ Bind Variable

Memory Tuning
ระบบจัดการฐานข้อมูล (Database Management System : DBMS) ต้องการหน่วยความจำ (Memory) ที่เพียงพอ เพื่อใช้งานการทำงานต่าง ๆ เช่น shared_pool, buffer cache, log buffer ตัวอย่างเช่นเราสามารถดูอัตรา Buffer Hit Ratio ถ้าค่ายิ่งมากยิ่งเร็ว แสดงว่าข้อมูลที่เราต้องการถูก Load อยู่บน Memory แล้วไม่ต้องไปหาจากใน Harddisk ใหม่ แต่ถ้าค่าน้อยแสดงว่า Memory น้อยเกินไป หรือมีปัญหาเรื่องการ Design อื่น ๆ ทำให้ DBMS ไม่สามารถใช้ข้อมูลที่อยู่บน Memory ได้ต้องไปดึงจาก Harddisk มาใหม่

I/O Tuning
ระบบฐานข้อมูลไม่สามารถทำงานได้เร็ว ถ้าข้อมูลอยู่บน Disk ที่ช้า หรือ Network ที่ช้า และควรมีการบริหารจัดการ Data File, Log File, Temp File และ Block Size ที่เหมาะสม

Eliminate Database Contention
ใช้ Tools ตรวจหา Lock, Latch และ Wait ภายใน Database และกำหนด Schedule Batch Job ให้ไม่กระทบกับงานขณะ Peak Working Hours.

OS Tuning
ตรวจสอบ และ Tune OS, CPU, I/O, Memory Utilization โดยประสานงานกับผู้ดูแลระบบ และศึกษาเกี่ยวกับ OS Platform ที่ใช้อยู่ เช่น Windows, Linux, Unix, Solaris

ประโยชน์จากการทำ Performance Tuning
ทำให้มีการเรียกใช้ทรัพยากรของระบบได้สูงสุดและมีประสิทธิภาพมากยิ่งขึ้น สามารถเข้าถึงข้อมูลได้อย่างรวดเร็วมากยิ่งขึ้น หากมีการ tuning ที่ดีแล้ว อาจไม่จำเป็นที่จะต้องปรับเปลี่ยน Hardware ภายนอกเลย ทำให้ลดต้นทุนค่าใช้จ่ายได้อีกด้วย
 
แนวทางการปรับแต่ง การ tuning database นั้นมี 2 แนวทางใหญ่ๆเลยคือ
Hardware Tuning
การปรับแต่งทางด้านอุปกรณ์ Hardware ของระบบ เช่น การเปลี่ยน CPU หรือ RAM เป็นต้น เป็นวิธีที่ง่ายที่สุดและเป็นวิธีที่ต้องเสียเงินเยอะกว่าวิธีอื่นเช่นกัน ถ้าหากมีการออกแบบระบบฐานข้อมูลมาไม่ดี การปรับเปลี่ยน Hardware ใหม่นอกจากจะสิ้นเปลืองค่าใช้จ่ายแล้วอาจไม่สามารถช่วยอะไรเลยได้เช่นกัน

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

ที่มา - http://www.mediafire.com/?5viq1mk0cczzzfu

[teacher_a]

เทคนิคอื่นๆในการทำ Performance Tuning

1) ใช้ Normalization เพื่อ Optimize Transactional Process การทำ Normalization นั้น จะช่วยในมุมการใช้คำสั่ง Insert, Update, Delete  ทำให้เกิด Performance ที่ดี เพราะจะช่วยขจัดปัญหา เรื่องการซ้ำซ้อนของข้อมูล และลดโอกาสที่จะเกิดการผิดพลาดในการประมวลผลข้อมูลในตาราง แต่จะมีผลตรงกันข้ามกับคำสั่ง Select เพราะว่าใน Database หลายๆ ตัวที่อาจจะพบว่า Query เร็ว แต่ Insert ช้าก็ได้


 
2) แยกไฟล์ MDF และ LDF ไว้กันคนละ physical disk โดยคำสั่ง Insert, Update, Delete จะกระทำกับไฟล์ LDF เป็นหลัก ทั้งนี้ในงาน multi-user ขนาดใหญ่แล้วจะเกิด load read-write อยู่บน physical disk ก้อนเดียวกัน ถ้าเป็นไปได้ก็ควรจะแยกไฟล์ MDF และ LDF ไว้คนละ physical disk ก็จะทำให้เกิดการแบ่ง load read-write ไปไว้คนละ physical disk


 
3) แยก tempdb ไว้บน harddisk ก้อนใหม่ หลักการจะเหมือนกับการแยกไฟล์ MDF และ LDF ครับ คือการแยก load แต่ tempdb จะเป็น temp สำหรับ database ทุกก้อนที่อยู่ใน Server  tempdb จะรับงานหนักมาก ถ้าเป็นไปได้ก็ควรแยก tempdb ไว้คนบน physical disk ต่างหากได้จะดีมาก


 
4) สำหรับ table ขนาดใหญ่ให้ใช้ Table partition สาเหตุหลักของการทำ partitioning มากจาก tables ที่มีขนาดใหญ่ (very large tables and indexes) โดยทำการ แยกส่วนออกมาเป็นส่วนย่อยๆ เรียกว่า partitions     มองขนาดจากใหญ่ไปเล็กจะเป็นดังนี้  Tables->Partitions->Rowsโดยสามารถแบ่ง ข้อมูลออกเป็น range ใช้ข้อมูลใน data เช่น ปี เป็นต้น
ตัวอย่าง
table Customer มีพนักงานปี 2008-2010  จาก Table Customer จะประกอบด้วย Partition คือ customer_2008, customer_2009, customer_2010 เป็นต้น โดยสามารถ Query โดยระบุว่าจะใช้  Patitioning ไหนเพื่อเพิ่มความเร็วในการ Query หรือ ระบุว่าใช้ Table customer เลยก็ได้ การทำ table partition เป็นการแยก load ทาง horizontal โดยจะทำได้ตั้งแต่ sql server 2005 ขึ้นไป เป็นวิธีการกระจาย load ที่ฉลาดมากที่สุดวิธีหนึ่ง โดยสามารถกำหนดเงื่อนไขในการแบ่ง table เช่น แบ่งตามปีที่ซื้อสินค้า, แบ่งตามแผนก เป็นต้น 
ข้อจำกัดในการใช้ Table partition คือ

   1. Data Type ที่ใช้สำหรับทำเป็นเงื่อนไขใน Partition Function ต่อไปนี้จะใช้ไม่ได้ครับ เช่น text, ntext, image, xml, varbinary(max), varchar(max) และ Data type ที่สร้างมาจาก CLR
   2. จำนวน partition สูงสุดที่สร้างได้ก็คือ สามารถสร้างได้ไม่เกิน 1,000 partition (โดยส่วนตัวก็ไม่เคยใช้งานถึงครับ)
   3. จำนวน boundary ที่สร้างได้สูงสุดไม่เกิน 999


 
5) สำหรับ database ขนาดใหญ่ให้ใช้วิธีการแยกไฟล์ออกเป็นหลายๆ ไฟล์ โดยใช้ File Group หลักการจะเหมือนกับการทำ partition table แต่เป็นระดับ Database แทน โดยการแยก table กระจายไปแต่ละ physical disk แต่วิธีการแยกเราจะใช้ file group เป็นเหมือนการแบ่งกลุ่มให้กับข้อมูล ซึ่งสามารถทำได้ทุกเวอร์ชั่นของ SQL Server


 
6) เมื่อใช้ Data type แบบ TEXT, NTEXT, IMAGE ให้แยกเก็บต่างหากจาก Table Storage โดยปกติแล้วการเก็บข้อมูลของ column ที่มี data type เป็น text, ntext, image นั้นจะเก็บอยู่ใน page เดียวกับ column อื่นๆ เพราะฉะนั้น เวลาใช้คำสั่ง select ก็จะทำให้ sql server ไปอ่าน column เหล่านี้ขึ้นมาด้วย ซึ่งถือว่าเป็น column ที่พิเศษ ดังนันจึงควรจะทำการแยก column ที่เป็น text, ntext, image แยกออกมาต่างหาก เพื่อเพิ่ม performance โดยใช้คำสั่ง
EXEC sp_tableoption 'orders', 'text in row', 'ON'

7) พยายามใช้ constraint ที่เป็น built-in เช่น primary key, foreign key, check, default, unique แทนการเขียน trigger ขึ้นมาเอง contraint ต่างๆ ที่มีอยู่แล้ว ทาง microsoft ได้ทำการทดสอบเรื่อง performance มาเป็นอย่างดีอยู่แล้ว พยายามใช้ built-in เป็นหลักเพื่อให้ได้ performance ที่ดี แต่ถ้า built-in ที่มีอยู่ ไม่สามารถตอบสนองสิ่งที่เราต้องการได้จริงๆ ถืงจะเริ่มนึกถึงการเขียน trigger ขึ้นมาใช้งานเองอีกทีนึง

8 ) แยก Index ไว้คนละ Physical Disk โดยใช้ File Group การสร้าง index จะทำให้การ access data ทำได้เร็ว แต่ก็ควรที่จะแยก index ไว้คนละ physical disk โดยใช้ file group ได้เหมือนกัน

9) ใช้ Disk RAID เพื่อเพิ่ม Performance ถ้ามีงบประมาณมากหน่อย ก็อาจจะนึกถึงการซื้อ Hardware RAID Disk ซึ่งจะได้ performance ที่ดีแน่นอน



ที่มา - http://onzenzz.blogspot.com/2012/02/performance-tuning_8176.html

[teacher_a]

การสร้าง Partitioning Table บน MS Sql Server 2005

ว่าด้วยเรื่องการจัดเก็บข้อมูลบนฐานข้อมูล MS-SQL Sever 2005 ในกรณีที่องค์กรของท่าน ๆ ทั้งหลาย มีข้อมูลเป็นจำนวนมากเก็บสุม ๆ ไว้เป็นเวลานาน ไฟล์บน Database ที่แบ่งแค่ Data file กับ Log file ตาม default ของการติดตั้ง โตขึ้น ๆ ทุกวัน จน Database Server เริ่มอืด

MS SQL 2005 มี feature ให้เล่น ซึ่งผมคิดว่ามีประโยชน์มาก ๆ นั่นก็คือการจัดสรรค์ข้อมูลลงไฟล์แบบกระจายโดยอัตโนมัติ หรือเรียกว่าการทำ Partioning ครับ หลักการโดยทั่วไปก็คือ การกระจาย Record ออกในแนวราบ ลง File group หลาย ๆ ไฟล์ ที่เราสร้างไว้รองรับโดยอัตโนมัติ รับรองว่าข้อมูลที่เคยมีมากมายมหาศาล ข้อมูลที่ Access แต่ละทีอืดมากมาย จะเร็วขึ้นทันตาเห็น โดยไม่ต้องทำการเพิ่มทรัพยากรด้านฮาร์ดแวร์

ขั้นตอนและวิธ๊การ ขอยกตัวอย่างจาก Adventureworks แล้วกันนะครับ

-- ทำการสร้าง partitioning functions.
-- ตัวอย่างนี้จะทำการสร้าง Partition function แยกส่วนของข้อมูลเป็น 2 ช่วงคือ ข้อมูลก่อนและหลังวันที่ '2005-01-01'

create partition function YearPF(datetime)
as range right for values ('20050101');

-- ต่อมาต้องทำการเพิ่ม file group ลงไปใน database ใครถนัด GUI ก็ใช้ GUI ได้นะครับ case นี้ใช้เป็น Command Line ดังนี้
alter database AdventureWorks add filegroup YearFG1;
alter database AdventureWorks add filegroup YearFG2;

-- ต่อมาก็ต้องทำการ add file ลงแต่ละ file group
alter database AdventureWorks add file (name = 'YearF1', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF1.ndf') to filegroup YearFG1;
alter database AdventureWorks add file (name = 'YearF2', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF2.ndf') to filegroup YearFG2;

-- ต่อมาก็ต้องทำการเชื่อม file group เข้ากับ partition function โดยใช้ Partitioning Scheme
create partition scheme YearPS
as partition YearPF to (YearFG1, YearFG2)

-- สุดท้ายก็ทำการสร้าง Table โดยอ้างอิงถึง Partition Scheme ที่เราทำการสร้างไว้ ข้อมูลก็จะถูกกระจายโดยอัตโนมัติ
create table PartitionedOrders
(
Id int not null identity(1,1),
DueDate DateTime not null,
) on YearPS(DueDate) --ข้อมูลจะถูกแบ่งลงแต่ละ file group โดยใช้ field 'DueDate' เป็นเงื่อนไขในการกระจาย Data

-- ทดสอบการ insert ข้อมูล
insert into PartitionedOrders values('2010-09-01')
insert into PartitionedOrders values('1900-02-07')
insert into PartitionedOrders values(getdate())
insert into PartitionedOrders values('1998-10-30')
insert into PartitionedOrders values('2005-01-01')

-- ทีนี้เรามาตรวจสอบดูว่าข้อมูลแต่ละ Record ไปหล่นใส่ partition ไหนบ้างด้วยคำสั่งนี้เลยครับ
select *, $partition.YearPF(DueDate) from PartitionedOrders

-- และสามารถดูจำนวน Partition ที่สร้างไว้ด้วยคำสั่งนี้ครับ
select * from sys.partitions where object_id = object_id('PartitionedOrders')

จากการใช้งานที่ผ่านมาสรุปว่าการ ทำ Partition Table มีประโยชน์มาก ๆ ครับ เหมาะกับฐานข้อมูลที่มี Data เยอะไฟล์บวม Partition Table ช่วยท่านได้ แต่ถ้าท่านมี Storage ที่สามารถแยก แต่ละ Partition ลงฮาร์ดดิสก์คนละก้อนได้ละก็ ประสิทธิภาพของ Database โดยรวมจะเพิ่มขึ้นแบบเห็น ๆ โดยไม่ต้องลงทุนเปลี่ยน Database Server ใหม่ครับ

ที่มา - http://think2solution.blogspot.com/2010/09/partitioning-table-ms-sql-server-2005.html

[AnhViars]

แต่อย่างลืมนะครับว่า เครื่องก็ต้องดีด้วย

[Haibaraichi]

เข้าใจง่ายข้อมูลละเอียดดีครับ