میزان کردن طراحی فیزیکی پایگاه داده به صورت مشروط

Constrained physical design tuning سیّد امین میرزایی استاد راهنما: دکتر رهگذر درس مباحث ویژه در پایگاه داده – بهار ۸۹ میزان کردن طراحی فیزیکی پایگاه داده به صورت مشروط

مسئله‌ی مورد بحث: • با داشتن یک workload خاص (W) و بودجه‌ی ذخیره‌سازی مشخص (B)، یک مجموعه ساختار فیزیکی (یا یک پیکربندی) بیابید که متناسب با B بوده و کمترین هزینه‌ی اجرا برای W را داشته باشد. میزان کردن خودکار طراحی فیزیکی پایگاه داده

توجه زیاد به مسئله در دهه‌ی اخیر • در حوزه‌ی دانشگاه: • Papadomanolakis, S., Ailamaki, A.: An integer linear programming approach to database design. In: Workshop on Self-Managing Database Systems (2007) • Bruno, N., Chaudhuri, S.: An online approach to physical design tuning. In: Proceedings of the International Conference on Data Engineering (ICDE) (2007) • Bruno, N.,Chaudhuri, S.: Physical design refinement: The “Merge-Reduce” approach. In: International Conference on Extending Database Technology (EDBT) (2006) • Agrawal, S., Chu, E., Narasayya, V.: Automatic physical design tuning: workload as a sequence. In: Proceedings of the ACMInternational Conference on Management of Data (SIGMOD) (2006) • Bruno, N., Chaudhuri, S.: To tune or not to tune? A Lightweight Physical Design Alerter. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2006) • Bruno, N., Chaudhuri, S.: Automatic physical database tuning: A relaxation-based approach. In: Proceedings of the ACM International Conference on Management of Data (SIGMOD) (2005) • Zilio, D., Zuzarte, C., Lightstone, S., Ma, W., Lohman, G., Cochrane, R., Pirahesh, H., Colby, L., Gryz, J., Alton, E., Liang, D., Valentin, G.: Recommending materialized views and indexes with IBM DB2 design advisor. In: International Conference on Autonomic Computing (2004) • Agrawal, S., Narasayya, V., Yang, B.: Integrating vertical and horizontal partitioning into automated physical database design. In: Proceedings of the ACM International Conference on Management of Data (SIGMOD) (2004) • Valentin, G., Zuliani, M., Zilio, D., Lohman, G., Skelley, A.: DB2 advisor: An optimizer smart enough to recommend its own indexes. In: Proceedings of the International Conference on Data Engineering (ICDE) (2000) • Agrawal, S., Chaudhuri, S., Narasayya, V.: Automated selection of materialized views and indexes in SQL databases. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2000) • Chaudhuri, S., Narasayya, V.: Index merging. In: Proceedings of the International Conference on Data Engineering (ICDE) (1999) • Chaudhuri, S., Narasayya, V.: Autoadmin ’What-if’ index analysis utility. In: Proceedings of the ACM International Conference on Management of Data (SIGMOD) (1998) • Chaudhuri, S., Narasayya, V.: An efficient cost-driven index selection tool for Microsoft SQL Server. In: Proceedings of the International Conference on Very Large Databases (VLDB) (1997) میزان کردن خودکار طراحی فیزیکی پایگاه داده(ادامه)

توجه زیاد به مسئله در دهه‌ی اخیر (ادامه) • در حوزه‌ی صنعت در DBMSهای مختلف: • Agrawal, S., Chaudhuri, S., Kollar, L.,Marathe, A., Narasayya, V., Syamala, M.: Database Tuning Advisor for Microsoft SQL Server 2005. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2004) • Dageville,B., Das, D.,Dias, K.,Yagoub, K.,Zait, M.,Ziauddin, M.: Automatic SQL Tuning in Oracle 10g. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2004) • Zilio, D., Rao, J., Lightstone, S., Lohman, G., Storm, A., Garcia-Arellano, C., Fadden, S.: DB2 design advisor: Integrated automatic physical database design. In: Proceedings of the International Conference on Very Large Databases (VLDB) (2004) میزان کردن خودکار طراحی فیزیکی پایگاه داده(ادامه)

