Hi,

Even after changing refresh timers on Chat module to 60/60 my mySQL server is really loading up and making the entire site slow (it was not an issue before).

I also have reports from a user that the photo albums keeps loading. Most likely due to the general performance of the server.

The server is pretty high specs for a low-traffic site. And there where no performance problems before I upgraded. I assume this is due to an extreme amounts of private messages in the system (300.829 records in jos_community_chat_activity). But I really find it a pity if the new private message system drags down performance like this - especially since we cannot choose to stay on the old system.

Is there an index we could create on the server to speed things up? Or are there a filter clause missing in the code so it does not traverse all 300K records all the time?

Also please note - only the front end of the site is slow, the back end - as well as the other web sites running on the same server is still fast as ever.

Thanks
Nicolai

HI, Nicolai.

Thank you for contacting us.
I assign developer to investigate this further.
He'll contact you ASAP.

Hi,

If its coming from chat please you change "Checking for new messages in active chat" and "Checking for new messages in other chats" to bigger value..

thank you!

Hi Dimas,

Thank you - I already set them to 60 seconds yesterday. Both of them.

I'm in the process of adding Webmin access, i'll add the info to the original post in a minute.

Checking processes I have identified the queries that seem to take a long time:
SELECT id FROM jos_community_chat_activity WHERE chat_id = 3092 AND user_id = 64 ORDER BY id DESC LIMIT 1
SELECT id FROM jos_community_chat_activity WHERE chat_id = 3774 AND user_id = 64 ORDER BY id DESC LIMIT 1
SELECT id FROM jos_community_chat_activity WHERE chat_id = 7976 AND user_id = 2838 ORDER BY id DESC LIMIT 1

Okay I think I have solved it now by creating an index.

I logged slow queries and identified

SELECT ca.*
                FROM jos_community_chat_activity ca
                LEFT JOIN jos_community_chat_participants cc ON ca.chat_id = cc.chat_id
                LEFT JOIN jos_community_chat c ON c.id = ca.chat_id
                WHERE cc.user_id = 64
                AND cc.enabled = 1
                ORDER BY ca.id DESC LIMIT 1

ALTER TABLE `jos_community_chat_activity` ADD INDEX `NWH_chat_id` (`chat_id`);

Hi Nicolai,

Nice found.. we will improve it.. I already reported this to our developer.

thank you!

Hello

I am experiencing similar speed issue on Jomsocial 4.4.5. Can you please advise where I should add the script that worked for Nicolai:

ALTER TABLE `jos_community_chat_activity` ADD INDEX `NWH_chat_id` (`chat_id`);

Thank you

Richard

Hi Richard,

Run that code via phpmyadmin, and change the prefix "jos_" with your own table prefix.

thank you!

Hi Dimas

Sorry to be so stupid but I don't know what table prefix I should change?

Richard

Hi,
Look Joomla configuration->Server->Database Tables Prefix, you can know the table prefix from there :)

Hi,
Look Joomla configuration->Server->Database Tables Prefix, you can know the table prefix from there :)

Hi,
Look Joomla configuration->Server->Database Tables Prefix, you can know the table prefix from there :)

Hi,
Look Joomla configuration->Server->Database Tables Prefix, you can know the table prefix from there :)

Not really sure how to do this. I have given you the necessary access details. Could you please do this for me?