راهكارهايي براي‌ افزايش سرعت در بانك‌هاي اطلاعاتي SQL Server

[ملیساا 5015]

اشاره :

شايد بعضي از شما تاكنون دست‌اندركار يكي دو پروژه مبتني بر بانك‌هاي اطلاعاتي بوده‌ايد و يا اكنون با چنين پروژه‌هايي سروكار داريد. اگر تجربه كار در محيط‌هاي متوسط (مثلاً با يكصد كاربر) يا بزرگ‌ را نيز داشته باشيد، قطعاً با مسائل و مشكلات مربوط به كاهش سرعت ناشي از افزايش تعداد كاربران يا حجم پردازشي آن‌ها مواجه شده‌ايد.

اين مقاله با استناد به منابع مايكروسافتي، راهكارهايي را براي بهبود سرعت و كارايي سيستم در بانك‌هاي اطلاعاتي با تعداد كاربر و حجم پردازش زياد مورد بررسي قرار مي‌دهد. شايان ذكر است كه در تمامي نمونه‌هاي مورد اشاره، بانك‌هاي اطلاعاتي مبتني بر محصول مايكروسافت يعني SQL Server2000 مدنظر قرار گرفته است.

طبق بررسي‌هايي كه كارشناسان مايكروسافت انجام داده‌اند، كارايي يك سيستم بانك اطلاعاتي به پنج عامل مختلف بستگي دارد كه به ترتيب اهميت عبارتند از: برنامه نوشته شده، پايگاه داده موردنظر، سخت‌افزار سرور يا كلاينت، تنظيمات و نسخه مورد استفاده SQL Server و سيستم‌عامل ويندوز. همان‌طور كه حتماً مي‌بينيد، ساختار پايگاه داده، براي كارايي سيستم، در رتبه دوم اهميت قرار‌دارد. بنابراين ايجاب مي‌كند كه در زمان تحليل و طراحي سيستم، به‌صورت ويژه‌ به بانك اطلاعاتي در‌حال ساخت توجه شود و رابطه بين اين بانك و برنامه‌هاي كاربردي و همچنين رابطه بين اجزاي مختلف درون بانك، به بهترين شكل ممكن طراحي و پياده‌سازي شود.

 

 

توسعه

به‌طور كلي براي افزايش سرعت يك بانك اطلاعاتي مي‌توان به دو روش اقدام كرد. در واقع پنج عامل مورد اشاره در بالا‌، به دو دسته طولي و عرضي تقسيم‌بندي مي‌شوند. در توسعه طولي كه در اصطلاح انگليسي به Scalp up نيز شناخته مي‌شود، مدير سيستم با صرف هزينه‌، به ارتقاي سخت‌افزار (مثل پردازنده‌ها يا هاردديسك‌ها) يا به‌طوركلي ايجاد شبكه‌اي سريع‌تر اقدام مي‌نمايد يا مثلاً سيستم‌عامل خود را به نسخه‌اي جديدتر و پايدارتر ارتقا مي‌دهد. اما در روش عرضي (Scale out) تقريباً با حفظ همان سخت‌افزار و ساختار شبكه، به بهينه‌سازي روابط موجود ميان عناصر دخيل در سرعت مثل برنامه‌هاي كاربردي، بانك اطلاعاتي و سرور اقدام مي‌كند.

 

توسعه طولي (Scale up)

هدف اين مقاله پرداختن به توسعه عرضي براي بهره‌برداري بهينه از امكانات موجود است. اما قبل از آن، جادارد به‌صورت خلا‌صه و فهرست‌وار به توسعه طولي و راه‌حل‌هاي آن نيز پرداخته شود تا زمينه براي بررسي‌هاي بيشتر در آينده فراهم گردد.

 

راه‌حل يكم: افزايش حافظه مورد استفاده SQL Server از يك به سه گيگابايت. اين كار را بايد با دستكاري در فايلBoot.ini سرور 2000 يا 2003 كه SQL Server در آنجا قرار دارد، انجام دهيد. براي اطلاع از چگونگي انجام‌دادن اين كار، به سايت پشتيباني مايكروسافت رجوع كنيد نشاني(

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

) و در آنجا عبارت AWE SQLServer را جستجو كنيد تا مقالاتي كه در اين زمينه وجود دارد، در دسترس شما قرار گيرد.

 

راه‌حل دوم: ارتقاي سيستم‌عامل ويندوز 2000 به 2003 كه در فرايند caching، سيستم‌عاملي پايدارتر و هوشمندتر قلمداد مي‌شود.

 

راه‌حل سوم: استفاده از پردازنده‌هاي Xeon به جاي پنتيوم 4 در سرور. اين پردازنده‌ها به دليل ويژگيhyper threading، مي‌توانند سرعت پردازش اطلاعات در سمت سرور را به دو برابر افزايش دهند.

 

