Monthly Archives: January 2008

DB #5 : [MySQL][Replication] Improving Replication Performance

As the number of slaves connecting to a master increases, the load, although minimal, also increases, as each slave uses up a client connection to the master. Also, as each slave must receive a full copy of the master binary log, the network load on the master may also increase
and start to create a bottleneck.

If you are using a large number of slaves connected to one master, and that master is also busy processing requests (for example, as part of a scaleout solution), then you may want to improve the performance of the replication process.

One way to improve the performance of the replication process is to create a deeper replication structure that enables the master to replicate to only one slave, and for the remaining slaves to connect to this primary slave for their individual replication requirements. A sample of this
structure is shown in Figure 14.3, “Using an additional replication host to improve performance”.


For this to work, you must configure the MySQL instances as follows:

  • Master 1 is the primary master where all changes and updates are written to the database. Binary logging should be enabled on this machine.
  • Master 2 is the slave to the Master 1 that provides the replication functionality to the remainder of the slaves in the replication structure. Master 2 is the only machine allowed to connect to Master 1. Master 2 also has binary logging enabled, and the –log-slave-updates option so that replication instructions from Master 1 are also written to Master 2’s binary log so that they can then be replicated to the true slaves.
  • Slave 1, Slave 2, and Slave 3 act as slaves to Master 2, and replicate the information from Master 2, which is really the data logged on Master 1.

The above solution reduces the client load and the network interface load on the primary master, which should improve the overall performance of the primary master when used as a direct database solution.

If your slaves are having trouble keeping up with the replication process on the master then there are a number of options available:

  • If possible, you should put the relay logs and the data files on different physical drives. To do this, use the –relay-log option to specify the location of the relay log.
  • If the slaves are significantly slower than the master, then you may want to divide up the responsibility for replicating different databases to different slaves. See Section 14.2.4, “Replicating Different Databases to Different Slaves”.
  • If your master makes use of transactions and you are not concerned about transaction support on your slaves, then use MyISAM or another non-transactional engine. See Section 14.2.2, “Using Replication with Different Master and Slave Storage Engines”.
  • If your slaves are not acting as masters, and you have a potential solution in place to ensure that you can bring up a master in the event of failure, then you can switch off –log-slave-updates. This prevents ‘dumb’ slaves from also logging events they have executed into their own binary log.

Master에 접속하고 있는 slaves 수가 증가하면, 약간의 부하도 어느정도 증가하여, 각각의 slave가 master로의 client connection을 전부 써 버립니다. 게다가, 각각의 slave는 master의 binary log의 완전한 copy를 받을 필요가 있기 때문에, master network load도 같이 증가하여, bottleneck이 발생 system 전체 성능이 저하합니다.

Scale-out solution등으로 master에 접속하고 있는 slave 수가 많을 때는, 그것에 맞춰 master의 처리량을 증대 시키기 때문에, Replication process의 performance를 개선할 것을 추천합니다.

Replication process의 performance를 개선하는 방법의 하나로, 보다 깊은 Replication structure를 구성하는 것이 있습니다. 이것은 master가 1개의 slave만 복제를 행하고, 다른 slave는 개별 replication 요구로 대응하는 primary slave에 접속하는 방법입니다. 이 structure sample는 그림, 5.8 “추가 Replication host로 performance 개선”을 참조 해 주세요.

이것을 실현하기 위해, MySQL instances를 다음과 같이 설정합니다.

  • Master 1은 primary master로 이 database에 모든 change와 update가 write됩니다. Binary logging은 이 machine에서 실행가능합니다.
  • Master 2는 Master1의 slave입니다. Master 1은 replication structure에 있어, Replication의 기능성을 slave의 잔여분으로 제공합니다. 여기서 Master 2는 Master 1에 단일 접속하는 machine입니다. Master 2는 binary logging가 가능한 상태입니다. –log-slave-updates option으로 Master 1으로부터 복제지시가 Master 2의 binary log에 write되고, 이것에 의해, 양자가 slave 복제하게 됩니다.
  • Slave 1, Slave 2, Slave 3는 Master 2의 slave로써 가동되고, Master 2로부터 정보를 복제하지만, 실제는 Master 1의 log data입니다.

