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
Hello dear Anton!
I have to bother you again, your help is needed in solving this issue 
If you pay attention, I tried to describe the problem in detail within each service separately.
I really need your help in solving this issue, apparently, except for you, no one will figure this out
Respectfully,
Jamshid Tursunov
whats your server’s specification (cpu, ram, is disk ssd or not) ?
have you try to tweak MySQL config ?
have you try to make index, test it, perhaps ask MySQL pro to help you how to fix it (or follow some index pointed by @Edilson_Spessoto on previous posts
how big your data ? how many estimated rows on your tables ? (just estimation, no need exact number, like above a million, above 1 hundred thousand rows… something like this)
anton