Optimalkan database mysql secara menyeluruh dalam kondisi beban tinggi di Linux

Pada saat yang sama, jumlah kunjungan online terus meningkat. Ketika server dengan memori 1G jelas-jelas sangat tegang, bahkan akan crash setiap hari atau server akan macet dari waktu ke waktu sebulan. MySQL menggunakan algoritma yang sangat terukur, sehingga Anda biasanya dapat menjalankannya dengan lebih sedikit memori atau memberikan lebih banyak memori pada MySQL untuk mendapatkan kinerja yang lebih baik.

Setelah menginstal mysql, file konfigurasi harusnya ada di direktori /usr/local/mysql/share/mysql. Ada beberapa file konfigurasi, termasuk my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf. , Situs web dengan arus lalu lintas berbeda dan lingkungan server dengan konfigurasi berbeda tentu saja memerlukan file konfigurasi berbeda.

Dalam keadaan normal, file konfigurasi my-medium.cnf dapat memenuhi sebagian besar kebutuhan kita, umumnya kita akan menyalin file konfigurasi ke /etc/my.cnf dan hanya perlu memodifikasi file konfigurasi ini, gunakan variabel mysqladmin extended- status –u; root –p dapat melihat parameter saat ini. Ada tiga parameter konfigurasi yang paling penting yaitu key_buffer_size, query_cache_size, table_cache .

key_buffer_size hanya berfungsi untuk tabel MyISAM,

key_buffer_size menentukan ukuran buffer indeks, yang menentukan kecepatan pemrosesan indeks, terutama kecepatan pembacaan indeks. Secara umum, kami menetapkannya ke 16M. Faktanya, angka ini masih jauh dari cukup untuk situs yang sedikit lebih besar. Dengan memeriksa nilai status Key_read_requests dan Key_reads, Anda dapat mengetahui apakah pengaturan key_buffer_size masuk akal. Rasio key_reads / key_read_requests harus serendah mungkin, setidaknya 1:100, 1:1000 lebih baik (nilai status di atas dapat diperoleh dengan menggunakan SHOW STATUS LIKE 'key_read%'). Atau jika Anda sudah menginstal phpmyadmin, Anda dapat melihatnya melalui status server running. Penulis menyarankan untuk menggunakan phpmyadmin untuk mengelola mysql. Nilai status berikut adalah contoh analisis yang saya peroleh melalui phpmyadmin:

Server ini telah berjalan selama 20 hari
ukuran_buffer_kunci – 128M
permintaan_baca_kunci – 650759289
kunci_baca - 79112
Rasionya mendekati 1:8000 dan kondisi kesehatannya sangat baik.

Cara lain untuk memperkirakan key_buffer_size adalah dengan menjumlahkan ruang yang ditempati oleh indeks setiap tabel di database situs web Anda. Ambil server ini sebagai contoh: indeks dari beberapa tabel yang lebih besar berjumlah sekitar 125 juta lebih besar.

Mulai dari 4.0.1, MySQL menyediakan mekanisme buffering kueri. Menggunakan buffering kueri, MySQL menyimpan pernyataan SELECT dan hasil kueri di buffer. Nantinya, untuk pernyataan SELECT yang sama (peka huruf besar-kecil), hasilnya akan dibaca langsung dari buffer. Menurut panduan pengguna MySQL, penggunaan buffering kueri dapat mencapai efisiensi hingga 238%.

Dengan menyesuaikan parameter berikut, Anda dapat mengetahui apakah pengaturan query_cache_size masuk akal.
Sisipan Qcache
Qcache berhasil
Qcache plum rendah
Blok bebas Qcache
Total blok Qcache

Jika nilai Qcache_lowmem_prunes sangat besar, ini menunjukkan bahwa buffering sering kali tidak mencukupi. Pada saat yang sama, jika nilai Qcache_hits sangat besar, ini menunjukkan bahwa buffer kueri sangat sering digunakan perlu ditingkatkan. Jika nilai Qcache_hits kecil, ini menunjukkan bahwa tingkat pengulangan kueri Anda sangat tinggi. Dalam hal ini, penggunaan buffering kueri akan mempengaruhi efisiensi, jadi Anda dapat mempertimbangkan untuk tidak menggunakan buffering kueri. Selain itu, menambahkan SQL_NO_CACHE ke pernyataan SELECT dapat dengan jelas menunjukkan bahwa buffer kueri tidak digunakan.
Qcache_free_blocks, jika nilainya sangat besar, ini menunjukkan bahwa ada banyak fragmen di buffer query_cache_type menentukan apakah akan menggunakan buffering kueri

saya mengatur:
query_cache_size = 32M
kueri_cache_type= 1

Dapatkan nilai status berikut:
Kueri Qcache di cache 12737 menunjukkan jumlah item yang saat ini di-cache
Sisipan Qcache 20649006
Qcache mencapai 79060095 Tampaknya tingkat permintaan berulang cukup tinggi.
Qcache lowmem prunes 617913 Seringkali cache terlalu rendah.
Qcache tidak di-cache 189896
Memori bebas Qcache 18573912 Sisa ruang cache saat ini
Blok gratis Qcache 5328 Jumlah ini tampaknya agak besar dan terfragmentasi
Total blok Qcache 30953
Jika memori memungkinkan 32M, Anda harus menambahkan lebih banyak.

