mysql Import 속도를 향상시키다

mysql Import 속도를 향상시키다

는 大 의 데이터베이스를 가지고 있다.22GBmysqldumpgzip을 사용하다

하면 gz 파일이 됩니다..sql로 정리하다16.2GB

로컬 서버에서 데이터베이스를 Import하려고 하면 Import에 약 48시간이 걸립니다.수입 처리 속도를 높일 수 있는 방법이 있나요?

또한 성능 향상을 위해 하드웨어 변경이 필요한지 알고 싶습니다.

현재 시스템 구성

 Processor: 4th Gen i5
 RAM: 8GB

#업데이트

my.cnf는 다음과 같습니다.

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 512M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 4M
query_cache_size        = 512M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 512M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 512M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

3 월 9.9 일GB import Import 。에는 둘 다 .MyISAM ★★★★★★★★★★★★★★★★★」InnoDB를 참조할 수 있습니다.수입 실적을 향상시키려면 어떻게 해야 하나요?

있습니다.mysqldump 코드를 각 .

$dir="./";
$files = scandir($dir, 1);
array_pop($files);
array_pop($files);
$tablecount=0;
foreach($files as $file){
    $tablecount++;
    echo $tablecount."     ";

    echo $file."\n";
    $command="gunzip < ".$file." | mysql -u root -pubuntu cms";

    echo exec($command);
}

설명된 방법으로 덤프 및 복원을 수행하면 MySQL이 데이터를 가져올 때 인덱스를 완전히 재구축해야 합니다.또한 매번 데이터를 해석해야 합니다.

MySQL이 이미 알고 있는 형식으로 데이터 파일을 복사할 수 있다면 훨씬 더 효율적일 것입니다.좋은 방법은 Percona의 innobackupex를 사용하는 것입니다.

(여기서 다운로드 가능한 XtraBackup의 일부로 배포된 오픈 소스).

그러면 MyISAM 테이블의 스냅샷이 생성되고 InnoDB 테이블의 경우 기본 파일을 복사한 다음 트랜잭션 로그를 재생하여 일관된 상태를 보장합니다.가동 중인 서버에서 다운타임 없이 이 작업을 수행할 수 있습니다(그것이 고객님의 요건인지 모르겠습니다).

설명서를 읽는 것이 좋습니다만, 가장 간단한 형태로 백업을 하는 것은 다음과 같습니다.

$ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
$ innobackupex --apply-log /path/to/BACKUP-DIR/

데이터가 같은 머신에 있는 경우, innobackupex에는 다음과 같은 간단한 restore 명령도 있습니다.

$ innobackupex --copy-back /path/to/BACKUP-DIR

실제로 백업을 수행하는 방법은 여러 가지가 있으므로 시작하기 전에 설명서를 잘 읽어보시기 바랍니다.

속도와 관련하여, 약 600 IOPS를 실행하는 느린 테스트 서버는 이 방법을 사용하여 약 4시간 만에 500GB 백업을 복원할 수 있습니다.

마지막으로, 수입의 신속화를 위해 무엇을 할 수 있는지 말씀하셨습니다.그것은 대부분 병목의 종류에 따라 달라질 것이다.일반적으로 Import 조작은 I/O에 의존하며(io 대기 상태를 체크하여 테스트 가능), 디스크 throughput을 고속화하는 방법(디스크 자체의 고속화 또는 그 이상)이 있습니다.

문제의 원인을 완전히 이해하기 위해 누락된 파라미터가 많이 있습니다.예를 들어 다음과 같습니다.

1. MySQL 버전
2. 디스크 종류 및 속도
3. MySQL 서버를 시작하기 전에 서버에서 사용 가능한 메모리
4. mysqldump 이전 및 시점에서의 iostat 출력.
5. 처음에 덤프 파일을 생성하는 데 사용하는 매개 변수는 무엇입니까?

더 많이요.

MySQL 인스턴스가 150개 있고 그 중 하나에 3TB의 데이터가 저장되어 있기 때문에 문제가 디스크라고 추측해 보겠습니다.

