Re: very slow after a while...

Please CC the list as well as replying directly - it means more people
can help.

Costin Manda wrote:
>>Some more info please:
>>1. This is this one INSERT statement per transaction, yes? If that
>>fails, you do an UPDATE
>
> correct.
>
>>2. Are there any foreign-keys the insert will be checking?
>>3. What indexes are there on the main table/foreign-key-related tables?
>
>
> this is the table, the only restriction at the insert is the logid which
> must be unique.
>
> Table "public.pgconnectionlog"
> Column | Type | Modifiers
> ----------------+-----------------------+-----------
> logid | integer | not null
> username | character varying(20) |
> logtime | integer |
> connecttime | integer |
> disconnecttime | integer |
> usedcredit | double precision |
> usedtime | integer |
> phonenum | character varying(30) |
> prephonenum | character varying(20) |
> pricelistname | character varying(30) |
> precode | character varying(20) |
> effectivetime | integer |
> callerid | character varying(30) |
> serialnumber | character varying(30) |
> prefix | character varying(20) |
> tara | character varying |
> Indexes:
> "pgconnectionlog_pkey" PRIMARY KEY, btree (logid)
> "connecttime_index" btree (connecttime)
> "disconnecttime_index" btree (disconnecttime)
> "logtime_index" btree (logtime)
> "prefix_index" btree (prefix)
> "tara_index" btree (tara)
> "username_index" btree (username)

Hmm - nothing unusual there. I'd be suspicious of a problem with the
indexes, except you say reindexing has no effect.

>>Whatever the answers to these questions, perhaps look into loading your
>>data into a temporary table, inserting any rows without matching primary
>>keys and then deleting those and updating what's left.
>
> You think this will be faster? It does make sense. Anyway, the problem
> is not optimising the script, is the speed change , dramatic I would
> say.

Could you monitor what's happening while this slows down. In particular,
could you:
1. Run "vmstat 10" so we can see memory/cpu/disk usage while this is
happening.
2. See what's happening in pg_xlog - are you creating/cycling through a
lot of transaction-log files?
3. Keep an eye on the logs - are there any warnings there?

If you vacuum full, it's worth adding "verbose" to the that too, to see
what it's doing.
--
Richard Huxton
Archonet Ltd