سخت افزار و تنظیمات مورد نیاز برای افزایش سرعت MySQL با 1 میلیارد داده

[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 می زنم چند ساعت طول می کشد، این غیر طبیعی نیست؟
البته در صورت نیاز می توانیم کل پایگاه داده را به سیستم قوی تر تغییر دهیم!


آیا راهکار سخت افزاری برای کاهش زمان گزارش دهی، با توجه به اینکه فقط یک پردازش را یک نفر انجام می دهد، دارید؟

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

[Nik0TiN]

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

[EmRa228]

ممنون از پاسختان
اما قطعا من مطمئن هستم که مشکل کد نویسی ندارم که در این بخش سوالم رو مطرح کردم.
برای مثال LEFT JOIN کردن سه جدول حتی از داخل phpmyadmin نیز چند ساعت زمان می برد!!!

[mojiz]

ایندکس هاتون درست هست؟

[camelian77]

به نظر من هم این مشکل بیشتر مربوط به ساختار داده هست data structure که باید بازبینی بشه. به این موارد توجه کنید :

۱. موتور دیتابیس چیست؟ InnoDB? Xtreme? MyISAM?

۲. آیا داده ها قابلیت پارتیشن شدن رو دارن؟ اگر بله حتما بکنید. پارتیشنینگ روی سیستم ما با ۱۶۰ میلیون داده زمان سرچ ها رو ۳ برابر کاهش داد

۳. در هنگام JOIN کردن از چه متدی برای مچ کردن استفاده شده؟ sq1.data1 = sq2.data1 ? یا از foreign key?

۴. امکان کش کردن قسمتی از اطلاعات در php buffer هست که جوین نرم افزاری انجام بشه؟

۵. امکان آرشیو کردن قسمتی از تیبل بزرگ ۱ میلیاردی شما هست؟

۶. با داشتن ۶۴ گیگ رم می تونید کل اطلاعات رو در رم بخونید و زمان دسترسی رو شدیدا کاهش بدید (تقریبا ۱۰ برابر)

کلا باید ساختار داده شما بررسی بشه ... و ضمنا مای اس کیو ال یک نرم افزار threaded درست و حسابی نیست باید از apache lucene Or apache Solr OR استفاده کنید این کار شما big data هست.

در facebook از mysql به شکل grid - clustered database استفاده می شه.... یعنی یک کوئری می دونه که کدوم سرور حاوی کدوم اطلاعات هست و می ره از اون می پرسه.

[TukaNet]

چون در مورد نرا افزاری اطلاع دقیقی ندارم نظری نمی دهم

ولی از نظر سخت افزاری استفاده از کلاسترینگ چند سرور و یا استفاده از رایانش ابری برای شما موثر خواهد بود

[camelian77]

اگر واقعا نمی شه کل دیتابیس رو تغییر داد پیشنهاد من استفاده از سرور با ۱۲۸ گیگ رم + ۱۶ سی پی یو و خوندن کل داستان در memcache هست

[compiler]

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

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

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


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


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


آیا راهکار سخت افزاری برای کاهش زمان گزارش دهی، با توجه به اینکه فقط یک پردازش را یک نفر انجام می دهد، دارید؟

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


توصیه من به شما اینه :

استفاده از سروری با حد اقل ۲ سی پی یو فیزیکی Intel E5-2687W و ۲۵۶ گیگ رم + هارد SSD که سخت افزار قدرتمندی برای این کار به حساب میاد

استفاده از نسخه های Enterprise MySQL

استفاده از Nginx + php-fpm

استفاده از لینوکس توزیع Ubuntu یه جای CentOS

کانفیگ بدون کنتزل پنل و بسیار سبک

[EmRa228]

ایندکس هاتون درست هست؟

بله، علاوه بر ایندکس، از partitioning و Engine مناسب هم استفاده کردم.

[EmRa228]

۱. موتور دیتابیس چیست؟ InnoDB? Xtreme? MyISAM?
در بعضی جدول ها MyISAM در بعضی دیگر InnoDB ولی سعی کردم در جدول های دارای رکورد میلیونی از InnoDB استفاده کنم.


۲. آیا داده ها قابلیت پارتیشن شدن رو دارن؟ اگر بله حتما بکنید. پارتیشنینگ روی سیستم ما با ۱۶۰ میلیون داده زمان سرچ ها رو ۳ برابر کاهش داد
بله، جدول های بزرگ پارتیشن شدند ولی جدول 1 میلیونی خیر! یک میلیون نیاز به پارتیشن داره؟


۳. در هنگام JOIN کردن از چه متدی برای مچ کردن استفاده شده؟ sq1.data1 = sq2.data1 ? یا از foreign key?
از JOIN معمولی

SELECT * FROM `k` LEFT JOIN `j` USING(`k1`)


۴. امکان کش کردن قسمتی از اطلاعات در php buffer هست که جوین نرم افزاری انجام بشه؟
در این مورد بیشتر توضیح می دید؟


۵. امکان آرشیو کردن قسمتی از تیبل بزرگ ۱ میلیاردی شما هست؟
منظورتون از آرشیو کردن دقیقا چیه؟



۶. با داشتن ۶۴ گیگ رم می تونید کل اطلاعات رو در رم بخونید و زمان دسترسی رو شدیدا کاهش بدید (تقریبا ۱۰ برابر)

اگر واقعا نمی شه کل دیتابیس رو تغییر داد پیشنهاد من استفاده از سرور با ۱۲۸ گیگ رم + ۱۶ سی پی یو و خوندن کل داستان در memcache هست
چطوری این کار رو انجام بدم؟



باید از apache lucene Or apache Solr OR استفاده کنید
در مورد ویژگی های اینا یه توضیح می دید؟
مثلا سرعت ورود رکورد در ثانیه



در facebook از mysql به شکل grid - clustered database استفاده می شه.... یعنی یک کوئری می دونه که کدوم سرور حاوی کدوم اطلاعات هست و می ره از اون می پرسه.
وقتی می خواد ببینه دوستای من چه مطالبی نوشتن که به من نشون بده حتما JOIN استفاده کردن دیگه، اونم JOIN دو جدول چند میلیارد رکوردی!!!!!!
نمیشه یه کاری کرد مثل فیس بوک قدرتمند بشه؟

[EmRa228]

ولی از نظر سخت افزاری استفاده از کلاسترینگ چند سرور و یا استفاده از رایانش ابری برای شما موثر خواهد بود
در شرایط یک کاربر و یک کوئری جواب میده؟

چطور می تونم راه اندازی کنم؟

[EmRa228]

توصیه من به شما اینه :

استفاده از سروری با حد اقل ۲ سی پی یو فیزیکی Intel E5-2687W و ۲۵۶ گیگ رم + هارد SSD که سخت افزار قدرتمندی برای این کار به حساب میاد

استفاده از نسخه های Enterprise MySQL

استفاده از Nginx + php-fpm

استفاده از لینوکس توزیع Ubuntu یه جای CentOS

کانفیگ بدون کنتزل پنل و بسیار سبک

ممنون CPU که معرفی کردید واقعا دوست داشتنیه


نسخه های Enterprise MySQL چه فرقی دارند؟

دقیقا Nginx چه کمکی به بهبود سرعت می کنه؟

اگه اشتباه نکنم php-fpm پردازش PHP روی thread هست. من توی پردازش MySQL مشکل دارم نه PHP



استفاده از لینوکس توزیع Ubuntu یه جای CentOS
چه فرقی می کنه؟

[camelian77]

۱. موتور دیتابیس چیست؟ InnoDB? Xtreme? MyISAM?


در بعضی جدول ها MyISAM در بعضی دیگر InnoDB ولی سعی کردم در جدول های دارای رکورد میلیونی از InnoDB استفاده کنم.


من متوجه نشدم بالاخره جدول بزرگ شما میلیاد رکورد داره یا میلیون ؟ خیلی فرق می کنه. به هر حال InnoDB , MyISAM نقاط ضعف و قدرت مختلفی دارن باید تحقیق کنید با توجه به نوع داده ای که دارید . به هر حال میکس کردن شون به صلاح نیست و دست و پا گیر می شه.




۲. آیا داده ها قابلیت پارتیشن شدن رو دارن؟ اگر بله حتما بکنید. پارتیشنینگ روی سیستم ما با ۱۶۰ میلیون داده زمان سرچ ها رو ۳ برابر کاهش داد


بله، جدول های بزرگ پارتیشن شدند ولی جدول 1 میلیونی خیر! یک میلیون نیاز به پارتیشن داره؟

رجوع به سوال بالا. شما چند تا تیبل دارید؟ هر کدوم با چند تا رکورد؟ یک میلیون نیاز به پارتیشن داره؟ اگر داده ها و نحوه‌ی دسترسی بهشون پارتیشن شدنی هستند بله.



۳. در هنگام JOIN کردن از چه متدی برای مچ کردن استفاده شده؟ sq1.data1 = sq2.data1 ? یا از foreign key?




از JOIN معمولی


نا صحیح!!!‌ باید از foreigh key استفاده کنید زمان جستجو رو ۵۰ درصد می کاهه و فشار کمتری به منابع میاره ... ولی خوب باید ساختار داده رو بگید


کد PHP:

SELECT * FROM `k` LEFT JOIN `j` USING(`k1`)











۴. امکان کش کردن قسمتی از اطلاعات در php buffer هست که جوین نرم افزاری انجام بشه؟




در این مورد بیشتر توضیح می دید؟


MySQL :: MySQL HA/Scalability Guide :: 3 Using MySQL with memcached (http://dev.mysql.com/doc/mysql-ha-scalability/en/ha-memcached.html)





۵. امکان آرشیو کردن قسمتی از تیبل بزرگ ۱ میلیاردی شما هست؟


منظورتون از آرشیو کردن دقیقا چیه؟



نگه داری اطلاعاتی که «فعلا» بهشون احتیاج ندارم در یک جدول دیگه که جدول اصلی کوتاه و کوچک بمونه. دو تا مثال متفاوت می زنم. فرض کنید که در یک بیمارستان بزرگ دو جدول از بیماران و پزشکان داریم. پزشکان ثابت هستند و بیماران می رن و میان. بیمارستان دوست داره که آرشیو داشته باشه از همه رکورد ها پس نمی شه بیماران رو پاک کرد. join ما هم اتصال یک پزشک به یک بیمار هست one to many relationship . راه کار منتطقی این هست که به جای جستتجو در تیبل بیماران که هر روز بزرگتر می شه ... دو تیبل داشته باشیم: active patients and inactive / discharged patients. وقتی یک بیمار ترخیص شد منتقل کنیم به تیبل دوم که اصلا باهاش کاری نداریم در طول روز «شاید» یک روز یک رکورد رو بکشیم بیرون.

مثال دوم فیس بوک: استتوس ها رو در یک جدول، عکس ها در یکی، کامنت ها در یکی و این ها در جودل / دیتابیس / ماشین های متفاوت. در هنگام جستجو بدونیم که اگر دنبال کامنت هستیم در ماشین a1 هست و اگر دنبال کامنت در a2 هست .... به اینکار می گن sharding. رساله‌ی علمیه‌ی مربوطه:

MySQL Partitioning / Sharding / Splitting - which way to go? - Stack Overflow (http://stackoverflow.com/questions/45879/mysql-partitioning-sharding-splitting-which-way-to-go)






۶. با داشتن ۶۴ گیگ رم می تونید کل اطلاعات رو در رم بخونید و زمان دسترسی رو شدیدا کاهش بدید (تقریبا ۱۰

برابر)



اگر واقعا نمی شه کل دیتابیس رو تغییر داد پیشنهاد من استفاده از سرور با ۱۲۸ گیگ رم + ۱۶ سی پی یو و خوندن کل داستان در memcache هست


چطوری این کار رو انجام بدم؟

نصب و راه اندازی memcache خیلی پیچیده است و باید کلا کد هاتون رو عوض کنید و از مدل آرایه ای / شی ئی به مدل key value pair برید . اگر تیم تون آموزش کافی ندیده فقط دردسر هست ولی اگه دیگه این راه حل درست مشکل شما هست.





باید از apache lucene Or apache Solr OR استفاده کنید


در مورد ویژگی های اینا یه توضیح می دید؟
مثلا سرعت ورود رکورد در ثانیه


این ها متد ها و تکنولوژی های جستجو و ایندکسینگ داده های بسیار بزرگ هستند... مثلا ویکی پدیا از این روش برای جستجو در پایگاه داده اش استفاده می کنه. اگر داده های شما قابل ایندکس شدن باشن باید از این روند استفاده کنید. توضیح المسائل:

Apache Lucene - Apache Solr (http://lucene.apache.org/solr/)




در facebook از mysql به شکل grid - clustered database استفاده می شه.... یعنی یک کوئری می دونه که کدوم سرور حاوی کدوم اطلاعات هست و می ره از اون می پرسه.


وقتی می خواد ببینه دوستای من چه مطالبی نوشتن که به من نشون بده حتما JOIN استفاده کردن دیگه، اونم JOIN دو جدول چند میلیارد رکوردی!!!!!!
نمیشه یه کاری کرد مثل فیس بوک قدرتمند بشه؟

این رو اون بالا توضیح دادم . فیس بوک از sharding / splitting استفاده می کنه. کلا روش کار دیتابیس در حالت cluster کاملا متفاوت هست و شما بخشی از عملیات جستجو رو با منتطقی کردن (follow the convention in a logical manner ) انجام می دید. و با فاکتور های عددی توان پردازش رو انجام میدید. یک مثال ساده عملی می زنم که روشن بشه.

فرض کنید ما در حال ساخت پایگاه داده‌ی خلافی های ماشین ها در ایران هستیم. به طور خلاصه تقریبا ۱۰ میلیون ماشین در ایران وجود داره که هر کدوم ۱۰ تا خلاف مرتکب شدند. که می شه ۱۰۰ میلیون رکورد. حالا ما می خوایم پیدا کنیم ماشین هایی از تهران رو که در تعطیلات نوروزی با ماشین هایی از بوشهر در شهر شیراز (بر اثر مصرف بالای شیر + عسل !!! هوای خوب + شعر ) تصادف کردند و هر دو مقصر بودند. فرض کنید که ماشین ها صرفا با شماره پلاک ملی شناخته می شن (اون آبی جدید ها).

در حالت برنامه نویسی ساده و دیزاین ساده و غیر پیچیده و بسیط دیتابیس، شما یک جدول دارید به این صورت :

پلاک خودرو مقصر / نوع خلاف ENUM / محل تصادف / پلاک ماشین متلاشی شده

برای پیدا کردن کوئری بالا شما نیاز دارید به:




SELECT * from accidents ac1 JOIN accidents ac2 ON ac1.accused = ac2.nonAccused WHERE city = shiraz AND ac1.vin IS FROM TERHAN and ac2.vin IS FROM BOOSHEHR



خب، چقدر طول می کشه؟ به طور ساده دو بار باینری سرچ رو باید در هم ضرب کرد! N^2 * log N * log N که برای ۱۰۰ میلیون رکورد می شه 10^8 * 10^8 * 8 * 8 که می شه نزدیک به 10 to the power of 17 یا به عبارتی ۱۰۰ میلیارد میلیون عملیات!

ولی حالا آقای باهوش زاده میاد و دیتابیس رو به این شکل فرمت می کنه که

accidents_fromTehran

accidents_fromMashahr

accidents_fromBooshehr

و همینطور بر اساس پلاک ماشین ها جدول های مختلف می سازه.

حالا همون کوٍئری رو با جدول های کوچک تر تکرار کنیم:




SELECT * from accidents_fromTehran ac1 JOIN accidents_fromMashahr ac2 ON ac1.accused = ac2.nonAccused WHERE city = shiraz


و این بار در جدول های بسیار کوچک تر باید بگردیم. با فرض ۱۰۰ شهر در ایران داشتن تعداد ماشین -خلاف های هر شهر / تعداد رکوردهای هر تیبل ۱ میلیون هست و با محاسبه بالا: 10^6 * 10^6 * 6 * 6 نزدیک ۱۰ به توان ۱۲

یه تقسیم کنید می بینید که ۱۷ -۱۲ = ۵ و یعنی تقریبا ۱۰ هزار برابر سریعتر هست.

راه کار چی بود؟ ما بخشی از جستجو رو بر مبنای منتطق ( اینکه می تونیم با پلاک ماشین از اول شهرش رو تشخیص بدیم و بگداریممش در تیبل خودش) ساده کردیم.

بازم اگر سوالی بود درخدمتم و از بحث تخصصی شدیدا استقبال می کنم. در اولین ریپلایتون قضیه میلیارد و میلیون رو شرح بدید و اگر می تونید table structure رو اینجا بگذارید.

[compiler]

ممنون CPU که معرفی کردید واقعا دوست داشتنیه

نسخه های Enterprise MySQL چه فرقی دارند؟

دقیقا Nginx چه کمکی به بهبود سرعت می کنه؟

اگه اشتباه نکنم php-fpm پردازش PHP روی thread هست. من توی پردازش MySQL مشکل دارم نه PHP

چه فرقی می کنه؟

در کنار هارد SSD خود به خود مشکلاتتون رو حل خواهد کرد . بخش عمده ای از مشکلات شما سرعت هارده .... ۲۵۶ گیگ رم هم برای این دیتابیس با Memcache سرعت رو به میزان قابل توجهی افزایش میده و حجم پردازش رو هم خیلی خیلی کمتر خواهد کرد .


http://www.mysql.com/products/enterprise (http://www.mysql.com/products/enterprise/)

لینک فوق برتری ها و مزیای نسخه Enterprise رو معرفی کرده . البته به عنوان کارشناس خودتون باید بررسی کنید ولی ۱۰۰٪ تاثیر زیادی در بهبود Peformance خواهد داشت .

php-fpm + nginx رو هم صرفا به دلیل مصرف کمتر منابع در کنار سرعت بیشتر معرفی کردم . اگل کل مجموعه بهینه سازی بشه بهتره...

اوبونتو هم سبک تر و به روز تره . به عقیده شخصی من سرعتش هم بیشتره .

[TukaNet]

در شرایط یک کاربر و یک کوئری جواب میده؟

چطور می تونم راه اندازی کنم؟

راه کاری که عرض کردم اصلا به تعداد کاربر و نوع استفاده شما بستگی نداره

بعد از عمل کلاسترینگ یا تشکیل مجموعه رایانش ابری در نهایت یک سرور خواهید داشت

قدرت پردازش و میزان منابع این سرور برابر هست با جمع قدرت سرورهایی که در این مجموعه هستند

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