MRatWork Forum by Mustafa Ramadhan - MySQL dengan Trafik Tinggi

Sponsor:
Server and Web Integrator
Link:
6.5.0 or 7.0.0
Click for "How to install"
Donation/Sponsorship:
Kloxo-MR is open-source.
Donate and or Sponsorship always welcome.
Click to:

MRatWork Forum | Please login or register. | 2017-11-07, 18:14:36

- MRatWork Forum by Mustafa Ramadhan »
- Language Specific Discussions »
- Indonesia Users »
- MySQL dengan Trafik Tinggi

« previous next »

- Print

Pages: [1] Go Down

### Author Topic: MySQL dengan Trafik Tinggi (Read 550 times)

0 Members and 1 Guest are viewing this topic.

#### napster

- Junior Member
- Posts: 18
- Karma: +0/-0

##### MySQL dengan Trafik Tinggi

« on: 2017-02-07, 13:48:13 »
Dear MR,
Pak MR saya mohon bantuan lengkap cara optimasi MySQL. Sedikit gambaran tentang server saya:
Server 1: Untuk data web (php, html, css, js, image, dll)
Server 2: Hanya untuk database MySQL (Menggunakan kloxo-mr)
Nah kendalaya adalah saat trafik sangat tinggi, server 2 sering down "Too Many Connections". Mohon jika ada cara untuk optimasi server 2 saya.
Terima kasih
Logged

#### MRatWork

- Administrator
- The Elite
- Posts: 15,381
- Karma: +112/-9
- Gender:

##### Re: MySQL dengan Trafik Tinggi

« Reply #1 on: 2017-02-07, 14:10:18 »
Coba install mysqltuner dengan 'yum install mysqltuner -y'. Kemudian jalankan dengan 'mysqltuner --user root --pass PasswordMySQLRoot' (ganti PasswordMySQLRoot dengan password sebenarnya). Ikuti pesan optimasinya.
Logged
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

#### napster

- Junior Member
- Posts: 18
- Karma: +0/-0

##### Re: MySQL dengan Trafik Tinggi

« Reply #2 on: 2017-02-07, 14:15:22 »
Sudah sy install pak, selanjutnya itu yang bingung.
Code: [Select]

    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Remove Anonymous User accounts - there are 2 Anonymous accounts.
        Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Restrict Host for user@% to user@SpecificDNSorIp
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce or eliminate persistent connections to reduce connection usage
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries which have no LIMIT clause
        Set thread_cache_size to 4 as a starting value
        Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
        max_connections (> 151)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)

Logged

#### MRatWork

- Administrator
- The Elite
- Posts: 15,381
- Karma: +112/-9
- Gender:

##### Re: MySQL dengan Trafik Tinggi

« Reply #3 on: 2017-02-07, 14:39:36 »
Jika anda pakai Kloxo-MR 7.0, edit /etc/my.cnf.d/server.conf. Bila pakai Kloxo-MR 6.5, edit /etc/my.cnf.
Setelah edit, lakukan restart dengan 'sh /script/restart-mysql' dan jalankan lagi mysqltuner sampai tidak ditemukannya pesan pada 'Variables to adjust'.
Pesan 'max_connections (> 151)' menunjukkan anda harus merubah/menambahkan 'max_connections=160' misalnya (dibawah '[mysqld]' header pada file .cnf terkait.
Untuk mengoptimize table lakukan 'sh /script/mysql-optimize --select=optimize'.
Logged
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

#### napster

- Junior Member
- Posts: 18
- Karma: +0/-0

##### Re: MySQL dengan Trafik Tinggi

« Reply #4 on: 2017-02-07, 15:29:20 »
Sudah saya coba semua pak, tinggal tunggu hasilnya, apa ada yg komplain lagi. Terima kasih pak bantuanya.
Logged

- Print

Pages: [1] Go Up
« previous next »

- MRatWork Forum by Mustafa Ramadhan »
- Language Specific Discussions »
- Indonesia Users »
- MySQL dengan Trafik Tinggi

Top 4 Global Search Engines: Google Bing Baidu Yahoo

- Design By SMFSimple.com
- SMF 2.0.14 | SMF © 2017, Simple Machines

Page created in 0.028 seconds with 17 queries.

Mirror created by MasterkinG32.CoM