Performance issue of History plugin?

Question

I am using NoahY's history plugin which is great: https://github.com/NoahY/q2a-history

However, I have got a performance issue. Try to load this site: http://www.gute-mathe-fragen.de/user/echteinfachtv?tab=history It takes up to 5 seconds to appear.

Others q2a-sites on my domain load below 1 second.

Could the php code (or mysql?) be improved to speed up the history?

Thank you!

 

----

Update 2012-10-17 12:07h

the delay is caused by function user_activity_form().
If you comment it out at line 101: $content['form-activity-list'] = $this->user_activity_form();  
the page loads immediately.

I thought it could be the while loop on line 224:
        while ( ($event=qa_db_read_one_assoc($events,true)) !== null ) {
but setting a continue right in the beginning does *not* speed up things!

Setting a return in the function user_activity_form() on different positions I could see the the MySQL query on line 145 takes ages:

        $events = qa_db_query_sub(
            "SELECT
                e.event,
                BINARY e.params as params,
                UNIX_TIMESTAMP(e.datetime) AS datetime,
                p.postid AS postid
            FROM
                ^eventlog AS e
            LEFT JOIN
                ^posts AS p
                ON e.params LIKE CONCAT('%postid=', p.postid, '\t%' ) OR e.params LIKE CONCAT('%postid=', p.postid)
            WHERE
                e.userid=#
                AND
                DATE_SUB(CURDATE(),INTERVAL # DAY) <= datetime
            ORDER BY datetime DESC"
            .(qa_opt('user_act_list_max')?" LIMIT ".(int)qa_opt('user_act_list_max'):""),
            $userid, qa_opt('user_act_list_age')
        );

This is the guilty one ;)
 

Comments

It is probably the JOIN clause. Using LIKE for that is pretty inefficient, you should join on an actual field e.g. postid. I'm guessing the eventlog table doesn't have a postid field, which it probably should.

---

Hi Scott, thanks for the input. Table qa_eventlog has columns: datetime, ipaddress, userid, handle, cookieid, event, params.

I see myself still amateur using mysql, and will look into it soon, trying to find a solution. Of course I hope that NoahY might have an idea on that :)

Answer 1

Mmm.  Trying to do too much with one query I guess.  Can you try version 1.6 and see if it works?

Comments

Thanks, v1.6 needs <1 second to load the user history!

PS (qa-history-layer.php):
1. Line 161 you are using "error_log", shouldn't it be removed?
2. Line 331 with $activity_url = qa_path_html(...): Last parameter should be "null" instead of "$anchor". See also http://www.question2answer.org/qa/16060/

All the best,
Kai

---

Ah, I just compared the output of v1.4 with v1.6 and see that some user history is missing. E.g. in_a_select appears in v1.4 but not in v1.6. # In v1.4 I have 2 edits of one question (which is correct), but v1.6 shows only 1 edit. Another answer got edited twice (correct in v1.4), v1.6 is not showing anything.

Could it be that the postid is not handled correctly - leading to a situation that other events related to a specific postid are not showing up anymore? I can send you an excel sheet with the comparison v1.4 <-> v1.6 if needed.

---

I checked my server's error log:
[18-Oct-2012 13:30:52] PHP Notice:  Undefined offset:  1 in /qa-include/qa-app-format.php(1468) : eval()'d code on line 229

Line 229 of qa-history-layer.php:         $events[$m[1]] = $event;

# moving this line in the if() before, solves it, but then I get:
[18-Oct-2012 15:53:07] PHP Notice:  Undefined index:  post in /qa-include/qa-app-format.php(1468) : eval()'d code on line 340
[18-Oct-2012 15:53:07] PHP Notice:  Undefined index:  post in /qa-include/qa-app-format.php(1468) : eval()'d code on line 365

Line 340 of qa-history-layer.php:     $params['parentid'] = $event['post']['parentid'];
Line 365 of qa-history-layer.php:     $params['title'] = $event['post']['title'];

---

Okay, please try the latest code.

---

Showing history but error log says:
- PHP Notice:  Undefined index:  post in /qa-include/qa-app-format.php(1468) : eval()'d code on line 339
- PHP Notice:  Undefined index:  post in /qa-include/qa-app-format.php(1468) : eval()'d code on line 364

In the history list the post names are not showing up for: a_vote_up and q_vote_up.

---

okay, try again.

---

Thanks for your work. Recent state of error.log:
1. PHP Notice:  Undefined index:  postid in /qa-include/qa-app-format.php(1468) : eval()'d code on line 255
2. Undefined index:  2944 [different ids here] in /qa-include/qa-app-format.php(1468) : eval()'d code on line 340
3. PHP Notice:  Undefined index:  2628 in /qa-include/qa-app-format.php(1468) : eval()'d code on line 365

Human view, comparing outputs of v1.4 with v1.9:
1. I see more events than before! E.g. new: a_hide, in_a_question, c_hide, c_edit, c_post, c_reshow.
2. Badges do not show up (the entire line is missing).
3. I see q_edit output even if the post is deleted / does not exist any more.

Runtime of v1.9 < 2 sec.

---

Good morning Noah, I just checked the recent version that you upload 50 min ago. Very nice, working fast and showing all* events.

Not showing these events: a_delete, c_delete, q_delete (but I think this has to do with deleted questions and skipping them, which is okay!).

One badge is not showing up in my list, another one is. Maybe this has to do with my badge-plugin version 2.2?

---

I changed line 256 from:
if(!in_array($type, $nopost) && $post == null)

to:
if(!in_array($type, $nopost) && $post == null && $type!='badge_awarded')

Now all badges are showing up!

PS: Thank you Noah, your badge: "best q2a-plugin developer"!

---

Last thing to mention: I changed badge awarding, rechecked, new badges got awarded. However, no notification and nothing showing up in the user history list? ... I will update the badges plugin to the newest version soon, as I think this might be the problem. Just wanted to let you know.

---

Hi Noah, I am not sure but could the new amount of queries of the plugin be part of this error (that I got yesterday night): "has more than 'max_user_connections' active connections in /qa-include/qa-db.php on line 66" → see here http://question2answer.org/qa/18513/ ?