MRatWork Forum by Mustafa Ramadhan - MYSQL Bengkak

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, 19:36:59

- MRatWork Forum by Mustafa Ramadhan »
- Language Specific Discussions »
- Indonesia Users »
- MYSQL Bengkak

« previous next »

- Print

Pages: 1 [2] Go Down

### Author Topic: MYSQL Bengkak (Read 6934 times)

0 Members and 1 Guest are viewing this topic.

#### MRatWork

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

##### Re: MYSQL Bengkak

« Reply #15 on: 2014-01-10, 02:55:07 »
Jika anda yakin semua table sudah myisam (periksa melalui phpmyadmin), maka ibdata1 tidak diperlukan lagi.
Logged
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

#### Miki

- Senior Member
- Posts: 167
- Karma: +0/-0

##### Re: MYSQL Bengkak

« Reply #16 on: 2014-01-10, 11:10:28 »
dari seluruh hasil cek semua sudah myisam kecuali 2 gampar yang dilampirkan:
apakah itu tidak menjadikan sebuah trouble jika saya menghapus ibdata1 ?
Terima kasih
« Last Edit: 2014-01-10, 11:13:19 by Miki »
Logged
"the freedom speak is expression to exchange knowledge"

#### MRatWork

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

##### Re: MYSQL Bengkak

« Reply #17 on: 2014-01-10, 11:47:35 »
Coba lihat di /usr/share/mysql/my-huge.cnf ada 'innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend'. Ini menunjukkan dimana ibdata* bisa diset ukuran maksimalnya.
Silahkan pelajari apa yang dimaksud.
BTW, anda bisa pakai setting my.cnf lain dengan menyalin /usr/share/mysql/my-huge.cnf ke /etc/my.cnf misalnya.
Forum ini menggunakan my-huge.cnf sebagai 'template' untuk /etc/my.cnf.
Logged
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

#### Miki

- Senior Member
- Posts: 167
- Karma: +0/-0

##### Re: MYSQL Bengkak

