playSMS Forum

MySQL to peak load CPU

I enable slow log and receive next:
SET timestamp=1572173531;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘gateway’ AND registry_family=‘generic’;

Time: 2019-10-27T10:52:11.814910Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000102 Lock_time: 0.000043 Rows_sent: 1 Rows_examined: 1

SET timestamp=1572173531;
SELECT * FROM playsms_gatewayInfobip_config;

Time: 2019-10-27T10:52:11.818170Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000237 Lock_time: 0.000063 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173531;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘gateway’ AND registry_family=‘jasmin’;

Time: 2019-10-27T10:52:11.822385Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000233 Lock_time: 0.000050 Rows_sent: 14 Rows_examined: 204

SET timestamp=1572173531;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘gateway’ AND registry_family=‘kannel’;

Time: 2019-10-27T10:52:11.826532Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000104 Lock_time: 0.000054 Rows_sent: 1 Rows_examined: 1

SET timestamp=1572173531;
SELECT * FROM playsms_gatewayNexmo_config;

Time: 2019-10-27T10:52:11.829699Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000227 Lock_time: 0.000050 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173531;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘gateway’ AND registry_family=‘openvox’;

Time: 2019-10-27T10:52:11.834488Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000237 Lock_time: 0.000049 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173531;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘gateway’ AND registry_family=‘playnet’;

Time: 2019-10-27T10:52:11.839124Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000247 Lock_time: 0.000049 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173531;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘gateway’ AND registry_family=‘smstools’;

Time: 2019-10-27T10:52:11.843307Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000101 Lock_time: 0.000041 Rows_sent: 1 Rows_examined: 1

SET timestamp=1572173531;
SELECT * FROM playsms_gatewayTelerivet_config;

Time: 2019-10-27T10:52:11.846369Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000092 Lock_time: 0.000042 Rows_sent: 1 Rows_examined: 1

SET timestamp=1572173531;
SELECT * FROM playsms_gatewayTwilio_config;

Time: 2019-10-27T10:52:11.849413Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000094 Lock_time: 0.000040 Rows_sent: 1 Rows_examined: 1

SET timestamp=1572173531;
SELECT * FROM playsms_gatewayUplink_config;

Time: 2019-10-27T10:52:11.868963Z

User@Host: playsms[playsms] @ localhost [] Id: 3046

Query_time: 0.000106 Lock_time: 0.000040 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173531;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:12.199881Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.000916 Lock_time: 0.000255 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173532;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:12.206367Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.000282 Lock_time: 0.000101 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173532;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:12.282250Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.072363 Lock_time: 0.000128 Rows_sent: 0 Rows_examined: 256513

SET timestamp=1572173532;
SELECT * FROM playsms_tblDLR WHERE flag_processed=‘1’ LIMIT 1000;

Time: 2019-10-27T10:52:12.288912Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.000160 Lock_time: 0.000063 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173532;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:12.308347Z

User@Host: playsms[playsms] @ localhost [] Id: 3048

Query_time: 0.000457 Lock_time: 0.000110 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173532;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:12.309335Z

User@Host: playsms[playsms] @ localhost [] Id: 3048

Query_time: 0.000203 Lock_time: 0.000075 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173532;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:12.314384Z

User@Host: playsms[playsms] @ localhost [] Id: 3048

Query_time: 0.004721 Lock_time: 0.000074 Rows_sent: 0 Rows_examined: 4860

SET timestamp=1572173532;
SELECT * FROM playsms_tblRecvSMS WHERE flag_processed=‘1’ LIMIT 1000;

Time: 2019-10-27T10:52:12.314946Z

User@Host: playsms[playsms] @ localhost [] Id: 3048

Query_time: 0.000149 Lock_time: 0.000056 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173532;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:12.323777Z

User@Host: playsms[playsms] @ localhost [] Id: 3049

Query_time: 0.000286 Lock_time: 0.000062 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173532;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:12.327979Z

User@Host: playsms[playsms] @ localhost [] Id: 3049