이제 해결책으로 넘어가겠습니다.

먼저 MySQL이 최고의 성능을 발휘하도록 구성되어 있지 않습니다.

설정하는 가장 중요한 설정에 대해서는, 다음의 Percona 블로그 투고를 참조해 주세요.http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/

특히 다음 파라미터를 확인합니다.

innodb_buffer_pool_size 
innodb_flush_log_at_trx_commit
innodb_flush_method

같은 드라이브에서 파일을 읽어내는 디스크로 인해 문제가 더 악화되고 있는 경우.

또한 사용 가능한 RAM이 부족하여 MySQL 서버가 스왑을 시작하면 문제가 더욱 커집니다.

이를 확인하려면 복원 절차 이전과 시점에 시스템에 대한 진단을 실행해야 합니다.

또한 mysqldump보다 빠르게 동작하는 리빌드 작업을 수행하기 위해 다른 기술을 사용하는 것이 좋습니다.

Percona Xtrabackup - http://www.percona.com/doc/percona-xtrabackup/2.2/

백업을 생성하여 여기서 복원하거나 스트리밍 옵션을 사용하여 실행 중인 서버에서 직접 재구축해야 합니다.

또한 MySQL 버전 5.5 - InnoDB는 MyISAM보다 속도가 빠릅니다.모든 테이블을 그것에 바꾸는 것을 고려해보세요.

네가 할 수 있는 한 가지는

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0

그리고 그 가치들을 가지고 놀 수도 있다.

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_flush_method

my.cnf다른 innodb 파라미터와 함께 최적의 파라미터를 확인해야 합니다.

이것은 내가 과거에 겪었던 문제이며, 나는 내가 완전히 대처했다고 생각하지 않지만 처음부터 나를 이 방향으로 이끌었기를 바란다.시간을 꽤 절약할 수 있었을 텐데

"max_allowed_packet" 변수를 충분한 크기로 늘려야 합니다.텍스트 데이터가 많으면 도움이 됩니다.고성능 하드웨어를 사용하면 데이터 Import 속도가 향상됩니다.

mysql --max_allowed_packet=256M -u root -p < "database-file.sql"

저는 당신과 거의 같은 크기의 덤프를 수입해야 했습니다(15.8).GB) 및 다음 설정을 사용하여 완료하는 데 2.2시간이 걸렸습니다.

my.cnf:

innodb_buffer_pool_size = 12G
innodb_log_buffer_size = 256M
innodb_log_file_size = 2G
innodb_write_io_threads = 32
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

시스템 사양은 다음과 같습니다.

CPU: core i5 7th gen
RAM: 16GB
HDD: 500GB

시스템 사양에 따라 이 설정을 구성했습니다.사용하시는 시스템의 RAM은 8GB이므로 다음과 같이 설정할 수 있습니다.

innodb_buffer_pool_size = 5G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

어떤 구성이 더 적합한지 확인할 수 있습니다.또한 변경을 적용하려면 mysql을 다시 시작해야 합니다.

방법 1: 가짜로 지정된 외부 키를 비활성화합니다.

SET AUTOCMIT = 0, SET FORNE_KEY_CHECKS=0

방법 2: Big Dump를 사용하면 mysqldump 파일을 청크하고 Import합니다.http://www.ozerov.de/bigdump/usage/

질문:.서버/명령줄에서 직접 업로드가 아니라 덤프를 Import하는 방법을 업로드한다고 하셨습니까?

을 사용하다사용하고 있습니다.mysqldumpCSV csv csv csv （ 음음음음 csv ）

mysqldump -u [username] -p -t -T/path/to/db/directory [database] --fields-enclosed-by=\" --fields-terminated-by=,

'가져오다'를 사용해서 .LOAD DATA INFILE다음과 같이 mysql 클라이언트 내에서 쿼리를 실행합니다.

LOAD DATA FROM INFILE /path/to/db/directory/table.csv INTO TABLE FIELDS TERMINATED BY ',';

