Cara menggunakan query cache mysql 8

Akhir tahun adalah saat yang paling tepat untuk beres-beres setelah setahun menulis kode program. Salah satu contoh bentuk upaya beres-beres tersebut misalnya adalah memeriksa apakah kinerja database yang dipakai oleh aplikasi sudah maksimal. Sama seperti kendaraan bermotor, database juga perlu dirawat secara berkala. Bila yang merawat sepeda motor adalah mekanik di bengkel, maka profesi yang bertugas merawat database dinamakan sebagai database administrator.

Untuk mengukur kinerja program, saya akan mencoba mensimulasikan operasi pengisian sejumlah data penjualan. Operasi ini tidak hanya terdiri atas operasi INSERT, tetapi juga operasi SELECT untuk mencari nama konsumen dan nama produk. Hasil yang saya peroleh adalah dibutuhkan waktu 30.053 ms untuk menyelesaikan query yang ada. Ini adalah perkiraan kasar karena masih belum memperhitungkan jeda waktu yang ditimbulkan oleh jaringan dan kesibukan server saat diakses secara bersamaan. Walaupun demikian, tanpa memperhitungkan hal lain tersebut, apakah nilai ‘dasar’ ini bisa ditingkatkan?

Konfiguras MySQL Server bisa dilakukan dengan mengubah file my.ini. Saya dapat menemukan file ini di lokasi C:\Program Files\MySQL\MySQL Server 5.6 atau di C:\ProgramData\MySQL\MySQL Server 5.6. Untuk lokasi yang lebih akurat, saya dapat menjalankan MySQL dari Command Prompt dan memberikan perintah

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

0. Disini akan ada daftar lokasi pada bagian Default options are read from the following files in the given order:. Saya juga dapat melakukan pengaturan secara sementara tanpa mengubah file my.ini dengan memberikan perintah

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

1. Akan tetapi, karena ingin melakukan perbandingan, saya memilih mengubah file my.ini dan me-restart database setiap kali pengujian dilakukan.

Salah satu pengaturan yang paling klasik adalah mengubah nilai

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

2. Ini adalah besarnya wilayah di memori yang dialokasikan khusus untuk menampung cache yang berisi informasi tabel dan index. Semakin besar ukuran buffer pool, maka semakin sedikit operasi disk yang dibutuhkan. Nilai default-nya yang berupa 128 MB merupakan sesuatu yang cukup kecil bila dipakai pada server dengan jumlah memori mencapai gigabyte. Dokumentasi MySQL Server merekomendasikan nilai 80% dari jumlah memori untuk database yang sibuk. Jangan lupa bahwa nilai

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

2 yang terlalu besar malah bisa mengakibatkan perlambatan akibat paging.

Sebagai percobaan, saya mengubah konfigurasi menjadi seperti berikut ini:

innodb_buffer_pool_size=512M
innodb_log_file_size=512M

Saya juga meningkatkan ukuran

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

4 untuk mengurangi aktifitas flush. Hasil akhir percobaan setelah perubahan konfigurasi menunjukkan bahwa terdapat peningkatan waktu eksekusi sebesar 7%. Ini adalah peningkatan yang cukup besar. Sayangnya, saya tidak bisa menaikkan

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

2 menjadi 1GB karena walaupun memiliki free memori lebih dari jumlah tersebut, Windows mensyaratkan lokasi memori bebas harus berlanjut dan tidak boleh tersebar! Batasan ini hanya terasa pada Windows 32-bit dan hampir tidak terjadi di Windows 64-bit (akibat ruang alokasi memori yang masih luas 🙂 ).

Sejak versi 5.6.8, nilai

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

6 secara default adalah 0 sehingga query cache tidak aktif. Query cache adalah fitur untuk menampung hasil query secara sementara di memori sehingga hasil yang sama bisa dikembalikan dengan cepat untuk SQL yang sama. Tentu saja query cache harus dihapus bila isi tabel yang dibaca sudah berubah sejak terakhir kali dibaca. Bila query cache terlalu sering kadaluarsa, ini bukannya mempercepat malah bisa memperlambat. Dengan demikian, query cache bisa bermanfaat dan juga bisa merugikan tergantung dari seberapa sering aplikasi mengubah isi tabelnya.

Sebagai contoh, saya akan menggunakan

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

7 bawaan MySQL Server untuk mensimulasikan query

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

8 yang dilakukan oleh beberapa klien secara bersamaan dengan memberikan perintah berikut ini:

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

