Question 121432 in Redis Question posted in
The official documentation can be found here.

Redis – How to improve spring boot and mysql single insert statement performance which is called many times

I have single insert statement

insert into mq_message (id, message, type, ..) values (...)

This statement is called in spring boot chat application each time message sent to chat channel.
We already have many many chat channels and huge number of users.

How to improve its performance or is there any method so that spring data can batch these insert queries ??

Appreciate your help.

Updated — adding create statement for the table:

CREATE TABLE `mq_message` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `room_id` varchar(32) NOT NULL COMMENT 'Room ID',
      `message_no` int(10) unsigned NOT NULL COMMENT 'Message No',
      `previous_message_no` int(10) unsigned NOT NULL COMMENT 'Previous Message No',
      `post_user_id` varchar(32) NOT NULL COMMENT 'Post User ID',
      `json_body` json NOT NULL COMMENT 'Json Body',
      `is_deleted` tinyint(1) unsigned NOT NULL COMMENT 'is deleted',
      `is_isolated` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Isolation flag',
      `create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'Create Time',
      `update_time` timestamp(3) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(3) COMMENT 'Update Time',
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx_char_room_id_message_no` (`chat_room_id`,`message_no`),
      KEY `idx_create_time` (`create_time`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Tags:

2

Answers


  1. 0

    Try to use the transaction (begin insert query and commit)
    Refer the scenario 3 of the given link

    Insert Query Optimization

    Login or Signup to reply.
  2. 0

    Let’s see SHOW CREATE TABLE mq_message — Getting rid of some redundant indexes will help a little.

    Well…

    No indexes seem redundant.

    In some situations with

    PRIMARY KEY(id), UNIQUE(...)

    getting rid of id and promoting ... to be the PK would eliminate some extra effort. However, it would require changes to any JOINs that link to this table, and may lead to other issues.

    It seems unlikely that any query will use INDEX(create_time). Possibly it could/should be replaced by INDEX(post_user_id, create_time) if the typical query is like

    WHERE user_id = ...
ORDER BY create_time DESC

    That may help such a query, but won’t help the INSERTs we are talking about.

    You say that the single-row inserts are a performance problem. How many such rows are inserted per second? Do they come in bursts? I would expect 100 per second not to be a problem.

    I see varchar(32). Are they UUIDs? If so, do you use MySQL to generate them? Or some other tool? Show us one example of such; I want to see if it is "type-1", in which case, there may be a technique to help: UUIDs .

    You have shown us the INSERT for this table; do you also insert a fresh message at the same time? And any other rows? If there are multiple rows into separate tables, then BEGIN...COMMIT (or spring equivalent) is strongly advised.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search