MYSQL Bengkak

[Miki]

berdasarkan lapiran pada gambar yang didapat dari '/var/lib/mysql' yang mana sajakah yang boleh dihapus?
mohon petunjuknya master..

Terima kasih.

[MRatWork]

1. Jadikan '#log_bin = /var/log/mysql/mysql-bin.log' (ada '#') pada /etc/my.cnf
2. Stop mysql
3. Hapus semua file mysql-bin
4. start mysql

[Miki]

lapor tidak ditemukan "log_bin = /var/log/mysql/mysql-bin.log' pada '/etc/my.cnf' :

[mysqld]
;###123###;###123###skip-innodb
default-storage-engine=myisam

## General
datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
;###123###;###123###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                = 32M
query-cache-limit               = 1M

## 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                  = 32M
max-heap-table-size             = 64M

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

### Storage Engines
innodb                          = FORCE

## MyISAM
key-buffer-size                 = 64M
myisam-sort-buffer-size         = 128M

## InnoDB
#innodb-buffer-pool-size        = 128M
#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           = 16G
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                = 65535

[mysql]
no-auto-rehash

[MRatWork]

tampilkan saja 'dir -l /var/lib/mysql/mysql*'.

Coba baca juga http://www.cyberciti.biz/faq/what-is-mysql-binary-log/

[MRatWork]

Juga http://www.marcinrybak.com/2009/09/clean-mysql-binary-logs-with.html

[Miki]

dir -l /var/lib/mysql/mysql*
-rw-rw---- 1 mysql mysql  344185532 Dec 26 21:29 /var/lib/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql    2123726 Dec 26 21:38 /var/lib/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql   57170964 Dec 26 21:46 /var/lib/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql  121767188 Dec 26 22:21 /var/lib/mysql/mysql-bin.000004
-rw-rw---- 1 mysql mysql   42893952 Dec 26 22:31 /var/lib/mysql/mysql-bin.000005
-rw-rw---- 1 mysql mysql   13761246 Dec 26 22:37 /var/lib/mysql/mysql-bin.000006
-rw-rw---- 1 mysql mysql   33119769 Dec 26 22:44 /var/lib/mysql/mysql-bin.000007
-rw-rw---- 1 mysql mysql 1073818928 Dec 27 03:33 /var/lib/mysql/mysql-bin.000008
-rw-rw---- 1 mysql mysql 1073743105 Dec 27 10:31 /var/lib/mysql/mysql-bin.000009
-rw-rw---- 1 mysql mysql 1073746086 Dec 27 16:12 /var/lib/mysql/mysql-bin.000010
-rw-rw---- 1 mysql mysql  267515992 Dec 27 17:23 /var/lib/mysql/mysql-bin.000011
-rw-rw---- 1 mysql mysql    5895359 Dec 27 17:25 /var/lib/mysql/mysql-bin.000012
-rw-rw---- 1 mysql mysql        228 Dec 27 17:23 /var/lib/mysql/mysql-bin.index
srwxrwxrwx 1 mysql mysql          0 Jan  2 05:56 /var/lib/mysql/mysql.sock

