Rujukan parameter pengoptimuman MySQL!

Kata Pengantar

Apabila ia berkaitan dengan penalaan dalam operasi dan penyelenggaraan MySQL harian, fail konfigurasi MySQL my.cnf tidak boleh diabaikan. Parameter lalai MySQL tidak dapat memenuhi keperluan perniagaan dalam talian harian kami, jadi pengoptimuman parameter juga merupakan pautan yang sangat diperlukan. Saya tidak mahu menyenaraikan berapa banyak item yang terdapat dalam konfigurasi my.cnf dan maksud setiap item ini boleh didapati dalam dokumentasi rasmi. Yang berikut hanya menerangkan beberapa parameter yang perlu diberi perhatian dalam kerja harian.

Beberapa parameter diterangkan di bawah. Sudah tentu terdapat tetapan lain yang mungkin dimainkan, bergantung pada beban atau perkakasan anda: anda memerlukan penalaan khas dalam situasi dengan memori perlahan dan cakera laju, konkurensi tinggi dan beban kerja intensif tulis. Walau bagaimanapun, matlamat di sini adalah supaya anda boleh dengan cepat mendapatkan konfigurasi MySQL yang mantap tanpa menghabiskan terlalu banyak masa untuk melaraskan beberapa tetapan MySQL yang tidak penting atau membaca dokumentasi untuk mengetahui tetapan yang penting untuk anda.

Konfigurasi InnoDB

Bermula dari MySQL versi 5.5, InnoDB ialah enjin storan lalai dan ia digunakan lebih banyak daripada mana-mana enjin storan lain. Itulah sebabnya ia perlu dikonfigurasikan dengan teliti.

innodb_file_per_table

Data jadual dan indeks disimpan dalam ruang jadual kongsi atau ruang jadual berasingan. Pemasangan senario kerja kami menetapkan innodb_file_per_table = HIDUP secara lalai, yang turut memudahkan pemindahan ruang jadual berasingan semasa kerja. Dalam MySQL 5.6, nilai lalai sifat ini adalah HIDUP.

_flush_log_at_trx_commit

Nilai lalai ialah 1, yang bermaksud InnoDB menyokong sepenuhnya ciri ACID. Nilai ini paling sesuai apabila kebimbangan utama anda ialah keselamatan data, seperti pada nod induk. Tetapi untuk sistem dengan kelajuan cakera (baca dan tulis) perlahan, ia akan membawa overhed yang besar, kerana setiap kali perubahan flushing pada log buat semula memerlukan fsyncs tambahan.

Menetapkan nilainya kepada 2 akan mengakibatkan tingkah laku yang tidak boleh dipercayai. Oleh kerana urus niaga komited hanya disalurkan ke log buat semula sekali sesaat, ia boleh diterima untuk beberapa senario Contohnya, nilai ini boleh diterima untuk nod sandaran nod utama. Nilai 0 adalah lebih pantas, tetapi mungkin mengakibatkan beberapa kehilangan data sekiranya berlaku ranap sistem: hanya digunakan pada nod sandaran. Apabila bercakap tentang parameter ini, sync_binlog lain pasti akan terlintas di fikiran.

innodb_flush_method

Konfigurasi ini menentukan cara data dan log ditulis ke cakera keras. Terdapat tiga kaedah keseluruhannya Kami menggunakan O_DIRECT secara lalai. Mod O_DIRECT: Operasi penulisan fail data adalah terus dari penimbal mysql innodb ke cakera, tanpa melalui penimbal sistem pengendalian Penyelesaian sebenar adalah dalam langkah siram, dan log masih perlu melalui penimbal OS.

innodb_log_buffer_size

Konfigurasi ini menentukan cache yang diperuntukkan untuk transaksi yang masih belum dilaksanakan. Nilai lalai (1MB) biasanya mencukupi, tetapi jika transaksi anda mengandungi objek binari yang besar atau medan teks yang besar, cache ini akan mengisi dengan cepat dan mencetuskan operasi I/O tambahan. Lihat pembolehubah status Innodb_log_waits, jika bukan 0, tambah saiz innodb_log_buffer_size.

innodb_buffer_pool_size

Parameter ini haruslah sesuatu yang mesti diberi perhatian semasa operasi dan penyelenggaraan. Kumpulan penimbal adalah tempat data dan indeks dicache. Ia adalah parameter teras MySQL. Dalam keadaan biasa, parameter ini ditetapkan kepada 60%~70% daripada memori fizikal. (Walau bagaimanapun, kejadian kami pada asasnya ialah penempatan bercampur bagi berbilang kejadian, jadi nilai ini perlu dianalisis berdasarkan skala perniagaan.)

innodb_log_file_size

Ini adalah saiz log buat semula. Pengelogan semula digunakan untuk memastikan operasi tulis adalah pantas dan boleh dipercayai serta boleh dipulihkan daripada ranap sistem. Jika anda tahu bahawa aplikasi anda perlu menulis data dengan kerap dan anda menggunakan MySQL 5.6, maka anda boleh menetapkannya kepada 4G dari awal. (Saiz tertentu perlu diselaraskan dengan sewajarnya mengikut perniagaan anda sendiri)