Query_time: 0.000137 Lock_time: 0.000051 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173532;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:12.331522Z

User@Host: playsms[playsms] @ localhost [] Id: 3049

Query_time: 0.000251 Lock_time: 0.000052 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173532;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘features’ AND registry_family=‘mailsms’;

Time: 2019-10-27T10:52:12.335521Z

User@Host: playsms[playsms] @ localhost [] Id: 3049

Query_time: 0.000114 Lock_time: 0.000038 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173532;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:12.445301Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.152431 Lock_time: 0.000072 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173532;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘blocked’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:12.586793Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.135570 Lock_time: 0.000072 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173532;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘dev’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:12.730881Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.136297 Lock_time: 0.000074 Rows_sent: 412 Rows_examined: 134055

SET timestamp=1572173532;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘kannel’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:12.734869Z

User@Host: playsms[playsms] @ localhost [] Id: 3050

Query_time: 0.000685 Lock_time: 0.000150 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173532;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:12.735767Z

User@Host: playsms[playsms] @ localhost [] Id: 3050

Query_time: 0.000231 Lock_time: 0.000080 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173532;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:12.748596Z

User@Host: playsms[playsms] @ localhost [] Id: 3051

Query_time: 0.000301 Lock_time: 0.000060 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173532;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:12.749071Z

User@Host: playsms[playsms] @ localhost [] Id: 3051

Query_time: 0.000159 Lock_time: 0.000052 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173532;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:12.789510Z

User@Host: playsms[playsms] @ localhost [] Id: 3050

Query_time: 0.053481 Lock_time: 0.000077 Rows_sent: 0 Rows_examined: 128243

SET timestamp=1572173532;
SELECT id, queue_code FROM playsms_tblSMSOutgoing_queue WHERE flag=‘0’ LIMIT 10;

Time: 2019-10-27T10:52:12.835329Z

User@Host: playsms[playsms] @ localhost [] Id: 3050

Query_time: 0.045442 Lock_time: 0.000149 Rows_sent: 0 Rows_examined: 128243

SET timestamp=1572173532;
SELECT id, queue_code FROM playsms_tblSMSOutgoing_queue WHERE flag=‘3’ LIMIT 10;

Time: 2019-10-27T10:52:12.897807Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.150354 Lock_time: 0.000076 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173532;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘nexmo’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:13.039484Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.137574 Lock_time: 0.000060 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173532;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘xvestrus’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:13.179209Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.135602 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173533;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘xvesttaj’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:13.319050Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.135700 Lock_time: 0.000059 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173533;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘barclayalert’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:13.320938Z

User@Host: playsms[playsms] @ localhost [] Id: 3052

Query_time: 0.000325 Lock_time: 0.000073 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173533;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:13.321630Z

User@Host: playsms[playsms] @ localhost [] Id: 3052

Query_time: 0.000144 Lock_time: 0.000054 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173533;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:13.324802Z

User@Host: playsms[playsms] @ localhost [] Id: 3052

Query_time: 0.002986 Lock_time: 0.000055 Rows_sent: 0 Rows_examined: 4860

SET timestamp=1572173533;
SELECT * FROM playsms_tblRecvSMS WHERE flag_processed=‘1’ LIMIT 1000;

Time: 2019-10-27T10:52:13.325330Z

User@Host: playsms[playsms] @ localhost [] Id: 3052

Query_time: 0.000103 Lock_time: 0.000039 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173533;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:13.342855Z

User@Host: playsms[playsms] @ localhost [] Id: 3053

Query_time: 0.000296 Lock_time: 0.000062 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173533;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:13.343410Z

User@Host: playsms[playsms] @ localhost [] Id: 3053

Query_time: 0.000140 Lock_time: 0.000052 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173533;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:13.343849Z

User@Host: playsms[playsms] @ localhost [] Id: 3053

Query_time: 0.000223 Lock_time: 0.000051 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173533;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘features’ AND registry_family=‘mailsms’;

Time: 2019-10-27T10:52:13.344412Z

User@Host: playsms[playsms] @ localhost [] Id: 3053

