thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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
 

saamxvr

Well-Known Member
Oct 30, 2010
90
0
56
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 ?
 

saamxvr

Well-Known Member
Oct 30, 2010
90
0
56
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 ..
 

popeye

Well-Known Member
May 23, 2013
368
2
18
Texas
cPanel Access Level
Root Administrator
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
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,228
463
No, you should run a MySQL tuner to determine the best settings to use in your /etc/my.cnf file.

Thank you.