이 solution은 primary master의 client load뿐만이 아니라, Network interface load를 줄이는게 가능하고, primary master의 performance 전체를 개선하는 direct database solution으로 활용가능합니다.

Master의 replication process에 있어 문제를 일으키는 slave가 있는 경우에는 다음 option으로 대응합니다.

  • Relay log와 data files을 가능한 한 물리적으로 독립된 drive에 분리합니다. 그렇게 하기위해, –relay-log option을 사용하여, relay log의 보관장소를 지정합니다.
  • Slave가 master보다 매우 느린 경우는, database 종류에 맞춰 복제 역활을 다른 slave에 나눠주는 것을 추천합니다. 자세한 내용은 5.3.4. “다른 database로부터 다른 slave로의 replication”을 참조 하세요.
  • Master transaction을 활용하고, slave가 그 transaction support를 하고 있는 지 어떤지를 확인하기 위해, MyISAM 또는 그 외의 non-transaction engine을 사용합니다. 자세한 내용은 5.3.2 “Storage engine가 다른 master와 slave의 replication”을 참조 하세요

Slaves가 masters처럼 가동하고 있지 않는 상황에서, 대처가능한 방법이 있고, 장애 event 중의 master를 조작 할 수 있는 경우에는, –log-slave-updates option을 사용합니다. 이것은 “dumb(처리능력없음)” slave가 또, 각각의 binary log에 실행했던 event를 기록하는 것을 방지합니다.


DB #4 : [MySQL][InnoDB] Adding and Removing InnoDB Data and Log Files

This section describes what you can do when your InnoDB tablespace runs out of room or when you want to change the size of the log files.

The easiest way to increase the size of the InnoDB tablespace is to configure it from the beginning to be auto-extending. Specify the autoextend attribute for the last data file in the tablespace definition. Then InnoDB increases the size of that file automatically in 8MB increments when it runs out of space. The increment size can be changed by setting the value of the innodb_autoextend_increment system variable, which is measured in MB.

lternatively, you can increase the size of your tablespace by adding another data file. To do this, you have to shut down the MySQL server, change the tablespace configuration to add a new data file to the end of innodb_data_file_path, and start the server again.

If your last data file was defined with the keyword autoextend, the procedure for reconfiguring the tablespace must take into account the size to which the last data file has grown. Obtain the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file. Remember that only the last data file in the innodb_data_file_path can be specified as auto-extending.

As an example, assume that the tablespace has just one auto-extending data file ibdata1:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to not be auto-extending and adding another auto-extending data file:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

When you add a new file to the tablespace configuration, make sure that it does not exist. InnoDB will create and initialize the file when you restart the server.

Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:

1. Use mysqldump to dump all your InnoDB tables.

2. Stop the server.

3. Remove all the existing tablespace files, including the ibdata and ib_log files. 
If you want to keep a backup copy of the information, then copy all the ib* files 
to another location before the removing the files in your MySQL installation.

4. Remove any .frm files for InnoDB tables.

5. Configure a new tablespace.

6. Restart the server.

7. Import the dump files.

If you want to change the number or the size of your InnoDB log files, use the following instructions. The procedure to use depends on the value of innodb_fast_shutdown:

If innodb_fast_shutdown is not set to 2: You must stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the logs). Then copy the old log files into a safe place just in case something went wrong in the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, edit my.cnf to change the log file configuration, and start the MySQL server again. mysqld sees that no log files exist at startup and tells you that it is creating new ones.

If innodb_fast_shutdown is set to 2: You should shut down the server, set innodb_fast_shutdown to 1, and restart the server. The server should be allowed to recover. Then you should shut down the server again and follow the procedure described in the preceding item to change InnoDB log file size. Set innodb_fast_shutdown back to 2 and restart the server.

이 Session은 InnoDB Tablespace가 Space를 전부 사용해 버리거나, Log files size를 변경하고 싶을 때에 무엇이 가능한 지를 설명하고 있습니다.

