-2

This is not a question about capacity planning, its about mysql performance over local network vs on the same virtual machine (where it needs to share cpu with other processes)

I have a wordpress+woocommerce site hosted on a VPS with 2 cores, 4gb of ram and local nvme storage, configured with ubuntu 18.04, nginx, php7.3 and percona mysql 8. Hosted on Hetzner in Germany.

Lately traffic on my site grew and so did my cpu usage. mainly by mysql ( roughly 2/3 of the cpu is used by mysql and the other 1/3 by php-fpm). Sometimes when i have many people visiting my site at the same time it takes a lot of time to load because my cpu is at 100% for many seconds

htop screenshot of my server tipical load

I'm now facing a decision to make: buy another VPS like this and move mysql onto it or upgrade this VPS and double its specs?

Either way it will cost me the same, certainly having 2 separate machines means disaster recovery would be easier/faster, if one of the servers crashes I would have to recover only half of the services to put the site back online and it could also be easier to setup a load balanced system in future.

But how much will it cost in terms of TTFB having to contact another machine on the private network everytime wordpress needs to query the database? Or might my load times improve because I'm dedicating 2 cores to php and 2 cores to mysql so they can work more in parallel on the same request with less queueing?

My priority is to make my site's TTFB as short as possible, without php cache because I can't use it for technical reasons right now.

Nocte 1337
  • 1
  • 1
  • 5
    Does this answer your question? [Can you help me with my capacity planning?](https://serverfault.com/questions/384686/can-you-help-me-with-my-capacity-planning) – Dennis Nolte Feb 18 '20 at 10:19
  • Additional information request. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions and expedite your query completions. – Wilson Hauck Mar 07 '20 at 16:53

1 Answers1

0

Generally communication with a database server over local network is really fast. It probably won't be the bottleneck. Depending on the provider, your servers could even be on the same machine. You will need to measure timings in your environment to get accurate data to make this decision for your setup.

Jonas Äppelgran
  • 51
  • 2