table_cache menentukan ukuran cache tabel. Setiap kali MySQL mengakses sebuah tabel, jika ada ruang di buffer tabel, tabel tersebut dibuka dan ditempatkan di dalamnya, sehingga memungkinkan akses lebih cepat ke isi tabel. Dengan memeriksa nilai status Open_tables dan Opened_tables pada waktu puncak, Anda dapat memutuskan apakah Anda perlu meningkatkan nilai table_cache. Jika ternyata open_tables sama dengan table_cache, dan open_tables bertambah, maka Anda perlu meningkatkan nilai table_cache (nilai status di atas dapat diperoleh dengan menggunakan SHOW STATUS LIKE 'Open%tables'). Perhatikan bahwa table_cache tidak dapat disetel ke nilai besar secara membabi buta. Jika disetel terlalu tinggi, hal ini dapat menyebabkan deskriptor file tidak mencukupi, sehingga mengakibatkan kinerja tidak stabil atau kegagalan koneksi.

Untuk mesin dengan memori 1G, nilai yang disarankan adalah 128-256.

Pengaturan penulis
tabel_cache = 256

Dapatkan status berikut:
Buka tabel 256
Buka tabel 9046
Walaupun open_tables sudah sama dengan table_cache, namun relatif terhadap waktu berjalan server, sudah berjalan selama 20 hari dan nilai open_tables juga sangat rendah. Oleh karena itu, meningkatkan nilai table_cache seharusnya tidak banyak berguna. Jika nilai di atas muncul setelah berjalan selama 6 jam, maka Anda harus mempertimbangkan untuk meningkatkan table_cache.

Jika Anda tidak perlu mencatat log biner, matikan fungsi ini. Perhatikan bahwa setelah mematikannya, Anda tidak dapat memulihkan data sebelum masalah terjadi. Log biner berisi semua pernyataan yang memperbarui data, dan itu tujuannya adalah untuk memulihkan database. Gunakan untuk mengembalikan data ke keadaan akhir sebanyak mungkin. Selain itu, jika Anda melakukan replikasi sinkron (Replikasi), Anda juga perlu menggunakan log biner untuk mentransfer modifikasi.

log_bin menentukan file log. Jika tidak ada nama file yang diberikan, MySQL akan menghasilkan nama file default itu sendiri. MySQL akan secara otomatis menambahkan indeks numerik setelah nama file, dan akan membuat ulang file biner baru setiap kali layanan dimulai. Selain itu, gunakan log-bin-index untuk menentukan file indeks; gunakan binlog-do-db untuk menentukan database yang akan direkam; gunakan binlog-ignore-db untuk menentukan database yang tidak akan direkam. Catatan: binlog-do-db dan binlog-ignore-db hanya menentukan satu database dalam satu waktu. Menentukan beberapa database memerlukan beberapa pernyataan. Selain itu, MySQL akan mengubah semua nama database menjadi huruf kecil. Anda harus menggunakan semua nama huruf kecil saat menentukan database, jika tidak maka tidak akan berfungsi.

Untuk mematikan fungsi ini cukup tambahkan tanda # di depannya
#log-bin

Aktifkan log kueri lambat (log kueri lambat)

Log kueri lambat berguna untuk melacak kueri yang bermasalah. Ini mencatat semua kueri yang memeriksa long_query_time, dan jika perlu, mencatat tanpa menggunakan indeks. Berikut ini contoh log kueri yang lambat:

Untuk mengaktifkan log kueri lambat, Anda perlu mengatur parameter log_slow_queries, long_query_times, dan log-queries-not-using-indexes.

log_slow_queries menentukan file log. Jika tidak ada nama file yang diberikan, MySQL akan menghasilkan nama file default dengan sendirinya. long_query_times menentukan ambang permintaan lambat, defaultnya adalah 10 detik. log-queries-not-using-indexes adalah parameter yang diperkenalkan setelah 4.1.0, yang menunjukkan bahwa kueri yang tidak menggunakan indeks dicatat. Penulis menyetel long_query_time=10

Pengaturan penulis:

sort_buffer_size = 1 juta
koneksi_maks=120
tunggu_waktu habis =120
log_belakang=100
read_buffer_size = 1 juta
thread_cache=32
waktu_interaktif=120
thread_concurrency = 4

Deskripsi parameter:

jaminan simpanan
Jumlah koneksi yang harus dimiliki MySQL. Ini berfungsi ketika thread utama MySQL menerima banyak permintaan koneksi dalam waktu singkat, dan kemudian thread utama memerlukan waktu (walaupun sebentar) untuk memeriksa koneksi dan memulai thread baru. Nilai back_log menunjukkan berapa banyak permintaan yang dapat disimpan dalam tumpukan dalam waktu singkat sebelum MySQL berhenti menjawab permintaan baru untuk sementara. Hanya jika Anda mengharapkan banyak koneksi dalam waktu singkat Anda perlu meningkatkannya, dengan kata lain, nilai ini adalah ukuran antrian mendengarkan koneksi TCP/IP yang masuk. Sistem operasi Anda memiliki batasannya sendiri pada ukuran antrean ini. Halaman manual untuk panggilan sistem Unix listening(2) seharusnya memiliki rincian lebih lanjut. Periksa dokumentasi OS Anda untuk mengetahui nilai maksimum variabel ini. Mencoba menyetel back_log lebih tinggi dari batas sistem operasi Anda tidak akan berpengaruh.
max_connections
Jumlah maksimum koneksi bersamaan adalah 120. Jika melebihi nilai ini, maka secara otomatis akan pulih dan masalah akan teratasi secara otomatis.

thread_cache
Saya tidak dapat menemukan instruksi spesifik apa pun, tetapi setelah menyetelnya ke 32, lebih dari 400 utas dibuat dalam 20 hari, sedangkan ribuan utas dibuat dalam satu hari sebelumnya, jadi masih berguna.

thread_konkurensi
#Setel ke nomor cpu Anda x2, misalnya jika hanya ada satu cpu maka thread_concurrency=2
#Ada 2 cpu, lalu thread_concurrency=4

lewati-innodb
#Hapus dukungan innodb