Cara mengetahui penyebab Mysql bekerja berat?

[redpages]

Dear masta,

Satu VPS saya dipakai untuk wordpress multisite. Akhir-akhir ini kenirja sangat berat. Beberapa plugin saya matikan tetap berat juga. Bagaimana cara mengetahui penyebab proses berjalan berat?

Mohon pencerahannya.

Htop terlampir.

Nginx log access:

61.240.144.64 - - [02/Aug/2015:13:13:34 +0700] "GET / HTTP/1.0" 200 3075 "-" "masscan/1.0 (https://github.com/robertdavidgraham/masscan)" "-"
111.248.114.16 - - [02/Aug/2015:13:26:59 +0700] "CONNECT vip163mx01.mxmail.netease.com:25 HTTP/1.0" 400 172 "-" "-" "-"
115.230.127.237 - - [02/Aug/2015:14:26:21 +0700] "GET http://zc.qq.com/cgi-bin/common/attr?id=260714&r=0.4268894392661096 HTTP/1.1" 200 1168 "-" "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0; 360SE)" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //MyAdmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //phpMyAdmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //dbadmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //myadmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //phpmyadmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //pma/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET /muieblackcat HTTP/1.1" 200 1168 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //mysql/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //sqladmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //phpadmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //MySqlAdmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //web/phpMyAdmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET / HTTP/1.1" 200 1168 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET / HTTP/1.1" 200 1168 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //mysqladmin/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"
61.19.250.141 - - [02/Aug/2015:14:44:52 +0700] "GET //pma/scripts/setup.php HTTP/1.1" 404 47 "-" "-" "-"

Sysinfo

A. Kloxo-MR: 7.0.0.b-2015073001
   - Web: hiawatha-9.14.0-f.6.mr.el6.i386
   - PHP: php54s-5.4.41-1.ius.el6 (cgi mode)
B. Plateform:
   - OS: CentOS release 6.6 (Final) i686
   - Hostname: server30.xbackbone.com
C. Services:
   1. MySQL: MariaDB-server-10.0.20-1.el6.i686
   2. PHP:
      - Branch: php54-cli-5.4.43-1.ius.el6.i686
      - Used: --Use PHP Branch--
   3. Httpd: --uninstalled--
      - PHP Type:
   4. Lighttpd: --uninstalled--
   5. Hiawatha: --unused--
   6. Nginx: nginx-1.9.3-1.el6.ngx.i386
   7. Cache: --uninstalled--
   8. Dns: djbdns-1.05-17.4.mr.el6.i386
   9. Qmail: qmail-toaster-1.03-1.3.55.mr.el6.i386
      - with: courier-imap-toaster-4.1.2-1.3.18.mr.el6.i386
D. Memory:
                total       used       free     shared    buffers     cached
   Mem:          1006        940         66          0         68        403
   -/+ buffers/cache:        467        538
   Swap:            0          0          0
E. Disk Space:
   Filesystem      Size  Used Avail Use% Mounted on
   /dev/sda1        40G   27G   11G  72% /

[MRatWork]

Dari nginx log, ada masalah permission untuk directory /home/httpd/malhikdua.com/stats/.

Dari htop, yang bikin berat bukan mysql tapi php-fpm untuk kruchild.

[redpages]

oo gitu, apakah pakai pure nginx masih ada proses di httpd?
dan saya cek opsi statistik sudah disabled semua.

Untuk mensetting phpfpm di kloxo mr 7 masih di direktori  "/etc/php-fpm.d" ya?

Kondisi saat ini spt ini :

;catch_workers_output = yes
;listen = 127.0.0.1:57798
listen = /opt/configs/php-fpm/sock/kruchild.sock
listen.backlog = 65536
listen.allowed_clients = 127.0.0.1
listen.owner = kruchild
listen.group = kruchild
listen.mode = 0666
user = kruchild
group = kruchild
;pm = dynamic
pm = ondemand
pm.max_children = 6
;pm.start_servers = 4
;pm.min_spare_servers = 2
;pm.max_spare_servers = 4
pm.max_requests = 1000

;pm.status_path = /status
;ping.path = /ping
;ping.response = pong
request_terminate_timeout = 120s
request_slowlog_timeout = 30s
slowlog = /var/log/php-fpm/slow.log
rlimit_files = 1024
rlimit_core = 0
;chroot = /home/kruchild
;chdir = /
catch_workers_output = yes
security.limit_extensions = .php .php3 .php4 .php5

env[HOSTNAME] = $HOSTNAME
env[PATH] = /bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
env[TMP] = /tmp
env[TMPDIR] = /tmp
env[TEMP] = /tmp
env[OSTYPE] = $OSTYPE
env[MACHTYPE] = $MACHTYPE
env[MALLOC_CHECK_] = 2

php_flag[zlib.output_compression] = off
php_admin_value[disable_functions] = exec,passthru,shell_exec,system,proc_open,popen,show_source
php_flag[display_errors] = off
php_flag[file_uploads] = on
php_admin_value[upload_max_filesize] = 16M
php_flag[log_errors] = off
php_flag[output_buffering] = off
php_flag[register_argc_argv] = on
php_flag[mysql.allow_persistent] = off
php_admin_value[max_execution_time] = 120
php_admin_value[max_input_time] = 120
php_admin_value[memory_limit] = 128M
php_admin_value[post_max_size] = 32M
php_flag[allow_url_fopen] = on
php_flag[allow_url_include] = on
php_admin_value[session.save_path] = /home/kloxo/client/kruchild/session
php_flag[cgi.force_redirect] = on
php_flag[enable_dl] = on
php_admin_value[open_basedir] = /home/kruchild/:/tmp/:/usr/share/pear/:/var/lib/php/session/:/home/kloxo/httpd/script/:/home/kloxo/httpd/disable/
php_admin_value[max_input_vars] = 3000
php_admin_value[date.timezone] = "Europe/London"

Terima kasih atas pencerahannya.

[MRatWork]

Mungkin bukan php sendiri yang menjadi sebab tapi file php yang menyebabkan proses yang 'berat'.

[redpages]

iya. saya test dengan mendisbabled akun "kruchild" langsung normal, berkurang banyak bar nya.
Sudah saya download terus scan antivirus tetap dianggap bersih.

Adakah cara alternatif buat mencari file yg jadi penyebab problem tersebut?

[MRatWork]

Jika pakai wordpress, coba install wordfence plugins.

[redpages]

sudah saya install. dan memang dijumpai ada file-file berbahaya. Setelah saya hapus file-file tersebut jalan website tetap berat. sering 502 gateway. problem 502 gateway hilang setiap di sh /script/cleanup; Begitu seterusnya.

Kenapa ya?

NGinx error log terlampir

Terima kasih atas bantuannya.

[MRatWork]

Saran saya, pindahkan isi domain terkait (gunakan copy-paste di filemanager), hapus domain terkait, create domain terkait lagi, kembalikan isi domain yang ada.

[redpages]

tetap.
Tapi saya coba cek direktori karena terlihat sisa space yg tidak wajar. Ada pembengkakan di dir /var/lib/mysql/..
Disitu ada banyak file berlabel mysql-bin..
Apakah itu penyebabnya? kenapa ya?