« Reply #18 on: 2014-01-10, 18:14:09 »
serem juga main-main dengan ibdata1 tadinya mau langsung hapus' karena ragu akhirnya rename aja ibdata1.bak dan restart sql menunjukan daemon failed.
untung aja egk langsung dihapus jadi masih bisa 'mv /var/lib/mysql/ibdata1.bak /var/lib/mysql/ibdata1
mv: overwrite `/var/lib/mysql/ibdata1'? y'
mengenai /usr/share/mysql/my-huge.cnf saya hanya mengabil beberapa bagian dari isinya dan disertakan pada yang sedang digunakan saja :
Code: [Select]

    ##  -----------------------------------------------------------------------
    ##         \   ^__^
    ##          \  (oo)\_______
    ##             (__)\       )\/\
    ##                 ||----w |
    ##                 ||     ||

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

    ## General
    datadir                         = /var/lib/mysql
    tmpdir                          = /var/lib/mysqltmp
    socket                          = /var/lib/mysql/mysql.sock
    ;###123###;###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            = 4M
    join-buffer-size                = 2M

    ## 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

    # binary logging format - mixed recommended
    binlog_format=mixed

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

    [myisamchk]
    key_buffer_size = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M

    ## 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

    # Uncomment the following if you are using InnoDB tables
    innodb_data_home_dir = /var/lib/mysql
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size = 256M
    innodb_additional_mem_pool_size = 20M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 64M
    #innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    #innodb_lock_wait_timeout = 50

    ## 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

Mohon koreksinya jika ada yang salah..
Terima kasih
Logged
"the freedom speak is expression to exchange knowledge"

#### Miki

- Senior Member
- Posts: 167
- Karma: +0/-0

##### Re: MYSQL Bengkak

« Reply #19 on: 2014-01-11, 04:47:53 »
semakin hari semakin bengkak:
Quote

> drwx------ 2 mysql mysql 4096 Jan 3 02:26 admin
> drwx------ 2 mysql mysql 12288 Jan 11 09:34 afterlogic
> -rwx------ 1 mysql mysql 404525940736 Jan 11 10:17 ibdata1
> -rw-rw---- 1 mysql mysql 5242880 Jan 11 10:18 ib_logfile0
> -rw-rw---- 1 mysql mysql 5242880 Jan 11 10:18 ib_logfile1
> drwx------ 2 mysql mysql 12288 Jan 11 09:34 kloxo
> drwx------ 2 mysql mysql 4096 Jan 11 09:34 mysql
> -rw-rw---- 1 mysql mysql 133 Jan 11 09:00 mysql-bin.index
> -rw-rw---- 1 mysql mysql 0 Jan 11 09:11 mysqld-bin.index
> srwxrwxrwx 1 mysql mysql 0 Jan 11 10:19 mysql.sock
> drwx------ 2 mysql mysql 86016 Jan 11 09:34 anu_dtbaseku
> drwx------ 2 mysql mysql 4096 Sep 19 06:14 performance_schema
> drwx------ 2 mysql mysql 4096 Jan 11 09:34 roundcubemail
> drwx------ 2 mysql mysql 4096 Sep 19 06:14 test
> drwx------ 2 mysql mysql 4096 Jan 11 09:34 vpopmail

help... help..
Quote

> A. Kloxo-MR: 6.5.0.f-2014010101
> B. OS: CentOS release 5.10 (Final) i686
> C. Apps:
> 1. MySQL: mysql55-5.5.35-1.ius.el5
> 2. PHP: php54-5.4.23-3.ius.el5
> 3. Httpd: httpd-2.2.26-1.el5
> 4. Lighttpd: --uninstalled--
> 5. Nginx: nginx-1.5.8-1.el5
> 6. Qmail: qmail-toaster-1.03-1.3.29.mr.el5
> - with: courier-imap-toaster-4.1.2-1.3.14.mr.el5
> 7. Dns: bind-9.9.4-1.P1.el5
> D. Php-type (for Httpd/proxy): mod_php_ruid2
> E. Memory:
> total used free shared buffers cached
> Mem: 2469 2368 101 0 72 1669
> -/+ buffers/cache: 626 1843
> Swap: 11999 0 11999

Quote

> # The MySQL server
> [mysqld]
> skip-innodb
> default-storage-engine=myisam
> #innodb_file_per_table=on
> ## General
> datadir = /var/lib/mysql
> tmpdir = /var/lib/mysqltmp
> socket = /var/lib/mysql/mysql.sock
> ;###123###;###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 = 4M
> join-buffer-size = 2M
> ## 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
> # binary logging format - mixed recommended
> binlog_format=mixed
> ## MyISAM
> key-buffer-size = 64M
> myisam-sort-buffer-size = 128M
> [myisamchk]
> key_buffer_size = 128M
> sort_buffer_size = 128M
> read_buffer = 2M
> write_buffer = 2M
> ## 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
> # Uncomment the following if you are using InnoDB tables
> #innodb_data_home_dir = /var/lib/mysql
> #innodb_data_file_path = ibdata1:10M:autoextend
> #innodb_log_group_home_dir = /var/lib/mysql
> # You can set .._buffer_pool_size up to 50 - 80 %
> # of RAM but beware of setting memory usage too high
> #innodb_buffer_pool_size = 256M
> #innodb_additional_mem_pool_size = 20M
> # Set .._log_file_size to 25 % of buffer pool size
> #innodb_log_file_size = 64M
> #innodb_log_buffer_size = 8M
> innodb_flush_log_at_trx_commit = 1
> #innodb_lock_wait_timeout = 50
> ## 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

help..help..
« Last Edit: 2014-01-11, 04:51:30 by Miki »
Logged
"the freedom speak is expression to exchange knowledge"

#### MRatWork

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

##### Re: MYSQL Bengkak

« Reply #20 on: 2014-01-11, 09:34:08 »
Baca http://www.mysqlperformanceblog.com/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/
Logged
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

#### Miki

- Senior Member
- Posts: 167
- Karma: +0/-0

##### Re: MYSQL Bengkak

« Reply #21 on: 2014-01-11, 17:51:35 »
Quote

> tail /var/log/mysqld.log
> 140111 23:44:19 InnoDB: Completed initialization of buffer pool
> 140111 23:44:19 InnoDB: highest supported file format is Barracuda.
> 140111 23:44:24 InnoDB: Waiting for the background threads to start
> 140111 23:44:25 InnoDB: 5.5.35 started; log sequence number 345692438464
> 140111 23:44:25 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
> 140111 23:44:25 [Note] - '0.0.0.0' resolves to '0.0.0.0';
> 140111 23:44:25 [Note] Server socket created on IP: '0.0.0.0'.
> 140111 23:44:25 [Note] Event Scheduler: Loaded 0 events
> 140111 23:44:25 [Note] /usr/libexec/mysqld: ready for connections.
> Version: '5.5.35' socket: '/var/lib/mysql/mysql.sock' port: 3306 Distributed by The IUS Community Project

perasaan sudah di convert ke myISAM, bisakah untuk tidak menggunakan inoDB? bisakah untuk tidak ingin menggunakan ibdata.. myISAM only.
Terrima kasih.
Logged
"the freedom speak is expression to exchange knowledge"

#### MRatWork

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

##### Re: MYSQL Bengkak

« Reply #22 on: 2014-01-11, 18:34:39 »
Ini karena masih ada table yang masih pakai innodb. Pada kondisi tertentu, kadangkala mysql-convert tidak berhasil melakukan conversion. Perlu cara manual (export ke sql, delete table dan import sql) untuk database yang 'bandel'.
Logged
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

#### Miki

- Senior Member
- Posts: 167
- Karma: +0/-0

##### Re: MYSQL Bengkak

« Reply #23 on: 2014-01-11, 18:57:15 »
Quote from: MRatWork on 2014-01-11, 18:34:39

> Ini karena masih ada table yang masih pakai innodb. Pada kondisi tertentu, kadangkala mysql-convert tidak berhasil melakukan conversion. Perlu cara manual (export ke sql, delete table dan import sql) untuk database yang 'bandel'.

kalau boleh minta contoh scema my.cnf yang digunakan forum ini.
atau contoh scema my.cnf yang full tidak menggunakan inoDB. myISAM only untuk ram 2GB
Terima kasih.
Logged
"the freedom speak is expression to exchange knowledge"

#### MRatWork

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

##### Re: MYSQL Bengkak

« Reply #24 on: 2014-01-11, 19:05:07 »
Pada mysql 5.5, innodb menjadi 1 dengan corenya sehingga menambahkan 'skip-innodb' mungkin menimbulkan masalah.
Secara prinsip, forum ini memakai config dari /usr/share/mysql/my-huge.cnf yang disalinkan ke /etc/my.cnf. Untuk anda mungkin bisa memakai my-medium.cnf atau my-large.cnf.
Logged
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

#### Miki

- Senior Member
- Posts: 167
- Karma: +0/-0

##### Re: MYSQL Bengkak

« Reply #25 on: 2014-01-12, 04:12:47 »
mysql berapa yang terpisah dengan core nya.. saya tidak ingin menggunakan inoDB
Terima kasih.
dibawah ini maksudnya apa dan harus diapakan?
Code: [Select]

    140111  8:55:44 [Note] /usr/libexec/mysqld: Shutdown complete

    140111 08:55:44 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
    140111 08:58:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    140111  8:58:41 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
    140111  8:58:41 [Note] Plugin 'FEDERATED' is disabled.
    140111  8:58:41 [Note] Plugin 'InnoDB' is disabled.
    140111  8:58:41 [ERROR] /usr/libexec/mysqld: unknown option '--skip-locking'
    140111  8:58:41 [ERROR] Aborting

    140111  8:58:41 [Note] /usr/libexec/mysqld: Shutdown complete

    140111 08:58:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
    140111 09:00:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    140111  9:00:11 [Note] Plugin 'FEDERATED' is disabled.
    140111  9:00:11 [Note] Plugin 'InnoDB' is disabled.
    140111  9:00:11 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
    140111  9:00:11 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
    140111  9:00:11 [Note] Server socket created on IP: '0.0.0.0'.
    140111  9:00:11 [Note] Event Scheduler: Loaded 0 events
    140111  9:00:11 [Note] /usr/libexec/mysqld: ready for connections.
    Version: '5.5.35-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Distributed by The IUS Community Project
    140111  9:11:47 [Note] /usr/libexec/mysqld: Normal shutdown

    140111  9:11:47 [Note] Event Scheduler: Purging the queue. 0 events
    140111  9:11:47 [Note] /usr/libexec/mysqld: Shutdown complete

    140111 09:11:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
    140111 09:11:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    140111  9:11:48 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=mysqld-bin' to avoid this problem.
    140111  9:11:48 [Note] Plugin 'FEDERATED' is disabled.
    140111  9:11:48 [Warning] /usr/libexec/mysqld: ignoring option '--innodb-file-per-table' due to invalid value 'on'
    140111  9:11:48 [Note] Plugin 'InnoDB' is disabled.
    140111  9:11:48 [ERROR] /usr/libexec/mysqld: unknown option '--skip-locking'
    140111  9:11:48 [ERROR] Aborting

    140111  9:11:48 [Note] /usr/libexec/mysqld: Shutdown complete

    140111 09:11:48 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

« Last Edit: 2014-01-12, 04:42:28 by Miki »
Logged
"the freedom speak is expression to exchange knowledge"

- Print

Pages: 1 [2] Go Up
« previous next »

- MRatWork Forum by Mustafa Ramadhan »
- Language Specific Discussions »
- Indonesia Users »
- MYSQL Bengkak

MRatWork Affiliates: BIGRAF(R) Inc. House of LMAR EFARgrafix

..:: Monetize your Website or Blog with BidVertiser ::.. ..:: Sell your ad space with BidVertiser ::..

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

Page created in 0.055 seconds with 17 queries.

Mirror created by MasterkinG32.CoM