안녕하세요.

mariadb를 이용해서 한창 데이터베이스를 배우고 있는 초짜입니다.

​

파이썬으로 웹페이지 크롤링을 연습하고 있는데요.

뜬금없이 데이터베이스 처리에서 막혀서 진도가 안나가고 있습니다.

이에 질문드려봅니다.

​

​

​

[현상]

전부 bulk insert로 데이터를 넣고 있습니다.

상대적으로 데이터량이 작은 키워드 테이블은 막힘없이 들어가고 있구요.

주소테이블까지도 쿼리길이 기준으로 300,000자정도의 크기인데 잘 들어가고 있습니다.

문제는 내용 테이블인데요. 4스레드로 파이썬에서 밀어넣으면 update/commit단계에서

짧게는 10초 길게는 1000초정도까지 지연이 생깁니다.

​

​

​

[테이블의 구조]

​

키워드 테이블의 구조

IDX : bigint 프라이머리 키

KEYWORD : varchar(255) 키워드가 들어갑니다. 유니크 키

PROC_URL : tinyint 해당 키워드에 대한 작업이 되었는지 판별 (0:작업전/1:주소수집완료)

​

주소 테이블의 구조

IDX : bigint 프라이머리 키

URL : varchar(255) 크롤링할 주소가 들어갑니다.

TITLE : varchar(255) 게시물의 제목이 들어갑니다. full-text 엔진

PROC : 해당 URL의 내용을 가져왔는지 판별 (0:작업전/1:내용수집완료)

​

내용 테이블의 구조

IDX : bigint 프라이머리 키

내용 : midtext 본문의 내용이 들어갑니다. full-text 엔진

​

혹시 관계성 검증때문에 lock이 생긴게 아닌가하여,

각 테이블간에 foreign키는 전부 생략해놓은 상태입니다.

​

​

[자세한 설명]

일 처리 단계는 다음과 같습니다.

1. 키워드 프로그램

파이썬에서 키워드 수집 -> php의 insert.php 호출 -> mariadb에서 처리

한번에 삽입되는 쿼리의 양은 쿼리문기준으로 100,000글자 미만입니다.

​

2. 주소수집프로그램

keylist.php(키워드테이블 PROC=0인것들 IDX기준 ASC)에서 10개씩 불러옴

-> 파이썬에서 작업완료 -> php의 keyinsert.php 호출 -> mariadb에서 처리

한번에 삽입되는 쿼리의 양은 쿼리문기준으로 100,000글자 미만입니다.

​

3. 내용수집프로그램

urllist.php(주소테이블 PROC=0인것들 IDX기준 ASC)에서 10개씩 불러옴

-> 파이썬에서 작업완료 -> php의 urlinsert.php 호출 -> mariadb에서 처리

한번에 삽입되는 쿼리의 양은 쿼리문기준으로 1,000,000글자 미만입니다.

​

이런 과정입니다.

1, 2, 3번의 파이썬 프로그램이 실시간으로 계속 돌고 있는 상태입니다. (프로그램당 4쓰레드)

​

​

​

[insert 쿼리문]

1. 키워드 프로그램

INSERT IGNORE INTO KEYWORD_0 (KEYWORD_KEYWORD, KEYWORD_DATE, KEYWORD_SITE, KEYWORD_HIGH) VALUES (),(),(), ... 10개

​

2. 주소 프로그램

INSERT INTO URL_0 (URL_TITLE, URL_LINK, URL_DATE, URL_REF_IDX, URL_HIGH) VALUES (),(),()...

UPDATE KEYWORD_0 SET KEYWORD_PROC_URL='1' WHERE KEYWORD_IDX IN (1,2,3,4,5,6,7,...) // 작업이 완료되었다는 표시

UPDATE KEYWORD_0 SET KEYWORD_DEL='1' WHERE KEYWORD_IDX IN (1,2,3,4,5,6,7,...) // 관련 주소가 없는 경우 표시

​

3. 본문 프로그램

INSERT INTO PAGE_0(PAGE_CONTENT, PAGE_IMG, PAGE_SUBKEYWORD, PAGE_DATE, PAGE_REF_IDX) VALUES (), (), (), ... 10개

UPDATE URL_0 SET URL_PROC=1 WHERE URL_IDX IN (1,2,3,4,5,6,7,8,9,10) // 작업이 완료되었다는 표시

UPDATE URL_0 SET URL_DEL=1 WHERE URL_IDX IN (1,2,3,...) // 해당 주소에 본문이 없다면 표시

​

​

​

[my.cnf]

[client-server]

# Import all .cnf files from configuration directory

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mariadb.conf.d/

[mysqld]

datadir = /database/mysql

socket = /var/run/mysqld/mysqld.sock

pid-file = /var/run/mysqld/mydqld.pid

max_connection = 100

#innodb_thread_concurrency = 0

#innodb_read_io_threads = 64

#innodb_write_io_threads = 64

#innodb_adaptive_hash_index = ON

#connect_timeout = 900

#wait_timeout = 7200

max_allowed_packet = 16M

#thread_stack = 256K

