بهینه سازی دیتابیس MySQL

بهینه سازی MySQL

اجرای MySQL با تنظیمات بهینه به ما کمک میکنه که لود سرور رو کاهش بدیم و مصرف منابع رو کم کنیم و از کاهش سرعت سرور جلوگیری بشه. به طور کلی بعد از بهینه سازی آپاچی برای اجرای درخواست های سنگین، لازم هست که تنظیمات مربوط به MySQL را نیز بهینه کنین. در این آموزش با استفاده از MySQLTuner دیتابیس رو بهینه میکنیم. پایگاه داده های MySQL بزرگ می تواند مقدار قابل توجهی از حافظه رم رو مصرف کنند. در این آموزش باید دسترسی به سرور داشته باشید که فقط با امکان خرید vps ممکن هست، یعنی در هاست اشتراکی چنین دسترسی ندارید.

MySQLTuner

اسکریپت MySQLTuner دیتابیس MySQL شما رو مورد ارزیابی قرار میده و بعد از اون پیشنهاداتش برای بهینه سازی و افزایش کارایی رو میده. برای نصب و راه اندازی طبق آموزش زیر پیش میریم

با دستور زیر MySQLTuner  رو دانلود میکنیم

wget http://mysqltuner.com/mysqltuner.pl

سپس پرمیژن رو تنظیم میکنیم

chmod +x mysqltuner.pl

سپس mysqltuner.pl رو اجرا میکنیم

./mysqltuner.pl

و خروجی مشابه زیر به شما نمایش خواهد داد.

         >>  MySQLTuner 1.4.0 - Major Hayden 
         >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
         >>  Run with '--help' for additional options and output filtering
        Please enter your MySQL administrative login: root
        Please enter your MySQL administrative password:
        [OK] Currently running supported MySQL version 5.5.41-0+wheezy1
        [OK] Operating on 64-bit architecture

        -------- Storage Engine Statistics -------------------------------------------
        [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
        [--] Data in InnoDB tables: 1M (Tables: 11)
        [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
        [!!] Total fragmented tables: 11

        -------- Security Recommendations  -------------------------------------------
        [OK] All database users have passwords assigned

        -------- Performance Metrics -------------------------------------------------
        [--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
        [--] Reads / Writes: 100% / 0%
        [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
        [OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
        [OK] Slow queries: 0% (0/113)
        [OK] Highest usage of available connections: 0% (1/151)
        [OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
        [!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
        [OK] Query cache prunes per day: 0
        [OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
        [OK] Thread cache hit rate: 97% (1 created / 42 connections)
        [OK] Table cache hit rate: 24% (52 open / 215 opened)
        [OK] Open file limit used: 4% (48/1K)
        [OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
        [OK] InnoDB buffer pool / data size: 128.0M/1.2M
        [OK] InnoDB log waits: 0
        -------- Recommendations -----------------------------------------------------
        General recommendations:
            Run OPTIMIZE TABLE to defragment tables for better performance
            Enable the slow query log to troubleshoot bad queries
        Variables to adjust:
            query_cache_limit (> 1M, or use smaller result sets)

مسیر تنظیمات فایل دیتابیس mysql جهت بهینه سازی در آدرس /etc/mysql/my.cnf است ، به همین دلیل قبل از انجام هرگونه تغییرات ، حتما از فایل بالا یک بکاپ تهیه کنین.

key_buffer

تغییر key_buffer حافظه بیشتری را به MySQL اختصاص می دهد، که می تواند پایگاه های داده شما را به طور قابل توجهی سریعتر کند در صورتی که حافظه آزاد داشته باشید. اندازه key_buffer باید بطور کلی نباید بیشتر از 25% مموری سیستم باشد وقتی که از موتور تیبل MyISAM استفاده میکنین و برای innoDB حداکثر باید 70% باشد، هنگامیکه این مقادیر بیش از اندازه تنظیم شوند منابع شما به هدر میرود.

با توجه به مستندات MySQL، برای سرورهای با 256MB (یا بیشتر) از RAM با تعداد زیادی جداول، تنظیم 64M توصیه می شود ، سرور ها با 128 مگابایت رم و جداول کمتر می توانند به مقدار پیش فرض 16M تنظیم شوند. وب سایت هایی با منابع و جداول حتی کمتر می توانند این مقدار را پایین تر بگذارند.

max_allowed_packet

این پارامتر به شما اجازه می دهد حداکثر اندازه یک بسته قابل انتقال را تنظیم کنید.یک بسته یک حالت SQL است که یک ردیف را به یک کاربر ارسال می شود.اگر می دانید که سرور MySQL شما  بسته های بزرگ را قرار است پردازش کند ، بهتر است این را به اندازه بزرگترین بسته افزایش دهید . اگر این مقدار بیش از حد کوچک باشد، خطایی در فایل error log دریافت خواهید کرد.

thread_stack

این مقدار حاوی اندازه پشته (stack ) برای هر موضوع است.MySQL مقدار پیش فرض متغیر thread_stack را برای استفاده عادی کافی می داند؛با این حال، اگر یک خطا مربوط به thread_stack ثبت شود، می توانید این مقدار را افزایش دهید.
thread_cache_size

اگر thread_cache_size “غیرفعال” (برابر 0) باشد ، سپس هر اتصال جدیدی که ایجاد می شود نیاز به یک رشته (thread) جدید است. وقتی کانکشن ها قطع بشن ، رشته (thread) نیز از بین میرن. در غیر اینصورت این thread ها در یک کش ذخیره میشود تا زمانیکه یک کانکشن برقرار بشه.به طور کلی این تنظیم تأثیر کمی در عملکرد دارد مگر اینکه شما صدها کانکشن را در هر دقیقه دریافت کنید ، این زمان این مقدار باید افزایش یابد بنابراین اکثر کانکشن ها را می توان روی رشته های ذخیره شده بارگزاری کرد.

max_connections

این پارامتر حداکثر مقدار اتصالات همزمان را تنظیم می کند. بهتر است قبل از تنظیم این شماره، حداکثر تعداد کانکشن هایی که در گذشته داشته اید را در نظر بگیرید، بنابراین شما می بایست بین آن تعداد کانشکن بالا بالا و مقدار max_connections بافر ایجاد کنید.توجه داشته باشید،که این شامل حداکثر تعداد کاربران آنلاین در سایت شما نیست ؛ بلکه حداکثر تعداد کاربرانی که درخواست دارند ، نشان می دهد.
table_cache

این مقدار باید بالاتر از مقدار open_tables شما باشد. برای تعیین این مقدار از دستور زیر میتونین استفاده کنید:

SHOW STATUS LIKE 'open%';