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. 2024-04-29, 01:29:10
« previous next »
Pages: 1 [2]   Go Down

Author Topic: MYSQL Bengkak  (Read 15037 times)

0 Members and 1 Guest are viewing this topic.

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,807
  • Karma: +119/-11
  • Gender: Male
    • View Profile
    • MRatWork Forum
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!) --

Offline Miki

  • Senior Member
  • *
  • Posts: 183
  • Karma: +0/-0
    • View Profile
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"

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,807
  • Karma: +119/-11
  • Gender: Male
    • View Profile
    • MRatWork Forum
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!) --

Offline Miki

  • Senior Member
  • *
  • Posts: 183
  • Karma: +0/-0
    • View Profile
Re: MYSQL Bengkak
« Reply #18 on: 2014-01-10, 18:14:09 »
serem juga main-main dengan ibdata1  ;D 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"

Offline Miki

  • Senior Member
  • *
  • Posts: 183
  • Karma: +0/-0
    • View Profile
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"

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,807
  • Karma: +119/-11
  • Gender: Male
    • View Profile
    • MRatWork Forum
Re: MYSQL Bengkak
« Reply #20 on: 2014-01-11, 09:34:08 »
Logged
..:: MRatWork (Mustafa Ramadhan Projects) ::..
-- Server/Web-integrator - Web Hosting (Kloxo-MR READY!) --

Offline Miki

  • Senior Member
  • *
  • Posts: 183
  • Karma: +0/-0
    • View Profile
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"

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,807
  • Karma: +119/-11
  • Gender: Male
    • View Profile
    • MRatWork Forum
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!) --

Offline Miki

  • Senior Member
  • *
  • Posts: 183
  • Karma: +0/-0
    • View Profile
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"

Offline MRatWork

  • Administrator
  • The Elite
  • *****
  • Posts: 15,807
  • Karma: +119/-11
  • Gender: Male
    • View Profile
    • MRatWork Forum
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!) --

Offline Miki

  • Senior Member
  • *
  • Posts: 183
  • Karma: +0/-0
    • View Profile
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"
Pages: 1 [2]   Go Up
« previous next »
 


Top 10 Social Networking:    Facebook    Twitter    LinkedIn    Pinterest    Google Plus    Tumblr    Instagram    VK    Flickr    Vine

Page created in 0.041 seconds with 18 queries.

web stats analysis