راه‌حل چهارم: هاردديسك‌هاي اسكازي با 15‌هزار دور در دقيقه و سرعت سه مگابيت در ثانيه و يا Sata با 10‌هزار دور در دقيقه و دو مگابيت در ثانيه نسبت به هاردديسك‌هاي IDE با 7500 دور در دقيقه و يك مگابيت در ثانيه از عملكرد بهتري برخوردارند.پس درصورت امكان، از اين ادوات ذخيره‌سازي در سرور بانك اطلا‌عاتي استفاده كنيد.

 

راه‌حل پنجم: جداسازي محل ذخيره فايل‌هاي داده‌اي بانك اطلاعاتي (mdf) و فايل‌هاي لاگ (ldf) برروي دو هاردديسك مختلف يا دو ديسك مختلف از يك RAID. معمولاً براي نگهداري mdf استفاده از RAID1 و براي ldf استفاده از RAID5 توصيه مي‌شود.

 

با جداسازي اين فايل‌ها از يكديگر، عمل ايجاد لاگ، وقفه‌اي در خواندن و نوشتن اطلاعات بر روي هاردديسكي كه حاوي فايل‌هاي داده‌اي mdf است، ايجاد نمي‌كند.

 

راه‌حل ششم: راه‌حل آخر و در واقع مشكل‌ترين راه، تقسيم بانك اطلاعاتي (در صورت لزوم) به دو بانك جدا از هم و بر روي دو سرور مختلف است. به عنوان مثال، فرض كنيد كه عمليات روزانه سيستم شما به دو دسته تقسيم مي‌شود: دسته يكم عملياتي است كه طي آن بايد از آخرين اطلاعات موجود بر روي سيستم استفاده شود و هرگونه تغيير نيز بايد فوراً در همان لحظه بر روي بانك سيستم‌ها (جداول مربوط به آن‌ها كه به

online transactional Processing) OLTP) مشهورند،) اعمال شود.

 

دسته دوم نيز شامل عملياتي است كه طي آن مي‌توان از اطلاعات چند ساعت يا چند روز پيش نيز استفاده كرد و لزومي به داشتن آخرين اطلاعات به صورت لحظه‌اي نيست. به عنوان نمونه فرض كنيد تعدادي از گزارش‌هاي سيستم مربوط به تحليل آماري فرايندهاي مختلف ماه پيش است. بنابراين بايد تمهيداتي انديشيده شود تا تهيه اين گزارش‌ها -كه البته ارزش آني ندارند، اما به دليل بازه زماني و نوع تحليل آن‌ها، منابع زيادي از سيستم براي خواندن اطلاعات انبوه و تجزيه و تحليل صرف مي‌شود، بايد بر روي سرور دومي در شبكه كه به

سيستم‌هاي online Analytical Processing) OLAP) مشهورند قرار گيرند تا در كار كساني كه مشغول كار با OLTP هستند، خللي ايجاد نشود.

 

بنابراين سرور دومي را در شبكه در نظر بگيريد و كپي بانك اطلاعاتي موجود در سرور اول را به سرور دوم انتقال دهيد. سپس با استفاده از روش Replication سيستم را طوري تنظيم كنيد تا در مواقع خلوت‌بودن ترافيك سيستم (مثلاً نيمه شب) اطلاعات Upgrade شده آن روز را از سرور اول به سرور دوم كپي كند. كليه برنامه‌هايي كه با OLAP كار مي‌كنند را به بانك مشابه، اما با آدرس سرور دوم ارجاع دهيد.

 

براي كسب اطلاعات بيشتر در زمينه نحوه انجام‌دادن Replication، عبارت مذكور را در سايت ماهنامه شبكه جستجو كنيد. تا به مقالا‌تي در اين زمينه دست پيدا كنيد.

 

توسعه عرضي (Scale out)

 

انوادگي

 

نام

 

شماره تامين اجتماعي بيمه شده

 

شماره سريال بيمه شده

 

ب

 

الف

 

ايندكس خوشه‌اي يا خاصيت منحصر به فرد

 

كليد اوليه ايندكس غيرخوشه‌اي

 

 

راه‌هاي موجود در توسعه عرضي در واقع سريع‌ترين راه‌حل‌هاي افزايش سرعت در بانك‌هاي اطلاعاتي را تشكيل مي‌دهند. برخي از اين راه‌ها فقط با يك بار استفاده، اثر دايمي خود را روي سيستم به جا مي‌گذارند. اما برخي ديگر بايد به عنوان يك الگوي دوره‌اي در مراحل زماني مناسب ازسوي مدير سيستم اجرا شود. اين راه‌ها در واقع جزئي از دستورالعمل‌هاي نگهداري و پشتيباني سيستم محسوب مي‌شوند. در ادامه به بررسي آن‌ها مي‌پردازيم:

 

