MATERI 6 - BOTTLENECK PADA MYSQL

Saya, Marinda Cahya P, dari Kelas 2B dengan nomor absen 16, menulis materi ini sebagai rangkuman dari Praktikum Instalasi dan Konfigurasi Server Database yang dilaksanakan pada tanggal 19 Maret 2025. Topik yang dibahas dalam praktikum ini adalah Optimasi Bottleneck dalam MySQL. Rangkuman ini disusun sebagai salah satu syarat untuk memenuhi tugas praktikum Mata Kuliah Sistem Manajemen Basis Data. Jika terdapat kesalahan dalam penulisan atau gambar yang kurang jelas, saya mohon maaf. 

BOTTLENECK PADA MySQL

Latar Belakang

Bottleneck dalam MySQL adalah kondisi di mana kinerja database mengalami penurunan karena keterbatasan sumber daya atau masalah dalam eksekusi query. Penyebab utama bottleneck meliputi:

• Full table scan akibat tidak adanya indeks.

• Terlalu banyak koneksi database yang tidak dikelola dengan baik.

• Deadlock dalam transaksi akibat operasi yang tumpang tindih.

• Ukuran buffer pool yang kecil, menyebabkan MySQL sering membaca dari disk.

• Query yang tidak optimal, seperti penggunaan SELECT * yang mengambil semua kolom tanpa perlu.

Dengan memahami dan mengoptimalkan sumber bottleneck ini, kinerja database dapat ditingkatkan secara signifikan.

Permasalahan yang diangkat

Adapun Permasalahan yang diangkat dalam tugas ini yakni :

1. Bagaimana cara mengidentifikasi dan mengatasi bottleneck dalam MySQL?
2. Bagaimana cara mengoptimalkan query agar lebih cepat dan efisien?
3. Bagaimana cara memanfaatkan indeks untuk meningkatkan performa database?
4. Bagaimana cara mengelola ukuran buffer pool untuk memaksimalkan efisiensi?
5. Bagaimana pengaruh penggunaan query cache terhadap performa MySQL?

Skenario aktivitas 

Dalam tugas ini, dilakukan beberapa tahapan:

1. Pembuatan dan pengelolaan indeks untuk mempercepat query.
2. Penggunaan query cache untuk mengurangi beban query yang berulang.
3. Pengaturan buffer pool size untuk mengoptimalkan akses ke disk.
4. Pengujian performa query sebelum dan sesudah optimasi.
5. Penerapan optimasi bottleneck pada database minimarket.

Pembahasan (Latihan Soal)

1. Jalankan setiap langkah dari materi diatas, tuliskan pemahaman anda.

• Create Tabel User

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    nama VARCHAR(100) NOT NULL,

    email VARCHAR(100) NOT NULL UNIQUE

);

• Create Tabel Orders

CREATE TABLE orders (

    order_id INT AUTO_INCREMENT PRIMARY KEY,

    customer_id INT NOT NULL,

    STATUS VARCHAR(50),

    total DECIMAL(10, 2),

    order_date DATE,

    FOREIGN KEY (customer_id) REFERENCES users(id)

);

• Menambahkan Index
• Show Index

SHOW INDEX FROM nama_tabel;

• Index pada Kolom Where

CREATE INDEX idx_nama ON users(nama);

• Index pad akolom yang digunakan dalam join

CREATE INDEX idx_customer_id ON orders(customer_id);

• Index Multi-Kolom untuk query yang melibatkan beberapa kolom

CREATE INDEX idx_multi ON orders(customer_id, STATUS);

• Mengevaluasi Efektivitas Indeks

EXPLAIN SELECT id, email FROM users WHERE nama = 'John';

• Menggunakan Query Cache

menggunakan MySQL 8.0, karena fitur ini sudah dihapus dan digantikan oleh InnoDB

Buffer Pool (seperti dibawah ini).

• Meningkatkan Ukuran Buffer Pool

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

• Menghindari SELECT

SELECT nama, email FROM users WHERE id = 10;

2. Pada database minimartket, pada table tr_penjualan_raw, lakukan optimasi dengan menggunakan tehnik diatas. Gunakan index dan query cache.

• Indeks pada kolom WHERE

            CREATE INDEX idx_tgl_transaksi ON tr_penjualan_raw (tgl_transaksi);

            SHOW INDEX FROM tr_penjualan_raw;

            

• Indeks pada kolom JOIN

            CREATE INDEX idx_kode_item ON tr_penjualan_raw (kode_item);

      SHOW INDEX FROM tr_penjualan_raw;

• Indeks Multi-Kolom untuk query yang melibatkan beberapa kolom

CREATE INDEX idx_nama_kasir ON tr_penjualan_raw (nama_kasir);

SHOW INDEX FROM tr_penjualan_raw;

Pada Gambar tersebut menjelaskan tampilan indeks dengan Where, join, Indeks Multi-Kolom untuk query yang melibatkan beberapa kolom dengan query CREATE INDEX idx_kode_item ON tr_penjualan_raw (kode_item);

• Dengan menggunakan MySQL 8.0, karena fitur ini sudah dihapus dan digantikan oleh InnoDB Buffer Pool.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Pada Gambar tersebut merupakan hasil dari oleh InnoDB Buffer Pool dengan memori tot 8 GB.

SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024; -- 4GB

SHOW ENGINE INNODB STATUS;

Pada Gambar tersebut menampilkan dengan memori total 8 GB dan akan memakai oleh InnoDB Buffer Pool size 4 GB atau setengah dari 8 GB(memori total)

3. Query berikut apakah sudah optimal?? Jika belum, lakukan optimasi.