DB Tablespace Size를 증설하는 가장 간단한 방법은, 처음부터 이것을 autoextend increment Mode롤 설정하는 것입니다. Tablespace 정의내의 마지막 data file의 autoextend 속성을 지정 해 주세요.
그럼, InnoDB는 영역을 전부 사용해 버렸을때, 그 file size를 자동적으로 8MB Increment 증설. Increment size는, MB 단위로 innodb_autoextend_increment system 변수갓을 설정하는 것으로 변경 가능합니다.

또는, 다른 data file을 추가하는 것으로 tablespsace size를 증가시는 것이 가능합니다. 이 작업을 하기 위해, MySQL Server를 Shutdown 후, innodb_data_file_path의 마지막에 새로운 data file을 추가하는 것으로 tablespace 설정을 변경하고, MySQL Server를 재기동합니다.

만약 최후의 data file이 keyword autoextend로 정의 되어 있다면, tablespace 재설정 순서는 마지막의 data file이 어느 정도의 size까지 증설할지 고려할 필요가 있습니다. table file size를 위해, 그것을 1024 x 1024bytes (=1MB)의 배수 근접치까지 맞추고, 맞춘 size를 innodb_data_file_path내에 명시적으로 지정하세요. 그럼, 다른 data file을 추가하는 것이 가능합니다. innodb_data_file_path 내의 마지막 data file만이 autoextend increment로써 지정 가능하다는 것 기억하세요.

한가지 예를 들면, tablespace가 1개만 autoextend increment data file ibdata1을 가지고 있다고 가정:

이 data file이 오랜시간에 걸쳐 988MB까지 확장되었다고 가정 해 주세요. 여기서, 원래 data file을 Un-autoextend increment로 변경하고, 다른 autoextend increment file을 추가한 후 설정 Line이 있습니다 :

data space설정에서 새로운 file을 추가할 때에는, 그것이 존재하고 있지 않은 지 확인 하세요. InnoDB는 Server를 재기동 할 때 file을 작성, 초기화 합니다.

현재, data file을 tablespace로부터 삭제하는 것은 불가능. tablespace size를 작게하기 위해서는, 아래 방법을 이용하세요:

1. 모든 InnoDB table을 dump하기 위해 mysqldump를 이용하세요.

2. MySQL Server를 정지 해 주세요.

3. ibdata, ib_log files을 포함하는 모든 존재하는 tablespace files을 삭제해 주세요.

만약 그 정보를 backup 하기를원한다면, 당신의 MySQL 설치관련 files을 삭제하기 전에 다른 위치에

모든 ib* files을 copy 하세요.

4. 모든 *.frm 확장자를 가진 files을 삭제 해 주세요.

5. 새로운 tablespace를 설정 해 주세요.

6. MySQL Server를 재기동 해 주세요.

7. dump files을 inport 해 주세요.

InnoDB log files 수와 size를 변경 하고 싶으면, 다음 지시를 따라주세요. 이용 방법은 innodb_fast_shutdown 값에 의해 결정됩니다:

만약 innodb_fast_shutdown이 2로 설정되어 있지 않으면: MySQL Server을 정지하고, Error 없이 shutdown 되었는지 확인 할 필요가 있습니다.(log 안에 미처리 transaction 정보가 없는것을 보증하기 위해) shutdown 시에 문제가 발생했을 경우, tablespace를 복구하기 위해 필요함으로,
오래된 log files을 안전한 장소에 copy 해 두세요. 오래된 log files을 log file directory로부터 삭제하고, log file설정을 변경하기 위해 my.cnf를 편집하고, MySQL server를 재기동 해주세요. mysqld는 log files이 존재하지 않는 것을 확인하고, 새로운 것을 작성하고 있다고 보고 합니다.

만약 innodb_fast_shutdown이 2로 설정되어 있으면: MySQL Server를 shutdown하고, innodb_fast_shutdown을 1로 설정하고, MySQL Server를 재기동 하세요.

MySQL Server는 복구를 허가합니다. 그리고, MySQL Server를 다시 한번 shutdown하고, InnoDB log file size를 변경하기위해 앞에서 설명하고 있는 방법을 반드시 따라주세요. innodb_fast_shutdown을 2로 다시 설정하고, MySQL Server 를 재설정 하세요.