1 - از ساخت جداولي كه فاقد كليد اوليه (Primary key) باشند، خودداري كنيد. كليد اوليه علاوه بر جلوگيري از ورود اشتباه اطلاعات از سوي كاربر، به دليل داشتن خاصيت منحصر به‌فرد بودن (Unique) به سريع‌تر پيدا‌شدن ركورد موردنظر از همان جدول كمك شاياني مي‌كند. تا آنجا كه براي سيستم امكان دارد براي كليد اوليه از فيلدهاي عددي استفاده كنيد.

 

استفاده از فيلدهاي رشته‌اي (string) مثلchar ياvarchar به‌عنوان كليد اوليه، كمي كندتر از فيلدهاي عددي است. از انتخاب فيلدهاي رشته‌اي با طول زياد و يا فيلدهايي مثل Memo ،Text و Picture به عنوان كليد اوليه نيز اجتناب كنيد.

 

2 - تمام كليدهاي خارجي (Foreign key) قابل تعريف در بانك را تعريف كنيد. وجود كليدهاي خارجي نيز علاوه بر جلوگيري از اشتباه كاربر در واردكردن يا حذف اطلاعات، موجب مي‌شود هنگام لينك شدن (join) جداول مادر و فرزند از طريق كليدهاي خارجي، سيستم سرعت بيشتري را در انجام دستورات Select شما از خود نشان دهند.

 

3 - همان‌طور كه مي‌دانيد ايندكس‌ها در دو نوع خوشه‌اي (cluster) و غيرخوشه‌اي (Non cluster) قابل ساخت هستند. ايندكس‌ها باعث افزايش سرعت خواندن اطلاعات به‌وسيله دستور Select مي‌شوند.

ما تعريف بي‌رويه آن‌ها در سيستم نيز باعث كاهش سرعت اجراي دستورات فرايندي مثل Insert ،Update و Delete مي‌شود. بنابراين سعي كنيد ايندكس‌هاي ضروري را در سيستم تعريف كنيد. اما در اين راه دست و دلبازي بي‌مورد از خود نشان ندهيد. به عنوان مثال، فرض كنيد در يك شعبه اداره تأمين اجتماعي، جدولي ويژه تعريف بيمه‌شدگان به شكل زير وجود دارد.

 

 

 

 

مبلغ

 

تاريخ

 

شماره سريال

 

1

 

جزء دوم كليد اوليه

 

جزء اول كليد اوليه

 

1

 

 

 

كليد خارجي از جدول قبل

 

1

 

جزئي از ايندكس خوشه اي

 

جزئي از ايندكس خوشه اي

 

 

 

 

جدولي نيز براي نگهداري وجه حق بيمه از بيمه‌شدگان نيز تعريف شده است.

 

همان‌طور كه مشاهده مي‌كنيد، ايندكس نوع خوشه‌اي به فيلدي داده شده كه نسبت به بقيه فيلدها در يك جدول كاربرد بيشتري دارد. چرا كه اين نوع ايندكس نسبت به نوع غيرخوشه‌اي سرعت بيشتري دارد. در ضمن در هر جدول از بانك اطلاعاتي شما فقط قادر به تعريف يك ايندكس خوشه‌اي هستيد كه انتخاب فيلد آن اهميت زيادي دارد. بنابراين لزومي ندارد فيلدي كه كليد اوليه است، حتماً به عنوان ايندكس خوشه‌اي انتخاب شود.

 

نكته مهم ديگر اين است كه لا‌زم است تمام كليدهاي اوليه جداول ايندكس داراي باشند (خوشه‌اي يا غيرخوشه‌اي) نكته ديگر در زمان ساخت ايندكس‌ها فاكتور پرشدن (Fill Factor) آن‌ها است. اين فاكتور در واقع بيانگر ميزان فضاي مياني است كه بايد براي ركوردهايي كه در آينده درج يا حذف مي‌شوند، خالي نگه داشته شود. بنابراين اگر احساس مي‌كنيد جدول شما به‌طور مداوم مورد عمليات حذف و درج (Insert،‌Delete) قرار مي‌گيرد، اين فاكتور را پايين (مثلاً 30 درصد) انتخاب كنيد. اما اگر صرفاً عمليات درج بر روي يك جدول انجام مي‌گيرد و ميزان حذف اطلاعات از آن بسيار كم است، مي‌توانيد اين ميزان را به ارقام بالاتر مثلاً 90 درصد افزايش دهيد. زيرا اين نوع جداول نيازي به داشتن فضاي خالي مياني براي ركوردهايي كه در آينده جانشين ركوردهاي حذف شده مي‌شوند، ندارد.

 

