Laporan ini mengkuantifikasi dampak terhadap queries per second (QPS) akibat pengaktifan dan konfigurasi Performance Schema pada ApsaraDB RDS for MySQL dan PolarDB for MySQL. Gunakan data ini untuk menimbang kemampuan pemantauan terhadap overhead performa sebelum menyesuaikan pengaturan Performance Schema Anda.
Temuan utama pada tingkat konkurensi 32: Menonaktifkan Performance Schema sepenuhnya (pfsoff) memberikan QPS tertinggi. Mengaktifkan pemantauan penuh (pfson_conon_inson) mengurangi QPS hingga ~23% pada RDS dan ~24% pada PolarDB dalam beban kerja write-only. Mengaktifkan konfigurasi DAS Performance Insight (pfson_conon_ins_sqlon) menambahkan overhead 13–22% sekaligus menyediakan diagnostik tingkat statement.
Skenario pengujian
Tabel berikut menjelaskan lingkungan pengujian.
| Atribut | Nilai |
|---|---|
| Basis Data | ApsaraDB RDS for MySQL, PolarDB for MySQL |
| Tool pengujian | SysBench |
| Tabel pengujian | 10 |
| Tingkat konkurensi | 1, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1.024 |
Spesifikasi instans dan kluster
| Database | Edisi | Instance family | Tipe instans | CPU dan memori | Koneksi maksimum | IOPS maksimum |
|---|---|---|---|---|---|---|
| ApsaraDB RDS for MySQL | Edisi Ketersediaan Tinggi | Tujuan umum | mysql.n4.large.2c | 4 core, 16 GB | 8.000 | 2.800 |
| PolarDB for MySQL | Edisi Kluster | Spesifikasi khusus | polar.mysql.x4.large | 4 core, 16 GB | 8.000 | 48.000 |
SysBench adalah alat benchmark modular, open-source, lintas platform, dan multi-threaded untuk mengevaluasi performa database di bawah beban berat. Untuk informasi lebih lanjut, lihat dokumentasi SysBench.
Skenario dengan pengaturan parameter berbeda
Skenario berikut didefinisikan berdasarkan kombinasi parameter performance_schema serta item konfigurasi consumers dan instrumentation. Setiap skenario merepresentasikan tingkat kemampuan pemantauan yang berbeda.
Untuk informasi tentang cara memodifikasi parameter performance_schema, lihat Modifikasi parameter instans ApsaraDB RDS for MySQL.| Skenario | Performance Schema | Consumers | Instrumentation | Kemampuan pemantauan |
|---|---|---|---|---|
| pfsoff | Dinonaktifkan | — | — | Tidak ada. Performa garis dasar. |
| pfson_conoff_insoff | Diaktifkan | Semua OFF | Semua OFF | Minimal — Performance Schema aktif tetapi tidak mengumpulkan data. |
| pfson_conon_insoff | Diaktifkan | Semua ON | Semua OFF | Sebagian — pipeline consumer aktif, tetapi tidak ada titik instrumentation yang dilacak. |
| pfson_conon_ins_sqlon | Diaktifkan | Dipilih ON (lihat di bawah) | Instrumentation statement ON | Statistik tingkat statement. Ini adalah konfigurasi yang digunakan oleh fitur DAS Performance Insight versi baru. |
| pfson_conoff_inson | Diaktifkan | Semua OFF | Semua ON | Instrumentation aktif, tetapi tidak ada consumer yang memproses data. |
| pfson_conon_inson | Diaktifkan | Semua ON | Semua ON | Pemantauan penuh — semua event dan statistik dikumpulkan. Overhead tertinggi. |
Perintah SQL untuk setiap skenario
pfson_conoff_insoff
-- Nonaktifkan semua consumer
UPDATE performance_schema.setup_consumers SET Enabled = 'NO';
-- Nonaktifkan semua instrumentation
UPDATE performance_schema.setup_instruments SET Enabled = 'NO', timed = 'NO';pfson_conon_insoff
-- Aktifkan semua consumer
UPDATE performance_schema.setup_consumers SET Enabled = 'YES';
-- Nonaktifkan semua instrumentation
UPDATE performance_schema.setup_instruments SET Enabled = 'NO', timed = 'NO';pfson_conon_ins_sqlon
-- Aktifkan consumer tertentu: global_instrumentation, thread_instrumentation,
-- events_statements_current, events_statements_history, statements_digest
UPDATE performance_schema.setup_consumers
SET enabled = IF(name IN (
'global_instrumentation',
'thread_instrumentation',
'events_statements_current',
'events_statements_history',
'statements_digest'
), 'YES', 'NO');
-- Aktifkan hanya instrumentation statement
UPDATE performance_schema.setup_instruments
SET enabled = IF(name LIKE 'statement/%', 'YES', 'NO'),
timed = enabled;pfson_conoff_inson
-- Nonaktifkan semua consumer
UPDATE performance_schema.setup_consumers SET Enabled = 'NO';
-- Aktifkan semua instrumentation
UPDATE performance_schema.setup_instruments SET Enabled = 'YES', timed = 'YES';pfson_conon_inson
-- Aktifkan semua consumer
UPDATE performance_schema.setup_consumers SET Enabled = 'YES';
-- Aktifkan semua instrumentation
UPDATE performance_schema.setup_instruments SET Enabled = 'YES', timed = 'YES';Hasil pengujian
Rata-rata QPS pada tingkat konkurensi 32, diukur setelah utilisasi CPU mencapai 100%. Skenario pfsoff merupakan garis dasar (Performance Schema dinonaktifkan). Semua skenario lain menunjukkan penurunan QPS relatif terhadap garis dasar tersebut.
ApsaraDB RDS for MySQL
| Skenario | QPS read-only | vs. garis dasar | QPS read/write | vs. garis dasar | QPS write-only | vs. garis dasar |
|---|---|---|---|---|---|---|
| pfsoff | 47.176 | Garis dasar | 37.999 | Garis dasar | 39.940 | Garis dasar |
| pfson_conoff_insoff | 41.111 | −12,86% | 33.170 | −12,71% | 32.327 | −19,06% |
| pfson_conon_insoff | 41.773 | −11,45% | 33.568 | −11,66% | 31.434 | −21,30% |
| pfson_conon_ins_sqlon | 39.889 | −15,44% | 33.056 | −13,01% | 31.865 | −20,22% |
| pfson_conoff_inson | 39.344 | −16,61% | 32.698 | −13,95% | 31.698 | −20,64% |
| pfson_conon_inson | 39.168 | −16,97% | 32.280 | −15,05% | 30.834 | −22,81% |
PolarDB for MySQL
| Skenario | QPS read-only | vs. garis dasar | QPS read/write | vs. garis dasar | QPS write-only | vs. garis dasar |
|---|---|---|---|---|---|---|
| pfsoff | 36.768 | Garis dasar | 38.876 | Garis dasar | 39.550 | Garis dasar |
| pfson_conoff_insoff | 31.981 | −13,02% | 33.342 | −14,24% | 34.182 | −13,57% |
| pfson_conon_insoff | 31.187 | −15,18% | 32.980 | −15,17% | 31.768 | −19,68% |
| pfson_conon_ins_sqlon | 30.976 | −15,75% | 31.980 | −17,77% | 31.101 | −21,36% |
| pfson_conoff_inson | 31.025 | −15,62% | 32.855 | −15,49% | 31.750 | −19,72% |
| pfson_conon_inson | 29.652 | −19,35% | 30.656 | −21,14% | 30.014 | −24,11% |
Data pengujian
Rata-rata QPS di seluruh tingkat konkurensi (1 hingga 1.024), diukur setelah utilisasi CPU mencapai 100%.
ApsaraDB RDS for MySQL
Skenario read-only
Tabel 1. Data pengujian dalam skenario read-only
| Tingkat konkurensi | pfsoff | pfson_conoff_insoff | pfson_conon_insoff | pfson_conon_ins_sqlon | pfson_conoff_inson | pfson_conon_inson |
|---|---|---|---|---|---|---|
| 1 | 3.456 (garis dasar) | 3.209 (−7,16%) | 3.233 (−6,45%) | 3.179 (−8,02%) | 3.249 (−5,98%) | 3.116 (−9,84%) |
| 2 | 9.432 (garis dasar) | 8.668 (−8,10%) | 8.701 (−7,75%) | 8.588 (−8,95%) | 8.854 (−6,13%) | 8.313 (−11,86%) |
| 4 | 16.485 (garis dasar) | 14.965 (−9,22%) | 15.123 (−8,26%) | 14.926 (−9,46%) | 14.942 (−9,36%) | 14.053 (−14,75%) |
| 8 | 25.614 (garis dasar) | 23.024 (−10,11%) | 23.286 (−9,09%) | 22.781 (−11,06%) | 23.017 (−10,14%) | 21.659 (−15,44%) |
| 16 | 39.846 (garis dasar) | 35.869 (−9,98%) | 35.483 (−10,95%) | 34.162 (−13,14%) | 34.288 (−13,95%) | 33.387 (−16,21%) |
| 32 | 47.176 (garis dasar) | 41.111 (−12,86%) | 41.773 (−11,45%) | 39.889 (−15,44%) | 39.344 (−16,61%) | 39.168 (−16,97%) |
| 64 | 49.230 (garis dasar) | 42.186 (−14,31%) | 42.776 (−13,11%) | 41.791 (−15,11%) | 41.747 (−15,20%) | 38.877 (−21,03%) |
| 128 | 48.201 (garis dasar) | 41.458 (−13,99%) | 41.280 (−14,36%) | 40.797 (−15,36%) | 40.990 (−14,96%) | 36.532 (−24,21%) |
| 256 | 46.982 (garis dasar) | 40.090 (−14,67%) | 39.869 (−15,14%) | 39.556 (−15,81%) | 40.414 (−13,98%) | 34.602 (−26,35%) |
| 512 | 47.026 (garis dasar) | 41.279 (−15,22%) | 40.353 (−14,19%) | 39.488 (−16,03%) | 39.337 (−16,35%) | 34.023 (−27,65%) |
| 1.024 | 47.533 (garis dasar) | 40.104 (−15,63%) | 40.032 (−15,78%) | 39.509 (−16,88%) | 39.462 (−16,98%) | 33.696 (−29,11%) |
Skenario write-only
Tabel 2. Data pengujian dalam skenario write-only
| Tingkat konkurensi | pfsoff | pfson_conoff_insoff | pfson_conon_insoff | pfson_conon_ins_sqlon | pfson_conoff_inson | pfson_conon_inson |
|---|---|---|---|---|---|---|
| 1 | 3.614 (garis dasar) | 3.305 (−8,56%) | 3.293 (−8,88%) | 3.269 (−9,56%) | 3.354 (−7,19%) | 3.222 (−10,86%) |
| 2 | 8.452 (garis dasar) | 7.644 (−9,56%) | 7.547 (−10,71%) | 7.606 (−10,01%) | 7.603 (−10,04%) | 7.281 (−13,86%) |
| 4 | 13.648 (garis dasar) | 12.231 (−10,38%) | 12.193 (−10,66%) | 12.106 (−11,30%) | 12.057 (−11,66%) | 11.676 (−14,45%) |
| 8 | 25.648 (garis dasar) | 22.227 (−13,34%) | 22.824 (−11,01%) | 22.360 (−12,82%) | 22.534 (−12,14%) | 21.431 (−16,44%) |
| 16 | 32.154 (garis dasar) | 27.119 (−15,66%) | 27.669 (−13,95%) | 27.093 (−15,74%) | 27.347 (−14,95%) | 26.170 (−18,61%) |
| 32 | 39.940 (garis dasar) | 32.327 (−19,06%) | 31.434 (−21,30%) | 31.865 (−20,22%) | 31.698 (−20,64%) | 30.834 (−22,81%) |
| 64 | 39.123 (garis dasar) | 30.915 (−20,98%) | 31.220 (−20,20%) | 30.661 (−21,63%) | 30.672 (−21,06%) | 29.514 (−24,56%) |
| 128 | 41.320 (garis dasar) | 32.692 (−20,88%) | 32.610 (−21,08%) | 32.135 (−22,23%) | 32.184 (−22,11%) | 29.423 (−28,79%) |
| 256 | 42.004 (garis dasar) | 33.322 (−20,67%) | 33.124 (−21,14%) | 32.838 (−21,82%) | 32.814 (−21,88%) | 30.348 (−27,75%) |
| 512 | 43.156 (garis dasar) | 34.033 (−21,14%) | 33.597 (−22,15%) | 33.783 (−21,72%) | 33.196 (−23,08%) | 30.157 (−30,12%) |
| 1.024 | 47.562 (garis dasar) | 37.056 (−22,09%) | 36.232 (−23,82%) | 37.113 (−21,97%) | 36.723 (−22,79%) | 32.256 (−32,18%) |
Read/Write Scenario
Tabel 3. Data pengujian dalam skenario read/write
| Tingkat konkurensi | pfsoff | pfson_conoff_insoff | pfson_conon_insoff | pfson_conon_ins_sqlon | pfson_conoff_inson | pfson_conon_inson |
|---|---|---|---|---|---|---|
| 1 | 2.997 (garis dasar) | 2.754 (−8,1%) | 2.844 (−5,12%) | 2.758 (−7,98%) | 2.741 (−8,55%) | 2.672 (−10,86%) |
| 2 | 8.135 (garis dasar) | 7.304 (−10,22%) | 7.610 (−6,45%) | 7.420 (−8,79%) | 7.498 (−7,83%) | 7.226 (−11,17%) |
| 4 | 14.568 (garis dasar) | 13.031 (−10,55%) | 13.427 (−7,83%) | 13.178 (−9,54%) | 13.164 (−9,64%) | 12.754 (−12,45%) |
| 8 | 24.658 (garis dasar) | 21.933 (−11,05%) | 22.663 (−8,09%) | 21.854 (−11,37%) | 22.130 (−10,25%) | 21.250 (−13,82%) |
| 16 | 31.562 (garis dasar) | 28.119 (−10,91%) | 28.443 (−9,88%) | 27.629 (−12,46%) | 27.936 (−11,49%) | 26.957 (−14,61%) |
| 32 | 37.999 (garis dasar) | 33.170 (−12,71%) | 33.568 (−11,66%) | 33.056 (−13,01%) | 32.698 (−13,95%) | 32.280 (−15,05%) |
| 64 | 39.120 (garis dasar) | 33.311 (−14,85%) | 33.948 (−13,22%) | 33.068 (−15,47%) | 33.659 (−13,96%) | 31.605 (−19,21%) |
| 128 | 41.687 (garis dasar) | 35.742 (−14,26%) | 35.334 (−15,24%) | 35.451 (−14,96%) | 35.488 (−14,87%) | 32.899 (−21,08%) |
| 256 | 42.654 (garis dasar) | 36.243 (−15,03%) | 33.570 (−16,14%) | 36.115 (−15,33%) | 35.949 (−15,72%) | 33.133 (−22,32%) |
| 512 | 43.156 (garis dasar) | 36.626 (−15,13%) | 36.087 (−16,38%) | 36.302 (−15,88%) | 36.286 (−15,92%) | 32.518 (−24,65%) |
| 1.024 | 43.658 (garis dasar) | 36.699 (−15,94%) | 36.625 (−16,11%) | 36.585 (−16,20%) | 36.756 (−15,81%) | 32.359 (−25,88%) |
PolarDB for MySQL
Skenario read-only
Tabel 4. Data pengujian dalam skenario read-only
| Tingkat konkurensi | pfsoff | pfson_conoff_insoff | pfson_conon_insoff | pfson_conon_ins_sqlon | pfson_conoff_inson | pfson_conon_inson |
|---|---|---|---|---|---|---|
| 1 | 3.389 (garis dasar) | 3.296 (−2,74%) | 3.233 (−4,62%) | 3.179 (−2,56%) | 3.249 (−4,13%) | 3.016 (−11,04%) |
| 2 | 8.868 (garis dasar) | 8.632 (−3,08%) | 8.601 (−3,78%) | 8.588 (−3,16%) | 8.654 (−2,84%) | 8.213 (−7,39%) |
| 4 | 16.965 (garis dasar) | 14.485 (−14,62%) | 15.123 (−10,86%) | 14.926 (−12,02%) | 14.942 (−11,92%) | 13.053 (−23,06%) |
| 8 | 25.024 (garis dasar) | 22.614 (−9,63%) | 23.286 (−9,01%) | 22.781 (−8,96%) | 23.017 (−8,14%) | 21.659 (−14,45%) |
| 16 | 38.869 (garis dasar) | 35.846 (−9,88%) | 35.483 (−8,95%) | 34.162 (−12,11%) | 34.288 (−11,79%) | 32.387 (−16,68%) |
| 32 | 36.768 (garis dasar) | 31.981 (−13,02%) | 31.187 (−15,18%) | 30.976 (−15,75%) | 31.025 (−15,62%) | 29.652 (−19,35%) |
| 64 | 49.186 (garis dasar) | 42.230 (−14,14%) | 42.776 (−13,07%) | 41.791 (−15,03%) | 41.747 (−15,12%) | 39.877 (−18,93%) |
| 128 | 42.958 (garis dasar) | 39.201 (−11,88%) | 40.280 (−9,08%) | 39.797 (−10,43%) | 39.990 (−10,11%) | 37.532 (−12,63%) |
| 256 | 45.090 (garis dasar) | 41.026 (−9,01%) | 39.869 (−11,58%) | 39.556 (−12,27%) | 40.414 (−10,37%) | 35.602 (−21,04%) |
| 512 | 46.279 (garis dasar) | 41.004 (−11,14%) | 40.353 (−12,81%) | 39.488 (−14,67%) | 39.337 (−15,02%) | 34.023 (−26,48%) |
| 1.024 | 47.231 (garis dasar) | 41.533 (−12,06%) | 40.032 (−15,24%) | 39.509 (−16,35%) | 39.462 (−16,45%) | 33.696 (−28,66%) |
Skenario write-only
Tabel 5. Data pengujian dalam skenario write-only
| Tingkat konkurensi | pfsoff | pfson_conoff_insoff | pfson_conon_insoff | pfson_conon_ins_sqlon | pfson_conoff_inson | pfson_conon_inson |
|---|---|---|---|---|---|---|
| 1 | 3.505 (garis dasar) | 3.364 (−4,07%) | 3.354 (−5,41%) | 3.269 (−6,73%) | 3.293 (−6,05%) | 3.122 (−10,93%) |
| 2 | 7.844 (garis dasar) | 7.452 (−4,56%) | 7.603 (−4,22%) | 7.606 (−5,09%) | 7.547 (−3,44%) | 7.281 (−7,18%) |
| 4 | 13.231 (garis dasar) | 12.648 (−4,38%) | 12.057 (−8,87%) | 12.106 (−8,30%) | 12.193 (−7,85%) | 10.676 (−19,31%) |
| 8 | 25.227 (garis dasar) | 22.648 (−10,22%) | 22.534 (−10,68%) | 22.360 (−11,36%) | 22.824 (−9,53%) | 20.431 (−19,01%) |
| 16 | 27.119 (garis dasar) | 26.154 (−13,66%) | 26.347 (−11,95%) | 25.093 (−14,74%) | 26.669 (−12,95%) | 22.170 (−18,25%) |
| 32 | 39.550 (garis dasar) | 34.182 (−13,57%) | 31.768 (−19,68%) | 31.101 (−21,36%) | 31.750 (−19,72%) | 30.014 (−24,11%) |
| 64 | 37.915 (garis dasar) | 31.223 (−17,65%) | 30.672 (−19,13%) | 30.661 (−19,12%) | 31.220 (−17,66%) | 29.514 (−22,16%) |
| 128 | 34.692 (garis dasar) | 31.320 (−16,88%) | 31.884 (−12,08%) | 32.135 (−10,76%) | 31.910 (−14,11%) | 29.423 (−15,22%) |
| 256 | 33.390 (garis dasar) | 31.704 (−7,67%) | 31.814 (−7,14%) | 32.018 (−5,41%) | 31.124 (−9,88%) | 29.348 (−12,11%) |
| 512 | 34.033 (garis dasar) | 31.156 (−8,45%) | 31.196 (−8,34%) | 31.783 (−6,61%) | 31.597 (−7,16%) | 30.157 (−11,39%) |
| 1.024 | 36.855 (garis dasar) | 33.862 (−8,12%) | 33.723 (−8,52%) | 33.613 (−8,81%) | 33.232 (−9,83%) | 32.956 (−10,58%) |
Skenario read/write
Tabel 6. Data pengujian dalam skenario read/write
| Tingkat konkurensi | pfsoff | pfson_conoff_insoff | pfson_conon_insoff | pfson_conon_ins_sqlon | pfson_conoff_inson | pfson_conon_inson |
|---|---|---|---|---|---|---|
| 1 | 3.305 (garis dasar) | 2.754 (−16,67%) | 2.843 (−13,98%) | 2.758 (−16,55%) | 2.741 (−17,07%) | 2.614 (−20,91%) |
| 2 | 8.668 (garis dasar) | 7.304 (−15,74%) | 7.610 (−12,21%) | 7.420 (−14,42%) | 7.498 (−13,52%) | 7.182 (−17,14%) |
| 4 | 14.965 (garis dasar) | 13.031 (−12,92%) | 13.427 (−10,28%) | 13.178 (−11,94%) | 13.164 (−12,03%) | 12.754 (−14,77%) |
| 8 | 23.068 (garis dasar) | 21.933 (−9,94%) | 21.663 (−6,09%) | 21.354 (−8,68%) | 21.130 (−8,41%) | 20.250 (−12,22%) |
| 16 | 35.869 (garis dasar) | 28.119 (−14,96%) | 28.443 (−17,71%) | 28.629 (−16,74%) | 27.936 (−16,95%) | 26.957 (−24,85%) |
| 32 | 38.876 (garis dasar) | 33.342 (−14,24%) | 32.980 (−15,17%) | 31.980 (−17,74%) | 32.855 (−15,49%) | 30.656 (−21,14%) |
| 64 | 42.186 (garis dasar) | 33.311 (−20,86%) | 33.948 (−19,53%) | 33.068 (−21,61%) | 33.659 (−20,21%) | 31.305 (−25,79%) |
| 128 | 40.359 (garis dasar) | 35.742 (−11,44%) | 35.334 (−12,45%) | 35.451 (−12,16%) | 35.488 (−12,07%) | 32.899 (−18,48%) |
| 256 | 40.090 (garis dasar) | 36.243 (−9,67%) | 33.570 (−16,26%) | 36.115 (−9,92%) | 35.949 (−10,33%) | 33.125 (−17,37%) |
| 512 | 41.220 (garis dasar) | 36.626 (−11,15%) | 36.087 (−12,45%) | 36.302 (−11,93%) | 36.286 (−11,98%) | 32.451 (−20,27%) |
| 1.024 | 40.055 (garis dasar) | 36.699 (−8,38%) | 36.625 (−8,56%) | 36.585 (−8,66%) | 36.756 (−8,24%) | 32.860 (−17,96%) |