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
On Fri, 2004-10-01 at 15:01, Igor Maciel Macaubas wrote:
> Hi Guys,
>
> I have a PostgreSQL server, running 7.4.2, that will store a really
> large amount of data (200GB) being migrated from an Oracle database. I
> have a machine with two 120GB Ultra ATA IDE disks,
I'd normally advise against AT
* Igor Maciel Macaubas ([EMAIL PROTECTED]) wrote:
> I have a PostgreSQL server, running 7.4.2, that will store a really large amount of
> data (200GB) being migrated from an Oracle database. I have a machine with two 120GB
> Ultra ATA IDE disks, and I'd like to know if PostgreSQL could split it o
Igor,
I would recommend you investigate LVM:
http://www.tldp.org/HOWTO/LVM-HOWTO/
This enables you to string multiple physical units into a single volume -
as well as expand and grow the volume. I am unsure about postgres
consequences of this for as much data as you have, so I will leave it up
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
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Igor Maciel Macaubas")
would write:
> Heather, I might be able to do it. Let me check .. I can buy a cheap
> RAID IDE controller and try to mount everything as one.
You'd be about as well off, if you're running Linux, to use the "md"
RAI
Hey all, its me again. If I do not do a count(product_id) on my
tsearch2 queries, its actually really fast, for example;
explain analyze SELECT product_id FROM product, to_tsquery('bear') AS q
WHERE vector @@ q LIMIT 1000;
QUERY PL
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
Hi,
Heather, I might be able to do it. Let me check .. I can buy a cheap RAID
IDE controller and try to mount everything as one.
I was looking at the change log of postgresql 8, and figured out that it
supports tablespace now, giving me the flexibility of storing my tables in
different disks an
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
Could you configure the two volumes as one volume with RAID? If so, then
Postgres shouldn't require any special adjusting to accomplish what you
are after.
Heather Johnson
Senior Database Programmer
New York Post
Igor Maciel Macaubas wrote:
Hi Guys,
I have a PostgreSQL server, running 7.4.2, t
Hi Guys,
I have a PostgreSQL server, running 7.4.2, that
will store a really large amount of data (200GB) being migrated from an Oracle database. I have a machine with two 120GB
Ultra ATA IDE disks, and I'd like to know if PostgreSQL could split it over both
disks (that gives me 240GB or st
-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
Hi... I take vast offense to the suggestion that my query / database design
is at fault here. I highly doubt it, although I AM willing to admit fault
where the fault is indeed my own. However, on the topic of sorting all
15,000 rows of the thread select query and then limiting the output to 25,
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
On Tue, Sep 28, 2004 at 17:19:20 -0400,
Angus Berry <[EMAIL PROTECTED]> wrote:
> Hi, I wonder if anyone can help.
>
> I'd like to execute a SQL query that performs an action on all the
> (unrelated) tables that I select. The following query doesn't work, but
> the subselect (which works) shows w
-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
Shane,
Two things: 1) when was the last time you vacuumed the
database? From my experience that has a large effect on the database
performance. 2) Why do you have so many postmaster processes? I've got
an active database but only one postmaster.
Dick Goulet
Senior Oracle DBA
Oracle
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 contention. Your sample q
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
"Gonzales,S" <[EMAIL PROTECTED]> writes:
> How do i prevent ordinary users from altering the template1 database,
> eg they can create tables in template1;
Revoke public's CREATE rights on the public schema in template1. See
the GRANT and REVOKE manual pages.
regards, tom
On Thu, 30 Sep 2004, Duane Winner wrote:
> Hello,
>
> We have a database with data loaded and now I need to GRANT
> SELECT,UPDATE,INSERT,DELETE to all tables in 4 different schemas for a
> specific postgresql user account.
>
> I have two problems:
>
> 1) One of these schemas has 75 tables. Is ther
On 2004-07-28, Bruce Momjian <[EMAIL PROTECTED]> wrote:
>
> Here is another open PITR issue that I think will have to be addressed
> in 7.6. If you do a critical transaction, but do nothing else for eight
> hours, that critical transaction hasn't been archived yet. It is still
> sitting in pg_xlo
Hello,
I'm setting up a new server with pgsql 7.4.3 and pg_autovacuum.
I'm going to set up a cronjob that shuts down the postmaster every night
and then perform a "cold backup" of the data directory.
My question is: should I shutdown pg_autovacuum as well or may it leave
the demon up and running
On 2004-08-18, Dave Cramer <[EMAIL PROTECTED]> wrote:
> Jerome,
>
> I'm curious what you expect out of clustering. High Availability, and
> Load Balancing seem possible, but Performance ? I would think there is a
> cost associated with clustering, that degrades performance.
Data in an RDBM
John McBride wrote:
Hello,
I have been playing with postgresql at home, working through a book on
general database installation and use. Mostly it is an oracle book, but
much of the book is easily translated to Postgres. This is on a fedora
core 2 linux box, kept fully yum updated.
Here are the
Hi, I wonder if anyone can help.
I'd like to execute a SQL query that performs an action on all the
(unrelated) tables that I select. The following query doesn't work, but
the subselect (which works) shows what I'm trying to do. All tables have
an identical column called id_num.
delete from
(sel
Hello,
We have a database with data loaded and now I need to GRANT
SELECT,UPDATE,INSERT,DELETE to all tables in 4 different schemas for a
specific postgresql user account.
I have two problems:
1) One of these schemas has 75 tables. Is there a way to do the GRANT
command with a wildcard to give
Is there any way to recover data after a mistaken delete query, using
the binary data files? For example, can I make a database return to the
state it was one day ago?
Thanks in advance,
Vangelis Natsios
---(end of broadcast)---
TIP 5: Have you che
How do i prevent ordinary users from altering the template1 database,
eg they can create tables in template1;
revoke doenst do the trick,
template1 remains visible and a user can create table in template1 using
for example pgadmin
select * from pg_database where datname like '%temp%';
datname
Theo Galanakis wrote:
I'm currently logging postgres dubugging to syslogs and wanted to know
if it is possible to log which database each log entry is refering to?
At the moment it looks something like this :
Sep 30 02:49:59 tickle postgres[31285]: [31-1] LOG: statement: select *
from content_
Hello.
I am running 8.0.0-beta3 on Mac OS X 10.3.5 and 8.0.0-beta2-dev3
(pginstaller) on windows 2000. I have two databases. The dumpfile
(format c) for database 1 is 150MB and the dumpfile for database 2 is
2GB.
Dumping the databases on the mac worked. I have loaded the dumpfiles in
another d
43 matches
Mail list logo