innodb_support_xa

innodb_support_xa boleh menukar penyerahan transaksi dua peringkat XA InnoDB. Secara lalai, innodb_support_xa=true menyokong penyerahan transaksi dua peringkat XA. Memandangkan penyerahan transaksi dua peringkat XA mengakibatkan siram berlebihan dan operasi lain, impak prestasi akan mencapai 10%. Oleh itu, untuk meningkatkan prestasi, beberapa DBA akan menetapkan innodb_support_xa=false. Dalam kes ini, redolog dan binlog tidak akan disegerakkan, dan mungkin terdapat situasi di mana transaksi diserahkan dalam pangkalan data utama tetapi tidak direkodkan dalam binlog. Ini juga boleh menyebabkan kehilangan data transaksi.

innodb_additional_mem_pool_size

Parameter ini digunakan untuk menyimpan maklumat medan data dan struktur data dalaman yang lain. Lebih banyak jadual yang ada, lebih banyak memori perlu diperuntukkan di sini. Jika InnoDB kehabisan memori dalam kumpulan ini, InnoDB mula memperuntukkan memori daripada sistem pengendalian dan menulis mesej amaran kepada log ralat MySQL. Lalai ialah 8MB. Tetapan umum ialah 16MB.

sambungan_maks

Bilangan lalai sambungan dalam pelayan MySQL agak kecil, biasanya sekitar 100. Sebaiknya tetapkan nilai maksimum yang lebih besar. Secara amnya, jika anda menetapkannya kepada 500~1000, setiap pautan akan menduduki jumlah memori tertentu, jadi lebih besar parameter, lebih baik. Sesetengah orang akan meningkatkan saiz parameter ini apabila menghadapi terlalu banyak sambungan Walau bagaimanapun, sebenarnya, jika terdapat masalah dengan volum perniagaan atau logik program atau SQL tidak ditulis dengan baik, walaupun meningkatkan parameter ini tidak akan membantu hanya menunggu masa sebelum ralat berlaku lagi. Menggunakan pengumpulan sambungan dalam aplikasi anda atau pengumpulan proses dalam MySQL boleh membantu menyelesaikan masalah ini.

• Peruntukan memori peringkat penglihatan

max_threads(当前活跃连接数)* (

read_buffer_size– 顺序读缓冲，提高顺序读效率

+read_rnd_buffer_size– 随机读缓冲，提高随机读效率

+sort_buffer_size– 排序缓冲，提高排序效率

+join_buffer_size– 表连接缓冲，提高表连接效率

+binlog_cache_size– 二进制日志缓冲，提高二进制日志写入效率ß

+tmp_table_size– 内存临时表，提高临时表存储效率

+thread_stack– 线程堆栈，暂时寄存SQL语句/存储过程

+thread_cache_size– 线程缓存，降低多次反复打开线程开销

+net_buffer_length– 线程持连接缓冲以及读取结果缓冲

+bulk_insert_buffer_size– MyISAM表批量写入数据缓冲

)

• Peruntukan memori peringkat global

global buffer(全局内存分配总和) =

innodb_buffer_pool_size

— InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等

+ innodb_additional_mem_pool_size

— InnoDB数据字典额外内存，缓存所有表数据字典

+innodb_log_buffer_size

— InnoDB REDO日志缓冲，提高REDO日志写入效率

+key_buffer_size

— MyISAM表索引高速缓冲，提高MyISAM表索引读写效率

+query_cache_size

–查询高速缓存，缓存查询结果，提高反复查询返回效率+table_cahce — 表空间文件描述符缓存，提高数据表打开效率

+table_definition_cache

–表定义文件描述符缓存，提高数据表打开效率

Matlamat utama pengoptimuman parameter adalah untuk membolehkan MySQL menggunakan sumber dengan lebih baik dengan mengawal peruntukan memori yang munasabah disyorkan untuk mengurangkan peruntukan memori Sesi.

server-id

Pastikan server-id berbeza semasa menyalin skema, biasanya ID induk lebih kecil daripada ID hamba.

log_bin

Jika anda mahu pelayan pangkalan data bertindak sebagai nod sandaran untuk nod induk, maka menghidupkan log binari adalah satu kemestian. Jika anda melakukan ini, jangan lupa untuk menetapkan server_id kepada nilai unik. Walaupun dengan hanya satu pelayan, ini (menghidupkan pengelogan binari) berguna jika anda ingin melakukan pemulihan data titik-dalam-masa: pulihkan daripada sandaran terbaharu anda (sandaran penuh) dan gunakan perubahan dalam log binari (sandaran tambahan ) .

Log binari akan disimpan secara kekal setelah dibuat. Jadi, jika anda tidak mahu kehabisan ruang cakera, anda boleh menggunakan PURGE BINARY LOGS untuk membersihkan fail lama, atau tetapkan expire_logs_days untuk menentukan berapa hari selepas itu log akan dibersihkan secara automatik. Pengelogan binari bukan tanpa overhed, jadi disyorkan untuk mematikan pilihan ini jika anda tidak memerlukannya pada nod replika yang bukan nod utama.