Query_time: 0.000101 Lock_time: 0.000039 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173533;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:13.487067Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.151817 Lock_time: 0.000071 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173533;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘rbsalert’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:13.629749Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.138568 Lock_time: 0.000059 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173533;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘hsbalert’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:13.751828Z

User@Host: playsms[playsms] @ localhost [] Id: 3054

Query_time: 0.000447 Lock_time: 0.000140 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173533;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:13.752335Z

User@Host: playsms[playsms] @ localhost [] Id: 3054

Query_time: 0.000149 Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173533;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:13.777688Z

User@Host: playsms[playsms] @ localhost [] Id: 3047

Query_time: 0.143823 Lock_time: 0.000062 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173533;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘bistox’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:13.840730Z

User@Host: playsms[playsms] @ localhost [] Id: 3055

Query_time: 0.000759 Lock_time: 0.000174 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173533;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:13.841961Z

User@Host: playsms[playsms] @ localhost [] Id: 3055

Query_time: 0.000226 Lock_time: 0.000082 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173533;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:13.888135Z

User@Host: playsms[playsms] @ localhost [] Id: 3055

Query_time: 0.045742 Lock_time: 0.000092 Rows_sent: 0 Rows_examined: 128243

SET timestamp=1572173533;
SELECT id, queue_code FROM playsms_tblSMSOutgoing_queue WHERE flag=‘0’ LIMIT 10;

Time: 2019-10-27T10:52:13.932260Z

User@Host: playsms[playsms] @ localhost [] Id: 3055

Query_time: 0.043692 Lock_time: 0.000116 Rows_sent: 0 Rows_examined: 128243

SET timestamp=1572173533;
SELECT id, queue_code FROM playsms_tblSMSOutgoing_queue WHERE flag=‘3’ LIMIT 10;

Time: 2019-10-27T10:52:14.333117Z

User@Host: playsms[playsms] @ localhost [] Id: 3056

Query_time: 0.000859 Lock_time: 0.000195 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173534;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:14.338226Z

User@Host: playsms[playsms] @ localhost [] Id: 3056

Query_time: 0.000461 Lock_time: 0.000159 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173534;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:14.344119Z

User@Host: playsms[playsms] @ localhost [] Id: 3056

Query_time: 0.004947 Lock_time: 0.000144 Rows_sent: 0 Rows_examined: 4860

SET timestamp=1572173534;
SELECT * FROM playsms_tblRecvSMS WHERE flag_processed=‘1’ LIMIT 1000;

Time: 2019-10-27T10:52:14.344972Z

User@Host: playsms[playsms] @ localhost [] Id: 3056

Query_time: 0.000657 Lock_time: 0.000050 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173534;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:14.350514Z

User@Host: playsms[playsms] @ localhost [] Id: 3057

Query_time: 0.000297 Lock_time: 0.000062 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173534;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:14.351029Z

User@Host: playsms[playsms] @ localhost [] Id: 3057

Query_time: 0.000162 Lock_time: 0.000051 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173534;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:14.351444Z

User@Host: playsms[playsms] @ localhost [] Id: 3057

Query_time: 0.000222 Lock_time: 0.000050 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173534;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘features’ AND registry_family=‘mailsms’;

Time: 2019-10-27T10:52:14.351733Z

User@Host: playsms[playsms] @ localhost [] Id: 3057

Query_time: 0.000101 Lock_time: 0.000039 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173534;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:14.757264Z

User@Host: playsms[playsms] @ localhost [] Id: 3058

Query_time: 0.000722 Lock_time: 0.000139 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173534;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:14.758344Z

User@Host: playsms[playsms] @ localhost [] Id: 3058

Query_time: 0.000248 Lock_time: 0.000102 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173534;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:14.784434Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.000499 Lock_time: 0.000117 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173534;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:14.791194Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.000262 Lock_time: 0.000108 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173534;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:14.869813Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.074862 Lock_time: 0.000071 Rows_sent: 0 Rows_examined: 256513

