Hari ini kita akan berbicara lagi dan lagi tentang mySQL. Mari pahami pengoptimalan dan bahas banyak parameter server.
Mari kita mulai.
Mulailah
Server yang kami biarkan berada di CentOS . Akan mengoptimalkan metode pengeditan konfigurasi my.cnf .
Mengatur beberapa parameter dapat meningkatkan
kinerja database server beberapa kali!
Untuk memulainya, mari kita putuskan apa yang umumnya kita optimalkan - yaitu, berapa banyak tabel di mesin mana yang kita miliki, perangkat keras apa yang kita miliki dan di bawah parameter apa kita akan menyesuaikan semuanya.
Untuk ini, kami menggunakan htop (sebagai alat yang indah dan intuitif):
yum install htop
Dapatkan htop :
htop
Kami mendapatkan sesuatu seperti ini:
tuliskan diri Anda di my.cnf :
# 3 , 4
Sekarang mari kita cari tahu jumlah tabel dan tipenya.
Untuk ini kami mengambil tuner mysql :
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Ayo lari:
perl mysqltuner.pl
Kira-kira kesimpulannya:
Mari menulis untuk diri kita sendiri di my.cnf :
# 64M myisam, 770M innoDB
Konfigurasi tipikal biasanya direkomendasikan seperti ini:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-external-locking
max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 2048M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 144 <a
href="https://ruhighload.com/query_cache_size+%d0%bf%d0%b0%d1
%80%d0%b0%d0%bc%d0%b5%d1%82%d1%80+%d0%b2+mysql"
target="_blank" style="color: rgb(232, 95, 99);">query_cache_size</a>
= 0 slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
Sekarang mari kita cari tahu apa yang akan kita optimalkan di sini, mengapa, bagaimana dan mengapa (terutama mengapa parameter ini tidak cukup.
Optimasi dan konfigurasi
Pertama, Anda dapat menggulir ke bagian bawah keluaran tuner mysql dan melihat apa yang direkomendasikannya. Dalam kasus kami, ini terlihat seperti ini:
wget
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl
Kami tidak akan melakukan substitusi yang sembrono, dan membahas parameter mysql , yang mungkin menarik bagi kami sejak awal. Apa yang:
lewati-penguncian-eksternal , - menghapus kunci luar, yang lebih cepat;
-name-the melewatkan RESOLVE , - memungkinkan MySQL untuk menghindari jawaban untuk meminta DNS memeriksa koneksi klien ke server MySQL .
Dengan demikian, server MySQL hanya akan menggunakan URL
IP, bukan nama host yang sedikit, tetapi lebih cepat.
binlog_cache _ size, - ukuran cache untuk menyimpan perubahan dalam log biner. Menyetel ukuran hanya untuk cache transaksi. Lakukan 100M - tidak lagi diperlukan.
innodb_stats_on_metadata = 0 (OFF) , - untuk mempercepat dengan
INFORMATION_SCHEMA, SHOW TABLE STATUS atau SHOW INDEX nonaktifkan statistik pembaruan untuk fungsi seperti
quer y _cache_size = 128M dan query_sache_type
= 1 , - minta cache. 1 - pada prinsipnya diaktifkan, batas 128M . Tidak
disarankan untuk diletakkan di atas 256M , mengingat dapat mengakibatkan penyumbatan.
Karena kami memiliki lebih dari tabel InnoDB , itu menghapus cache _ size bed .
Dengan versi MySQL 5.6 query_cache_size dinonaktifkan, dan dengan versi 8.0, dihapus secara
default, semua tabel dan indeks disimpan dalam satu file, jadi kami menggunakan innodb_file_per_table = 1.
Nilai innodb_open_files dan table_open_cache - disarankan untuk menyetel kedua opsi di 4096 atau 8192 . A biasanya dihitung sebagai jumlah tabel di semua basis dikalikan dengan 2 , kira-kira.
Saat bekerja dengan InnoDB adalah parameter innodb_buffer_pool_size yang paling penting , ini diatur berdasarkan prinsip "semakin banyak, semakin baik." Disarankan untuk mengalokasikan hingga 70-80% dari RAM server.
innodb_log_file_size - mempengaruhi kecepatan tulis, mengatur ukuran log operasi (operasi ditulis pertama kali ke log, dan kemudian diterapkan ke data di disk). Semakin besar log ini, semakin cepat catatan akan bekerja (karena ada lebih banyak catatan di file log). Selalu ada dua file, dan ukurannya sama. Nilai parameter menetapkan ukuran satu file.
!️ innodb_log_file_size MySQL, ib_logfile-n ( /var/lib/mysql/), innodb_log_file_size MySQL.
MySQL - .
Instalasi ukuran besar innodb_log_file_size dapat menyebabkan peningkatan kinerja, tetapi pada saat yang sama akan meningkatkan waktu pemulihan, pilih dari 256M ke 1G .
innodb_log _ buffer_size - ukuran buffer transaksi. Umumnya disarankan untuk tidak menerapkan jika tidak menggunakan BLOB dan TEXT besar.
innodb_flush _ method , - mendefinisikan logika untuk membuang data ke disk. Dalam sistem modern yang menggunakan RAID dan situs cadangan, Anda akan memilih antara ODSYNCdan ODIRECT , - parameter pertama lebih cepat, lebih aman kedua.
_ size bed key_buffer - buffer untuk bekerja dengan kunci dan indeks, dan sort_buffer - buffer untuk menyortir. Jika Anda tidak menggunakan tabel MyISAM , Anda disarankan untuk menyetel key_buffer_size ke 32MB untuk menyimpan indeks
tabel temp .
Parameter thread_cache _ size menunjukkan jumlah utas (utas), meninggalkan cache ketika klien memutuskan hubungan. Dengan koneksi baru, utas tidak dibuat, tetapi diambil dari cache, yang menghemat sumber daya di bawah beban berat.
innodb_flush_log_attrx_commit , - dapat meningkatkan throughput catatan data dalam ratusan kali basis. Ini menentukan apakah Mysql akan membuang setiap operasi ke disk (ke file log).
innodb_flush_log_at_trx_commit = 1 digunakan untuk kasus
ketika penyimpanan data - adalah prioritas nomor satu.
innodb_flush_log_at_trx_commit = 2 untuk kasus ketika kehilangan data kecil tidak penting. Ada juga 0 (nol) - opsi yang paling produktif, tetapi tidak aman.
max_connections - jika Anda mendapatkan error "Terlalu banyak koneksi",opsi ini harus ditingkatkan. Jadi tidak ada manfaat besar dalam pengoptimalan darinya.
Jumlah file input / output dalam aliran InnoDB opsi yang ditentukan innodb_read_io_threads , innodbwrite_io_threads , parameter ini biasanya disetel ke 4 atau 8 , ROM cepat disetel di SSD 16. Artinya innodb_thread_concurrency mengatur jumlah inti * 2 .
Konfigurasinya seperti ini:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-external-locking
skip-name-resolve
binlog_cache_size = 100M
thread_cache_size = 32
innodb_stats_on_metadata = OFF
query_cache_limit = 1M
query_cache_size = 0 query_cache_type = 1
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256
innodb_log_buffer_size = 6M
innodb_additional_mem_pool_size = 16M
innodb_flush_method = O_DSYNC
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency = 6
innodb_file_per_table = 1
key_buffer_size = 32M
tmp_table_size = 64M
max_connections = 350
sort_buffer_size = 16M read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 8M
thread_stack = 1M
binlog_cache_size = 8M
tmp_table_size = 128M
table_open_cache = 2048
[mysqldump] quick
quote-names
max_allowed_packet = 16M
Dan akhirnya, Anda dapat melihat rekomendasi dari tuner dan mengikutinya.
Kesimpulan
Inilah konfigurasi yang menarik ternyata. Jika Anda merasa kesulitan, maka pertama-tama Anda harus menggunakan kalkulator mySQL , yang akan memberi tahu Anda parameter utama dan memungkinkan Anda untuk tidak melampaui memori yang tersedia - bagaimanapun, semuanya tergantung padanya:
Terima kasih atas perhatian Anda. Bergabunglah dalam diskusi.