source

MySQL 5.7.30에 비해 마리아DB 10.4.13의 퍼포먼스가 느리다

goodcode 2022. 9. 8. 21:51
반응형

MySQL 5.7.30에 비해 마리아DB 10.4.13의 퍼포먼스가 느리다

MySQL 데이터에서 MariaDB로 대용량(3GB 이상) 데이터베이스를 마이그레이션한 후 특정 쿼리 성능에 문제가 있는 64비트 버전입니다.데이터베이스가 분석, 최적화, 재구축됩니다.다음은 MariaDB 구성, 데이터베이스 구성 및 해당 쿼리입니다.

이 문제에 대한 접근 방법, 방법, 장소, 시기에 대한 조언에 감사드립니다.

기계 파라미터는 다음과 같습니다.인텔 Core i5 CPU @3.6GHz, 16GB RAM, Sandisk 512GB SSD, Windows 10 v.1909 사용.

느린 성능의 SQL 쿼리(10초, MySQL 5.7에서는 약 1초):

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

SQL 쿼리에 대해 설명합니다.

테이블에 대한 SQL 생성:

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'
;

'DBmy.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

그거 참 의문스럽네요.성능을 이해하려면 분석해야 할 것 같습니다.

두 가지 서브쿼리 패턴이 있는 것 같네요여기 하나의 패턴이 있습니다.

      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)

그리고 여기 또 다른 것이 있다

      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)

는, 필터 「 필터」)가 .WHERE col=val를 참조해 주세요.이 경우, 이 기능은 사용하실 수 없습니다 ( )time BETWEEN this AND that)

따라서 최적화를 위해서는 선택성이 가장 높은 컬럼으로 멀티컬럼 인덱스를 시작해야 합니다.당신의 질의 패턴에 대한 복합 커버 인덱스가 필요합니다.

첫 번째 패턴은 이 인덱스를 사용해 보는 것이 좋을 것 같습니다.

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

두 번째 패턴은 조금 더 간단합니다.

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

필요한 이유는 () 가장 은 (아마도) 이기 때문입니다CLOSE_TIME ★★★★★★★★★★★★★★★★★」OPEN_TIMECMD첫, 는 MariaDB에 인덱스를 있을 수 .CMD IN (0,1)

중요한 것은 쿼리 플래너가 테이블로 다시 이동할 필요 없이 인덱스만으로 쿼리를 충족할 수 있도록 하는 것입니다.

수 SYMBOL LIKE 'value%'로로 합니다.SYMBOL = 'value'어플리케이션이 정상적으로 동작하고 있는 경우, 그렇게 해 주세요. '아예'를 넣으세요.SYMBOL1번으로 하다 평등한 일치합니다.

(중요한 주의: 다음과 같은 행의 질의에 기재되어 있습니다.

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

VOLUME 및 CLOSE_PRICE에 대해 예측할 수 없는 값을 얻을 수 있습니다.

(만약 이것이 제 고용주를 위해 다른 사람의 돈을 다루는 질문이라면, 저는 정확성을 분석하는데 몇 시간을 할애할 것입니다.)

매우 최근의 MariaDB 및 잘못된 쿼리(죄송합니다만, 이러한 많은 하위 선택 항목이 포함된 쿼리는 잘못된 쿼리일 뿐입니다)에서 이러한 종류의 동작을 본 적이 있기 때문에, 저는 여기에서 위험을 무릅쓰고 추측합니다(MySQL 5.7에서 설명 계획을 제공할 수 없기 때문입니다).

전환semijoin=off당신의 안에서optimizer_switch실행 계획이 덜 나쁜지 여부를 확인합니다.

또, 많은 설정을 전환하고 있는 것을 깨닫지 않을 수 없습니다.대부분의 설정을 조작할 필요는 없습니다.따라서, 우선은, 이 설정만을 사용해 깨끗한 설정을 실시하는 것을 추천합니다.innodb_buffer_pool_size메모리 크기에 맞게 설정합니다.

  • 의 끝에 (m,n)을 사용하지 마십시오.FLOAT또는DOUBLE. 당신이 가지고 있기 때문에,0, 당신은 약간을 사용하는 것이 좋다.INT바리안트
  • 색인CMD(CMD,OPEN_TIME,CLOSE_TIME,LOGIN,VOLUME,SYMBOL,CLOSE_PRICE) -- 열이 너무 많은 것 같습니다.
  • 당신의 테이블이 InnoDB였으면 좋겠어요.
  • RAM을 점유하고 있는 다른 애플리케이션이 없는 한 증가 가능innodb_buffer_pool_size.
  • 이 패턴은 일반적으로 비효율적입니다.FROM ( SELECT ... ) JOIN ( SELECT ... )
  • @fd_time0-- @ 변수를 사용하는 것은 위험합니다.옵티마이저는 예기치 않은 결과를 초래할 수 있습니다.즉, 설정 시기와 사용 시도에 의존할 수 없습니다.(그리고 최종적으로 사용할 수 없게 됩니다.)

  • 그UNION ALLs정확히 두 개의 행을 생성하는 것처럼 보이는데 두 값을 합산하면 됩니까?대신UNION에 공급되는SUM, 다음을 수행합니다.

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

  • 이것.WHERE에는 4개의 범위가 있습니다.인덱스는 하나만 사용할 수 있습니다.

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

Optimizer에는 다음 3가지 선택지가 있습니다.

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

  • 기호 끝에 와일드카드가 필요 없는 경우 다음 인덱스를 사용하는 것이 좋습니다.

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

  • (제 취향) 대신

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

나는 더 좋다

        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 ... 잘못된 데이터가 표시됩니다!!!태그 참조[groupwise-maximum].

언급URL : https://stackoverflow.com/questions/61953804/mariadb-10-4-13-slow-performance-compared-to-mysql-5-7-30

반응형

'source' 카테고리의 다른 글

스파크 오류 - 지원되지 않는 클래스 파일의 줄자 버전  (0) 2022.09.08
테이블 내의 각 외부 키 ID 인스턴스 수를 카운트하려면 어떻게 해야 합니까?  (0) 2022.09.08
Linux 또는 Windows에서 OS X를 컴파일하는 방법  (0) 2022.09.05
문자열의 첫 번째 문자 제거  (0) 2022.09.05
Linux 명령 프롬프트에서 $_GET을 전달하는 PHP  (0) 2022.09.05

'source'의 다른글

  • 현재글MySQL 5.7.30에 비해 마리아DB 10.4.13의 퍼포먼스가 느리다

관련글

댓글

티스토리툴바