---(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
Problem with this strategy. You want battery-backed write caching for
best performance & safety. (I've tried IDE for WAL before w/ write
caching off -- the DB got crippled whenever I had to copy files from/to
the drive on the WAL partition -- ended up just moving WAL back on the
same SCSI drive
On 4/15/05, Enrico Weigelt <[EMAIL PROTECTED]> wrote:
> a) SELECT items.a, items.b, ..., users.username FROM items, users
> WHERE items.uid = users.uid;
>
> c) CREATE FUNCTION id2username(oid) RETURNS text
> LANGUAGE 'SQL' IMMUTABLE AS '
> SELECT username AS RESULT FROM users W
> d) self-join with a function ;)
> EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username
> FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN
> aaa USING (n);
That's pretty clever.
It sure seems like the server was not caching the results of the
function...maybe
Another odd thing is when I tried turning off merge joins on the XP desktop
It took 32 secs to run compared to the 6 secs it was taking.
On the Linux (4proc box) it is now running in 3 secs with the mergejoins
turned off.
Unfortunately it takes over 2 minutes to actually return the 160,000+ rows.
What are the statistics
for tbljobtitle.id and tbljobtitle.clientnum
I added default_statistics_target = 250 to the config and re-loaded the data
base. If that is what you mean?
--- how many distinct values of each,
tbljobtitle.id 6764 for all clients 1018 for SAKS
tbljobtitle.clientnum 237 di
Sorry if this posts twice I posted and did not see it hit the list.
What are the statistics
for tbljobtitle.id and tbljobtitle.clientnum
I added default_statistics_target = 250 to the config and re-loaded the data
base. If that is what you mean?
--- how many distinct values of each,
tbljobtitl
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Joel Fradkin
> Sent: 18 April 2005 14:02
> To: PostgreSQL Perform
> Subject: FW: [PERFORM] speed of querry?
>
> Another odd thing is when I tried turning off merge joins on
> the XP desktop
> It
pgAdmin III uses libpq, not the ODBC driver.
Sorry I am not too aware of all the semantics.
I guess the question is if it is normal to take 2 mins to get 160K of
records, or is there something else I can do (I plan on limiting the query
screens using limit and offset; I realize this will only be e
On 4/18/05, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > d) self-join with a function ;)
> > EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username
> > FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN
> > aaa USING (n);
>
> That's pretty clever.
> It sure seems l
On Mon, 18 Apr 2005, Joel Fradkin wrote:
> Another odd thing is when I tried turning off merge joins on the XP desktop
> It took 32 secs to run compared to the 6 secs it was taking.
> On the Linux (4proc box) it is now running in 3 secs with the mergejoins
> turned off.
>
> Unfortunately it takes
Hi all,
Some months ago i post a similar problem here i it was solved by running
vaccumdb time by time.
So, when i started using the postgres, i never been used the vacuumdb, and
after 2 months i started using once a week, after few weeks, i tried once a
day and now twice a day.
At this weekend i
William Yu <[EMAIL PROTECTED]> writes:
> Using the above prices for a fixed budget for RAID-10, you could get:
>
> SATA 7200 -- 680MB per $1000
> SATA 10K -- 200MB per $1000
> SCSI 10K -- 125MB per $1000
What a lot of these analyses miss is that cheaper == faster because cheaper
means you can
> -Original Message-
> From: Greg Stark [mailto:[EMAIL PROTECTED]
> Sent: Monday, April 18, 2005 9:59 AM
> To: William Yu
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to improve db performance with $7K?
>
> William Yu <[EMAIL PROTECTED]> writes:
>
> > Using the abov
This is fundamentaly untrue.
A mirror is still a mirror. At most in a RAID 10 you can have two
simultaneous seeks. You are always going to be limited by the seek
time of your drives. It's a stripe, so you have to read from all
members of the stripe to get data, requiring all drives to seek.
Th
Hi,
I thought that an index can be used for sorting.
I'm a little confused about the following result:
create index OperationsName on Operations(cOperationName);
explain SELECT * FROM Operations ORDER BY cOperationName;
QUERY PLAN
---
> -Original Message-
> From: Andrei Gaspar [mailto:[EMAIL PROTECTED]
> Sent: Monday, April 18, 2005 10:36 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Sort and index
>
> I thought that an index can be used for sorting.
> I'm a little confused about the following result:
>
Alex Turner <[EMAIL PROTECTED]> writes:
> This is fundamentaly untrue.
>
> A mirror is still a mirror. At most in a RAID 10 you can have two
> simultaneous seeks. You are always going to be limited by the seek
> time of your drives. It's a stripe, so you have to read from all
> members of the
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
>> d) self-join with a function ;)
>> EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username
>> FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN
>> aaa USING (n);
> That's pretty clever.
> It sure seems like the server
"Rodrigo Moreno" <[EMAIL PROTECTED]> writes:
> At this weekend i have started to use pg_autovacuum with default settings.
> I really worried about that, because it's no enough anymore, and users claim
> about performace. But running the vacuumdb full, everthing starts to run
> better again, so i t
Thanks for the quick response
Andrei
Dave Held wrote:
-Original Message-
From: Andrei Gaspar [mailto:[EMAIL PROTECTED]
Sent: Monday, April 18, 2005 10:36 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Sort and index
I thought that an index can be used for sorting.
I'm a little c
Tom,
How to check if the value it's enough ? The log generate by vacuum verbose
can help ?
The current values for:
max_fsm_pages = 1048576
max_fsm_relations = 1000
this is enough ?
Regards,
Rodrigo
-Mensagem original-
De: Tom Lane [mailto:[EMAIL PROTECTED]
Enviada em: segunda-fe
[snip]
>
> Adding drives will not let you get lower response times than the average seek
> time on your drives*. But it will let you reach that response time more often.
>
[snip]
I believe your assertion is fundamentaly flawed. Adding more drives
will not let you reach that response time more o
On Mon, Apr 18, 2005 at 10:44:43AM -0500, Dave Held wrote:
> >
> > I thought that an index can be used for sorting.
> > I'm a little confused about the following result:
> >
> > create index OperationsName on Operations(cOperationName);
> > explain SELECT * FROM Operations ORDER BY cOperationName
"Rodrigo Moreno" <[EMAIL PROTECTED]> writes:
> The current values for:
> max_fsm_pages = 1048576
> max_fsm_relations = 1000
> this is enough ?
That max_fsm_pages value is enough to cover 8Gb, so it should work OK
for a database disk footprint up to 10 or so Gb. I don't know how many
tables in
Hi,
At 18:56 18/04/2005, Alex Turner wrote:
All drives are required to fill every request in all RAID levels
No, this is definitely wrong. In many cases, most drives don't actually
have the data requested, how could they handle the request?
When reading one random sector, only *one* drive out of
Alex Turner wrote:
[snip]
Adding drives will not let you get lower response times than the average seek
time on your drives*. But it will let you reach that response time more often.
[snip]
I believe your assertion is fundamentaly flawed. Adding more drives
will not let you reach that resp
Alex Turner wrote:
[snip]
Adding drives will not let you get lower response times than the average seek
time on your drives*. But it will let you reach that response time more often.
[snip]
I believe your assertion is fundamentaly flawed. Adding more drives
will not let you reach that response t
Hi,
At 16:59 18/04/2005, Greg Stark wrote:
William Yu <[EMAIL PROTECTED]> writes:
> Using the above prices for a fixed budget for RAID-10, you could get:
>
> SATA 7200 -- 680MB per $1000
> SATA 10K -- 200MB per $1000
> SCSI 10K -- 125MB per $1000
What a lot of these analyses miss is that cheaper
Alex,
In the situation of the animal hospital server I oversee, their
application is OLTP. Adding hard drives (6-8) does help performance.
Benchmarks like pgbench and OSDB agree with it, but in reality users
could not see noticeable change. However, moving the top 5/10 tables and
indexes to the
> That max_fsm_pages value is enough to cover 8Gb, so it should work OK for
a database disk footprint up to 10 or so Gb. > I don't know how many tables
in your installation so I can't say if max_fsm_relations is high enough, but
you can check >that by looking at the tail end of the output of VACUU
Not true - the recommended RAID level is RAID 10, not RAID 0+1 (at
least I would never recommend 1+0 for anything).
RAID 10 and RAID 0+1 are _quite_ different. One gives you very good
redundancy, the other is only slightly better than RAID 5, but
operates faster in degraded mode (single drive).
I think the add more disks thing is really from the point of view that
one disk isn't enough ever. You should really have at least four
drives configured into two RAID 1s. Most DBAs will know this, but
most average Joes won't.
Alex Turner
netEconomist
On 4/18/05, Steve Poe <[EMAIL PROTECTED]> w
Ok - well - I am partially wrong...
If you're stripe size is 64Kb, and you are reading 256k worth of data,
it will be spread across four drives, so you will need to read from
four devices to get your 256k of data (RAID 0 or 5 or 10), but if you
are only reading 64kb of data, I guess you would only
So I wonder if one could take this stripe size thing further and say
that a larger stripe size is more likely to result in requests getting
served parallized across disks which would lead to increased
performance?
Again, thanks to all people on this list, I know that I have learnt a
_hell_ of alot
Jacques Caron <[EMAIL PROTECTED]> writes:
> When writing:
> - in RAID 0, 1 drive
> - in RAID 1, RAID 0+1 or 1+0, 2 drives
> - in RAID 5, you need to read on all drives and write on 2.
Actually RAID 5 only really needs to read from two drives. The existing parity
block and the block you're replac
Alex Turner wrote:
Not true - the recommended RAID level is RAID 10, not RAID 0+1 (at
least I would never recommend 1+0 for anything).
Uhmm I was under the impression that 1+0 was RAID 10 and that 0+1 is NOT
RAID 10.
Ref: http://www.acnc.com/raid.html
Sincerely,
Joshua D. Drake
---
Hi,
At 20:16 18/04/2005, Alex Turner wrote:
So my assertion that adding more drives doesn't help is pretty
wrong... particularly with OLTP because it's always dealing with
blocks that are smaller that the stripe size.
When doing random seeks (which is what a database needs most of the time),
the n
Tom Lane <[EMAIL PROTECTED]> writes:
> It would be interesting sometime to try to teach the planner about
> inlining SQL-language functions to become joins. That is, given
>
> create function id2name(int) returns text as
> 'select name from mytab where id = $1' language sql stable;
>
> select u
Don't you think "optimal stripe width" would be
a good question to research the binaries for? I'd
think that drives the answer, largely. (uh oh, pun alert)
EG, oracle issues IO requests (this may have changed _just_
recently) in 64KB chunks, regardless of what you ask for.
So when I did my stri
Hi,
At 20:21 18/04/2005, Alex Turner wrote:
So I wonder if one could take this stripe size thing further and say
that a larger stripe size is more likely to result in requests getting
served parallized across disks which would lead to increased
performance?
Actually, it would be pretty much the opp
Mistype.. I meant 0+1 in the second instance :(
On 4/18/05, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> Alex Turner wrote:
> > Not true - the recommended RAID level is RAID 10, not RAID 0+1 (at
> > least I would never recommend 1+0 for anything).
>
> Uhmm I was under the impression that 1+0 was
Added to TODO:
* Compress WAL entries [wal]
I have also added this email to TODO.detail.
---
Simon Riggs wrote:
> On Sun, 2005-04-10 at 21:12 -0400, Bruce Momjian wrote:
> > Jim C. Nasby wrote:
> > > Maybe better f
All,
A couple of questions regarding REINDEX command:
Running PostgreSQL 7.4.2 on Solaris.
1) When is it necessary to run REINDEX or drop/create
an index? All I could really find in the docs is:
"In some situations it is worthwhile to rebuild
indexes periodically with the REINDEX command. (The
All,
If I run the command "vacuumdb mydb" I understand that
it does some disk space recovery (but not as much as
"vacuumdb --full mydb").
Question: if I run the command "vacuumdb --analyze
mydb" does it still do the aforementioned disk space
recovery AS WELL AS update query planning statistics?
On 4/18/05, Jacques Caron <[EMAIL PROTECTED]> wrote:
> Hi,
>
> At 20:21 18/04/2005, Alex Turner wrote:
> >So I wonder if one could take this stripe size thing further and say
> >that a larger stripe size is more likely to result in requests getting
> >served parallized across disks which would lea
Bill,
> 1) When is it necessary to run REINDEX or drop/create
> an index? All I could really find in the docs is:
If you need to VACUUM FULL, you need to REINDEX as well. For example, if you
drop millions of rows from a table.
> 2) If reindexing is necessary, how can this be done in
> a non-o
On Mon, Apr 18, 2005 at 12:21:42 -0700,
Bill Chandler <[EMAIL PROTECTED]> wrote:
>
> Running PostgreSQL 7.4.2 on Solaris.
>
> 1) When is it necessary to run REINDEX or drop/create
> an index? All I could really find in the docs is:
>
> "In some situations it is worthwhile to rebuild
> indexes
On Mon, Apr 18, 2005 at 12:27:08 -0700,
Bill Chandler <[EMAIL PROTECTED]> wrote:
> All,
>
> If I run the command "vacuumdb mydb" I understand that
> it does some disk space recovery (but not as much as
> "vacuumdb --full mydb").
You are better off not using vacuum full unless some unusual eve
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> It would be interesting sometime to try to teach the planner about
>> inlining SQL-language functions to become joins. That is, given
> The Inlining of the function is presumably a side-issue. I have tons of
> querie
Josh Berkus writes:
>> 1) When is it necessary to run REINDEX or drop/create
>> an index? All I could really find in the docs is:
> If you need to VACUUM FULL, you need to REINDEX as well. For example, if you
> drop millions of rows from a table.
That's probably a pretty good rule of thumb. I
Oooops, I revived the never-ending $7K thread. :)
Well part of my message is to first relook at the idea that SATA is
cheap but slow. Most people look at SATA from the view of consumer-level
drives, no NCQ/TCQ -- basically these drives are IDEs that can connect
to SATA cables. But if you then lo
Kevin Brown wrote:
> Greg Stark wrote:
>
>
> > I think you're being misled by analyzing the write case.
> >
> > Consider the read case. When a user process requests a block and
> > that read makes its way down to the driver level, the driver can't
> > just put it aside and wait until it's conven
Does it really matter at which end of the cable the queueing is done
(Assuming both ends know as much about drive geometry etc..)?
Alex Turner
netEconomist
On 4/18/05, Bruce Momjian wrote:
> Kevin Brown wrote:
> > Greg Stark wrote:
> >
> >
> > > I think you're being misled by analyzing the write
On Mon, Apr 18, 2005 at 06:49:44PM -0400, Alex Turner wrote:
> Does it really matter at which end of the cable the queueing is done
> (Assuming both ends know as much about drive geometry etc..)?
That is a pretty strong assumption, isn't it? Also you seem to be
assuming that the controller<->disk
On 4/14/05, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> That's basically what it comes down to: SCSI lets the disk drive itself
> do the low-level I/O scheduling whereas the ATA spec prevents the drive
> from doing so (unless it cheats, ie, caches writes). Also, in SCSI it's
> possible for the drive
I am new to cross references between tables, and I am trying to
understand how they impact performance. From reading the documentation I
was under the impression that deffering foreign keys would yield about
the same performance as dropping them before a copy, and adding them
after. However, I
Hmmm. My brain is being jostled and I'm confusing illustra-postgres,
informix-postgres and postgresql. Some things had functions and
some things had constants and I do not remember which products had
what combination. But probably how they are in postgresql, post
hellerstein, is how I am rememberi
Alex Turner wrote:
> Does it really matter at which end of the cable the queueing is done
> (Assuming both ends know as much about drive geometry etc..)?
Good question. If the SCSI system was moving the head from track 1 to
10, and a request then came in for track 5, could the system make the
hea
Since the database I am working on has many FKs, I would rather not have to
drop/add them when I am loading large data sets.
You may want to hunt the archives. IIRCC I saw a couple of posts in the
recent months about an update you can do to one of the system tables to disable
the key checks and t
On Sun, Apr 17, 2005 at 10:05:29PM +0200, Rüdiger Herrmann wrote:
>
> I need to write several PL/pgSQL functions all returning a "result set" wich
> can be obtained by a single SELECT statement.
> For now the functions are called by a Java application.
> Both REFCURSOR and SETOF serve my purpose,
61 matches
Mail list logo
