CODEWITHSUNDEEP
mysqlmariadbquery-performance
Dani
Dani

Reputation: 15069

remote query against mysql server takes much more time (40s vs 1.8s)

** check update 5 at the bottom ** - looks like a bug of mysql connectors. Thanks all the helpers for improving my original table :-) but the issue goes deeper than that.

I have a simple query that takes 1.8s on the server, but 40s from remote connection. the query returns 1 row:

SELECT a, b, c FROM d WHERE cola <= 123456789012345 and 123456789012345 <= colb

server is mysql 8.0.21. this query running in workbench takes 1.89s

same query running on my own computer in navicat client takes anything between 30s and 60s based on server load.

the query returns one row from a 10M rows table.

what might cause the difference (Internet connection is fiber, and fast) ? anyway to solve this ?

Explain results:

** UPDATE **

same query has difference in execution time on slow log when coming locally or remotely as described. same query running against mariadb on linux server remotly shows good performance (2s) both local and remotely.

something is off with the mysql 8.0.21 on windows I guess. maybe an upgrade process caused this ?

Explain results:

Select type: Simple 
Type: ALL 
Possible keys: cola, colb cola-colb,colb-cola 
key: null 
key_lenn: null 
rows: 10M 
filtered: 22.58 
Extra:
using WHERE.

** UPDATE 2 - Show Create Table (same on local and remote) **

CREATE TABLE `d` (
   `cola` decimal(39,0) unsigned DEFAULT NULL,
   `colb` decimal(39,0) unsigned DEFAULT NULL,
   `a` char(2) COLLATE utf8_bin DEFAULT NULL,
   `b` varchar(64) COLLATE utf8_bin DEFAULT NULL,
   `c` varchar(128) COLLATE utf8_bin DEFAULT NULL,
   `d` varchar(128) COLLATE utf8_bin DEFAULT NULL,
   KEY `idx_cola` (`cola`),
   KEY `idx_colb` (`colb`),
   KEY `idx_cola_colb` (`cola`,`colb`),
   KEY `idx_colb_cola` (`colb`,`cola`) USING BTREE
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

** UPDATE 3 ** After the comments about myISAM I've converted the table to INNODB. it had 2 effects: A. processing time now is almost equal for remote and local B. they are both 6-7s and not the 2s it used to be as MyISAM. C. Horrible - after fixing the indexes as as @Rick James suggested I've got the server running the query in less than 1s but remote stayed 7s

so on one hand I'm happy I'm down from 40s to 7s, but how come the performance got so worst from local to remote ?

** UPDATE 4 **

I have built a small app to run the query and installed it on the server my app using localhost as server address takes 7s to run the query. Workbench on the same query - 0.9s. Tried Work Bench From Remote address and it keeps the 0.9s Navicat / mysql.data connector for mysql 8.0.21 - from remote address - 7s ! I can't understand why is the difference.

** UPDATE 5 ** reproduced on my own computer. Workbench 0.9. code and navicat 7s. opened a bug at bugs.mysql.com

** UPDATE 6 **

performance schema: enter image description here

The exact same query just from workbench:

enter image description here

Upvotes: 0

Views: 828

Answers (2)

Dani
Dani

Reputation: 15069

I have found the reason for this issue. It looks like WORKBENCH is adding to the query sent to the server limit 0,1000 at the end. Although the query only returns 1 row, it makes a 10s difference in execution time - with and without that addition.

MariaDB seems to be immune to this, as the query works the same with and without this addition.

Upvotes: 0

Rick James
Rick James

Reputation: 142298

I recommend these two 2-column indexes:

INDEX(cola, colb)
INDEX(colb, cola)

Do not also have INDEX(cola) and INDEX(colb); the optimizer will erroneously prefer to use them.

I assume that the two columns and the constants are timestamps? And you are checking for overlap? Is the particular query searching near one "end" of the timestamps?

How much RAM do you have? How big (in GB) is the table? Please provide EXPLAIN SELECT ....

Do not use MyISAM. It is old, removed from 8.0, has fewer optimizations, etc.

decimal(39,0) -- Really? What kind of value are these? That takes 18 bytes.

If you are pointing out an optimization difference between 8.0 and MariaDB, make it clearer that that is the point. Workbench is just a UI; it has nothing to do with the processing except that it might deliver the first few rows "quickly", thereby fooling you into thinking that it is faster.

When switching from MyISAM to InnoDB, you must adjust key_buffer_size (lower) and innodb_buffer_pool_size (much higher). See http://mysql.rjweb.org/doc.php/memory

Upvotes: 1

Related Questions