데이터를 포함하는 SQL 쿼리를 실행하는 것보다 훨씬 빠릅니다.물론 이미 작성되고 있는(그리고 비어 있는) 테이블에도 의존합니다.

물론 빈 스키마를 먼저 내보낸 다음 가져오는 방법으로도 이 작업을 수행할 수 있습니다.

더 많은 RAM, 더 빠른 프로세서, 더 빠른 쓰기용 SSD.여러 개의 개별 인서트보다 빠르게 실행되도록 인서트를 배치하십시오.파일 용량이 커서 시간이 걸릴 거예요.

의 [Vinbot하고 있습니다.LOAD DATA INFILE데스크톱에서 하기 위해 약 또는 DBA가 CREATE TABLEmySQ s 、 mySQL l l l l l l 。

mySQL 8.0.17에 도입된 새로운 기능인 [mySQL Parallel Table Import Utility][2]는 다음 단계로 업그레이드합니다.

인텔 Core I7-6820에서는 CSV 테이블 Import에 약 15분(약1 Gb)이 소요되어 현재는 5:30이 소요됩니다.SATA SSD 탑재 HQnVME M.2 1Tb WD Black 드라이브(구 데스크톱용으로 구입했지만 호환되지 않는 것으로 판명됨)를 추가하고 mySQL 설치를 해당 드라이브로 이동하자 시간이 4분 15초로 단축되었습니다.

유틸리티를 실행하기 전에 테이블 정의에서 인덱스의 대부분을 정의합니다.인덱싱 없이 로드는 더욱 빨라지지만 로드 후 인덱싱에는 총 시간이 더 많이 걸립니다.병렬 로더의 멀티 코어 기능이 인덱스 작성까지 확장되므로 이 방법은 타당합니다.

나도ALTER INSTANCE DISABLE INNODB REDO_LOG(제8.0.21)을 병렬 로더 유틸리티 스크립트로 사용합니다.벌크 부하가 끝난 후에도 이 기능을 끄지 않도록 주의해 주십시오.다시 활성화하지 않아 인스턴스(테이블뿐만 아니라 인스턴스 전체)가 손상되었습니다.난 항상 이중 쓰기 버퍼링을 꺼.

CPU 모니터에는 8개의 코어를 모두 사용하고 있는 유틸리티가 표시됩니다.

병렬 로더가 완료되면 단일 스레드 mySQL로 돌아갑니다(멀티 사용자가 아닌 선형 분석 작업 세트용).새로운 nVME는 시간을 10% 정도 단축합니다.이 유틸리티를 사용하면 매일 몇 분씩 시간이 절약됩니다.

이 유틸리티를 사용하면 버퍼 크기와 스레드 수를 관리할 수 있습니다.CPU의 물리 코어 수(8)와 일치하며, 이것이 최적이라고 생각됩니다.(처음에는 병렬로더 구성에 대한 최적화 힌트를 찾고 있었습니다).[1] : https://stackoverflow.com/a/29922299/5839677 [2] : https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-parallel-table.html

고객님께는 이것이 선택사항이 아닐지 모르겠지만, 가장 좋은 방법은 Tata와 AndySavage가 이미 말한 바와 같습니다.Percona의 innobackupex를 사용하여 프로덕션 서버에서 데이터 파일의 스냅샷을 만든 후 로컬 박스에 설치하는 것입니다.일관된 방법으로 InnoDb 테이블을 백업하고 MyISAM 테이블에 대한 쓰기 잠금을 수행합니다.

프로덕션 시스템에서 전체 백업을 준비합니다.

http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/preparing_a_backup_ibk.html

백업된 파일을 로컬 컴퓨터에 복사(또는 SSH를 통해 파이프 연결 - 자세한 내용은 여기를 참조하십시오)한 후 복원합니다.

백업을 복원합니다.

http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_a_backup_ibk.html

innobackupex의 전체 매뉴얼은 다음 URL에서 찾을 수 있습니다.http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html

SQL 덤프를 읽는 것보다 복원 시간이 훨씬 빠릅니다.

언급URL : https://stackoverflow.com/questions/29643714/improve-speed-of-mysql-import