CODEWITHSUNDEEP
phpmysqlsortingredisphpredis
Marin Marinov
Marin Marinov

Reputation: 11

Can redis improve my MySQL performance and how to store data in redis similar to a DB table?

I have the following web site:

The user inputs some data and based on it the server generates a lot of results, that need to be displayed back to the user. I am calculating the data with php, storing it in a MySQL DB and display it in Datatables with server side processing. The data needs to be saved for a limited time - on every whole hour the whole table with it is DROPPED and re-created.

The maximum observed load is: 7000 sessions/users per day, with max of 400 users at a single time. Every hour we have over 50 milion records inserted in the main table. We are using a Dedicated server with Intel i7 and 24GB ram, HDD disk.

The problem is that when more people (>100 at a time) use the site, the MySQL cannot handle the load and MySQL + hard disk become the bottleneck. The user has to wait minutes even for a few thousand results. The disk is HDD and for now there is not an option to put SSD.

The QUESTION(S):

  1. Can replacing MySQL with Redis improve the performance and how much?

  2. How to store the produced data in redis, so i can retrieve it for 1 user and sort it by any of the values and filter it?

I have the following data in php

$user_data = array (
   array("id"=>1, "session"="3124", "set"=>"set1", "int1"=>1, "int2"=>11, "int3"=>111, "int4"=>1111),
   array("id"=>2, "session"="1287", "set"=>"set2", "int1"=>2, "int2"=>22, "int3"=>222, "int4"=>2222)...
)

$user_data can be an array with length from 1 to 1-2milion (I am calculating it and inserting in the DB in chunks of 10000)

I need to store in redis data for at least 400 such users and be able to retrieve data for particular user in chunks of 10/20 for the pagination. I also need to be able to sort by any of the fields set (string), int1, int2... (i have around 22 int fields) and also filter by any of the integer fields ( similar to sql WHERE clause 9000 < int4 < 100000 ).

  1. Also can redis make something similar to SQLs WHERE set LIKE '%value%'?

Upvotes: 0

Views: 2742

Answers (2)

WillS
WillS

Reputation: 362

1. Can replacing MySQL with Redis improve the performance and how much?

Yes, Redis can improve your basic read/write performance due to the fact that it stores the information directly in memory. This post describes a performance increase by a factor of 3, but the post is dated in 2009 so the numbers may have changed since.

However, this performance gain is only relevant as long as you have enough memory. Once you exceed the allotted amount of memory, your server will start swapping to disk, drastically reducing Redis performance.

Another thing to keep in mind is that information stored in Redis is not guaranteed to be persistent by default--the data set is only stored every 60 seconds or if at least 1000 keys change. Other changes will be lost on a server restart or power loss.

2. How to store the produced data in redis, so i can retrieve it for 1 user and sort it by any of the values and filter it?

Redis data store and has a different approach from traditional relational databases. It does not offer complex sorting, but basic sorting can be done through sorted sets and the SORT command. That will have to be done by the PHP server.

Redis does have any searching support--it will have to be implemented by your PHP server.

3. Conclusion

In my opinion, the best way to handle what you are asking is to use a Redis server for caching and the MySQL server for storing information that you need to be persistent (if you don't have any information that has to be persistent, you can just have the Redis server).

You said that

The data needs to be saved for a limited time - on every whole hour the whole table with it is DROPPED and re-created.

which is perfect for Redis. Redis supports a TTL through the EXPIRE command on keys, which automatically deletes a key after a set amount of time. This way you don't need to drop and re-create any tables--Redis does it for you.

Upvotes: 0

Fernando
Fernando

Reputation: 2189

Probably Redis is a good fit for you problem, if you can hold all your data in memory. But you must re-think your data structure. Redis is very different than a relational database, and there is no direct migration.

As for you questions.

  1. Probably it can help with performance. How much, it will depends on your use-case and data structure. Your constraint will not be hard-disk anymore, but maybe something else.

  2. Redis have no concept similar to ORDER BY, or WHERE as SQL. You will be responsible to maintain your indices and filters.

I would create a HSET for every "record" and then, use several ZSET to create indexes of that records. (if you really need to order on any field, then you'll need one ZSET per field)

As for filters, the ZSET used for indexes, will probably be useful to filter ranges of int values.

Unfortunately for LIKE query, I really don't have a answer. When I need advanced search capabilities, I usually use ElasticSearch (in combination with redis and/or mysql)

Upvotes: 1

Related Questions