Sponsor:

Server and Web Integrator
Link:
Kloxo-MR logo
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:
Click Here
Please login or register. 2017-11-07, 20:47:12

Author Topic: Minta saran settingan database my.cnf RAM 16GB 4Ghz  (Read 2315 times)

0 Members and 1 Guest are viewing this topic.

Offline mino

  • Junior Member
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Mohon batuannya gan jika visitor mulai rame web saya sering down sekalipun gak down pasti jadi lemot, ini masalahnya ada di server mysql atau ada masalah lain ya gan??

tolong di bantu gan ;D

Code: [Select]
[mysqld]
skip-innodb
default-storage-engine=myisam
#OpenVZ AIO bug fix
innodb_use_native_aio=0

## General
datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
skip-name-resolve
sql-mode                        = NO_ENGINE_SUBSTITUTION
#event-scheduler                = 1

## Cache
thread-cache-size               = 16
table-open-cache                = 4096
table-definition-cache          = 2048
query-cache-size                = 232M
query-cache-limit               = 264M

## Per-thread Buffers
sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 1M
join-buffer-size                = 1M

## Temp Tables
tmp-table-size                  = 232M
max-heap-table-size             = 264M

## Networking
back-log                        = 100
max-connections                 = 220
max-connect-errors              = 10000
max-allowed-packet              = 516M
interactive-timeout             = 3600
wait-timeout                    = 600

### Storage Engines
innodb                          = FORCE

## MyISAM
key-buffer-size                 = 3G
myisam-sort-buffer-size         = 512M

## InnoDB
#innodb-buffer-pool-size        = 512M
#innodb-log-file-size           = 100M
#innodb-log-buffer-size         = 8M
#innodb-file-per-table          = 1
#innodb-open-files              = 300

## Replication
server-id                       = 1
#log-bin                        = /var/log/mysql/bin-log
#relay-log                      = /var/log/mysql/relay-log
relay-log-space-limit           = 6G
expire-logs-days                = 7
#read-only                      = 1
#sync-binlog                    = 1
#log-slave-updates              = 1
#binlog-format                  = STATEMENT
#auto-increment-offset          = 1
#auto-increment-increment       = 2

## Logging
log-output                      = FILE
slow-query-log                  = 1
slow-query-log-file             = /var/log/mysql/slow-log
log-slow-slave-statements
long-query-time                 = 2

[mysqld_safe]
log-error                       = /var/log/mysqld.log
open-files-limit                = 655350

[mysql]
no-auto-rehash

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,381
  • Karma: +112/-9
  • Gender: Male
    • View Profile
    • MRatWork Forum
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #1 on: 2015-09-27, 17:59:24 »
Informasikan 'sh /script/sysinfo'.
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

Offline mino

  • Junior Member
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #2 on: 2015-09-28, 03:54:57 »
ini gan
Code: [Select]
A. Kloxo-MR: 6.5.0.f-2015083001

B. OS: CentOS release 6.7 (Final) x86_64

C. Apps:
   1. MySQL: mysql55-5.5.45-1.ius.el6.x86_64
   2. PHP: php53u-5.3.29-1.ius.el6.x86_64
   3. Httpd: httpd-2.2.31-1.mr.el6.x86_64
   4. Lighttpd: --uninstalled--
   5. Nginx: nginx-1.9.5-1.el6.ngx.x86_64
   6. Qmail: qmail-toaster-1.03-1.3.55.mr.el6.x86_64
      - with: courier-imap-toaster-4.1.2-1.3.18.mr.el6.x86_64
   7. Dns: bind-9.9.7-1.mr.el6.x86_64

D. Php-type (for Httpd/proxy): mod_php_ruid2

