چرا تک کوئری های SELECT در SQL لزوما کار درستی نیست؟

فرض کنید یک سیستم دارید طراحی میکنید که قرار توی آدرس site.com/post/12 پست‌ایی با آی‌دی ۱۲ رو نشون بده.(طی این آموزش فرض کش کردن رو کلا در نظر نمیگیریم، اما در آخر توضیح میدم چطور این کار خیلی برای کش هم بهینه‌تر هست) احتمالا کاری که میکنید این هست توی اون route کوئری زیر رو اجرا میکنید:

SELECT post_title, post_content FROM posts WHERE id=12 LIMIT 0,1;

برای راحتی این آموش من مثال پست رو در نظر نمیگیرم. فرض کنید یک سایت کوتاه کننده لینک دارید میزنید، اینطور که هر لینک یک شناسه داره ( site.com/xgSy ) و توی دیتابیستون جدولی دارید که دو سطر random_key و url داره (random_key رو ایندکس میکنیم) که از طریق اون random_key به url اصلی دسترسی پیدا میکنیم و کاربر رو به اون آدرس redirect میکنیم.

خب حالا فرض کنید یک route نوشتید برای این کار و xgSy رو از کاربر گرفتید، حالت خیلی ساده اینه کوئری پایین رو اجرا کنیم:

SELECT url FROM urls WHERE random_key=xgSy LIMIT 0,1;

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

من توی دیتابیس ام ۱۰ میلیون ریکورد تصادفی تولید کردم و با go یک کد خیلی ساده نوشتم که یک route به شکل /single داشته باشیم که هر ریکوئستی که بهش زده شد یک آی دی تصادفی تولید کنه و اون رو از دیتابیس بگیره. کدش به شکل زیره:

func getSingle(db *gorm.DB) func(c echo.Context) error {
   return func(c echo.Context) error {
      id := rand.Intn(10000000)
      var url URL
      tr := db.Where(&quotrandom_key = ?&quot, id).First(&url)
      if tr.Error != nil {
         fmt.Println(tr.Error)
         return c.String(http.StatusInternalServerError, url.URL)
      }
      return c.String(http.StatusOK, url.URL)
   }

بنچ مارک اولی که میگیرم برای ۲۰ هزار ریکوئست ولی با کانکارنسی ( ریکوئست های همزمان ) ۲۰ هست. یعنی همزمان فقط ۲۰ تا ریکوئست سمت سایت ما میاد. ( برای این کار هم از Apache benchmark استفاده میکنم )

$ ab -n 20000 -c 20 http://localhost:1323/single   
Requests per second:    4076.20 [#/sec] (mean)
Time per request:       4.907 [ms] (mean)
Time per request:       0.245 [ms] (mean, across all concurrent requests)
Percentage of the requests served within a certain time (ms)
  50%      5
  66%      6
  75%      6
  80%      6
  90%      7
  95%      8
  98%     10
  99%     12
 100%     23 (longest request)

با این بار کم تونستیم ۴ هزار ریکوئست برثانیه رو هندل کنیم! عدد خوبیه. 99th percentile مون هم ۱۲ میلی ثانیست با میانگین ۵ میلی‌ثانیه. عدد های خوبی هستن. اگه نیازمون در همین حده ( یعنی نهایتا ۲۰ ریکوئست همزمان ) که این روش جوابه و دیگه نیازی به تغییر نیست. اما اگه همزمان ۲ هزار ریکوئست رو قرار بود هندل کنیم چه اتفاقی میوفته؟ ارور میخوریم و دیتابیس پاسخگو نیست D: علت چیه؟ توی MySQL ما یک limit داریم روی تعداد کانکشن های همزمان. کامند زیر رو اگه اجرا کنید احتمالا بهتون ۱۵۰ این حدودا میده. یعنی پیشفرض دیتابیس ۱۵۰ کانکشن همزمان رو پشتیبانی میکنه.

mysql> show variables like 'max_connections';

اما میتونیم این عدد رو بیشتر کنیم تا بتونیم تست کنیم واقعا سیستم ما روی بار زیاد چطور عمل میکنه؟

mysql> SET GLOBAL max_connections = 2500;

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

Requests per second:    872.82 [#/sec] (mean)
Time per request:       2291.436 [ms] (mean)
Time per request:       1.146 [ms] (mean, across all concurrent requests)
Percentage of the requests served within a certain time (ms)
  50%     79
  66%    118
  75%    210
  80%    502
  90%   1114
  95%   1177
  98%   3070
  99%   3159
 100%   7319 (longest request)

این پرفورمنس چیزی نیست که ما بخوایم. ریسپانس تایم برای 99th percentile روی ۳ ثانیست! اما مشکل این حرکت چیه؟

• اگه قرار باشه ارتباط ما با دیتابیس ۵۰۰ میکروثانیه طول بکشه، برای ۲ هزار کانکشن همزمان ایی که داریم حدود یک ثانیه رو از دست میدیم.
• وقتی میخوایم چند تا ریکوئست رو در لحظه بگیریم، چرا چند تا چند تا بفرستیم سمت دیتابیس؟ همه رو با هم بفرستیم!

دریافت اطلاعات از دیتابیس به شکل دسته‌ایی (Batch)

مسئله رو دوباره مطرح کنم. فرض کنید یک سیستم دارید با لود بالا که در لحظه چند هزار درخواست با هم میاد. حالا ما بیایم یه کاری کنیم، تمام اون درخواست ها رو با هم بفرستیم سمت دیتابیس. دیگه تک تک نفرستیم. برای این کار یک بنچ مارک ریز گرفتم. اگه شما ۱۰ هزار تا SELECT تکی بزنید به دیتابیس حدود ۱.۷ثانیه طول میکشه. اما اگه همه رو با هم بگیری ۷۰میلی‌ثانیه طول میکشه.

اگه دقیق تر بخوایم بررسی کنیم کلمه در لحظه گنگ هست. یعنی شاید فاصله بین دو ریکوئست چند هزارم ثانیه باشه، ایا این ها در لحظه هستن؟ ما باید یک پنجره زمانی مشخص کنیم. هر ریکوئستی که توی اون پنجره زمانی اومد با بقیه ریکوئست های اون پنجره در لحظه در نظر میگیریم. این طبیعتا یک delay به سیستم ما اضافه میکنه. یعنی اگه اون پنجره رو ۵۰ میلی‌ثانیه در نظر بگیریم، هر میانگین پاسخ ما به سیستم حدود ۵۰ میلی‌ثانیه هست.
این نکته رو باید در نظر داشت. این سیستم ایی که پیاده میکنیم سیستمی نیست که در مرز سیستم با کاربر در ارتباطه، سیستمی که طراحی میکنیم معمولا provider برای cache هست که از دیتابیس اطلاعاتی که توی کش نیست رو میریزه توی کش. یعنی اگر کاربر ما یک صفحه رو میخواد ببینه و توی کش نیست به این سیستم میگیم فلان آی دی توی کش نیست برای ما provide کن و کاربرای بعدی دیگه دیتا رو از cache میخونن.

برای پیاده سازی سیستم من از زبان Go استفاده میکنم ( یکم هم قدرت Go رو توی طراحی سیستم ها نشونتون بدم :) )

اول یک دور ببینیم چه چیزی میخوایم طراحی کنیم:

• توی Route برنامه هر آی‌دی جدیدی که دریافت کردیم به سیستممون میدیم که فلان آی‌دی رو به من بده. اون در جواب به من یک چیزی (توی Go ما با Channel ها سروکار داریم) میده که میتونیم روش Listen کنیم و تا وقتی که جواب توش ریخته میشه ترد (Thread) فعلی برناممون که میخواد پاسخ HTTP رو به کاربر بده متوفق میشه. مثل پترن Async/Await. از اونجایی که من نمیخوام وارد جزئیات Go بشیم مفهوم این بخش رو اینطور در نظر بگیرید. ما یک متغیر داریم که خالیه. میتونیم بگیم تا وقتی پر نشده ترد ما بلاک شه.
• حالا سیستم ما طوری پیاده سازی شده که در لحظه آی‌دی های جدید رو میگیره، هر ۲۰ میلی‌ثانیه یک بار (همون پنجره زمانی که توضیح دادم) تمام آی‌دی هایی جدیدی که گرفته رو با یک کوئری از دیتابیس میگیره.
• حالا که تک‌تک جواب ها رو داریم به ترتیب توی اون متغیر هایی که در مرحله اول ساخته بودیم میریزیم، اون ترد چون دیتارو گرفته ادامه پیدا میکنه و HTTP Response رو به کاربر میده.

یک نمونه کد و بنچ مارک

برای این موضوع من یک نمونه کد Go آماده کردم که روش بنچ‌مارک بگیرم.

func NewSQLProvider(db *gorm.DB) *SQLProvider {
   sp := &SQLProvider{
      fetch:        make(chan int, MaxSPS*10),
      promises:     make(map[int][]chan<- *URL),
      promisesLock: sync.Mutex{},
      db:           db,
   }
   go sp.Work()
   return sp
}

تایپ SQLProvider ما یک چنل به نام fetch داره که آی‌دی هایی که قرار بود Provide شن رو توی خودش نگه‌میداره. همونطوری که گفتیم سیستم ما دو بخش هست. بخشی که درخواست های URL های جدید رو براساس آی دی دریافت میکنه و بخش دوم که آی‌دی ها رو با هم از دیتابیس میگیره.

برای طراحی بخش اول ما یک مپ به اسم promises داریم. هر وقت متد Provide اجرا میشه یک چنل میسازیم که اون سمت Route ما روش Listen کنه و درعین حال ما توی مپ promises با آی‌دی لینک نگهش میداریم که بعدا که Work اون لینک رو از دیتابیس گرفت، از طریق مپ بهش دسترسی داشته باشیم و دیتا رو بریزیم توش و Route ما پاسخ بده:

func (sp *SQLProvider) Provide(id int) <-chan *URL {
   sp.promisesLock.Lock()
   defer sp.promisesLock.Unlock()
   promise := make(chan *URL, 1)
   if _, founded := sp.promises[id]; !founded {
      sp.promises[id] = make([]chan<- *URL, 0)
   }
   sp.promises[id] = append(sp.promises[id], promise)
   sp.fetch <- id
   return promise
}

و در نهایت Work ما که هر لحظه چک میکنه که از آخرین باری که Provide کرده چقدر گذشته که اگه اون پنجره زمانی به اتمام رسید یا به حداکثر ظرفیت رسیدیم شروع کنه Provide کردن.

func (sp *SQLProvider) Work() {
   ch := sp.fetch
   lastFetch := time.Now()
   for {
      // if true then we reached time or size limit
      if len(ch) > MaxSPS || time.Now().Sub(lastFetch) > (MaxSelectWait*time.Millisecond) {
         fLen := int(math.Min(float64(len(ch)), MaxSPS))
         lastFetch = time.Now()
         if fLen == 0 {
            continue
         }
         // make array of url ids
         ids := make([]int, 0, fLen)
         for i := 0; i < fLen; i++ {
            select {
            case id := <-ch:
               ids = append(ids, id)
            }
         }
         // run provider
         var urls []*URL
         sp.db.Where(&quotrandom_key IN ?&quot, ids).Find(&urls)
         providedUrls := make(map[int]bool)
         // response provided urls
         for _, url := range urls {
            sp.promisesLock.Lock()
            if cc, founded := sp.promises[url.RandomKey]; founded {
               providedUrls[url.RandomKey] = true
               sp.response(cc, url)
            }
            sp.promisesLock.Unlock()
         }
         // response not founds
         for _, id := range ids {
            if _, provided := providedUrls[id]; !provided {
               if cc, founded := sp.promises[id]; founded {
                  sp.response(cc, nil)
               }
            }
         }
      }
   }
}
func (sp *SQLProvider) response(cc []chan<- *URL, url *URL) {
   for _, c := range cc {
      go func(c chan<- *URL, url *URL) {
         select {
         case c <- url:
         default:
         }
      }(c, url)
   }
}

و در نهایت Route ایی که باهاش بنچ‌مارک میخوایم بگیریم:

// Handler
func getBatch(db *gorm.DB) func(c echo.Context) error {
   sp := NewSQLProvider(db)
   return func(c echo.Context) error {
      id := rand.Intn(10000000)
      urlPromise := sp.Provide(id)
      select {
      case url := <-urlPromise:
         return c.String(http.StatusOK, url.URL)
      case <-time.After(1 * time.Second):
         fmt.Println(&quotError &quot, id)
         return c.String(http.StatusNotFound, &quotNot founded :(&quot)
      }
   }
}

خب، بنچ‌مارک بگیریم؟

Requests per second:    14361.56 [#/sec] (mean)
Time per request:       139.261 [ms] (mean)
Time per request:       0.070 [ms] (mean, across all concurrent requests)
Percentage of the requests served within a certain time (ms)
  50%    131
  66%    140
  75%    147
  80%    152
  90%    165
  95%    190
  98%    209
  99%    215
 100%    258 (longest request)

رسیدیم به ۱۴هزار ریکوئست در ثانیه که هم open connection های دیتابیسمون خیلی کم شد ( از ۲۵۰۰ رسیدیم به یک!) و هم درخواست های بیشتری رو میتونیم هندل کنیم، و هم از لحاظ زمانی 99th percentile سیستم خیلی بهتر عمل میکنه و از همه مهم تر، سیستم رو به امان خدا ول نکردیم، میتونیم به بهترین شکل Tune کنیم و کاملا به سیستم مسلط هستیم.

در ادامه چه کنیم؟

• دو تا نکته هست. احتمالا وقتی به چنین طراحی ایی نیاز دارید روی سیستم بزرگی کار میکنید که شامل چندین سرویس مختلف هست. چطور قرار این سیستم رو بشکونیم به دو سیستم؟ انشالله توی مقاله ایی در آینده یک طراحی خفن با RabbitMQ میریم که بتونیم سیستم رو بشکونیم به ۲ سرویس.
• باز هم تاکید کنم، این سیستم بیشتر برای Provide کش هستش و از اونجایی که همیشه میخوایم Response Time زیر ۵ ثانیه باشه باید حتما پاسخ ها کش بشه.