MariaDB 10.4.13 slow performance compared to MySQL 5.7.30

Question

Having trouble with particular query performance after migrating large (3+ GB) database from MySQL data to MariaDB, it is 64bit version. The database is analyzed, optimized, rebuild. Below is the config of MariaDB, database scheme and the query in question.

Greatly appreciate an advise of what/how/where/when to approach this problem.

Machine parameters are: Intel Core i5 CPU @3.6GHz, 16GB RAM, Sandisk 512GB SSD, using Windows 10 v.1909.

SQL query with slow performance ( 10 seconds, used to be around 1 second on MySQL 5.7):

SELECT * FROM (
        SELECT 
      '#AT&T' AS instrument,
      (SELECT '2020-05-21 09:30' AS report_period) report_period,
    #Average price
        (SELECT AVG(avg_price.avg_price) AS avg_price FROM 
        (
          SELECT  AVG(t.CLOSE_PRICE) AS avg_price
          FROM mt4_trades t
          WHERE t.CLOSE_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND)  AND t.OPEN_TIME > '2012-08-26' 
          AND t.SYMBOL LIKE '#AT&T%' AND t.CMD IN (0,1) 
        UNION ALL
          SELECT  AVG(t.OPEN_PRICE) AS avg_price
          FROM mt4_trades t
          WHERE t.OPEN_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) 
          AND t.SYMBOL LIKE '#AT&T%' AND t.CMD IN (0,1)  

        ) avg_price) avg_price,          

      #Total deals value
        (
        SELECT SUM(total_deals_value.total_deals_value) AS total_deals_value FROM   (
          SELECT SUM(t.VOLUME/100.0 * 1  * t.CLOSE_PRICE ) AS total_deals_value
          FROM mt4_trades t
          WHERE t.CLOSE_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND t.OPEN_TIME > '2012-08-26' 
          AND t.SYMBOL LIKE '#AT&T%'  AND t.CMD IN (0,1) 
        UNION ALL           
          SELECT SUM(t.VOLUME/100.0 * 1  * t.OPEN_PRICE ) AS total_deals_value      
          FROM mt4_trades t
          WHERE t.OPEN_TIME BETWEEN '2020-05-21 09:30' AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND)   
          AND t.SYMBOL LIKE '#AT&T%'  AND t.CMD IN (0,1)  
          ) total_deals_value) AS total_deals_value) result

        LEFT OUTER JOIN   
        (SELECT '#AT&T' AS instrument, @fd_time0 AS fd_time, @fd_price0 AS fd_price, 
          (@fd_volume0/100.0 * 1  * @fd_price0 ) AS fd_volume 
            FROM (
              SELECT @fd_time0 := fd_time AS fd_time, @fd_volume0 := VOLUME AS VOLUME, @fd_price0 := PRICE AS PRICE 
              FROM 
                  (SELECT MIN(t.CLOSE_TIME) AS fd_time, t.VOLUME, t.CLOSE_PRICE AS PRICE FROM mt4_trades t WHERE t.CLOSE_TIME BETWEEN 
                    DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND '2020-05-21 11:30' AND t.OPEN_TIME > '2012-08-26' 
                    AND t.SYMBOL LIKE '#AT&T%' 
                  UNION ALL
                  SELECT MIN(t.OPEN_TIME) AS fd_time, t.VOLUME, t.OPEN_PRICE AS PRICE FROM mt4_trades t WHERE t.OPEN_TIME BETWEEN 
                    DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) AND '2020-05-21 11:30' 
                    AND t.SYMBOL LIKE '#AT&T%'   
                    ORDER BY fd_time) first_deal WHERE first_deal.fd_time IS NOT NULL ORDER BY first_deal.fd_time ASC LIMIT 1
            ) AS first_deal) temp_result ON temp_result.instrument =  result.instrument 

Explain of the SQL query:

Create SQL for table:

