Re: [PERFORM] my boss want to migrate to ORACLE
On Jul 28, 2004, at 1:08 PM, Stephane Tessier wrote: we have a BIG problem of performance,it's slow Can you isolate which part is slow? (log_min_duration is useful for finding your slow running queries) we use postgres 7.3 for php security application with approximately 4 millions of insertion by day and 4 millions of delete and update That is pretty heavy write volume. Are these updates done in batches or "now and then"? If they are done in batches you could speed them up by wrapping them inside a transaction. #shared_buffers = 256 # min max_connections*2 or 16, 8KB each #shared_buffers = 196000 # min max_connections*2 or 16, 8KB each shared_buffers = 128000 # min max_connections*2 or 16, 8KB each Too much. Generally over 1 will stop benefitting you. #wal_buffers = 8 # min 4, typically 8KB each Might want to bump this up #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each Given your write volume, increase this up a bit.. oh.. 20 or 30 of them will help a lot. But it will use 16*30MB of disk space. Oracle is *NOT* a silver bullet. It will not instantly make your problems go away. I'm working on a project porting some things to Oracle and as a test I also ported it to Postgres. And you know what? Postgres is running about 30% faster than Oracle. The Oracle lovers here are not too happy with that one :) Just so you know.. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] my boss want to migrate to ORACLE
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) iowait is very high 98% --> look like postgresql wait for io access raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each write on disk use more transactions (we have a lot of insert/update without transaction). cpu look like not running very hard *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) *pg_autovacuum running at 12 and 24 hour each day -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: 29 juillet, 2004 23:00 To: Stephane Tessier Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] my boss want to migrate to ORACLE A furthur thought or two: - you are *sure* that it is Postgres that is slow? (could be Php...or your machine could be out of some resource - see next 2 points) - is your machine running out of cpu or memory? - is your machine seeing huge io transfers or long io waits? - are you running Php on this machine as well as Postgres? - what os (and what release) are you running? (guessing Linux but...) As an aside, they always say this but: Postgres 7.4 generally performs better than 7.3...so an upgrade could be worth it - *after* you have solved/identified the other issues. best wishes Mark Quoting Stephane Tessier <[EMAIL PROTECTED]>: > Hi everyone, > > somebody can help me??? my boss want to migrate to > ORACLE > > we have a BIG problem of performance,it's slow > we use postgres 7.3 for php security application with approximately 4 > millions of insertion by day and 4 millions of delete and update > and archive db with 40 millions of archived stuff... > > we have 10 databases for our clients and a centralized database for the > general stuff. > > database specs: > > double XEON 2.4 on DELL PowerEdge2650 > 2 gigs of RAM > 5 SCSI Drive RAID 5 15rpm > > tasks: > > 4 millions of transactions by day > 160 open connection 24 hours by day 7 days by week > pg_autovacuum running 24/7 > reindex on midnight ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] my boss want to migrate to ORACLE
Stephane wrote: Hi everyone, somebody can help me??? my boss want to migrate to ORACLE #fsync = true [snip] Are you using battery baked RAID? Your problem is probably due to the database syncing all the time. With fsync one, you get 1 sync per transaction that updates, deletes, etc. 4 million writes/day = 46 writes/sec avg. Of course, writes will be very bursty, and when you get over 100 you are going to have problems even on 15k system. All databases have this problem, including Oracle. Keeping WAL and data on separate volumes helps a lot. It's quicker and easier to use hardware solution tho. If you want to run fsync on with that much I/O, consider using a battery backed raid controller that caches writes. This will make a *big* difference. A quick'n'dirty test is to turn fsync off for a little while to see if this fixes your performance problems. Merlin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] my boss want to migrate to ORACLE
On Fri, 2004-07-30 at 07:56, Stephane Tessier 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) > iowait is very high 98% --> look like postgresql wait for io access > raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each > write on disk Just get battery backed cache on your RAID controller. RAID0 is way too unreliable for a production environment. One disk dies and all your data is just gone. > use more transactions (we have a lot of insert/update without transaction). > cpu look like not running very hard > > *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) > *pg_autovacuum running at 12 and 24 hour each day > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of > [EMAIL PROTECTED] > Sent: 29 juillet, 2004 23:00 > To: Stephane Tessier > Cc: [EMAIL PROTECTED] > Subject: Re: [PERFORM] my boss want to migrate to ORACLE > > > A furthur thought or two: > > - you are *sure* that it is Postgres that is slow? (could be Php...or your > machine could be out of some resource - see next 2 points) > - is your machine running out of cpu or memory? > - is your machine seeing huge io transfers or long io waits? > - are you running Php on this machine as well as Postgres? > - what os (and what release) are you running? (guessing Linux but...) > > As an aside, they always say this but: Postgres 7.4 generally performs > better > than 7.3...so an upgrade could be worth it - *after* you have > solved/identified > the other issues. > > best wishes > > Mark > > Quoting Stephane Tessier <[EMAIL PROTECTED]>: > > > Hi everyone, > > > > somebody can help me??? my boss want to migrate to > > ORACLE > > > > we have a BIG problem of performance,it's slow > > we use postgres 7.3 for php security application with approximately 4 > > millions of insertion by day and 4 millions of delete and update > > and archive db with 40 millions of archived stuff... > > > > we have 10 databases for our clients and a centralized database for the > > general stuff. > > > > database specs: > > > > double XEON 2.4 on DELL PowerEdge2650 > > 2 gigs of RAM > > 5 SCSI Drive RAID 5 15rpm > > > > tasks: > > > > 4 millions of transactions by day > > 160 open connection 24 hours by day 7 days by week > > pg_autovacuum running 24/7 > > reindex on midnight > > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Optimizer refuses to hash join
Stan Bielski <[EMAIL PROTECTED]> writes: > On Thu, 29 Jul 2004, Tom Lane wrote: >> Are you sure the join condition is hashjoinable? You didn't say >> anything about the datatypes involved ... > My apologies. The columns that I want to join are both type 'inet'. > Shouldn't that be hashjoinable? Depends on your PG version. The raw type isn't hashjoinable, because its '=' operator ignores the inet-vs-cidr flag. Before 7.4 the operator was (correctly) marked not hashjoinable. In 7.4 it was (incorrectly) marked hashjoinable, due no doubt to momentary brain fade on my part. For 7.5 it is hashjoinable and the join will actually work, because we added a specialized hash function that also ignores the inet-vs-cidr flag. If you are joining data that is all inet or all cidr (no mixtures), then 7.4 works okay, which is why we didn't notice the bug right away. If that's good enough for now, you could emulate the 7.4 behavior in earlier releases by setting the oprcanhash flag in pg_operator for the inet equality operator. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] my boss want to migrate to ORACLE
Stephane Tessier 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) iowait is very high 98% --> look like postgresql wait for io access raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each write on disk use more transactions (we have a lot of insert/update without transaction). cpu look like not running very hard *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) *pg_autovacuum running at 12 and 24 hour each day What do you mean by "pg_autovacuum running at 12 and 24 hour each day"? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] my boss want to migrate to ORACLE
On Fri, 30 Jul 2004, Matthew T. O'Connor wrote: > Stephane Tessier 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) > >iowait is very high 98% --> look like postgresql wait for io access > >raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each > >write on disk > >use more transactions (we have a lot of insert/update without transaction). > >cpu look like not running very hard > > > >*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) > >*pg_autovacuum running at 12 and 24 hour each day > > > > > What do you mean by "pg_autovacuum running at 12 and 24 hour each day"? I suspect he means at 1200 and 2400 each day (i.e noon and midnight). -- Dan Langille - http://www.langille.org/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] my boss want to migrate to ORACLE
pg_autovacuum is a daemon, not something that get's run twice a day. I think that's what the question Matthew was getting @. I'm not sure what would happen to performance if pg_autovacuum was launched twice a day from cron, but you could end up in an ugly situation if it starts up. --brian On Jul 30, 2004, at 12:11 PM, Dan Langille wrote: On Fri, 30 Jul 2004, Matthew T. O'Connor wrote: Stephane Tessier 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) iowait is very high 98% --> look like postgresql wait for io access raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each write on disk use more transactions (we have a lot of insert/update without transaction). cpu look like not running very hard *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) *pg_autovacuum running at 12 and 24 hour each day What do you mean by "pg_autovacuum running at 12 and 24 hour each day"? I suspect he means at 1200 and 2400 each day (i.e noon and midnight). -- Dan Langille - http://www.langille.org/ ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] my boss want to migrate to ORACLE
After a long battle with technology, [EMAIL PROTECTED] ("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.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] my boss want to migrate to ORACLE
On Fri, 2004-07-30 at 19:22, Christopher Browne wrote:
> After a long battle with technology, [EMAIL PROTECTED] ("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
> :-).
FYI, in a previous post on this topic, the original poster put up a top
output that showed the machine using 2 gigs of swap with 150 Meg for
kernel cache, and all the memory being used by a few postgresql
processes. The machine was simply configured to give WAY too much
memory to shared buffers and sort mem and this was likely causing the
big slowdown.
Adding a battery backed caching RAID controller and properly configuring
postgresql.conf should get him into the realm of reasonable performance.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
