I am using MariaDB & Apache (LAMP) and my VPS Ram is 512MB.
Now the problem is sometimes when i use top, the MariaDB process is using 506MB of RAM so i want to optimize my MariaDB.
Here is my ./mysqltuner.pl's result:
[!!] Currently running unsupported MySQL version 10.0.11-MariaDB-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 33M (Tables: 94)
[--] Data in InnoDB tables: 6M (Tables: 111)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 4
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 8m 39s (3K q [7.657 qps], 39 conn, TX: 32M, RX: 513K)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 94.0M global + 26.3M per thread (50 max threads)
[!!] Maximum possible memory usage: 1.4G (282% of installed RAM)
[OK] Slow queries: 0% (0/3K)
[OK] Highest usage of available connections: 4% (2/50)
[OK] Key buffer size / total MyISAM indexes: 4.0M/3.9M
[OK] Key buffer hit rate: 99.0% (29K cached / 281 reads)
[OK] Query cache efficiency: 40.9% (2K cached / 6K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 481 sorts)
[!!] Temporary tables created on disk: 35% (75 on disk / 213 total)
[OK] Thread cache hit rate: 94% (2 created / 39 connections)
[OK] Table cache hit rate: 493% (79 open / 16 opened)
[OK] Open file limit used: 0% (52/65K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB buffer pool / data size: 64.0M/6.3M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 16M)
max_heap_table_size (> 8M)
and here is my my.cnf's content:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mariadb
datadir = /data/mariadb
pid-file = /data/mariadb/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
skip-name-resolve
#skip-networking
back_log = 300
max_connections = 20
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30
log_error = /data/mariadb/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mariadb/mysql-slow.log
performance_schema = 0
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 30
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
I have no idea what i can do. Help me please!
You should use the last version available on github. Memory advice seems to be wrong andhave been corrected since.
Temporary table can be configurated with tmp_table_size and max_heap_table_size variable.
https://www.percona.com/blog/2007/01/19/tmp_table_size-and-max_heap_table_size/