SET timestamp=1572173534;
SELECT * FROM playsms_tblDLR WHERE flag_processed=‘1’ LIMIT 1000;

Time: 2019-10-27T10:52:14.874499Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.000154 Lock_time: 0.000054 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173534;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:14.936904Z

User@Host: playsms[playsms] @ localhost [] Id: 3060

Query_time: 0.000765 Lock_time: 0.000208 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173534;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:14.938095Z

User@Host: playsms[playsms] @ localhost [] Id: 3060

Query_time: 0.000406 Lock_time: 0.000132 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173534;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:14.987839Z

User@Host: playsms[playsms] @ localhost [] Id: 3060

Query_time: 0.049251 Lock_time: 0.000123 Rows_sent: 0 Rows_examined: 128243

SET timestamp=1572173534;
SELECT id, queue_code FROM playsms_tblSMSOutgoing_queue WHERE flag=‘0’ LIMIT 10;

Time: 2019-10-27T10:52:15.020243Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.141112 Lock_time: 0.000060 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173534;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘blocked’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:15.036845Z

User@Host: playsms[playsms] @ localhost [] Id: 3060

Query_time: 0.048550 Lock_time: 0.000181 Rows_sent: 0 Rows_examined: 128243

SET timestamp=1572173534;
SELECT id, queue_code FROM playsms_tblSMSOutgoing_queue WHERE flag=‘3’ LIMIT 10;

Time: 2019-10-27T10:52:15.164915Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.139071 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173535;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘dev’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:15.306972Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.136502 Lock_time: 0.000061 Rows_sent: 412 Rows_examined: 134055

SET timestamp=1572173535;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘kannel’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:15.352674Z

User@Host: playsms[playsms] @ localhost [] Id: 3061

Query_time: 0.000831 Lock_time: 0.000229 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173535;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:15.354436Z

User@Host: playsms[playsms] @ localhost [] Id: 3061

Query_time: 0.000493 Lock_time: 0.000205 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173535;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:15.361753Z

User@Host: playsms[playsms] @ localhost [] Id: 3061

Query_time: 0.006684 Lock_time: 0.000168 Rows_sent: 0 Rows_examined: 4860

SET timestamp=1572173535;
SELECT * FROM playsms_tblRecvSMS WHERE flag_processed=‘1’ LIMIT 1000;

Time: 2019-10-27T10:52:15.362314Z

User@Host: playsms[playsms] @ localhost [] Id: 3061

Query_time: 0.000202 Lock_time: 0.000072 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173535;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:15.363027Z

User@Host: playsms[playsms] @ localhost [] Id: 3062

Query_time: 0.000313 Lock_time: 0.000071 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173535;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:15.363656Z

User@Host: playsms[playsms] @ localhost [] Id: 3062

Query_time: 0.000145 Lock_time: 0.000055 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173535;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:15.364238Z

User@Host: playsms[playsms] @ localhost [] Id: 3062

Query_time: 0.000227 Lock_time: 0.000052 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173535;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘features’ AND registry_family=‘mailsms’;

Time: 2019-10-27T10:52:15.364622Z

User@Host: playsms[playsms] @ localhost [] Id: 3062

Query_time: 0.000104 Lock_time: 0.000040 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173535;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:15.456524Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.142408 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173535;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘nexmo’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:15.600042Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.138076 Lock_time: 0.000580 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173535;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘xvestrus’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:15.742091Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.136353 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173535;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘xvesttaj’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:15.762218Z

User@Host: playsms[playsms] @ localhost [] Id: 3063

Query_time: 0.000606 Lock_time: 0.000118 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173535;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:15.763203Z

User@Host: playsms[playsms] @ localhost [] Id: 3063

Query_time: 0.000376 Lock_time: 0.000116 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173535;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:15.883896Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.135937 Lock_time: 0.000079 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173535;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘barclayalert’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:16.027140Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.136822 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173535;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘rbsalert’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:16.041577Z

User@Host: playsms[playsms] @ localhost [] Id: 3064

