Mysql Optimization help required

Hi, I have suffering a slow speed on my server running mostly wordpress and apps. Server is Xeon 8 processors 16 GB RAM. My.cnf file details----

[mysqld]
    local-infile=0

    connect_timeout=360
    wait_timeout=2400
    interactive_timeout=2400
    default-storage-engine=InnoDB

    max_connections = 400
    max_user_connections = 100

    key_buffer_size=1400M
    join_buffer_size=10M
    sort_buffer_size=256K
    read_buffer_size = 256K
    read_rnd_buffer_size = 256K

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-index = 1
    low_priority_updates=1
    concurrent_insert=ALWAYS

    query_cache_type = 1
    query_cache_size =128M
    query_cache_limit = 4M

    max_allowed_packet=268435456
    tmp_table_size=64M
    max_heap_table_size=64M


    table_definition_cache=38000
    table_open_cache=40000

    thread_cache_size=64

    innodb_buffer_pool_size=1624M
    innodb_file_per_table=1

open_files_limit=655690

Mysqltuner result output as follows-

>>  MySQLTuner 1.3.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.6.17-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 11G (Tables: 8537)
[--] Data in InnoDB tables: 1G (Tables: 7114)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 0B (Tables: 73)
[!!] Total fragmented tables: 3525

-------- Security Recommendations  -------------------------------------------
[!!] User 'mmbagsin_wrdp1@204.45.126.18' has no password set.
[!!] User 'mmbagsin_wrdp1@76.73.118.178' has no password set.
[!!] User 'mmbagsin_wrdp1@localhost' has no password set.
[!!] User 'mmbagsin_wrdp1@server1.kakinfotech.com' has no password set.
[!!] User 'mmbagsin_wrdp1@server1.watchmenindia.com' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 23h 51m 28s (21M q [61.043 qps], 297K conn, TX: 106B, RX: 5B)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 3.2G global + 11.0M per thread (400 max threads)
[OK] Maximum possible memory usage: 7.5G (47% of installed RAM)
[OK] Slow queries: 3% (633K/21M)
[OK] Highest usage of available connections: 14% (58/400)
[OK] Key buffer size / total MyISAM indexes: 1.4G/4.4G
[OK] Key buffer hit rate: 100.0% (578M cached / 226K reads)
[OK] Query cache efficiency: 61.4% (10M cached / 17M selects)
[!!] Query cache prunes per day: 75532
[OK] Sorts requiring temporary tables: 0% (148 temp sorts / 607K sorts)
[!!] Joins performed without indexes: 5563
[!!] Temporary tables created on disk: 32% (269K on disk / 815K total)
[OK] Thread cache hit rate: 99% (58 created / 297K connections)
[OK] Table cache hit rate: 46% (32K open / 69K opened)
[OK] Open file limit used: 3% (25K/655K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
[OK] InnoDB buffer pool / data size: 1.6G/1.5G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better 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 (> 128M)
    join_buffer_size (> 10.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)

Waiting for an update on this thread. Thank you,

Infopro

November 10, 2014 17:18

What is the purpose of opening a second thread? New rig, different configuration? Your original thread is here, and has been replied to, some time ago: Mysql takes up heavy load on CPU and eats most of the ram - cPanel Forums

webhosting-k999

November 10, 2014 17:28

Hi, After a long while, we have added many sites. System takes too much time to load a website... Can you suggest some tweaks in the variables...???

November 10, 2014 18:22

I can't. Well I could, but not the tweaks you're hoping for, the same tweaks many come by these forums and others, hoping for. That magic configuration that solves everything. There isn't one. I will say this, no matter how well tweaked your system is, how perfect your configurations are, one poorly maintained website can cause you grief. Add "many sites" similar to that first problematic site, and you can tweak till the cows come home, it won't help. Starting multiple threads on the same topic, won't help either. Managing the sites on the server better, properly, is the best advice I could give you. Details on how to do that, you should already know. Only you know what those "many sites" are doing, what they have installed as far as addons and customizations that can crush a server in minutes if done incorrectly, how much actual traffic they get and so on. If you don't, start there. As for me, if a website starts pounding one of my servers resources, I don't go look for ways to meet the demand, I figure out if the demand is real or some poorly coded chat room script for wordpress, for example. Please don't start additional threads on the same topic.

Mysql Optimization help required

Comments

Didn't find what you were looking for?