Optimize Mysql Server


Status
Not open for further replies.
pangeran1995 said:
yang MyISAM gimana tuh :D
Click to expand...

Ini utk myisam:

key_buffer_size = 16G #klo di innodb namanya innodb_buffer_pool_size
#untuk sorting
myisam_sort_buffer_size =246M
myisam_max_sort_file_size =10G

Sama untuk default storage engine nya default-storage-engine = InnoDB, tapi cek aja dulu saat ini setingannya apa.
 
pangeran1995 said:
saat ni saya menggunakan mariadb versi 10..
jika SET default_storage_engine=INNODB; untuk memaksa user .. apakah ada efek negatifnya?
Click to expand...

Ngga ada. User2 existing yg sudah pada MyISAM ya tetap terus MyISAM. Kecuali ada user baru yang dicreate, maka user baru tersebut yang akan jadi menggunakan INNODB.
 
terima kasih ilmunya
saya coba optimasikan
sebelumnya saya menggunakan ini

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
local-infile=0
innodb_file_per_table=1

datadir=/var/lib/mysql
tmp_table_size=50M
max_heap_table_size=50M
query_cache_limit=50M
query_cache_size=50M
query_cache_type=1
max_user_connections=45
max_connections=100
innodb_buffer_pool_size = 500M

collation_server=utf8_unicode_ci
character_set_server=utf8

delayed_insert_timeout=40

interactive_timeout=10
wait_timeout=60
connect_timeout=2
thread_cache_size=128
key_buffer=1000M
join_buffer_size=4M
max_connect_errors=20
max_allowed_packet=16M
table_cache=5000
sort_buffer_size=256K
read_buffer_size=128K
read_rnd_buffer_size=8M
myisam_sort_buffer_size=256M
server-id=1

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
 
Ini setting saya di DS 16GB RAM yang pakai MariaDB 10.x:
Code: 
[mysqld]
default-storage-engine=aria
#skip-innodb
performance-schema=on
#default-tmp-storage-engine=myisam
#innodb=off
#OpenVZ AIO bug fix
innodb-use-native-aio=1


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

key-buffer-size=256M
join-buffer-size=54M
sort-buffer-size=54M
thread-cache-size=256
tmp-table-size=512M
max-heap-table-size=256M
max-connections=100

max-allowed-packet=16M
table-cache=2048

## specific for aria
aria-pagecache-buffer-size=256M
aria-recover=FORCE
aria-repair_threads=2
 
pangeran1995 said:
terima kasih ilmunya
saya coba optimasikan
sebelumnya saya menggunakan ini

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
local-infile=0
innodb_file_per_table=1

datadir=/var/lib/mysql
tmp_table_size=50M
max_heap_table_size=50M
query_cache_limit=50M
query_cache_size=50M
query_cache_type=1
max_user_connections=45
max_connections=100
innodb_buffer_pool_size = 500M

collation_server=utf8_unicode_ci
character_set_server=utf8

delayed_insert_timeout=40

interactive_timeout=10
wait_timeout=60
connect_timeout=2
thread_cache_size=128
key_buffer=1000M
join_buffer_size=4M
max_connect_errors=20
max_allowed_packet=16M
table_cache=5000
sort_buffer_size=256K
read_buffer_size=128K
read_rnd_buffer_size=8M
myisam_sort_buffer_size=256M
server-id=1

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
Click to expand...

> innodb_buffer_pool_size = 500M
Ya ampun, pelit amat.
Ini server khusus untuk DB aja kan?
 
mustafaramadhan said:
Ini setting saya di DS 16GB RAM yang pakai MariaDB 10.x:
Code: 
[mysqld]
default-storage-engine=aria
#skip-innodb
performance-schema=on
#default-tmp-storage-engine=myisam
#innodb=off
#OpenVZ AIO bug fix
innodb-use-native-aio=1


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

key-buffer-size=256M
join-buffer-size=54M
sort-buffer-size=54M
thread-cache-size=256
tmp-table-size=512M
max-heap-table-size=256M
max-connections=100

max-allowed-packet=16M
table-cache=2048

## specific for aria
aria-pagecache-buffer-size=256M
aria-recover=FORCE
aria-repair_threads=2
Click to expand...

Gimana Tuann rasanya pakai aria?
Ini lock nya per table ya Tuann?
 
Status
Not open for further replies.
Back
Top