Perintah di atas akan mensimulasikan akses dari 20 client secara bersamaan yang mengerjakan isi query di file select.sql selama 10 kali.

Sebagai perbandingan, saya kemudian mengaktifkan query cache dengan menambahkan baris berikut ini di my.ini:

query_cache_type=1
query_cache_size=5MB

Kali ini, bila saya memberikan perintah yang sama, saya akan memperoleh hasil seperti berikut ini:

C:\> mysqlslap --user=root --password --create-schema=inventory
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 36.043 seconds
        Minimum number of seconds to run all queries: 31.943 seconds
        Maximum number of seconds to run all queries: 41.467 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

Terlihat bahwa mengaktifkan query cache hanya membuat query menjadi lambat. Mengapa bisa demikian? Untuk melihat status query cache, saya dapat memberikan perintah SQL berikut ini:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 327     |
| Qcache_free_memory      | 988680  |
| Qcache_hits             | 1196351 |
| Qcache_inserts          | 65103   |
| Qcache_lowmem_prunes    | 64153   |
| Qcache_not_cached       | 240682  |
| Qcache_queries_in_cache | 950     |
| Qcache_total_blocks     | 2351    |
+-------------------------+---------+

Nilai

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

9 menunjukkan jumlah query yang terpaksa dihapus karena ukuran query cache sudah tidak cukup lagi. Pada hasil di atas, nilainya cukup tinggi. Walaupun demikian, nilai

query_cache_type=1
query_cache_size=5MB

0 yang tinggi menunjukkan bahwa query cache seharusnya bisa membantu. Oleh sebab itu, saya akan mencoba meningkatkan ukuran query cache dengan mengubah konfigurasi menjadi seperti berikut ini:

query_cache_type=1
query_cache_size=10MB

Hasil ketika menjalankan

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

7 adalah:

C:\> mysqlslap --user=root --password --create-schema=inventory
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 24.233 seconds
        Minimum number of seconds to run all queries: 22.326 seconds
        Maximum number of seconds to run all queries: 31.803 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

Walaupun sudah tidak lambat lagi, saya tidak menjumpai peningkatkan kinerja yang cukup berarti saat mengaktifkan query cache. Hal ini cukup masuk akal karena saat ini belum ada tabel raksasa yang membutuhkan waktu pencarian lama sehingga query cache belum dapat menunjukkan taringnya.

Bicara soal jaringan, MySQL Server memiliki kebiasaan melakukan request DNS untuk melakukan validasi host pada saat pengguna login. Bila DNS server yang dipakai lambat (misalnya bawaan ISP), maka hal ini bisa menimbulkan kesan bahwa koneksi database sangat lambat pada aplikasi pertama kali dijalankan. Untuk mengatasinya, saya bisa mematikan request DNS dan hanya melakukan validasi berdasarkan IP dengan menambahkan konfigurasi berikut ini:

skip_name_resolve=ON

Fitur lainnya yang berguna untuk tabel berukuran besar adalah kompresi tabel. MySQL Server mendukung 2 jenis file format untuk InnoDB: Antelope (versi original) dan Barracuda (versi terbaru). File format yang dipakai secara default adalah Antelope untuk memaksimalkan kompatibilitas dengan fitur lama. Karena suka ber-eksperimen, saya akan beralih ke Barracuda dengan menambahkan baris berikut ini di file konfigurasi:

innodb_file_format=Barracuda

Salah satu fitur baru yang ditawarkan oleh format Barracuda adalah kompresi tabel. Kompresi dapat diakses dengan menambahkan

query_cache_type=1
query_cache_size=5MB

2 pada saat memberikan perintah

query_cache_type=1
query_cache_size=5MB

3 atau

query_cache_type=1
query_cache_size=5MB

4 seperti pada:

ALTER TABLE `produk`
ROW_FORMAT = COMPRESSED ;

Untuk bisa memakai kompresi, saya perlu memastikan bahwa nilai

query_cache_type=1
query_cache_size=5MB

5 adalah

query_cache_type=1
query_cache_size=5MB

6 (nilai default sejak versi 5.6.6). Lagi-lagi saya tidak memiliki tabel yang berukuran sangat besar sehingga saya tidak menjumpai peningkatan kinerja yang cukup berarti dengan mengaktifkan kompresi tabel.

kode mysql

Cara menggunakan query cache mysql 8

Pos Terkait

Periklanan

BERITA TERKINI

Toplist Popular

Periklanan

Terpopuler

Periklanan

Tentang Kami

Legal

Dukungan

Social