Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


VPS specs for 5GB DB with 32,000,000 rows
New on LowEndTalk? Please Register and read our Community Rules.

VPS specs for 5GB DB with 32,000,000 rows

regmapwregmapw Member
edited September 2021 in Help

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

    Thanked by 1regmapw
  • more than 5 GB ram to cache the whole thing

    Thanked by 2yoursunny regmapw
  • jsgjsg Member, Resident Benchmarker

    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.

    Thanked by 1regmapw
  • 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).

    Thanked by 1regmapw
  • jsgjsg Member, Resident Benchmarker

    @devlopesbernardo said:
    ... 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.

    Thanked by 1regmapw
  • @regmapw said: all these queries can be cached for 30 days

    just wondering how to cache queries?

  • @JasonM said: 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.

    @AXYZE said:
    https://php-friends.de/vserver-ssd/vserver-s-ssd-g3-1y

    and cache whole database on ram

    Is there an English version of that website?

  • @regmapw said:
    Thanks guys for your insights. I will start with 4GB RAM and will increase the resources if needed.

    @AXYZE said:
    https://php-friends.de/vserver-ssd/vserver-s-ssd-g3-1y

    and cache whole database on ram

    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.

    Thanked by 2regmapw cadddr
  • @AXYZE said:

    @regmapw said:
    Thanks guys for your insights. I will start with 4GB RAM and will increase the resources if needed.

    @AXYZE said:
    https://php-friends.de/vserver-ssd/vserver-s-ssd-g3-1y

    and cache whole database on ram

    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

  • @noaman said:

    @AXYZE said:

    @regmapw said:
    Thanks guys for your insights. I will start with 4GB RAM and will increase the resources if needed.

    @AXYZE said:
    https://php-friends.de/vserver-ssd/vserver-s-ssd-g3-1y

    and cache whole database on ram

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

    Thanked by 1noaman
  • JasonMJasonM Member
    edited September 2021

    @Kassem said: 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.

    thank. I'll be looking into this and implement later :)

  • @JasonM said:

    @Kassem said: 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.

    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.

  • @regmapw said:
    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.

    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

  • dev_vpsdev_vps Member
    edited September 2021

    @AXYZE said:
    https://php-friends.de/vserver-ssd/vserver-s-ssd-g3-1y

    and cache whole database on ram

    Strongly recommended
    Excellent performance and peace of mind

Sign In or Register to comment.