Query_time: 0.000356 Lock_time: 0.000077 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173536;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:16.045870Z

User@Host: playsms[playsms] @ localhost [] Id: 3064

Query_time: 0.000160 Lock_time: 0.000063 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173536;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:16.093141Z

User@Host: playsms[playsms] @ localhost [] Id: 3064

Query_time: 0.043954 Lock_time: 0.000050 Rows_sent: 0 Rows_examined: 128243

SET timestamp=1572173536;
SELECT id, queue_code FROM playsms_tblSMSOutgoing_queue WHERE flag=‘0’ LIMIT 10;

Time: 2019-10-27T10:52:16.140864Z

User@Host: playsms[playsms] @ localhost [] Id: 3064

Query_time: 0.043706 Lock_time: 0.000115 Rows_sent: 0 Rows_examined: 128243

SET timestamp=1572173536;
SELECT id, queue_code FROM playsms_tblSMSOutgoing_queue WHERE flag=‘3’ LIMIT 10;

Time: 2019-10-27T10:52:16.170912Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.138093 Lock_time: 0.000068 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173536;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘hsbalert’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:16.311227Z

User@Host: playsms[playsms] @ localhost [] Id: 3059

Query_time: 0.134723 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: 134055

SET timestamp=1572173536;
SELECT * FROM playsms_tblSMSOutgoing WHERE p_status=‘0’ AND p_smsc=‘bistox’ AND flag_deleted=‘0’;

Time: 2019-10-27T10:52:16.370848Z

User@Host: playsms[playsms] @ localhost [] Id: 3066

Query_time: 0.000589 Lock_time: 0.000143 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173536;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:16.370852Z

User@Host: playsms[playsms] @ localhost [] Id: 3065

Query_time: 0.000538 Lock_time: 0.000089 Rows_sent: 39 Rows_examined: 204

SET timestamp=1572173536;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘1’ AND registry_group=‘core’ AND registry_family=‘main_config’;

Time: 2019-10-27T10:52:16.371815Z

User@Host: playsms[playsms] @ localhost [] Id: 3066

Query_time: 0.000226 Lock_time: 0.000081 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173536;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:16.372905Z

User@Host: playsms[playsms] @ localhost [] Id: 3065

Query_time: 0.000936 Lock_time: 0.000799 Rows_sent: 0 Rows_examined: 37

SET timestamp=1572173536;
SELECT language_module FROM playsms_tblUser WHERE flag_deleted=‘0’ AND username=’’;

Time: 2019-10-27T10:52:16.373492Z

User@Host: playsms[playsms] @ localhost [] Id: 3066

Query_time: 0.000357 Lock_time: 0.000079 Rows_sent: 0 Rows_examined: 204

SET timestamp=1572173536;
SELECT registry_key, registry_value FROM playsms_tblRegistry WHERE uid=‘0’ AND registry_group=‘features’ AND registry_family=‘mailsms’;

Time: 2019-10-27T10:52:16.374022Z

User@Host: playsms[playsms] @ localhost [] Id: 3066

Query_time: 0.000177 Lock_time: 0.000058 Rows_sent: 10 Rows_examined: 10

SET timestamp=1572173536;
SELECT * FROM playsms_tblGateway;

Time: 2019-10-27T10:52:16.377877Z

User@Host: playsms[playsms] @ localhost [] Id: 3065

Query_time: 0.004438 Lock_time: 0.000105 Rows_sent: 0 Rows_examined: 4860

SET timestamp=1572173536;
SELECT * FROM playsms_tblRecvSMS WHERE flag_processed=‘1’ LIMIT 1000;

Time: 2019-10-27T10:52:16.378322Z

User@Host: playsms[playsms] @ localhost [] Id: 3065

Query_time: 0.000105 Lock_time: 0.000039 Rows_sent: 10 Rows_examined: 10

Hi everybody!
Guys, Anton any ideas?
Please help!!!

Regards,
Jamshid Tursunov

