Re: Turning off transactions completely.

From: "Arsalan Zaidi" <azaidi(at)directi(dot)com>
To: <Maarten(dot)Boekhold(at)reuters(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Turning off transactions completely.
Date: 2002-01-08 08:28:40
Message-ID: 011901c1981e$7ba471e0$4301a8c0@directi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> You know that too many indices hurts insert/update/delete performance,
> right? For each of these actions, all related indices would need to be
> updated. So if you have any indices that are not used or you could do
> without, remove them.

As mentioned, tables which see a lot of data modification have their index's
removed and then re-created. I don't bother doing this with tables which
aren't so heavily modified.

> Are you using the same database connection for each thread in your
> multi-threaded approach? Postgresql will only benefit from multiple
> processors if there are multiple postgres processes running. In
> application speach that means that you need to have multiple database
> connections open (i.e. one backend process per connection).

Each thread opens up a new connection. Using (g)top I have already verified
that there are several postgres instances running. The machine is entirely
dedicated to this task and there were no other users.

Just to re-iterate.

1. I've done quite a bit of tweaking with the WAL*, shared buffers and sort
mem over several weeks. They're about as optimum as I can get them. Turning
off fsync was one of the first things I did BTW.
2. The executable was compiled on the machine with as many optimisations as
gcc could reasonably support.
3. The queries are just about as good as I can get them and have been
throughly EXPLAIN'ed with live/large amounts of data in the tables. No IN's
used, only EXISTS (where required). This one bit me right at the start. I
had to kill one query after it ran for ~36hrs! BTW, EXISTS seems to be far
more efficient than a JOIN. Is this always true?
4. Temp tables are used to simplify complex queries (and speed them up I
hope).
5. RAID-0 (SCSI/HW) + Dual Proc + 1GB RAM. Linux 2.4.17(smp) (pure Linus. No
other patches). Swap is on a seperate IDE drive.
6. COPY is used where-ever it can be. Index's are dropped before heavy
modification and then recreated. I don't use CLUSTER.
7. Driver app was multi-threaded. It made things worse. BTW, the apps jobs
consists largely of firing off SQL queries in the correct sequence; so you'd
better not go blaming my code!

Is there anything I've missed out?

--Arsalan.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Arsalan Zaidi 2002-01-08 08:36:33 Re: Turning off transactions completely.
Previous Message Martijn van Oosterhout 2002-01-08 08:14:43 Re: Turning off transactions completely.