Advice Tuning my server

[COLOR=rgb(0, 0, 0)]Hi all, as many others I Learn many info from you in the past and now I manage to run server independently, now I am new to sql tuning and I been searching around the web a while now and tuning seems to be a difficult think so I would appreciate some advice/help . Can anyone comment on the results below are good, bad or worst. I downloaded and launched the mysqltuner.pl and the result is :



-------- Performance Metrics -------------------------------------------------
[--] Up for: 5s (9 q [1.800 qps], 6 conn, TX: 14K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 522.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 691.9M (8% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/729.0K
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 83% (1 created / 6 connections)
[OK] Table cache hit rate: 89% (60 open / 67 opened)
[OK] Open file limit used: 0% (16/5K)
[OK] Table locks acquired immediately: 100% (70 immediate / 70 locks)
[!!] Connections aborted: 16%

-------- 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
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)

[COLOR=rgb(0, 0, 0)]

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      = 32M
max_heap_table_size = 32M
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

Infopro

July 08, 2015 09:10

-------- Performance Metrics ------------------------------------------------- [--] Up for: 5s (9 q [1.800 qps], 6 conn, TX: 14K, RX: 1K)

MySQL started within last 24 hours - recommendations may be inaccurate

You need to have MySQL up for at least 24 hours.

stefano

July 08, 2015 10:07

Hi Stefano thx for your reply I will post a new update tomorrow , hope some one can help

July 09, 2015 08:47

Hi Infopro the follow is the latest result , can you please advice Thx

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 5h 10m 1s (488K q [4.654 qps], 9K conn, TX: 2B, RX: 114M)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 522.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 691.9M (8% of installed RAM)
[OK] Slow queries: 0% (1/488K)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/729.0K
[OK] Key buffer hit rate: 100.0% (465K cached / 30 reads)
[OK] Query cache efficiency: 70.9% (305K cached / 431K selects)
[!!] Query cache prunes per day: 38311
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 35K sorts)
[!!] Joins performed without indexes: 1369
[!!] Temporary tables created on disk: 58% (6K on disk / 11K total)
[OK] Thread cache hit rate: 99% (9 created / 9K connections)
[OK] Table cache hit rate: 33% (2K open / 5K opened)
[OK] Open file limit used: 3% (160/5K)
[OK] Table locks acquired immediately: 100% (222K immediate / 222K locks)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB BufferPool Size :450.0M
[--] InnoDB BufferPool Inst :1
[OK] InnoDB buffer pool / data size: 450.0M/310.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
    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
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

cPanelMichael

July 09, 2015 13:47

Hello :) Note that you can always implement some of the recommendations provided by the MySQL tuner, let MySQL run for another 24 hours, and post the updated results for user-feedback. You may also want to post the results on forums where more discussions of MySQL optimization occur (e.g. WebHostingTalk or StackOverflow) as often times the users on our forums focus on discussions of the cPanel/WHM software itself. Thank you.

Advice Tuning my server

Comments

Didn't find what you were looking for?