Optimasi MySQL untuk VPS Ram 1 GB


Radhitya

Radhitya

Beginner 2.0
Assalamualaikum, saya ingin tanya lagi nih.
Gimana ya cara optimasi MySQL untuk VPS Ram 1 GB? Mungkin ada yang punya konfigurasi terbaik.

Ini punya saya
Code: 
[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysql.sock

[mysqld]
binlog_cache_size = 64K
join_buffer_size = 1024K
query_cache_type = 1
max_heap_table_size = 64M
port        = 3306
socket        = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 4M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 4K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 4M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 64M
symbolic-links=0
thread_stack = 256K
innodb_file_per_table
max_connections = 100
max_user_connections=50
wait_timeout=50
interactive_timeout=50
long_query_time=5

#skip-name-resolve
max_connections = 100
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on


innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 256M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 1
innodb_write_io_threads = 1

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

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

[mysqlhotcopy]
interactive-timeout
 
pedagang

pedagang

Hosting Guru
tanyaGan, kenapa harus Innodb .... myisam kan lebih ringan.

untuk max connection, default nya sih 150 itu sudah dikurangi jadi 100
untuk lebih aman-nya mungkin 50 ..... ini juga dah gede untuk ukuran vps tsb
 
XXIKU.COM

XXIKU.COM

Hosting Guru
Radhitya said:
Assalamualaikum, saya ingin tanya lagi nih.
Gimana ya cara optimasi MySQL untuk VPS Ram 1 GB? Mungkin ada yang punya konfigurasi terbaik.

Ini punya saya
Code: 
[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysql.sock

[mysqld]
binlog_cache_size = 64K
join_buffer_size = 1024K
query_cache_type = 1
max_heap_table_size = 64M
port        = 3306
socket        = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 4M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 4K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 4M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 64M
symbolic-links=0
thread_stack = 256K
innodb_file_per_table
max_connections = 100
max_user_connections=50
wait_timeout=50
interactive_timeout=50
long_query_time=5

#skip-name-resolve
max_connections = 100
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on


innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 256M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 1
innodb_write_io_threads = 1

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

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

[mysqlhotcopy]
interactive-timeout
Click to expand...

Coba gunakan, semoga membantu

[client]
#password= your_password
port= 3306
socket= /tmp/mysql.sock

[mysqld]
binlog_cache_size = 64K
thread_stack = 256K
join_buffer_size = 1024K
query_cache_type = 1
max_heap_table_size = 64M
port= 3306
socket= /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 100G
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 4K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 64M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 100
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""
 
XXIKU.COM

XXIKU.COM

Hosting Guru
Paling optimal bisa juga gunakan

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

[mysqld]
binlog_cache_size = 64K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1
max_heap_table_size = 384M
port = 3306
socket = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 100G
table_open_cache = 192
sort_buffer_size = 768K
net_buffer_length = 4K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 96
query_cache_size = 128M
tmp_table_size = 384M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 200
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""


innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 384M

Ini untuk optimasi untuk 2 Core 2 GB Ram terbaik menurut saya. Saran sih gunanya ini saja.
 
jaapns

jaapns

Hosting Guru
Verified Provider
settingan mysql untuk setiap ram itu beda2 , ada faktor content jumlah query dsb,

baiknya lgs di test pake mysql tunner saja :
github.com

GitHub - major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. - major/MySQLTuner-perl
github.com github.com

jadi nanti terakhirnya ada advice apa saja yg harus di naikkan / tweak


Simplenya jalankan :
Code: 
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Code: 
chmod +x mysqltuner.pl

Code: 
./mysqltuner.pl

Terakhirnya akan muncul rekomendasi nya , edit my.cnf nya /etc/mysql/my.cnf , di panel berbeda , beda juga letaknya , tinggal di edit saja trus restart mysql servicenya
 
XXIKU.COM

XXIKU.COM

Hosting Guru
Terakhir, gunakan optimasi cache pada memory dan RAM dengan multiple server agar lebih optimal.
- Memcached
- Redis
- Varnish

Untuk bagian Page Cache, Database Cache, Objeck Cache, dan Pragment Cache. bisa pilih salah satu diatas untuk bagian-bagian ini atau kombinasikan semuanya.
 
J

Jowy

Poster 1.0
udah oke tuh confignya, tinggal main cache aja buat nge cache query result biar ga keseringan ngehit database bisa pake plugin kalo wordpress
 
pedagang

pedagang

Hosting Guru
Jowy said:
udah oke tuh confignya, tinggal main cache aja buat nge cache query result biar ga keseringan ngehit database bisa pake plugin kalo wordpress
Click to expand...
kalo pake w3 total cache hati-hati dengan minify, kemarin amp saya rusak. pusing ngecek yg bikin error, jadi sy sekalian nggak pake minify
 
You must log in or register to reply here.

Top