Api.php causing MariaDB to stop responding (too many connections)

MrBiome

Linux Server Info:
Distributor ID: Ubuntu
Description: Ubuntu 22.04.4 LTS
Release: 22.04
Codename: jammy

Server Information
osTicket Version v1.18.1 (0375576) — Up to date
Web Server Software Apache/2.4.54 (Ubuntu)
MySQL Version 10.6.16
PHP Version 8.1.27

Hi there,

We have been running OSTICKET now for a few years without issue. Recently we have started having issues where the database becomes unresponsive, and the only way to fix is to kill the MariaDB (stopping the service never completes).

Looking at the processes when the issue occurs we see the process below, which has 150 cocurrent connections:

It appears that the cron job /var/www/osticket/api/cron.php which runs every minute, is not closing properly from about 2am in the morning. The connections then build up over time until all the DB connections are used and the DB stops responding. If I reboot the server or reset the mariadb process the DB is then accesible again and I can see the cron.php job running and closing the connections sucessfully again.

Has anyone experienced anything similar, or could point us in the right direction to resolve this issue please? Any help is greatly appreciated. Thanks.

KevinTheJedi

MrBiome

That query looks irrelevant. You need to enable MySQL slow query logs to see if there is a long running query that’s causing the stacking and eventual unresponsiveness.

Cheers.

MrBiome

KevinTheJedi ok thanks I have set that up so will monitor and see what turns up.

MrBiome

We are still seeing the database stop responding and I cannot see any pattern to the SQL processes that are running at the time. Yesterday the ost__search table became locked and it could not be queried at all when we had the issue. Today, I can see update queries for the DB but I could manually run select statements on the database. Below is the slow query log from today when we experienced the issue (about 12:30pm)

51.5s user time, 330ms system time, 42.88M rss, 48.21M vsz
Current date: Thu Apr 25 13:12:55 2024
Hostname: #######
Files: /var/log/mysql/mariadb-slow.log
Overall: 187.92k total, 120 unique, 0.48 QPS, 0.00x concurrency ________
Time range: 2024-04-21 00:00:25 to 2024-04-25 13:12:38
Attribute total min max avg 95% stddev median
============ ======= ======= ======= ======= ======= ======= =======
Exec time 767s 66us 6s 4ms 9ms 25ms 1ms
Lock time 16s 0 673ms 84us 144us 2ms 66us
Rows sent 4.79M 0 34.59k 26.70 151.03 266.50 6.98
Rows examine 52.14M 0 171.61k 290.94 487.09 2.12k 16.81
Rows affecte 8.44k 0 16 0.05 0 0.32 0
Bytes sent 445.91M 11 11.50M 2.43k 8.89k 89.04k 487.09
Merge passes 0 0 0 0 0 0 0
Tmp tables 82.95k 1 24 1.14 0.99 1.38 0.99
Tmp disk tbl 69.67k 0 3 0.96 0.99 0.20 0.99
Tmp tbl size 2.56G 0 13.94M 35.97k 23.58k 150.11k 23.58k
Query size 27.74M 22 5.86k 154.77 284.79 278.07 130.47
Boolean:
Filesort 77% yes, 22% no
Full join 1% yes, 98% no
Full scan 98% yes, 1% no
Priority que 0% yes, 99% no
Tmp table 41% yes, 58% no
Tmp table on 39% yes, 60% no
Process List

+------+---------------+---------- | Id | User | Host +------+---------------+---------- | 3669 | ###| 10.###| osticket_db | Sleep | 3671 | #### | 10### | NULL | 3713 | ###| 10.### | osticket_db | Sleep | 4149 | ###| 10.### | osticket_db | Sleep | 4150 | ###| 10.### | NULL | 4151 | ###| 10.### | NULL | 4318 | osticket_user | localhost | 4361 | osticket_user | localhost | 4440 | osticket_user | localhost | 4473 | osticket_user | localhost | 4503 | osticket_user | localhost | 4546 | osticket_user | localhost | 4556 | osticket_user | localhost | 4561 | osticket_user | localhost | 4603 | osticket_user | localhost | 4634 | root | localhost +------+---------------+---------- 16 rows in set (0.001 sec) ---------+-------------+---------+------+----------+------------------------------------------------------------------------------------------------------+----------+
| db | Command | Time | State | Info | Progress |
---------+-------------+---------+------+----------+------------------------------------------------------------------------------------------------------+----------+
| 4541 | | NULL | 0.000 |
| Sleep | 6180 | | NULL | 0.000 |
| 6341 | | NULL | 0.000 |
| 4541 | | NULL | 0.000 |
| Sleep | 5139 | | NULL | 0.000 |
| Sleep | 3943 | | NULL | 0.000 |
| osticket_db | Query | 3644 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009713', user_id | 0.000 |
| osticket_db | Query | 2984 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009714', user_id | 0.000 |
| osticket_db | Query | 2385 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009715', user_id | 0.000 |
| osticket_db | Query | 1783 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009716', user_id | 0.000 |
| osticket_db | Query | 1183 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009717', user_id | 0.000 |
| osticket_db | Query | 757 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009718', user_id | 0.000 |
| osticket_db | Query | 583 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009719', user_id | 0.000 |
| osticket_db | Query | 457 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009720', user_id | 0.000 |
| osticket_db | Query | 158 | Update | INSERT INTO ost_ticket SET created = NOW(), lastupdate = NOW(), number = '009721', user_id | 0.000 |
| osticket_db | Query | 0 | starting | SHOW PROCESSLIST | 0.000 |
---------+-------------+---------+------+----------+------------------------------------------------------------------------------------------------------+----------+

Any help on where to look next would be appreciated. I can only get things working again by running a task kill on the service.

KevinTheJedi

MrBiome

I see you have a couple of long running queries from NULL and from osticket_db however they are simply Sleep commands and not real queries. I can see the longest queries are Sleep commands which is odd. Does it give you more information? All I see is hash symbols #.

Cheers.

KevinTheJedi

MrBiome

You can also try switching to MySQL 8.0+ to see if that helps. DB performance is better out of the box with MySQL 8.0+ than lower versions and MariaDB versions.

Cheers.

KevinTheJedi

MrBiome

Also, at this point it does kinda sound like cron is not exiting which is causing the db query to not exit and infinitely hang. You should impose maximum execution limit in MariaDB/MySQL to see if that helps. Then you can enable DEBUG logs, etc. to see what exactly is causing it to hang.

Cheers.

MrBiome

KevinTheJedi The hashes are where I have removed the ip addresses - looks like these are local host queries. They seemed transitory as when I looked again at the processes they had disappeared

MrBiome

KevinTheJedi Ok thanks - I will look at switching the database over to MySQL and modifying the execution limit and see if that improves it. Again thanks for your help.