عدم وجود یک راه حل کامل برای سناریوهای عملی، با وجود حجم بالای کار در این زمینه • مثال • SELECT a, b, c, d, e FROM R WHERE a=10 • فرض: فقط یک سطر از R شرط بالا را دارد • دو نوع شاخص: • IC: شاخص کامل بر روی a, b, c, d, e • تنها یک I/O برای نتیجه • IN: شاخص محدود فقط بر روی a • نیاز به دو I/O • شاخص اول تنها مقدار کمی کارآمدتر از شاخص دوم است، در حالی که فضای بسیار بیشتری اشغال می‌کند • مخصوصاً وقتی UPDATE ها در نظر گرفته شوند • روش‌های فعلی جوابگوی این مثال نیستند راه حل کامل؟

رسایی (Expressiveness) تأثیربخشی (Effectiveness) ویژه‌گری (Specialization) نیازها

معرفی یک زبان برای تعیین قیدها بررسی یک چهارچوب معرفی‌شده برای جستجو وفق دادن چهارچوب فوق برای در نظر گرفتن قیدها ارزیابی نتایج معرفی چهارچوبی برای میزان کردن به صورت تعاملی فهرست عناوین مقاله

مجموعه‌ها • W[2], W[“QLong”] • توابع • تعداد ستون‌های T: cols[T] • اندازه‌ی شاخص I: size[I] • هزینه‌ی پرسش q در پیکربندی C: cost(q, C) • ... زبانی برای تعریف قیدها

W: Workload ورودی C: پیکربندی موردنظر COrig: پیکربندی اولیه موجود بر روی پایگاه داده CBase: بخشی از پیکربندی اولیه موجود که تنها از قیود یک‌پارچگی منتج می‌شوند CSelectBase: بهترین پیکربندی برای پرسش‌های SELECT ثابت‌ها

قیود ساده (Simple Constraints) • ASSERT size(C) ≤ 200M • اندازه‌ی شاخص نهایی از ۲۰۰ مگابایت کمتر باشد • ASSERT cost(W[2], C) ≤ 2 * cost(W[2], COrig) • هزینه‌ی دومین پرسش workload در پیکربندی نهایی، بیش از دو برابر هزینه‌ی آن در پیکربندی فعلی نباشد امکانات زبان

مولدها (Generators) • FOR Q IN W ASSERT cost(Q, C) ≤ 2 * cost(Q, COrig) • هزینه‌ی هیچ کدام از پرسش‌‌های workload در پیکربندی نهایی، بیش از دو برابر هزینه‌ی آن در پیکربندی فعلی نباشد • FOR I in C ASSERT numCols(I) ≤ 4 • تمامی شاخص‌ها در پیکربندی نهایی، حداکثر ۴ ستون داشته باشند امکانات زبان (ادامه)

فیلترها (Filters) • FOR I in C WHERE I LIKE “col3,*” ASSERT numCols(I) ≤ 4 • تنها شاخص‌هایی از پیکربندی نهایی که اولین ستون آنها “col3” است، حداکثر ۴ ستون داشته باشند امکانات زبان (ادامه)

تجمیع (Aggregation) • FOR I in C ASSERT sum(size(I)) ≤ 200M • مجموع اندازه‌ی شاخص‌ها در پیکربندی نهایی از ۲۰۰ مگابایت کمتر باشد • FOR I in C WHERE table(I) = TABLES[“T”] ASSERT sum(size(I)) ≤ 4 * size(TABLES[“T”]) • مجموع اندازه‌ی شاخص‌های جدول “T” در پیکربندی نهایی، از ۴ برابر اندازه‌ی خود جدول بیشتر نباشد امکانات زبان (ادامه)

قیود تودرتو (Nested Constraints) • FOR T in TABLES FOR I in C WHERE table(I) = T ASSERT sum(size(I)) ≤ 4 * size(T) • مجموع اندازه‌ی شاخص‌های هیچ جدولی در پیکربندی نهایی، از ۴ برابر اندازه‌ی خود جدول بیشتر نباشد امکانات زبان (ادامه)