/var/lib/mysql/mysql:
total 1060
-rw-rw---- 1 mysql mysql   8820 Sep 22 11:40 columns_priv.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 columns_priv.MYD
-rw-rw---- 1 mysql mysql   4096 Sep 22 11:40 columns_priv.MYI
-rw-rw---- 1 mysql mysql   9582 Sep 22 11:40 db.frm
-rw-rw---- 1 mysql mysql  29480 Jan  1 08:22 db.MYD
-rw-rw---- 1 mysql mysql   9216 Jan  1 08:26 db.MYI
-rw-rw---- 1 mysql mysql  10223 Sep 22 11:40 event.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 event.MYD
-rw-rw---- 1 mysql mysql   2048 Sep 22 11:40 event.MYI
-rw-rw---- 1 mysql mysql   8665 Sep 22 11:40 func.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 func.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 func.MYI
-rw-rw---- 1 mysql mysql   8776 Sep 22 11:40 general_log.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 general_log.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 general_log.MYI
-rw-rw---- 1 mysql mysql   8700 Sep 22 11:40 help_category.frm
-rw-rw---- 1 mysql mysql   1120 Sep 22 11:40 help_category.MYD
-rw-rw---- 1 mysql mysql   3072 Sep 22 11:40 help_category.MYI
-rw-rw---- 1 mysql mysql   8612 Sep 22 11:40 help_keyword.frm
-rw-rw---- 1 mysql mysql  91999 Sep 22 11:40 help_keyword.MYD
-rw-rw---- 1 mysql mysql  17408 Sep 22 11:40 help_keyword.MYI
-rw-rw---- 1 mysql mysql   8630 Sep 22 11:40 help_relation.frm
-rw-rw---- 1 mysql mysql   9423 Sep 22 11:40 help_relation.MYD
-rw-rw---- 1 mysql mysql  18432 Sep 22 11:40 help_relation.MYI
-rw-rw---- 1 mysql mysql   8770 Sep 22 11:40 help_topic.frm
-rw-rw---- 1 mysql mysql 465272 Sep 22 11:40 help_topic.MYD
-rw-rw---- 1 mysql mysql  21504 Sep 22 11:40 help_topic.MYI
-rw-rw---- 1 mysql mysql   9510 Sep 22 11:40 host.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 host.MYD
-rw-rw---- 1 mysql mysql   2048 Sep 22 11:40 host.MYI
-rw-rw---- 1 mysql mysql   8778 Dec 31 17:36 ndb_binlog_index.frm
-rw-rw---- 1 mysql mysql   8586 Sep 22 11:40 plugin.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 plugin.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 plugin.MYI
-rw-rw---- 1 mysql mysql   9996 Sep 22 11:40 proc.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 proc.MYD
-rw-rw---- 1 mysql mysql   2048 Sep 22 11:40 proc.MYI
-rw-rw---- 1 mysql mysql   8875 Sep 22 11:40 procs_priv.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 procs_priv.MYD
-rw-rw---- 1 mysql mysql   4096 Sep 22 11:40 procs_priv.MYI
-rw-rw---- 1 mysql mysql   8800 Sep 22 11:40 proxies_priv.frm
-rw-rw---- 1 mysql mysql   1386 Sep 22 11:40 proxies_priv.MYD
-rw-rw---- 1 mysql mysql   5120 Sep 22 11:40 proxies_priv.MYI
-rw-rw---- 1 mysql mysql   8838 Sep 22 11:40 servers.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 servers.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 servers.MYI
-rw-rw---- 1 mysql mysql   8976 Sep 22 11:40 slow_log.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 slow_log.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 slow_log.MYI
-rw-rw---- 1 mysql mysql   8955 Sep 22 11:40 tables_priv.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 tables_priv.MYD
-rw-rw---- 1 mysql mysql   4096 Sep 22 11:40 tables_priv.MYI
-rw-rw---- 1 mysql mysql   8636 Sep 22 11:40 time_zone.frm
-rw-rw---- 1 mysql mysql   8624 Sep 22 11:40 time_zone_leap_second.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 time_zone_leap_second.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 time_zone_leap_second.MYI
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 time_zone.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 time_zone.MYI
-rw-rw---- 1 mysql mysql   8606 Sep 22 11:40 time_zone_name.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 time_zone_name.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 time_zone_name.MYI
-rw-rw---- 1 mysql mysql   8686 Sep 22 11:40 time_zone_transition.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 time_zone_transition.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 time_zone_transition.MYI
-rw-rw---- 1 mysql mysql   8748 Sep 22 11:40 time_zone_transition_type.frm
-rw-rw---- 1 mysql mysql      0 Sep 22 11:40 time_zone_transition_type.MYD
-rw-rw---- 1 mysql mysql   1024 Sep 22 11:40 time_zone_transition_type.MYI
-rw-rw---- 1 mysql mysql  10630 Sep 22 11:40 user.frm
-rw-rw---- 1 mysql mysql   6584 Jan  1 08:22 user.MYD
-rw-rw---- 1 mysql mysql   4096 Jan  1 08:26 user.MYI

[MRatWork]

Coba lihat saja directory (artinya itu sekaligus name domain) mana saja yang menghabiskan ruang.

Menjalankan 'sh /script/mysql-optimize --select=optimize' akan 'merapikan' (semacam defrag) database sehingga akan memperkecil pemakaian ruang.

