Why does MySQL query on VPS take orders of magnitude longer?

Question

MySQL

1. There is a virtual machine with Ubuntu Server 18.04 installed in Virtualbox on the developer's local machine. Limits are set: 1 processor core with a maximum of 50% load and 512MB of RAM. (To emulate limited resources on VPS)

2. There is a VPS server (OpenVZ) with 1 core and 1024MB of memory with Ubuntu Server 16.04 installed

3. MySQL database is installed there and there, and the data is synchronized. It has two tables table1 (~275k records) and table2 (~130k records)

4. Same query:
SELECT * FROM `table1` LEFT JOIN `table2` ON `table1`.`id`=` table2`.`id` WHERE `uid`=1
on dev machine runs 0.0002 sec fetching 922 rows without problems,
and on the VPS server it works for 10 minutes, MySQL uses all available memory and 100% CPU load and then the process crashes (possibly beaten by the hoster as a heavily loaded server).

5. Where to dig, why can there be such a difference?

Answer 1

Both there and there the MySQL database is installed, and the data is synchronized

1) compare MySQL configs
2) make sure that not only the data, but also the indexes are the same
3) you can check disk performance, for example https://www.cyberciti.biz/faq/howto-linux-unix-tes...
in general one of two things - either the MySQL settings \ indexes are different, or the hoster is blatantly lying

Answer 2

everything listed is rubbish. What processors? How much memory is there is not so important. What disks? Maybe there is magnetic? What is the base configuration?
Everything needs to be compared

Answer 3

I categorically do not recommend OpenVZ, the CPU quota does not work normally there + the hoster can greatly oversell. I advise you to take VPS on KVM.

Answer 4

Take not the cheapest good VPS and everything will be fine if you are in the Russian Federation - you can take Xelent

Answer 5

bugsoff, I think the hoster is hiding something, it's time to change the vps,
you can try the xelent