Forum OpenACS Q&A: Insert Performance Issues - PG 7.2.3/OpenACS 4.5/perl/dbi

1.  increase the WAL files setting.  I think the usual setting is 0.  I usually increase it to 3 or 4.

2.  increase the sort_mem from 512 to 2048; this means "use up to 2MB RAM for in-memory sorting before dumping the data to disk and using the slower disk-based sorting routine".  512KB is not enough.

3. Check the fsync parameter - this varies by platform.  Turning it off is less safe but faster.

4.  Increase the shared_buffers setting.

5.  Make sure you have some checkpoint_segments (number greater than 0) .

Remember to restart the server after each change and test.  If using the pg_ctl program, check the actual logfile, since it has the bug that if the server exits due to a misconfigured .conf file it will still report success.

With 384MB of memory you can increase sort mem size well above 2MB, even, though that suggestion is much better than the default.

But the shared buffer pool must be greatly increased.  It's mentioned above but the importance isn't stressed.  Postgres, by default, only caches 64 blocks in shared memory and with that much memory you should increase the size to a much larger value.

Randy's suggestions are good, too - don't make things objects unless they need to be, which normally implies you need the auditing information built into objects (creation time, user, etc), you need to explicitly manage permissions on the object etc.

We don't use Postgres inheritance because it is broken, in essence.  There's a document lying around in file storage that explains why, the reasons are many.

We use sequences in our PG code mostly because we support both Oracle and PG, and using sequences leads to our being able to share a lot more queries between the two than would be true if we used the serial datatype in PG.  Our datamodels for Oracle  and PG are more closely alike, too, as a consequence of this decision.

Sequences are also more flexible in practice, and PG's serial datatype is implemented using sequences.

In regard to your example, it has been so long since I've experimented with the serial datatype in PG that I don't remember the details of how it works ...

I've created a table:

create table test2 (
  i        serial,
  txt      varchar(20)
);

I'm trying to build a new function, "test2__new" which will insert a new string into the table and automatically give it a new i value.

I have:

create function test__new (varchar)
return integer as '
declare
  new__text    alias for $1;
begin
  select test2_i_seq.nextval
  into v_index_id;

insert into test2
  (i, txt)
values
  (v_index_id, new__text);

return v_index_id;
end; ' language 'plpgsql';

---

Whenever I try to load this new function, it always gives me "parse erorr at or near "return"".  I am not sure what I'm missing.

Also, I'm trying to find some code that exports an SQL query to Excel/CSV via the OpenACS interface.  I did some searching and found that it is supposed to be a feature of the simple-survey module of dotLRN.  After downloading dotLRN 1.0, I haven't really been able to isolate it.  Can anyone suggest where I might be able to find this type of functionality?

Thanks.

John