cara mengumpulkan statistik secara manual atau otomatis - AnalyticDB

Topik ini menjelaskan tujuan dan jenis statistik di AnalyticDB for MySQL. Topik ini juga mencakup cara statistik dikumpulkan secara otomatis, cara mengumpulkannya secara manual, serta cara melihatnya.

Pengenalan fungsi

Pengoptimal kueri menggunakan statistik untuk menghasilkan rencana eksekusi berkualitas tinggi bagi mesin eksekusi. Kualitas rencana eksekusi secara langsung mempengaruhi performa kueri.

AnalyticDB for MySQL menyediakan fitur pengumpulan statistik otomatis yang diaktifkan secara default. Kluster dengan versi kernel 3.1.9.2 atau lebih baru juga mendukung statistik grup kolom. Fitur ini dinonaktifkan secara default dan harus diaktifkan secara manual. Selama jendela pemeliharaan, AnalyticDB for MySQL secara otomatis melakukan pengumpulan penuh atau berdasarkan sampel dari statistik dasar, histogram, dan grup kolom sesuai dengan volume data tabel. Pengumpulan penuh mungkin memerlukan beberapa hari untuk selesai jika diperlukan statistik untuk banyak kolom. Di luar jendela pemeliharaan, AnalyticDB for MySQL secara berkala melakukan pengumpulan tambahan otomatis dari statistik dasar.

AnalyticDB for MySQL menggunakan kebijakan pengumpulan yang berbeda tergantung pada metode impor data:

Untuk impor batch menggunakan INSERT OVERWRITE, AnalyticDB for MySQL segera dan secara otomatis mengumpulkan statistik dasar.
Untuk impor real-time menggunakan INSERT INTO atau REPLACE INTO, AnalyticDB for MySQL memicu tugas pengumpulan tambahan selama jendela pemeliharaan atau periode pengumpulan tambahan setelah pembuatan selesai. Kami menyarankan Anda untuk mengumpulkan statistik dasar secara manual sekali setelah mengimpor data.

Anda juga dapat menonaktifkan pengumpulan statistik otomatis dan menjalankan pernyataan ANALYZE TABLE untuk mengumpulkan statistik secara manual. Untuk informasi lebih lanjut, lihat Mengumpulkan Statistik Secara Manual.

Pengumpulan statistik otomatis di AnalyticDB for MySQL hanya berlaku untuk tabel internal, bukan tabel eksternal. Pengumpulan statistik manual berlaku untuk tabel internal dan eksternal.

Catatan penggunaan

Fitur statistik didukung pada kluster Edisi Perusahaan, Edisi Dasar, Edisi Data Lakehouse, dan Edisi Data Warehouse dengan versi kernel 3.1.6.1 atau lebih baru.

Catatan

Untuk melihat dan memperbarui versi minor dari kluster AnalyticDB for MySQL, masuk ke Konsol AnalyticDB for MySQL dan buka bagian Configuration Information di halaman Cluster Information.

Klasifikasi dan pemilihan statistik

AnalyticDB for MySQL mengumpulkan tiga jenis statistik: dasar (BASIC), histogram (HISTOGRAM), dan grup kolom (GROUP_STATS). Histogram dan statistik grup kolom dikumpulkan melalui pengumpulan penuh atau berdasarkan sampel. Statistik dasar dikumpulkan melalui pengumpulan penuh, berdasarkan sampel, atau koleksi tambahan otomatis. Metode default adalah pengumpulan tambahan otomatis.

Penting

Hanya kluster dengan versi kernel 3.1.9.2 atau lebih baru yang mendukung pengumpulan berdasarkan sampel untuk statistik dasar, histogram, dan grup kolom.

Statistik dasar

Statistik dasar mencakup nilai maksimum, nilai minimum, panjang rata-rata dalam byte, jumlah nilai unik, dan proporsi nilai NULL dalam sebuah kolom.

Skenario:

Kolom yang tidak digunakan dalam operasi penyaringan atau penggabungan.
Kolom dengan distribusi data yang merata, seperti kolom kunci primer.

Histogram

Histogram dibuat dengan membagi data dalam rentang menjadi bucket berdasarkan statistik dasar dari sebuah tabel. Setiap bucket dalam histogram menggambarkan karakteristik data dari rentang tertentu.

Klasifikasi:

