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

[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

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


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

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

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

MySQL Partitioning / Sharding / Splitting - which way to go? - Stack Overflow

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

برابر)



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


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

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

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


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

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

Apache Lucene - Apache 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

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

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

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