قیود نرم (Soft Constraints) • عدم لزوم ارضای کامل • تلاش برای هر چه نزدیک‌تر شدن به این قیود • FOR Q in W WHERE type(Q) = SELECT SOFT ASSERT cost(Q, C) ≤ cost(Q, COrig) / 1.1 • نرخ بهبود تمامی پرسش‌های SELECT در workload تحت پیکربندی نهایی، تا حد امکان به ۱۰٪ نزدیک باشد • FOR Q IN W SOFT ASSERT sum(cost(Q, C)) = 0 • مجموع هزینه‌های پرسش‌ها در پیکربندی نهایی، حداقل باشد • راه حل کلی تمام مسائل بهینه‌سازی! امکانات زبان (ادامه)

constraint:= [SOFT] ASSERT [agg] function (≤|=|≥) constant | FOR var IN generator [WHERE predicate] constraint • کلمات پررنگ: غیر پایانه‌ها • کلمات عادی: ثابت‌ها • کلمات داخل براکت: اختیاری • | : علامت انتخاب گرامر زبان

هیچ ستونی در بیش از ۳ شاخص نباشد • برای کاهش احتمال درگیری بین شاخص‌ها • FOR T in TABLES FOR col in cols(T) FOR I in C WHERE I LIKE “*,col,*” ASSERT count(I) ≤ 3 چند مثال

شاخص‌های خوشه‌بندی شده‌ی پیکربندی نهایی، باید با یکی از سه ستون a، b و یا c شروع شوند • FOR I in C WHERE clustered(I) ASSERT I LIKE “(a,*)|(b,*)|(c,*)” چند مثال (ادامه)

هیچ یک از پرسش‌های SELECT در پیکربندی نهایی، در مقایسه با پیکربندی فعلی بیش از ۱۰٪ تنزل پیدا نکند • FOR Q in W WHERE type(Q) = SELECT ASSERT cost(Q, C) ≤ 1.1 * cost(Q, COrig) چند مثال (ادامه)

هیچ شاخصی را نتوان بدون این که حداقل هزینه‌ی یک پرسش دو برابر شود، با حالت محدود آن جایگزین کرد • FOR I in C FOR Q in W ASSERT cost(Q, C - I + narrow(I))/cost(Q, C) ≤ 2 • تابع narrow(I) یک شاخص شامل فقط اولین ستون شاخص I را بر می‌گرداند (مثلاً: narrow((a,b,c)) = (a) ) چند مثال (ادامه)

معماری کلی چهارچوب استفاده شده برای جستجو

ارزیابی پیکربندی • بررسی قید فضا • محاسبه‌ی فاصله‌ی پیکربندی از قید در صورت ارضا نشدن قید • ارزیابی تابع بهینه‌ساز • محاسبه‌ی هزینه‌یمورد انتظار workload تحت پیکربندی فعلی • زمان‌بر ترین بخش فرایند بهینه‌سازی • استفاده از روش‌هایی خاص برای کم کردن هزینه‌ی این بخش معماری چهارچوب جستجو

تبدیل‌ها • به کار بستن قوانین تبدیل • به دست آوردن پیکربندی‌های جدید برای بررسی • استفاده از روش merge-reduce معماری چهارچوب جستجو (ادامه)

رتبه‌بندی پیکربندی کاندید • تخمین هزینه‌ی نهایی و اندازه‌ی workload • تخمین هزینه بسیار زمان‌بر تر از تخمین اندازه است • تعداد بالای پیکربندی‌های کاندید • هزینه‌ی بالای بهینه‌سازی پرسش‌ها معماری چهارچوب جستجو (ادامه)

هرس کردن پیکربندی‌ها • پیکربندی C از قید ذخیره‌سازی B خارج است • با تبدیل C به پیکربندی جدید C' می‌رسیم که B را ارضاء می‌کند • پیکربندی بهتری از C' نمی‌توان یافت • زیرا تبدیلات، کارایی پیکربندی را پایین می‌آورند • در نتیجه می‌توان C' را هرس کرد معماری چهارچوب جستجو (ادامه)

انتخاب پیکربندی آغازین • هر پیکربندی‌ای می‌تواند آغاز کننده باشد • انتخاب پیکربندی آغازین خوب، فضای جستجو را محدود می‌کند معماری چهارچوب جستجو (ادامه)

رتبه‌بندی پیکربندی‌ها • الگوریتم رتبه‌بندی: • به ازای هر Ci از پیکربندی‌ها • رتبه‌ی Ci = |{cj∈ C: cj بر ci تسلط داشته باشد}| • R = [] • برای i از ۱ تا n • Li = {c ∈ C: رتبه‌ی c = i} • LPi = یک جایگشت تصادفی از Li • LPi را به R اضافه کن • خروجی الگوریتم: R در نظر گرفتن قیدها در چهارچوب جستجو

