On Thu, 2004-06-17 at 13:09 -0700, Patrick Hatcher wrote:
>
>
>
> Pg: 7.4.2
> RedHat 7.3
> Ram: 8gig
>
> I have 6 million row table that I vacuum full analyze each night. The time
> seems to be streching out further and further as I add more rows. I read
> the archives and Josh's annotated p
Dear Gurus,
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
Sent: Thursday, June 10, 2004 7:14 PM
>
> On Thu, 10 Jun 2004, Stephan Szabo wrote:
>
> >
> > On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:
> >
> > > I agree, but it should be a simple rewrite. No?
> >
> > It's N
I'm trying to migrate an application from an Oracle
backend to PostgreSQL and have a performance question.
The hardware for the database is the same, a SunFire
v120, 2x73GB U2W SCSI disks, 1GB RAM, 650MHz US-IIe
CPU. Running Solaris 8.
The table in question has 541741 rows. Under Oracle,
the que
Gary Cowell wrote:
I'm not as familiar with postgresql as I am with
Oracle but I think I've configured comparible
buffering and sort area sizes, certainly there isn't
much physical IO going on in either case.
People are going to want to know:
1. version of PG
2. explain analyse output, rather than
On 18/06/2004 12:31 Gary Cowell wrote:
[snip]
I'm not as familiar with postgresql as I am with
Oracle but I think I've configured comparible
buffering and sort area sizes, certainly there isn't
much physical IO going on in either case.
What can I do to speed up this query? Other queries
are slightl
On Jun 18, 2004, at 7:31 AM, Gary Cowell wrote:
The explain output on postgres shows the same
execution with a scan on vers and a sort but the query
time is 78.6 seconds.
Does it run just as slow if you run it again?
It could be a case of the caches being empty
Oracle but I think I've configured co
Gary Cowell wrote:
The explain output on postgres shows the same
execution with a scan on vers and a sort but the query
time is 78.6 seconds.
The explain output from PostgreSQL is:
QUERY PLAN
Hi everyone .
How much memory should I give to the kernel and postgresql
I have 1G of memory and 120G of HD
Shared Buffers = ?
Vacuum Mem = ?
SHMAX = ?
Sorry I have so many question .I am a newbie L
I have 30G of data
At least 30 simultaneus users
But I will use it
--- [EMAIL PROTECTED] wrote: > You can roughly estimate time
spent for just scaning
> the table using
> something like this:
>
> select sum(version) from ... where version is not
> null
>
> and just
>
> select sum(version) from ...
>
> The results would be interesting to com
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
On Fri, 18 Jun 2004, Michael Ryan S. Puncia wrote:
> How much memory should I give to the kernel and postgresql
>
> I have 1G of memory and 120G of HD
>
> Shared Buffers = ?
>
> Vacuum Mem = ?
Maybe you should read
http://www.varlena.com/va
Hi ,
I have similare problem and found that the problem is by pg sort.
It is extremly slow by me.
Also in my case I tryed to migrate one db from oracle to pg .
To solve this problem I dinamicaly set sort_mem to some big value.
In this case the sort is working into RAM and is relative fast.
Yo
The problems still remains...
I've tried to change shmax to 128 mb (i've got 2 GB of ram),
then the others parameter are set as follow:
shared_buffers = 8096# 2*max_connections, min 16
max_fsm_relations = 500# min 10, fsm is free space map
max_fsm_pages = 15000 # min 1000, fsm is
Michael Ryan S. Puncia wrote:
Hi everyone .
How much memory should I give to the kernel and postgresql
I have 1G of memory and 120G of HD
Devrim's pointed you to a guide to the configuration file. There's also
an introduction to performance tuning on the same site.
An important thing to rememb
Gary Cowell wrote:
--- [EMAIL PROTECTED] wrote: > You can roughly estimate time
spent for just scaning
the table using
something like this:
select sum(version) from ... where version is not
null
and just
select sum(version) from ...
The results would be interesting to compare.
=?iso-8859-1?Q?SZUCS_G=E1bor?= <[EMAIL PROTECTED]> writes:
>> It's NULLs inside the subselect that are the issue.
>
> select 1 in (select a from foo)
> select exists ( select 1 from foo where a=1)
> Just a dumb try :)
> SELECT (exists(select 1 from foo where a isnull) AND NULL)
>OR exi
=?iso-8859-1?q?Gary=20Cowell?= <[EMAIL PROTECTED]> writes:
>-> Sort (cost=117865.77..119220.13 rows=541741
> width=132) (actual time=63623.417..66127.641
> rows=541741 loops=1)
This is clearly where the time is going.
> sort_mem = 16384
Probably not enough for this problem. The estim
"Domenico Sgarbossa" <[EMAIL PROTECTED]> writes:
> so when the users go home, i've got something like 15/2kb free ram, the
> rest is cached and 0kb of swap...
> It seems that when pg_dump starts the cached memory isn't released so the
> system begin to swap,
A sane kernel should drop disk buff
Hi,
Tom Lane wrote:
=?iso-8859-1?q?Gary=20Cowell?= <[EMAIL PROTECTED]> writes:
-> Sort (cost=117865.77..119220.13 rows=541741
width=132) (actual time=63623.417..66127.641
rows=541741 loops=1)
This is clearly where the time is going.
sort_mem = 16384
> Try increasing sort_mem temporarily, and see if that
> makes a difference:
>SET sort_mem = 64000;
>EXPLAIN ANALYSE ...
I did this (actualy 65536) and got the following:
pvcsdb=# explain analyze select distinct version from
vers where version is not null;
On Fri, 18 Jun 2004, [iso-8859-1] SZUCS Gábor wrote:
> Dear Gurus,
>
> - Original Message -
> From: "Stephan Szabo" <[EMAIL PROTECTED]>
> Sent: Thursday, June 10, 2004 7:14 PM
>
>
> >
> > On Thu, 10 Jun 2004, Stephan Szabo wrote:
> >
> > >
> > > On Thu, 10 Jun 2004, Jean-Luc Lachance wrote
* Tom Lane ([EMAIL PROTECTED]) wrote:
> [... thinks for awhile ...] It seems possible that they may use sort
> code that knows it is performing a DISTINCT operation and discards
> duplicates on sight. Given that there are only 534 distinct values,
> the sort would easily stay in memory if that we
Stephen Frost <[EMAIL PROTECTED]> writes:
> * Tom Lane ([EMAIL PROTECTED]) wrote:
>> [... thinks for awhile ...] It seems possible that they may use sort
>> code that knows it is performing a DISTINCT operation and discards
>> duplicates on sight. Given that there are only 534 distinct values,
>>
* Tom Lane ([EMAIL PROTECTED]) wrote:
> Stephen Frost <[EMAIL PROTECTED]> writes:
> > * Tom Lane ([EMAIL PROTECTED]) wrote:
> >> [... thinks for awhile ...] It seems possible that they may use sort
> >> code that knows it is performing a DISTINCT operation and discards
> >> duplicates on sight. G
On Fri, 2004-06-18 at 09:11, Tom Lane wrote:
> "Domenico Sgarbossa" <[EMAIL PROTECTED]> writes:
> > so when the users go home, i've got something like 15/2kb free ram, the
> > rest is cached and 0kb of swap...
> > It seems that when pg_dump starts the cached memory isn't released so the
> > sys
* Stephen Frost ([EMAIL PROTECTED]) wrote:
> systems does in 40 seconds. My only other concern is the Oracle system
> having to do the write I/O while the postgres one doesn't... I don't
> see an obvious way to get around that though, and I'm not sure if it'd
> really make *that* big of a differe
I believe it was more like the kernel was tuned to make it less common,
but certain things can still trigger it. I know the problem was still
there in the 2.4.24 on the last server I was playing with, but it was a
lot less of a problem than it had been under 2.4.9 on an earlier machine
with the sa
Hello,
I would have to double check BUT I believe this is fixed in later 2.4.x
kernels as well. If you don't want to go through the hassle of 2.6
(although it really is a nice kernel) then upgrade to 2.4.26.
Sincerely,
Joshau D. Drake
Scott Marlowe wrote:
On Fri, 2004-06-18 at 09:11, Tom Lane wr
=?iso-8859-1?q?Gary=20Cowell?= <[EMAIL PROTECTED]> writes:
> So it seems the idea that oracle is dropping duplicate
> rows prior to the sort when using distinct may indeed
> be the case.
Okay. We won't have any short-term solution for making DISTINCT do that,
but if you are on PG 7.4 you could ge
--- Tom Lane <[EMAIL PROTECTED]> wrote: >
=?iso-8859-1?q?Gary=20Cowell?=
> <[EMAIL PROTECTED]> writes:
> > So it seems the idea that oracle is dropping
> duplicate
> > rows prior to the sort when using distinct may
> indeed
> > be the case.
>
> Okay. We won't have any short-term solution for
> ma
29 matches
Mail list logo