اين مسئله براي ايندكس‌هايي كه برروي ديدها (Indexed Views) ساخته مي‌شوند نيز صادق است. به‌طوركلي گذاشتن ايندكس برروي ديدها به افزايش سرعت آن‌ها كمك مي‌كند. در اين حالت، كليه مطالب مذكور از جمله سياست استفاده از ايندكس‌هاي خوشه‌اي و غيرخوشه‌اي و همچنينFill Factor در جداول، در مورد ديدها نيز عيناً بايد رعايت گردد.

 

4 - در هنگام نوشتن دستورات Select يا در هنگام ساختن ديدها، از استفاده بي‌مورد از پارامترهاي پردازش مثلDistinct و LIKE order by و لينك‌هاي خارجي (Outer join) اجتناب كنيد. در صورت استفاده از اين پارامترها، مطمئن باشيد كه گذاشتن آن‌ها كاملاً ضروري است و چاره ديگري نداريد.

 

5 - از واگذاري پردازش‌هاي رياضي يا آماري سنگين و مداوم به سرور بانك اطلاعاتي بپرهيزيد. مثلا‌ً به دستور زير نگاهي بيندازيد.

SELECT( a*( b+c )) +( d* E+F)) %G/H From ... WHERE ...

 

 

به‌جاي اين‌كار، مي‌توانيد ابتدا با استفاده از يك Select معمولي مثل Select a ,b ,c ,d ,E ,F ,G ,h فيلدهاي موردنظر را در حافظه كلاينت لود كنيد و سپس عمليات رياضي مذكور را در همان جا انجام دهيد. با اين كار پردازشي كه سرور بايد مثلاً براي 50 كلاينت در عرض چند دقيقه انجام دهد، بين آن 50 كلاينت تقسيم مي‌شود و در واقع هر كلاينت فقط سهم پردازشي مربوط به خود را انجام مي‌دهد.

 

6 - گاهي عمل اجتماع بين دو Select توسط دستور Union به شدت بر عملكرد و سرعت سيستم اثر منفي مي‌گذارد. بنابراين در صورت امكان به جاي استفاده از روش مذكور، از روش‌هاي ديگري كه هدفتان را برآورده نمايد، استفاده كنيد.

 

7 - سعي نماييد فيلدهايي كه از نظر مقدار و ارزش با يكديگر مقايسه مي‌شوند، از يك جنس (type) باشند. در غير اين‌صورت سيستم‌مجبور مي‌شود به طور ضمني، عمل تبديل داده را انجام دهد كه كمي برايش وقت‌گير است. به مثال زير توجه كنيد و فرض بگيريد فيلد customer ID در جدول customers از جنس nchar تعريف شده است.

 

Declare@custID char (5)

Set @ CustID =' FDLKO'

Select * From Customers where customerID=@custID

8 - تاحد ممكن از به كار بردن توابع (چه پيش ساخته توسط SQL Server و چه ساخته شده توسط كاربر) در قسمت WHERE يا order by اجتناب كنيد. مثال زير نمونه‌اي از اين مورد است:

 

 

Select * Form orders Where DateAdd (Day, 15, orderdata) = '2005/23/07'

9 - در زمان نوشتن تريگر (trigger) بر روي جداول يك بانك اطلاعاتي، از نوشتن تعداد زيادي دستورالعمل در آن‌ها خودداري كنيد. به عبارت ديگر تريگرها را تا حد امكان كوتاه كنيد و دستورالعمل‌ پياد‌ه‌سازي آن‌ها را كم نماييد.

10 - در زمان ساخت كرسر (cursor) درون توابع، روال‌ها و تريگرها از پارامترهاي Forward only يا read only و همچنين local استفاده كنيد تا SQL Server با دانستن اين نكته كه شما قصد تغيير داده‌ها در كرسر موردنظر را نداريد، تغيير يافتني بودن آن‌ها را درنظر نگيرد و آن را براي شما سريع‌تر بسازد.

 

11 - در صورتي كه تكه‌اي از برنامه شما به ساخت يك جدول موقت (temporary table) نياز دارد، اين كار بايد با ظرافت خاصي صورت بگيرد. اصولا SQL Server براي اجتناب برنامه‌نويسان از ساخت جداول موقت، از يك نوع داده(Data type) خاص به نام Table پشتيباني مي‌كند كه مزيت استفاده از آن اين است كه به‌جاي هاردديسك، در حافظه رم قرارگرفته است و در نتيجه نسبت به جداول موقت سرعت بيشتري دارد.

 