[Miki]

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.35 Distributed by The IUS Community Project

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global expire_logs_days=14;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "expire%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 14    |
+------------------+-------+
1 row in set (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

[Miki]

Coba lihat saja directory (artinya itu sekaligus name domain) mana saja yang menghabiskan ruang.

Menjalankan 'sh /script/mysql-optimize --select=optimize' akan 'merapikan' (semacam defrag) database sehingga akan memperkecil pemakaian ruang.

lama banget selesainya egk sabaran di ctrl+cencel :
( note : Table does not support optimize, doing recreate + analyze instead )
hampir semua begitu..

[MRatWork]

Innodb memang tidak support optimize. Hanya MyISAM dan Aria yang support optimize.

[Miki]

dir -l /var/lib/mysql
total 388430616
mysql mysql         4096 Jan  3 02:26 admin
mysql mysql         4096 Jan  7 12:47 afterlogic
mysql mysql 397353680896 Jan  9 07:41 ibdata1
mysql mysql      5242880 Jan  9 07:41 ib_logfile0
mysql mysql      5242880 Jan  9 07:41 ib_logfile1

Bolehkah yang diberi warna merah dihapus? ada efek samping nya egk?

Thx

[MRatWork]

dir -l /var/lib/mysql
total 388430616
mysql mysql         4096 Jan  3 02:26 admin
mysql mysql         4096 Jan  7 12:47 afterlogic
mysql mysql 397353680896 Jan  9 07:41 ibdata1
mysql mysql      5242880 Jan  9 07:41 ib_logfile0
mysql mysql      5242880 Jan  9 07:41 ib_logfile1

Bolehkah yang diberi warna merah dihapus? ada efek samping nya egk?

Thx

Jangan didelete. Pelajari http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html dan http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table

[Miki]

The paths to individual InnoDB data files and their sizes. The full directory path to each data file is formed by concatenating innodb_data_home_dir to each path specified here. The file sizes are specified KB, MB or GB (1024MB) by appending K, M or G to the size value. If specifying data file size in kilobytes (KB), do so in multiples of 1024. Otherwise, KB values are rounded off to nearest megabyte (MB) boundary. The sum of the sizes of the files must be at least slightly larger than 10MB. If you do not specify innodb_data_file_path, the default behavior is to create a single auto-extending data file, slightly larger than 10MB, named ibdata1. The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on those operating systems that support big files. You can also use raw disk partitions as data files. For detailed information on configuring InnoDB tablespace files, see Section 14.3.6, “InnoDB Configuration”.

Code: [Select]

http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html
semakin hari semakin bengkak:

Code: [Select]

375.088G ibdata1

Code: [Select]

[mysqld]
innodb_file_per_table=on
;###123###;###123###;###123###;###123###;###123###;###123###skip-innodb
default-storage-engine=myisam

## General
datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
;###123###;###123###;###123###;###123###;###123###;###123###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                = 32M
query-cache-limit               = 1M

## 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                  = 32M
max-heap-table-size             = 64M

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

### Storage Engines
innodb                          = FORCE

## MyISAM
key-buffer-size                 = 64M
myisam-sort-buffer-size         = 128M

## InnoDB
#innodb-buffer-pool-size        = 128M
#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           = 16G
expire-logs-days                = 3
#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                = 65535

[mysql]
no-auto-rehash

[Miki]

pusing master.. 

[Miki]

Coba lihat saja directory (artinya itu sekaligus name domain) mana saja yang menghabiskan ruang.

Menjalankan 'sh /script/mysql-optimize --select=optimize' akan 'merapikan' (semacam defrag) database sehingga akan memperkecil pemakaian ruang.

lama banget selesainya egk sabaran di ctrl+cencel :
( note : Table does not support optimize, doing recreate + analyze instead )
hampir semua begitu..

akhirnya ketemu yang bikin lama saat melakukan optimize:

Code: [Select]

There is a common issue whereby a sites 'cache_form' table gets really big. It basically doesn't clear on Cron runs and just keeps growing, often getting to a couple GB in size.

There are various posts about possible causes and cures, but if you find that you aren't able to log into your site to clear your caches due to your DB size limit being breached, then you can use this MySQL query to clear the 'cache_form' table manually. This will just clear the table - it won't break your site and you won't lose any data.
dan saya juga sudah melakukan 'sh /script/mysql-convert --engine=MyISAM' lanjut dengan 'sh /script/mysql-optimize --select=optimize'
sedikit lega.. walau belum ke tahap penyelesaian bengkaknya pada:

Code: [Select]

mysql 404525940736 Jan 10 03:58 ibdata1
nunggu kode dari master yang akan saya paste di ssh master

Terima kasih master..