1

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).

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Cliff Anger
  • 195
  • 2
  • 11

1 Answers1

1

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).

user1597430
  • 1,138
  • 1
  • 7
  • 14
  • Since i have separate servers and want to get better performance in each one, i used a REDIS server alone, and my website in another one ... is there a way to achieve this using REDIS + File in the same time ? If yes, any hint for this ? Thank you in advance. – Cliff Anger Jun 11 '19 at 16:34
  • @Cliff Anger, so you physically separated logger (Redis) and your website which generates a lot of log events? Or did you mean something else when said "server"? – user1597430 Jun 11 '19 at 16:53
  • I separated REDIS from the website server (Like you mentionned it). – Cliff Anger Jun 11 '19 at 19:53
  • @Cliff Anger, I said "you don't need REDIS". I probably don't understand something. Why do you use MySQL and Redis in the same time? Is it necessary for website or is it your own idea to make something faster? As far as I understand, you have PHP+MySQL website and Redis is not required at all. Correct me if I am wrong. – user1597430 Jun 11 '19 at 20:34
  • I am using REDIS to store infos for a short time since it's very faster than MySQL (MySQL have low performance alone when there too mutch datas to save at once, especially when there many concurrent users per second - Since REDIS store data in-memory you can understand that is far faster than MySQL), REDIS is a must for my project since i am doing other thing also with it ... but i have no idea on how to save temporary MySQL requests on REDIS and then call them using LOAD DATA INFILE for best performances. – Cliff Anger Jun 11 '19 at 21:30
  • 1
    @Cliff Anger, yeah and I suggested to exclude Redis from this task. Try to use simple files for logging (like Nginx do it). And use a cron task with LOAD DATA INFILE query to insert data into the MySQL as fast as possible. It should be faster and cheaper than 2 DBMS. If you need samples or clarifications - you can open a chat room, leave a link here and I'll describe it properly. – user1597430 Jun 11 '19 at 22:32
  • Thank you for your help, but REDIS is a requirement for my case since i need it for other tasks to manage multiple servers and share infos between them, i need a way to store MySQL queries on REDIS and then a possibility to load them on MySQL as it is a file for better performance on loading the queries. – Cliff Anger Jun 11 '19 at 22:53
  • @Cliff Anger, find a way to export data from Redis into CSV file and use a query from this answer https://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile. I believe, you'll be surprised how fast it works. Don't forget to raise your `max_allowed_packet` in MySQL settings, it is a file size limit for LOAD DATA INFILE. But to be honest, you don't need Redis here - you can write a CSV file directly from your website (fopen + 'a' attribute flag) – user1597430 Jun 11 '19 at 23:08
  • Exporting datas to CSV and then export back to MySQL will take too much time no ? Thank's – Cliff Anger Jun 11 '19 at 23:11
  • @Cliff Anger, please read the last version of my comment above. LOAD DATA INFILE - is a direct command for MySQL to insert data "as is". It skips all possible SQL mechanisms, validations and layers because SQL queries are expensive in comparison with this way. When you import any dump.sql from a command line - you also use LOAD DATA INFILE. It's the fastest way. What I suggest is - forget about Redis, create a simple file, append data line by line in a CSV manner and feed the result with a help of LOAD DATA INFILE. Test it first. You'll discover that you don't need Redis here. – user1597430 Jun 11 '19 at 23:18
  • You are right sir, i think i will use your method for MySQL queries and REDIS for other thing ... many thank's for your help - Really appreciated :) Sorry for your time. – Cliff Anger Jun 11 '19 at 23:30