#thread_cache_size = 128

sort_buffer_size = 256K

join_buffer_size = 256K

read_buffer_size = 256K

#read_md_buffer_size = 256K

#net_buffer_length = 16K

#bulk_insert_buffer_size = 4000M // innodb라서 필요가 없는듯 해요.

tmp_table_size = 1M

max_heap_table_size = 32M

#max_open_files = 40000

#wait_timeout = 30

query_cache_size = 0

query_cache_type = OFF

query_cache_limit = 2M

#open_files_limit = 65535

innodb_flush_log_at_trx_commit = 0

innodb_buffer_pool_size = 8000M

innodb_buffer_pool_instances = 4

innodb_autoinc_lock_mode = 1

innodb_log_file_size = 256M

innodb_log_buffer_size = 8M

innodb_flush_method = O_DIRECT

#table_definition_cache = 4000

#table_open_cache = 40000

#open_files_limit = 120000

#binlog_cache_size = 16M

#max_binlog_size = 128M

#innodb_lock_wait_timeout = 10

#innodb_additional_mem_pool_size = 2M

#innodb_log_file_size

#skip-grant-tables

#symbolic-links=0

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

slow_query_log = 1

slow_query_log_file = /database/log/slow_query.log

long_query_time = 3

#autocommit = on

transaction_isolation = 'READ-COMMITTED'

#performance_schema = 1

innodb_locks_unsafe_for_binlog = on

​

​

​

[지연될때 트랜잭션 정보]

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 19004

--Thread 140032845915904 has waited at fts0fts.cc line 4850 for 34.00 seconds the semaphore:

X-lock on RW-latch at 0x556d6cf6bdd8 created in file fts0fts.cc line 617

a writer (thread id 140032511084288) has reserved it in mode exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time write locked in file fts0fts.cc line 4850

--Thread 140032510777088 has waited at fts0fts.cc line 4850 for 34.00 seconds the semaphore:

X-lock on RW-latch at 0x556d6cf6bdd8 created in file fts0fts.cc line 617

a writer (thread id 140032511084288) has reserved it in mode exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time write locked in file fts0fts.cc line 4850

--Thread 140032845608704 has waited at fts0fts.cc line 4850 for 35.00 seconds the semaphore:

X-lock on RW-latch at 0x556d6cf6bdd8 created in file fts0fts.cc line 617

a writer (thread id 140032511084288) has reserved it in mode exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time write locked in file fts0fts.cc line 4850

OS WAIT ARRAY INFO: signal count 17555

RW-shared spins 13013, rounds 388535, OS waits 12823

RW-excl spins 693, rounds 20972, OS waits 690

RW-sx spins 1253, rounds 36857, OS waits 1173

Spin rounds per wait: 29.86 RW-shared, 30.26 RW-excl, 29.42 RW-sx

​

------------

TRANSACTIONS

------------

Trx id counter 9792189

Purge done for trx's n:o < 9792180 undo n:o < 0 state: running

History list length 27

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421507487839432, not started

0 lock struct(s), heap size 1128, 0 row lock(s)

---TRANSACTION 9792188, ACTIVE 34 sec inserting

mysql tables in use 1, locked 1

0 lock struct(s), heap size 1128, 0 row lock(s)

MySQL thread id 55, OS thread handle 140032510777088, query id 121 localhost root Update

INSERT INTO PAGE_0(... 중략)

---TRANSACTION 9792187, ACTIVE 34 sec inserting

mysql tables in use 1, locked 1

0 lock struct(s), heap size 1128, 0 row lock(s)

MySQL thread id 54, OS thread handle 140032845915904, query id 120 localhost root Update

INSERT INTO PAGE_0(... 중략)

---TRANSACTION 9792186, ACTIVE 35 sec inserting

mysql tables in use 1, locked 1

0 lock struct(s), heap size 1128, 0 row lock(s)

MySQL thread id 53, OS thread handle 140032845608704, query id 119 localhost root Update

INSERT INTO PAGE_0(...중략)

---TRANSACTION 9792185, ACTIVE 35 sec fetching indexed FTS document

0 lock struct(s), heap size 1128, 0 row lock(s)

Trx read view will not see trx with id >= 9792186, sees < 9792170

---TRANSACTION 9792182, ACTIVE 35 sec inserting

mysql tables in use 1, locked 1

0 lock struct(s), heap size 1128, 0 row lock(s)

MySQL thread id 52, OS thread handle 140032511084288, query id 118 localhost root Update

INSERT INTO PAGE_0((..중략)

---TRANSACTION 421507487822568, not started

0 lock struct(s), heap size 1128, 0 row lock(s)

---TRANSACTION 9792174, ACTIVE 1143 sec recovered trx

ROLLING BACK 1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 980262

---TRANSACTION 9792171, ACTIVE 1143 sec recovered trx

1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 10

---TRANSACTION 9792170, ACTIVE 1143 sec recovered trx

1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 10

​

​

​

장문의 글 읽어주셔서 감사드리구요.

부디 초보가 힘을 얻을수 있도록 도움 부탁드립니다.

감사합니다.