This is a very interesting discussion, and I don’t want to close this thread. Since it’s no longer a Tech Support issue in the strict sense, though, I’m going to move this to “General”. Thank you.
This is an archived forum and the content is probably no longer relevant, but is provided here for posterity.
The active forums are here.
December 03, 2009 4:30pm
Subscribe [11]#31 / Dec 09, 2009 5:58am
This is a very interesting discussion, and I don’t want to close this thread. Since it’s no longer a Tech Support issue in the strict sense, though, I’m going to move this to “General”. Thank you.
#32 / Dec 09, 2009 11:00pm
Paul, thanks for the info on the $TMPL->log_item(). What a wonderful debugging tool.
I narrowed the performance hit down to two spots:
elseif ($this->display_by == 'day')
{
$TMPL->log_item('If display by day');
// We need to run a query and fetch the distinct days in which there are entries
$dql = "SELECT t.year, t.month, t.day ".$sql;
$TMPL->log_item('Add status declaration: ');
/** ----------------------------------------------
/** Add status declaration
/** ----------------------------------------------*/
if ($status = $TMPL->fetch_param('status'))
{
$status = str_replace('Open', 'open', $status);
$status = str_replace('Closed', 'closed', $status);
$sstr = $FNS->sql_andor_string($status, 't.status');
if ( ! preg_match("#\'closed\'#i", $sstr))
{
$sstr .= " AND t.status != 'closed' ";
}
$dql .= $sstr;
}
else
{
$dql .= "AND t.status = 'open' ";
}
$TMPL->log_item('Add category limit. ');
/** --------------------
/** Add Category Limit
/** --------------------*/
if ($cat_id != '')
{
$dql .= "AND exp_category_posts.cat_id = '".$DB->escape_str($cat_id)."' ";
}
$TMPL->log_item('start sql: '.$dql);
$query = $DB->query($dql);
$TMPL->log_item('end sql. ');(0.088554) If display by day
(0.088564) Add status declaration:
(0.088660) Add category limit.
(0.088672) start sql: SELECT t.year, t.month, t.day FROM exp_weblog_titles AS t LEFT JOIN exp_weblogs ON t.weblog_id = exp_weblogs.weblog_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id WHERE t.entry_id !='' AND t.site_id IN ('1','2') AND t.entry_date < 1260430961 AND (t.expiration_date = 0 OR t.expiration_date > 1260430961) AND exp_weblogs.is_user_blog = 'n' AND t.status IN ('open','Featured','TMO','TMO Featured','iPO','iPO Featured') AND t.status != 'closed'
(1.161428) end sql.and
$TMPL->log_item('fetch the entry_id numbers: '.$sql_a.$sql_b.$sql);
/** ----------------------------------------------
/** Fetch the entry_id numbers
/** ----------------------------------------------*/
$query = $DB->query($sql_a.$sql_b.$sql);
//exit($sql_a.$sql_b.$sql);
if ($query->num_rows == 0)
{
$this->sql = '';
return;
}
$TMPL->log_item('build the full sql query');(1.281430) add limits to query
(1.281447) fetch the entry_id numbers: SELECT t.entry_id FROM exp_weblog_titles AS t LEFT JOIN exp_weblogs ON t.weblog_id = exp_weblogs.weblog_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id WHERE t.entry_id !='' AND t.site_id IN ('1','2') AND t.entry_date < 1260430961 AND (t.expiration_date = 0 OR t.expiration_date > 1260430961) AND exp_weblogs.is_user_blog = 'n' AND ((t.year = '2009' AND t.month = '12' AND t.day = '03' ) OR(t.year = '2009' AND t.month = '12' AND t.day = '02' ) OR(t.year = '2009' AND t.month = '12' AND t.day = '01' ) OR(t.year = '2009' AND t.month = '11' AND t.day = '30' ) OR(t.year = '2009' AND t.month = '11' AND t.day = '26' ) OR(t.year = '2009' AND t.month = '11' AND t.day = '25' ) )AND t.status IN ('open','Featured','TMO','TMO Featured','iPO','iPO Featured') AND t.status != 'closed' ORDER BY t.entry_date desc, t.entry_id desc
(1.971778) build the full sql query
(1.972054) end build sql queryThis makes sense to me, as our hunch has been our DB has been the bottleneck. As to why these times are improved over earlier, These tests were run at 9PM eastern, the earlier ~3.3 test during peak traffic times around 2PM eastern.
I could confirm that running the last query in phpMyAdmin did take 0.5 sec. Here is the explain data:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE exp_weblogs ref PRIMARY,is_user_blog is_user_blog 1 const 17 Using where; Using temporary; Using filesort
1 SIMPLE t ref PRIMARY,weblog_id,status,entry_date,expiration_dat… weblog_id 4 tmo_ee.exp_weblogs.weblog_id 4070 Using where
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 tmo_ee.t.author_id 1 Using indexThe first query only took 0.0027 in phpMyAdmin. So it seems this script was waiting on MySQL for 1 second, and its not that the query itself is slow. Am I reading that correctly?
#33 / Dec 09, 2009 11:19pm
Are you using the Entry “Views” Tracking tag by chance?
#34 / Dec 09, 2009 11:57pm
I do have “Enable Section Entry View Tracking?” enabled. I have limited it to one template on one site. Is it really a horrible performance hit? The clients on this one site are really keen on knowing how many total hits they get per entry. I use mint (on a separate db server) for the other sites, but it only shows recent hits… the client needs reporting by month.
I’ll try turning it off tomorrow and see if it makes a big difference.
#35 / Dec 10, 2009 1:03am
Its not really a performance hit because of its general usage, it rather depends on a given site. 99% of the sites out there using it, it likely has no impact at all. Sites with 10’s to 100’s of thousands or more articles, with heavy traffic, you are going to likely get (and your system administrators clearly would see) read/write lock contention happening on the weblog_titles table. On a normal basis the weblog_titles table is a heavy read table, light writes, as it contains things like title, author, status (open/closed/etc), and other details about an article. If you turn on view_count tracking, you are adding a write operation for every article view that happens across a site, this can in some cases lead to a heavy read/write lock contention as mentioned earlier.
Pretty easy to spot at the mysql server level if its taking place. Easy to test for too, if you disable that in the article template, and review your template debug output, do you still see the delay at the same spots as you do now? If so, I would personally recommend chatting with Paul about their Tracker module. It will provide you/your client more article tracking details in different time frames, graphing, etc. but it also moves the write operations to heavy write, light read tables outside of the EE weblog_titles table.
This may not be the answer in this case, or may only be one of many things going on, I have not seen your servers in action of course so impossible for me to assist fully in diagnosing like I can for my hosting clients. It would be easy to test for and also in general to see at the mysql show processlist level during your db server usage spikes.