3

We have two similar spec HDD CentOS 7 servers for two different clients in different countries, both clients have the same database and same indexes.

  • Client A: The server is only running MySQL 8 and it's extremely fast. Query X takes 11 seconds to finish. The database of client A has more records than client B.
  • Client B: The server is running LAMP + MySQL 5.7, RAM, and CPU is normal, query X is talking more than two hours. Any query that takes X time on any other server, takes 10x time on client B server.

The MySQL storage for client B is located on a NAS, which has a 1GB interface with the server and 5MB/s IO speed. Only read/write are done on NAS. The reason is that the server has a small HDD, 250GB. Could that be the reason why it's so slow?

Please note that if the query does not have joins, it’s fast but if it has joins it’s not. Could it be that NAS is trying to cache something?

Giacomo1968
  • 3,542
  • 27
  • 38
Lynob
  • 241
  • 1
  • 9
  • 17
  • 2
    5 MB/sec is a very poor I/O speed and to answer your question, yes, it could be the reason. But why ask us to guess? Look at the stats on the server. – tater Sep 07 '20 at 10:45
  • @tetech what stats to look for specifically? – Lynob Sep 07 '20 at 10:46
  • The information you added about joins could be indicative of a huge temp table being created. – tater Sep 07 '20 at 10:51
  • @tetech indeed, a huge temp table being created, can't NAS handle that? on any other db server the who thing takes 11s at worst – Lynob Sep 07 '20 at 10:53
  • Your measurements suggest that your particular NAS cannot. How big is the temp table, consider putting in RAM or local disk. – tater Sep 07 '20 at 10:58
  • Your topology description is too vague. What country is each client in (X and Y is ok)? Where are there two servers? Or is it the same server? Is the NAS for each server "next to" the Server? – Rick James Sep 07 '20 at 16:36
  • What query is so sensitive to latency? Is it a single query? Is the disk far from the server? Is it a bunch of queries? Could they be rolled into a stored proc? – Rick James Sep 07 '20 at 16:37
  • Query that takes 11 seconds is not extremely fast, it is fairly slow. If it is aggregating over a million rows, or returning a million rows, it's OK, but that makes it an exceptionally heavy query. But it it just selects 50 rows, even joining three million-row tables, it should still be ready in a fraction of a second. – Jan Hudec Sep 07 '20 at 21:39
  • Sounds like one setup is doing queries in one way and the other is doing queries in another way. Which means the NAS has nothing to do with it: It all sounds like the query running with Client B is simply not being properly indexed. To check, run the same query with `EXPLAIN` prefixed to it and look at the output. Check `possible_keys` as well as `key` and then `rows`. The key here is if the index is working the value of `rows` should always be less than the total number of rows in the table. That is what proper indexing can/will do: Limit the pool of data being checked. Check your indexes. – Giacomo1968 Sep 08 '20 at 00:25

3 Answers3

4

a 1GB interface with the server and 5MB/s IO speed

5 Mb is slow over a 1Gb link.

the server has a small HDD, 250GB.

That's a comfortable size for a Windows Server, but how much of that is free for use?
Windows really doesn't like running short of disk space, especially given that a chunk of that space is given over to the Swap (paging) file!

How much memory (RAM) does this server have available?

if the query does not have joins, it's fast but if it has joins it's not ...

Joins do not a Slow Query make ... unless those joins are not properly supported by Indexes.

We're all assuming here that your query is basically

select * from table1 ;

That would be slow, because it has to pull every single data page, across that [slow] network link, into memory and then pass it on to the client application. When you start adding "where" clauses based on indexed fields, then MySQL can start doing something more clever.

It would be useful for us to see the actual query that you're running and the structures of the tables that it's using.

Phill W.
  • 1,479
  • 7
  • 7
1

What is your read / write pattern ?

If you write a lot of small records, disk and network latency is your enemy, and NAS add a layer of round-trip network latency.

Depending on your NAS you may also suffer from write amplification (for instance, if your database thinks that blocks are 8k while the NAS blocks are 64k)

This is less of a problem with reads, because data gets cached on the server, but writes make you pay the latency tax immediately.

In today's computers latency is the main problem.

https://blog.morizyun.com/computer-science/basic-latency-comparison-numbers.html

Michel de Becdelièvre
  • 151
  • 3
1

Please note that if the query does not have joins, it's fast but if it has joins it's not. could it be that NAS is trying to cache something?

In that case I'd first consider unoptimized schema masked by more clever algorithm is MySQL 8 not present in MySQL 5.7 or not usable in the other setup.

When you are doing a join, you should generally have an index. Then the database will scan one of the tables and pick the matching rows from the other using the index.

If you don't, the database might be able to sort the tables by the join column, in memory, and merge them, or build an in-memory temporary index provided that

  • it has the algorithm implemented,
  • has enough memory for caching the data, and
  • makes correct guess at which algorithm will be faster.

Otherwise it just falls back to O(n²) algorithm and that is a performance killer on large tables.

So my first guess would be that for the newer server the above three conditions are satisfied, and it optimizes the query, but for the older one some of them are not, and it runs two nested full scans.

So I'd first use EXPLAIN to make sure the queries are using indices, and create appropriate indices if they don't.

Jan Hudec
  • 275
  • 3
  • 13