MySQL vs Redis – Configuring MySQL as ACI

MySQLredis

i have an use case in my application where i need real time communication between users based on their position. Basically one user will create an "event" and it will be sent to near users. On the server side i'm planning use redis or mysql to manage events and participants. Based on questions like this one i could configure mysql with:

innodb_flush_log_at_trx_commit = 2

and it increases write performance. Do you think that properly configured MySQL could compete with Redis?, what i like of MySQL is that i can use an ORM in front of it. Or maybe i'm missing something about the difference between Redis and MySQL.

Thanks in advance.

Best Answer

There are too many differences to enumerate. The choice between them is heavily based on your application, which you have not described.

That setting affects writes, not reads.

If you are not exceeding 100 queries per second on slow spinning drives, MySQL will do just fine. Or, if it does not, then we should look at your indexes and queries.

Related Solutions

Mysql – Why is it slower to access records at the end of the database

This is because you're asking it to skip the first 150000 records. It has to read through all of them first before it gets to the 5 that you actually want:

EXPLAIN SELECT id,category_id,sha FROM incidents LIMIT 5 OFFSET 150000;

If you put an ORDER BY clause then it's possible that it could jump to end of the index (if it knows how many there are in it) or at least do an index skip scan to jump through it faster (vs sequential scan on the table rows). Either way though there would be more work involved then just getting the first couple records.

To do something like this efficiently you should specify an exact range that you want to query rather than just saying "#150000 - #150005":

SELECT *
FROM my_table
WHERE id BETWEEN 100000 AND 100005

That way the query plan can use an index to jump to that specific sub section of the table immediately.

How to Update Multiple Rows in a Large MySQL Table

SHOW ENGINE INNODB STATUS\G and see https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded increasing the lock timeout period could solve the issue. Apparently default is 50seconds. Which is maybe enough for all of the rows with userid=99, but not enough for rows with 99,22,88 or 7 userid. Just speculation.

Best Answer

Related Solutions

Mysql – Why is it slower to access records at the end of the database

How to Update Multiple Rows in a Large MySQL Table

Related Question