أفضل إعدادات لملف my.cnf في خادم لينكس

من المعروف لمدراء الخوادم أن ملف الإعدادات “my.cnf” هو المسؤول عن كثير من إعدادات محرك قواعد البيانات MySQL أو InnoDB أو غيرها. ووضع إعدادات خاطئة أو الاعتماد على الإعدادات الافتراضية يسبب في كثير من الأحيان مشاكل كبيرة لا حصر لها، من استخدام خاطئ لموارد الخادم إلى ضعف الأداء في التعامل مع كم كبير من استعلامات قواعد البيانات ما يسبب في النهاية إلى انهيار الخادم وتوقفه بسبب إعدادات غير صحيحة.

يجب أن أنوه على أن الإعدادات التالية لا تناسب كل خادم، ذلك لاختلاف العتاد من خادم لآخر. فهناك خوادم بذاكرة عشوائية أكبر من غيرها وهناك خوادم بمعالجات أنوية متعددة (ثنائية، رباعية وغيرها). هذين العاملين مهمين جداً عند تغيير قيم المتغيرات في ملف my.cnf

سأحاول هنا شرح إعداد خادم Linux بذاكرة عشوائية 16GB ذو أربعة أنوية.

أولاً أين يوجد ملف my.cnf ؟

لو ذهبنا لموقع MySQL وقرأنا التعليمات هناك في الصفحة التالية (http://dev.mysql.com/doc/refman/5.7/en/option-files.html) هناك قسم 5.2 يذكر أماكن تواجد الملف my.cnf والذي يمكننا التعديل عليه:

هناك بعض برامج باش bash التي تساعدك في وضع إعدادات محرك قواعد البيانات MySQL أو InnoDB تناسب استخدامك وتعتمد على آخر إحصائيات الخادم لقواعد البيانات لوضع قيم مناسبة. شخصياً استخدم اثنين لكن لا أعتمد كثيراً على القيم النهائية، لأنها فقط نصائح وليس بالضرورة تناسبك.

السكربتات هي:

• Tuning-Primer.sh
• mysqltuner.pl

الأول سكربت باش والثاني سكربت بيرل. كلاهما مفيد جداً إذا كنت لا تمتلك الخبرة الكافية لإعداد قواعد قيم MySQL.

قبل أن نبداً بتنصيب واستخدام البرنامجين لنقوم أولاً بإصلاح جميع قواعد البيانات في الخادم عن طريق الأمر التالي (سيقوم الأمر بإصلاح فقط قواعد MySQL وليس InnoDB لأنه لا يمكن استخدام هذا الأمر عليها:

 # mysqlcheck --check --auto-repair --all-databases 

لنقوم الآن بتثبيت mysqltuner على الخادم عن طريق الأوامر التالية:

# cd /usr/local/bin

# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl 

# chmod +x mysqltuner.pl

والآن نقوم بتثبيت tuning-primer على الخادم عن طريق الأوامر التالية:

# cd /usr/local/bin

# wget https://launchpadlibrarian.net/78745738/tuning-primer.sh

# chmod u+x tuning-primer.sh

الآن وبعد أن قمنا بتحميل كلا البرنامجين، وقبل تشغيلهما تأكد أنك أن الخادم كان يعمل على الأقل لمدة يوم كامل أو يومين.

طريقة تشغيل mysqltuner هي:

# cd /usr/local/bin
# /usr/local/bin/mysqltuner.pl

طريقة تشغيل tuning-primer هي:

# cd /usr/local/bin
# tuning-primer.sh

بعد تشغيل tuning-primer سيكون الناتج شيء مشابه للتالي وهي نتائج لكل عملية فحص للإعدادات في ملف my.cnf والقيم التي يجب أن تكون عليها.

ولو لاحظنا في البداية أن الخادم يعمل بدون انقطاع لمدة 5 أيام، وهنا ملاحظة أنه كلما قمنا بتشغيل البرنامج بعد عمل الخادم لمدة أكثر كلما كانت القيم أكثر دقة ومناسبة.

التعديلات في الإعدادات ستكون باللون الأحمر وإذا كانت الاعدادات سليمة ستكون باللون الأخضر.

وإتبع ماذا يقترح عليك السكربت، حتى تصل إلى أفضل إعدادات.

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.6.34-log x86_64

Uptime = 5 days 1 hrs 18 min 8 sec
Avg. qps = 47
Total Questions = 20634985
Threads Connected = 6

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 499879 out of 20635006 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled
Binlog sync is not enabled, you could loose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 50
Current threads_cached = 45
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 400
Current threads_connected = 6
Historic max_used_connections = 129
The number of used connections is 32% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 3 M
Current InnoDB data space = 3 M
Current InnoDB buffer pool free = 99 %
Current innodb_buffer_pool_size = 4.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 5.20 G
Configured Max Per-thread Buffers : 1.58 G
Configured Max Global Buffers : 4.69 G
Configured Max Memory Limit : 6.27 G
Physical Memory : 11.56 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 262 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 113
Key buffer free ratio = 50 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 512 M
Current query_cache_used = 297 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 58.04 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 512 K
Current read_rnd_buffer_size = 512 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 2364 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 65536 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 4000 tables
Current table_definition_cache = 4000 tables
You have a total of 2725 tables
You have 3984 open tables.
Current table_cache hit rate is 28%
, while 99% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 1.00 G
Current tmp_table_size = 1.00 G
Of 812930 temp tables, 42% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 512 K
Current table scan ratio = 58 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1695
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'

بعد تشغيل mysqltuner.pl سيكون الناتج شيء مشابه للتالي وفي الأخير سترى Recommendations وهنا ستجد القيم التي يستحسن منك تعديلها، طبعا هناك نتائج ونصائح أعلى النتائج حاول أيضاً اتباعها:

>>MySQLTuner 1.6.20 - Major Hayden <major@mhtx.net>
>>Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.34-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1G (Tables: 2524)
[--] Data in InnoDB tables: 6M (Tables: 115)
[--] Data in MEMORY tables: 0B (Tables: 4)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5d 1h 34m 8s (20M q [47.325 qps], 730K conn, TX: 104G, RX: 7G)
[--] Reads / Writes: 79% / 21%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory: 11.6G
[--] Max MySQL memory: 7.3G
[--] Other process memory: 507.8M
[--] Total buffers: 5.7G global + 4.0M per thread (400 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 6.2G (53.56% of installed RAM)
[OK] Maximum possible memory usage: 7.3G (62.71% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 2% (501K/20M)
[OK] Highest usage of available connections: 32% (129/400)
[OK] Aborted connections: 0.02%(153/730535)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (300 temp sorts / 806K sorts)
[!!] Joins performed without indexes: 2367
[!!] Temporary tables created on disk: 74% (606K on disk / 816K total)
[OK] Thread cache hit rate: 99% (136 created / 730K connections)
[OK] Table cache hit rate: 28% (3K open / 14K opened)
[OK] Open file limit used: 9% (6K/65K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
[OK] Binlog cache memory access: 100.00% ( 556 Memory / 556 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 52.1% (34M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/262.3M
[OK] Read Key buffer hit rate: 99.1% (589M cached / 5M reads)
[OK] Write Key buffer hit rate: 96.8% (112M cached / 3M writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 4.0G/6.7M
[OK] InnoDB buffer pool instances: 4
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.98% (4023136 hits/ 4023896 total)
[!!] InnoDB Write Log efficiency: 39.19% (1097 hits/ 2799 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1702 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Restrict Host for user@% to user@SpecificDNSorIp
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_type (=0)
join_buffer_size (> 2.0M, or always use indexes with joins)

وللتعديل على القيم نقوم باستخدام أي محرر، أفضل شخصياً pico كالتالي:

# pico /etc/my.cnf
ملف my.cnf الخاص بأحد خوادم مناري كالتالي:

#############################################
#DATE: 2016-10-29
#SITE: https://manary.net
#DESCRIPTION: MySQL config 5.6.x
#RAM: 16GB RAM dedicated server
#Connections: 400 connections
#By: MUSTAFA AL-YOUSEF
#############################################

[client]
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8

[mysqld]
## Charset and Collation
default-storage-engine = MyISAM
default-tmp-storage-engine = MyISAM
character-set-server = utf8
collation-server = utf8_general_ci
performance-schema = on
local-infile=0

## Files
back_log = 300
open_files_limit=10000
#open_files = 1024
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
skip-external-locking
skip-name-resolve

## Logging
datadir = /var/lib/mysql
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-index
#log = mysql-gen.log
log_error = mysql-error.err
log_warnings
log_bin = mysql-bin
slow_query_log=1
slow_query_log=1
slow_query_log_file=mysql-slow.log
log_queries_not_using_indexes
long_query_time = 10 #default: 10
max_binlog_size = 256M #max size for binlog before rolling
expire_logs_days = 4 #binlog files older than this will be purged

## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 512K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 512K #default: 2M, larger may cause perf issues
read_buffer_size = 512K #default: 128K, change in increments of 4K
read_rnd_buffer_size = 512K #default: 256K
join_buffer_size = 3M #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
## total per-thread buffer memory usage: 44160000K = 43.125GB

## Query Cache
query_cache_type = 1
query_cache_size = 512M #32MB for every 1GB of RAM
query_cache_limit = 4M #max query result size to put in cache

## Connections
max_connections = 400 #multiplier for memory usage via per-thread buffers
max_connect_errors = 10 #default: 10
concurrent_insert = 2 #default: 1, 2: enable insert for all instances
connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10
max_allowed_packet=268435456

## Table and TMP settings
max_heap_table_size = 1G #recommend same size as tmp_table_size
bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size
tmp_table_size = 1G #recommend 1G min

## Table cache settings
table_definition_cache = 4000
table_open_cache = 4000 #5.1.x, 5.5.x <default: 64>
#tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir

## Thread settings
thread_concurrency = 32 #recommend 2x CPU cores
thread_cache_size = 50 #recommend 5% of max_connections

## MyISAM Engine
key_buffer = 64M ## 128MB for every 1GB of RAM
myisam_sort_buffer_size = 64M #index buffer size for creating/altering indexes
myisam_max_sort_file_size = 64M #max file size for tmp table when creating/alering indexes
myisam_repair_threads = 4 #thread quantity when running repairs
myisam_recover = BACKUP #repair mode, recommend BACKUP

## InnoDB Plugin Independent Settings
innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size = 256M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128
innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4
innodb_buffer_pool_size = 4G #global buffer
innodb_additional_mem_pool_size = 4M #global buffer
innodb_status_file #extra reporting
innodb_file_per_table = 1 #enable always
innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID
innodb_table_locks = 0 #preserve table locks
innodb_log_buffer_size = 128M #global buffer
innodb_lock_wait_timeout = 60
innodb_thread_concurrency = 16 #recommend 2x core quantity
innodb_commit_concurrency = 16 #recommend 4x num disks
#innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush
skip-innodb-doublewrite
#innodb_force_recovery = 1
innodb_buffer_pool_instances = 4

## Binlog sync settings
## XA transactions = 1, otherwise set to 0 for best performance
sync_binlog = 0

[mysqldump]
quick
quote-names
max_allowed_packet = 128M

أتمنى أنني وفقت بالشرح وإذا كانت لديكم ملاحظات أسئلة أو مشاكل، اكتبوا تعليق على الموضوع وسأقوم إن شاء الله تعالى بالرد عليها.