saamxvr

Well-Known Member
Oct 30, 2010
90
0
56
Hello ,

i need help to oprimiz my MySQL

this is my server info :

Intel(R) Xeon(R) CPU E3-1240 V2 @ 3.40GHz
16GB RAM
CentOS 6.4 - 64-Bit

this is my currant my.cnf
[mysqld]
innodb_file_per_table=1

this is my tuner report



Code:
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/url]
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 588M (Tables: 91)
[--] Data in InnoDB tables: 1M (Tables: 89)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 2

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 15s (19K q [21.802 qps], 319 conn, TX: 979M, RX: 4M)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (3% of installed RAM)
[OK] Slow queries: 0% (0/19K)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/102.3M
[OK] Key buffer hit rate: 99.5% (1M cached / 8K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 4% (136 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 30% (425 on disk / 1K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 96% (226 open / 233 opened)
[OK] Open file limit used: 22% (235/1K)
[OK] Table locks acquired immediately: 99% (21K immediate / 21K locks)
[OK] InnoDB data size / buffer pool: 1.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)




Waiting for the suggest :
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
add

key_buffer_size = 150M
query_cache_limit = 1M
query_cache_size = 30M
query_cache_type = 1
table_open_cache = 1000
thread_cache_size = 16
tmp_table_size = 50M
max_heap_table_size = 50M

slow_query_log_file=mysql-slow.log
long_query_time=0.1

and later restart

after several hours, or best 24h, run mysqltuner.pl again and post your result
 

saamxvr

Well-Known Member
Oct 30, 2010
90
0
56
Thanks you .. and this is result


Code:
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/url]
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 351)
[--] Data in InnoDB tables: 2M (Tables: 86)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 60

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 3h 27m 33s (9M q [95.266 qps], 97K conn, TX: 129B, RX: 3B)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 374.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 789.2M (4% of installed RAM)
[OK] Slow queries: 0% (4K/9M)
[OK] Highest usage of available connections: 78% (119/151)
[OK] Key buffer size / total MyISAM indexes: 150.0M/387.7M
[OK] Key buffer hit rate: 100.0% (1B cached / 478K reads)
[OK] Query cache efficiency: 21.3% (1M cached / 8M selects)
[!!] Query cache prunes per day: 1052942
[OK] Sorts requiring temporary tables: 2% (17K temp sorts / 870K sorts)
[!!] Temporary tables created on disk: 39% (297K on disk / 751K total)
[OK] Thread cache hit rate: 99% (137 created / 97K connections)
[OK] Table cache hit rate: 48% (667 open / 1K opened)
[OK] Open file limit used: 43% (935/2K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
[OK] InnoDB data size / buffer pool: 2.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    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 (> 30M)
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Much better, but since

[!!] Temporary tables created on disk: 39% (297K on disk / 751K total)

you should optimize your queries (especially the ones using temporary tables on disk)
query can can be also increased
query_cache_size = 70M

generate review of current slow queries
mkdir -p /root/install
cd /root/install
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
and upload slow.txt here in code brackets
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,201
363
Thanks you .. and this is result
I am happy to see the optimization recommendations you received on this thread have been helpful so far. Feel free to let us know the outcome after the most recent suggestions.

Thank you.
 

saamxvr

Well-Known Member
Oct 30, 2010
90
0
56
Hello ,

Thanks you Michael , thinkbot , i have fallow last suggest , and its take me forever to load slow.txt .. its not come out put ... any idea ?

Thank you
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
the generation of slow.txt can take a while, but during that you will receive percentage info of how much is finished
just slow.txt file is not big, so it should not be a problem uploading it here
 

saamxvr

Well-Known Member
Oct 30, 2010
90
0
56
Hello i didnt get any reply latly - however i get new result

Code:
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/url]
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 694)
[--] Data in InnoDB tables: 4M (Tables: 103)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 115

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 38s (332K q [354.747 qps], 3K conn, TX: 1B, RX: 103M)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 414.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 829.2M (5% of installed RAM)
[OK] Slow queries: 0% (421/332K)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 150.0M/1.0G
[OK] Key buffer hit rate: 99.9% (155M cached / 128K reads)
[!!] Query cache efficiency: 18.6% (52K cached / 281K selects)
[!!] Query cache prunes per day: 862249
[OK] Sorts requiring temporary tables: 0% (164 temp sorts / 21K sorts)
[!!] Temporary tables created on disk: 36% (4K on disk / 13K total)
[OK] Thread cache hit rate: 99% (13 created / 3K connections)
[OK] Table cache hit rate: 98% (402 open / 409 opened)
[OK] Open file limit used: 14% (699/4K)
[OK] Table locks acquired immediately: 99% (284K immediate / 284K locks)
[OK] InnoDB data size / buffer pool: 4.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    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_limit (> 1M, or use smaller result sets)
    query_cache_size (> 70M)
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)
Any suggest ?
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Its very good, but please generate slow log again

./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
before your slow log didnt exist
 

saamxvr

Well-Known Member
Oct 30, 2010
90
0
56
im still getting

[email protected] [~]# ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
-bash: ./pt-query-digest: No such file or directory


any idea ?
 

saamxvr

Well-Known Member
Oct 30, 2010
90
0
56
OK when im type this command i get this result

[email protected] [~]# du -sh /var/lib/mysql/mysql-slow.log
21M /var/lib/mysql/mysql-slow.log

but when im run this command

[email protected] [~]# ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
-bash: ./pt-query-digest: No such file or directory

any idea ?
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
you previosuly installed pt-query-digest in /root/install

cou first
cd /root/install
then
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt