از آنجایی که مدتی‌ست بطور جدی مشغول برنامه‌نویسی وب با ساختار LAMP هستم، و مسلما یکی از مهارت‌های هر توسعه‌ی دهنده‌ی وب که قصد داشته باشد توسعه‌دهنده‌ی full-stack شود کار با پایگاه‌های مختلف و نوشتن query‌های بهینه است. از آنجایی سرعت اجرای دستورات sql بسیار بالاست، مخصوصا اگر دیتابیس مذکور رکوردهای زیادی نداشته باشه. هرچند در همین حالت هم امکان سنجشِ بهینگی دستورات و زمان اجرای آنها بصورت عملی وجود دارد، ولی با اینحال لذت کار کردن با یک دیتابیس بزرگ از نعمت‌هایی‌ست که نمی‌توان در مقابلش مقاومت کرد!

برای داشتن یک دیتابیس بزرگ می‌توانید یک وب اپلیکیشن توسعه دهید و منتظر بمانید تا کاربران ثبت‌نام کنند و به تولید محتوا بپردازند و … . یا اینکه خودتان یک دیتابیس بسازید و آن را با داده‌های تقلبی(fake) پر کنید، که باز هم جذاب نیست. و اما بهترین روش استفاده از یک دیتابیس open-source است که بصورت آزاد منتشر شده و می‌توانید به راحتی و رایگان، بدون اینکه عذاب وجدان داشته باشید از آن استفاده کنید. و من از دیتابیس گنجور که حدود یک و نیم میلیون رکورد از اشعار پارسی‌زبانان دارد استفاده کردم. که از سایت گنجور به راحتی قابل دسترس و دانلود هست. هرچند که در وبسایت متن‌باز بودن گنجور ذکر شده بود با اینحال باز هم با ایمیل از اجازه‌ی استفاده از دیتابیس‌شون اطمینان حاصل کردم تا خیالم از این بابت راحت باشه.

تبدیل sqlite به sql

و اما دیتابیسی که من دانلود کردم sql نبود و sqlite بود، که یک ورژن سبک‌تر از همون sql هست که بیشتر در تلفن‌های همراه استفاده میشه و من قبلا در اندروید باهش کار کرده بودم. هرچند میشه از sqlite در وب، حداقل با php که مشکل خاصی نداره استفاده کرد، ولی هدف من داشتن یک دیتابیس sql واقعی بود تا شرایط واقعی‌تر باشه! پس مجبور شدم sqlite رو به sql تبدیل کنم، که بعد از کمی گوگل کردن به این راه‌حل رسیدم؛ یک اسکریپت پایتون با چاشنی bashscript که در عرض چند ثانیه دستورات sqlite رو به mysql تبدیل می‌کنه، البته این اسکریپت چندتا مشکل داشت که من اون مشکلات رو در فایل نهایی sql با replace all در sublime text حل کردم.

آپلود دیتابیس بزرگ روی هاست اشتراکی

حالا این دیتابیس رو به روشی نه‌چندان سریع به mysqlای که روی لوکال دارم ایمپورت کردم! از این نظر می‌گم نه چندان سریع که فکر کردم خیلی راحت در commandline می‌نویسم:‌

mysql -u username -p database_name < file.sql

و نهایتا بعد از چند ثانیه import میشه. ولی اجرای این دستور حدود ۲۰ ساعت زمان برد!!! البته روش‌هایی هست که این زمان را کاهش داد که البته من تست نکردم ولی در پاسخ‌ها و نظراتی که دیدم تاثیر معجزه‌ آسایی نمی‌توان در کاهش این زمان اعمال کرد. همچنین import کردن چنین دیتابیس‌هایی با phpmyadmin هم چندان منطقی و بهینه نیست و حتی براحتی هم ممکن نیست! (البته در پارگراف بعد هم منطقی و هم ممکن میشه!)

حالا اگر قرار بود که پروژه‌ی نهایی روی سرور خود‌مون (vps یا vds) باشه مسئله خیلی راحت‌تر بود. ولی هدف من این بود که این دیتابیس را روی یک هاست اشتراکی آپلود کنم، که باید با phpmyadmin کار می‌کردم و مجبور به تن دادن به مشکلاتی و محدودیت‌هایی مثل upload_max_filesize و max_execution_time و … بودم. منطقی‌ترین روشی که به ذهنم رسید شکستن یک فایل بزرگ به چند فایل sql کوچک و آپلود و ایمپورت اونها بصورت تکی بود. پس با دستور:

split -l 200000 ./dump.sql ./file-

در ترمینال xubuntu خوب و دوست‌داشتنی فایل sql ای که یک‌ونیم میلیون خط داره رو به فایل‌های  ۲۰۰,۰۰۰ خطی تبدیل می‌کنیم. که باز البته نیاز داره که هر فایل رو با دستورات mysql کمی مرتب کنیم تا قابل اجرا باشه و خطا نداشته باشه. و باز بهتر هست که هر فایل رو فشرده کنیم تا حجمش کمتر بشه و سرعت‌ آپلود‌مون بالابره، پس برای هر فایل دستور:

gzip -v file.sql

را اجرا می‌کنیم و حالا می‌تونیم براحتی فایل‌های چندمگابایتی کوچک‌مان را براحتی با phpmyadmin روی هاست اشتراکی‌مون آپلود کنیم.

نکته: معمولا اگر فایل ‌sql را به پشتیبان هاست‌تون بدید می‌تونن خیلی راحت import کنند و نیازی به این همه دردسر نیست:)

بیت‌ها، یک پروژه‌ی آخر هفته‌ای

و تمام این‌ها منجر شد به اینکه چند خط هم کدنویسی کنم و با codeigniter در back-end و bootstrap در front-end یک وبسایت راه‌بندازم، که البته خیلی ساده هست و هنوز کلی هم باگ داره، ولی گوگل چندهزار صفحه‌ ازش ایندکس کرده 🙂

صفحات را کش کنیم

و در نهایت هرچقدر هم که query های خوبی بنویسیم باز هم زمانی که حجم اطلاعات زیاد باشند زمان پاسخ طولانی خواهند بود، حتی وقتی که زمان اجرای query پایین باشه ممکنه رندر صفحات بزرگ html برای سرور سنگین و وقت‌گیر باشند، از آنجایی که با یک خط کد ساده در codeigniter می‌توان هر صفحه‌ی وب (کنترلر با url منحصر به فرد) را کش کنیم، برای صفحات بزرگی مثل غزلیات صائب تبریزی که البته می‌توان با pagination به چند صفحه تقصیمش کرد! ولی درحال حاظر من بصورت یکجا حدود ۶ هزار مصرع رو در یک صفحه نمایش می‌دم! تفاوت زمان انتظار و لود در دو حالت بدون کش و با کش در تصویر زیر مشخص است.

پ.ن: بعضی از راه‌حل‌هایی که اینجا مطرح شده بهترین و بهینه‌ترین روش برای حل اون مسئله نیست، مخصوصا در طولانی مدت، بلکه بیشتر یک پاسخ سریع و ساده به مسئله‌ای‌ست که برای بار اول با آن مواجه شده‌ام. مسلما اگر این مسئله دغدغه‌ی روزمره‌مان شود به مرور می‌توانیم راهکارهایی بهینه و منطقی‌تر ارائه دهیم.