You can do some things to optimize your MySQL load:

  • Purge old table data (outgoing, outgoing_queue, outgoing_queue_dst)
  • Verify your table sizes (number of rows)
  • Verify SQL queries that use those tables
  • Create indexes.

Edilson, thanks for answering.
I have one request for you, if this does not bother you.
I do not have advanced knowledge of MySQL.
If I give you access (via teamviewer), can you customize me?
Thanks in advance.

Respectfully,
Jamshid Tursunov

mysql> show tables
-> ;
±----------------------------------------+
| Tables_in_playsms |
±----------------------------------------+
| playsms_featureAutoreply |
| playsms_featureAutoreply_scenario |
| playsms_featureBoard |
| playsms_featureBoard_log |
| playsms_featureCommand |
| playsms_featureCredit |
| playsms_featureCustom |
| playsms_featureFirewall |
| playsms_featureInboxgroup |
| playsms_featureInboxgroup_catchall |
| playsms_featureInboxgroup_log_in |
| playsms_featureInboxgroup_log_out |
| playsms_featureInboxgroup_members |
| playsms_featureMsgtemplate |
| playsms_featureOutgoing |
| playsms_featurePhonebook |
| playsms_featurePhonebook_group |
| playsms_featurePhonebook_group_contacts |
| playsms_featurePoll |
| playsms_featurePoll_choice |
| playsms_featurePoll_log |
| playsms_featureQuiz |
| playsms_featureQuiz_log |
| playsms_featureSchedule |
| playsms_featureSchedule_dst |
| playsms_featureSendfromfile |
| playsms_featureSimplerate |
| playsms_featureSmssysnc |
| playsms_featureStoplist |
| playsms_featureSubscribe |
| playsms_featureSubscribe_member |
| playsms_featureSubscribe_msg |
| playsms_gatewayBulksms_apidata |
| playsms_gatewayBulksms_config |
| playsms_gatewayClickatell_apidata |
| playsms_gatewayClickatell_config |
| playsms_gatewayGeneric_log |
| playsms_gatewayInfobip_apidata |
| playsms_gatewayInfobip_config |
| playsms_gatewayJasmin_log |
| playsms_gatewayNexmo |
| playsms_gatewayNexmo_config |
| playsms_gatewayPlaynet_outgoing |
| playsms_gatewaySmstools_dlr |
| playsms_gatewayTelerivet |
| playsms_gatewayTelerivet_config |
| playsms_gatewayTemplate_config |
| playsms_gatewayTwilio |
| playsms_gatewayTwilio_config |
| playsms_gatewayUplink |
| playsms_gatewayUplink_config |
| playsms_tblACL |
| playsms_tblBilling |
| playsms_tblCountry |
| playsms_tblDLR |
| playsms_tblGateway |
| playsms_tblNotif |
| playsms_tblRecvSMS |
| playsms_tblRegistry |
| playsms_tblSMSInbox |
| playsms_tblSMSIncoming |
| playsms_tblSMSOutgoing |
| playsms_tblSMSOutgoing_queue |
| playsms_tblSMSOutgoing_queue_dst |
| playsms_tblUser |
±----------------------------------------+
65 rows in set (0.01 sec)

mysql> SHOW INDEX FROM playsms_tblSMSOutgoing;
±-----------------------±-----------±----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
±-----------------------±-----------±----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| playsms_tblSMSOutgoing | 0 | PRIMARY | 1 | id | A | 103305 | NULL | NULL | | BTREE | | | YES | NULL |
| playsms_tblSMSOutgoing | 0 | smslog_id | 1 | smslog_id | A | 106574 | NULL | NULL | YES | BTREE | | | YES | NULL |
| playsms_tblSMSOutgoing | 1 | uid | 1 | uid | A | 25 | NULL | NULL | | BTREE | | | YES | NULL |
±-----------------------±-----------±----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
3 rows in set (0.02 sec)

