Question is about the relation between fragmentation of file and VACUUM
performance.
<Environment>
OS：RedHat Enterprise Linux AS Release 3(Taroon Update 6)
Kernel 2.4.21-37.ELsmp on an i686
Filesystem Type ext3
Filesystem features: has_journal filetype needs_recovery sparse_super large_file
CPU：Intel(R) Xeon(TM) CPU 2.80GHz stepping 01
Memory：2.0GB
HDD：80GB（S-ATA）
SATA max UDMA/133
PostgreSQL：7.3.8
<DB Environment>
1. Approx. there are 3500 tables in the DB
2. Index is attached to table.
3. Every two minutes interval approx. 10,000 records are inserted into 1000tables.
So total 7,200,000 records are inserted in 1000 tables per days
4. Tables data are saved for 7 days.Older than 7 days data are deleted.
So maximum total records 50,400,000 can be exist in DB.
5. VACCUME is executed against DB as below
Six times a day i.e. every 4 hours the VACCUME ANALYZE is started once.
And out of the six times once VACCUME FULL ANALYZE is processed.
At the beginning, volume of data increases linearly because
the records are added for seven days. After seven days older than
seven days data are deleted. So volume of data will not increase
after seventh days.
When the performance of inserting data was measured in the above-
mentioned environment, it takes six minutes to write 10000 lines
after 4/5 days the measurement had begun. While searching the reason
of bottleneck by executing iostat command it is understood that DISK I/O
was problem for the neck as %iowait was almost 100% at that time.
On the very first day processing time of VACUUM is not a problem but
when the day progress its process time is increasing.Then I examined the
fragmentation of database area(pgsql/data/base) by using the following tools.
Disk Allocation Viewer
http://sourceforge.net/projects/davtools/
Fragmentation rate is 28% before defrag.
The processing time of VACUUM became 20 minutes, and also inserting data
took short time, when data base area (pgsql/data/base) was copied, deleted,
copied again, and the fragmentation was canceled (defrag).
Moreover, After the fragmentation cancelled the processing time for VACCUM
was 20 minutes, but after 7 days it took 40 minutes for processing.When again
checked the fragmentation rate with the tool it was 11%.Therefore, it is
understood that the fragmentation progresses again.
However, In my current environment I can't stop PostgreSQL and cancel
fragmentation.
Could anyone advise some solutions for this fragmentation problem
without stopping PostgreSQL ? For example, using the followings or anything
else..
-Tuning of postgresql.conf
-PostgreSQL(8.1.0) of latest version and VACUUM
Thanks in advance.
Abe
