Category Archives: MySQL

Linux #21 : MySQL Log를 Syslog(Remote Syslog)로 관리

정말 오래간만에 하는 포스팅 인듯 하네요. = =;

MySQL의 Error log를 Remote Syslog를 통해서 관리를 해보고자 아래와 같이 Script를 만들어 보았습니다.
Syslog 수집 서버를 구성하고 수집 서버를 통해서 MySQL Error Log를 통합 관리 하기 위하여 사용하고 있습니다.
Remote Syslog 수집 서버에 개발해 놓은 Application에 Filtering 기능을 이용하여 MySQL과 관련 된 Critical Log를 검출하여
Alert를 발생시키는 구조입니다.

# ================================================================
# -process
# Mysql Alert Log to Remote Syslog
# -process content
# Mysql Alert Log to Remote Syslog
# -how to use
# -created date
# 2010/05/17 Jeff Kim
# ================================================================
MYSQL_VAR=[MySQL Var Directory]

function Printmsg {
echo "`date \"+%Y-%m-%d %H:%M\"` [$1] $2" >> $TMP/message.log
echo "`date \"+%Y-%m-%d %H:%M\"` [$1] $2"
logger -p daemon.notice "[$1] $2"

function Endscript {
Printmsg "INFO" "End Script : $SCRIPT_NAME."
echo "################################################################################################"

function Startscript {
if [ -z "$SCRIPT_START" ];then
cat < ###############################################################################################

#if [ "$SCRIPT_START" != ""y"" ];then
# Endscript
# Printmsg "INFO" "Pressed 'y' : $SCRIPT_NAME"

function Whoamiroot {
if [ "$WHOAMI" != "root" ];then
Printmsg "ERROR" "Permission deny. Only root!"
Printmsg "INFO" "Forced-end this script."


if [ "$HOSTNAME" = "" ];then
Printmsg "ERROR" "Please, Check Host Name.";Endscript
Printmsg "INFO" "nohup tail -f ${MYSQL_VAR}/${HOSTNAME}.err | logger -t MySQL -p ${FACILITY}.${PRIORITY} &"
nohup tail -f ${MYSQL_VAR}/${HOSTNAME}.err | logger -t MySQL -p ${FACILITY}.${PRIORITY} &

script가 실행되고 있고, remote syslog 서버에 Log를 보내는 설정이 되어 있다면 MySQL Error가 발생하는 즉시 Syslog와 Remote Syslog 서버에 MySQL 관련 Log가 기록이 됩니다. 그리고 이 로그를 다시 재처리하면 MySQL의 Log를 모니터링 할 수 있지요.

DB #6 : [MySQL]High Availability and Scalability

When using MySQL you may need to ensure the availability or scalability of your MySQL installation. Availability refers to the ability to cope with, and if necessary recover from, failures on the host, including failures of MySQL, the operating system, or the hardware. Scalability refers to the ability to spread the load of your application queries across multiple MySQL servers. As your application and usage increases, you may need to spread the queries for the application across multiple servers to improve response times.

cope with + noun : 극복하다. 잘 처리하다
refer to + noun : 관련되다
spread : 분담하다, 분배하다.

There are a number of solutions available for solving issues of availability and scalability. The two primary solutions supported by MySQL are MySQL Replication and MySQL Cluster. Further options are available using third-party solutions such as DRBD (Distributed Replicated Block Device) and Heartbeat, and more complex scenarios can be solved through a combination of these technologies. These tools work in different ways:

MySQL Replication enables statements and data from one MySQL server instance to be replicated to another MySQL server instance. Without using more complex setups, data can only be replicated from a single master server to any number of slaves. The replication is asynchronous, so the synchronization does not take place in real time, and there is no guarantee that data from the master will have been replicated to the slaves.

Advantages :

  • MySQL Replication is available on all platforms supported by MySQL, and since it isn’t operating system-specific it can operate across different platforms.
  • Replication is asynchronous and can be stopped and restarted at any time, making it suitable for replicating over slower links, partial links and even across geographical boundaries.
  • Data can be replicated from one master to any number of slaves, making replication suitable in environments with heavy reads, but light writes (for example, many web applications), by spreading the load across multiple slaves.
suitable : 적당한, 적절한
partial : 편파적인, 일부분의
boundary : 한계, 경계
sperad : 분배하다. 나누다

Disavantages :

  • Data can only be written to the master. In advanced configurations, though, you can set up a multiple-master configuration where the data is replicated around a ring configuration.
  • There is no guarantee that data on master and slaves will be consistent at a given point in time. Because replication is asynchronous there may be a small delay between data being written to the master and it being available on the slaves. This can cause problems in applications where a write to the master must be available for a read on the slaves (for example a web application).
consitent : 일관된, 모순이 없는

Recommended uses :

  • Scale-out solutions that require a large number of reads but fewer writes (for example, web serving).
  • Logging/data analysis of live data. By replicating live data to a slave you can perform queries on the slave without affecting the operation of the master.

Online backup (availability), where you need an active copy of the data available. You need to combine this with other tools, such as custom scripts or Heartbeat. However, because of the asynchronous architecture, the data may be incomplete.

live data : using data 사용중인
affect : ~에 영향을 미치다.

Offline backup. You can use replication to keep a copy of the data. By replicating the data to a slave, you take the slave down and get a reliable snapshot of the data (without MySQL running), then restart MySQL and replication to catch up. The master (and any other slaves) can be kept running during this period.

For information on setting up and confliguring replication, see Chapter 15, Replication.

MySQL Cluster is synchronous solution that enables multiple MySQL instances to share database information. Unlike replication, data in a cluster can be read from or written to any node within the cluster, and information will be distributed to the other nodes.

Advantages :

  • Offers multiple read and write nodes for data storage.
  • Provides automatic failover between nodes. Only transaction information for the active node being used is lost in the event of a failure.
  • Data on nodes is instantaneously distributed to the other data nodes.
instantaneously : 즉시

Disadvantages :

  • Available on a limited range of platforms.
  • Nodes whithin a cluster should be connected via a LAN; geographically separate nodes are not supported .However, you can replicate from one cluster to another using MySQL Replication, although the replication in this case is still asynchronous.

Recommended uses :

  • Applications that need very high availability, such as telecoms and banking.
  • Applications that require an equal or higher number of writes compared to reads.

For information on MySQL Cluster, see Chapter 16, MySQL Cluster.

DRBD(Distributed Replicated Block Device) is a solution from Linbit supported only on Linux. DRBD creates a virtual block device(which is associated with an underlying physical block device)that can be replicated from the primary server to a secondary server. You create a filesystem on the virtual block device, and this information is then replicated, at the block level, to the secondary server.

underlying : 기본적인, 근원적인, 기초를 이루는

Because the block device, not the data you are storing on it, is being replicated the validity of the information is more reliable than with data-only replication solutions. DRBD can also ensure data integrity by only returning from a write operation on the primary server when the data has been written to the underlying physical block device on both the primary and secondary servers.

validity : 타당성, 유효성
reliable : 신뢰하다, 신용하다
ensure : 확실하게 하다. 보장하다
integrity : 고결, 완전한 (상태)

Advantages :

  • Provides high availability and data integrity across two servers in the event of hardware or system failure.
  • Ensures data integrity by enforcing write consistency on the primary and secondary nodes.
consistency : 일관성

Disadvantages :

  • Only provides a method for duplication data across the nodes,. Secondary nodes cannot use the DRBD device while data is being replicated, and so the MySQL on the secondary node cannot be simultaneously active.
  • Cannot provide scalability, since secondary nodes don’t have access to the secondary data.
simultaneously : 동시에

Recommended uses :

  • High availability situations where concurrent access to the data is not required, but instant access to the active data in the event of a system or hardware failure is required.

For information on configuring DRBD and configuring MySQL for use with a DRBD device, see Section 14.1. “Using MySQL with DRBD for High Availability”

Heartbeat is a software solution for Linux. It is not a data replication or synchronization solution, but a solution for monitoring servers and switching active MySQL servers automatically in the event of failure. Heartbeat needs to be combined with MySQL Replication or DRBD to provide automatic failover.

The information and suitability of the various technologies and different scenarios is summarized in the table below.

suitability : 적당한
various : 다양한, 여러가지, 색색

MySQL을 사용한다면, MySQL 설치에 가용성 또는 확장성을 고려할 필요가 있습니다. 가용성은 MySQL, OS, H/W down을 포함한 host server의 down으로 부터의 복구하거나, 잘 처리하는 능력과 관련됩니다.
확 장성은 다수의 MySQL servers로 application queries의 load를 분담하는 능력과 관련됩니다. Application이나 사용량이 증가하면, response times의 개선을 위해 다수의 servers로 application queries를 분담할 필요가 있습니다.

아래는 가용성과 확장성의 issues를 해결 가능하는 solutions입니다. 두 개의 solutions은 MySQL의 Replication과 Cluster로 제공됩니다. Further : 더 나아가서, 그 이상의 options은 third-party solutions을 통한 DRBD (Distributed Replicatted Block Device)와 Hearbeat, 그 기술들을 혼합시켜 만든 더 복잡한 시나리오로 처리 가능합니다. 이들 기능은 각기 다른 방법으로 일을 합니다.

MySQL Replication 가능한 상황들과 하나의 MySQL server instance로부터의 data는 다른 MySQL server instance로 Replicate 될 수 있다. 복잡한 설정 없이, 단지 data는 단일 master server에서 다른 많은 slaves로 replicate 할 수 있다. Replication은 비동기로, 그래서 실시간으로 동기화 하지 않고, 제약없이 master로 부터 slaves로 data를 replicate 할 것이다.
Advantages :

  • MySQL Replication은 MySQL이 지원되는 모든 platforms에서 사용가능하고, 시스템 특성에 따라 작동하고 있지 않은 관계로, 다른 platforms 사이에서도 동작 가능하다.
  • Replication은 비동기로 언제라도 stop 및 restart 가능하고, 느린 링크들, 편파적인 링크들, 위치적인 한계를 뛰어 넘어 replicating을 하기 적절하게 하고 있다.
  • Data 는 하나의 master로부터 많은 수의 slaves로 replicate 할 수 있고, 다중의 slaves사이의 부하를 분배하기 위해 많은 reads, 적은 writes (예를 들면, 많은 web applications)의 환경에 적절하다.

Disavantages :

  • Data는 단지 master에만 쓸 수 있다. 보다 발전된 환경에선 data는 링의 형태의 replcate되고 다중의 master 환경을 설정 할 수 있다.
  • 제 약없이 master와 slaves에게 주어진 시간에 일관되게 data를 보낼 것이다. Replication은 data가 master에 쓰여지게 될 때, slaves에 그것이 가능하도록 작은 지연이 발생하는 비동기 방식이기 때문이다.이것은 master에 쓰고, slaves에서 읽어야만 하는 예를 들면 web application같은 app에서 문제를 야기 시킬 수 있다.

Recommended uses :

  • Scale-out solutions으로 많은 수의 reads 이나 적은 writes인 예를 들면 web serving.
  • 사용중인 data의 Logging/data analysis. 사용중인 data를 slave로의 replicate 하는 것에 의해 master의 동작환경에 영향을 끼치는 것 없이 queries을 개선 가능하다.
  • Online backup (가용성), 살아 있는 가용 data의 copy가 필요할 때. 이것들을 준비된 스크립트 또는 Heartbeat 와 같은 다른 tools과 함께 조합 할 필요가 있다.

Offline backup. data를 복사하면서 replication을 사용 할 수 있다. slave로 data를 replcation 하는 것으로 slave를 정지시키거나, Mysql를 가동하지 않고도 data의 snapshot을 얻을 수 있고, MySQL을 재기동 하면, replication이 활성화 된다. Master(또, 어떤 다른 slaves)는 이 기간 동안 계속 가동 가능하다.
replication의 set up 및 설정에 대한 정보는 Chapter 15, Replication을 참조하세요.

MySQL Cluster은 database information 공유로 다중의 Multiple MySQL instances 가능한 동기 solution이다. replication과 다르게, cluster에서 data는 cluster에 포함된 어떤 node에서도 read/write가 가능하고, Information은 다른 nodes로 분산 되게 됩니다.
Advantages :

  • data storage를 통해 multiple read/write nodes 제공합니다.
  • Nodes 사이의 자동 failover를 제공. 단 활성 node가 사용하고 있는 transaction information은 failure event로서 손실 됩니다.
  • Nodes 상의 data는 즉시 다른 data nodes로 분할 됩니다.

Disadvantages :

  • 사용가능한 platforms이 제한 되어 있습니다.
  • cluster에 포함된 Nodes는 via a LAN으로 접속 해야 합니다; 물리적으로 분리된 nodes는 지원하지 않습니다. 그리고, 하나의 cluster에서 다른 MySQL Replication으로 replcation 가능합니다, 하지만 이 경우의 replcation은 비동기가 됩니다.

Recommended uses :

  • 통신이나 은행과 같은 매우 높은 가용성을 필요로하는 Applications.
    reads와 writes가 동일하거나 writes가 많은 비중을 차지하는 Applications.
    MySQL Cluster의 정보는 Chapter 16, MySQL Cluster를 참조하세요.
  • DRBD는 Linux 상의 단지 Linbit(?)으로부터 지원되는 solution이다. DRBD는 primary server로부터 secondary server로 replicate 가능한  virtual block device(기본적인 physical block device로 연관되어진)를 생성한다.
  • Virtual block device 상에서 filesystem를 만들고, 이 information은 secondary server로 block level로 replicate 된다.

보 관하고 있는 data가 아닌, 그 정보의 유효성을 replicate하고 있는 block device이기 때문에, data만 replication하는 solutions보다 더 신뢰가능하다. DRBD는 또한 primary와 secondary servers 양쪽의 본래의 physical block device로 쓰여질 때, primary server의 write operation으로 부터 return되므로 data의 완전성(고결성)을 보장한다.

Advantages :

  • 두 servers 사이의 hardware의 event 혹은 system failure에서 높은 가용성과 data의 고결성을 제공한다.

Disadvantages :

  • 단 지 nodes 사이에서 data를 복제하기 위한 방법을 제공한다. Secondary nodes는 data를 replicate하고 있는 동안에는 DRBD device를 사용할 수 없고, secondary node상의 MySQL은 동시에 활성화 될 수 없다.

Recommended uses :

  • data로의 concurrent access 고 가용성 상황에는 적합하지 않지만, system의 evnet 혹은 hardware failure에서 활성 data로의 즉각적인 aceess에는 적합하다.

DRBD 설정과 DRDB device를 사용하기 위한 MySQL 설정 정보는 Section 14.1 “Using MySQL with DRBD for High Availability”를 참조하세요.

Heartbeat 는 Linux를 위한 software solution 입니다. data replication 또는 synchronization solution이 아니라, failure event를 자동으로 active 상태의 MySQL servers를 switching하고 servers를 monitoring하는 solution입니다. Heartbeat는 MySQL Replication 혹은 자동 failover를 제공하는 DRBD와 조합이 필요합니다.

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를 발견하는것이 가능.