I don't think I have enough detail about your app. Couple of questions,
are there any tables that recieve a lot of inserts / updates / deletes
that are not deleted and recreated often? If so, one possibility is
that you don't have a large enough FSM settings and your table is
actually growing despite using autovac. Does that sounds possbile to you?
Missner, T. R. wrote:
Hello,
I have been a happy postgresql developer for a few years now. Recently
I have discovered a very strange phenomenon in regards to inserting
rows.
My app inserts millions of records a day, averaging about 30 rows a
second. I use autovac to make sure my stats and indexes are up to date.
Rarely are rows ever deleted. Each day a brand new set of tables is
created and eventually the old tables are dropped. The app calls
functions which based on some simple logic perform the correct inserts.
The problem I am seeing is that after a particular database gets kinda
old, say a couple of months, performance begins to degrade. Even after
creating brand new tables my insert speed is slow in comparison ( by a
magnitude of 5 or more ) with a brand new schema which has the exact
same tables. I am running on an IBM 360 dual processor Linux server
with a 100 gig raid array spanning 5 scsi disks. The machine has 1 gig
of ram of which 500 meg is dedicated to Postgresql.
Just to be clear, the question I have is why would a brand new db schema
allow inserts faster than an older schema with brand new tables? Since
the tables are empty to start, vacuuming should not be an issue at all.
Each schema is identical in every way except the db name and creation
date.
Any ideas are appreciated.
Thanks,
T.R. Missner
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
