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
 

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