Histogram Hibrida: Variasi dari histogram ketinggian sama yang dapat lebih baik menggambarkan nilai hotspot.
Histogram Frekuensi: Cocok untuk kolom dengan jumlah nilai unik yang sedikit. Setiap nilai sesuai dengan satu bucket.

Catatan

AnalyticDB for MySQL secara otomatis memilih histogram yang sesuai untuk Anda.

Skenario: Kolom dengan distribusi data yang tidak merata yang digunakan dalam operasi penyaringan dan penggabungan. Jika data didistribusikan secara merata, statistik dasar dapat menggantikan histogram dalam skenario penyaringan dan penggabungan.

Dibandingkan dengan statistik dasar, histogram lebih akurat mencerminkan statistik tabel tetapi lebih mahal dan menggunakan lebih banyak ruang cache. Dalam skenario dengan banyak tabel, mengumpulkan histogram untuk semua kolom dapat menurunkan rasio hit cache. Cache default dapat menampung statistik untuk sekitar 20.000 kolom histogram atau 2 juta kolom statistik dasar.

Statistik grup kolom

Penting

Hanya kluster dengan versi kernel 3.1.9.2 atau lebih baru yang mendukung fitur statistik grup kolom.

Statistik dasar dan histogram dikumpulkan untuk kolom tunggal. Statistik grup kolom dikumpulkan untuk beberapa kolom dalam tabel yang sama dan menggambarkan korelasi di antara mereka.

Skenario: Digunakan untuk agregasi multi-kolom. Jika beberapa kolom sangat berkorelasi, gunakan statistik grup kolom untuk lebih akurat memperkirakan jumlah baris keluaran dan memilih metode eksekusi yang lebih baik.

Pengumpulan statistik otomatis

Aktifkan atau nonaktifkan pengumpulan statistik otomatis

AnalyticDB for MySQL mendukung pengumpulan statistik otomatis. Fitur ini diaktifkan secara default. Anda dapat menjalankan perintah berikut untuk menonaktifkan atau mengaktifkannya kembali.

SET adb_config O_CBO_AUTONOMOUS_STATS_ENABLED = [false｜true];

Aktifkan atau nonaktifkan pengumpulan otomatis statistik grup kolom

Kluster dengan versi kernel 3.1.9.2 atau lebih baru mendukung pengumpulan otomatis statistik grup kolom. Fitur ini dinonaktifkan secara default. Anda dapat menjalankan perintah berikut untuk mengaktifkan atau menonaktifkannya.

SET adb_config O_CBO_MAINTENANCE_WINDOW_COLLECT_GROUP_STATS_ENABLED = [false｜true];

Catatan

Sebelum Anda menggunakan pengumpulan otomatis statistik grup kolom, pastikan bahwa pengumpulan statistik otomatis telah diaktifkan.

Tetapkan jendela pemeliharaan

Jendela pemeliharaan default untuk pengumpulan statistik otomatis adalah 04:00 hingga 05:00. Anda dapat menjalankan perintah berikut untuk mengubah jendela pemeliharaan. Kami menyarankan Anda menetapkan jendela pada jam-jam sepi. Interval antara waktu mulai dan waktu akhir harus minimal 1 menit dan tidak lebih dari 3 jam. Waktu mulai harus lebih awal dari waktu akhir. Jika Anda memasukkan waktu yang tidak valid, waktu default akan digunakan.

Catatan

Jendela pemeliharaan menggunakan zona waktu yang sama dengan kluster.

SET adb_config O_CBO_MAINTENANCE_WINDOW_DURATION = [04:00-05:00];

Tetapkan ambang batas volume data untuk pengumpulan statistik

Anda dapat menggunakan perintah berikut untuk menetapkan ambang batas volume data untuk pengumpulan statistik. Nilai defaultnya adalah 5.000.000.000 (5 miliar) baris.

SET adb_config O_CBO_MAINTENANCE_WINDOW_COLLECTOR_ROW_LIMIT = 10000;

Jika jumlah baris dalam tabel melebihi ambang batas volume data:

Untuk kluster dengan versi kernel lebih awal dari 3.1.9.2, pengumpulan statistik melewati tabel tersebut.
Untuk kluster dengan versi kernel 3.1.9.2 atau lebih baru, statistik untuk tabel tersebut dikumpulkan melalui sampling.

Aktifkan atau nonaktifkan batas beban untuk pengumpulan statistik

Selama jendela pemeliharaan, AnalyticDB for MySQL membatasi beban selama pengumpulan statistik otomatis untuk mengurangi penggunaan sumber daya I/O saat memindai tabel. Fitur ini diaktifkan secara default. Jika sumber daya idle selama jendela pemeliharaan, Anda dapat menonaktifkan batas beban untuk mempercepat pembaruan statistik.

SET adb_config O_CBO_AUTONOMOUS_STATS_SCAN_RATE_LIMIT_ENABLED = [false｜true];

Kumpulkan statistik dalam kelompok sumber daya tertentu

Secara default, pengumpulan statistik otomatis menggunakan akun sistem untuk menjalankan perintah. Untuk menjalankan perintah pengumpulan statistik otomatis dalam kelompok sumber daya tertentu, Anda dapat menjalankan perintah berikut untuk menentukan akun database. Setelah Anda menentukan akun database, AnalyticDB for MySQL menjalankan perintah dalam kelompok sumber daya yang terpasang pada akun tersebut. Akun database harus memiliki izin kueri pada semua kolom dari semua tabel dan terpasang pada kelompok sumber daya. Untuk informasi lebih lanjut tentang cara memasang kelompok sumber daya, lihat Buat dan Kelola Kelompok Sumber Daya.

SET adb_config O_CBO_AUTONOMOUS_STATS_ACCOUNT = [user_name];

Tetapkan rasio kedaluwarsa untuk kolom

Rasio kedaluwarsa default untuk kolom adalah 0,1 (10%). Ketika rasio baris yang terpengaruh oleh operasi `UPDATE`, `DELETE`, `INSERT`, atau `REPLACE` terhadap total jumlah baris dalam tabel melebihi rasio kedaluwarsa, statistik untuk tabel tersebut dianggap kedaluwarsa. AnalyticDB for MySQL kemudian mengumpulkan ulang statistik untuk semua kolom tabel yang kedaluwarsa selama jendela pemeliharaan. Jika rasio kedaluwarsa tidak terlampaui, statistik tidak dikumpulkan secara otomatis selama jendela pemeliharaan.

SET adb_config O_CBO_STATS_EXPIRED_RATIO = 0.1;

Kumpulkan statistik secara manual

Kumpulkan statistik pada seluruh tabel

Perintah ANALYZE TABLE memindai seluruh tabel untuk mengumpulkan statistik. Untuk tabel besar, mengumpulkan statistik pada seluruh tabel bisa memakan waktu lama. Kami menyarankan Anda menjalankan perintah ini selama jam-jam sepi atau mengumpulkan statistik melalui sampling.

Sintaksis

ANALYZE TABLE [schema_name.]table_name [UPDATE [BASIC|HISTOGRAM|GROUP_STATS]] [ON column_name[,...]] [WITH ENABLE SAMPLING]

Parameter

Parameter	Wajib	Deskripsi
`schema_name`	Tidak	Nama database.
`table_name`	Ya	Nama tabel. AnalyticDB for MySQL mengumpulkan statistik untuk tabel ini. Setiap pernyataan `ANALYZE TABLE` hanya dapat menentukan satu tabel. Tabel tersebut dapat berupa tabel internal atau eksternal.
`UPDATE [BASIC\|HISTOGRAM\|GROUP_STATS]`	Tidak	Jenis statistik. Nilai yang valid: BASIC (default): statistik dasar. HISTOGRAM: histogram. GROUP_STATS: statistik grup kolom. Penting Hanya kluster dengan versi kernel 3.1.9.2 atau lebih baru yang mendukung fitur statistik grup kolom.
`ON column_name[,...]`	Tidak	Kolom untuk mengumpulkan statistik. Jika Anda tidak menentukan kolom, statistik dikumpulkan untuk semua kolom dalam tabel.
`WITH ENABLE SAMPLING`	Tidak	Kumpulkan statistik melalui sampling. Penting Hanya kluster dengan versi kernel 3.1.9.2 atau lebih baru yang mendukung pengumpulan berdasarkan sampel untuk statistik dasar, histogram, dan grup kolom.

Contoh

Kumpulkan statistik dasar pada semua kolom tabel adb_demo.customer. Anda dapat menggunakan salah satu dari pernyataan berikut:
- ```
ANALYZE TABLE adb_demo.customer;
```
- ```
ANALYZE TABLE adb_demo.customer UPDATE BASIC;
```
Kumpulkan statistik dasar pada kolom customer_id dari tabel adb_demo.customer.
```
ANALYZE TABLE adb_demo.customer UPDATE BASIC ON customer_id;
```
Kumpulkan informasi histogram pada kolom customer_id dan login_time dari tabel adb_demo.customer.
```
ANALYZE TABLE adb_demo.customer UPDATE HISTOGRAM ON customer_id,login_time;
```
Kumpulkan statistik grup kolom melalui sampling pada kolom customer_id dan login_time dari tabel adb_demo.customer.
```
ANALYZE TABLE adb_demo.customer UPDATE GROUP_STATS ON customer_id,login_time with enable sampling;
```

