테이블 DELETE 후 VACUUM 테스트

VACUUM 

1) 공간 재사용

1. 데이터베이스에서 레코드를 삭제하면 물리적인 공간은 여전히 사용 중이지만, 해당 데이터는 더 이상 필요하지 않는다.
2. 삭제된 데이터가 차지하고 있던 공간을 다시 사용 가능하게 만들어 공간 효율성을 높인다.

 

2) 성능개선

 

3) MVCC 관리

데이터베이스 시스템에서 Multi-Version Concurrency Control (MVCC) 메커니즘을 관리하는 데 필수적이다.

MVCC는 트랜잭션 격리 수준을 지원하고, 동시성을 관리하는 데 사용되어 정기적인 VACUUM 작업이 필요하다.

 

 

 

테스트

=====

# SELECT * FROM hostinfo WHERE _agentid='1';
 _agentid |          _hostname           | _hostnameext |                            _os                             | _fw | _agentversion |   _model   | _serial | _processorcount | _processorclock | _memorysize | _swapsize | _poolid | _replication | _smt | _micropar | _capped | _ec | _virtualcpu | _weight | _cpupool | _ams |  _allip   | _numanodecount |   _btime
----------+------------------------------+--------------+------------------------------------------------------------+-----+---------------+------------+---------+-----------------+-----------------+-------------+-----------+---------+--------------+------+-----------+---------+-----+-------------+---------+----------+------+-----------+----------------+------------
        1 | OL7.9.myguest.virtualbox.org |              | Linux(Oracle Linux) 5.4.17-2136.331.7.el7uek.x86_64 x86_64 |     | V4.31.49      | VirtualBox | 0       |               2 |            2188 |        3629 |         0 |      -1 |            0 |    0 |         0 |       0 |  -1 |           2 |      -1 |       -1 |    0 | 10.0.2.15 |                | 1718238672

(1개 행)

# DELETE FROM hostinfo WHERE _agentid='1';
DELETE 1

# SELECT FROM hostinfo WHERE _agentid='1';
--
(0개 행)

 

# SELECT * FROM hostinfo;
 _agentid |  _hostname   | _hostnameext |                            _os                             |   _fw   | _agentversion |   _model   | _serial  | _processorcount | _processorclock | _memorysize | _swapsize | _poolid | _replication | _smt | _micropar | _capped | _ec | _virtualcpu | _weight | _cpupool | _ams |                        _allip                         | _numanodecount |   _btime
----------+--------------+--------------+------------------------------------------------------------+---------+---------------+------------+----------+-----------------+-----------------+-------------+-----------+---------+--------------+------+-----------+---------+-----+-------------+---------+----------+------+-------------------------------------------------------+----------------+------------
        4 | jwchoi_ol7_2 |              | Linux(Oracle Linux) 5.4.17-2136.331.7.el7uek.x86_64 x86_64 |         | V4.31.49      | VirtualBox | 0        |               2 |            2188 |        3629 |         0 |      -1 |            0 |    0 |         0 |       0 |  -1 |           2 |      -1 |       -1 |    0 | 10.0.2.15,192.168.138.4                               |                | 1718925744
        3 | jwchoi_ol7   |              | Linux(Oracle Linux) 5.4.17-2136.331.7.el7uek.x86_64 x86_64 |         | V4.31.49      | VirtualBox | 0        |               2 |            2188 |        3629 |         0 |      -1 |            0 |    0 |         0 |       0 |  -1 |           2 |      -1 |       -1 |    0 | 10.0.2.15                                             |                | 1718925744
        2 | jaewoochoi   |              | Windows 11 Pro                                             | Unknown | V4.1.13.1     | 21F6S00M00 | PF40J09A |              12 |            1900 |       16056 |         0 |      -1 |            0 |    0 |         0 |       0 |   0 |          12 |       0 |        0 |    0 | 192.168.56.1,192.168.138.1,192.168.1.150,192.168.0.34 |                | 1718544487
(3개 행)
// 삭제한 hostname이 삭제가 잘 되었는지 조회

 

 

# SELECT _hostname FROM hostinfo WHERE _hostname LIKE '%box' ORDER BY 1;
 _hostname
-----------
(0개 행)
//삭제한 Hostname 조회

 

 

# VACUUM FULL FREEZE ANALYZE hostinfo;

//삭제한 hostinfo 테이블 VACUUM

 

 

# SELECT * FROM hostinfo;

 _agentid |  _hostname   | _hostnameext |                            _os                             |   _fw   | _agentversion |   _model   | _serial  | _processorcount | _processorclock | _memorysize | _swapsize | _poolid | _replication | _smt | _micropar | _capped | _ec | _virtualcpu | _weight | _cpupool | _ams |                        _allip                         | _numanodecount |   _btime
----------+--------------+--------------+------------------------------------------------------------+---------+---------------+------------+----------+-----------------+-----------------+-------------+-----------+---------+--------------+------+-----------+---------+-----+-------------+---------+----------+------+-------------------------------------------------------+----------------+------------
        4 | jwchoi_ol7_2 |              | Linux(Oracle Linux) 5.4.17-2136.331.7.el7uek.x86_64 x86_64 |         | V4.31.49      | VirtualBox | 0        |               2 |            2188 |        3629 |         0 |      -1 |            0 |    0 |         0 |       0 |  -1 |           2 |      -1 |       -1 |    0 | 10.0.2.15,192.168.138.4                               |                | 1718925744
        3 | jwchoi_ol7   |              | Linux(Oracle Linux) 5.4.17-2136.331.7.el7uek.x86_64 x86_64 |         | V4.31.49      | VirtualBox | 0        |               2 |            2188 |        3629 |         0 |      -1 |            0 |    0 |         0 |       0 |  -1 |           2 |      -1 |       -1 |    0 | 10.0.2.15                                             |                | 1718925744
        2 | jaewoochoi   |              | Windows 11 Pro                                             | Unknown | V4.1.13.1     | 21F6S00M00 | PF40J09A |              12 |            1900 |       16056 |         0 |      -1 |            0 |    0 |         0 |       0 |   0 |          12 |       0 |        0 |    0 | 192.168.56.1,192.168.138.1,192.168.1.150,192.168.0.34 |                | 1718544487
(3개 행)
// hostinfo 테이블 조회

===

 

VACUUM FULL FREEZE ANALYZE

 

• FULL: 테이블을 완전히 잠그고, 테이블의 모든 데이터를 재작성하여 디스크 공간을 최대한 회수
• FREEZE: 테이블의 트랜잭션 ID를 갱신하여 매우 오래된 트랜잭션으로 인한 문제를 방지
• ANALYZE: 테이블의 통계를 수집하여 쿼리 플래너가 보다 최적화된 쿼리 실행 계획을 수립할 수 있도록 함