쿼리 결과를 파일로 저장하기 위해서는 into outfile를 사용한다.
파일을 절대경로로 지정 가능하고 data폴더 아래 데이타베이스 폴더아래 생성된다.

mysql>select * into outfile 'filename' from table_name;

이번 기사에서는 Swynk.com의 필자인 Alexander Chigrik가 제안하는 대용량 SQL Server 관리자를 위한 다양한 팁 14가지에 대해 알아보자.

이번 내용에서는 잘 알려지지는 않았지만 SQL Server 환경에서 성능을 개선하기 위한 매우 유용한 방법들에 대해서 알아보자.

1. 가능한 테이블의 컬럼수는 작게 설계하는 것이 좋다.

테이블의 컬럼수가 적은 것은 당연히 I/O의 성능 향상에 도움이 된다. 당연히 쿼리 연결시 필요한 컬럼만을 가져와야 하며 작은 컬럼수의 설계는 백업이나 리스토어 등의 업무에 성능 개선을 준다.

2. 컬럼 타입을 text/ntext를 써야 하는 경우라면 varchar/nvarchar 컬럼 형태로 사용하는 것이 좋다.

왜냐하면 SQL Server는  text/ntext 컬럼의 경우 다른 데이터들과 분할에서 저장하게 되며 이 저장된 값이 필요할때는 다른 형식의 값들보다 오래걸리는 것은 당연한 이야기이다.

3. 만약 유니코드값이 입력되지 않는 컬럼 타입에 nchar/nvarchar를 써야 하는 경우라면 char/varchar를 고려하는 것이 좋다.

당연히 이러한 설계는 테이블 사이즈를 감소시킨다. 작은 테이블 사이즈는 I/O비용을 감소시키고 데이터베이스의 부하를 줄이면서 성능향상의 기본이 된다.

4. 만약 테이블 컬럼에 들어올 데이터의 크기가 4바이트 이하라면 varchar/nvarchar 컬럼 대신 char/nchar를  사용하는 것이 좋다.

고정된 값의 크기를 갖는 char 데이터 타입은 가변적인 값을 갖는 varchar 데이터 타입보다 작은 사이즈를 갖는다.

결국 char 데이터 타입을 사용하면 업데이트를 하거나 입력을 하는데 도움이 된다. 업데이트를 실행하게 되면 기존 값을 지우고 새로운 값을 입력하는 수정작업이 이뤄지게 되는데 이러한 물리적인 데이터의 수정작업은 사이즈가 작을 수록 성능 향상에 도움이 되는 것이다.

5. 데이터베이스의 테이블을 3정규화를 하는 것이 좋다.

만약 3정규화를 통한 테이블 설계가 이뤄지지 않고 종속관계를 포함하지 않거나 2정규화 등을 통해 테이블 설계가 이뤄진다면 많은 문제를 발생시킬 수 있다.

3정규화를 통해 여러 테이블의 조인비용등의 두려움으로 인해 반정규화를 한다는 것은 위에서 말한 심플한 컬럼 디자인과 쿼리시 필요한 컬럼만을 조인하는 방법등으로 얼마든지 성능개선을 할 수 있다.

6. 데이터베이스의 테이블이 4정규화와 5정규화를 요구한다면 반정규화를 고민해보는것도 좋다.

정규화에서 4정규화와 5정규화를 요구한다면 그 결과는 성능저하를 요인할 수 있다. 이런 경우 성능 개선을 위해 반정규화 설계를 고민해보는 것이 필요하다.

7. 만약 모든 테이블의 레코드를 지우는 삭제작업을 해야 한다면 DELETE 명령어를 통한 삭제보다는 TRUNCATE TABLE을 사용하는 것을 고려해 볼만 하다.

TRUNCATE TABLE를 사용하면 각각의 레코드를 지우며 로깅을 하는 DELETE 보다는 훨씬 빠른 방법이다.

8. 가능한 엔터프라이즈 메니저를 통해 원격 접속하여 관리하는 것은 서버에 많은 부담을 준다.