رتبه‌بندی پیکربندی‌ها(ادامه) در نظر گرفتن قیدها در چهارچوب جستجو (ادامه)

هرس کردن فضای جستجو • توسعه‌ی روش هرس کردن در حالت عادی • با در نظر گرفتن قیدها • امکان راهنمایی هرس کردن با استفاده از کلمات کلیدی MONOTONIC_UP و MONOTONIC_DOWN • امکان استفاده از روش‌های ابتکاری (heuristic) با استفاده از کلمات کلیدی HILL_CLIMB و KEEP_VALID • امکان راهنمایی تبدیلات با استفاده از دستور AVOID • AVOID delete(I) WHERE name(I)=“goodI” در نظر گرفتن قیدها در چهارچوب جستجو (ادامه)

پیاده‌سازی به زبان C++ پیاده‌سازی چهارچوب تعریف شده

تنظیمات آزمایش • استفاده از نسخه‌ی تغییر یافته‌ی Microsoft SQL Server 2005 • استفاده از پایگاه داده‌ی TPC-H • تولید workload با استفاده از QGen ارزیابی نتایج آزمایش

تنها یک قید ذخیره‌سازی • Initial = CSelectBest SOFT ASSERT cost(W,C) = 0 ASSERT size(C) ≤ B ارزیابی نتایج آزمایش (ادامه)

تنها یک قید ذخیره‌سازی کیفیت پیکربندی پیشنهادی برای قید ذخیره‌سازی

تنها یک قید ذخیره‌سازی (ادامه) کارایی روش‌های جایگزین مختلف

تنها یک قید ذخیره‌سازی (ادامه) کیفیت روش‌های پیشنهادی در مرور زمان

تنها یک قید ذخیره‌سازی (ادامه) استفاده از backtrack به پیکربندی‌های قبلی

تنها یک قید ذخیره‌سازی (ادامه) تعداد تبدیلات کاندید

بهینه‌سازی با تنها یک هدف • scoreW(C) = cost(W,C) + w ・ max(0, size(C) − 2,000) ارزیابی نتایج آزمایش (ادامه)

بهینه‌سازی با تنها یک هدف فضای جستجوی اولیه با استفاده از توابع رتبه‌بندی مختلف

بهینه‌سازی با تنها یک هدف (ادامه) بهترین پیکربندی برای توابع رتبه‌بندی مختلف

قیود بیشتر و پیچیده‌تر • FOR T TABLES FOR I in indexes(T) ASSERT count(I) ≤ 4 ارزیابی نتایج آزمایش (ادامه)

قیود بیشتر و پیچیده‌تر تعداد شاخص‌ها به ازای هر جدول در ۲ پیکربندی

قیود بیشتر و پیچیده‌تر (ادامه) مجموعه‌ی غیر مسلط پیکربندی‌ها برای IPT ≤ 4

قیود بیشتر و پیچیده‌تر (ادامه) هزینه‌ی مورد انتظار پرسش‌ها برای IPT ≤ 4

قیود بیشتر و پیچیده‌تر (ادامه) • FOR I IN C ASSERT sum(size(I)) ≤ 2G FOR Q IN W ASSERT cost(Q, C) ≤ 0.7* cost(Q, COrig) ارزیابی نتایج آزمایش (ادامه)

قیود بیشتر و پیچیده‌تر (ادامه) پیکربندی‌های غیر مسلط برای S70

قیود بیشتر و پیچیده‌تر (ادامه) • FOR I IN C SOFT ASSERT sum(size(I)) ≤ 2G ارزیابی نتایج آزمایش (ادامه)

قیود بیشتر و پیچیده‌تر (ادامه) پیکربندی‌های غیر مسلط برای S70 راحت شده (relaxed)

قیود بیشتر و پیچیده‌تر (ادامه) هزینه‌ی مورد انتظار پرسش‌ها برای S70

مقیاس‌پذیری • به نسبت تعداد قیود: ارزیابی نتایج آزمایش (ادامه)

میزان کردن طراحی فیزیکی پایگاه داده به صورت مشروط