MySQL Optimize Help

saamxvr

January 18, 2014 07:57

Hello , i need help to oprimiz my MySQL this is my server info : [QUOTE]Intel(R) Xeon(R) CPU E3-1240 V2 @ 3.40GHz 16GB RAM CentOS 6.4 - 64-Bit
this is my currant my.cnf [QUOTE] [mysqld] innodb_file_per_table=1
this is my tuner report

 >>  MySQLTuner 1.2.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page
 >>  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 :

Comments

28 comments

thinkbot

January 18, 2014 18:30
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
0
saamxvr

January 20, 2014 10:14
Thanks you .. and this is result >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page >> 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)
0
thinkbot

January 20, 2014 17:48
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
0
cPanelMichael

January 22, 2014 14:03
[quote="saamxvr, post: 1552182">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.
0
saamxvr

January 22, 2014 14:08
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
0
thinkbot

January 22, 2014 14:14
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
0
saamxvr

January 22, 2014 15:22
This is my screen still like this last 1hours ... see the attachment .
0
thinkbot

January 22, 2014 15:45
Can you run du -sh /var/lib/mysql/mysql-slow.log
0
saamxvr

January 22, 2014 15:49
I getting this error : root@srv [~]# du -sh /var/lib/mysql/mysql-slow.log du: cannot access `/var/lib/mysql/mysql-slow.log': No such file or directory root@srv [~]#
0
saamxvr

February 03, 2014 04:07
Hello i didnt get any reply latly - however i get new result >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page >> 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 ?
0
thinkbot

February 03, 2014 14:12
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
0
saamxvr

February 05, 2014 09:15
im still getting root@srv [~]# ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt -bash: ./pt-query-digest: No such file or directory any idea ?
0
thinkbot

February 06, 2014 01:10
slow_query_log_file=mysql-slow.log long_query_time=0.1 ohh forgot this one slow_query_log=1 please add that and restart
0
saamxvr

February 10, 2014 03:53
OK when im type this command i get this result root@srv [~]# du -sh /var/lib/mysql/mysql-slow.log 21M /var/lib/mysql/mysql-slow.log but when im run this command root@srv [~]# ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt -bash: ./pt-query-digest: No such file or directory any idea ?
0
thinkbot

February 10, 2014 08:17
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
0
saamxvr

February 10, 2014 16:50
its not come out put, its just like below screen
0
thinkbot

February 10, 2014 17:03
yep, now open /root/install/slow.txt and copy content here
0
saamxvr

February 10, 2014 17:20
Humm ok but its big list .. how i copy all from ssh ?
0
thinkbot

February 10, 2014 17:21
use sftp or scp to copy the file to PC, filezilla supports it
0
saamxvr

February 11, 2014 03:39
Haha .. finally, i have attached file :)
0
thinkbot

February 11, 2014 16:49
database hiphop this query SELECT wp_posts.ID, post_title, post_name, post_date, COUNT(wp_comments.comment_post_ID) AS 'comment_total' FROM wp_posts LEFT JOIN wp_comments ON wp_posts.ID = wp_comments.comment_post_ID WHERE comment_approved = '1' AND post_date_gmt < '2014-02-10 13:40:57' AND post_status = 'publish' AND post_password = '' GROUP BY wp_comments.comment_post_ID ORDER BY comment_total DESC LIMIT 5\G takes 76% of mysql slow queries time thats probably the query that returns the top 5 most commented posts it was executed 19 925 times, in 95% cases takes 2s, and examines 211k rows so it would be good to add some wordpress plugin for caching or disable the plugin that runs this query anyways caching is always good
0
saamxvr

February 12, 2014 03:01
Hello, Thank you very much your suggest as i see it done by theme, its take most recent post and comment in footer aria .. that is the case so i will remove or add widget cache plugin . and will see it will be reduce . and also do i need optimize mysql server ? i mean add more value to my.cnf ?
0
thinkbot

February 12, 2014 09:39
No need to increase my.cnf values any more, it's good
0
saamxvr

February 12, 2014 09:43
You right .. the issue is my currant cache plugin not worked properly .. so i switch to W3 its now reducing MySQL Load .. i will update withing 48hours result ..
0
popeye

January 13, 2015 13:20
Hi sorry to jump on the thread but do these setting below work on all cpanel servers ? also where would i add them please 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
0
cPanelMichael

January 13, 2015 16:04
No, you should run a MySQL tuner to determine the best settings to use in your /etc/my.cnf file. Thank you.
0
popeye

January 13, 2015 16:07
Hi how do i run MySQL tuner please
0
cPanelMichael

January 13, 2015 17:03
[quote="popeye, post: 1807401">Hi how do i run MySQL tuner please
Please see the following thread for information on how to use a MySQL tuner script: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?