MariaDB 10.4.13 slow performance compared to MySQL 5.7.30
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
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. )