اما به ياد داشته باشيد كه استفاده بي‌رويه از اين نوع داده، حافظه زيادي را صرف مي‌كند كه مي‌تواند باعث كاهش كارايي سيستم شود. بنابراين اگر احساس مي‌كنيد تعداد جداول موقت، ركوردهاي آن‌ها و زمان استفاده از آن‌ها كم است، از اين نوع داده استفاده كنيد. در غير اين‌صورت، راه‌حل جدول موقت را انتخاب كنيد.

 

12- قفل‌گذاري بر روي ركوردهايي كه در حال خواندن، درج شدن، حذف شدن يا تغيير كردن هستند، هميشه از مباحث مهم بانك‌هاي اطلاعاتي بوده‌است. همان‌طور‌كه مي‌دانيد يك فرايند (Transaction) شامل يك يا چند دستورالعمل SQL است كه يا بايد همگي به صورت موفقيت‌آميز اجرا شوند (committed) يا در صورت ايجاد خطا در زمان اجراشدن يكي، اجراي بقيه نيز منتفي شود (Rollbacked).

 

 

 

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

ايندكس گذاري برروي ديده ها(Indexed Views) يكي از بهترين راههاي فوري جهت افزايش سرعت جستجو بر روي ديدهااست. در حالت عادي گزينه Manage Indexes بر روي ديدها قابل انتخاب نيست مگر آنكه اولا كليه جداول يا ديدهاي موجود در آن، خود داراي ايندكس باشد و دوم اينكه كليه ديدهاي موجود در آن و هم خود ديد مورد نظر با دستور زير ساخته شده باشند.

 

Create View....Whit Schema Binding AS.......

 

 

 

فرايند به دو صورت قابل پياده‌سازي است. اين كار يا با استفاده از دستورات Begin trans و Committrans انجام مي‌شود كه به آن حالت صريح (Explicit) مي‌گويند يا به صورت ضمني (Implicit) صورت مي‌گيرد كه در آن اثري از دو دستور مذكور ديده نمي‌شود و هر دستور SQL يك فرايند مجزا به حساب مي‌آيد. در هر دو روش ركوردهايي كه تحت‌تأثير دامنه فرايند قرار مي‌گيرند، توسط سيستم قفل مي‌گردند و براي ديگر كاربران نيز غيرقابل استفاده مي‌شوند و در نتيجه باعث كاهش سرعت كار آن‌ها به دليل ايجاد انتظار براي آزاد شدن ركوردها مي‌شود.

 

