Seberapa cepat replikasi mysql

Merekomendasikan penggunaan slowlog [dengan long_query_time = 1] untuk menemukan kueri nakal. Kemudian kita bisa mendiskusikan bagaimana memperbaikinya.

Detail dan pengamatan lainnya

[ [key_buffer_size - 1.2 * Key_blocks_used * 1024] ] = [[512M - 1.2 * 8 * 1024]] / 16384M = 3.1% -- Persentase RAM terbuang di key_buffer. -- Kurangi key_buffer_size [sekarang 536870912]

[ Key_blocks_used * 1024 / key_buffer_size ] = 8 * 1024 / 512M = 0.00% -- Persentase key_buffer digunakan. Tanda air tinggi. -- Turunkan key_buffer_size [sekarang 536870912] untuk menghindari penggunaan memori yang tidak perlu

[ [key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70] ] = [[512M / 0.20 + 128M / 0.70]] / 16384M = 16.7% -- Sebagian besar ram yang tersedia harus disediakan untuk caching. -- http. //mysql. rjweb. org/dok. php/memori

[ table_open_cache ] = 16,293 -- Jumlah deskriptor tabel yang akan di-cache -- Beberapa ratus biasanya bagus

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

0 -- InnoDB Data + Index cache -- 128M [default lama] sangat kecil

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

1 -- % dari RAM yang digunakan untuk InnoDB buffer_pool -- Setel ke sekitar 70% dari RAM yang tersedia. [Menjadi rendah kurang efisien; pertukaran risiko terlalu tinggi. ]

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

2 -- "InnoDB. page_cleaner. 1000ms dimaksudkan loop mengambil. " dapat diperbaiki dengan menurunkan lru_scan_depth

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

3 -- Saat membilas, gunakan IOP sebanyak ini. -- Pembacaan bisa lamban atau runcing

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

4 -- Kapasitas. maks/polos -- Merekomendasikan 2. Maks harus sama dengan IOP yang dapat ditangani oleh subsistem I/O Anda. [Jika jenis drive tidak diketahui 2000/200 mungkin pasangan yang masuk akal. ]

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

5 - Baca permintaan yang harus mencapai disk - Tingkatkan innodb_buffer_pool_size [sekarang 134217728] jika Anda memiliki cukup RAM

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

6 - Baca permintaan yang harus mencapai disk - Tingkatkan innodb_buffer_pool_size [sekarang 134217728] jika Anda memiliki cukup RAM

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

7 -- Cache hilang di buffer_pool. -- Tingkatkan innodb_buffer_pool_size [sekarang 134217728]? . ]

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

8 -- InnoDB I/O -- Tingkatkan innodb_buffer_pool_size [sekarang 134217728]?

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

_9

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

0 -- Rasio -- [lihat menit]

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

1 -- Bagaimana InnoDB harus meminta OS untuk menulis blok. Sarankan O_DIRECT atau O_ALL_DIRECT [Percona] untuk menghindari buffering ganda. [Setidaknya untuk Unix. ] Lihat chrischandler untuk peringatan tentang O_ALL_DIRECT

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

_2

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

_3 -- Pengoptimalan kecil saat menulis blok ke disk. -- Gunakan 0 untuk drive SSD;

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

_4 -- Jika > 100%, perlu lebih banyak io_capacity. -- Tingkatkan innodb_io_capacity [sekarang 200] jika drive dapat menanganinya

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

3 -- I/O ops per detik mampu di disk. 100 untuk drive lambat;

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

_6 -- Apakah akan menggunakan hash adaptif [AHI]. -- AKTIF untuk sebagian besar hanya dapat dibaca;

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

6 -- Biasanya harus ON. -- Ada kasus di mana MATI lebih baik. Lihat juga innodb_adaptive_hash_index_parts [sekarang 8] [setelah 5. 7. 9] dan innodb_adaptive_hash_index_partitions [MariaDB dan Percona]. ON telah terlibat dalam crash yang jarang terjadi [bug 73890]. 10. 5. 0 memutuskan untuk default OFF

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

_8 -- Apakah akan mencatat semua Deadlock. -- Jika Anda mengalami Deadlock, aktifkan ini. Hati-hati. Jika Anda memiliki banyak kebuntuan, ini dapat menulis banyak ke disk

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

_9 -- Batas byte pada hasil FULLTEXT. [Mungkin tidak dialokasikan sebelumnya, tetapi tumbuh?] -- Turunkan setelan

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

0 -- local_infile [sekarang AKTIF] = AKTIF adalah potensi masalah keamanan

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

1 -- Kehabisan ruang di QC -- tingkatkan query_cache_size [sekarang 16777216]

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

2 - Rasio Penghapusan [frekuensi perlu memangkas karena memori tidak cukup]

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

3 -- Hit to insert ratio -- tinggi bagus -- Pertimbangkan untuk mematikan cache kueri

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

4 -- Hit ratio -- PILIH yang menggunakan QC -- Pertimbangkan untuk mematikan cache kueri

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

5 -- Kueri cache hit rate -- Mungkin yang terbaik adalah mematikan QC

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

6 -- query_alloc_block_size vs formula -- Sesuaikan query_alloc_block_size [sekarang 16384]

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

7 -- Frekuensi pembuatan tabel "temp" disk sebagai bagian dari SELECT kompleks -- tingkatkan tmp_table_size [sekarang 16777216] dan max_heap_table_size [sekarang 16777216]. Periksa aturan untuk tabel temp saat MEMORY digunakan, bukan MyISAM. Mungkin skema kecil atau perubahan kueri dapat menghindari MyISAM. Indeks yang lebih baik dan perumusan ulang kueri lebih mungkin membantu

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

8 -- Persentase kueri yang membutuhkan tabel tmp di disk. -- Indeks yang lebih baik / Tidak ada gumpalan / dll

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / [Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ] = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

9 -- Persentase tabel temp yang tumpah ke disk -- Mungkin meningkatkan tmp_table_size [sekarang 16777216] dan max_heap_table_size [sekarang 16777216];

[ Innodb_pages_read + Innodb_pages_written ] / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107

0 -- Apakah Anda menutup pernyataan yang telah Anda siapkan?

[ Innodb_pages_read + Innodb_pages_written ] / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107

1 -- Pernyataan yang disiapkan harus Ditutup. -- Periksa apakah semua pernyataan Disiapkan "Tertutup"

[ Innodb_pages_read + Innodb_pages_written ] / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107

2 -- PERNYATAAN/BARIS/CAMPURAN. -- ROW lebih disukai oleh 5. 7 [10. 3]

[ Innodb_pages_read + Innodb_pages_written ] / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107

3 -- Cutoff [Detik] untuk menentukan kueri "lambat". -- Sarankan 2

[ Innodb_pages_read + Innodb_pages_written ] / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107

4 -- Laju hit cache subquery

[ Innodb_pages_read + Innodb_pages_written ] / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107

5 -- Apakah akan memasukkannya ke dalam slowlog. -- Ini mengacaukan slowlog; . Dan kurangi long_query_time [sekarang 5] untuk menangkap kueri yang paling menarik

[ Innodb_pages_read + Innodb_pages_written ] / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107

6 -- [Ukuran otomatis mulai tanggal 5. 6. 6;

Mengapa replikasi MySQL lambat?

Apakah replikasi MySQL memengaruhi kinerja?

Bagaimana cara mengurangi lag replikasi di MySQL?

Apakah MySQL yang tercepat?