• Masim “Vavai” Sugianto

    Founder PT. Excellent Infotama Kreasindo– perusahaan email services provider (ESP) dan menjadi konsultan pada berbagai instansi pemerintah, korporasi besar, lembaga perbankan dan institusi pendidikan di Indonesia.

    Lebih detail tentang Vavai.




  • Publishing

  • Recent Posts

    • TOEFL ITP Score (Kedua)
    • Bisnis Kelapa Muda
    • Model Usaha
    • Back-End Developer, Front-End Developer & Staff IT Support
    • Markas Excellent Danita
  • Recent Comments

    • asebg.bigcartel.comcanadian-pharmacy on Instalasi Squid Proxy Server pada OpenSUSE
    • dkyubn.bizwebs.com on Instalasi Squid Proxy Server pada OpenSUSE
    • http://aonubs.website2.me/ on Instalasi Squid Proxy Server pada OpenSUSE
    • canadian pharmacy online on Instalasi Squid Proxy Server pada OpenSUSE
    • site592154748.fo.team on Instalasi Squid Proxy Server pada OpenSUSE
  • Archives

    • April 2022
    • January 2022
    • July 2021
    • June 2021
    • February 2021
    • January 2021
    • December 2020
    • August 2020
    • July 2020
    • April 2020
    • March 2020
    • August 2019
    • July 2019
    • June 2019
    • March 2019
    • February 2019
    • January 2019
    • November 2018
    • October 2018
    • August 2018
    • May 2018
    • April 2018
    • January 2018
    • December 2017
    • July 2017
    • December 2016
    • November 2016
    • April 2016
    • March 2016
    • November 2015
    • July 2015
    • April 2015
    • November 2014
    • October 2014
    • February 2014
    • January 2014
    • December 2013
    • November 2013
    • October 2013
    • September 2013
    • August 2013
    • June 2013
    • May 2013
    • March 2013
    • February 2013
    • January 2013
    • December 2012
    • November 2012
    • October 2012
    • September 2012
    • August 2012
    • July 2012
    • June 2012
    • May 2012
    • April 2012
    • March 2012
    • February 2012
    • January 2012
    • December 2011
    • November 2011
    • October 2011
    • September 2011
    • August 2011
    • July 2011
    • June 2011
    • May 2011
    • April 2011
    • March 2011
    • February 2011
    • January 2011
    • December 2010
    • November 2010
    • October 2010
    • September 2010
    • August 2010
    • July 2010
    • June 2010
    • May 2010
    • April 2010
    • March 2010
    • February 2010
    • January 2010
    • October 2008
    • September 2008
    • August 2008
    • July 2008
    • June 2008
    • May 2008
    • April 2008
    • March 2008
    • February 2008
    • January 2008
    • December 2007
    • November 2007
    • October 2007
    • September 2007
    • August 2007
    • July 2007
    • June 2007
    • May 2007
    • April 2007
    • March 2007
  • Home
  • Bisnis & Marketing
  • Computer & IT Related
  • Lifestyle
  • Publishing
  • Free Stuff
  • About
  • Kontak

Tips Improvement Performance MySQL Server Secara Mudah

December 19, 2012

Setelah proses instalasi, langkah berikutnya untuk melakukan setup MySQL Server yang handal adalah dengan melakukan proses tuning terhadap konfigurasi MySQL. Berikut adalah beberapa hal yang bisa dilakukan untuk meningkatkan kehandalannya

3.1 Check Standar Keamanan

Untuk mengecek kehandalan sistem MySQL Server yang baru diinstall, cukup dengan melakukan perintah :

mysql_secure_installation

Setelah dijalankan, MySQL akan melakukan pengecekan terhadap konifgurasi default. Ikuti petunjuk yang diberikan untuk memperbaiki standar konfigurasi yang ada.

linux:/srv # mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we’ll need the current

password for the root user. If you’ve just installed MySQL, and

you haven’t set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MySQL

root user without the proper authorisation.

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

… Success!

By default, a MySQL installation has an anonymous user, allowing anyone

to log into MySQL without having to have a user account created for

them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n]

Setelah selesai dicheck, restart ulang service MySQL.

3.2 Menggunakan Konfigurasi yang Tepat

MySQL secara default menggunakan konfigurasi moderate, dalam arti bahwa konfigurasi yang yang diberikan merupakan konifgurasi yang menyesuaikan dengan sistem server secara umum. Ada penyesuaian antara performa dengan stabilitas. Tidak diset performa tinggi agar tidak mempengaruhi stabilitas sistem.mysql-performance-tuning

Kita bisa dengan mudah melakukan ujicoba tuning sistem dengan cara membackup file konfigurasi standard dan kemudian mengubah beberapa opsi/parameter yang tersedia.

Untuk parameter yang ditune up, tidak perlu dihapalkan satu persatu, karena MySQL sudah menyediakannya pada folder /usr/share/mysql. Pada folder tersebut ada nama file konfigurasi : my-small.cnf, my-medium.cnf, my-large.cnf dan my-huge.cnf.

Salin atau modifikasi file asli /etc/my.cnf yang sudah dibackup dan membandingkannya dengan file konfigurasi yang diinginkan diatas.

3.3 Menggunakan MySQLTuner

Ada juga cara termudah untuk memastikan bahwa MySQL Server yang kita install sudah dikonfigurasi dengan baik, yaitu dengan menggunakan script mysqltuner. Script ini dibuat dengan bahasa Perl dan akan melakukan pengecekan terhadap konfigurasi database MySQL yang kita miliki. Jangan lupa untuk membackup file konfigurasi yang ada sebelum menjalankannya.

