استفاده از MySQL برای گزارش گیری تحلیلی

[EmRa228]

سلام و خسته نباشید
من یک سایت گزارشگیری و تحلیل گری دارم که با زبان PHP و پایگاه داده MySQL برنامه نویسی شده.

این سایت فقط یک کاربر دارد اما پایگاه داده اش در مجموع حدود 1 میلیارد رکورد دارد و دارای دو جدول 300 میلیون رکوردی و نرخ افزایش روزانه 5 میلیون رکورد است.

روی سرور MySQL آن گزارش گیری های پیچیده انجام می شود که مصرف بسیار بالای CPU را در بر دارد.
متاسفانه زمان بعضی از گزارش ها به 6 ساعت هم می رسد اما ما نهایتا 60 ثانیه می خواهیم.
این سایت هم اکنون روی سرور با 20 گیگ رم و حدود 2.7 پردازنده 4 هسته ای Xeon پشتیبانی می شود.


ما فقط می خواهیم سرعت گزارش دهی کاهش پیدا کند.
بیشتر منایعی که MySQL مصرف می کند مخصوصا در مواقع JOIN زدن، CPU است اما هر پردازش بیشتر از 2.7 مصرف ندارد.


به نظر من چون بزرگترین سایت دنیا (فیس بوک) از پایگاه داده MySQL استفاده می کند، خود MySQL نباید اینقدر ضعیف باشد!!!
اما وقتی سه جدول که هر کدام حدود 1 میلیون رکورد دارد را LEFT JOIN می زنم چند ساعت طول می کشد، این غیر طبیعی نیست؟
البته در صورت نیاز می توانیم کل پایگاه داده را به سیستم قوی تر تغییر دهیم!


راهکارهای کارشناسی خود را برای کاهش زمان گزارش گیری بفرمایید.

منتظر جوابتان هستم
باتشکر

[linuxUser]

سلام دوست عزیز
من خودم با یک دیتابیس MySQL کار می کردم که حدود 30 میلیون رکورد داشته. برای انجام تراکنش های ساده مشکل زیادی نداشت (مثل حذف یک رکورد، درج یک رکورد جدید و یا Select ساده) ولی وقتی کار به join می رسید خیلی کار مشکل می شد و در بسیاری از موارد دیتابیس برای یک مدت خیلی طولانی جواب نمی داد.
اون MySQL که Facebook باهاش کار میکنه با این ورژن که ما داریم زمین تا آسمون فرق داره. اونا خودشون نسبت به نوع کوری ها Engine دیتابیس تغییر می دن واز تکنیک های خیلی زیادی استفاده می کنند.
من چندین ساله که دارم با MySQL کار می کنم ومی تونم به جرات بگم دیتابیس خیلی مناسبی برای این حجم از اطلاعات که دارید می گید نیست. گزینه های دیگه برای این حجم از اطلاعات دیتابیس Oracle و یا دیتابیس PostgreSQL هست.
اگه می خواید حتما از MySQL استفاده کنید من می تونم چند تا راهنمایی کنم :


حتما از آخرین ورژن استفاده کنید.
اگه تعداد عملیات های insert, update, delete نسبت به select هایی که برای گزارش گیری میگیرید خیلی کمتری که به نظر هم همین طور میادT، از گزینه MyISAM برای Engine تمامی جداول استفاده کنید.
تا اون جایی که امکان داره از join جداول بزرگ پرهیز کنید و نهایتا بیش از یک join در یک کوری استفاده نکنید.
حتما کلید های اصلی جدول به صورت عددی (int, bigInt , ...) انتخاب کنید
از allow null گذاشتن فیلدهایی در جداول که در گزارشات شرطی روی آن ها اعمال می شود پرهیز کنید. (برای این فیلدها یک مقدار پیشفرض نا معتبر قرار دهید)

می تونید به کتاب High Performance MySQL هم یه نگاهی بندازید.

[morteza147]

من تو این زمینه تخصص چندانی ندارم
این ها را یه بار امتحان کنید نتیجه را اینجا اعلام کنید
1. وقتی می خواهیید جوین کنید به جای * از نام فیلد ها استفاده کنید
2. و همچنین در آخر از دستور limit 20 استفاده کنید به جای 20 هر چند رکورد را لازم دارید بنویسید