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.
VPS specs for 5GB DB with 32,000,000 rows
Hi, I have a 5GB MySQL table with 32 million rows. I expect 5 million MySQL requests every month (I think 50000 per second will be max, normally 2000/s).
Select queries will use index columns only, and all these queries can be cached for 30 days.
What VPS specs do you guys recommend?
Thanks in advance.
Comments
50k/s it’s a lot of query’s
build a cluster using couple of vps or add another layer for caching the DB
4 GB memory
2 vCPU
SSD
will be a good starting point
more than 5 GB ram to cache the whole thing
The DB size and the number of rows isn't a problem (at all). The number of req/s is.
I'd go for an 26xx v4 or Epyc (or Ryzen) based VPS with min. 2 vCores, min 4GB memory - the more the better (because it's one of the most decisive factors) and a good NVMe.
Plus I'd be sure to optimize the DB server and use a good application cache.
Note: If you have plenty memory a decent SSD will do fine.
If in doubt compromise on the CPU, not on the memory.
Create indexes in the DB. Make sure that my.cnf makes sense - (e.g, try some configs of mysqltuner).
I'd suggest 4-8GB of ram (the more, the better), a SSD or NVME and a provider who will not destroy your I/O (as Contabo does, at least per my experience).
As that comes up again: I have purchased a Contabo NVMe VPS and yes, the results are worse than in my benchmarks on an (almost) empty node. No surprise there nor in any way Contabo specific.
Of course I've seen better VPS - but they usually also cost more (modulo rare insane promos).
I'm wondering why some people hate Contabo so much. One will, except for very rare cases, not get a consistently great, low spread VPS for small money. All VPS providers live in a space where resources are partitioned and shared. What one should aim for (as a customer) is the right ratio for a given use case and within a given budget. Contabo has a certain position in the spectrum between very cheap and quite sh_tty - expensive and really great. For many use cases that position is just about right and for many it is not, simple as that.
A propos 'use case'. This thread is about a DB focused server with relatively high number of req/s, with the latter being the decisive factor, not the DB. Disk IO is not a major concern here.
https://php-friends.de/vserver-ssd/vserver-s-ssd-g3-1y
and cache whole database on ram
just wondering how to cache queries?
By saving query results in a memory store (Redis or Memcached or other) and only hitting db server if the memory store doesn't have the answers.
Example with Redis and PHP and mySQL but the same can be applied with other choices:
https://www.digitalocean.com/community/tutorials/how-to-set-up-redis-as-a-cache-for-mysql-with-php-on-ubuntu-20-04
Thanks guys for your insights. I will start with 4GB RAM and will increase the resources if needed.
Is there an English version of that website?
Page is only in German, control panel is in english & german so dont worry. They dont have flashy multilanguage site or big marketing, but they have quality servers at low price. No overselling, helpful support which goes beyond what you normally see. Highly recommend, I transfer much data so I prefer netcup (120TB) or Hetzner (20TB), but if 5TB is enough for you then go for PHP-Friends. There's no better offer for database at this price. Real 10GB of ram, NVMe storage, dedicated threads that you can use 24/7 and they wont throttle you.
A little offtopic
Could you tell me where are you seeing 120 TB on netcup? I can only see 40 TB. An order link would be much appreciated
Every Root-Server comes with 120 TB per month.
(Go to https://www.netcup.eu/vserver/#root-server-details and place your cursor over "i" to the right of "Traffic flatrate".)
thank. I'll be looking into this and implement later
Could you tell us more about the application layer?
PHP
NodeJs
What framework are you using?
I can suggest couple of things for Laravel.
I am doing something similar for machine learning algorithms where data is logged in a PostgreSQL database
My VPS configuration
2 dedicated core vCPU
16gb RAM
80 gb NVME disk space
Consider using memory based tables to hold partial data in memory that can be written to physical disk asynchronously
cost - I am paying less than $30 per quarter
Strongly recommended
Excellent performance and peace of mind