Tatsumi Abe wrote: > 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 > 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. I'd guess the root of your problem is the number of tables (3500), which if each has one index represents at least 7000 files. That means a lot of your I/O time will probably be spent moving the disk heads between the different files. You say you can't stop the server, so there's no point in thinking about a quick hardware upgrade to help you. Also a version-upgrade is not do-able for you. I can only think of two other options: 1. Change the database schema to reduce the number of tables involved. I'm assuming that of the 3500 tables most hold the same data but for different clients (or something similar). This might not be practical either. 2. Re-order how you access the database. ANALYSE the updated tables regularly, but only VACUUM them after deletions. Group your inserts so that all the inserts for table1 go together, then all the inserts for table2 go together and so on. This should help with the fragmentation by making sure the files get extended in larger chunks. Are you sure it's not possible to spend 15 mins offline to solve this? -- Richard Huxton Archonet Ltd