[root@server30 ~]# du -sh /var/lib/mysql/*
1.3M    /var/lib/mysql/afterlogic
972M    /var/lib/mysql/aria_log.00000011
4.0K    /var/lib/mysql/aria_log_control
48M     /var/lib/mysql/ib_logfile0
48M     /var/lib/mysql/ib_logfile1
12M     /var/lib/mysql/ibdata1
4.7M    /var/lib/mysql/kloxo
649M    /var/lib/mysql/kruchild_sdm14
8.0K    /var/lib/mysql/kruchild_sdm15
0       /var/lib/mysql/multi-master.info
8.0K    /var/lib/mysql/mydns
1.3M    /var/lib/mysql/mysql
400K    /var/lib/mysql/mysql-bin.000001
1.6M    /var/lib/mysql/mysql-bin.000002
4.0K    /var/lib/mysql/mysql-bin.000003
8.0K    /var/lib/mysql/mysql-bin.000004
1.7M    /var/lib/mysql/mysql-bin.000005
4.0K    /var/lib/mysql/mysql-bin.000006
709M    /var/lib/mysql/mysql-bin.000007
3.1M    /var/lib/mysql/mysql-bin.000008
4.0K    /var/lib/mysql/mysql-bin.000009
126M    /var/lib/mysql/mysql-bin.000010
116K    /var/lib/mysql/mysql-bin.000011
844M    /var/lib/mysql/mysql-bin.000012
3.2M    /var/lib/mysql/mysql-bin.000013
4.0K    /var/lib/mysql/mysql-bin.000014
536K    /var/lib/mysql/mysql-bin.000015
2.8M    /var/lib/mysql/mysql-bin.000016
4.0K    /var/lib/mysql/mysql-bin.000017
9.9M    /var/lib/mysql/mysql-bin.000018
548K    /var/lib/mysql/mysql-bin.000019
3.9M    /var/lib/mysql/mysql-bin.000020
4.0K    /var/lib/mysql/mysql-bin.000021
33M     /var/lib/mysql/mysql-bin.000022
5.2M    /var/lib/mysql/mysql-bin.000023
4.0K    /var/lib/mysql/mysql-bin.000024
3.1M    /var/lib/mysql/mysql-bin.000025
1.1G    /var/lib/mysql/mysql-bin.000026
1.1G    /var/lib/mysql/mysql-bin.000027
1.1G    /var/lib/mysql/mysql-bin.000028
1.1G    /var/lib/mysql/mysql-bin.000029
1.1G    /var/lib/mysql/mysql-bin.000030
1.1G    /var/lib/mysql/mysql-bin.000031
1.1G    /var/lib/mysql/mysql-bin.000032
1.1G    /var/lib/mysql/mysql-bin.000033
1.1G    /var/lib/mysql/mysql-bin.000034
1.1G    /var/lib/mysql/mysql-bin.000035
1.1G    /var/lib/mysql/mysql-bin.000036
1.1G    /var/lib/mysql/mysql-bin.000037
1.1G    /var/lib/mysql/mysql-bin.000038
1.1G    /var/lib/mysql/mysql-bin.000039
1.1G    /var/lib/mysql/mysql-bin.000040
1.1G    /var/lib/mysql/mysql-bin.000041
369M    /var/lib/mysql/mysql-bin.000042
5.4M    /var/lib/mysql/mysql-bin.000043
1.3M    /var/lib/mysql/mysql-bin.000044
20M     /var/lib/mysql/mysql-bin.000045
2.7M    /var/lib/mysql/mysql-bin.000046
1.6M    /var/lib/mysql/mysql-bin.000047
216K    /var/lib/mysql/mysql-bin.000048
648M    /var/lib/mysql/mysql-bin.000049
1.1G    /var/lib/mysql/mysql-bin.000050
37M     /var/lib/mysql/mysql-bin.000051
348K    /var/lib/mysql/mysql-bin.000052
68K     /var/lib/mysql/mysql-bin.000053
284K    /var/lib/mysql/mysql-bin.000054
2.2M    /var/lib/mysql/mysql-bin.000055
828K    /var/lib/mysql/mysql-bin.000056
646M    /var/lib/mysql/mysql-bin.000057
720K    /var/lib/mysql/mysql-bin.000058
420K    /var/lib/mysql/mysql-bin.000059
1.4M    /var/lib/mysql/mysql-bin.000060
27M     /var/lib/mysql/mysql-bin.000061
46M     /var/lib/mysql/mysql-bin.000062
1.2M    /var/lib/mysql/mysql-bin.000063
13M     /var/lib/mysql/mysql-bin.000064
50M     /var/lib/mysql/mysql-bin.000065
3.3M    /var/lib/mysql/mysql-bin.000066
28M     /var/lib/mysql/mysql-bin.000067
896K    /var/lib/mysql/mysql-bin.000068
5.8M    /var/lib/mysql/mysql-bin.000069
13M     /var/lib/mysql/mysql-bin.000070
836K    /var/lib/mysql/mysql-bin.000071
160K    /var/lib/mysql/mysql-bin.000072
712K    /var/lib/mysql/mysql-bin.000073
1.5M    /var/lib/mysql/mysql-bin.000074
240K    /var/lib/mysql/mysql-bin.000075
4.0K    /var/lib/mysql/mysql-bin.index
0       /var/lib/mysql/mysql.sock
220K    /var/lib/mysql/performance_schema
460K    /var/lib/mysql/powerdns
308K    /var/lib/mysql/roundcubemail
860K    /var/lib/mysql/server30.xbackbone.com.err
4.0K    /var/lib/mysql/server30.xbackbone.com.pid
8.0K    /var/lib/mysql/test
136K    /var/lib/mysql/vpopmail
[root@server30 ~]#

Untuk diketahui, mysql vps mariadb dengan tipe aria

B. Plateform:
   - OS: CentOS release 6.7 (Final) i686
   - Hostname: server30.xbackbone.com
C. Services:
   1. MySQL: MariaDB-server-10.0.21-1.el6.i686
   2. PHP:
      - Branch: php54-cli-5.4.43-1.ius.el6.i686
      - Used: --Use PHP Branch--
   3. Httpd: --uninstalled--
      - PHP Type:
   4. Lighttpd: --uninstalled--
   5. Hiawatha: --unused--
   6. Nginx: nginx-1.9.3-1.el6.ngx.i386
   7. Cache: --uninstalled--
   8. Dns: djbdns-1.05-17.4.mr.el6.i386
   9. Qmail: qmail-toaster-1.03-1.3.55.mr.el6.i386
      - with: courier-imap-toaster-4.1.2-1.3.18.mr.el6.i386
D. Memory:
                total       used       free     shared    buffers     cached
   Mem:          1006        924         81         15        218        378
   -/+ buffers/cache:        328        677
   Swap:            0          0          0
E. Disk Space:
   Filesystem      Size  Used Avail Use% Mounted on
   /dev/sda1        40G   29G  9.2G  76% /

[MRatWork]

Baca http://mindspill.net/computing/linux-notes/disable-mysql-binary-logging/. Karena anda pakai MariaDB maka configurenya di /etc/my.cnf.d/server.cnf (bukan /etc/my.cnf).