Good afternoon All,
Im hoping for some advise or direction with the below. We are running a Kamailio SBC used for high call volumes and recently decided to upgrade the Database servers as the existing servers were struggling. Both the old and new databases are using MariaDB(version details all below), the problem we face is that since migrating to the new database there appears to a limitation to the amount of DB connections that can be processed or accepted. The SBC functions normally until the CPS starts to exceed around 100cps at which point the SBC stops responding to invites(in bursts which resembles flood prevention). We have checked both the Kamailio logs and the MySQL logs on the DB, both log files have no indication of errors that would cause the problem experienced(the logs attached are seen when the SBC is cut back to use the old DB, not sure they are related to the fault), the connections/processes in mysql do not drastically increase or get stuck either. The fault points to a db connection limit of some kind but all users have no limitations set. If anyone has experienced a similar problem or has any advise it would be greatly appreciated.
Some info on the Kamialio and DB versions
New DB
root at voice-jhb-db01:~# mysql --version
mysql Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Old DB
[root at isadb01 ~]# mysql --version
mysql Ver 15.1 Distrib 10.0.36-MariaDB, for Linux (x86_64) using readline 5.1
Kamailio SBC
[root at sbc01 ~]# kamailio -v
version: kamailio 5.1.4 (x86_64/linux)
Kind regards,
Stephen
Voice Engineer
Tel:
2404
Fax:
3468
Email:
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20210531/d3a203ea/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0.png
Type: image/png
Size: 27438 bytes
Desc: not available
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20210531/d3a203ea/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 1.png
Type: image/png
Size: 168668 bytes
Desc: not available
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20210531/d3a203ea/attachment-0001.png>
-------------- next part --------------
MariaDB [mysql]> select * from user where User = 'kamailio' and Host = '%';
+------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+---------+--------------+--------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | is_role | default_role | max_statement_time |
+------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+---------+--------------+--------------------+
| % | kamailio | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | N | N | | 0.000000 |
+------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+---------+--------------+--------------------+
-------------- next part --------------
MariaDB [kamailio01]> show full processlist;
+------+-------------+-------------------+-------------+--------------+--------+-----------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+------+-------------+-------------------+-------------+--------------+--------+-----------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 10 | system user | | NULL | Slave_IO | 181147 | Waiting for master to send event | NULL | 0.000 |
| 13 | system user | | NULL | Slave_worker | 5 | Waiting for work from SQL thread | NULL | 0.000 |
| 14 | system user | | NULL | Slave_worker | 5 | Waiting for work from SQL thread | NULL | 0.000 |
| 12 | system user | | NULL | Slave_worker | 5 | Waiting for work from SQL thread | NULL | 0.000 |
| 15 | system user | | NULL | Slave_worker | 2 | Waiting for work from SQL thread | NULL | 0.000 |
| 16 | system user | | NULL | Slave_worker | 2 | Waiting for work from SQL thread | NULL | 0.000 |
| 17 | system user | | NULL | Slave_worker | 5 | Waiting for work from SQL thread | NULL | 0.000 |
| 19 | system user | | NULL | Slave_worker | 5 | Waiting for work from SQL thread | NULL | 0.000 |
| 18 | system user | | NULL | Slave_worker | 2 | Waiting for work from SQL thread | NULL | 0.000 |
| 11 | system user | | NULL | Slave_SQL | 435 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
| 334 | replication | 10.10.0.4:37846 | NULL | Binlog Dump | 180250 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
| 4025 | voipmonitor | 41.79.80.5:58632 | voipmonitor | Sleep | 3 | | NULL | 0.000 |
| 4026 | voipmonitor | 41.79.80.5:58634 | voipmonitor | Sleep | 12738 | | NULL | 0.000 |
| 4984 | voipmonitor | 41.79.80.22:38936 | voipmonitor | Sleep | 17 | | NULL | 0.000 |
| 4987 | root | localhost | kamailio01 | Query | 0 | Init | show full processlist | 0.000 |
| 4993 | kamailio | 41.79.80.5:59098 | kamailio01 | Sleep | 3 | | NULL | 0.000 |
| 4994 | kamailio | 41.79.80.5:59100 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 4995 | kamailio | 41.79.80.5:59102 | kamailio01 | Sleep | 3 | | NULL | 0.000 |
| 4997 | kamailio | 41.79.80.5:59106 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 4998 | kamailio | 41.79.80.5:59108 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 4999 | kamailio | 41.79.80.5:59110 | kamailio01 | Sleep | 4 | | NULL | 0.000 |
| 5000 | kamailio | 41.79.80.5:59114 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5001 | kamailio | 41.79.80.5:59116 | kamailio01 | Sleep | 3 | | NULL | 0.000 |
| 4996 | kamailio | 41.79.80.5:59104 | kamailio01 | Sleep | 3 | | NULL | 0.000 |
| 5002 | kamailio | 41.79.80.5:59112 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5003 | kamailio | 41.79.80.5:59118 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5004 | kamailio | 41.79.80.5:59120 | kamailio01 | Sleep | 4 | | NULL | 0.000 |
| 5005 | kamailio | 41.79.80.5:59122 | kamailio01 | Sleep | 5 | | NULL | 0.000 |
| 5006 | kamailio | 41.79.80.5:59124 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5007 | kamailio | 41.79.80.5:59126 | kamailio01 | Sleep | 4 | | NULL | 0.000 |
| 5008 | kamailio | 41.79.80.5:59128 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5009 | kamailio | 41.79.80.5:59130 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5010 | kamailio | 41.79.80.5:59132 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5011 | kamailio | 41.79.80.5:59134 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5012 | kamailio | 41.79.80.5:59136 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5013 | kamailio | 41.79.80.5:59138 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5014 | kamailio | 41.79.80.5:59140 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5015 | kamailio | 41.79.80.5:59142 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5016 | kamailio | 41.79.80.5:59144 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5017 | kamailio | 41.79.80.5:59146 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5018 | kamailio | 41.79.80.5:59148 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5019 | kamailio | 41.79.80.5:59150 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5020 | kamailio | 41.79.80.5:59152 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5021 | kamailio | 41.79.80.5:59154 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5022 | kamailio | 41.79.80.5:59156 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5023 | kamailio | 41.79.80.5:59158 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5024 | kamailio | 41.79.80.5:59160 | kamailio01 | Sleep | 1 | | NULL | 0.000 |
| 5025 | kamailio | 41.79.80.5:59162 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5026 | kamailio | 41.79.80.5:59164 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5027 | kamailio | 41.79.80.5:59166 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5028 | kamailio | 41.79.80.5:59168 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5029 | kamailio | 41.79.80.5:59170 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5030 | kamailio | 41.79.80.5:59172 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5031 | kamailio | 41.79.80.5:59174 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5032 | kamailio | 41.79.80.5:59176 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5033 | kamailio | 41.79.80.5:59178 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5034 | kamailio | 41.79.80.5:59180 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5035 | kamailio | 41.79.80.5:59182 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5036 | kamailio | 41.79.80.5:59184 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5037 | kamailio | 41.79.80.5:59186 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5038 | kamailio | 41.79.80.5:59188 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5039 | kamailio | 41.79.80.5:59190 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5040 | kamailio | 41.79.80.5:59192 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5041 | kamailio | 41.79.80.5:59194 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5042 | kamailio | 41.79.80.5:59196 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5043 | kamailio | 41.79.80.5:59200 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5044 | kamailio | 41.79.80.5:59198 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5045 | kamailio | 41.79.80.5:59202 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5046 | kamailio | 41.79.80.5:59204 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5047 | kamailio | 41.79.80.5:59206 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5048 | kamailio | 41.79.80.5:59208 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5049 | kamailio | 41.79.80.5:59210 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5050 | kamailio | 41.79.80.5:59214 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5051 | kamailio | 41.79.80.5:59212 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5052 | kamailio | 41.79.80.5:59216 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5053 | kamailio | 41.79.80.5:59218 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5054 | kamailio | 41.79.80.5:59220 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5055 | kamailio | 41.79.80.5:59222 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5056 | kamailio | 41.79.80.5:59224 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5057 | kamailio | 41.79.80.5:59226 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5058 | kamailio | 41.79.80.5:59228 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5059 | kamailio | 41.79.80.5:59230 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5060 | root | localhost:59232 | kamailio01 | Query | 0 | Updating | DELETE FROM cdr_live WHERE call_id = NAME_CONST('p_call_id',_utf8mb4'7f2263a47022d98b31f9a98b5de99771 at 10.250.2.202:5060' COLLATE 'utf8mb4_general_ci') | 0.000 |
| 5061 | root | localhost:59234 | kamailio01 | Query | 0 | Updating | DELETE FROM cdr_live WHERE call_id = NAME_CONST('p_call_id',_utf8mb4'471809ac2d0c5c5b39cd4ecd503f8360 at 10.250.2.231:5060' COLLATE 'utf8mb4_general_ci') | 0.000 |
| 5062 | kamailio | 41.79.80.5:59236 | kamailio01 | Query | 0 | Update | INSERT INTO cdr_live (call_id, start, original_src, original_dst, final_src, final_dst, src_account, dst_account, routing_label, disposition, tag01, tag02, tag03, export_status) VALUES ('0b2210090aa51e6041b60c5053ad1415 at 10.250.2.202:5060', NOW(2), '', '27787560679', '+27871358599', '+27787560679', 'UBERMO16', 'MTN', 'D083', 'PENDING', '', '', '', 'L') | 0.000 |
| 5063 | root | localhost:59238 | kamailio01 | Query | 0 | Sending data | INSERT INTO cdr_xfer SELECT * FROM cdr_live WHERE call_id = NAME_CONST('p_call_id',_utf8mb4'00ffba7c684481d252fc8a9a3c387637 at 41.79.80.133:5060' COLLATE 'utf8mb4_general_ci') | 0.000 |
| 5064 | kamailio | 41.79.80.5:59240 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5065 | kamailio | 41.79.80.5:59242 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5066 | kamailio | 41.79.80.5:59244 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
| 5067 | kamailio | 41.79.80.5:59246 | kamailio01 | Sleep | 7 | | NULL | 0.000 |
+------+-------------+-------------------+-------------+--------------+--------+-----------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
95 rows in set (0.000 sec)
-------------- next part --------------
2021-05-29 9:48:17 27496 [Warning] Aborted connection 27496 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27512 [Warning] Aborted connection 27512 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27515 [Warning] Aborted connection 27515 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27515 [Warning] Aborted connection 27515 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27510 [Warning] Aborted connection 27510 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27510 [Warning] Aborted connection 27510 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27499 [Warning] Aborted connection 27499 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27499 [Warning] Aborted connection 27499 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27506 [Warning] Aborted connection 27506 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27506 [Warning] Aborted connection 27506 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27492 [Warning] Aborted connection 27492 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27502 [Warning] Aborted connection 27502 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27492 [Warning] Aborted connection 27492 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27502 [Warning] Aborted connection 27502 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27514 [Warning] Aborted connection 27514 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27511 [Warning] Aborted connection 27511 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27511 [Warning] Aborted connection 27511 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27514 [Warning] Aborted connection 27514 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27487 [Warning] Aborted connection 27487 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27487 [Warning] Aborted connection 27487 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27488 [Warning] Aborted connection 27488 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27488 [Warning] Aborted connection 27488 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27552 [Warning] Aborted connection 27552 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27552 [Warning] Aborted connection 27552 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27551 [Warning] Aborted connection 27551 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27551 [Warning] Aborted connection 27551 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27554 [Warning] Aborted connection 27554 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27554 [Warning] Aborted connection 27554 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)
2021-05-29 9:48:17 27553 [Warning] Aborted connection 27553 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (Got an error reading communication packets)
2021-05-29 9:48:17 27553 [Warning] Aborted connection 27553 to db: 'kamailio01' user: 'kamailio' host: '41.79.80.5' (CLOSE_CONNECTION)