DB #1 : [MySQL][InnoDB] Raw Device 대응에 대해서

[아래내용은 사이트에서 인용 하였습니다]

Buffer로서도 File로서도, 영역확보 시에 반드시 Access 단위(Page Size 혹은 Sector Size) 1개분 여분을 취득해서, align합니다.
Oracle은 Parameter 혹은 무언가로 설정해서 사용한다는 이야기가 있습니다만(추후 확인), InnoDB의 경우는 이러한 User측의 제어 방법은 없고, Raw Device를 사용여부에 관계없이, Raw Device를 사용하고 있어서 문제없는Code가 실행 됩니다.

Raw Device를 다시 집어보면 Kernel의 IO Buffer를 우회하여 Device에 대해 직접적인 Read/Write를 행하기 위해, Kernel이 Application에게 제공하는 Service 구조라고 할까요.

스스로 Buffering을 하는 Software의 경우, 이 경우가 Overhead가 줄고, “ACID를 위한 동기 Write가 필요”한 경우에 더욱 편리하다고 하네요. (fsync ?)

Raw Device가 장래 Linux에서 사라질지 모른다라는 “System Call Open시에 O_DIRECT를 지정하기 위해 Source Code를 바꿔 써야할 필요가….”
만약 그렇게 되면 InnoDB도 Source를 수정하지 않으면 안된다는.. O_RDWR지정 뿐이므로 (O_RDWR ? )

You can use raw disk partitions as data files in the shared tablespace. By using a raw disk, you can perform non-buffered I/O on Windows and on some Unix systems without filesystem overhead, which may improve performance.

When using the InnoDB storage engine with a large innodb_buffer_pool_size value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), a significant performance gain can be achieved by placing InnoDB data files and log files on raw devices or on a separate direct I/O UFS filesystem (using mount option forcedirectio; see mount_ufs(1M)). Users of the Veritas filesystem VxFS should use the mount option convosync=direct.

Question or Comment

1. InnoDB의 O_DIRECT 대응은 innodb_flush_method=O_DIRECT
    Response가 느려지는 결과가 초래... (Many Practice)
2. Raw Device를 사용하면 InnoDB의 성능이 향상 되는가?
    Disk Access에 걸리는 시간의 대부분은 Disk Access입니다. (Memory로의 Copy나, Cache Searching등은 상당히 적은 시간으로, Disk Access의 시간은 기본적으로 Paging와 Read/Write Transfer 시간입니다.

Read/Write Transfer는 어쩔 수 없는 부분이고, Paging를 전체적인 시야로 어떻게 줄이느냐가 성능을 향상시키는 중요한 Point 입니다.)

Linux 2.6 Kernel에서는 Paging하고 Disk Access할 때에 가능한 한 Data의 물리적배치순으로 Request를 재배치하고 Disk Access를 실행합니다. (어떤 Timing에 그걸 실행 할 지는 elevator= 등의 지정에 의함) 그러므로, Local Device등과 같은 Sequential Access 위주의 IO Type에 강한 Device에서는 Head의 이동이 최소한으로 제어할 수 있습니다. 또, 고가의 RAID Storage에서는 스스로 이와 같은 Access를 수정해 주기때문에 Buffering 여부와 관계없이 그 차이는 거의 없다고 볼 수 있습니다. 즉, OS의 Buffering를 Skip하고 Disk Access를 하면, Local Device등이 느려질 가능성이 있고, 후에 Access자체가 빨라질 가능성도 희박하다고 볼 수 있습니다.

그럼, RAW Device 혹은 O_DIRECT는 무엇인가? 주목해야 할 점은 두 가지..

- Memory의 절약
- Data의 정합성

Memory의 절약은 Buffer를 쓰지않으므로 Memory 절약이 가능한 만큼 Buffer pool에 비중을 높여 Cache hit rate를 올리는 것이 가능할 지도 모르겠지만, 최근엔 Memory를 많이 탑재하고 있어서 그다지 ..
Data의 정합성은 성능은 아닙니다. 복수의 서버에서 Disk를 동시에 사용할 경우(예. Oracle RAC) 필수. 현재로써는 Raw Device 혹은 O_DIRECT는 MySQL에서 사용해도 그다지 Merit가 없다라는게 의견.

