MySQL settings help needed

[PankajKumar]

Hi All,

I recently moved my hosting server and upgraded RAM from 1GB to 3GB. The reason for move was high traffic causing MySQL to go down every 4-5 days.

When i run mysqltuner on new VPS, it produced following output.

>>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.38-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 67M (Tables: 285)
[--] Data in InnoDB tables: 172M (Tables: 102)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 11

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 9h 34m 42s (1M q [4.225 qps], 42K conn, TX: 18B, RX: 243M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 748.0M global + 8.5M per thread (250 max threads)
[!!] Maximum possible memory usage: 2.8G (95% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 14% (36/250)
[OK] Key buffer size / total MyISAM indexes: 256.0M/31.2M
[OK] Key buffer hit rate: 99.8% (3M cached / 6K reads)
[OK] Query cache efficiency: 46.3% (1M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 31K sorts)
[OK] Temporary tables created on disk: 24% (4K on disk / 20K total)
[OK] Thread cache hit rate: 99% (49 created / 42K connections)
[OK] Table cache hit rate: 33% (454 open / 1K opened)
[OK] Open file limit used: 3% (637/16K)
[OK] Table locks acquired immediately: 99% (258K immediate / 258K locks)
[OK] InnoDB buffer pool / data size: 256.0M/172.8M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries

Maximum possible memory usage is 2.8 GB, that is not good. When I am looking at /etc/my.cnf file, it already has a lot of settings.

[mysqld]
## connection settings
max_connect_errors=400
max_connections=250
max_user_connections=50
wait_timeout=60
connect_timeout=10
interactive_timeout=60

## cache settings
query_cache_limit=4M
query_cache_size=96M
query_cache_type=1
table_open_cache=1024
thread_cache_size=8

## buffer sizes
key_buffer=256M
sort_buffer_size=4M
read_buffer_size=2M
join_buffer_size=2M
aria_pagecache_buffer_size = 16m
aria_sort_buffer_size = 16m

## tmpdir / temp table sizes
tmp_table_size=128M
max_heap_table_size=128M

## misc. settings
datadir=/var/lib/mysql
skip-external-locking

server-id = 1
open-files-limit = 16384
max_allowed_packet = 16M

## innodb settings
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 6
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 4M
innodb_file_per_table = 1

## slow query logging
#log_slow_queries=/var/lib/mysql/slow-queries.log
#log-long-format
#long_query_time=5

open_files_limit=4262
[mysqld_safe]
open-files-limit = 16384

[mysqldump]
quick
max_allowed_packet=32M

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 32M
write_buffer = 16M

[mysql]
no-auto-rehash

I am not sure which ones to adjust for reducing the memory footprint, usually mysqltuner provide some settings to optimize but it has not done so here. So I am confused and don't want to risk anything by doing something I dont know.

In my older server, my.cnf was created by me and it was very simple.

[mysqld]
skip-name-resolve
max_connections=75
max_user_connections=50
open_files_limit=4262
query_cache_size=16M
tmp_table_size=8M
max_heap_table_size=5M
thread_cache_size=8
innodb_buffer_pool_size=192M
key_buffer_size=50M
sort_buffer_size=256K
max_allowed_packet=268435456

Please provide some guidance for mysql settings.

 

[cPanelMichael]

Hello

I'm sure you will receive some user-feedback regarding settings to the /etc/my.cnf file, but in the meantime, you can also use a command such as:

mysqladmin processlist

This might help you pinpoint any particular databases that are using excessive resources.

Thank you.