SELECT * FROM tr_penjualan_raw WHERE YEAR(tgl_transaksi) = 2024;a.  Redesaian tabel tr_penjualan, tambahkan partisi pada tabel tersebut. Sehingga  ada tabel baru tr_penjualan_partisi

• Query tr_penjualan_raw pada tahun 2014

SELECT * FROM tr_penjualan_raw WHERE YEAR(tgl_transaksi) = 2014;

• Optimasi Create Index

CREATE INDEX idx_tgl_transaksi ON tr_penjualan_raw (tgl_transaksi);

• Melakukan Select data dalam tabel tr_penjualan_raw

SELECT * FROM tr_penjualan_raw

WHERE tgl_transaksi BETWEEN '2014-01-01' AND '2014-12-31';

Pada Gambar Diatas merupakan tabel tr_penjualan_raw yang sudah di berikan index, dengan tahun yang sudah ditentukan saya menggunakan tahun yakni tahun 2014 dengan rentang rentang tanggal '2014-01-01' AND '2014-12-31';

4. Query berikut apakah sudah optimal? Jika belum, lakukan optimasi 

SELECT * FROM tr_penjualan_raw WHERE kode_item IN ('ITEM1', 'ITEM2', 'ITEM3', ...,'ITEM500');

• Select data dari tabel tr_penjualan_raw

SELECT * FROM tr_penjualan_raw

WHERE kode_item IN (

    'ITM-038', 'ITM-020', 'ITM-017', 'ITM-007', 'ITM-015',

    'ITM-006', 'ITM-035', 'ITM-034', 'ITM-022', 'ITM-021',

    'ITM-005', 'ITM-012', 'ITM-009', 'ITM-031'

);

• Create Index pada tabel tr_penjualan_raw

CREATE INDEX idx_kode_item ON tr_penjualan_raw (kode_item);

• Melakukan Select data dalam tabel tr_penjualan_raw

SELECT * FROM tr_penjualan_raw

WHERE kode_item IN ('ITM-038', 'ITM-020', 'ITM-017', 'ITM-007', 'ITM-015',

    'ITM-006', 'ITM-035', 'ITM-034', 'ITM-022', 'ITM-021',

    'ITM-005', 'ITM-012', 'ITM-009', 'ITM-031'

);

Pada Gambar Diatas merupakan tabel tr_penjualan_raw yang sudah di berikan index, dengan tahun yang sudah ditentukan yakni ITM-038', 'ITM-020', 'ITM-017', 'ITM-007', 'ITM-015', 'ITM-006', 'ITM-035', 'ITM-034', 'ITM-022', 'ITM-021', 'ITM-005', 'ITM-012', 'ITM-009', 'ITM-031';

5. Query berikut apakah sudah optimal?? Jika belum, lakukan optimasi

SELECT * FROM tr_penjualan_raw WHERE nama_kasir LIKE ‘Sari';

• Select data tabel tr_penjualan_raw

ALTER TABLE tr_penjualan_raw ADD FULLTEXT(nama_kasir);

• Lalu Mengupdate data dengan query berikut

UPDATE tr_penjualan_raw SET nama_kasir = 'Sari' WHERE kode_kasir = '039-212';

• Select data dari tabel kolom tr_penjualan_raw

SELECT * FROM tr_penjualan_raw WHERE nama_kasir LIKE 'Sari';

Pada Gambar tersebut menunjukan memasukkan 1 nama saja dengan nama “Sari”, dan Menjelaskan bahwa query ini akan menampilkan data di mana nama kasir berisi teks 'Sari', baik di awal, di tengah, maupun di akhir kata.

6. Diberikan query berikut. Pada kolom harga belum ada index. Apakah query berikut sudah optimal?? Jelaskan langkah2 optimasinya.

SELECT MAX(harga) FROM tr_penjualan_raw WHERE kode_cabang = 'CB001';

• Update data

UPDATE tr_penjualan_raw SET harga = 20000 WHERE kode_kasir = '039-212';

memperbarui data di tabel tr_penjualan_raw.

Semua baris yang memiliki kode_kasir = '039-212' akan diperbarui, dengan kolom harga diubah menjadi 20000.

• Select max

SELECT MAX(harga) FROM tr_penjualan_raw WHERE kode_cabang = 'CABANG-039';

Pada gambar tersebut mencari harga tertinggi (MAX(harga)) dalam tabel tr_penjualan_raw untuk transaksi yang terjadi di cabang dengan kode 'CABANG-039'. Hasilnya akan berupa satu nilai, yaitu harga tertinggi dari cabang tersebut yaitu 20000.

• Select data

SELECT * FROM tr_penjualan_raw WHERE nama_kasir LIKE '%Sari%';

Pada Gambar tersebut Perintah ini menampilkan semua kolom (*) dari tabel tr_penjualan_raw di mana nama_kasir mengandung kata "Sari" di mana saja dalam Namanya sari.

Kesimpulan

• Indeks sangat penting untuk mempercepat query, terutama pada kolom yang sering digunakan dalam WHERE dan JOIN.
• Query cache dapat membantu mempercepat eksekusi ulang query yang sering digunakan.
• Buffer pool yang lebih besar mengurangi akses ke disk, meningkatkan efisiensi database.
• Menghindari SELECT * membuat query lebih cepat dan efisien.
• Pengujian dengan EXPLAIN sangat berguna untuk mengetahui apakah query sudah dioptimalkan dengan baik.
• Dengan menerapkan teknik optimasi bottleneck ini, database MySQL dapat berjalan lebih efisien, mengurangi waktu eksekusi query, dan meningkatkan kapasitas penanganan data dalam jumlah besar.

Referensi

MySQL Indexs

MySQL Idexs Stackoverflow

Sql indexing and performance tuning