추가로, Oracle에서도 Raw Device이용은 Option은 필요없습니다. Option이 필요한 건 O_DIRECT와 비동기 IO입니다. Oracle의 경우, 원래 물리배치를 고려 후 Access 이므로 그다지 변화가 없다라는 의견이 많습니다. 비동기 IO는 효과가 있다고 합니다만. InnoDB는 기본적으로 스스로 비동기 IO와 같은 움직임을 하고 있는 것 같아 보입니다.

Raw Device의 이용이 반드시 좋다고 할 수 없는 이유는 Kernel이 IO Scheduler를 사용해 효율 좋게 정리해서 Write를 해 준다는 것 입니다. (App=MySQL과 Kernel에서 2중으로 Buffering을 하여 Overhead하는 것 보다, 정리해서 IO를 사용하는 것이 효율성이 좋다)
Kernel Cache를 사용하면서, ACID 보증을 위한 Data Write를 어떻게 할 것인가..
Battery backup이 있는 Write Cache 대응의 RAID Controller를 사용하면서, O_DIRECT를 지정한다 등.. 성능도 ACID도 OK 현실적으로는 그 이상 필요가 없을지도..

DB #3 : [MySQL][InnoDB] File Space Management

The data files that you define in the configuration file form the tablespace of InnoDB. The files are simply concatenated to form the tablespace. There is no striping in use. Currently, you cannot define where within the tablespace your tables are allocated. However, in a newly created tablespace, InnoDB allocates space starting from the first data file.

The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of 64 consecutive pages. The “files” inside a tablespace are called segments in InnoDB. The term “rollback segment” is somewhat confusing because it actually contains many
tablespace segments.

Two segments are allocated for each index in InnoDB. One is for non-leaf nodes of the B-tree, the other is for the leaf nodes. The idea here is to achieve better sequentiality for the leaf nodes, which contain the data.

When a segment grows inside the tablespace, InnoDB allocates the first 32 pages to it individually. After that InnoDB starts to allocate whole extents to the segment. InnoDB can add to a large segment up to 4 extents at a time to ensure good sequentiality of data.

Some pages in the tablespace contain bitmaps of other pages, and therefore a few extents in an InnoDB tablespace cannot be allocated to segments as a whole, but only as individual pages.

When you ask for available free space in the tablespace by issuing a SHOW TABLE STATUS statement, InnoDB reports the extents that are definitely free in the tablespace. InnoDB always reserves some extents for cleanup and other internal purposes; these reserved extents are not included in the free space.

When you delete data from a table, InnoDB contracts the corresponding B-tree indexes. Whether the freed space becomes available for other users depends on whether the pattern of deletes frees individual pages or extents to the tablespace. Dropping a table or deleting all rows from it
is guaranteed to release the space to other users, but remember that deleted rows are physically removed only in an (automatic) purge operation after they are no longer needed for transaction rollbacks or consistent reads. (See Section 12.5.12, “Implementation of Multi-Versioning”.)

설정file을 정의하는 datafile로부터, InnoDB의 tablespace가 구성 됨. 이 file들은, 단순하게 연결된 tablespace가 됨. striping은 사용하지 않음. 현시점에서, tablespace의 어떤 위치에 table이 할
당되어있는지는 정의 불가능함. 그러나, 새롭게 작성되는 tablespace내에서는 InnoDB가 최초의datafile부터 영역을 할당함.

Tablespace는, default size가 16KB의 database page로 구성. 이 page들은, 64개의 연속한 page로부터 extents 되어 group화함. InnoDB는, tablespace내부의 “files”을 segment라고 부름. 이것은 실제로 많은 tablespace segment를 포함하고 있기 때문에, “rollback segment”라는 이름은, 다소 오해를 일으킬 수 있음.

InnoDB는, 각 index에 2개의 segment를 할당함. 1개는 B Tree의 non-leaf nodes용, 다른 1개는 leaf nodes용. 이것에는, data를 포함하고 있는 leaf nodes에서 연속성을 높이는 의미.

