roshandelpoor · September 15, 2024 08:25 · Sep 14, 2024 · Sep 14, 2024
diff --git a/README.md b/README.md
@@ -0,0 +1,136 @@
+# MySQL Configuration Guide
+
+## Overview
+
+This document provides a comprehensive explanation of the MySQL configuration settings used in the provided configuration file. The settings are optimized for a high-performance MySQL server with a focus on balancing memory usage, CPU utilization, and disk I/O efficiency. The comments in the file also include guidelines for adjusting parameters based on server hardware and workload characteristics.
+
+## General Settings
+
+### `innodb_buffer_pool_size`
+- **Description**: The size of the InnoDB buffer pool, which caches data and indexes from InnoDB tables.
+- **Calculation**: Set to 60-70% of the available RAM. In this case, it's set to 26G based on 43GB of total RAM.
+
+### `innodb_buffer_pool_instances`
+- **Description**: The number of memory instances for the InnoDB buffer pool.
+- **Calculation**: Set to CPU cores divided by 2. Here, it's set to 24 for a 48-core CPU.
+
+### `innodb_log_file_size`
+- **Description**: The size of each InnoDB log file. Larger values reduce disk I/O but increase recovery time.
+- **Calculation**: Using `(RAM * 0.5) / (Number of Log Files)`, the value is set to 8G. Ensure that the value is a power of 2 for optimal performance.
+
+### `innodb_log_buffer_size`
+- **Description**: The size of the buffer used to store logs before writing them to disk.
+- **Calculation**: Set to the minimum of 64MB or 1% of total RAM. In this case, it is set to 64MB.
+
+### `innodb_flush_log_at_trx_commit`
+- **Description**: Controls the balance between performance and data durability.
+- **Value**: Set to `1` for maximum data durability.
+
+## Performance Tuning
+
+### `max_connections`
+- **Description**: The maximum number of connections MySQL allows at the same time.
+- **Calculation**: For web applications, set to `((CPU_CORES * 2) + (total RAM_GB * 2)) * 2`. In this case, it's set to 576.
+
+### `thread_cache_size`
+- **Description**: Caches client threads to reduce the overhead of thread creation.
+- **Calculation**: Set to 10% of `max_connections`, resulting in a value of 58.
+
+### `table_open_cache`
+- **Description**: The number of tables MySQL keeps open. 
+- **Calculation**: Set to `max_connections * 5`, which results in 2880.
+
+### `tmp_table_size` and `max_heap_table_size`
+- **Description**: Control the maximum size of temporary tables in memory.
+- **Calculation**: `tmp_table_size` is set to the nearest power of 2 greater than 102MB, which is 128MB. `max_heap_table_size` is set to 8MB based on 10% of available RAM divided by the number of connections.
+
+## Cache Settings
+
+### `query_cache_type` and `query_cache_size`
+- **Description**: Query caching is disabled to avoid performance bottlenecks in modern multi-core systems.
+- **Value**: Both set to `0`.
+
+## InnoDB Settings
+
+### `innodb_flush_method`
+- **Description**: Controls how InnoDB writes data to disk.
+- **Value**: Set to `O_DIRECT` for HDD or RAID setups to bypass the OS cache.
+
+### `innodb_file_per_table`
+- **Description**: Stores each table in its own file, improving I/O performance.
+- **Value**: Set to `ON`.
+
+### `innodb_read_io_threads` and `innodb_write_io_threads`
+- **Description**: Controls the number of I/O threads for read and write operations.
+- **Calculation**: Based on CPU cores, read threads are set to 26, and write threads are set to 22.
+
+### `innodb_io_capacity` and `innodb_io_capacity_max`
+- **Description**: Limits the number of I/O operations per second.
+- **Calculation**: Based on the disk's IOPS, set to 4500 for standard operations and 9000 for peak I/O.
+
+## Logging & Monitoring
+
+### `slow_query_log`
+- **Description**: Enables logging of slow queries for performance analysis.
+- **Value**: Enabled with a threshold of 1 second. Queries not using indexes are also logged.
+
+### `log_error`
+- **Description**: Specifies the location of the error log.
+- **Value**: Set to `/var/log/mysql/error.log`.
+
+### `log_warnings`
+- **Description**: Increases the verbosity of warning logs.
+- **Value**: Set to `2` for more detailed logs.
+
+## Timeout Settings
+
+### `net_read_timeout` and `net_write_timeout`
+- **Description**: Control the timeouts for reading from and writing to the network.
+- **Calculation**: Set to 25 seconds, considering execution time, network latency, and a safety margin.
+
+## Character Set
+
+### `character-set-server` and `collation-server`
+- **Description**: Set the default character set and collation for the server.
+- **Value**: Set to `utf8mb4` and `utf8mb4_unicode_ci` for full Unicode support.
+
+## Connection Settings
+
+### `max_connect_errors`
+- **Description**: Limits the number of failed connection attempts before blocking a client.
+- **Calculation**: Set to 20% of `max_connections`, resulting in 115.
+
+## Buffer Settings
+
+### `key_buffer_size`
+- **Description**: Buffer for MyISAM index blocks.
+- **Value**: Set to 1MB since MyISAM is not used.
+
+### `sort_buffer_size`
+- **Description**: Buffer used for sorting operations like `ORDER BY` and `GROUP BY`.
+- **Calculation**: Set to 8MB.
+
+### `read_buffer_size` and `read_rnd_buffer_size`
+- **Description**: Buffers used for full table scans and random reads.
+- **Value**: Set to 16MB for both.
+
+### `join_buffer_size`
+- **Description**: Buffer used for joins without indexes.
+- **Calculation**: Set to 128MB based on the number of join tables and buffer sizes.
+
+## Other Settings
+
+### `table_definition_cache`
+- **Description**: Caches table definitions to improve performance when opening tables.
+- **Value**: Set to 2000, based on the number of tables and connections.
+
+---
+
+## Final Notes
+
+This configuration is optimized for a server with the following specifications:
+- **RAM**: 43GB
+- **CPU**: 48 cores
+- **Disk**: SSD with high IOPS
+
+The settings are designed for a balanced workload with a focus on high concurrency, efficient memory usage, and optimal disk I/O. Adjustments may be necessary based on your specific environment and workload. Always monitor performance metrics and adjust settings incrementally to avoid over-allocating resources.
diff --git a/mysql.cnf b/mysql.cnf
@@ -0,0 +1,190 @@
+[mysqld]
+# ------------------------------------------
+# GENERAL SETTINGS
+# ------------------------------------------
+; بین ۶۰ تا ۷۰ درصد از رم باقیمانده سرور
+; 43*0.6G 
+innodb_buffer_pool_size = 26G     
+; تعداد هسته‌های CPU / 2   
+innodb_buffer_pool_instances = 24
+; (RAM * 0.5) / (Number of Log Files)
+; Number of Log Files: تعداد فایل‌های لاگ InnoDB که به طور پیش‌فرض برابر با 2 است.
+; محدودیت‌ها:
+; مقدار innodb_log_file_size باید بین 1 مگابایت تا 512 گیگابایت باشد.
+; مقدار innodb_log_file_size باید به صورت توانی از 2 (مانند 1MB، 2MB، 4MB، 8MB، 16MB، …) تنظیم شود.
+; افزایش بیش از حد innodb_log_file_size می‌تواند منجر به افزایش زمان بازیابی (Recovery Time) در هنگام خرابی یا راه‌اندازی مجدد سرور شود.
+; ceil((43*0.5)/2) < 2^n
+innodb_log_file_size = 8G
+; min(64MB, (Total_RAM * 0.01))
+; مقدار پیش‌فرض innodb_log_buffer_size در MySQL 8، 16 مگابایت است که برای اکثر کاربردها مناسب است.
+; اگر سرور شما تراکنش‌های بزرگ (مانند عملیات بارگذاری انبوه یا UPDATE های بزرگ) را پردازش می‌کند، ممکن است افزایش این مقدار به 32 مگابایت یا 64 مگابایت کارایی را بهبود بخشد.
+; افزایش بیش از حد innodb_log_buffer_size می‌تواند منجر به هدر رفتن حافظه شود، زیرا هر نخ (Thread) یک کپی از این بافر را در حافظه نگه می‌دارد.
+; به طور کلی، نیازی به تنظیم innodb_log_buffer_size به مقادیر بیشتر از 64 مگابایت نیست، مگر در شرایط بسیار خاص با تراکنش‌های بسیار بزرگ.
+; min(64, (43G*0.01)) => min(64, 430) = 64
+innodb_log_buffer_size = 64M 
+; 1 => اطمینان بیشتر در مورد داده‌ها
+; 2 => سرعت بیشتر    
+innodb_flush_log_at_trx_commit = 1  
+
+# ------------------------------------------
+# PERFORMANCE TUNING
+# ------------------------------------------
+; ((CPU_CORES * 2) + (total RAM_GB * 2)) * (if web then 2 else 1)
+; خطاهایی مانند “Too many connections” یا “Connection refused” را بررسی کنید. وجود این خطاها نشان‌دهنده نیاز به افزایش max_connections است.
+; اگر بر اساس نظارت‌های انجام شده، نیاز به افزایش max_connections دارید، این کار را به تدریج انجام دهید. برای مثال، مقدار را 10٪ تا 20٪ افزایش دهید و عملکرد سرور را مجدداً بررسی کنید.
+; برنامه‌های با کوئری‌های کوتاه و سبک (مانند برنامه‌های وب) به max_connections بالاتری نسبت به برنامه‌های با کوئری‌های سنگین و طولانی (مانند گزارش‌گیری) نیاز دارند.
+; هر کاربر به طور بالقوه می‌تواند یک یا چند اتصال به دیتابیس ایجاد کند.
+; (48*2) + (96*2) = 96+192 = 288 * 2 = 
+max_connections = 576
+; تعداد ترد‌های کلاینت غیرفعالی را که سرور نگه می‌دارد تا در صورت نیاز مجدد، به جای ایجاد ترد جدید، از آن‌ها استفاده کند، تعیین می‌کند.
+; ceil(0.1 * max_connections)
+; برای تعیین بهترین مقدار برای thread_cache_size، لازم است که وضعیت سرور را با استفاده از دستوراتی مانند SHOW GLOBAL STATUS LIKE 'Threads_%'; مانیتور کنید. دو متریک کلیدی برای بررسی، Threads_created (تعداد ترد‌های ایجاد شده) و Threads_cached (تعداد ترد‌های موجود در کش) هستند. هدف این است که نسبت Threads_created به Connections کمتر از 10٪ باشد. اگر این نسبت بالاتر است، افزایش thread_cache_size می‌تواند مفید باشد.
+; ceil(0.1*576)
+thread_cache_size = 58
+; برای پروژه‌هایی با لود پایین یا سرورهایی با رم پایین:
+;       min(max_connections * N, (table_count + table_definition_cache))
+; برای پروژه‌هایی با لود بالا و رم متناسب:
+;       max_connections * tables_per_join
+; در فرمول اول:
+;   max_connections: حداکثر تعداد اتصالات همزمان به سرور MySQL است که در فایل کانفیگ تنظیم می‌شود.
+;   N: یک ضریب است که معمولاً بین 1 تا 10 انتخاب می‌شود. این ضریب نشان‌دهنده تعداد جدول‌هایی است که به طور متوسط هر اتصال باز می‌کند.
+;   table_count: تعداد کل جدول‌های موجود در تمام دیتابیس‌ها است.
+;   table_definition_cache: تعداد تعاریف جدول (Table Definition) که در حافظه نگهداری می‌شوند. این مقدار را می‌توان با فرمول table_count * 1.2 محاسبه کرد.
+; نحوه انتخاب ضریب N:
+;   برای سرورهای با رم کم (کمتر از 2GB)، مقدار N را برابر با 1 یا 2 انتخاب کنید.
+;   برای سرورهای با رم متوسط (2GB تا 32GB)، مقدار N را برابر با 4 یا 8 انتخاب کنید.
+;   برای سرورهای با رم بالا (بیش از 32GB)، مقدار N را برابر با 8 یا 16 انتخاب کنید.
+; 576 * 5
+table_open_cache = 2880
+; Min(Max(Total_Database_Size * 0.1, Largest_Table_Size * 0.2), Available_Memory * 0.1)
+; min(Max(1GB*0.1, 500*0.2), 43GB*0.1) = min(MAX(102, 100), 4403) = 102
+; در تمامی مقادیر مرتبط با حافظه توان ۲ بودن مقدار لحاظ شود چون باعث بهبود مدیریت حافظه و کاهش Memory Fragmentation می‌شود
+; نزدیک‌ترین توان ۲ به عدد ۱۰۲ و ترجیحا بزرگتر از آن = 128
+tmp_table_size = 128M
+; اگر از جداول حافظه‌ای استفاده نمیکنید:
+;       (Available_RAM * 0.1) / max_connections
+; اگر از جداول حافظه‌ای استفاده میکنید:
+;       Size_of_Largest_Memory_Table * 1.2
+; (43GB*0.1)/576 = 7.6M => نزدیک‌ترین توان۲ => 
+max_heap_table_size = 8M
+
+# ------------------------------------------
+# CACHE SETTINGS
+# ------------------------------------------
+; اگر write heavy هستید قطعا باید کوئری کش را غیرفعال کنید
+; اگر نیستید:
+;        در MySQL 8، Query Cache به طور پیش‌فرض غیرفعال است و استفاده از آن توصیه نمی‌شود.
+;       در سرورهای مدرن با چندین هسته CPU و تعداد زیاد نخ (Thread)،  Query Cache به دلیل نیاز به قفل‌گذاری سراسری (Global Lock) در زمان به‌روزرسانی، به شدت  کارایی خود را از دست می‌دهد. این قفل‌گذاری باعث می‌شود که تنها یک نخ بتواند در هر لحظه به کش کوئری دسترسی داشته باشد و سایر نخ‌ها  باید منتظر بمانند. 
+;       MySQL 8  با  ارائه  بهینه‌سازی‌های  گسترده در  موتور  ذخیره‌سازی  InnoDB،  استفاده از  ایندکس‌های  بهینه‌تر،  و  بهبود  کارایی  کوئری‌ها،  نیاز به  Query Cache  را  به  حداقل  رسانده است.
+query_cache_type = 0 
+query_cache_size = 0 
+
+# ------------------------------------------
+# INNODB SETTINGS
+# ------------------------------------------
+; در سیستم‌عامل‌های Unix/Linux، استفاده از O_DIRECT یا O_DSYNC می‌تواند کارایی بهتری نسبت به fsync داشته باشد.
+; در سیستم‌هایی با حافظه محدود یا I/O سنگین، استفاده از O_DIRECT می‌تواند به بهبود کارایی کمک کند.
+; در سیستم‌هایی که پایداری داده بسیار حیاتی است، بهتر است از O_DIRECT_NO_FSYNC استفاده نشود.
+; در صورت عدم اطمینان، گزینه پیش‌فرض fsync انتخاب مناسبی است.
+; برای HDD:
+;       innodb_flush_method = O_DIRECT: این مقدار باعث می‌شود که InnoDB از نوشتن مستقیم (Direct I/O) استفاده کند و از کش سیستم عامل (OS Cache) بایپس کند. این روش برای HDD مناسب‌تر است زیرا الگوی نوشتن متوالی دارد.
+; برای SSD:
+;       innodb_flush_method = fsync: این مقدار باعث می‌شود که InnoDB از سیستم‌کال fsync() برای نوشتن داده‌ها استفاده کند. این روش برای SSD مناسب‌تر است زیرا از مزایای سرعت بالای نوشتن تصادفی در SSD بهره می‌برد.
+; در صورت استفاده از RAID با کنترلر باتری (Battery-Backed RAID Controller):
+;       innodb_flush_method = O_DIRECT: در این حالت نیز استفاده از نوشتن مستقیم توصیه می‌شود.
+innodb_flush_method = O_DIRECT
+; off => همه جدول های inoddb در یک فایل بزرگ ذخیره می‌شوند
+; on => هر جدول در یک فایل که مدیریت فضای ذخیره سازی و I/O را بهتر می‌کند
+innodb_file_per_table = ON
+; Max(min(64, CPU cores/2), disk numbers) +- 10%
+; اگر read heavy هستید ۱۰درصد اضافه کنید
+; Max(min(64, 24), 4) + 10% = 24 + 2.4 = 26.4 => 26
+innodb_read_io_threads = 26
+; Max(min(64, CPU cores/2), disk numbers) +- 10%
+; اگر write heavy هستید ۱۰درصد اضافه کنید
+; Max(min(64, 24), 4) - 10% = 24 - 2.4 = 22.4 => 22
+innodb_write_io_threads = 22        # نخ‌های ورودی/خروجی برای نوشتن
+; مقدار صفر اجازه می‌دهد مدیریت همزمانی‌ها خودکار باشد
+innodb_thread_concurrency = 0
+; تعداد عملیات I/O (ورودی/خروجی) در ثانیه را تعیین می‌کند
+; IOPS_per_disk * num_disks * 0.9
+; IOPS_per_disk: تعداد عملیات I/O در ثانیه برای هر دیسک است. این مقدار به نوع دیسک بستگی دارد:
+;       برای دیسک‌های HDD، معمولاً بین 100 تا 200 IOPS است.
+;       برای دیسک‌های SSD، می‌تواند بسیار بالاتر باشد، معمولاً بین 5000 تا 50000 IOPS یا حتی بیشتر.
+; مقدار num_disks برابر با تعداد دیسک‌های فیزیکی موجود در سرور است که معمولاً به صورت RAID پیکربندی می‌شوند.
+; برای جلوگیری از اشباع کامل I/O از ظرفیت نود درصدی آن استفاده می‌شود.
+; 5000*1*0.9
+innodb_io_capacity = 4500
+; innodb_io_capacity * 2
+innodb_io_capacity_max = 9000
+
+# ------------------------------------------
+# LOGGING & MONITORING
+# ------------------------------------------
+slow_query_log = 1                  # لاگ کوئری‌های کند رو فعال کن که بتونی شناسایی و بهینه‌سازی کنی
+slow_query_log_file = /var/log/mysql/slow.log  # محل ذخیره لاگ کوئری‌های کند
+long_query_time = 1                 # کوئری‌هایی که بیش از 1 ثانیه طول می‌کشن لاگ می‌شن
+log_queries_not_using_indexes = 1   # کوئری‌هایی که از ایندکس استفاده نمی‌کنن رو لاگ کن تا ببینی کجاها نیاز به بهینه‌سازی داری
+log_error = /var/log/mysql/error.log  # محل ذخیره خطاهای MySQL
+log_warnings = 2                    # سطح هشدارها رو زیاد کن تا راحت‌تر مشکلات رو پیدا کنی
+general_log = 0                     # لاگ عمومی رو خاموش کن که منابع سیستمت رو زیاد مصرف نکنه
+general_log_file = /var/log/mysql/general.log  # محل ذخیره لاگ عمومی در صورت فعال بودن
+
+# ------------------------------------------
+# TIMEOUT SETTINGS
+# ------------------------------------------
+; max_execution_time + network_latency + safety_margin
+;   max_execution_time: حداکثر زمان اجرای یک کوئری در برنامه شما است. این مقدار باید با تنظیم max_execution_time در PHP (یا زبان برنامه‌نویسی سمت سرور شما) هماهنگ باشد.
+;   network_latency: تاخیر شبکه بین سرور دیتابیس و برنامه است. این مقدار به عواملی مانند فاصله فیزیکی، سرعت شبکه، و بار ترافیک بستگی دارد.
+;   safety_margin: یک حاشیه اطمینان اضافی برای در نظر گرفتن تغییرات احتمالی در تاخیر شبکه و زمان اجرای کوئری است.
+; 15 + 5 + 5
+net_read_timeout = 25
+net_write_timeout = 25
+
+# ------------------------------------------
+# CHARACTER SET
+# ------------------------------------------
+character-set-server = utf8mb4
+collation-server = utf8mb4_unicode_ci
+
+# ------------------------------------------
+# CONNECTION SETTINGS
+# ------------------------------------------
+; تعداد دفعات مجاز برای تلاش ناموفق اتصال از یک کلاینت (Client) را قبل از بلاک شدن آن کلاینت تعیین می‌کند
+; شمارنده خطاها زمانی ریست می‌شود که کلاینت با موفقیت به سرور متصل شود.
+;  افزایش بیش از حد max_connect_errors می‌تواند سرور شما را در معرض حملات DoS (Denial of Service) قرار دهد
+; max_connections * 0.2
+max_connect_errors = 115
+
+# ------------------------------------------
+# BUFFER SETTINGS
+# ------------------------------------------
+; اگر جدول MyISAM استفاده نمی کنیم:
+;       1M
+; اگر استفاده می کنیم:
+;       (Total MyISAM Index Size) * 1.2
+;               SELECT SUM(INDEX_LENGTH) 
+;               FROM information_schema.TABLES
+;               WHERE ENGINE = 'MyISAM';
+; این مقدار نباید بیشتر از ۵۰٪ رم در دسترس باشد
+key_buffer_size = 1M
+; این بافر برای عملیاتی مانند ORDER BY، GROUP BY، و DISTINCT استفاده می‌شود.
+; (available_memory * 0.1) / max_connections
+; (43GB*0.1)/576 => توان ۲ نزدیک => 
+sort_buffer_size = 8M
+;  بافر مورد استفاده برای خواندن رکوردها در هنگام اجرای کوئری‌های بدون استفاده از ایندکس را تعیین می‌کند. انتخاب مقدار بهینه برای این پارامتر می‌تواند کارایی کوئری‌های سنگین را بهبود بخشد.
+; sort_buffer_size = 2^n => read_buffer_size = 2^(n+1)
+read_buffer_size = 16M               # بافر خوندن برای اسکن جداول
+;  = read_buffer_size
+read_rnd_buffer_size = 16M           # بافر خوندن تصادفی برای اسکن‌های رندوم
+; avg_join_tables * (sort_buffer_size + read_buffer_size)
+; در نهایت نزدیک توان ۲ به عدد بالا
+; 5 * (16 + 8) = 
+join_buffer_size = 128M
+
+# ------------------------------------------
+# OTHER SETTINGS
+# ------------------------------------------
+; max_connections * tables_per_connection
+; tables_per_connection: میانگین تعداد جدول‌هایی است که در هر اتصال استفاده می‌شوند.
+table_definition_cache = 2000
diff --git a/story.md b/story.md
@@ -0,0 +1,24 @@
+# The Story Behind the Creation
+
+## Persian Version
+
+من برای نیازهای شخصی خودم همواره نیاز داشتم که MySQL را براساس منابع مختلف در سرورهای مختلف کانفیگ کنم.  
+و چون همیشه فرمول‌ها و نحوه محاسبه اعداد را فراموش می‌کردم، تصمیم گرفتم یک‌بار برای همیشه یک کانفیگ جامع به همراه توضیحات و فرمول‌ها بسازم که همیشه جلوی چشمم باشد و از من وقت کمتری بگیره.
+
+---
+
+## English Version
+
+One of my ongoing challenges in server management has always been configuring MySQL based on various resources across different servers. Since I often forget the formulas and how to accurately calculate the parameters, I decided to solve this problem once and for all.
+
+### The Result?
+
+A comprehensive configuration file that not only contains all the necessary settings but also includes detailed explanations and formulas. Now, whenever I need to configure a server, everything is right in front of me, and instead of spending a lot of time, I can easily optimize the servers in the shortest possible time.
+
+---
+
+## Contribute and Improve
+
+This configuration file has been a great time-saver for me, but there's always room for improvement!  
+If you have any suggestions, optimizations, or additional insights, feel free to contribute.  
+**Your input is more than welcome!**
No results found