title: How can i optimize MariaDB's my.cnf file? (with mysqltuner result)
body: <p>I am using MariaDB &amp; Apache (LAMP) and my VPS Ram is 512MB.</p>
  
  <p>Now the problem is sometimes when i use <code>top</code>, the MariaDB process is using 506MB of RAM so i want to optimize my MariaDB.</p>
  
  <p>Here is my <code>./mysqltuner.pl</code>'s result:</p>
  
  <pre><code>[!!] 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 (&gt; 16M)
      max_heap_table_size (&gt; 8M)
  </code></pre>
  
  <p>and here is my my.cnf's content:</p>
  
  <pre><code>[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
  </code></pre>
  
  <p>I have no idea what i can do. Help me please!</p>