Re: my boss want to migrate to ORACLE

After a long battle with technology, stephane(dot)tessier(at)abovesecurity(dot)com ("Stephane Tessier"), an earthling, wrote:
> I think with your help guys I'll do it!
>
> I'm working on it!
>
> I'll work on theses issues:
>
> we have space for more ram(we use 2 gigs on possibility of 3 gigs)

That _may_ help; not completely clear.

> iowait is very high 98% --> look like postgresql wait for io access

In that case, if you haven't got a RAID controller with battery backed
cache, then that should buy you a BIG boost in performance. Maybe
$1500 USD; that could be money FABULOUSLY well spent.

> raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
> write on disk

I try to avoid talking about RAID levels, and leave them to others
:-).

Sticking WAL on a solid state disk would be WAY COOL; you almost
certainly are hitting WAL really hard, which eventually cooks disks.
What is unfortunate is that there doesn't seem to be a "low end" 1GB
SSD; I'd hope that would cost $5K, and that might give a bigger boost
than the battery-backed RAID controller with lotsa cache.

> use more transactions (we have a lot of insert/update without
> transaction).

That'll help unless you get the RAID controller, in which case WAL
updates become much cheaper.

> cpu look like not running very hard

Not surprising.

> *php is not running on the same machine
> *redhat enterprise 3.0 ES
> *the version of postgresql is 7.3.4(using RHDB from redhat)

All makes sense. It would be attractive to move to 7.4.2 or 7.4.3;
they're really quite a lot faster. If there's no option to migrate
quickly, then 7.5 has interesting cache management changes that ought
to help even more, particularly with your vacuuming issues :-).

But it's probably better to get two incremental changes; migrating to
7.4, and being able to tell the boss "That improved performance by
x%", and then doing _another_ upgrade that _also_ improves things
should provide a pretty compelling argument in favour of keeping up
the good work with PostgreSQL.

> *pg_autovacuum running at 12 and 24 hour each day

That really doesn't make sense.

The point of pg_autovacuum is for it to run 24 hours a day.

If you kick it off twice, once at 11:59, then stop it, and then once
at 23:59, and then stop it, it shouldn't actually do any work. Or
have you set it up with a 'sleep period' of ~12 hours?
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/sgml.html
The *Worst* Things to Say to a Police Officer: Hey, is that a 9 mm?
That's nothing compared to this .44 magnum.