I'm looking for advice from the I/O gurus who have been in the SSD game
for a while now.
I understand that the majority of consumer grade SSD drives lack the
required capacitor to complete a write on a sudden power loss. But,
what about pairing up with a hardware controller with BBU write
On 3/4/11 11:03 AM, Wayne Conrad wrote:
On 03/04/11 10:34, Glyn Astill wrote:
I'm wondering (and this may be a can of worms) what peoples opinions
are on these schedulers?
When testing our new DB box just last month, we saw a big improvement
in bonnie++ random I/O rates when using the noop
On 10/11/10 8:02 PM, Scott Carey wrote:
would give you a 1MB read-ahead. Also, consider XFS and its built-in
defragmentation. I have found that a longer lived postgres DB will get extreme
file fragmentation over time and sequential scans end up mostly random.
On-line file defrag helps
On 10/12/10 10:44 AM, Scott Carey wrote:
On Oct 12, 2010, at 8:39 AM, Dan Harris wrote:
On 10/11/10 8:02 PM, Scott Carey wrote:
would give you a 1MB read-ahead. Also, consider XFS and its built-in
defragmentation. I have found that a longer lived postgres DB will get extreme
file
On 10/12/10 4:33 PM, Neil Whelchel wrote:
On Tuesday 12 October 2010 08:39:19 Dan Harris wrote:
On 10/11/10 8:02 PM, Scott Carey wrote:
would give you a 1MB read-ahead. Also, consider XFS and its built-in
defragmentation. I have found that a longer lived postgres DB will get
extreme file
On 10/7/10 11:47 AM, Aaron Turner wrote:
snip
Basically, each connection is taking about 100MB resident. As we need
to increase the number of threads to be able to query all the devices
in the 5 minute window, we're running out of memory.
I think the first thing to do is look into using a
On 3/22/10 4:36 PM, Carlo Stonebanks wrote:
Here we go again!
Can anyone see any obvious faults?
Carlo
maintenance_work_mem = 256MB
I'm not sure how large your individual tables are, but you might want to
bump this value up to get faster vacuums.
max_fsm_relations = 1000
I think this will
My company finally has the means to install a new database server for
replication. I have Googled and found a lot of sparse information out
there regarding replication systems for PostgreSQL and a lot of it looks
very out-of-date. Can I please get some ideas from those of you that
are
Laszlo Nagy wrote:
Question 4. How to make the partitions? This is the hardest question.
Here is my plan:
- the OS resides on 2 disks, RAID 1
- the databases should go on 8 disks, RAID 0 + 1
Make sure you understand the difference between RAID 1+0 and RAID 0+1..
I suspect you'll end up
I've been fighting with the common workarounds for inadequate response
times on select count(*) and min(),max() on tables with tens of
millions of rows for quite a while now and understand the reasons for
the table scans.
I have applications that regularly poll a table ( ideally, the more
My PG server came to a screeching halt yesterday. Looking at top saw a very
large number of startup waiting tasks. A pg_dump was running and one of my
scripts had issued a CREATE DATABASE command. It looks like the CREATE DATABASE
was exclusive but was having to wait for the pg_dump to
Kari Lavikka wrote:
Hello!
Some background info.. We have a blog table that contains about eight
million blog entries. Average length of an entry is 1200 letters.
Because each 8k page can accommodate only a few entries, every query
that involves several entries causes several random seeks to
Today, I looked at 'top' on my PG server and saw a pid that reported 270 hours
of CPU time. Considering this is a very simple query, I was surprised to say
the least. I was about to just kill the pid, but I figured I'd try and see
exactly what it was stuck doing for so long.
Here's the
Tom Lane wrote:
Dan Harris [EMAIL PROTECTED] writes:
Here's the strace summary as run for a few second sample:
% time seconds usecs/call callserrors syscall
-- --- --- - -
97.250.671629 92 7272
Thomas Finneid wrote:
Hi
During the somes I did I noticed that it does not necessarily seem to be
true that one needs the fastest disks to have a pg system that is fast.
It seems to me that its more important to:
- choose the correct methods to use for the operation
- tune the pg memory
Andrew Sullivan wrote:
On Thu, Jun 07, 2007 at 03:26:56PM -0600, Dan Harris wrote:
They don't always have to be in a single transaction, that's a good idea to
break it up and vacuum in between, I'll consider that. Thanks
If you can do it this way, it helps _a lot_. I've had to do this
sort
Tyler Durden wrote:
Hi,
I'm having some problems in performance in a simple select count(id)
from I have 700 000 records in one table, and when I do:
# explain select (id) from table_name;
-[ RECORD 1
]
QUERY PLAN | Seq Scan
Daniel Haensse wrote:
Dear list,
I'm running postgres on a tomcat server. The vacuum is run every hour
(cronjob) which leads to a performance drop of the tomcat applications.
I played around with renice command and I think it is possible to reduce
this effect which a renice. The problem is how
Orhan Aglagul wrote:
Hi Everybody,
I was trying to see how many inserts per seconds my application could
handle on various machines.
I read that postgres does have issues with MP Xeon (costly context
switching). But I still think that with fsync=on 65 seconds is ridiculous.
CPU is
Michael Stone wrote:
On Fri, Apr 27, 2007 at 09:27:49AM -0400, Carlos Moreno wrote:
Notice that the second part of my suggestion covers this --- have
additional
switches to initdb
snip
If the person knows all that, why wouldn't they know to just change the
config parameters?
Exactly..
Bill Moran wrote:
In response to Dan Harris [EMAIL PROTECTED]:
snip
Why does the user need to manually track max_fsm_pages and max_fsm_relations? I
bet there are many users who have never taken the time to understand what this
means and wondering why performance still stinks after vacuuming
Is there a pg_stat_* table or the like that will show how bloated an index is?
I am trying to squeeze some disk space and want to track down where the worst
offenders are before performing a global REINDEX on all tables, as the database
is rougly 400GB on disk and this takes a very long time to
8.0.3 - Linux 2.6.18..
Freshly vacuumed and analyzed
This database has been humming along fine for a while now, but I've got one of
those sticky queries that is taking
much too long to finish.
After some digging, I've found that the planner is choosing to apply a
necessary seq scan to the
I've found that it would be helpful to be able to tell how busy my
dedicated PG server is ( Linux 2.6 kernel, v8.0.3 currently ) before
pounding it with some OLAP-type queries. Specifically, I have a
multi-threaded client program that needs to run several thousand
sequential queries. I broke
Dan Harris wrote:
I've found that it would be helpful to be able to tell how busy my
dedicated PG server is ( Linux 2.6 kernel, v8.0.3 currently ) before
pounding it with some OLAP-type queries.
..snip
Thank you all for your great ideas! I'm going to try the perl function
as that seems
I have a new task of automating the export of a very complex Crystal
Report. One thing I have learned in the last 36 hours is that the
export process to PDF is really, really, slooww..
Anyway, that is none of your concern. But, I am thinking that I can
somehow utilize some of PG's strengths
Thank you all for your ideas. I appreciate the quick response.
-Dan
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Arjen van der Meijden wrote:
But be aware that there can be substantial and unexpected differences on
this relatively new platform due to simply changing the OS, like we saw
when going from linux 2.6.15 to 2.6.18, as you can see here:
http://tweakers.net/reviews/657/2
Having upgraded to
[EMAIL PROTECTED] wrote:
both of the two database are live but use for two different web app.
my company don't want to spend more to buy a new server, so then I think of
to implement both under the same server and one instance..
Just as an anecdote, I am running 30 databases on a single
My database is used primarily in an OLAP-type environment. Sometimes my
users get a little carried away and find some way to slip past the
sanity filters in the applications and end up bogging down the server
with queries that run for hours and hours. And, of course, what users
tend to do is
Tom Lane wrote
You should be using SIGINT, not SIGTERM.
regards, tom lane
Thank you very much for clarifying this point! It works :)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Tom Lane wrote:
SNIP
So it's estimating 5775 cost units per probe into eventactivity, which
is pretty high --- it must think that a lot of rows will be retrieved by
the index (way more than the 20 or so it thinks will get past the filter
condition).
What does the pg_stats entry for
Tom Lane wrote:
That's very strange --- the estimated cost of the seqscan is high enough
that the planner should have chosen a nestloop with inner indexscan on
the big table. I'm not sure about the join-order point, but the hash
plan for the first join seems wrong in any case.
Um, you do have
I have a table with 1 live row that I found has 115000 dead rows in it (
from a testing run ). I'm trying to VACUUM FULL the table and it has
run for over 18 hours without completion. Considering the hardware on
this box and the fact that performance seems reasonable in all other
aspects,
Markus Bertheau wrote:
Have you tried using a GIST index on lat long? These things are
meant for two-dimensional data, whereas btree doesn't handle
two-dimensional data that well. How many rows satisfy either of the
long / lat condition?
According to the analyze, less than 500 rows
Dan Harris wrote:
Markus Bertheau wrote:
Have you tried using a GIST index on lat long? These things are
meant for two-dimensional data, whereas btree doesn't handle
two-dimensional data that well. How many rows satisfy either of the
long / lat condition?
According to the analyze, less
Merlin Moncure wrote:
As others will probably mention, effective queries on lot/long which
is a spatial problem will require r-tree or gist. I don't have a lot
of experience with exotic indexes but this may be the way to go.
One easy optimization to consider making is to make an index on
explain analyze
select distinct eventmain.incidentid, eventmain.entrydate,
eventgeo.long, eventgeo.lat, eventgeo.geox, eventgeo.geoy
from eventmain, eventgeo
where
eventmain.incidentid = eventgeo.incidentid and
( long -104.998027962962 and long -104.985957781349 ) and
( lat
On Oct 3, 2005, at 5:02 AM, Steinar H. Gunderson wrote:
I thought this might be interesting, not the least due to the
extremely low
price ($150 + the price of regular DIMMs):
This has been posted before, and the main reason nobody got very
excited is that:
a) it only uses the PCI
On Oct 3, 2005, at 5:02 AM, Steinar H. Gunderson wrote:
I thought this might be interesting, not the least due to the
extremely low
price ($150 + the price of regular DIMMs):
Replying before my other post came through.. It looks like their
benchmarks are markedly improved since the
On Sep 28, 2005, at 8:32 AM, Arnau wrote:
Hi all,
I have been googling a bit searching info about a way to
monitor postgresql (CPU Memory, num processes, ... )
You didn't mention your platform, but I have an xterm open pretty
much continuously for my DB server that runs plain old
Do you have any sources for that information? I am running dual
SmartArray 6402's in my DL585 and haven't noticed anything poor about
their performance.
On Sep 1, 2005, at 2:24 PM, Luke Lonergan wrote:
Are you using the built-in HP SmartArray RAID/SCSI controllers? If
so, that
could be
Thanks for all the great ideas. I have more options to evaluate now.
-Dan
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
I have a web page for my customers that shows them count of records
and some min/max date ranges in each table of a database, as this is
how we bill them for service. They can log in and check the counts
at any time. I'd like for the counts to be as fresh as possible by
keeping this
On Aug 9, 2005, at 1:08 PM, Tom Lane wrote:
Joshua D. Drake [EMAIL PROTECTED] writes:
My experience is that when this type of thing happens it is typically
specific queries that cause the problem. If you turn on statement
logging you can get the exact queries and debug from there.
Here
On Aug 10, 2005, at 12:49 AM, Steve Poe wrote:
Dan,
Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a
clarification, since RAID 0 is still a single-point of failure even if
RAID1 is on top of RAID0.
Well, you tell me if I stated incorrectly. There are two raid
enclosures
On Aug 9, 2005, at 3:51 PM, John A Meinel wrote:
Dan Harris wrote:
On Aug 10, 2005, at 12:49 AM, Steve Poe wrote:
Dan,
Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a
clarification, since RAID 0 is still a single-point of failure
even if
RAID1 is on top of RAID0.
Well
On Jul 28, 2005, at 8:38 AM, Chris Hoover wrote:
I did some more testing, and ran the explain analyze on the problem.
In my session I did a set enable_hashjoin = false and then ran the
analyze. This caused it to use the indexes as I have been expecting
it to do.
Now, how can I get it to use
I am working on a process that will be inserting tens of million rows
and need this to be as quick as possible.
The catch is that for each row I could potentially insert, I need to
look and see if the relationship is already there to prevent
multiple entries. Currently I am doing a
Lately, I've been reading a lot about these new Coraid AoE RAID
devices ( http://www.coraid.com ). They tout it as being fast and
cheap and better than iSCSI due to the lack of TCP/IP over the wire.
Is it likely that a 15-drive RAID 10 Linux software RAID would
outperform a 4-drive 10k
On Jul 14, 2005, at 10:12 PM, John A Meinel wrote:
My biggest question is why the planner things the Nested Loop would be
so expensive.
Have you tuned any of the parameters? It seems like something is
out of
whack. (cpu_tuple_cost, random_page_cost, etc...)
here's some of my
On Jul 15, 2005, at 9:09 AM, Dan Harris wrote:
On Jul 14, 2005, at 10:12 PM, John A Meinel wrote:
My biggest question is why the planner things the Nested Loop
would be
so expensive.
Have you tuned any of the parameters? It seems like something is
out of
whack. (cpu_tuple_cost
On Jul 15, 2005, at 2:39 PM, Ron Wills wrote:
Hello all
I'm running a postgres 7.4.5, on a dual 2.4Ghz Athlon, 1Gig RAM and
an 3Ware SATA raid.
Operating System? Which file system are you using? I was having a
similar problem just a few days ago and learned that ext3 was the
I'm trying to improve the speed of this query:
explain select recordtext from eventactivity inner join ( select
incidentid from k_r where id = 94 ) a using ( incidentid ) inner join
( select incidentid from k_b where id = 107 ) b using ( incidentid );
On Jul 14, 2005, at 9:47 AM, Alvaro Herrera wrote:
On Thu, Jul 14, 2005 at 12:28:05AM -0600, Dan Harris wrote:
. Ext3 must really be crappy
for postgres, or at least is on this box.
Were you using the default journal settings for ext3?
Yes, I was. Next time I get a chance to reboot
On Jul 14, 2005, at 5:12 PM, John A Meinel wrote:
Dan Harris wrote:
Well, postgres is estimating around 500 rows each, is that way
off? Try
just doing:
EXPLAIN ANALYZE SELECT incidentid FROM k_b WHERE id = 107;
EXPLAIN ANALYZE SELECT incidentid FROM k_r WHERE id = 94;
Once again, do
On Jul 14, 2005, at 7:15 PM, John A Meinel wrote:
Is the distribution of your rows uneven? Meaning do you have more rows
with a later id than an earlier one?
There are definitely some id's that will have many times more than
the others. If I group and count them, the top 10 are fairly
Gurus,
A table in one of my databases has just crossed the 30 million row
mark and has begun to feel very sluggish for just about anything I do
with it. I keep the entire database vacuumed regularly. And, as
long as I'm not doing a sequential scan, things seem reasonably quick
most of
On Jul 13, 2005, at 1:11 PM, John A Meinel wrote:
I might be wrong, but there may be something much more substantially
wrong than slow i/o.
John
Yes, I'm afraid of that too. I just don't know what tools I should
use to figure that out. I have some 20 other databases on this
system,
On Jul 13, 2005, at 2:17 PM, Stephen Frost wrote:
Could you come up w/ a test case that others could reproduce where
explain isn't returning?
This was simply due to my n00bness :) I had always been doing
explain analyze, instead of just explain. Next time one of these
queries comes up,
that EVERY query is logged is not what I'm after
for this project. The infinite-running queries are unpredictable
and may only happen once a week. Logging 24/7 in anticipation of one
of these occurrences is not something I'd like to do.
Thanks,
Dan Harris
---(end
Sorry to bother everyone with yet another my query isn't using an
index problem but I am over my head on this one.. I am open to ways
of restructuring this query to perform better.
I have a table, 'ea', with 22 million rows in it. VACUUM ANALYZE has
been just run on the table.
This is the
On May 8, 2005, at 6:51 PM, Russell Smith wrote:
On Mon, 9 May 2005 09:20 am, Dan Harris wrote:
You cannot use an index for %CORVETTE%, or %RED%. There is no way
for the index to know if a row had that in the middle without scanning
the whole
index. So it's much cheaper to do a sequence scan
On May 8, 2005, at 8:06 PM, Josh Berkus wrote:
If I were to use tsearch2 for full-text indexing, would I need to
create another table that merges all of my recordtext rows into a
single 'text' field type?
No. Read the OpenFTS docs, they are fairly clear on how to set up a
simple
FTS index.
On May 8, 2005, at 8:32 PM, Russell Smith wrote:
I have run this, and while it is very fast, I'm concerned it's not
doing what I need.
How fast is very fast?
It took 35 seconds to complete versus ~450 my old way.
select distinct em.incidentid, ea.recordtext as retdata, eg.long,
eg.lat
FROM em
Greetings,
I have been beating myself up today trying to optimize indices for a
query that uses LIKE. In my research I have read that the locale
setting may affect PostgreSQL's choice of seq scan vs index scan. I am
running Fedora Core 2 and it appears when I run locale that it is set
to
of these things,
they better be some good marks!
Thanks again
-Dan Harris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
. I've had to tune the shmmax on linux machines before but
I'm new to AIX and not sure if this is even required on that platform?
Google has not been much help for specifics here.
Hoping someone else here has a similar platform and can offer some advice..
Thanks!
-Dan Harris
68 matches
Mail list logo