CREATE TABLE `mt4_trades` (
`TICKET` INT(11) UNSIGNED NOT NULL,
`LOGIN` INT(11) UNSIGNED NOT NULL,
`SYMBOL` VARCHAR(16) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`DIGITS` TINYINT(3) UNSIGNED NOT NULL,
`CMD` TINYINT(3) UNSIGNED NOT NULL,
`VOLUME` MEDIUMINT(8) UNSIGNED NOT NULL,
`OPEN_TIME` DATETIME NOT NULL,
`OPEN_PRICE` FLOAT(12,0) NOT NULL,
`SL` FLOAT(12,0) NOT NULL,
`TP` FLOAT(12,0) NOT NULL,
`CLOSE_TIME` DATETIME NOT NULL,
`EXPIRATION` DATETIME NOT NULL,
`REASON` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`CONV_RATE1` FLOAT(12,0) NOT NULL,
`CONV_RATE2` FLOAT(12,0) NOT NULL,
`COMMISSION` FLOAT(12,0) NOT NULL,
`COMMISSION_AGENT` FLOAT(12,0) NOT NULL,
`SWAPS` FLOAT(12,0) NOT NULL,
`CLOSE_PRICE` FLOAT(12,0) NOT NULL,
`PROFIT` FLOAT(12,0) NOT NULL,
`TAXES` FLOAT(12,0) NOT NULL,
`COMMENT` VARCHAR(32) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`INTERNAL_ID` INT(11) NOT NULL,
`MARGIN_RATE` FLOAT(12,0) NOT NULL,
`TIMESTAMP` INT(11) UNSIGNED NOT NULL,
`MAGIC` INT(11) NOT NULL DEFAULT '0',
`GW_VOLUME` INT(11) NOT NULL DEFAULT '0',
`GW_OPEN_PRICE` INT(11) NOT NULL DEFAULT '0',
`GW_CLOSE_PRICE` INT(11) NOT NULL DEFAULT '0',
`MODIFY_TIME` DATETIME NOT NULL,
PRIMARY KEY (`TICKET`) USING BTREE,
INDEX `INDEX_STAMP` (`TIMESTAMP`, `COMMENT`) USING BTREE,
INDEX `CMD` (`CMD`, `OPEN_TIME`, `CLOSE_TIME`, `LOGIN`, `VOLUME`, `SYMBOL`, `CLOSE_PRICE`) USING 
BTREE
)
COLLATE='utf8_general_ci'
;

MariaDB's my.ini

[mysqld]
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp" 
datadir = "C:/xampp/mysql/data"
log_error = "mysql_error.log"
pid_file = "mysql.pid"
collation_server=utf8_general_ci
character_set_server=utf8

## CUSTOM EDIT
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,STRICT_TRANS_TABLES
skip_external_locking
skip_name_resolve
max_connections                 = 200
table_open_cache                = 10000
table_definition_cache          = 2000
open_files_limit                = 20000
##MyISAM setting
key_buffer                      = 512M
myisam_sort_buffer_size         = 2M
#
max_allowed_packet              = 16M
max_sort_length                 = 16384
sort_buffer_size                = 1M
net_buffer_length               = 64K
read_buffer_size                = 256K
read_rnd_buffer_size            = 512K
#INNO DB settings
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 4G
innodb_sort_buffer_size         = 16M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size            = 1024M
innodb_log_buffer_size          = 32M
innodb_flush_log_at_trx_commit  = 2
innodb_stats_on_metadata        = 0
innodb_lock_wait_timeout        = 600
innodb_flush_method             = normal
#A minor optimization when writing blocks to disk. Use 0 for SSD drives; 1 for HDD.
innodb_flush_neighbors          = 0
innodb_io_capacity              = 2000
#
innodb_buffer_pool_instances    = 3
innodb_thread_concurrency       = 12
innodb_autoextend_increment     = 64
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16
concurrent_insert               = 2
thread_stack                    = 512K
interactive_timeout             = 600
wait_timeout                    = 600
query_cache_type                = 2
query_cache_limit               = 64M
query_cache_min_res_unit        = 1
query_cache_size                = 16M
thread_cache_size               = 128
low_priority_updates
tmp_table_size                  = 4M
max_heap_table_size             = 4M
bulk_insert_buffer_size         = 256M
group_concat_max_len            = 512K
# Define which query should be considered as slow, in seconds
long_query_time                 = 6
join_cache_level                = 8
# Size limit for the whole join
#join_buffer_space_limit        = 512M
join_buffer_size                = 4M
# Optimizer switches
optimizer_switch                ='orderby_uses_equalities=on'
optimizer_switch                ='mrr=on,mrr_sort_keys=on'
optimizer_switch                ='index_merge_sort_intersection=on'
optimizer_switch                ='optimize_join_buffer_size=on'
optimizer_switch                ='join_cache_bka=on'
optimizer_switch                ='join_cache_hashed=on'
optimizer_switch='in_to_exists=on'
optimizer_switch='join_cache_incremental=on'
#optimizer_switch='loosescan=on'

# Where do all the plugins live
plugin_dir = "C:/xampp/mysql/lib/plugin/" 
server-id   = 1

Answer 1

That's quite a query. I believe you'll need to break it down to understand its performance.

It looks to me like you have two subquery patterns. Here's one pattern

      SELECT something_or_other
        FROM mt4_trades t
       WHERE t.CLOSE_TIME BETWEEN '2020-05-21 09:30' 
                              AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND)  
         AND t.OPEN_TIME > '2012-08-26' 
         AND t.SYMBOL LIKE '#AT&T%'
         AND t.CMD IN (0,1) 

