Since we started using Zulu heavily for remote workers we started having spikes in database use sometimes several times a day. These spikes can reach up to 30 CPU load and usually cause all Zulus to drop until it drops below 5.
During the spikes I can see a mysql process reach over 300% CPU. Is there a way to optimize de database to avoid such high cpu utilization? What are the recommendations to optimize Mysql for Freepbx?
Without knowing what the queries are ,that would be hard to say, at a time of low but not no mysql traffic, set the query log on
turn it of after a couple of minutes and when you look at that log, it might help you identify a missing index. Or perhaps the need to offload mysql to a seperate machine
Today was a particularly bad day. We had three events with over 40 CPU load (1 minute) where all calls dropped. I get this on the slow query log at the time of the overload:
SET timestamp=1614885923;
SELECT SQL_CALC_FOUND_ROWS i.*,
IFNULL(st.archived, 0) AS archived,
IFNULL(sum(1 - sa.seen), 0) AS unseenstreams, – clever way to count unseen streams
IFNULL(st.notification, ‘normal’) AS notification,
MAX(s.when) AS last_stream_event
FROM zulu_interactions_contacts c
JOIN zulu_interactions_members m ON m.linkedid = c.linkedid
JOIN zulu_interactions_interactions i ON m.interaction_id = i.id
LEFT OUTER JOIN zulu_interactions_interaction_states st
ON (st.interaction_id = i.id AND st.userman_id = c.userman_id)
LEFT OUTER JOIN zulu_interactions_streams s
ON (s.interaction_id = i.id)
LEFT OUTER JOIN zulu_interactions_stream_actions sa
ON (sa.stream_id = s.id AND sa.zulu_id = c.userman_id)
WHERE c.type = 'USER'
AND c.userman_id = '329'
GROUP BY i.id
HAVING archived = 0
ORDER BY
GREATEST(MAX(s.`when`), i.updated_at) DESC,
i.start_time DESC,
-- order by id to get stable ordering when timestamps are the same
i.id DESC
LIMIT 0, 30;
Zulu seems to be the offender there but I have no idea how to prevent this. I ran mysqltuner and implemented the recommendations to optimize the server but things seems to be getting worse.
For this sort of reason, my guess is that you will see less and less of Zulu until it’s just gone.
I agree that Zulu must go but for this moment I am stuck with it. The question is how to survive until we can find a replacement. As far as I can tell when users are heavily using chat or searching through the directory is when we start seeing CPU load climbing. As a desperate measure I am stopping Apache when CPU load goes over 8 (1min). This will stop most interactions between Zulus (chat, login, contacts) that use Apache (/admin/ajax.php) and prevent CPU load from booting everyone off the server. Unfortunately I cannot keep Apache down as that prevents users from logging in. At least it lets active users keep their calls.
The problem with Sangoma Connect is that it does not have the same features as Zulu yet and has some stupid restrictions like only be able to use it on a single mobile device (plus no desktop client).
Already did that. We originally had an appliance from Sangoma rated for 400 users. That server started rebooting randomly which we thought was a hardware problem. We replaced that with a Dell server that has a more powerful CPU and double the ram. This server should handle almost double the load. I am afraid that even if I get another more powerful server that Zulu will still bring it to its knees.
Curious if you have opened a bug or support ticket on this. It may be worth doing. As a commercial module we don’t have the source so it is difficult to troubleshoot code issues etc.
I don’t know anything about zulu, but I’m wondering if part of the issue is that some of the zulu tables the query is traversing are huge? If so, can some of the data be pruned if not really needed.
Might also be useful to get disk IO stats to see if slow disk IO is contributing. I usually use atop, but ‘iostat -xdk 10’ works too.
That is unfortunate but the source is closed and by many ‘anecdotes’ is a resource hog. Likely only the Publisher can help you.
I have spent over 400 USD for a support ticket from Sangoma and after more than a week the only conclusion was that you should not use Zulu for more than 30% of the server capacity. Of course there is no documentation on those limits so we’re screwed and the only option seems to stop uising Zulu. My original server was for 400 users and the new one is a bit more powerful (600 users I would estimate). We have around 170 users on Zulu since the office was closed and everyone started working from home.
Today I am doing a reset on all Zulu data to see if that will reduce the database burden. I have tried to optimize mariadb as much as possible to give it extra resources but until now we are still hitting 40 CPU load a couple times a day during peak hours. I guess that is why Zulu is free with PBXact.
Yes, unless you are dealing with a bug (which they do not consider this as) you need to purchas support credits. It is $395 USD for a two hour block.
After doing some Mariadb optimization (mysqltuner is your friend) and getting rid of all of Zulu history we are now getting much lower CPU load all day. I guess we cannot keep more than a few months of zulu interactions to prevent an overload.
Obviously users are mad that they do not have access to their chat history but the alternative was several incidents a day. Now to keep an eye on the zulu interactions tables to see how fast they grow.
hi
I’m interested by what you do for solve the issue .
Can you tell us some tweaks you do for optimize database with mysqltuner ?
and the commande for rid Zulu history for older than
last * month
Thanks
Interesting, we went trough exactly the same findings and had to abandon our Pbxact 60 for it.
Paid 400 dollars in support credits for it as well without any result and now have a useless appliance.
We moved to a VM which is able to cope with the load (luckily we have far fewer users) and only make use of the calling functionality.