The queries themselves are simple, normally drawing information from one
table with few conditions or in the most complex cases using joins on
two table or sub queries. These behave very well and always have, the
problem is that these queries take place in rather large amounts due to
the dumb na
Gaetano Mendola wrote:
Martin Foster wrote:
I run a Perl/CGI driven website that makes extensive use of PostgreSQL
(7.4.3) for everything from user information to formatting and display
of specific sections of the site. The server itself, is a dual
processor AMD Opteron 1.4Ghz w/ 2GB Ram and 2
explain analyze actually runs the query to do timings. Just run explain
and see what you come up with. More than likely there is a nestloop in
there which is causing the long query time.
Try bumping up shared buffers some and sort mem as much as you safely
can.
Just use an EXISTS query I suggest.
Martin Foster wrote:
I run a Perl/CGI driven website that makes extensive use of PostgreSQL
(7.4.3) for everything from user information to formatting and display
of specific sections of the site. The server itself, is a dual
processor AMD Opteron 1.4Ghz w/ 2GB Ram and 2 x 120GB hard drives
m
Marius Andreiana wrote:
On Tue, 2004-08-03 at 08:05 -0400, Merlin Moncure wrote:
Trying to run this query:
EXPLAIN ANALYSE
select * FROM trans
WHERE query_id NOT IN (select query_id FROM query)
but it will remain like that forever (cancelled after 30 min).
explain analyze actually runs the query to
Hello,
It sounds to me like you are IO bound. 2x120GB hard drives just isn't
going to cut it with that many connections (as a general rule). Are you
swapping ?
Sincerely,
Joshua D. Drake
Martin Foster wrote:
I run a Perl/CGI driven website that makes extensive use of PostgreSQL
(7.4.3) for ev
On Tue, 3 Aug 2004, Martin Foster wrote:
> to roughly 175 or more. Essentially, the machine seems to struggle
> to keep up with continual requests and slows down respectively as
> resources are tied down.
I suggest you try to find queries that are slow and check to see if the
plans are opt
On Tue, 2004-08-03 at 10:10, Merlin Moncure wrote:
> > > Try bumping up shared buffers some and sort mem as much as you
> safely
> > > can.
> > Thank you, that did it!
> >
> > With
> > shared_buffers = 3000 # min 16, at least max_connections*2,
> 8KB
> > each
> > sort_mem = 128000
I run a Perl/CGI driven website that makes extensive use of PostgreSQL
(7.4.3) for everything from user information to formatting and display
of specific sections of the site. The server itself, is a dual
processor AMD Opteron 1.4Ghz w/ 2GB Ram and 2 x 120GB hard drives
mirrored for redundanc
Matthew T. O'Connor wrote:
Lending, Rune wrote:
Hello all.
I am managing a large database with lots of transactions in different
tables.
The largest tables have around 5-6 millions tuples and around
5-6 inserts and maybe 2 updates pr day.
While the smalest tables have only a few tup
> > Try bumping up shared buffers some and sort mem as much as you
safely
> > can.
> Thank you, that did it!
>
> With
> shared_buffers = 3000 # min 16, at least max_connections*2,
8KB
> each
> sort_mem = 128000 # min 64, size in KB
>
> it takes <3 seconds (my hardware is not s
On Tue, 2004-08-03 at 08:05 -0400, Merlin Moncure wrote:
> > Trying to run this query:
> > EXPLAIN ANALYSE
> > select * FROM trans
> > WHERE query_id NOT IN (select query_id FROM query)
> >
> > but it will remain like that forever (cancelled after 30 min).
>
> explain analyze actually runs the qu
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> Try bumping up shared buffers some and sort mem as much as you safely
> can.
sort_mem is probably the issue here. The only reasonable way to do NOT
IN is with a hash table, and the default setting of sort_mem is probably
too small to support a 137042
Lending, Rune wrote:
Hello all.
I am managing a large database with lots of transactions in different
tables.
The largest tables have around 5-6 millions tuples and around
5-6 inserts and maybe 2 updates pr day.
While the smalest tables have only a few tuples and a few updates
/ins
On Tue, 3 Aug 2004, Marius Andreiana wrote:
> I just vacuum analyse'd the database.
>
> Trying to run this query:
> EXPLAIN ANALYSE
> select * FROM trans
> WHERE query_id NOT IN (select query_id FROM query)
>
> but it will remain like that forever (cancelled after 30 min).
>
> My postgresql.conf
> Trying to run this query:
> EXPLAIN ANALYSE
> select * FROM trans
> WHERE query_id NOT IN (select query_id FROM query)
>
> but it will remain like that forever (cancelled after 30 min).
explain analyze actually runs the query to do timings. Just run explain
and see what you come up with. More
Title: Melding
Hello
all.
I am managing a
large database with lots of transactions in different
tables.
The largest tables
have around 5-6 millions tuples and around 5-6 inserts and maybe 2
updates pr day.
While the smalest
tables have only a few tuples and a few updates /ins
Hi
I have 2 tables like this:
CREATE TABLE query (
query_idint not null,
dat varchar(64) null ,
sub_acc_id int null ,
query_ipvarchar(64) null ,
osd_user_type varchar(64) null
)
;
CREATE TABLE trans (
transaction_id
18 matches
Mail list logo
