Vacuum influence

Objectives of the experiment

1) Assess the degree of influence of regular execution of vacuum/analyze on the performance of the DBMS.

2) Assess the degree of influence of table swelling on DBMS performance.

Implementation of the experiment

One iteration of the test consists of a transaction select/insert/update/delete (number of rows in tables ~1,000,000).

Number of iterations = 1000.

Estimated parameters

1. Total execution time ( total_exec_time performances pg_stat_statements)

2. Number of requests ( calls performances pg_stat_statements )

3. Number of resulting rows ( rows performances pg_stat_statements )

4. Total number of shared blocks read ( shared_blks_read performances pg_stat_statements )

5. Total number of “polluted” shared blocks ( shared_blk_dirtied performances pg_stat_statements )

6. Total number of shared blocks written ( shared_blk_written performances pg_stat_statements )

7. Number of transactions ( xact_commit performances pg_stat_database )

8. DBMS performance metric ( CPI )

Results of the experiment

Benchmark – vacuum + analyze after each iteration

Test without running vacuum + analyze after each iteration

Comparison of results with “Benchmark – vacuum + analyze after each iteration“

• Test execution time – slightly reduced

• The volume of processed shared blocks has increased significantly

• Productivity – significantly decreased

Fragmentation 11%

Comparison of results with “Test without running vacuum + analyze after each iteration“

• Test execution time has increased significantly

• The volume of processed shared blocks has increased significantly

• Productivity – significantly decreased

Fragmentation 100%

Comparison of results with “Test without running vacuum + analyze after each iteration“

• Test execution time has increased significantly

• The volume of processed shared blocks has increased significantly

• Productivity – significantly decreased

Results

Running vacuum+analyze after massive data changes significantly increases DBMS performance, although the overall execution time increases somewhat.

Even relatively small fragmentation has a significant impact on DBMS performance.

Further increase in fragmentation does not have a noticeable impact on DBMS performance.