@oscman
You need to tell me which query is problematic.

http://image.prntscr.com/image/e919f95004bc453e937cd47331e7a5e1.jpg
I think this is the query, running monitor processlist 1 sec spamms with this query
SELECT count (m.pk_i_id) as i _count FROM (oc_t_im_threads as t)

@oscman
It is not possible to say from your screenshot.
- go to your config.php, at bottom before ?> tag add:
define('OSC_DEBUG_DB', true) ;- login to oc-admin (just to be logged in)
- go to your homepage and at bottom of your page you have section "Database queries" - visible just to users logged in as admins
- there is list of all queries, that were send to database to generate your site
- if your site is loading 12 seconds, there must be query that execute at least 9seconds  (usually all queries are executed in 0.5s).

I have checked on our plugin's demo site. There are 4 queries from Instant Messenger plugin with execution time:
0.000787973403931s
0.000936031341553
0.000886917114258
0.00207591056824

Longest query:
SELECT t.i_thread_id, t.fk_i_item_id, t.i_from_user_id, t.s_from_user_name, t.s_from_user_email, t.i_from_user_notify, t.i_to_user_id, t.s_to_user_name, t.s_to_user_email, t.i_to_user_notify, t.s_title, t.d_datetime, count(m.pk_i_id) as i_count, t.i_flag
FROM (plugins_t_im_threads as t)
LEFT OUTER JOIN plugins_t_im_messages as m ON t.i_thread_id = m.fk_i_thread_id
WHERE t.i_from_user_id = 6707107 OR t.i_to_user_id = 6707107
GROUP BY t.i_thread_id, t.fk_i_item_id, t.i_from_user_id, t.s_from_user_name, t.s_from_user_email, t.i_from_user_notify, t.i_to_user_id, t.s_to_user_name, t.s_to_user_email, t.i_to_user_notify, t.s_title, t.i_flag
ORDER BY t.d_datetime DESC
LIMIT 6
It is one to show latest messages.
You could try to add index to your database if this one would take most time.

on query list i see only this 2 queries

SELECT oc_t_item.*, oc_t_item.s_contact_name as s_user_name FROM (oc_t_item, oc_t_item_stats) WHERE

SELECT count(m.pk_i_id) as i_count FROM (oc_t_im_threads as t) LEFT OUTER JOIN oc_t_im_messages as

the server run smooth when there are 300 or less users online when there are 300 or more users cpu usage goes up and up
this problem goes away the second i disable messeneger. I don't know what else i can do

Please help me! not having a messenger is killing my site!

@oscman
First do what I wrote to you. You cannot see 2 queries, osclass require at least 30 queries to generate every page.

@oscman
Slowest SQL is this one:
SELECT count(m.pk_i_id) as i_count
FROM (oc_t_im_threads as t)
LEFT OUTER JOIN oc_t_im_messages as m ON t.i_thread_id = m.fk_i_thread_id AND m.i_read = 0 AND (t.i_from_user_id = 2569 AND m.i_type = 1 OR t.i_to_user_id = 2569 AND m.i_type = 0)
It takes maximum 2 seconds to load.

It belongs to function from ModelIM.php:
public function countMessagesByUserId( $user_id ) {
  $this->dao->select('count(m.pk_i_id) as i_count');
  $this->dao->from( $this->getTable_threads() . ' as t' );
  $this->dao->join( $this->getTable_messages() . ' as m', 't.i_thread_id = m.fk_i_thread_id AND m.i_read = 0 AND (t.i_from_user_id = ' . $user_id . ' AND m.i_type = 1  OR  t.i_to_user_id = ' . $user_id . ' AND m.i_type = 0)', 'LEFT OUTER' );

  $result = $this->dao->get();
  if( !$result ) { return array(); }
  return $result->row();
}

You can try to change LEFT OUTER to INNER to check performance improvements.


This is used in 1 function only, in index.php:
im_messages
In this function you can try to replace this code:
    $count = ModelIM::newInstance()->countMessagesByUserId( $user_id );
    $count = $count['i_count'];

with:
$count = 0;

This function counts unread messages that are shown in link to message center (like in demo in header).


You could try to create indexes on used columns as well:
oc_t_im_messages.PK_I_ID
oc_t_im_messages.FK_I_THREAD_ID
oc_t_im_messages.I_READ
oc_t_im_messages.I_TYPE
oc_t_im_threads.I_THREAD_ID
oc_t_im_threads.I_FROM_USER_ID
oc_t_im_threads.I_TO_USER_ID

Testing now with just changing to INNER
its been 20 minutes since plugin was enabled and it looks good!
will report back if it starts again

@oscman
Good, I cannot remember why there is left outer, but I suppose that if you change it to inner, effect will be that in case there are no conversation unread, blank space is shown instead of zero.

looks like this was the problem, server didn't go over 2% load since i changed to INNER. Thank you so much for this fix.

could you check my question about republish button in backoffice forum?

The problem is back today, i don't know if its because there are a lot of messangers already or because i enabled backofice manager today, i pm you the sql debug could you check what is wrong again?

It started to use cpu And ram, now it is eating stable 60gb ram out of 100

When i go to plugin >> conversations it takes 1 minute to load the page

and how can i do this?

You could try to create indexes on used columns as well:
oc_t_im_messages.PK_I_ID
oc_t_im_messages.FK_I_THREAD_ID
oc_t_im_messages.I_READ
oc_t_im_messages.I_TYPE
oc_t_im_threads.I_THREAD_ID
oc_t_im_threads.I_FROM_USER_ID
oc_t_im_threads.I_TO_USER_ID

and there is query running and it says copying to tmp table on disk is that normal?
Query   784   Copying to tmp table on disk   SELECT t.i_thread_id, t.fk_i_item_id, t.i_from_user_id, t.s_from_user_name, t.s_from_user_email, t.i

@oscman
To create indexes talk with your Database Administrator, I am not expert here.
In ModelIM.php you have more "LEFT OUTER", change them to inner.
In some cases it takes 0.3s to get these data. Does not seems to be long, but if it is 1000*0.3s, it is more time...

Also consider using memcache to leverage your database.

Thanks, cpu load gone abit down when i switch to inner. It still eats 60 gb of ram and 25%cpu do you know why? if i enable memcache wouldn't that increase ram load?

Ok edit: now i can't send message when i go to user item and try to send a message, the send button spins but nothing happens, page doesn't refresh message is not sending. i switched back to left outer and it works.
What queries exactly are safe to make INNER?

@oscman
I am not db expert or what, I just know that my hosting told me to reduce database load, use memcache that will store queries running most against database.
If you use inner instead of left outer, worst what should happen is that there will be blank space instead of zero.

thank you for the info