Mitch Pirtle wrote:
On Mon, 13 Dec 2004 09:23:13 -0800, Joshua D. Drake
[EMAIL PROTECTED] wrote:
RAID 10 will typically always outperform RAID 5 with the same HD config.
Isn't RAID10 just RAID5 mirrored? How does that speed up performance?
Or am I missing something?
-- Mitch
Hi Mitch,
Nope,
Madison Kelly wrote:
Nope, Raid 10 (one zero) is a mirror is stripes, no parity. with r10
Woops, that should be mirror of stripes.
By the way, what you are thinking of is possible, it would be 51 (five
one; a raid 5 built on mirrors) or 15 (a mirror of raid 5 arrays).
Always be careful, 10
Hi all,
Is there a fast(er) way to get the sum of all integer values for a
certain condition over many thousands of rows? What I am currently doing
is this (which takes ~5-10sec.):
SELECT SUM (a.file_size) FROM file_info_1 a, file_set_1 b WHERE
a.file_name=b.fs_name AND
Richard Huxton wrote:
Madison Kelly wrote:
Hi all,
Is there a fast(er) way to get the sum of all integer values for a
certain condition over many thousands of rows? What I am currently
doing is this (which takes ~5-10sec.):
OK, I'm assuming you've configured PG to your satisfaction
the index? Should I have just used the
'file_parent_dir' and 'file_name'?
Thanks all!!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
code to update each subdirectory separately and use simpler
UPDATE statement for each.
Thanks again!
Madison
PS - I cc'ed the list to follow up on what I found out so far. (Hi list!)
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
Here is my full query:
tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type
FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC,
file_name ASC;
This is my index (which I guess is wrong):
tle
Bruno Wolff III wrote:
On Sun, Jun 12, 2005 at 18:52:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
After sending that email I kept plucking away and in the course of
doing so decided that I didn't need to return the 'file_type' column.
Other than that, it would see my query now matches
Bruno Wolff III wrote:
On Sun, Jun 12, 2005 at 23:42:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
As you probably saw in my last reply, I went back to the old index and
tried the query you and Tom Lane recommended. Should this not have
caught the index?
Probably, but there might
!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 8: explain analyze is your friend
somewhat of a n00b, I agree with the idea
of a smarter, more flexible planner. I guess the trade off is the added
overhead neaded versus the size of the average query.
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
Bruno Wolff III wrote:
Please actually try this before changing anything else.
If I follow then I tried it but still got the sequential scan.
Given the fairly large number of rows being selected, it seems likely
(16GB RAM should
allow for that plus other OS overhead).
Well, I'm relatively new so defer to others but this is my suggestion.
Best of luck!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
Bruno Wolff III wrote:
On Mon, Jun 13, 2005 at 15:05:00 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
Wow!
With the sequence scan off my query took less than 2sec. When I turned
it back on the time jumped back up to just under 14sec.
tle-bu= set enable_seqscan = off; SET
tle-bu= EXPLAIN
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
So the index obiously provides a major performance boost! I just need
to figure out how to tell the planner how to use it...
Simple division shows that the planner's cost estimate ratio between the
seqscan and the indexscan (11956.84
nothing -- postgresql will figure it out
}
That is a wonderful idea and I already have the foundation in place to
easily implement this. Thanks!!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle
/pointers!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---(end of broadcast)---
TIP 3: if posting/reading
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
Can anyone see why the index might not be being used?
You didn't initdb in 'C' locale. You can either re-initdb,
or create a specialized index with a non-default operator class
to support LIKE. See the documentation
Hi all,
I hope I am not asking too many questions. :)
I have been trying to solve a performance problem in my program for a
while now and, after getting an index to work which didn't speed things
up enough, I am stumped. I am hoping someone here might have come across
a similar issue and
PFC wrote:
Hello,
I once upon a time worked in a company doing backup software and I
remember these problems, we had exactly the same !
Prety neat. :)
The file tree was all into memory and everytime the user clicked on
something it haaad to update everything. Being C++ it
Hi all,
I am trying to do an update on a table but so far I can't seem to
come up with a usable index. After my last question/thread the user
'PFC' recommended I store whether a file was to be backed up as either
't'(rue), 'f'(alse) or 'i'(nherit) to speed up changing files and sub
: 60.359 ms
(4 rows)
Bingo!
Hopefully someone might find this useful in the archives. :p
Madison
Madison Kelly wrote:
Hi all,
I am trying to do an update on a table but so far I can't seem to come
up with a usable index. After my last question/thread the user 'PFC'
recommended I store
because it's a little convoluded. ^_^;
Thanks as always!
Madison
Where the big performance concern is when
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU; The Linux Experience, Back Up
Main Project Page: http://tle-bu.org
Community Forum:http
Stephen Frost wrote:
* Madison Kelly ([EMAIL PROTECTED]) wrote:
If the performace difference comes from the 'COPY...' command being
slower because of the automatic quoting can I somehow tell PostgreSQL
that the data is pre-quoted? Could the performance difference be
something else?
I
DBs (using v4.1 for MySQL which is also
mature at this point).
As others mentioned though, so far the most likely explanation is the
'fsync' being enabled on PostgreSQL.
Thanks for the reply!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU
Hi all,
I've got a script (perl, in case it matters) that I need to run once
a month to prepare statements. This script queries and updates the
database a *lot*. I am not concerned with the performance of the SQL
calls so much as I am about the impact it has on the server's load.
Is there a
Scott Marlowe wrote:
On Thu, 2006-11-02 at 09:14, Madison Kelly wrote:
Hi all,
I've got a script (perl, in case it matters) that I need to run once
a month to prepare statements. This script queries and updates the
database a *lot*. I am not concerned with the performance of the SQL
calls
Tobias Brox wrote:
[Madison Kelly - Thu at 10:25:07AM -0500]
Will the priority of the script pass down to the pgsql queries it calls?
I figured (likely incorrectly) that because the queries were executed by
the psql server the queries ran with the server's priority.
I think you are right
Scott Marlowe wrote:
nope, the priorities don't pass down. you connect via a client lib to
the server, which spawns a backend process that does the work for you.
The backend process inherits its priority from the postmaster that
spawns it, and they all run at the same priority.
Shoot, but
Andreas Kostyrka wrote:
Am Donnerstag, den 02.11.2006, 09:41 -0600 schrieb Scott Marlowe:
Sometimes it's the simple solutions that work best. :) Welcome to the
world of pgsql, btw...
OTOH, there are also non-simple solutions to this, which might make
sense anyway: Install slony, and run
Tobias Brox wrote:
[Madison Kelly - Mon at 08:10:12AM -0500]
to run, which puts it into your drawback section. The server in
question is also almost under load of some sort, too.
A great tip and one I am sure to make use of later, thanks!
I must have been sleepy, listing up cons vs
Tobias Brox wrote:
[Madison Kelly - Mon at 08:48:19AM -0500]
Ah, sorry, long single queries is what you meant.
No - long running single transactions :-) If it's only read-only
queries, one will probably benefit by having one transaction for every
query.
In this case, what happens is one
Hi all,
I am asking in this list because, at the end of the day, this is a
performance question.
I am looking at writing a search engine of sorts for my database. I
have only ever written very simple search engines before which amounted
to not much more that the query string being used
Mark Stosberg wrote:
Madison Kelly wrote:
I think the more direct question I was trying to get at is How do you
build a 'relavence' search engine? One where results are returned/sorted
by relevance of some sort?. At this point, the best I can think of,
would be to perform multiple queries
Hi all,
I've got a simple table with a lot of data in it:
CREATE TABLE customer_data (
cd_id int primary key
default(nextval('cd_seq')),
cd_cust_id int not null,
cd_variable textnot
Decibel! wrote:
On Aug 15, 2008, at 1:36 PM, Madison Kelly wrote:
The 'cust_id' references the customer that the given data belongs to.
The reason for this data bucket (does this structure have a proper
name?) is that the data I need to store on a give customer is quite
variable and outside
Hi all,
I've got a fairly small DB (~850MB when pg_dump'ed) running on PgSQL
v8.1.11 a CentOS 5.3 x86_64 Xen-based virtual machine. The filesystem is
ext3 on LVM with 32MB extents. It's about the only real resource-hungry
VM on the server.
It slows down over time and I can't seem to find a
Steve Crawford wrote:
Madison Kelly wrote:
Hi all,
I've got a fairly small DB...
It slows down over time and I can't seem to find a way to get the
performance to return without doing a dump and reload of the database...
Some questions:
Is autovacuum running? This is the most likely
Gary Doades wrote:
From your queries it definitely looks like its your stats that are the
problem. When the stats get well out of date the planner is choosing a
hash join because it thinks thousands of rows are involved where as only
a few are actually involved. Thats why, with better stats,
Kevin Grittner wrote:
Madison Kelly li...@alteeve.com wrote:
I've added CLUSTER - ANALYZE - VACUUM to my nightly
routine and dropped the VACUUM FULL call.
The CLUSTER is probably not going to make much difference once
you've eliminated bloat, unless your queries do a lot of searches
Brad Nicholson wrote:
I think you are going down the wrong route here - you should be looking
at preventative maintenance instead of fixing it after its broken.
Ensure that autovacuum is running for the database (assuming that you
are on a relatively modern version of PG), and possibly tune it
Steve Crawford wrote:
Madison Kelly wrote:
Steve Crawford wrote:
Madison Kelly wrote:
Hi all,
I've got a fairly small DB...
It slows down over time and I can't seem to find a way to get the
performance to return without doing a dump and reload of the
database...
Yup, I even tried
Rosser Schwarz wrote:
On Mon, Jan 4, 2010 at 2:57 PM, Madison Kelly li...@alteeve.com wrote:
As for autovacuum, I assumed (yes, I know) that all v8.x releases enabled it
by default. How would I confirm that it's running or not?
I believe it's not enabled by default in 8.1-land
Scott Marlowe wrote:
On Mon, Jan 4, 2010 at 3:13 PM, Greg Smith g...@2ndquadrant.com wrote:
Madison Kelly wrote:
I think for now, I will stick with 8.1, but I will certainly try out your
repo edit above on a test machine and see how that works out. I am always
reticent to change something
45 matches
Mail list logo