Kumpulkan statistik pada partisi

Batasan versi

Hanya kluster Edisi Perusahaan, Edisi Dasar, dan Edisi Data Lakehouse dengan versi kernel 3.1.9.1 atau lebih baru yang mendukung penggunaan perintah ANALYZE TABLE untuk melakukan pengumpulan penuh statistik dasar pada partisi tabel eksternal OSS.

Catatan

Untuk melihat dan memperbarui versi minor dari kluster AnalyticDB for MySQL, masuk ke Konsol AnalyticDB for MySQL dan buka bagian Configuration Information di halaman Cluster Information.

Sintaksis

ANALYZE TABLE table_name WITH PARTITIONS = ARRAY[ARRAY[PARTITION_KEYS] [, PARTITION_KEYS, ....]]

Deskripsi parameter

Parameter	Wajib	Deskripsi
`table_name`	Ya	Nama tabel. AnalyticDB for MySQL mengumpulkan statistik untuk tabel ini. Setiap pernyataan `ANALYZE TABLE` hanya dapat menentukan satu tabel. Tabel tersebut dapat berupa tabel internal atau eksternal.
`PARTITION_KEYS`	Ya	Partisi untuk mengumpulkan statistik.

Contoh

Kumpulkan statistik pada partisi 2023-01 dan 2023-02 dari tabel partisi test1.

ANALYZE TABLE test1 WITH PARTITIONS = ARRAY[ARRAY['2023-01'], ARRAY['2023-02']];

Kumpulkan statistik pada partisi (1,1) dan (1,0) dari tabel partisi test2.

ANALYZE TABLE test2 WITH PARTITIONS = ARRAY[ARRAY[1, 1], ARRAY[1, 0]];

Lihat statistik

Statistik disimpan dalam format biner di AnalyticDB for MySQL. Anda dapat melihat statistik di tabel sistem INFORMATION_SCHEMA.

Jalankan perintah berikut untuk melihat statistik tingkat tabel:
```
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
```
Jalankan perintah berikut untuk melihat statistik tingkat kolom, termasuk statistik dasar, histogram, dan statistik grup kolom:
```
SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
```

Tanya Jawab Umum

Mengapa perintah ANALYZE didiagnosis sebagai kueri lambat?

Pernyataan ANALYZE yang diinisiasi secara otomatis selama jendela pemeliharaan dieksekusi dengan pembatasan I/O dan prioritas CPU rendah. Pernyataan tersebut mungkin didiagnosis sebagai kueri lambat karena dieksekusi dalam waktu lama. Namun, ini tidak mempengaruhi layanan. Jika beban CPU tidak tinggi atau overload CPU tidak terkait erat dengan jendela pemeliharaan, Anda dapat mengabaikan masalah ini. Jika CPU terus-menerus overload, lihat bagian "Apa yang harus dilakukan jika waktu respons kueri terpengaruh oleh overload CPU yang disebabkan oleh pengumpulan statistik?" dari topik ini untuk menyelesaikan masalah ini.

Apa penyebab beban CPU tinggi saat fitur statistik digunakan?

CPU mungkin menjadi overload karena alasan berikut:

Selama jendela pemeliharaan default dari 04:00 hingga 05:00, sistem melakukan pemindaian penuh pada setiap tabel untuk mengumpulkan statistik kolom. Selama periode ini, CPU menjadi overload.
Dalam sebagian besar kasus, statistik dikumpulkan secara tambahan, yang tidak mengonsumsi banyak sumber daya. Secara default, fitur statistik diaktifkan untuk kluster AnalyticDB for MySQL Edisi Data Warehouse yang menjalankan V3.1.6 atau lebih baru. Saat kluster diperbarui dari versi minor sebelumnya ke V3.1.6 atau lebih baru, statistik penuh dikumpulkan. Ini dapat menyebabkan overload CPU selama beberapa hari pertama setelah pembaruan versi minor. Beban CPU menurun setelah pemindaian data penuh selesai.