mysql> SHOW INDEX FROM playsms_tblSMSOutgoing_queue;
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| playsms_tblSMSOutgoing_queue | 0 | PRIMARY | 1 | id | A | 90196 | NULL | NULL | | BTREE | | | YES | NULL |
| playsms_tblSMSOutgoing_queue | 0 | queue_code | 1 | queue_code | A | 78857 | NULL | NULL | | BTREE | | | YES | NULL |
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
2 rows in set (0.01 sec)

mysql> SHOW INDEX FROM playsms_tblSMSOutgoing_queue_dst;
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| playsms_tblSMSOutgoing_queue_dst | 0 | PRIMARY | 1 | id | A | 106962 | NULL | NULL | | BTREE | | | YES | NULL |
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
1 row in set (0.01 sec)

Edilson, I ask you to help solve this problem.
Thanks in advance.

Yours faithfully,
Jamshid Tursunov

Good afternoon, Anton!
Anton, please note, if it’s not difficult, that there is such a problem. Maybe fix it in the next release. I am not a database specialist and am afraid to spoil the regular work of the service.
Just in case, I will publish a similar post in Gitlab.
Thanks in advance.

Respectfully,
Jamshid Tursunov

Hy people, let me share some indexes I have created in my database

mysql> show indexes from playsms_tblSMSOutgoing;
±-----------------------±-----------±-------------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±-----------------------±-----------±-------------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| playsms_tblSMSOutgoing | 0 | PRIMARY | 1 | id | A | 20871734 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing | 0 | smslog_id | 1 | smslog_id | A | 20871734 | NULL | NULL | YES | BTREE | | |
| playsms_tblSMSOutgoing | 1 | uid | 1 | uid | A | 983 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing | 1 | idx_smsc | 1 | p_smsc | A | 20 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing | 1 | p_datetime_tz_idx | 1 | p_datetime_tz | A | 5217934 | NULL | NULL | YES | BTREE | | |
| playsms_tblSMSOutgoing | 1 | p_datetime_tz_idx | 2 | p_smsc | A | 5217934 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing | 1 | idx_flag | 1 | p_status | A | 4 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing | 1 | idx_flag | 2 | p_smsc | A | 50 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing | 1 | idx_flag | 3 | flag_deleted | A | 50 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing | 1 | idx_resumoperiodo2 | 1 | uid | A | 983 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing | 1 | idx_resumoperiodo2 | 2 | p_datetime_tz | A | 6957245 | NULL | NULL | YES | BTREE | | |
| playsms_tblSMSOutgoing | 1 | idx_resumoperiodo2 | 3 | p_status | A | 6957245 | NULL | NULL | | BTREE | | |
±-----------------------±-----------±-------------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
12 rows in set (0.01 sec)

mysql> show indexes from playsms_tblSMSOutgoing_queue;
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| playsms_tblSMSOutgoing_queue | 0 | PRIMARY | 1 | id | A | 3964456 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing_queue | 0 | queue_code | 1 | queue_code | A | 4015281 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing_queue | 1 | idx_flag | 1 | flag | A | 7 | NULL | NULL | | BTREE | | |
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
3 rows in set (0.00 sec)

mysql> show indexes from playsms_tblSMSOutgoing_queue_dst
-> ;
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| playsms_tblSMSOutgoing_queue_dst | 0 | PRIMARY | 1 | id | A | 11825903 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing_queue_dst | 1 | queue_id | 1 | queue_id | A | 3783182 | NULL | NULL | | BTREE | | |
| playsms_tblSMSOutgoing_queue_dst | 1 | idx_pid | 1 | pid | A | 6738766 | NULL | NULL | YES | BTREE | | |
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
3 rows in set (0.01 sec)

mysql>

In case of systems with high usage, is important to purge regularly above tables.

mysql> show indexes from playsms_tblSMSOutgoing;
+------------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table                  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| playsms_tblSMSOutgoing |          0 | PRIMARY   |            1 | id          | A         |      108290 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| playsms_tblSMSOutgoing |          0 | smslog_id |            1 | smslog_id   | A         |      108296 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| playsms_tblSMSOutgoing |          1 | uid       |            1 | uid         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