엔터프라이즈 매니저의 경우 많은 리소스를 요구하기 때문에 가능한 쿼리분석기등을 통한 제어가 부담을 줄일 수 있다.

9. PDC, BDC, 도메인 컨트롤러 또는 DNS서비스등이 설치된 곳에 설치하지 않는 것이 좋다.

도메인 컨틀롤러 등은 많은 오버헤드가 발생한다.  가능한 SQL Server 만을 독립적으로 설치해서 모든 자원이 서버만을 위해 사용되게 설계하는 것은 기본이다.

10. 성능 저하를 막기 위해 가능한 로컬 하드에 백업한 후 다른 백업장치로 옮기는게 좋다.

만약 백업을 하게 되면 SQL Server에서 몇가지 명령들을 사용할 수 없게 된다. 예를 들어 백업하는 동안 "ALTER DATABASE","ADD FILE","REMOVE FILE 옵션","shrink a database","CREATE INDEX","SELECT INTO" 등을 사용할 수 없다. 백업하는 시간이 오래걸리구 성능저하를 고려한다면 먼저 로컬 하드디스크에 백업한 후 백업 파일을 테이프 백업장치 등에 옮기는것이 좋다.

11. 가능한 로그를 남기지 않는 "nonlogged bulk copy"를 이용하는 것이 좋다.

"nonlogged bulk copy" 는 로그를 남기면서 대량 복사 작업을 하는 것보다 무척 빠르다. 하지만 이것을 이용하려면 다음을 충족시켜야 한다.

• 데이터베이스 옵션에서 "select into/bulkcopy"가 체크되어야 한다.
• 만들어질 테이블은 만들어져 있지 않아야 한다.
• 만들어질 테이블은 인덱스가 없어야 실행할 수 있다.

12. 가능한 "bcp" 또는 "DTS"를 이용하는 것보다 "BULK INSERT" 명령어를 통해 데이터를 옮기는 것이 좋다.

"BULK INSERT" 명령어를 이용하면 텍스트 파일로 생성한후 "DTS"를 통해서 값을 가져오거나 서버간 연결을 통해 "DTS"로 가져오는 것보다 훨씬 빠르다. 자신의 데이터가 수억레코드가 된다면 고려해 볼만한 내용이다.

13. SQL Server 테이블에 값을 넣거나 다른 데이터로 변환하고자 할때 DTS를 이용하는 것보다 bcp 유틸리티를 사용하는 것이 좋다.

bcp 유틸리티를 사용하는 것이 DTS를 사용하는 것보다 매우 빠르다.

14. 가능한 트랜잭션을 짧게 구성하는 것이 좋다.

트랜잭션 처리 작업이 짧게 이뤄지도록 설계하는것은 데드락을 발생시키는 것을 줄이는 당연한 이야기이다.

Mysql Replication을 구성하는 여러 구조의 예와 각각의 장단점을 정리한 문서

출처 : High Performance Mysql Ch7

