Shulou(Shulou.com)05/31 Report--

How to efficiently import Mysql millions of magnitude data into Redis, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

With the operation of the system, the amount of data becomes larger and larger, and simply storing the data in mysql can no longer meet the query requirements. At this time, we introduce Redis as the cache layer of the query, save the hot data in the business to Redis, expand the service capability of traditional relational databases, and users can quickly obtain common data from Redis directly through applications, or use Redis to save active users' sessions in interactive applications. Can greatly reduce the load of the back-end relational database and improve the user experience. The disadvantages of traditional commands using traditional redis client commands are as follows: because redis is a single-threaded model, although it avoids the time required for thread switching under multi-threads, and the execution of commands in a single order is very fast, but in the scenario of mass data import, the time taken to send commands and the time it takes to receive server response results will be magnified. If you need to import 1 million pieces of data, it will cost 1 million * (T1 + T2) for the command execution time alone. In addition to sending commands one by one, of course, the redis design will also consider this issue, so the pipelining pipeline pattern has emerged.

But pipelining is not available on the command line, so we need to write new processing code to receive batch responses. But only a few client-side code supports it, such as the extension of php-redis that does not support asynchrony. Pipelining pipeline mode actually reduces the interaction time of TCP connections, when a batch of commands are executed, the results are sent at one time. Its implementation principle is to use FIFO (first-in, first-out) queue to ensure the sequence of data. Only a small number of clients support non-blocking I pact O, and not all clients are able to parse responses in an efficient way to maximize throughput. For these reasons, the preferred way to import large amounts of data into Redis is to generate a format containing Redis protocol data and send it in batches. Data import Redis warm-up nc command import data nc is the abbreviation of netcat, the functions of nc are: 1, to achieve the listening of any TCP/UDP port, after adding the-l parameter, nc can be used as server to listen for the scan of designated port 2 and port in TCP or UDP mode, nc can be used as client to initiate TCP or UDP connection 3, file transfer between machines 4, network speed measurement between machines uses pipe mode to import data, however Using nc snooping is not a very reliable way to perform large-scale data imports, because netcat does not really know when all the data is transferred and cannot check for errors. In Redis version 2.6 or later, the Redis-cli script supports a new pattern called the pipe pipeline pattern, which is designed to perform large-scale inserts.

From the figure above, you can see the return result of the pipe command. The number of replies returned is the number of lines of commands in the txt file, and errors represents the number of commands in which the error was executed.

Redis protocol learning

The format of the agreement is:

*\ r\ n

$\ r\ n

\ r\ n

...

$\ r\ n

\ r\ n

For example: insert a piece of data of type hash According to the Redis protocol, HSET id book1 book_description1 has a total of 4 parts, so it begins with * 4, and the rest is explained as follows:

Note that the HSET command itself is sent as one of the parameters of the protocol. The protocol data structure constructed is: * 4\ r\ nroom4\ r\ nHSET\ r\ nroom2\ r\ nid\ r\ nroom5\ r\ nbook1\ r\ nroom17\ r\ nbook_description1\ r\ n

Format it:

* 4\ r\ n

$4\ r\ n

HSET\ r\ n

$2\ r\ n

Idvvvv\ r\ n

$5\ r\ n

Book1\ r\ n

$17\ r\ n

Book_description1\ r\ n

The RESP protocol bulkRedis client communicates with the Redis server using a protocol called RESP (Redis Serialization Protocol). Redis-cli pipe mode needs to be as fast as the nc command, and solves the problem that the nc command does not know when the command ends. While sending the data, it also reads the response and tries to parse it. Once no more data is read in the input stream, it will send a special 20-bit echo command indicating that the last command has been sent. If the same data is matched in the response result, the batch delivery is successful.

Using this technique, we don't need to parse the protocol sent to the server to see how many commands we sent, just parse the response. When parsing the reply, redis makes a count of the parsed reply, and in the end can tell the user the number of commands transmitted to the server by a large number of insert sessions. This is the result of the actual operation we did using pipe mode above. Replace the input data source with mysql. In the above example, we use an txt text as the input data source and use the pipe schema to import the data. Based on the study and understanding of the above protocol, we only need to import the data from mysql into Redis through pipe schema according to the established protocol. Actual case-importing million-level data from Mysql to Redis first creates data due to environmental limitations, so there is no real data to import here, so let's first use a stored procedure to create 1 million pieces of data. The stored procedure is used as follows: DELIMITER $$

USE `cb_ mon` $$

DROP PROCEDURE IF EXISTS `test_ insert` $$

CREATE DEFINER= `root` @ `% `PROCEDURE `test_ insert` ()

BEGIN

DECLARE i INT DEFAULT 1

WHILE i

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.