Berikut adalah cara menggunakan MySQLTuner :

wget mysqltuner.pl –no-check-certificate

chmod +x mysqltuner.pl

./mysqltuner.pl

Contoh tampilan script saat dijalankan :

# ./mysqltuner.pl

>> MySQLTuner 1.2.0 – Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with β€˜β€“help’ for additional options and output filtering

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:

——– General Statistics ————————————————–

[–] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.95

[OK] Operating on 64-bit architecture

——– Storage Engine Statistics β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”-

[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[!!] InnoDB is enabled but isn’t being used

[OK] Total fragmented tables: 0

——– Security Recommendations β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”-

[OK] All database users have passwords assigned

——– Performance Metrics β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”-

[–] Up for: 2h 33m 25s (59 q [0.006 qps], 25 conn, TX: 23K, RX: 3K)

[–] Reads / Writes: 86% / 14%

[–] Total buffers: 42.0M global + 1.9M per thread (100 max threads)

[OK] Maximum possible memory usage: 229.5M (11% of installed RAM)

[OK] Slow queries: 0% (0/59)

[OK] Highest usage of available connections: 1% (1/100)

[OK] Key buffer size / total MyISAM indexes: 16.0M/21.0K

[!!] Key buffer hit rate: 66.7% (12 cached / 4 reads)

[!!] Query cache is disabled

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)

[OK] Temporary tables created on disk: 0% (0 on disk / 8 total)

[!!] Thread cache is disabled

[OK] Table cache hit rate: 73% (17 open / 23 opened)

[OK] Open file limit used: 3% (34/1K)

[OK] Table locks acquired immediately: 100% (30 immediate / 30 locks)

[!!] Connections aborted: 8%

——– Recommendations —————————————————–

General recommendations:

Add skip-innodb to MySQL configuration to disable InnoDB

MySQL started within last 24 hours – recommendations may be inaccurate

Enable the slow query log to troubleshoot bad queries

Set thread_cache_size to 4 as a starting value

Your applications are not closing MySQL connections properly

Variables to adjust:

query_cache_size (>= 8M)

thread_cache_size (start at 4)

Beberapa tips diatas bisa dengan mudah dijalankan sehingga MySQL Server yang baru disetup bisa berjalan dengan baik, tidak dibiarkan demikian adanya :-)

LinuxMySQLResourceServer
Share

Resource  / Tips, Tricks & Tutorial

Masim Vavai Sugianto
Masim Vavai Sugianto, Tinggal di Bekasi, Bekerja sebagai wirausahawan/Konsultan IT. Penganjur penggunaan sistem Linux dan aplikasi Open Source. Hobby Membaca, Hiking dan Avonturir. Mengembangkan PT. Excellent Infotama Kreasindo sebagai lembaga training dan IT consulting.

You might also like

Eskalasi Masalah Email : mailbox unavailable invalid DNS MX or A/AAAA resource record
July 14, 2020
Update Script untuk Ubuntu Initial Server Setup
April 20, 2020
Instalasi Dasar Ubuntu Server 18.04 64 bit
April 19, 2020

Leave A Reply


Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

  • Artikel



  • Blogroll

    • DNS Propagation Checker
    • Instagram Photo/Video Downloader
    • PT. Excellent Infotama Kreasindo
  • Publishing

  • Recent Posts

    • TOEFL ITP Score (Kedua)
    • Bisnis Kelapa Muda
    • Model Usaha
    • Back-End Developer, Front-End Developer & Staff IT Support
    • Markas Excellent Danita
    • Pekerjaan Excellent
    • iMac 2021
    • Problem Solving
    • Kolam Mina Padi/Mina Genjer
    • Team Excellent


  • Links Ads

  • Recent Post

    • TOEFL ITP Score (Kedua)
    • Bisnis Kelapa Muda
    • Model Usaha
    • Back-End Developer, Front-End Developer & Staff IT Support
    • Markas Excellent Danita
    • Pekerjaan Excellent
    • iMac 2021
    • Problem Solving
    • Kolam Mina Padi/Mina Genjer
    • Team Excellent
    • Telur Bebek Mentah
    • Kursi Kayu dan Gerobak Angkringan
    • Kebun Pisang Barangan dan Ambon Kuning
    • Ternak Bebek
    • Data Statistik Covid dan Edukasi Media
  • Recent Comments

    • asebg.bigcartel.comcanadian-pharmacy on Instalasi Squid Proxy Server pada OpenSUSE
    • dkyubn.bizwebs.com on Instalasi Squid Proxy Server pada OpenSUSE
    • http://aonubs.website2.me/ on Instalasi Squid Proxy Server pada OpenSUSE
    • canadian pharmacy online on Instalasi Squid Proxy Server pada OpenSUSE
    • site592154748.fo.team on Instalasi Squid Proxy Server pada OpenSUSE
    • kqwsh.wordpress.com20220516what-everybody-else-does-when-it-comes-to-online-pharmacies on Instalasi Squid Proxy Server pada OpenSUSE
    • canadian rx on Instalasi Squid Proxy Server pada OpenSUSE
    • kwersd.mystrikingly.com on Instalasi Squid Proxy Server pada OpenSUSE
    • canadian prescriptions online on Instalasi Squid Proxy Server pada OpenSUSE
    • avuiom.sellfy.store on Instalasi Squid Proxy Server pada OpenSUSE