(http://dev.mysql.com/books/hpmysql-excerpts/ch07.html)

Sample Configurations

Building on the four rules, let's begin by constructing simple replication configurations and discussing the types of problems they solve. We'll also look at the types of configurations that don't work because they violate the second rule. We'll use the simple configuration as a building block for arbitrarily complex architectures.

Each configuration is illustrated in a figure that includes the server ID of each server as well as its role: master, slave, or master/slave.

Master with slaves

The most basic replication model, a single master with one or more slaves, is illustrated in Figure 7-1. The master is given server ID 1 and each slave has a different ID.

Figure 7-1. Simple master/slave replication

This configuration is useful in situations in which you have few write queries and many reads. Using several slaves, you can effectively spread the workload among many servers. In fact, each of the slaves can be running other services, such as Apache. By following this model, you can scale horizontally with many servers. The only limit you are likely to hit is bandwidth from the master to the slaves. If you have 20 slaves, which each need to pull an average of 500 KB per second, that's a total of 10,000 KB/sec (or nearly 10 Mbits/sec) of bandwidth.

A 100-Mbit network should have little trouble with that volume, but if either the rate of updates to the master increases or you significantly increase the number of slaves, you run the risk of saturating even a 100-Mbit network. In this case, you need to consider gigabit network hardware or an alternative replication architecture, such as the pyramid described later.

Slave with two masters

It would be nice to use a single slave to handle two unrelated masters, as seen in Figure 7-2. That allows you to minimize hardware costs and still have a backup server for each master. However, it's a violation of the second rule: a slave can't have two masters.

Figure 7-2. A slave can't have two masters

To get around that limitation, you can run two copies of MySQL on the slave machine. Each MySQL instance is responsible for replicating a different master. In fact, there's no reason you couldn't do this for 5 or 10 distinct MySQL masters. As long as the slave has sufficient disk space, I/O, and CPU power to keep up with all the masters, you shouldn't have any problems.

Dual master

Another possibility is to have a pair of masters, as pictured in Figure 7-3. This is particularly useful when two geographically separate parts of an organization need write access to the same shared database. Using a dual-master design means that neither site has to endure the latency associated with a WAN connection.

Figure 7-3. Dual master replication

Furthermore, WAN connections are more likely to have brief interruptions or outages. When they occur, neither site will be without access to their data, and when the connection returns to normal, both masters will catch up from each other.

Of course, there are drawbacks to this setup. Section 7.7.3, later in this chapter, discusses some of the problems associated with a multi-master setup. However, if responsibility for your data is relatively well partitioned (site A writes only to customer records, and site B writes only to contract records) you may not have much to worry about.

A logical extension to the dual-master configuration is to add one or more slaves to each master, as pictured in Figure 7-4. This has the same benefits and drawbacks of a dual-master arrangement, but it also inherits the master/slave benefits at each site. With a slave available, there is no single point of failure. The slaves can be used to offload read-intensive queries that don't require the absolutely latest data.

Figure 7-4. Dual master replication with slaves

Replication ring (multi-master)

The dual-master configuration is really just a special case of the master ring configuration, shown in Figure 7-5. In a master ring, there are three or more masters that form a ring. Each server is a slave of one of its neighbors and a master to the other.

Figure 7-5. A replication ring or multi-master replication topology

The benefits of a replication ring are, like a dual-master setup, geographical. Each site has a master so it can update the database without incurring high network latencies. However, this convenience comes at a high price. Master rings are fragile; if a single master is unavailable for any reason, the ring is broken. Queries will flow around the ring only until they reach the break. Full service can't be restored until all nodes are online.

To mitigate the risk of a single node crashing and interrupting service to the ring, you can add one or more slaves at each site, as shown in Figure 7-6. But this does little to guard against a loss of connectivity.

Figure 7-6. A replication ring with slaves at each site

Pyramid

In large, geographically diverse organizations, there may be a single master that must be replicated to many smaller offices. Rather than configure each slave to contact the master directly, it may be more manageable to use a pyramid design as illustrated in Figure 7-7.

Figure 7-7. Using a pyramid of MySQL servers to distribute data

The main office in Chicago can host the master (1). A slave in London (2) might replicate from Chicago and also serve as a local master to slaves in Paris, France (4), and Frankfurt, Germany (5).

create database japan4;

use japan4

create table abc (
aaa varchar(10),
bbb int(4)
);

INSERT INTO abc VALUES ('aaa', 100);
INSERT INTO abc VALUES ('bbb', 200);

delimiter //

create procedure kimsh (IN num int)
BEGIN
IF num > 0 THEN
UPDATE abc SET bbb = bbb - 50;
ELSE
UPDATE abc SET bbb = bbb + 100;
END IF;
END
//

delimiter ;

call kimsh(1);

SELECT * FROM abc;

call kimsh(-1);

SELECT * FROM abc;

##################################

create table person (
name varchar(20),
age int(4)
);

delimiter //

create procedure kim (IN a varchar(20), IN b int, OUT c varchar(30))
BEGIN
INSERT INTO person values (a, b);
SELECT CONCAT('Hello', a);
END
//

delimiter ;

call kim ('kimsh', 20, @kk);