skip_name_resolve

Apabila pelanggan menyambung ke pelayan pangkalan data, pelayan melaksanakan resolusi nama hos, dan apabila DNS perlahan, mewujudkan sambungan juga akan menjadi perlahan. Oleh itu, disyorkan untuk mematikan pilihan skip_name_resolve apabila memulakan pelayan tanpa melakukan carian DNS. Satu-satunya had ialah hanya alamat IP boleh digunakan dalam penyata GRANT kemudian, jadi berhati-hati mesti diambil apabila menambah tetapan ini pada sistem sedia ada.

sync_binlog

Nilai lalai sync_binlog ialah 0. Seperti mekanisme sistem pengendalian untuk menyegarkan fail lain, MySQL tidak akan menyegerakkan ke cakera tetapi bergantung pada sistem pengendalian untuk menyegarkan log binari.

Apabila sync_binlog =N (N>0), MySQL akan menggunakan fungsi fdatasync() untuk menyegerakkan log binari bertulisnya ke cakera setiap kali ia menulis log binari N kali. Ia adalah paling selamat apabila innodb_flush_log_at_trx_commit dan sync_binlog kedua-duanya 1. Dalam kes ranap perkhidmatan mysqld atau ranap hos pelayan, log binari mungkin kehilangan paling banyak satu penyata atau transaksi. Walau bagaimanapun, anda tidak boleh memiliki kek anda dan memakannya juga Double 1 akan membawa kepada operasi IO yang kerap, jadi mod ini juga merupakan cara yang paling perlahan. Untuk pertimbangan perniagaan kami dan apabila tekanan perniagaan membenarkan, lalai ialah konfigurasi dua kali ganda 1.

log_slave_update

Apabila seni bina lata perlu digunakan dalam perniagaan, parameter log_slave_update = 1 mesti dihidupkan, jika tidak, tahap ketiga mungkin tidak dapat menerima binlog yang dijana oleh tahap pertama, dan dengan itu penyegerakan data tidak dapat dilakukan.

tmpdir

Jika jadual sementara memori melebihi had, MySQL akan menukarnya secara automatik menjadi jadual MyISAM berasaskan cakera dan menyimpannya dalam direktori tmpdir yang ditentukan Oleh itu, konfigurasikan tmpdir kepada peranti storan dengan prestasi dan kelajuan yang baik secepat mungkin.

berkaitan log perlahan

slow_query_log = 1 #Buka log perlahan

slow_query_log_file = /mysql/log/mysql.slow

long_query_time = 0.5 #Tetapkan berapa saat pertanyaan akan dimasukkan ke dalam log perlahan

Soalan lain

Impak SSD pada parameter

Dengan perkembangan sains dan teknologi, semakin banyak peranti storan telah mula beralih daripada komponen mekanikal tradisional kepada storan kekal yang terdiri daripada komponen elektronik, dan harganya semakin diterima oleh perusahaan. Selepas kelajuan komponen storan dipertingkatkan, nampaknya membazir untuk menggunakan konfigurasi DB komponen mekanikal tradisional Oleh itu, konfigurasi MySQL perlu dilaraskan mengikut teknologi storan yang berbeza Contohnya, innodb_io_capacity perlu ditingkatkan, log fail dan buat semula harus diletakkan pada cakera keras mekanikal, dan buat asal hendaklah diletakkan pada cakera keras mekanikal Untuk SSD, penulisan atom tidak memerlukan Penampan Tulis Berganda, pemampatan InnoDB, mesin tunggal berbilang contoh + cgroup, dsb. Analisis situasi I/O dan laraskan innodb_io_capacity dan innodb_max_dirty_pages_pct secara dinamik;

Tetapan kolam benang

Kami belum melakukan sebarang penalaan untuk innodb_write_io_threads dan innodb_read_io_threads, tetapi saya percaya bahawa dengan melaraskan kepada 8 atau 16, prestasi I/O sistem akan menjadi lebih baik. Juga, anda perlu memberi perhatian kepada perkara-perkara berikut: sebarang pelarasan mesti berdasarkan sokongan data dan analisis yang ketat, jika tidak, ia akan menjadi perbincangan kosong jenis ini sangat bermakna dan benar-benar boleh membawa nilai dan fahami sebanyak mungkin mengapa kita perlu menyesuaikan cara ini.

CPU berkaitan

• Innodb_thread_concurrency=0
• Innodb_sync_spin_loops=288
• table_definition_cache=2000

IO berkaitan

• Innodb_flush_method Adalah disyorkan untuk menggunakan O_DIRECT
• Innodb_io_capacity ditetapkan kepada IOPS maksimum yang disokong oleh cakera
• Innodb_wirte_io_threads=8
• Innodb_read_io_threads=8
• Innodb_purge_threads=1
• Dari segi prabacaan Innodb, jika ia berdasarkan sistem indeks utama atau unik, adalah disyorkan untuk melumpuhkan prabacaan
• Innodb_random_read_ahead = off

Atas ialah kandungan terperinci Rujukan parameter pengoptimuman MySQL!. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!