Azar azar artırıp testler yaparak değişkenlerin değerlerini analiz edip gerekli değişiklikleri yapmak gerekir.
Hatasız MySQL kurulumu için aşağıdaki kodlar işinize yarayacaktır. Optimizasyon
MySQL yeniden başlatınca verdiği hata
-bash-4.2$ mysqld-debug
[Note] mysqld-debug (mysqld 5.6.35-debug) starting as process 45685...[/php]
Dizinleri ve yolu değişen kurallar aşağıdadır.
[php][mysqld]
basedir = /mysql/mysql-5.6.35/
collation-server = utf8_unicode_ci
character-set-server = utf8
datadir = /mysql/data/T02
explicit_defaults_for_timestamp
log-error = /mysql/log/errorlogT02.log
pid-file = /mysql/admin/T02.pid
port = 33002
secure-file-priv = /mysql/admin/secure
socket = /mysql/admin/T02.sock
tmpdir = /mysql/temp/T02/
user = mysql[/php]
MySQL 5.6.35 için uyarlanmış my.cnf kodları.
Makina özellikleri 4 GB of RAM and 4 Core CPU and CentOs 7 MySQL 5.6.35
Şimdi bu my.cnf dosyasını kullanıyoruz ve günlük 20-25k hiti var.
Dosyayı değişmeden önce mutlaka eski my.cnf dosyasını yedekleyiniz.
Servisin başlamaması ile ilgili herhangi bir hata almamışsanız, yeni my.cnf dosyanız aktif ve herhangi bir sorun bulunmamaktadır.
[php]
# ************************* #
# #
# Sphere "Moutarde" #
# #
# ************************* #
# ==================== #
# MySql Client #
# ==================== #
[client]
port = 3306
socket = mysql
# ----------------------- #
# access features #
# ----------------------- #
user = root
password = root
host = 127.0.0.1
# --------------- #
# Charset #
# --------------- #
default-character-set = latin1
# ==================== #
# MySql Server #
# ==================== #
[wampmysqld]
port = 3306
socket = mysql
# -------------- #
# Master #
# -------------- #
server-id = 1
skip-slave-start
# --------------- #
# Charset #
# --------------- #
character-set-server = latin1
collation-server = latin1_general_ci
character-set-filesystem = latin1
init-connect = 'SET collation_connection = latin1_general_ci'
init-connect = 'SET NAMES latin1 COLLATE latin1_general_ci'
# ------------------- #
# Date & Time #
# ------------------- #
date-format = "%d-%m-%Y"
datetime-format = "%d-%m-%Y %H:%i:%s"
default-time-zone = SYSTEM
explicit-defaults-for-timestamp = on
# ------------------- #
# Directories #
# ------------------- #
basedir = /bin/mysql/mysql5.6.35/
datadir = /bin/mysql/mysql5.6.35/data
tmpdir = /tmp
# -------------------------#
# Full Text Search #
# -------------------------#
ft-max-word-len = 10
ft-min-word-len = 1
ft-stopword-file = ""
# ---------------#
# InnoDB #
# ---------------#
default-storage-engine = InnoDB
default-tmp-storage-engine = InnoDB
innodb-adaptive-hash-index = on
innodb-buffer-pool-dump-now = off
innodb-buffer-pool-dump-at-shutdown = off
innodb-buffer-pool-instances = 2
innodb-buffer-pool-load-at-startup = OFF
innodb-buffer-pool-size = 256M
innodb-data-file-path = ibdata1:12M:autoextend
innodb-data-home-dir = /bin/mysql/mysql5.6.35/data/ibdata/
innodb-doublewrite = 1
#skip-innodb_doublewrite
innodb-file-format = Barracuda
innodb-file-format-check = on
innodb-file-per-table = 1
innodb-flush-log-at-trx-commit = 1
#innodb-force-recovery = 1
innodb-ft-enable-stopword = off
innodb-ft-max-token-size = 10
innodb-ft-min-token-size = 0
innodb-io-capacity = 2000
innodb-max-dirty-pages-pct = 90
innodb-lock-wait-timeout = 600
innodb-log-buffer-size = 16M
innodb-log-file-size = 20M
innodb-log-files-in-group = 2
innodb-log-group-home-dir = /bin/mysql/mysql5.6.35/data/ibdata/
innodb-max-dirty-pages-pct = 80
innodb-optimize-fulltext-only = 1
innodb-page-size = 16K
innodb-purge-threads = 10
innodb-read-io-threads = 4
innodb-stats-on-metadata = 0
innodb-support-xa = 1
innodb-thread-concurrency = 16
innodb-write-io-threads = 4
# ---------------- #
# Language #
# ---------------- #
lc-messages = tr_TR
lc-time-names = tr_TR
# ------------#
# Log #
# ------------#
#binlog-cache-size = 1M
#binlog-format = mixed
#log-bin = /logs/mysql_bin
log-error = /logs/mysql_error.log
#log-error-verbosity = 3
log-queries-not-using-indexes = 1
log-throttle-queries-not-using-indexes = 60
#general-log = on
#general-log-file = /logs/mysql_general.log
# ------------#
# Max #
# ------------#
max-allowed-packet = 16M
max-connections = 20
max-connect-errors = 10
max-error-count = 64
max-heap-table-size = 512M
max-user-connections = 0
max-sp-recursion-depth = 255
# --------------------#
# Buffer-Size #
# --------------------#
join-buffer-size = 512M
read-buffer-size = 512M
read-rnd-buffer-size = 512M
sort-buffer-size = 512M
# ---------------#
# MyIsam #
# ---------------#
bulk-insert-buffer-size = 64M
key-buffer-size = 1G
myisam-max-sort-file-size = 1G
myisam-recover-options = OFF
myisam-repair-threads = 1
myisam-sort-buffer-size = 128M
myisam-stats-method = nulls_unequal
myisam-use-mmap = OFF
# -------------------#
# Networking #
# -------------------#
back-log = 50
net-buffer-length = 8K
net-read-timeout = 60
net-write-timeout = 60
# -----------------#
# Password #
# -----------------#
disconnect-on-expired-password = 0
# ---------------------------#
# Performance-Schema #
# ---------------------------#
# performance-schema = ON
# performance-schema-events-waits-history-size = 20
# performance-schema-events-waits-history-long-size = 1500
# --------------#
# Query #
# --------------#
query-alloc-block-size = 8M
query-cache-limit = 512M
query-cache-min-res-unit = 4096
query-cache-size = 512M
query-cache-type = On
query-cache-wlock-invalidate = Off
# -------------#
# Skip #
# -------------#
#skip-innodb-doublewrite
skip-external-locking
skip-federated
#skip-grant-tables
#skip-log-warnings
skip-slave-start
# skip-innodb-adaptive-hash-index
# --------------------#
# Slow & Long #
# --------------------#
long-query-time = 1
slow-query-log = 1
slow-query-log-file = /logs/mysql_slow.log
# ------------#
# Ssl #
# ------------#
ssl
ssl-ca = /bin/apache/apache2.4.23/conf/certificat/ca/ca.crt
ssl-cert = /bin/apache/apache2.4.23/conf/certificat/localhost/localhost.crt
ssl-key = /bin/apache/apache2.4.23/conf/certificat/localhost/localhost.key
skip-ssl
# --------------#
# Table #
# --------------#
table-definition-cache = 512
table-open-cache = 512
# -------------------#
# Temp Table #
# -------------------#
tmp-table-size = 1G
# ---------------#
# Thread #
# ---------------#
thread-cache-size = 8
thread-stack = 256K
# ------------------------------#
# Timeout Configuration #
# ------------------------------#
connect-timeout = 600
interactive-timeout = 600
wait-timeout = 600
# --------------------#
# Transaction #
# --------------------#
transaction-isolation = SERIALIZABLE
# ---------------#
# Tuning #
# ---------------#
concurrent-insert = 2
open-files-limit = 8192
secure-file-priv = /tmp/
# =================== #
# MySql Admin #
# =================== #
[mysqldadmin]
force
# ================== #
# MySql Dump #
# ================== #
[mysqldump]
add-drop-database
add-drop-table
add-drop-trigger
default-character-set = utf8
flush-logs
flush-privileges
lock-all-tables
max-allowed-packet = 16M
no-autocommit
order-by-primary
quick
routines
skip-comments
skip-opt
skip-set-charset
skip-tz-utc
triggers
# ===================== #
# My Isam Check #
# ===================== #
[myisamchk]
key-buffer-size = 128M
sort-buffer-size = 128M
read-buffer = 2M
write-buffer = 2M
# ====================== #
# MySql Hot Copy #
# ====================== #
[mysqlhotcopy]
interactive-timeout
# ===================== #
# MySql Monitor #
# ===================== #
[mysql]
default-character-set = latin1
no-auto-rehash
#safe-updates
skip-reconnect
table
# ================== #
# MySql Safe #
# ================== #
[mysqld_safe]
open-files-limit = 8192
log-error = /logs/mysql_error.log
Tavsiye MySQL Tuning Script'i kurarak tam siteninizin ihtiyacına göre dosya oluşturabilirsiniz.
