programing

테이블 변경/작성 InnoDB가 너무 깁니다.

minimums 2023. 8. 26. 10:41
반응형

테이블 변경/작성 InnoDB가 너무 깁니다.

InnoDB(MyISAM 엔진 변경)를 사용해보고 싶습니다.삽입/삭제/업데이트가 정상적으로 작동합니다(트랜잭션 포함).선택 항목이 좋아 보입니다.InnoDB 테이블을 변경하고 만드는 것은 끔찍합니다.

InnoDB 및 MyISAM 테이블 생성/변경 비교:

MariaDB [temp_db]> CREATE TABLE `table_InnoDB` (
    ->   `id` int(10) unsigned NOT NULL DEFAULT 0,
    ->   `name` varchar(120) NOT NULL DEFAULT '',
    ->   `name_1` varchar(120) NOT NULL DEFAULT '',
    ->   `name_2` varchar(120) NOT NULL DEFAULT '',
    ->   `name_ms` varchar(250) NOT NULL DEFAULT '',
    ->   `name_ws` varchar(250) NOT NULL DEFAULT '',
    ->   `name_wa` varchar(250) NOT NULL DEFAULT '',
    ->   `name_t` varchar(250) NOT NULL DEFAULT '',
    ->   `name_o` varchar(250) NOT NULL DEFAULT '',
    ->   `street` varchar(250) NOT NULL DEFAULT '',
    ->   `street_t` varchar(250) NOT NULL DEFAULT '',
    ->   `lat` decimal(8,4) NOT NULL DEFAULT 0.0000,
    ->   `lng` decimal(8,4) NOT NULL DEFAULT 0.0000,
    ->   `latlng` varchar(15) NOT NULL DEFAULT '0',
    ->   `geohash` char(4) NOT NULL DEFAULT '',
    ->   `phone` varchar(50) NOT NULL DEFAULT '',
    ->   `website` varchar(100) NOT NULL DEFAULT '',
    ->   `facebook_id` varchar(32) NOT NULL DEFAULT '',
    ->   `city_id` int(10) unsigned NOT NULL DEFAULT 0,
    ->   `gd_city_id` int(10) unsigned NOT NULL DEFAULT 0
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (3.235 sec)

MariaDB [temp_db]> CREATE TABLE `table_MyISAM` (
    ->   `id` int(10) unsigned NOT NULL DEFAULT 0,
    ->   `name` varchar(120) NOT NULL DEFAULT '',
    ->   `name_1` varchar(120) NOT NULL DEFAULT '',
    ->   `name_2` varchar(120) NOT NULL DEFAULT '',
    ->   `name_ms` varchar(250) NOT NULL DEFAULT '',
    ->   `name_ws` varchar(250) NOT NULL DEFAULT '',
    ->   `name_wa` varchar(250) NOT NULL DEFAULT '',
    ->   `name_t` varchar(250) NOT NULL DEFAULT '',
    ->   `name_o` varchar(250) NOT NULL DEFAULT '',
    ->   `street` varchar(250) NOT NULL DEFAULT '',
    ->   `street_t` varchar(250) NOT NULL DEFAULT '',
    ->   `lat` decimal(8,4) NOT NULL DEFAULT 0.0000,
    ->   `lng` decimal(8,4) NOT NULL DEFAULT 0.0000,
    ->   `latlng` varchar(15) NOT NULL DEFAULT '0',
    ->   `geohash` char(4) NOT NULL DEFAULT '',
    ->   `phone` varchar(50) NOT NULL DEFAULT '',
    ->   `website` varchar(100) NOT NULL DEFAULT '',
    ->   `facebook_id` varchar(32) NOT NULL DEFAULT '',
    ->   `city_id` int(10) unsigned NOT NULL DEFAULT 0,
    ->   `gd_city_id` int(10) unsigned NOT NULL DEFAULT 0
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.564 sec)


# alter empty tables

MariaDB [temp_db]> alter table `table_InnoDB`
    -> add primary key (id),
    -> add index (street(50), name(50)),
    -> add index (street(50), city_id),
    -> add index (street(50), gd_city_id),
    -> add index (name(50), website),
    -> add index (website, city_id),
    -> add index (website, gd_city_id),
    -> add index (phone, website),
    -> add index (phone, name(50)),
    -> add index (latlng, name(50)),
    -> add index (city_id, name(50)),
    -> add index (gd_city_id, name(50)),
    -> add index (name_t(50), street(50)),
    -> add index (street_t(50), name_t(50)),
    -> add index (name_t(50), website),
    -> add index (name_t(50), phone);
Query OK, 0 rows affected (11.812 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [temp_db]> alter table `table_MyISAM`
    -> add primary key (id),
    -> add index (street(50), name(50)),
    -> add index (street(50), city_id),
    -> add index (street(50), gd_city_id),
    -> add index (name(50), website),
    -> add index (website, city_id),
    -> add index (website, gd_city_id),
    -> add index (phone, website),
    -> add index (phone, name(50)),
    -> add index (latlng, name(50)),
    -> add index (city_id, name(50)),
    -> add index (gd_city_id, name(50)),
    -> add index (name_t(50), street(50)),
    -> add index (street_t(50), name_t(50)),
    -> add index (name_t(50), website),
    -> add index (name_t(50), phone);
Query OK, 0 rows affected (0.681 sec)              
Records: 0  Duplicates: 0  Warnings: 0


# still empty tables

MariaDB [temp_db]> ALTER TABLE `table_InnoDB`
    -> DROP PRIMARY KEY,
    -> DROP INDEX `name_t_3`,
    -> DROP INDEX `name_t_2`,
    -> DROP INDEX `street_t`,
    -> DROP INDEX `name_t`,
    -> DROP INDEX `gd_city_id`,
    -> DROP INDEX `city_id`,
    -> DROP INDEX `latlng`,
    -> DROP INDEX `phone_2`,
    -> DROP INDEX `phone`,
    -> DROP INDEX `website_2`,
    -> DROP INDEX `website`,
    -> DROP INDEX `name`,
    -> DROP INDEX `street_3`,
    -> DROP INDEX `street_2`,
    -> DROP INDEX `street`;
Query OK, 0 rows affected (6.149 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [temp_db]> ALTER TABLE `table_MyISAM`
    -> DROP PRIMARY KEY,
    -> DROP INDEX `name_t_3`,
    -> DROP INDEX `name_t_2`,
    -> DROP INDEX `street_t`,
    -> DROP INDEX `name_t`,
    -> DROP INDEX `gd_city_id`,
    -> DROP INDEX `city_id`,
    -> DROP INDEX `latlng`,
    -> DROP INDEX `phone_2`,
    -> DROP INDEX `phone`,
    -> DROP INDEX `website_2`,
    -> DROP INDEX `website`,
    -> DROP INDEX `name`,
    -> DROP INDEX `street_3`,
    -> DROP INDEX `street_2`,
    -> DROP INDEX `street`;
Query OK, 0 rows affected (0.730 sec)              
Records: 0  Duplicates: 0  Warnings: 0


# insert data

MariaDB [temp_db]> INSERT table_InnoDB (`id`, `name`, `name_1`, `name_2`, `name_ms`, `name_ws`, `name_wa`, `name_t`, `name_o`, `street`, `street_t`, `lat`, `lng`, `latlng`, `geohash`, `phone`, `website`, `facebook_id`, `city_id`, `gd_city_id`) SELECT `id`, `name`, `name_1`, `name_2`, `name_ms`, `name_ws`, `name_wa`, `name_t`, `name_o`, `street`, `street_t`, `lat`, `lng`, `latlng`, `geohash`, `phone`, `website`, `facebook_id`, `city_id`, `gd_city_id` FROM test_data;
Query OK, 127451 rows affected (53.041 sec)
Records: 127451  Duplicates: 0  Warnings: 0

MariaDB [temp_db]> INSERT table_MyISAM (`id`, `name`, `name_1`, `name_2`, `name_ms`, `name_ws`, `name_wa`, `name_t`, `name_o`, `street`, `street_t`, `lat`, `lng`, `latlng`, `geohash`, `phone`, `website`, `facebook_id`, `city_id`, `gd_city_id`) SELECT `id`, `name`, `name_1`, `name_2`, `name_ms`, `name_ws`, `name_wa`, `name_t`, `name_o`, `street`, `street_t`, `lat`, `lng`, `latlng`, `geohash`, `phone`, `website`, `facebook_id`, `city_id`, `gd_city_id` FROM test_data;
Query OK, 127451 rows affected (1.125 sec)
Records: 127451  Duplicates: 0  Warnings: 0


# alter tables with 127451 rows

MariaDB [temp_db]> alter table `table_InnoDB`
    -> add primary key (id),
    -> add index (street(50), name(50)),
    -> add index (street(50), city_id),
    -> add index (street(50), gd_city_id),
    -> add index (name(50), website),
    -> add index (website, city_id),
    -> add index (website, gd_city_id),
    -> add index (phone, website),
    -> add index (phone, name(50)),
    -> add index (latlng, name(50)),
    -> add index (city_id, name(50)),
    -> add index (gd_city_id, name(50)),
    -> add index (name_t(50), street(50)),
    -> add index (street_t(50), name_t(50)),
    -> add index (name_t(50), website),
    -> add index (name_t(50), phone);
Query OK, 0 rows affected (32 min 33.282 sec)       
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [temp_db]> alter table `table_MyISAM`
    -> add primary key (id),
    -> add index (street(50), name(50)),
    -> add index (street(50), city_id),
    -> add index (street(50), gd_city_id),
    -> add index (name(50), website),
    -> add index (website, city_id),
    -> add index (website, gd_city_id),
    -> add index (phone, website),
    -> add index (phone, name(50)),
    -> add index (latlng, name(50)),
    -> add index (city_id, name(50)),
    -> add index (gd_city_id, name(50)),
    -> add index (name_t(50), street(50)),
    -> add index (street_t(50), name_t(50)),
    -> add index (name_t(50), website),
    -> add index (name_t(50), phone);
Query OK, 127451 rows affected (5.976 sec)             
Records: 127451  Duplicates: 0  Warnings: 0

10.3.13-MariaDB 구성:

datadir=/var/lib/mysql
tmpdir = /mnt/ssd/tmp/ 
socket=/var/lib/mysql/mysql.sock

symbolic-links=1

key_buffer_size = 1G
max_connections = 300
max_user_connections = 280
table_open_cache = 42192
open_files_limit = 165832
max_allowed_packet = 16M
log_warnings = 0
extra_port = 3307
extra_max_connections = 4
sql_mode = NO_ENGINE_SUBSTITUTION

performance_schema = ON

###############innodb###############
innodb_file_per_table = ON
innodb_sort_buffer_size = 8M
innodb_buffer_pool_size = 15G
innodb_buffer_pool_instances = 15
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_log_file_size = 2G

innodb_flush_log_at_trx_commit = 2
####################################

sort_buffer_size = 12M
read_buffer_size = 2M
join_buffer_size = 4M

thread_cache_size = 256
thread_stack=512K

thread_handling = one-thread-per-connection
thread_pool_max_threads  = 500

read_rnd_buffer_size = 16M
net_buffer_length = 1048576
net_read_timeout = 600
net_write_timeout = 600

myisam_sort_buffer_size = 128M
aria_sort_buffer_size = 2G
aria_pagecache_buffer_size = 2G

query_cache_type = OFF

max_heap_table_size = 512M
tmp_table_size = 512M

character-set-server=utf8
collation-server=utf8_general_ci
init-connect="SET NAMES utf8"
default_storage_engine="MyISAM"
slow_query_log_file = /var/log/mariadb/mariadb-slow.log
long-query-time = 5
slow_query_log = 0

skip-name-resolve
low_priority_updates = 1

대체 가능한 SSD가 100% 사용 중이지만 수백 IOPS에 수 MB/s 읽기/쓰기만 가능합니다.

기타 테스트:서버 대 워크스테이션

서버(SSD는 대체 테이블 엔진 이후 100% 사용 중=InnoDB: 여러 라이트크론 스크립트 + 복제):

MariaDB [temp_db]> ALTER TABLE `temp_db`.`table_MyISAM` ADD COLUMN `tmp` VARCHAR(45) NOT NULL DEFAULT '' AFTER `gd_city_id`;
Query OK, 127451 rows affected (6.149 sec)             
Records: 127451  Duplicates: 0  Warnings: 0

MariaDB [temp_db]> ALTER TABLE `temp_db`.`table_InnoDB` ADD COLUMN `tmp` VARCHAR(45) NOT NULL DEFAULT '' AFTER `gd_city_id`;
Query OK, 0 rows affected (11.830 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [temp_db]> ALTER TABLE `table_MyISAM` DROP COLUMN `tmp`;
Query OK, 127451 rows affected (5.182 sec)             
Records: 127451  Duplicates: 0  Warnings: 0

MariaDB [temp_db]> ALTER TABLE `table_InnoDB` DROP COLUMN `tmp`;
Query OK, 0 rows affected (18 min 36.757 sec)
Records: 0  Duplicates: 0  Warnings: 0

내 PC:

MariaDB [test]> ALTER TABLE `table_MyISAM` ADD COLUMN `tmp` VARCHAR(45) NOT NULL DEFAULT '' AFTER `gd_city_id`;
Query OK, 127451 rows affected (2.897 sec)             
Records: 127451  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE `table_InnoDB` ADD COLUMN `tmp` VARCHAR(45) NOT NULL DEFAULT '' AFTER `gd_city_id`;
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE `table_MyISAM` DROP COLUMN `tmp`;
Query OK, 127451 rows affected (2.822 sec)             
Records: 127451  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE `table_InnoDB` DROP COLUMN `tmp`;
Query OK, 0 rows affected (2.879 sec)
Records: 0  Duplicates: 0  Warnings: 0

서버: 49GB RAM, 32개 CPU.

언급URL : https://stackoverflow.com/questions/55791739/too-long-alter-create-table-innodb

반응형

'programing' 카테고리의 다른 글

Internet Explorer 11 "스토리지 부족" 오류  (0) 2023.08.26
mysql 쿼리에서만 첫 번째 행 가져오기  (0) 2023.08.26
ImageDataGenerator 사용 시 Keras 분할 트레인 테스트 세트  (0) 2023.08.26
기존 도커 컨테이너에 볼륨을 추가하려면 어떻게 해야 합니까?  (0) 2023.08.26
시트 JS를 사용한 단일 시트의 여러 테이블  (0) 2023.08.26

'programing'의 다른글

  • 현재글테이블 변경/작성 InnoDB가 너무 깁니다.

관련글

댓글

티스토리툴바