Jump to content


performance and chat archive

performance

  • You cannot reply to this topic
No replies to this topic

#1 peggy

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 03 September 2018 - 09:41 AM

Hi,
It's been a few years since we've installed Livezilla as our chat solution and each year in the peak period we clash on performance problems.

We've about:
- 100 registered operators
- 50 of them usually active
- few hundreds of concurrent clients
- 1000-2000 chat/day, made in about 6 hour, with a peak of 200-250 concurrent chats

In the past we've tried many ways to improve performance, in addiction to the documented advisories:
- we've supercharged our dedicated server
- we've checked slow queries and created missing mysql indexes, we also now hold the mysql tmp folder in RAM
- we've cached all the cacheable web resource
- we're throttling the php requests to avoid excessive server load

Now we still experience some load trouble with a client pool frequency time set at 8 sec (that, in our experience, is the upper limit to guarantee operator's needs)

We had a two year chat archive in the lz_chat_archive table (180.000 chats), we reduced it to 6 months to improve performance (now the archive holds 50.000 chats).

Queries on that table are now our major bottleneck and this query seems to be not optimizable due to the LIKE %string% comparison

SELECT COUNT(*) AS `total`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_archive` WHERE `closed`>N AND ((`chat_type`=N AND `group_id` IN ('S','S')) OR (`chat_type`=N AND (`group_id` IN ('S','S') OR `group_id`='S')) OR (`chat_type`=N AND `internal_id` LIKE 'S'))) AS `sta`) AS `totalperm`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_archive` WHERE `closed`>N AND (`chat_type`=N OR `chat_type`=N OR `chat_type`=N) AND ((LOWER(`fullname`) LIKE 'S' OR LOWER(`area_code`) LIKE 'S' OR LOWER(`plaintext`) LIKE 'S' OR LOWER(`email`) LIKE 'S' OR LOWER(`company`) LIKE 'S' OR LOWER(`phone`) LIKE 'S' OR LOWER(`chat_id`) LIKE 'S' OR LOWER(`external_id`) LIKE 'S' OR LOWER(`customs`) LIKE 'S' OR LOWER(`question`) LIKE 'S')) AND ((`chat_type`=N AND `group_id` IN ('S','S')) OR (`chat_type`=N AND (`group_id` IN ('S','S') OR `group_id`='S')) OR (`chat_type`=N AND `internal_id` LIKE 'S')) ORDER BY `closed` DESC) AS `stb`) AS `totalquery` FROM `lz_chat_archive`

SELECT COUNT(*) AS `total`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_archive` WHERE `closed`>N AND ((`chat_type`=N AND `group_id` IN ('S','S')) OR (`chat_type`=N AND (`group_id` IN ('S','S') OR `group_id`='S')) OR (`chat_type`=N AND `internal_id` LIKE 'S'))) AS `sta`) AS `totalperm`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_archive` WHERE `closed`>N AND (`chat_type`=N OR `chat_type`=N OR `chat_type`=N) AND ((`chat_type`=N AND `group_id` IN ('S','S')) OR (`chat_type`=N AND (`group_id` IN ('S','S') OR `group_id`='S')) OR (`chat_type`=N AND `internal_id` LIKE 'S')) ORDER BY `closed` DESC) AS `stb`) AS `totalquery` FROM `lz_chat_archive`

SELECT COUNT(*) AS `total`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_archive` WHERE `closed`>N AND ((`chat_type`=N AND `group_id` IN ('S')) OR (`chat_type`=N AND (`group_id` IN ('S') OR `group_id`='S')) OR (`chat_type`=N AND `internal_id` LIKE 'S'))) AS `sta`) AS `totalperm`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_archive` WHERE `closed`>N AND (`chat_type`=N OR `chat_type`=N OR `chat_type`=N) AND ((`chat_type`=N AND `group_id` IN ('S')) OR (`chat_type`=N AND (`group_id` IN ('S') OR `group_id`='S')) OR (`chat_type`=N AND `internal_id` LIKE 'S')) ORDER BY `closed` DESC) AS `stb`) AS `totalquery` FROM `lz_chat_archive`

here's an example:

SELECT COUNT(*) AS `total`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_archive` WHERE `closed`>0 AND ((`chat_type`=1 AND `group_id` IN ('YYY')) OR (`chat_type`=2 AND (`group_id` IN ('YYY') OR `group_id`='ZZZ')) OR (`chat_type`=0 AND `internal_id` LIKE '%f82fb931bb%'))) AS `sta`) AS `totalperm`,(SELECT COUNT(*) FROM (SELECT `lz_chat_archive`.`chat_id` FROM `lz_chat_archive` WHERE `closed`>0 AND (`chat_type`=0 OR `chat_type`=1 OR `chat_type`=2) AND ((`chat_type`=1 AND `group_id` IN ('YYY')) OR (`chat_type`=2 AND (`group_id` IN ('YYY') OR `group_id`='ZZZ')) OR (`chat_type`=0 AND `internal_id` LIKE '%f82fb931bb%')) ORDER BY `closed` DESC) AS `stb`) AS `totalquery` FROM `lz_chat_archive`;

I wonder if all the LIKE %string% are really necessary, especially on the second and third query searching for internal_id.

In a previous livezilla version we managed to differentiate the client pool frequency time between customers and operators with some successes.
In our experience seems to be better to differentiate pooling frequency between clients in active chat and waiting clients or between operators and customers.

Based on this scenario, have you further improvements or patches that we could apply to increase our setup resilience during peak time and, hopefully, reduce client pool frequency time ?

Best regards,
Matteo





1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users