Saat CPU overload, periksa apakah waktu respons kueri terpengaruh. Jika waktu respons kueri rata-rata tidak berubah secara signifikan, waktu respons kueri tidak terpengaruh. Nilai metrik penggunaan CPU mungkin tinggi selama pengumpulan statistik, tetapi ketika kueri dijalankan, sumber daya dialokasikan secara prioritas untuk eksekusi. Hal ini karena pernyataan ANALYZE dieksekusi dengan pembatasan I/O dan prioritas CPU rendah.

Apa yang harus dilakukan jika beban CPU tinggi mempengaruhi waktu respons kueri?

Anda dapat menyelesaikan masalah ini dengan menggunakan solusi berikut:

Ubah jendela pemeliharaan ke jam-jam sepi.

set adb_config O_CBO_MAINTENANCE_WINDOW_DURATION = [04:00-05:00];

Jika Anda tidak dapat menentukan jam-jam sepi yang tepat, kami menyarankan Anda mengubah batas I/O untuk kueri sistem menjadi nilai yang lebih besar dari atau sama dengan 16 MB. Nilai defaultnya adalah 50 MB.
```
set adb_config CSTORE_IO_LIMIT_SYSTEM_QUERY_BPS = 52428800;
```
Tetapkan pengumpulan statistik ke kelompok sumber daya prioritas rendah untuk mengisolasi beban. Untuk informasi lebih lanjut, lihat bagian "Pengumpulan Statistik Otomatis" dari topik Statistik.
```
set adb_config O_CBO_AUTONOMOUS_STATS_ACCOUNT = [user_name];
```
Tingkatkan rasio kedaluwarsa untuk kolom untuk mengurangi data yang harus dikumpulkan. Nilai defaultnya adalah 0,1. Rentang nilainya antara 0 dan 1. Kami menyarankan Anda tidak menetapkan rasio kedaluwarsa lebih dari 0,5.
```
set adb_config O_CBO_STATS_EXPIRED_RATIO = 0.1;
```

Jika tidak ada solusi di atas yang menyelesaikan masalah ini, jalankan pernyataan set adb_config O_CBO_AUTONOMOUS_STATS_ENABLED=false; untuk menonaktifkan pengumpulan statistik otomatis. Namun, kinerja mungkin menurun. Jika Anda ingin mengumpulkan statistik di masa mendatang, Anda harus mengumpulkan statistik secara manual. Untuk informasi lebih lanjut, lihat bagian "Kumpulkan Statistik Secara Manual" dari topik Statistik.

Mengapa statistik tidak diperbarui selama beberapa hari?

Masalah ini disebabkan oleh alasan berikut:

Statistik belum kedaluwarsa.
Statistik kedaluwarsa ketika jumlah data yang diperbarui, dimasukkan, atau diganti mencapai rasio kedaluwarsa. Rasio kedaluwarsa default adalah 0,1 (10%). Jika hanya sejumlah kecil data Anda yang berubah, Anda dapat terus menggunakan kluster Anda seperti yang diharapkan dan amati masalah ini selama seminggu lagi.
Sejumlah besar data terdapat di banyak kolom dan tabel.
Secara default, hanya 1 jam per hari diperlukan untuk mengumpulkan statistik yang tidak termasuk dalam pembaruan tambahan. Jika banyak kolom dan tabel terlibat, seperti lebih dari 1.000 kolom, sistem mungkin tidak dapat menyelesaikan pembaruan dalam satu hari. Pembaruan mungkin memerlukan satu minggu untuk selesai. Dalam hal ini, tidak adanya pembaruan statistik dalam beberapa hari adalah normal. Anda dapat terus menggunakan kluster seperti yang diharapkan dan amati masalah ini.

Apakah statistik diperbarui secara otomatis ketika data diimpor ke tabel baru?

Jika data diimpor secara batch menggunakan pernyataan INSERT OVERWRITE, statistik dasar dikumpulkan secara otomatis setelah impor data selesai. Jika data diimpor secara real-time menggunakan pernyataan INSERT INTO atau REPLACE INTO, statistik dikumpulkan selama jendela pemeliharaan berikutnya atau tugas pengumpulan tambahan dipicu selama periode pengumpulan tambahan setelah setiap tugas BUILD selesai. Kami menyarankan Anda untuk mengumpulkan statistik dasar secara manual setelah data diimpor. Untuk informasi lebih lanjut, lihat bagian "Kumpulkan Statistik Secara Manual" dari topik Statistik.