and here's the other

      SELECT something_or_other
        FROM mt4_trades t
       WHERE t.OPEN_TIME BETWEEN '2020-05-21 09:30'
                             AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND) 
        AND t.SYMBOL LIKE '#AT&T%'
        AND t.CMD IN (0,1)  

Unfortunately for exploiting indexes, you have no equality filters (WHERE col=val) in these query patterns. Index range scans can be very efficient, but they do best when they handle multiple equality filters and then one range filter. (time BETWEEN this AND that)

So to optimize we need to start your multicolumn indexes with the column with the greatest selectivity. We need compound covering indexes for your query patterns.

I think you should try this index for the first of your patterns.

CREATE INDEX closedex ON mt4_trades
     (CLOSE_TIME,  CMD, OPEN_TIME, SYMBOL, VOLUME, CLOSE_PRICE, LOGIN)

For the second of your patterns it's a little simpler

CREATE INDEX opendex ON mt4_trades
     (OPEN_TIME,  CMD, SYMBOL, VOLUME, CLOSE_PRICE, LOGIN)

You need both indexes because (I guess) your most selective columns are CLOSE_TIME and OPEN_TIME. You should also try putting CMD first in those indexes; maybe MariaDB knows how to use indexes efficiently for CMD IN (0,1).

The point is to make the query planner able to satisfy the query from the index alone, without having to jump back to the table.

If you can change SYMBOL LIKE 'value%' to SYMBOL = 'value' and still have your application work correctly, do so. Then put SYMBOL first in your index; that's an equality match.

(Important Note: in your query in the lines like

SELECT MIN(t.CLOSE_TIME) AS fd_time, t.VOLUME, t.CLOSE_PRICE AS PRICE

you're getting unpredictable values for VOLUME and CLOSE_PRICE.

(If this were my query handling other peoples' money for my employer, I'd spend a few hours analyzing it for correctness. )

Answer 2

Having seen this sort of behaviour with very recent MariaDB and bad queries (sorry, I won't sugar coat it - queries with many sub-selects like this are just bad queries), I'm going to go out on a limb here and make a guess (since you cannot provide the EXPLAIN plan from MySQL 5.7):

Toggle semijoin=off in your optimizer_switch setting and see if it picks a less bad execution plan.

I also cannot help but notice that you are toggling a LOT of configuration settings - nobody ever needs to touch the vast majority of those, so I suggest you start with a clean configuration with only the innodb_buffer_pool_size set appropriately for your memory size.

Answer 3

• Don't use (m,n) on the end of FLOAT or DOUBLE. Since you have ,0, you may as well use some INT variant.
• INDEX CMD (CMD, OPEN_TIME, CLOSE_TIME, LOGIN, VOLUME, SYMBOL, CLOSE_PRICE) -- probably too many columns.
• I hope your tables are InnoDB.
• Unless you have other apps hogging RAM, you could increase innodb_buffer_pool_size.
• This pattern is generally inefficient: FROM ( SELECT ... ) JOIN ( SELECT ... )
• @fd_time0 -- Using @ variables is risky; the optimizer may lead to surprises; that is, you cannot depend on when they are set versus used. (And they will eventually be disallowed.)

• The UNION ALLs seem to be generating exactly two rows, then you add the two values together? Instead of the UNION that feeds into a SUM, do this:

  ( SELECT ( SELECT ... ) + ( SELECT ... ) )
  

• This WHERE has 4 ranges. Only one can use an index:

                          WHERE  t.CLOSE_TIME BETWEEN ...
                            AND  t.OPEN_TIME > '2012-08-26'
                            AND  t.SYMBOL LIKE '#AT&T%'
                            AND  t.CMD IN (0,1)
  

I suggest you give the Optimizer 3 choices:

    INDEX(close_time)
    INDEX(OPEN_TIME)
    INDEX(SYMBOL)

• If you don't need the wildcard on the end of the symbol, then these indexes would be better:

  INDEX(SYMBOL, close_time)
  INDEX(SYMBOL, OPEN_TIME)
  

• (My preference) Instead of

  t.OPEN_TIME BETWEEN '2020-05-21 09:30'
                  AND DATE_ADD('2020-05-21 09:30', INTERVAL 119 SECOND
  

I prefer

        t.OPEN_TIME >= '2020-05-21 09:30'
    AND t.OPEN_TIME  < '2020-05-21 09:30' + INTERVAL 2 MINUTE

• SELECT MIN(t.CLOSE_TIME) AS fd_time, t.VOLUME ... will give the wrong data!!!. See the tag [groupwise-maximum].