MySQL tweak on small VPS

Edwardon

How to Optimize MySQL Performance Using MySQLTuner

Env: Debian9, MySQL 5.7
The MySQLTuner script assesses your MySQL installation and then outputs suggestions for increasing your server’s performance and stability.

  1. Download the MySQLTuner script: wget http://mysqltuner.com/mysqltuner.pl
  2. Change the scripts permissions to be executablechmod +x mysqltuner.pl
  3. Run the scriptmysqltuner.pl. You will be prompted to enter in your MySQL administrative login and password:./mysqltuner.pl
  4. The script will return results similar to the output below:
Variables to adjust:
    max_connections (> 2)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 1M)
    join_buffer_size (> 4.0M, or always use indexes with JOINs)
    tmp_table_size (> 128M)
    max_heap_table_size (> 8M)
    table_open_cache (> 1024)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 69.4M) if possible.
    innodb_log_file_size should be (=5M)

Under 512MB

# /etc/my.cnf:
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000
 
# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K
 
#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K

Around 512 MB ~ 1 GB

[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
 
# Here follows entries for some specific programs
 
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
 
log-bin=mysql-bin
 
# binary logging format - mixed recommended
binlog_format=mixed
 
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
MASTER_PORT=3306
 
# 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
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

References
1. http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html
2. http://www.yourhowto.net/mysql-sample-configuration-512m-ram