skysel

Member
Nov 30, 2010
12
0
51
I would like some guidlines on further optimizing mysql on my cPanel server. Below are the results from mysqltuner + current configuration. Any good advice or pointers would be greatly appreciated. Please note, that I'm not an experienced mysql administrator, I know basics + some advanced stuff, but do not know how to for example resolve slow queries.

Server configuration:

Code:
Memory: 

MemTotal:     12290784 kB
MemFree:       2015168 kB
Buffers:        345652 kB
Cached:        7575200 kB
SwapCached:       1364 kB
Active:        4445528 kB
Inactive:      5112664 kB
Code:
CPU (Quadcore):

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 4096 KB
physical id     : 1
siblings        : 4
core id         : 0
cpu cores       : 4
apicid          : 16
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
bogomips        : 4000.23
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: [8]
Code:
System:

Linux 2.6.18-164.11.1.el5 #1 SMP Wed Jan 20 07:32:21 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
mysqltuner.pl:

Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.91-community-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 16321)
[--] Data in InnoDB tables: 17M (Tables: 252)
[--] Data in MEMORY tables: 0B (Tables: 5)
[!!] Total fragmented tables: 712

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 2h 29m 18s (101M q [559.499 qps], 1M conn, TX: 116B, RX: 11B)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 412.0M global + 18.6M per thread (300 max threads)
[OK] Maximum possible memory usage: 5.9G (49% of installed RAM)
[OK] Slow queries: 2% (2M/101M)
[OK] Highest usage of available connections: 44% (133/300)
[OK] Key buffer size / total MyISAM indexes: 8.0M/895.8M
[OK] Key buffer hit rate: 97.8% (1B cached / 22M reads)
[OK] Query cache efficiency: 87.0% (78M cached / 89M selects)
[!!] Query cache prunes per day: 391920
[OK] Sorts requiring temporary tables: 0% (9K temp sorts / 986K sorts)
[!!] Joins performed without indexes: 96786
[!!] Temporary tables created on disk: 28% (241K on disk / 855K total)
[OK] Thread cache hit rate: 99% (6K created / 1M connections)
[OK] Table cache hit rate: 63% (32K open / 51K opened)
[OK] Open file limit used: 73% (48K/65K)
[OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
[OK] InnoDB data size / buffer pool: 17.7M/18.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    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 (> 256M) [see warning above]
    join_buffer_size (> 16.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
my.cnf

Code:
[mysqld]
set-variable = max_connections=300
safe-show-database
query_cache_size=256M
join_buffer_size=16M
thread_cache_size=12
table_cache=170000
interactive_timeout=14400
innodb_buffer_pool_size=18M
max_heap_table_size=128M
low_priority_updates=1
concurrent_insert=2
wait_timeout=5
connect_timeout=10
tmp_table_size=128M

long_query_time         = 1
log-slow-queries
log-queries-not-using-indexes
#basedir=/var/lib/mysql
#datadir=/var/lib/mysql