it sometimes happens that mysql is faster .obviosly you have a case
when you have to try force the query to use indexes .
here are some not nice and trivial solutions:
[1] add another column that marks the rows that are in the upper 25
now, set up trigger to update the field .that would be VERY
Are there any updates/deleteed/adding to the db?What
did help you ?Are you using transactions?
> gain in performance. All of my optimizations seem to help at first, but
> everything soon gets just as slow as it was before... and I mean SLOW.
>
--
Evgeny.
---(end of bro
On Sun, 3 Oct 2004, Gaetano Mendola wrote:
> Scott Marlowe wrote:
> > On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote:
> >
> >>On Fri, 1 Oct 2004, Scott Marlowe wrote:
> >>
> >>
> >>>On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> >>>
> Okay, just so no one posts about this again...
Sounds like all you need to do is to check what keys your app needs on
the tables and then ensure that you have these set up.
There is no way that MySQL will be faster than PG... seems like your
MySQL DB was optimised and your PG wasn't.
Should be very easy and quick to sort out.
Brad
Shane | Sk
William Yu wrote:
Shane | SkinnyCorp wrote:
I would like to also add that I've been a sysadmin for quite some
time, and
I've been designing databases for quite some time as well. I'm no
idiot, I
just can't find the bottleneck here (if one does in fact exist). So in
light
of this, please send me
Scott Marlowe wrote:
On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote:
On Fri, 1 Oct 2004, Scott Marlowe wrote:
On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a stat
On Sat, 2004-10-02 at 15:42, Stephan Szabo wrote:
> On Sat, 2 Oct 2004, Scott Marlowe wrote:
> > So would a union give good performance? Just union the first 25 or less
> > with status=5 with the rest, using a 1 and 0 in each union to order by
> > first? Hopefully the indexes would then be used.
On Sat, 2 Oct 2004, Scott Marlowe wrote:
> On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote:
> > On Fri, 1 Oct 2004, Scott Marlowe wrote:
> >
> > > On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> > > > Okay, just so no one posts about this again...
> > > >
> > > > the 'ORDER BY t.status=5
On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote:
> On Fri, 1 Oct 2004, Scott Marlowe wrote:
>
> > On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> > > Okay, just so no one posts about this again...
> > >
> > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
> > >
On Fri, 1 Oct 2004, Scott Marlowe wrote:
> On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> > Okay, just so no one posts about this again...
> >
> > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
> > with a status of '5' to the top of the list... it is NOT meant
On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> Okay, just so no one posts about this again...
>
> the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
> with a status of '5' to the top of the list... it is NOT meant to only grab
> threads where the status = 5. Oh and
William Yu wrote:
There are cases where seqscan will be faster than indexscans. For
example, your query to retrieve the latest 25 threads -- always faster
using seqscan. If it was using indexscan, that would explain the 9
seconds to run because the HD heads would have to jump back & forth from
Got any suggestions now?!? I was sort of looking for more information /
insight on my postgresql.conf file... but it seems we had to get the "IS HE
A MORON" question answered :P
Anyhow, again thank you for any help you can lend...
Well, try not to SHOUT is a good suggestion. Also, how about p
On Friday 01 October 2004 12:26 pm, Shane | SkinnyCorp wrote:
> Funny...
>
> I vacuum full EVERY night @ midnight...
>
> And yes, that's great about your similar machine with more RAM...
> only... does YOUR table have 60+ users @ 120-some queries per
> second at any given moment?
>
> ...
>
> I didn
On Fri, 1 Oct 2004, Michael Paesold wrote:
> Shane | SkinnyCorp wrote:
>
> > Okay, just so no one posts about this again...
> >
> > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
> > with a status of '5' to the top of the list... it is NOT meant to only
> > grab
> > thre
Shane | SkinnyCorp wrote:
I would like to also add that I've been a sysadmin for quite some time, and
I've been designing databases for quite some time as well. I'm no idiot, I
just can't find the bottleneck here (if one does in fact exist). So in light
of this, please send me some suggestions I ca
Shane | SkinnyCorp wrote:
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only
grab
threads where the status = 5. Oh and believe me, when I take this out of
th
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Friday 01 October 2004 01:26 pm, Shane | SkinnyCorp wrote:
> Okay, just so no one posts about this again...
>
> the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
> with a status of '5' to the top of the list... it is NOT meant
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY does
while you weren't looking, Shane | SkinnyCorp wrote:
> How else do you suggest I grab the 25 most recent
> threads posted?!?
select *
from thread_listing t
where t.status = 5
order by lastreply desc
limit 25
offset 0
The WHERE clause is there to limit the number of tuples you're looking
a
I would like to also add that I've been a sysadmin for quite some time, and
I've been designing databases for quite some time as well. I'm no idiot, I
just can't find the bottleneck here (if one does in fact exist). So in light
of this, please send me some suggestions I can work with.
Here are thr
Shane | SkinnyCorp wrote:
> # PGSQL Version 7.4.2
Upgrade to 7.4.5
> #---
> # RESOURCE USAGE (except WAL)
>
> #---
> # - Memory -
> shared_buffers = 81
Hi Shane,
As many others have alluded to - performance like this is almost always
attributable to your queries not using an index. Be it on Oracle, Mysql,
or postgres, i have seen this problem popup often.
Also, could you tell us what language you are using, and if you are using
a DB abstractio
o: "Shane | SkinnyCorp" <[EMAIL PROTECTED]>; "PgSQL ADMIN"
<[EMAIL PROTECTED]>
Sent: Friday, October 01, 2004 11:46 AM
Subject: Re: [ADMIN] PLEASE GOD HELP US!
> Shane,
>
> Two things: 1) when was the last time you vacuumed the
> database? From my ex
On Friday 01 October 2004 9:04 am, Shane | SkinnyCorp wrote:
> Hey, my name is Shane Witschen and I'm the Systems Administrator
> (mainly a developer) for a small web development company. We
> recently switched over to PostgreSQL after going over some of the
> powerful features that it holds over
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Friday 01 October 2004 09:04 am, you wrote:
> SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
> DESC,t.lastreply desc LIMIT 25 OFFSET 0
> Num Rows:25
> Affected Rows:0
> Exec Time: 9.160265922546
racle Certified 8i DBA
-Original Message-
From: Shane | SkinnyCorp [mailto:[EMAIL PROTECTED]
Sent: Friday, October 01, 2004 12:05 PM
To: PgSQL ADMIN
Subject: [ADMIN] PLEASE GOD HELP US!
Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly
a
developer) for a
I don't think ORDER BY X=Y will use an index even if casted. I may be wrong.
We're still using 7.3.5.
Joshua D. Drake wrote:
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows:25
Affected Rows:0
Exec
-Original Message-
> From: [EMAIL PROTECTED]
> On Behalf Of Shane | SkinnyCorp
> Sent: Friday, October 01, 2004 10:05 AM
> To: PgSQL ADMIN
> Subject: [ADMIN] PLEASE GOD HELP US!
>
>
You can use pgmonitor to visually see general load and check if there is
any con
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows:25
Affected Rows:0
Exec Time: 9.1602659225464
Is t.status a big or small int? You might need to cast it... This also
goes for the other queries belo
while you weren't looking, Shane | SkinnyCorp wrote:
> Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a
> developer) for a small web development company. We recently switched over
> to PostgreSQL after going over some of the powerful features that it holds
> over MySQL.
Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a
developer) for a small web development company. We recently switched over
to PostgreSQL after going over some of the powerful features that it holds
over MySQL.
However, after we launched one site, we slapped ourselves in
32 matches
Mail list logo
