Help with mysqltuner configuration my mysql.

[Bidi]

Hy guys, i`m having a small problem with my server, thinking is because of cloudlinux so i contact them, and they sugest me to use mysqltunner to optimize my mysql.

So here is.

[email protected] [~]# ps -eo comm,rss|awk '{arr[$1]+=$2} END {for (i in arr) {print arr[i]/1024, i}}'|grep -v '^0 '|sort -n -r| head
3910.73 mysqld
306.254 clamd
155.438 httpd
153.207 python
140.176 sc_trans
135.598 php
122.176 nginx
105.793 named
71.1016 /usr/local/cpan
55.3398 python2.7

My my.conf

[mysqld]
slow-query-log=1
long-query-time=1
slow-query-log-file="/var/lib/mysql/slow.log"
local-infile=0

max_connections=200
max_user_connections=50

join_buffer_size=1M
read_rnd_buffer_size=1M
max_allowed_packet=314572800

table_open_cache = 7500
thread_cache_size = 25

query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

tmp_table_size=50M
max_heap_table_size=50M

#tmpdir = "/home/mysqltmp"

open_files_limit=39000

key_buffer_size = 500M
myisam_sort_buffer_size = 256M

innodb_file_per_table=1
innodb_buffer_pool_size = 1G
#innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
default-storage-engine=MyISAM

And infos from mysqltunner

[email protected] [~]# ./mysqltuner.pl

>>  MySQLTuner 1.3.0 mod - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
>>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.6.27-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 3G (Tables: 12005)
[--] Data in InnoDB tables: 873M (Tables: 4847)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 0B (Tables: 39)
[!!] Total fragmented tables: 1293

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21d 10h 46m 55s (460M q [248.527 qps], 12M conn, TX: 4745B, RX: 69B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 1.7G global + 2.6M per thread (200 max threads)
[OK] Maximum possible memory usage: 2.2G (7% of installed RAM)
[OK] Slow queries: 0% (54K/460M)
[OK] Highest usage of available connections: 81% (162/200)
[OK] Key buffer size / total MyISAM indexes: 500.0M/833.5M
[OK] Key buffer hit rate: 100.0% (56B cached / 9M reads)
[OK] Query cache efficiency: 74.4% (285M cached / 384M selects)
[!!] Query cache prunes per day: 1417163
[OK] Sorts requiring temporary tables: 0% (227K temp sorts / 28M sorts)
[!!] Joins performed without indexes: 165274 (see join_buffer_size note below)
[!!] Temporary tables created on disk: 37% (7M on disk / 19M total)
[OK] Thread cache hit rate: 99% (7K created / 12M connections)
[!!] Table cache hit rate: 0% (7K open / 2M opened)
[OK] Open file limit used: 28% (11K/39K)
[OK] Table locks acquired immediately: 99% (145M immediate / 145M locks)
[OK] InnoDB buffer pool / data size: 1.0G/873.5M
[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. Please note this
    calculation is made by adding Select_full_join + Select_range_check
    status values and triggered when the total >250
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C

Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 50M, increase tmp_table_size)
    max_heap_table_size (> 50M, increase max_heap_table_size)
    table_cache (> 7500, table_open_cache hit rate <20%)

[email protected] [~]#

Please give me a hand .

Thank you.

 

[Infopro]

What problem is it that you're having, you don't mention it.

 

[Bidi]

I had some issue, like when an acount was hiting the ClouxLinux limits (Package) all the server started to slow down but verry slow, whm,cpanel ...etc

The guys from ClouxLinux respond about mysql problem and because on some packages i set the cpu limit to 56%, and they told me to run mysqltunner.

my server config Intel Xeon 2 x CPU 2.63Ghz, 12 Cores, 24 Threads, 32gb ram.....

 

[cPanelMichael]

Hello

You can modify/add values to the /etc/my.cnf file based on the "Variables to adjust" from the MySQL tuner. Let MySQL run for at least 24 hours after the change and then run the tuner again to see if additional adjustments are recommended.

Thank you.