My sql /etc/my.cnf settings are
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MariaDB 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
max_connections= 500
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#skip-networking
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
[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
mysqltunner output =
[email protected] [~]# perl mysqltuner.pl
>> MySQLTuner 1.6.1 - Major Hayden <
[email protected]>
>> Bug reports, feature requests, and downloads at
MySQLTuner-perl by major
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.0.21-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 1G (Tables: 184)
[--] Data in InnoDB tables: 15M (Tables: 65)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 17
-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User '
[email protected]' has user name as password.
[!!] User '
[email protected]' has user name as password.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] User '
[email protected]%' hasn't specific host restriction.
[!!] There is no basic password file list !
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 11s (35K q [139.558 qps], 614 conn, TX: 1B, RX: 15M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 560.0M global + 6.4M per thread (500 max threads)
[OK] Maximum reached memory usage: 822.7M (2.56% of installed RAM)
[OK] Maximum possible memory usage: 3.7G (11.72% of installed RAM)
[OK] Slow queries: 0% (0/35K)
[OK] Highest usage of available connections: 8% (41/500)
[OK] Aborted connections: 0.00% (0/614)
[OK] Query cache efficiency: 37.2% (18K cached / 49K selects)
[!!] Query cache prunes per day: 160063
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 4K sorts)
[!!] Temporary tables created on disk: 34% (616 on disk / 1K total)
[OK] Thread cache hit rate: 79% (126 created / 614 connections)
[OK] Table cache hit rate: 164% (161 open / 98 opened)
[OK] Open file limit used: 5% (128/2K)
[!!] Table locks acquired immediately: 86%
[OK] Binlog cache memory access: 100.00% ( 7 Memory / 7 Total)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 19.8% (53M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/45.1M
[OK] Read Key buffer hit rate: 99.9% (4M cached / 4K reads)
[!!] Write Key buffer hit rate: 4.8% (1K cached / 1K writes)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/15.6M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 6.87% (563 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 95.79% (12779 hits/ 13341 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 10 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for
[email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for
[email protected]% to
[email protected]
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
query_cache_size (> 16M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_buffer_pool_instances (=1)
Please advice
