New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Buffering Mysql Write into Redis
So, a fellow LET have given me an idea how to futher optimize my apps. My apps, is heavy on mysql write and read. I can use redis easily on mysql read, but how to write ?
Should I just spawn a dedicated node apps for processing the queue ? check every minute if there is a queue write query, write it to the database until there is none in the queue ? is that the right way to do it ? I am afraid when the nodejs can't keep up with the queue, the apps will just out of sync.
Comments
There's no perfect way in write cache.
If the cache success, and write to the database error, it will be a mess.
If two people write the same database, also will casue problem.
I would stick to standard methods where possible, e.g:
Actually read cache is no problem. Already did it. Page request and stuff like that has been served directly from redis. So read load is almost nonexistent
It's write sql where the students submit their answers is the problem. So yeah, it needs to be a heavy write. Their is just no around that.
My initial though put the queue in redis and process that queue in the background using another process. I am the developer, and I want to squeeze every last of possible optimization.
What sort of TPS are you seeing?
A query like:
Should execute at crazy high TPS on any decent hardware.
Regardless of if it's executed in the request context or the backend the performance you get for a query like that will be pretty much the same. Most of your load with a query like that will be in the maintaining ACID compliance (assuming innodb or postgresql).
I'd make sure you are minimizing transactions (either bulk inserts or multiple inserts in a tight loop within a transaction). Using a sensible database backend (Innodb or Postgresql) if using a RDBMS. And when you get to 50-100k+ TPS or if your answers table is very big consider partitioning. Look for any design issues (excess indexes, etc) or configuration issues (e.g insufficient innodb memory) that might be lowering your TPS on insert.
What's a TPS ?
times it with 10k req per sec.
Yes. that's pretty much it. But instead of answers (plural), it's answer (singular). Because the nature of the test, the school / teacher want to see the result in realtime (although delay few seconds should go unnoticed) . also in case the students device blowing up, and the students need to change his device, he should start where he left off.
I just got an idea. I could write the students answers into redis, and then save it into the database when the students finish their test. When the teacher / students, want to retrieve the answer mid-test, i could serve it from the redis instead of the database. a Concern about this method is memory footprint. Currently the mysql database is about 3GB. Does that mean the Redis would be about the same size ? and I need to allocate that much RAM or Bigger / Smaller ?
That sounds sensible to me. Temporarily save in memory assuming your server infra uptime is great. As for memory, I dont see how your db size which I assume is holding your saved data is related directly with the memory storing current answers from users before write. I think you can tweak mysql in memory housing to perhaps get the balance of memory right. Also remember you temp store data into memory and then clear that mem when you store to the db but so consider the possibility of expiring the in mem data if no save occurs possibly.
I just got an idea. I could write the students answers into redis, and then save it into the database when the students finish their test. When the teacher / students, want to retrieve the answer mid-test, i could serve it from the redis instead of the database
When you don't understand your RDBMS, you end up trying to invent a 'faster' DBms that loses data.
Some good suggestions above.
If eventual consistency is no problem to you, use a write queue. But that also means you won't be able to "read your own write". Sharding sounds feasible I guess. Either way you have to scale out your cluster to avoid a choke. Honestly, delaying a write to a DB through queueing is like a cheap trick to me.
Also ensure it's not premature optimization. I can see no numbers, stats, and requirements in your post.
Furthermore, I'm not so far aware of any database, with client-server model, that's not designed to handle any kind of load. Underestimation looms right there.
Transactions per Second (one unit of performance for write operations on a database)
mysql has a query cache I wouldn't double cache unless you have evidence supporting it being necessary.
10k TPS is not that high for this sort of workload, you should be able to handle that. Are you perhaps thrashing the table with reads? Are you using innodb? Are you on a SSD?
Is your problem performance, or lock contention? Are you IO bound or CPU bound?
Generally the process to follow:
1. Find the bottleneck
2. Theorize a solution
3. Measure the solutions performance (against synthetic data)
4. Test the solutions performance against realistic patterns
5. If successful implement.
Over engineering? Just splice memcache and continue. Or just add NVMe and more RAM.
Did you already read the disk I/O optimization guide of the MySQL documentation? Maybe there is an easier way you can still optimize you setup. Database systems are already heavily optimized; trying to add „your own flavor“ too eagerly may just increase your complexity significantly with barely any gain achieved at the same time. For example, RAM-based caches are already built-in to MySQL. If you still add something, make sure to carefully weight the benefit of performance gain versus the complexity increase, or it will bite you later on when you have to maintain that thing.
also
should be looking HOW database caching work in wordpress
Yes to all. At first too many read into mysql, that I keep hitting100% load of my dedicated server. Mind you this is my backend only server. The frontend is served from another server.
After using redis for the read, I can cut the load into 40%. Now, I want to cut it even more (perhaps by queue write) so I can merge the 2 server into 1 server. And in the near future, I am going to launch the free version of my apps, and expected the TPS increase twice or threece as much.
Granted the server is an old Dual E5620, and I plan to move into hetzer AX41. but still.
I hope you guys understand my concern.
Yeah, Cheap is why I came to LET. I prefer not to vertical scaling if possible. I can promote my apps to be lightweight. Hey my apps, can handle 10k students with just an i5 for example. The client doesn't really give a shit about what's the logic behind it. As complicated as it would be. Also I can say my apps, way lighter than the competitor.
Also more complicated mean less my adversaries can replicate the way it works. Because some people just stole my code, and resell them as their own on various local marketplace like tokopedia, shopee, bukalapak, etc.
This has nothing to do wordpress. It's not even php.
Yikes, do you even use SSDs then? Just give the AX41 a try and save yourself all the trouble. Time is money too.
This my number
When a user logged in (e.g. for 90 minutes) and doing the test, they will constantly sending the data / answer to the server. Peak at 8 - 9 AM, about 20k user logged in, meaning the TPS will be up to 20K, although it probably lower at 10K or so.
Thank you all for the input.
It's an SSD. Because it's a backend server, by nature, it doesn't need much space.
Ah ok, was just a guess as this is a ten year old CPU and SSDs where not the default back in 2010. Replacing an HDD with an SSD would have been a pretty easy way to gain quite some improvement.
^^^
Yes. You probably don't gain much from doubling up. Adjust cache size, flushing algorithm, and application logic.
That's not what TPS means. You do understand that right?
Transactions per Second is not the number of users logged in.
Caching writes to memory-based db is like begging for serious problems...
Yes, but when they logged in and doing activity (clicking the button / answering the question), they will send an insert query to the server.
So it's safe to assume 20k user logged in will at least generate 20k tps on the first 30sec, and 10K tps for the first few minutes of them logged in. It's not really 20k constant tps, that's why I used the term UP TO.
Is this activate automatically or do I need to set things up. Because when I haven't used redis for caching the query result, my server keep maxing out the load. After I used redis it come down to just 40%
Don't go for the AX41 server, it has consumer SSDs. Go for PX line or AX61 at least. These have enterprise NMVEs.
And install a monitoring tool to see the load.
But the cpu benchmark is lower, than AX41.
Then go for the AX61, those consumer SSDs have a low lifetime with a write load. I have a 20k TPS mysql server and after 6 months the lifetime of the SSD was only 50%.
As you're the developer, best to setup a test server instance - a good hourly billed VPS provider would do and try out your ideas and measure your resource usage. Check out Upcloud's New York datacenter it's purely AMD EPYC 7542 based and performance is nice - did Wordpress Cache-Enabler install/config/benchmark write up on 1cpu US$5/month instance and it performed very nicely with 1000-5000 concurrent users for Wordpress via loader.io https://servermanager.guide/203/wordpress-cache-enabler-advanced-full-page-caching-guide/
Also are you able to generate and simulate test loads ? That would be helpful in testing too.
Redis isn't purely memory based it has various levels of disk persistence https://redis.io/topics/persistence to back it and you can setup Redis clusters or master/slaves replication https://redis.io/topics/replication and with Redis Sentinels to handle failover https://redis.io/topics/sentinel.
But I agree with @SplitIce first figure out the exact bottleneck(s) https://www.lowendtalk.com/discussion/comment/3134473/#Comment_3134473 before you can look at solutions.
I find when you're constrained by server hardware/resources, it's the perfect time to truly understand and profile your web app's usage loads through tweaking and fine tuning all relevant settings that impact performance and scalability. It's one thing I've found since finding LET and playing with low end VPSes in optimising my LEMP stack configurations. You learn more than if you over spec'd on server hardware way beyond what you require
from my graphs, the server busy just for a few hours. after that, it's practically idling. Also some months (e.g. holiday season), it's just idling. I think consumer SSD is enough for that. I don't think I need to go overspec with AX61.
It's not technically bottleneck yet, because my server is still not maxed out. It's more like "do more with less"
First step given your current hardware, is get a better server hardware for similar price and/or benchmark load test your alternative servers and get a performance per $$$ calculation to work out the sweet spot for certain server/hardware choices. Your Xeon E56xx are really ancient! This exercise will serve you in future capacity planning too if you anticipate your usage/load to grow
For example if you find a newly spec'd VPS server outperforming your ancient Xeon E56xx server, then you maybe get better bang for your buck in terms of performance to price ratio.
The thing is, most vps provider doesn't allow sustained load. The one who allow such as buyvm cost a lot, to the point it's more sensible to rent dedicated instead. The most bang to the buck allowed sustained load vps AFAIK is netcup.
Also another probelm, because it's vps, I can't just google the benchmark of those vps. With dedicated, I can just create a spreadsheet, and compare the price / performance ratio.
Yeah depends on VPS provider. I haven't had problems with load testing on VPS from Upcloud and Linode. And Linode, DigitalOcean have dedicated cpu instance VPSes which may help. And the point isn't pushing the VPS to the max but understanding through monitoring, how incremental load impacts and affects performance and resource usage metrics. It's all relative. So if you test at 100 users and VPS allows you to handle 10000 req/s and your E5620 does 100 users with 2000 req/s and at 200, 500, 1000 users and so forth. You don't need to push it to the max for relative testing.
As I said in other thread, for dedicated for testing a few hours only at a time Vultr bare metal and Packet.net are good for such. I've load tested my Centmin Mod Nginx server to 80,000 to 350,000 concurrent connections on Vultr's Xeon E3-1270v5/v6 32/64GB bare metal dedicated for a few days testing in the past and only really was limited by their 10Gbps network speeds
If you're limited by network speeds, Packet.net has 10-40Gbps and Linode has 1-12Gbps depending on plans. Yes more expensive if you held them for a full month, but for a few hours or days testing is relatively cheaper. Especially on Packet.net as they have spot pricing discounts similar to Amazon EC2. So a AMD EPYC 7402P dedicated with spot pricing was around 30-40% the advertised hourly rate and I managed to do a few days testing. The beauty of hourly billing ^_^
It does not change the fact, redis is in-memory database engine. And despite all that replication/persistence you can still loose data in case of sudden power outage.
Don't get me wrong: redis is excellent software. I'm using it for file-locking (which is much faster with redis, than with mysql). But is is not designed to be something like "journaling-frontend" for any other db.