Tablespace내의 segment가 커지면, InnoDB는 그 segment에 최초의 32page를 별도로 할당. InnoDB는 그 후, extents 전체를 segment에 할당하기 시작함. InnoDB는 data의 연속성을 확보하기 위해, 큰 segment에 한번에 최대 4개의 extents를 추가 가능함.

Tablespace에는, 다른 page의 hitmap을 포함한 page가 있기때문에, InnoDB tablespace내의 몇몇의 extents는, 전체가 아닌 별도의 page만 segment에 할당 하는 것이 가능함.

SHOW TABLE STAUS statement를 발행하고 tablespace내의 빈 영역을 조회하면, InnoDB로부터 tablespace내의 완전하게 비어있는 extents가 보고 됨. InnoDB는, 항상 몇몇의 extents를 clean up, 그 외의 내부적인 용도를 위해 확보하기도 하고, 이 extents은 빈 영역에 포함하지 않음.

Table에서 data를 삭제하면, InnoDB에 따라 대응한 B Tree index가 축소됨. 이 것에 따라, 다른 user가 개방된 영역을 사용가능하도록 할지 어떨지는, 삭제 pattern이 tablespace의 가각의 page나 extents를 개방할지 어떨지에 따라 달라짐. table를 파괴하거나, 또는 table로부터 모든 행을 삭제하면, 다른 user에 확실하게 영역이 개방되지만, 삭제된 행은, transaction rollback 또는 일괄적
인 read로 그 record가 필요없어 진 후의 page 조작에서 처음으로 물리적으로 삭제되는 것에 주의해 주세요. (자세한 것은 “Multi version 실행”을 참조 해 주세요.)

DB #2 : [MySQL][InnoDB] About DISK I/O

InnoDB uses simulated asynchronous disk I/O: InnoDB creates a number of threads to take care of I/O operations, such as read-ahead.

There are two read-ahead heuristics in InnoDB:

In sequential read-ahead, if InnoDB notices that the access pattern to a segment in the tablespace is sequential, it posts in advance a batch of reads of database pages to the I/O system.

In random read-ahead, if InnoDB notices that some area in a tablespace seems to be in the process of being fully read into the buffer pool, it posts the remaining reads to the I/O system.

InnoDB uses a novel file flush technique called doublewrite. It adds safety to recovery following an operating system crash or a power outage, and improves performance on most varieties of Unix by reducing the need for fsync() operations.

Doublewrite means that before writing pages to a data file, InnoDB first writes them to a contiguous tablespace area called the doublewrite buffer.

Only after the write and the flush to the doublewrite buffer has completed does InnoDB write the pages to their proper positions in the data file. If the operating system crashes in the middle of a page write, InnoDB can later find a good copy of the page from the doublewrite buffer during recovery.

InnoDB는 의사(실제와 비슷한) 비동기 I/O(AIO)를 사용.
다수의 I/O Thread를 작성하고, read-ahead(先読み) 등의 I/O 조작에 대응.

InnoDB는 2개의 read-ahead heuristics가 존재

sequential read-ahead는 InnoDB가 Table space내의 segment로의 access pattern이 sequential인 걸 생각하면, I/O system에서 database의 read batch를 사전에 알려 줌

임의의 read-ahead은, InnoDB가 Table space내의 몇몇의 space가 buffer pool을 완전하게 read하고 있을 때 인 걸 생각하면, I/O system에서 남은 read를 알려 줌.

InnoDB는 doublewrite라고 하는 새로운 file flash technic을 이용. 이것은 OS의 crash 혹은 정전후의 recovery에 안전성을 부여, 또 fsync() operation의 필요성을 줄이는 것으로 거의 모든 종류의 Unix의 성능을 향상 시킴.

Doublewrite란, datafile에 page를 write하기 전에, InnoDB가 최초에 그것들을 doublewrite buffer라고 하는 인접한 tablespace area에 write하는 것을 의미.

doublewrite buffer로의 write와 crash가 완료한 후에 InnoDB는 datafile내의 올바른 위치에 page를 write함. 만약, OS가 page write도중에 crach 하면, InnoDB는 recovery 도중에 doublewrite buffer부터 유효한 copy를 발견하는것이 가능.