대량의 데이터를 처리해야하는 프로그램을 만들다보면 성능문제를 인식하지 않을 수 없다. JMeter를 이용하여 웹애플리케이션을 테스트 하면서 성능개선을 위해서 이것 저것 해보다가 오라클에서 제공하는 " Insert 문의 속도 "에 관한 문서를 발견하였다.
아래 그 문서의 내용이 있으며 insert문의 속도와 관련된 사항들 및 어떻게 속도를 높일 수 있는지에 대하여 설명을 하고 있다.
The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:
Connecting: (3)
Sending query to server: (2)
Parsing query: (2)
Inserting row: (1 × size of row)
Inserting indexes: (1 × number of indexes)
Closing: (1)
This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.
The size of the table slows down the insertion of indexes by
log N
, assuming B-tree indexes.
You can use the following methods to speed up inserts:
If you are inserting many rows from the same client at the
same time, use INSERT
statements with multiple VALUES
lists
to insert several rows at a time. This is considerably
faster (many times faster in some cases) than using
separate single-row INSERT
statements. If you are adding data to a nonempty table,
you can tune the
bulk_insert_buffer_size
variable to make data insertion even faster. See
Section 5.1.4, “Server System Variables”.
If multiple clients are inserting a lot of rows, you can
get higher speed by using the INSERT
DELAYED
statement. See
Section 13.2.5.2, “INSERT DELAYED Syntax”.
For a MyISAM
table, you can use
concurrent inserts to add rows at the same time that
SELECT
statements are
running, if there are no deleted rows in middle of the
data file. See Section 8.7.3, “Concurrent Inserts”.
When loading a table from a text file, use
LOAD DATA
INFILE
. This is usually 20 times faster than
using INSERT
statements.
See Section 13.2.6, “LOAD DATA INFILE Syntax”.
With some extra work, it is possible to make
LOAD DATA
INFILE
run even faster for a
MyISAM
table when the table has many
indexes. Use the following procedure:
Optionally create the table with
CREATE TABLE
.
Execute a FLUSH
TABLES
statement or a mysqladmin
flush-tables command.
Use myisamchk --keys-used=0 -rq
/path/to/db/tbl_name
.
This removes all use of indexes for the table.
Insert data into the table with
LOAD DATA
INFILE
. This does not update any indexes and
therefore is very fast.
If you intend only to read from the table in the future, use myisampack to compress it. See Section 14.5.3.3, “Compressed Table Characteristics”.
Re-create the indexes with myisamchk -rq
/path/to/db/tbl_name
.
This creates the index tree in memory before writing
it to disk, which is much faster that updating the
index during
LOAD DATA
INFILE
because it avoids lots of disk seeks.
The resulting index tree is also perfectly balanced.
Execute a FLUSH
TABLES
statement or a mysqladmin
flush-tables command.
LOAD DATA
INFILE
performs the preceding optimization
automatically if the MyISAM
table into
which you insert data is empty. The main difference
between automatic optimization and using the procedure
explicitly is that you can let
myisamchk allocate much more temporary
memory for the index creation than you might want the
server to allocate for index re-creation when it executes
the LOAD DATA
INFILE
statement.
You can also disable or enable the nonunique indexes for a
MyISAM
table by using the following
statements rather than myisamchk. If
you use these statements, you can skip the
FLUSH
TABLE
operations:
ALTER TABLEtbl_name
DISABLE KEYS; ALTER TABLEtbl_name
ENABLE KEYS;
To speed up INSERT
operations that are performed with multiple statements for
nontransactional tables, lock your tables:
LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES (8,26),(6,29); ... UNLOCK TABLES;
This benefits performance because the index buffer is
flushed to disk only once, after all
INSERT
statements have
completed. Normally, there would be as many index buffer
flushes as there are INSERT
statements. Explicit locking statements are not needed if
you can insert all rows with a single
INSERT
.
To obtain faster insertions for transactional tables, you
should use START
TRANSACTION
and
COMMIT
instead of
LOCK TABLES
.
Locking also lowers the total time for multiple-connection tests, although the maximum wait time for individual connections might go up because they wait for locks. Suppose that five clients attempt to perform inserts simultaneously as follows:
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
If you do not use locking, connections 2, 3, and 4 finish before 1 and 5. If you use locking, connections 2, 3, and 4 probably do not finish before 1 or 5, but the total time should be about 40% faster.
INSERT
,
UPDATE
, and
DELETE
operations are very
fast in MySQL, but you can obtain better overall
performance by adding locks around everything that does
more than about five successive inserts or updates. If you
do very many successive inserts, you could do a
LOCK TABLES
followed by an
UNLOCK
TABLES
once in a while (each 1,000 rows or so)
to permit other threads to access table. This would still
result in a nice performance gain.
INSERT
is still much slower
for loading data than
LOAD DATA
INFILE
, even when using the strategies just
outlined.
To increase performance for MyISAM
tables, for both
LOAD DATA
INFILE
and
INSERT
, enlarge the key
cache by increasing the
key_buffer_size
system
variable. See Section 8.9.2, “Tuning Server Parameters”.
[오라클/SQL] 컬럼에 외래키 제약 추가하기 ( ALTER TABLE ) (0) | 2015.04.20 |
---|---|
[오라클/SQL] 테이블에 컬럽 추가/삭제 하기 (0) | 2015.04.20 |
[오라클/oracle] Partitioned Tables and Indexes ( 테이블 및 인덱스 파티션 ) (0) | 2015.04.20 |
[오라클/oracle] 데이타 베이스 파일 수정하기 (0) | 2015.04.20 |
[오라클/oracle] 데이타베이스 파일 조회하기 (0) | 2015.04.20 |