E. Memory:
                total       used       free     shared    buffers     cached
   Mem:         15425       8620       6805         19        301       4125
   -/+ buffers/cache:       4192      11232
   Swap:         7783          0       7783

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,381
  • Karma: +112/-9
  • Gender: Male
    • View Profile
    • MRatWork Forum
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #3 on: 2015-09-28, 05:33:56 »
1. Coba jalankan 'sh /script/apache-optimize' agak apache instance bisa banyak
2. Install 'yum mysqltuner -y'
3. Run mysqltuner with 'mysqltuner --user root --pass ROOTPASSWORD' (change ROOTPASSWORD for real mysql root password (find out in /usr/local/lxlabs/kloxo/etc/slavedb/dbadmin; or reset this password via 'MySQL Password Reset')
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

Offline mino

  • Junior Member
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #4 on: 2015-09-28, 06:35:18 »
ini output nya terus gimana gan?
Code: [Select]
 >>  MySQLTuner 1.1.1 - 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: admin
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.45-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 24M (Tables: 23)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user 'admin'@'localhost' for table 'user'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13h 38m 29s (14M q [297.933 qps], 2M conn, TX: 8B, RX: 2B)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 3.6G global + 4.2M per thread (220 max threads)
[OK] Maximum possible memory usage: 4.5G (29% of installed RAM)
[OK] Slow queries: 0% (1/14M)
[!!] Highest connection usage: 100%  (221/220)
[OK] Key buffer size / total MyISAM indexes: 3.0G/1.3M
[OK] Key buffer hit rate: 100.0% (442M cached / 1K reads)
[OK] Query cache efficiency: 82.9% (6M cached / 8M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 228K sorts)
[!!] Joins performed without indexes: 661732
[OK] Temporary tables created on disk: 5% (51 on disk / 919 total)
[OK] Thread cache hit rate: 98% (45K created / 2M connections)
[OK] Table cache hit rate: 61% (278 open / 450 opened)
[OK] Open file limit used: 0% (333/655K)
[!!] Table locks acquired immediately: 90%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    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
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    max_connections (> 220)
    wait_timeout (< 600)
    interactive_timeout (< 3600)
    join_buffer_size (> 1.0M, or always use indexes with joins)

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,381
  • Karma: +112/-9
  • Gender: Male
    • View Profile
    • MRatWork Forum
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #5 on: 2015-09-28, 07:22:59 »
Anda tambahkan dibawah '[mysqld]' pada /etc/my.cnf, misalnya 'join_buffer_size=1.0M' dan kemudian restart dengan 'service mysqld restart'. Jalankan lagi mysqltuner sampai tidak ada pesan 'Variables to adjust:'.
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

Offline mino

  • Junior Member
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #6 on: 2015-09-28, 07:36:07 »
sudah saya tambahkan tapi tetep gak hilang,,,, kira2 kenapa ya?

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,381
  • Karma: +112/-9
  • Gender: Male
    • View Profile
    • MRatWork Forum
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #7 on: 2015-09-28, 08:18:39 »
sudah saya tambahkan tapi tetep gak hilang,,,, kira2 kenapa ya?
Tweak tidak mudah. Perlu pengetahuan yang cukup dan hasilnya tidak seketika. Pada kasus anda, sebenarnya setting sudah cukup.

Untuk melacak penyebabnya, coba jalankan 'top' dari ssh dan perhatikan program yang makan memory/cpu.
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

Offline mino

  • Junior Member
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #8 on: 2015-09-29, 03:55:12 »
untuk optimasi nya gimana sepertinya user admin memakan banyak CPU nih?

Code: [Select]
 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
13187 admin     20   0  436m  48m  11m R 99.3  0.3   0:15.89 httpd
12855 admin     20   0  438m  50m  12m R 97.9  0.3   1:24.26 httpd
13854 mysql     20   0 4525m  85m 5168 S 11.3  0.6   4:10.28 mysqld
12766 apache    20   0  415m  29m  12m S 51.4  0.2   1:07.64 httpd
12675 admin     20   0  510m  29m  12m S  6.6  0.2   0:59.04 httpd
13245 apache    20   0  415m  28m  11m S  6.0  0.2   0:04.10 httpd
14284 admin     20   0  416m  29m  11m S  6.0  0.2   0:02.72 httpd
12831 admin     20   0  418m  31m  12m S  5.6  0.2   1:04.59 httpd
13204 admin     20   0  418m  31m  11m S  5.6  0.2   0:46.42 httpd
12651 admin     20   0  416m  30m  11m S  5.3  0.2   1:24.94 httpd
12664 apache    20   0  418m  32m  11m S  5.3  0.2   1:23.42 httpd
14270 admin     20   0  416m  29m  11m S  5.3  0.2   0:03.61 httpd
14341 admin     20   0  417m  30m  11m S  5.3  0.2   0:00.55 httpd
12381 admin     20   0  417m  32m  13m S  5.0  0.2   1:48.81 httpd
12608 admin     20   0  416m  30m  11m S  5.0  0.2   0:58.11 httpd
13253 apache    20   0  416m  29m  11m S  5.0  0.2   0:06.41 httpd
12495 apache    20   0  418m  31m  12m S  4.6  0.2   1:17.01 httpd
12625 admin     20   0  415m  29m  12m S  4.6  0.2   0:58.87 httpd
12673 apache    20   0  416m  29m  12m S  4.6  0.2   1:19.35 httpd


Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,381
  • Karma: +112/-9
  • Gender: Male
    • View Profile
    • MRatWork Forum
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #9 on: 2015-09-29, 04:30:56 »
Itu akan terjadi karena anda pilih 'php-type' yang mod_php_ruid2/mod_php_itk/suphp. Jika ingin sedikit httpd, pilih suphp_worker/_event, fcgid_worker/_event atau php-fpm_worker/_event (dianjurkan pakai php-fpm_event karena cepat dan hemat memory).
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

Offline mino

  • Junior Member
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #10 on: 2015-09-29, 07:40:20 »
saya sudah gunakan php-fpm_event tapi tetep sama aja gan

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,381
  • Karma: +112/-9
  • Gender: Male
    • View Profile
    • MRatWork Forum
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #11 on: 2015-09-29, 08:35:01 »
Coba direstart dengan 'sh /script/restart-all -y' (atau jika perlu di-reboot).
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

Offline mino

  • Junior Member
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #12 on: 2015-09-29, 15:06:07 »
saya coba ubah server ke nginx malah ada pesan gini, buat ngatasinnya gimana gan?

Code: [Select]
Welcome to nginx!

If you see this page, the nginx web server is successfully installed and working. Further configuration is required.

For online documentation and support please refer to nginx.org.
Commercial support is available at nginx.com.

Thank you for using nginx.

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,381
  • Karma: +112/-9
  • Gender: Male
    • View Profile
    • MRatWork Forum
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #13 on: 2015-09-29, 16:23:35 »
saya coba ubah server ke nginx malah ada pesan gini, buat ngatasinnya gimana gan?

Code: [Select]
Welcome to nginx!

If you see this page, the nginx web server is successfully installed and working. Further configuration is required.

For online documentation and support please refer to nginx.org.
Commercial support is available at nginx.com.

Thank you for using nginx.
Anda melalukan perubahan melalui apa. Sebutkan langkah-langkahnya.
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

Offline mino

  • Junior Member
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Minta saran settingan database my.cnf RAM 16GB 4Ghz
« Reply #14 on: 2015-10-02, 00:58:58 »
saya stop httpd lalu start nginx kemudian fixweb tapi tetep kyk gitu

 


Top 4 Global Search Engines:    Google    Bing    Baidu    Yahoo
Click Here

Page created in 0.043 seconds with 17 queries.

web stats analysis
 
Mirror created by MasterkinG32.CoM