بنابراين براي رسيدن به حداكثر كارايي سيستم، بايد از ايجاد قفل‌هاي بي‌مورد بر روي ركوردهاي جداول بانك اطلاعاتي جلوگيري كرد. اين كار با استفاده از دستور SET Transaction Isolation Level Read Uncommitted براي فرايندهاي صريح (قبل از شروع فرايند، يعني قبل از دستور (begin Trans و يا استفاده از دستور WITH NOLOCK براي فرايندهاي ضمني (پس از قسمت From هر دستور SQL) قابل انجام است. در مورد مسئله فرايندها و انواع قفل‌گذاري مطالب خواندني زيادي در سايت مايكروسافت وجود دارد كه درصورت تمايل مي‌توانيد به آن‌ها نيز مراجعه كنيد.

 

13 - روال‌هاي ذخيره شده (stored Procedures) پس از هر اجرا، به ازاي هر دستورالعملي كه اجرا مي‌كنند، جهت اطلاع برنامه فراخوان (كلاينت) از موفقيت‌آميز بودن اجراي آن دستور SQL، پيغامي را به سمت آن برنامه مي‌فرستند. اين مسئله باعث افزايش ترافيك شبكه در اثر فرستادن مداوم پيغام ازSP به سمت كاربر مي‌شود. با تايپ دستور زير در ابتداي يكSP، مي‌توانيد آن را از انجام اين كار منع كنيد:

SET NOCOUNT ON

 

نتيجه‌گيري‌

مطالب فوق تنها قسمتي از راهكارهاي قابل انجام براي رسيدن به‌سرعت و بازدهي مناسب در بانك‌هاي اطلا‌عاتي مبتني بر SQL Server است. در ضمن‌ بايد اين نكته را هم درنظر داشت كه اصولا‌ً در سيستم‌هاي بزرگ اطلا‌عاتي تحت شبكه، توپولوژي و نوع اجزاي موجود در شبكه از اهميت بسيار زيادي در تعيين سطح كارايي يك بانك اطلا‌عاتي برخورداراست. گاهي حتي در حالي‌كه بهترين طراحي و پيكربندي SQL Server براي يك بانك اطلا‌عاتي انجام شده، يك اشتباه كوچك در سطح شبكه مي‌تواند تمام زحمات را بر ‌باد دهد يا مثلا‌ً يك سهل‌انگاري در نوشتن روال‌هاي ذخيره شده يا تريگرها مي‌تواند سيستم را به‌يك لوپ (Loop) پردازشي بي‌نهايت ببرد و باعث افت شديد سرعت اجراي برنامه‌ها شود. بنابراين در اين‌گونه سيستم‌ها، استفاده بجا و مناسب از منابع سيستم و شبكه و دقت در طراحي و پياده‌سازي جداول، ديدها، روال‌هاي ذخيره‌شده و تريگرها بسيار مهم و حياتي است.

 

 

 

نوشته :مهيار داعي‌الحق ماهنامه شبکه

[ملیساا 5015]

استفاده از راهکارهايي چون افزایش تعداد سرورها یا ارتقای سرورهای موجود در پردازنده قدرتمندتر، حافظه بیشتر، هارددیسک های سریع تر و حتی ارتقای ارتباطات شبکه ای یا امثال آن از جمله ترفند هایی هستند که برای رفع معضل سرعت، مورداستفاده قرار می گیرند. در این مقاله به یکی از روش های توسعه طولی، نگاهی می افکنیم.

 

 

ممکن است پس از طی چند سال و درج هزاران رکورد در جداول یک بانک اطلاعاتی، سرعت جستجو در میان اطلاعات درج شده،سرعت درج اطلاعات جدید یا تغییر و حذف آن ها کند شود و مدیران یا برنامه نویسان این بانک ها را به ایجاد دگرگونی در برخی قسمت های بانک ناچار نماید.دو روش معمول برای مواجهه با چنین پدیده ای وجود دارد: روش اول یعنی توسعه عرضی ( Scale up ) که ترجیحا باید مقدم بر روش دوم مورد استفاده قرار گیرد، با استفاده از ساز وکار هایی مثل ایجاد انواع ایندکس ها بر روی جداول یا دید ( view )های بانک، کوتاه نمودن و کم حجم تر کردن تریگرها، به حداقل رساندن تعداد دستورات SQL که در هر فرایند وجود دارد، پرهیز از استفاده بی موقع و مکرر از توابع تعریف شده توسط کاربر و غیره می توان تا حدودی مشکل را برطرف نمود. اما در برخی موارد با تمام این تمهیدات باز هم اشکالات و وقفه هایی در سرعت و عملکرد سیستم، مدیران بانک های اطلاعاتی را ناگزیر می کند برای حل مشکل به روش دوم یعنی توسعه طولی ( Scale out ) رو بیاورند.

در این روش، استفاده از راهکارهایی چون افزایش تعداد سرورها یا ارتقای سرورهای موجود در پردازنده قدرتمندتر، حافظه بیشتر، هارددیسک های سریع تر و حتی ارتقای ارتباطات شبکه ای یا امثال آن از جمله ترفند هایی هستند که برای رفع معضل سرعت، مورداستفاده قرار می گیرند. در این مقاله به یکی از روش های توسعه طولی، نگاهی می افکنیم.

 

صورت مسئله

 

فرض کنید شما دارای یک بانک اطلاعاتی در حال کار، روی یک سرور هستید و در طول روز حدود پانصد کاربر به طور متناوب مشغول کار با این بانک هستند. کاملا آشکار است که هر چه سعی کنید با استفاده از سازوکارهای توسعه عرضی ( مثل ایندکس گذاری و امثال آن )، سرعت و کارایی سیستم را افزایش دهید، باز هم برای ارائه گزارش های مطلوب و استاندارد و حتی برای ایجاد یک محیط کارا و کاربرپسند برای استفاده از آن، مجبور می شوید برای اتفاقاتی که ممکن است در اثر ترافیک سنگین عملیات کاربران اتفاق بیفتد، فکر دیگری بکنید. یعنی حتی اگر سرور شما یک کامپیوتر قدرتمند با دو پردازنده Xeon، چهار گیگابایت حافظه و یک هارددیسک سریع باشد، باز هم قطعا در پاره ای از اوقات تصادم انبوه درخواست های موردنیاز کاربران در یک زمان، باعث بروز مسائلی چون قفل شدن برخی رکوردهای بانک (locking ) یا مسدود شدن برخی درخواست ها به دلیل عدم وجود زمان کافی برای پردازش آن ( Timeout Blocking ) می شود.

انتخاب راه حل

 

راه حل مسئله با استفاده از روش توسعه طولی، افزودن به تعداد سرورهایی است که به شکلی نقش پردازشگر اطلاعات را بازی می کنند. در این روش، سه راه حل مختلف وجود دارد که با اتکا به آن ها می توان تعداد سرورها، سرورهای لایه واسط (Application Server ) و سرورهای بانک اطلاعاتی (Database Server ) را افزایش داد. با این کار ترافیک و سنگینی پردازش فقط روی سرور لایه واسط یا سرور بانک اطلاعاتی کاهش می یابد و به نحوی پدیده توازن بار (Load Balancing ) چند سرور صورت می گیرد. در ادامه به بررسی هر سه راه حل مذکور می پردازیم.

راه حل یکم: کپی برداری (Cloning )

 

در این راه حل به سادگی می توان به جای استفاده از یک سرور لایه واسط که نقش پردازش اطلاعات را بازی می کند، از چندین سرور برای انجام دادن عمل مذکور استفاده نمود. سرورهای لایه واسط عمدتا محل فعالیت کامپوننت ها ( COM) یا وب سرورها هستند. بنابراین اگر بتوان تعداد آن ها را افزایش داد و هر دسته از کاربران را به سمت یکی از این سرورها هدایت نمود، عملکرد پردازشی سرورهای لایه واسط افزایش می یابد و در نتیجه تا حدود زیادی از بروز سرعت در سیستم جلوگیری می شود. ضمن این که اگر هر کدام از این سرورها نیز با مشکل روبه رو شوند، می توان به صورت موقت کاربران آن را به سمت یک سرور دیگر هدایت کرد و از ایجاد وقفه در کار آن ها جلوگیری نمود. ( شکل 1 )

راه حل دوم: تقسیم بندی(Partitioning )

 

این راه حل به دو روش تقسیم می شود:

روش یکم: افزایش سرورهای لایه واسط

 

در این روش نیز تعداد سرورهای لایه واسط افزایش می یابد. اما بر خلاف راه حل قبل که چند سرور کاملا مشابه، نقش یکسانی را در پردازش درخواست های کاربران ایفا می کردند، این بار هر کدام از سرورهای لایه میانی صرفا عمل خاصی را انجام می دهند که سایر سرورها از انجام دادن آن معافند. مثلا اگر قبلا تنها یک سرور، هم محل فعالیت COM ها بود و هم نقش یک وب سرور را بازی می کرد، اکنون دو وظیفه مذکور را بین دو سرور مختلف ( و شاید با ویژگی ها و توانایی های مختلف ) تقسیم می کنیم. یا به عنوان مثالی دیگر اگر تا کنون تنها یک سرور لایه میانی هم شامل COM هایی بود که با استفاده از اشیای ADO ، دسترسی به سرور پایگاه را فراهم می آوردند و هم شامل COM های دیگری که اعمال محاسباتی پیچیده را انجام می داد، اکنون می توان این دو وظیفه را بین دو سرور مختلف به ترتیب با نام هایی چون Data Access و Business Logic تقسیم کرد.

نقطه قوت این روش این است که علاوه بر تقسیم ترافیک و پردازش میان دو یا چند سرور جداگانه، امکان جداسازی کاربران بر اساس نوع استفاده آن ها از اطلاعات و فراهم ساختن سرورهایی با کاربرد مختلف جهت انجام دادن وظایف متعدد وجود دارد و در نتیجه ضریب امنیت دسترسی یا پردازش اطلاعات نیز بالاتر می رود. نقطه ضعف آن هم این است که در صورت از کار افتادن یکی از این سرورهای لایه میانی، سایر سرورهای این لایه نمی توانند به سرعت جایگزین آن شوند و وظیفه آن را به طور موقع بر عهده بگیرند. ( شکل 2 )

 

 

روش دوم :تقسیم سرور پایگاه داده

 

در این روش، به جای سرورهای لایه میانی، سرور پایگاه داده به دو یا چند سرور تقسیم می شود تا حجم فرایند ( Transaction) های داخلی و پرس و جو های همزمان روی آن سرور کاهش پیدا کند. برای استفاده از این روش، در نظر گرفتن یک نکته اساسی، بسیار مهم است. این نکته، تشخیص اشتراک یا عدم اشتراکی بودن داده ها میان کاربران مختلف است. بدین معنی که یک مدیر پایگاه داده باید بداند که آیا می توان داده ها را به چند دسته تقسیم کرد و هر دسته را روی یک سرور جداگانه برای کاربرد مختلف قرار داد یا نه. به عنوان مثال، اگر شرکتی دارای یک سیستم جامع، شامل سه زیرسیستم انبار، فروش و حسابداری باشد، می تواند جداول دیدها و ارتباطات مربوط به هر یک از این سه زیر سیستم را در یک پایگاه داده روی یک سرور جداگانه قرار دهد ت هر یکی از آن ها در دسترس مسئولان انبار، فروش و حسابداری شرکت قرار گیرد.

سوالی که در اینجا مطرح می شود این است که اگر این سه زیر سیستم با یکدیگر در ارتباط باشند، باید چه کرد؟ مثلا فرض کنید که مسئول انبار برای خروج یک کالا از انبار باید بتواند به داده هایی از جداول مربوط به سیستم فروش دست یابد. بنابراین باید در این روش، راهی وجود داشته باشد تا در عین جدا بودن اطلاعات مذکور از یکدیگر، امکان استفاده کاربران مختلف از یکی از آن ها یا تلفیقی از آن ها نیز فراهم گردد.

در SQL Server نسخه 2000 برای این کار امکاناتی پیش بینی شده است. به عنوان مثال، شما با استفاده از قابلیت Linked server قادر خواهید بود یک بانک اطلاعاتی مقیم در یک سرور دیگر را طوری به یک بانک اطلاعاتی سرورتان پیوند بزنید که گویی هر دو در یک سرور قرار دارند.پس از این کار حتی می توانید پرس و جوهایی انجام دهید که از لینک کردن چند جدول و یا دید از هر دو بانک اطلاعاتی حاصل شود. به این قابلیت، جستجوی توزیع شده یعنی Distributed Query گفته می شود.

علاوه بر این خاصیت دیگری در این نسخه تعبیه شده است که امکان انجام دادن یک فرایند واحد روی چند بانک اطلاعاتی موجود در چند سرور مختلف را فراهم می کند(Distributed Transaction ). این قابلیت ها به گونه ای است که حتی امکان تعریف روابط وابستگی از طریق کلیدهای اولیه و کلید های خارجی میان بانک های مذکور نیز وجود دارد و یا مثلا ساخت یک دید با استفاده از لینک کردن جداول موجود در چند سرور نیز میسر گشته که به آن Distributed Portioned View گفته می شود.

به هر حال، بسیاری از راه حل های مربوط به "توزیع" در SQL Server برای استفاده همزمان از قدرت و قابلیت چندین سرور در نظر گرفته شده است. درشکل سه مثالی را مشاهده می کنید که در آن به صورت بسیار ساده جدول مشتریان یک شرکت به دلیل زیاد بودن و قابل جدا کردن اطلاعات آن، به سه دسته مشتریان شرق، غرب و مرکز کشور تقسیم بندی شده و هر کدام در عین ارتباط با یکدیگر و با کاربران، بر روی یک سرور مجزا قرار گرفته اند .(شکل 3 )

 

راه حل سوم: Replication

 

این راه حل نیز مشابه روش دوم Partitioning است. اما بر خلاف آن روش که سرور بانک اطلاعاتی را به چند سرور حاوی اطلاعات مختلف مورد نیازشان تقسیم می کردیم، در اینجا چند سرور بانک اطلاعاتی با استفاده از سازوکار Replication دقیقا شامل اطلاعات یکسان و همانند می باشند.

به عنوان مثال فرض کنید در یک شرکت بزرگ که دارای سه واحد اصلی فروش، انبار و حسابداری است، سه سرور بانک اطلاعاتی کاملا یکسان در نظر گرفته شده که هر یک از واحد ها داده های موردنیازشان را از جداول مربوط به خودشان از سروری که به آن ها اختصاص یافته دریافت می کنند و هر گاه تغییری را در آن اطلاعات به وجود آوردند، یا در همان لحظه باید در کلیه سرورهای دیگر نیز اعمال شود و یا طبق یک برنامه زمانبندی شده، در زمان دیگری مثلا در ساعات غیر اداری ک میزان ترافیک اطلاعات کاهش می یابد، به دیگر سرورها منتقل شود.اگر بخواهیم تغییر اطلاعات در همان لحظه به دیگر سرورها اعمال شود، می توان از Replication نوع فرایندی (Transactional) استفاده کرد.

در این روش با استفاده از قابلیتی به نام تغییر دو مرحله ای اطلاعات یا اصطلاحا Two Phase Commit هر تغییری بلافاصله در سایر سرورها نیز لزوما اعمال می شود. اما اگر بخواهیم تغییرات در زمان خاصی و به تعداد معمولی ( مثلا دو یا سه بار طی شبانه روز ) به سرورهای دیگر منتقل شود، می توان از Replication نوع ادغام استفاده کرد .(شکل 4)

بنابراین در این حالت هر سرور ضمن داشتن آخرین اطلاعات مربوط به واحد خود، آخرین اطلاعات رسیده از سایر سرورها (یا واحدهای دیگر) را نیز دارد.

لازم به ذکر است که عملیات انتقال اطلاعات با استفاده از Replication نکات و مسائل فنی فراوانی دارد که به تنهائی در قالب چند مقاله قابل بررسی است.

 

 

 

منبع : sqliran.com