CODEWITHSUNDEEP
phpmysqlredis
Cliff Anger
Cliff Anger

Reputation: 195

How to combine REDIS and MySQL for mass INSERT to get better performances (Real Time)

I have to INSERT around 1000 rows each second to MySQL database, since for each visitor i have to add the IP, Browser and Country to a specific Table for a statistics need.

Inserting the visits row by row take many performances from the server and many timeout encountered, in order to minimize this problem and get better performances, i want to combine REDIS + MySQL.

To reach my goal, i have to store each visit into REDIS accompaned with the exact date including seconds in a JSON format, and then from a PHP script i want to fetch that records and insert them to MySQL database using CRONJOB (Each second in order to make it REALTIME for users).

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

Inserting datas in one query take a very short time and will save many performance in my server.

So my exact problem is:

To be able to add a new entry into REDIS for each visit to an actual date (This mean add all visits infos to this date second) in a JSON FORMAT if possible or not, and then using a CRONJOB php script, i can fetch all the data in real time and add them to MySQL database (For each added row to MySQL database will be removed from REDIS to prevent duplicate).

Upvotes: 1

Views: 1561

Answers (1)

user1597430
user1597430

Reputation: 1146

You don't need REDIS and JSON in this scheme. All you need is to open a file for append (POSIX states atomic access to the file for append mode) and write your data line by line. You also should create a cron task and import data to MySQL with "LOAD DATA ... INFILE" syntax which is faster than any SQL command with INSERT keyword.

Hint 1: you can open this file directly in the memory to increase the performance.

Hint 2: you can write your data in binary format (pack numbers as int32/int64 values instead of strings - check pack/unpack man pages to understand what I mean).

Upvotes: 1

Related Questions