mysql> show indexes from playsms_tblSMSOutgoing_queue;
+------------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table                        | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| playsms_tblSMSOutgoing_queue |          0 | PRIMARY    |            1 | id          | A         |         823 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| playsms_tblSMSOutgoing_queue |          0 | queue_code |            1 | queue_code  | A         |         823 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

mysql> show indexes from playsms_tblSMSOutgoing_queue_dst
    -> ;
+----------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table                            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| playsms_tblSMSOutgoing_queue_dst |          0 | PRIMARY  |            1 | id          | A         |      109820 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

Hi, Man!

This is not the right approach, this is a crutch!
History should always be.

Regards,
Jamshid Tursunov

You must provide another ways to access history… But, if you think that there is such approach to optimize read/write access to tables with dozens of millions records, please, tell us when you find it.

Thanks for answering.
I am not a database specialist. but I look at the situation from an objective point of view. I didn’t see that in highly loaded systems, although in less loaded systems, the table was periodically cleared, all because indexing was not turned on (I don’t know the cause of the problem, I just rely on your words that you mentioned earlier in this post) or there are underperformances in the architecture of the database.

Regards,
Jamshid Tursunov

Anton, let me also draw your attention to this behavior of the system.
The database is very busy, even if idle.

Regards,
Jamshid Tursunov

try to kill playsmsd one by one, one at a time, and observe

the kill starts from dlrssmsd, then restart playsmsd then kill recvsmsd manually, restart again and so on

see which service affecting mysql

anton

Ok, thank you, will try it.

Regards,
Jamshid Tursunov

Anton, I tried to collect information on each process and together as a whole, and this is what happened:
Sequential shutdown and inclusion:


Before shutting down the processes, the CPU load varied in the range of 105-140%
Disconnected in the following sequence:
The first to disconnect ratesmsd, the CPU load dropped to around 30-50%, and this range, while watching, kept steady
The second turned off recvsmsd, the CPU load was in the range of 30-43%
The third disabled schedule, the CPU load was in the range of 30-42%
The fourth disconnected sendsmsd, the CPU load was in the range of 30-42%
Fifth disabled dlrssmsd, CPU load dropped to 0.7%, sometimes jumped to 1%


When all 5 services disconnected, the CPU load dropped to 0.7%, sometimes jumped to 1%


When I turned on dlrssmsd, the CPU load jumped right up to 30-42%
Next turned on sendsmsd, the CPU load remained in the range of 30-42%
After turning on schedule, the CPU load was in the range of 30-42%
Then turned on recvsmsd, the CPU load was in the range of 30-42%
When I turned on ratesmsd, the CPU load was kept in the range of 40-45%, but very often there are skips and the load reaches as much as 145%


Individual enable and disable: (I will enable and disable the service to calculate how much load a single process gives)
When I turned on dlrssmsd, the CPU load jumped right up to 42%
Next turned on sendsmsd, CPU load kept in the range 1.3-1.7%
After I enabled schedule, the CPU load was in the range 1.3-1.7%
Then I turned on recvsmsd, the CPU load kept in the range 1.3-1.7%
When I turned on ratesmsd, the CPU load started to jump right up to the mark above 100%

I want to emphasize that even after I turned off all the PlaySMS processes, the memory occupied by MySQL remained at 14%. I want to say that after disabling the PlaySMS process, the memory is not freed.

Respectfully,
Jamshid Tursunov

Anton, if possible, pay attention to the above MySQL behavior. Is it possible to optimize MySQL in order to reduce the load on the processor. Such bumps occur only with a simple system.

Respectfully,
Jamshid Tursunov

Anton, good day!
Did you have time to look at the unstable behavior of PlaySMS when stacking with a database?

Respectfully,
Jamshid Tursunov

Hi, sorry no, and I think its better to ask MySQL professionals/communities for that.

Looks to me its all on how to correctly index and/or optimize MySQL configs. Of course there are parts where changes can be done in playSMS to help it, but I haven’t got time to focus on that.

anton