Sql tuner advise

[DiPolo]

Learning great info from you guys in past i manage to run server independently , now I am new to sql tuning based on some online videos. Can anyone comment on the results below are good, bad or worst. I been searching around the web a while now and tuning seems to be a difficult think so I would appreciate some advice/help

I downloaded and launched the mysqltuner.pl and the result is :

-------- Performance Metrics -------------------------------------------------
[--] Up for: 33m 11s (5K q [2.602 qps], 124 conn, TX: 20M, RX: 1M)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 506.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 675.9M (8% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 1% (3/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/729.0K
[OK] Key buffer hit rate: 100.0% (5K cached / 1 reads)
[OK] Query cache efficiency: 60.6% (2K cached / 4K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 644 sorts)
[!!] Joins performed without indexes: 8
[!!] Temporary tables created on disk: 28% (53 on disk / 186 total)
[OK] Thread cache hit rate: 97% (3 created / 124 connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 4% (240/5K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)
[!!] Connections aborted: 24%

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB BufferPool Size :450.0M
[--] InnoDB BufferPool Inst :1
[OK] InnoDB buffer pool / data size: 450.0M/302.3M
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB log waits: 0

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Your applications are not closing MySQL connections properly
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)

my.cnf as follow

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

query_cache_type = 1
query_cache_size= 16777216
query_cache_limit=1048576

innodb_buffer_pool_instances =1
innodb_buffer_pool_size = 450M

tmp_table_size      = 16M
max_heap_table_size = 16M
join_buffer_size=256K

skip-host-cache
skip-name-resolve
explicit_defaults_for_timestamp=TRUE

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

and below the server information and memory

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 2
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 4
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 5
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

[root@sub0000540810 ~]# free
             total       used       free     shared    buffers     cached
Mem:       8060476    6786964    1273512          0     305988    4918716
-/+ buffers/cache:    1562260    6498216
Swap:      1048568     376732     671836

show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 11320176 |
| Qcache_hits             | 3571     |
| Qcache_inserts          | 1934     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 176      |
| Qcache_queries_in_cache | 1666     |
| Qcache_total_blocks     | 3538     |
+-------------------------+----------+
8 rows in set (0.00 sec)

I am using Mysql 5.6.12
Thanks in advance for your help
STef

 

[redhell]

Did you try percona mysql tools?
They will give you good hints.

 

[DiPolo]

I tried percona mysql tools and the below is the outcome :

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
innodb                         = FORCE
innodb-strict-mode             = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 6G

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

As I said I really don`t know much about tuning I notice the query cache is off , would be the above configuration a good start with for my server configuration?