0

I currently have a cloud server with 2 CPU's and 2GB RAM, Ive noticed that MySQL performs SELECT queries quite slowly on a table size greater than 250,000 rows unless the queries are cached. The table has all the correct indexes and my queries are optimized.

I have run the same MySQL setup on my personal computer and the queries are much faster. So I am thinking its more to do with the server itself rather than the MySQL queries or table setup.

I am considering upgrading the cloud server to 4GB Ram and 4CPU's or switching to a dedicated server with the same spec.

Will changing from a cloud server to a dedicated server improve MySQL performance?

asdf1234
  • 127
  • 4
  • 10
  • Depends on your cloud server, workloads, what "slow" means etc. – Sami Kuhmonen Mar 16 '16 at 04:40
  • If the query has not been cached, the cloud server can take 30-70 seconds to perform a select query for large results, the same query ran on my personal computer has so far taken a maximum of 12 seconds - this was run on a table size of 650,000 rows – asdf1234 Mar 16 '16 at 04:49
  • That sounds quite slow if there are not that many rows anyway. But if you can run it faster on hardware X than on the cloud server, that kind of says it would be faster switching hardware. But only way to know is to test – Sami Kuhmonen Mar 16 '16 at 04:51

4 Answers4

0

Though Cloud computing utilizes the SAN storage, you will be sharing the resources like CPU, RAM and Storage among number of VMs. This may cause the slowness in I/O operations and hence in your MySQL queries.

In dedicated server, all resources will be allocated to your applications only. Thus, switching to a dedicated server will certainly improve the MySQL server performance. Also, when you have database driven application, SSD drives are highly recommended.

AccuWebHosting.Com
  • 1,159
  • 1
  • 7
  • 17
0

2GB is tiny by today's norms. Ar you using InnoDB or MyISAM? What is the cache size? Does it have SSDs?

It sounds like the table is bigger than can be cached, and that the slow query is scanning the entire table. That says that the query and/or indexes are not well optimized. Let's see the SELECT and SHOW CREATE TABLE; more tuning may be possible.

Does the Cloud limit your IOPs? That mighe explain the difference. And it would further indicate that we should study the query.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

Given your query takes 20 seconds to run on Cloud environment, we can rule out network latency problems. To find out the bottleneck. Try using "top" while running the large query on cloud.

If CPU utilization is high, change to a high CPU type, maybe higher memory as well (you can see memory utilization with "top" as well).

If the %CPU of idle or wait is high, it basically suggests underlying disk is the bottleneck here. You can verify this by running some "fio" test, or use "iostat" and monitor the device usage for the query. The solution is adding more disk space or use a SSD device on cloud.

n00b1989
  • 11
  • 2
0

I will prefer cloud always. when it come to dedicated server there you can't find elasticity on server usage. choose a good cloud architecture to enhance the application performance. you have set proper auto scaling, devops methods.

You can also use a remote database to connect your application in order to avoid I/O request to application server.

Abdul Khader AK
  • 1
  • 2