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