-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> I'm pretty certain that there are no TPC-certified test results for
> Postgres, because to date no organization has cared to spend the money
> needed to perform a certifiable test.
Anyone have a rough idea of the costs involve
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> Where can I find a plan-readinf tutorial?
This covers explain plans in depth:
http://www.gtsm.com/oscon2003/explain.html
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200309291123
-BEGIN PGP SIGNATURE-
Comment: h
stributed, but it sure beats storing "uptime" over
and over again. :)
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200310101243
-BEGIN PGP SIGNATURE-
Comment: http://www.turnstep.com/pgp.html
iD8DBQE/huHxvJuQZxSWSsgRAiMNAKD4kQCwdv3fXyEFUu64mymtf567dwCcCKd5
ZzJaV7w
ching writes and "lying" about the write completing like IDE
drives often do by default?
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
ves themselves may
be doing some caching on their own. Has anyone verified that the controllers
are disabling the drive cache or issuing flushes or doing something else to be
sure to block the drives from caching writes?
--
greg
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
in cache is one thing. But what it needs to do is not
confirm the write to the host OS. Unless they want to sell their battery
backed unit which is an expensive add-on...
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists at o
moving the journal to a dedicated drive as well. (or if they're
scsi drives or you're sure the raid controller is safe from write caching then
just switch file systems to something that doesn't journal data.)
--
greg
---(end of broadcast)---
might be right. In which case you
might want to look into tools to tune your kernel vm system.
--
greg
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
nal drive that
additional drive may improve your array speed enough to overcome that
inefficiency.
--
greg
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
at you're triggering some bug, either in
the TCP stack or Postgres that is causing some connection attempts to not be
handled properly.
I'm skeptical that there's a bug in Postgres since lots of people do in fact
run web servers configured to open a new connection for every page.
n that having your WAL share a spindle with other parts of
the OS would have a bigger penalty on IDE drives than on SCSI drives though?
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I'm also wondering about whether I'm better off with one of these SATA raid
controllers or just going with SCSI drives.
--
greg
---(end of broadcast)---
TIP 8: explain analyze is your friend
form identically to the C capacity drive. If it has denser
platters that might improve performance slightly.
--
greg
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Kevin Brown <[EMAIL PROTECTED]> writes:
> 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
t old data from the redo logs back
to the table.
Engineering is all about tradeoffs.
--
greg
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
firmware can really
schedule things perfectly either.
I think most of the difference is that the drive manufacturers just don't
package their high end drives with ATA interfaces. So there are no 10k RPM ATA
drives and no 15k RPM ATA drives. I think WD is making fast SATA dr
be able to maintain their
mediocre latency much better under load than the SCSI array with fewer drives
would maintain its low latency response time despite its drives' lower average
seek time.
--
greg
---(end of broadcast)---
TIP 9: the planner wi
ise-class" hardware anyways.
* (Actually even that's possible: you could limit yourself to a portion of the
drive surface to reduce seek time)
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
d the block you're replacing. It just xors the old block, the new
block, and the existing parity block to generate the new parity block.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
FT SINGLE JOIN mytab ...
Where "LEFT SINGLE JOIN" is an imaginary syntax that doesn't actually have to
exist in the parser, but exists in the planner/executor and behaves
differently in the case of duplicate matches.
Actually I could see such a syntax being useful directly too.
-
ow i/o
bandwidth usage because most of the time is being spent seeking and waiting
for rotational latency.
--
greg
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
ne.
Incidentally, Sun sells a bunch of v20z and v40z machines on Ebay as some kind
of marketing strategy. You can pick one up for only a slightly absurd price if
you're happy with the configurations listed there. (And if you're in the US).
--
greg
-
consistently get good plans for any queries that depend on good
estimates for n_distinct.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
e entire table your estimates for n_distinct will be extremely
unreliable.
--
greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
ou with 10G of filesystem
cache. Which might in turn decrease the percentage of time waiting on i/o,
which would decrease the number of processes you need even further...
--
greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
n requests in the same web server.
However, your application sounds like it's more involved than a typical web
server. If it's handling many slow resources, such as connections to multiple
databases, SOAP services, mail, or other network services then you may well
n
hed by the decrease in i/o
needed to use the index.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
.
It would be pretty useful. In fact if it isn't how hash indexes are
implemented then it might be useful to provide a user visible hash(ROW)
function that allows creating such indexes as functional indexes. Though
hiding it would make the SQL simpler.
--
greg
--
Do you have INSERT/UPDATE/DELETE transactions happening
concurrently with this select scan? If so you should strongly look into
separating the transaction log from the data files.
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extens
dexing a
hash function.
--
greg
---(end of broadcast)---
TIP 8: explain analyze is your friend
ering.
Consider for example a query involving two or more hash indexes and the new
bitmap indexscan plan. You don't want to fetch the tuples if you can eliminate
them using one of the other indexes.
--
greg
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
27;re being curt all around, this one's not acceptable on the basis
that it's not grammatical.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
aptastic mangling
of plans in people's mails? I was assuming it was some new idea of how to mess
up people's mail coming out of Exchange or Lotus or some other such "corporate
messaging" software that only handled SMTP mail as an afterthought. This is,
uh, disappointing.
--
greg
;re curious about which Postgres prefers you'll have to
test with Postgres.
I'm not sure whether it will change the conclusion but I expect Postgres will
like bandwidth better than random benchmarks do.
--
greg
---(end of broadcast)---
TIP 2:
dered in picking which indexes to consider.
It *is* considered in evaluating which index is the best one to use and
whether it's better than a sequential scan. Just not in the initial choice of
which indexes to look at at all.
--
greg
---(end of broadcast)--
presentative of your system's performance then much of your
database is in cache and the effective value will be closer to 1. Try 2 or
even 1.5 or 1.2.
But like I said, test other queries and test under more representative
conditions other than repeating a single query over an
rrors are quite common for backup purposes. To take a
backup you break the mirror by taking one of the three copies out. Back it up
at your leisure, then just resync it in time for the next backup.
--
greg
---(end of broadcast)---
TIP 2: you can get o
Hi:
I'm beginning the push at our company to look at running
postgreSQL in production here. We have a dual CPU 2.8 GHZ Xeon
Box running oracle. Typical CPU load runs between 20% and 90%.
Raw DB size is about 200GB. We hit the disk at roughly 15MB/s
read volume and 3MB/s write.
records that
you're updating? In which case every record being updated might be causing a
full table scan on another table (or multiple other tables). If those tables
are entirely in cache then it could cause these high cpu low i/o symptoms.
Or are there any triggers on this table?
--
greg
er being flushed then you'll see much better
performance but run the risk of data loss in a power failure or hardware
failure. (But not in the case of an OS crash, or at least no more than
otherwise.)
You could also try using the "-O journal_dev" option to put the ext3 jour
ssion of adding functionality like this but afaik no
version of Postgres actually does this yet.
Adding the index may still help though.
--
greg
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
y to tell if the data is cached is having a "vmstat 1" running
in another window. Start the query and look at the bi/bo columns. If you see
bi spike upwards then it's reading from disk.
--
greg
---(end of broadcast)---
TIP 6: explain analyze is your friend
t, which pretty much defeats the point for a fast-start plan).
> I have some ideas about fixing this but it won't happen before 8.2.
Of course in this case assuming "id" is an integer column you can just sort by
-id instead.
--
greg
---(end of broadca
hared buffers
should never be more than about 10k no matter how much memory you have. Let
the kernel do the bulk of the buffering.
That said it's even more mysterious in your situation. Why would a large
shared buffers help insert-only performance much at all? My guess is that it&#x
he other hand if your database is primarily read-only then Raid 5 may not
be a problem and may be faster than raid 1+0.
--
greg
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ind though. The broader the multiway merges the more sort areas
would be used which would increase the likelihood of another sort using the
same sort area and hurting i/o performance.
--
greg
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
s this problem, you frequently have to add WHERE
col IS NOT NULL" in order to let it use an index.)
--
greg
---(end of broadcast)---
TIP 6: explain analyze is your friend
build? Or is it a BSD
ports or Gentoo build with unusual options?
Perhaps posting actual vmstat and iostat output might help if someone catches
something you didn't see?
--
greg
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Joshua Marsh <[EMAIL PROTECTED]> writes:
> We all want our systems to be CPU bound, but it's not always possible.
Sure it is, let me introduce you to my router, a 486DX100...
Ok, I guess that wasn't very helpful, I admit.
--
greg
-
total. It's
harder to scale up I/O subsystems than CPUs, instead of just replacing a CPU
it tends to mean replacing the whole system to get a better motherboard with a
faster, better bus, as well as adding more controllers and more disks.
--
greg
---(end of broadcast)--
nty of more mundane ways that Postgres is
responsible for how efficiently or not the kernel is used. Just using fewer
syscalls to do the same amount of reading would reduce cpu consumption.
> One IMHO obvious improvement would be to have vacuum and analyze only do
> direct
> IO. Now they
r two invocations of that on your system
because of the extra processor). I bet you'll see about half the percentage of
iowait because postres will get half as much opportunity to schedule i/o. If
what you are saying were true then you should get 0% iowait.
--
greg
---(end of broadcast)---
TIP 6: explain analyze is your friend
of opportunity for other contention that could
easily eat all of the gains.
I also fear that heading in that direction could push Postgres even further
from the niche of software that works fine even on low end hardware into the
realm of software that only works on high end hardware. It's alre
> (usually done by padding the buffer sizes by an extra 32 bytes or L1 line
> size). Whatever you do, all the usual high performance computing tricks
> should be used prior to considering any rewriting of major code sections.
So your philosophy is to worry about microoptimizations before worry
(I guess AMD64, Sparc64, Alpha, Itanic).
Afaik none of these items have happened but I don't know for sure.
--
greg
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail comma
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Last I heard the reason count(*) was so expensive was because its state
> > variable was a bigint. That means it doesn't fit in a Datum and has to be
> > alloced and stored as a
ould also need to be pretty
> uselessly indexed separately)?
You could create 32 partial indexes on some other column which wouldn't really
take much more space than a single index on that other column. But that won't
let you combine them effectively as a gist index would.
--
greg
-
uot;star join". What the user really
wants is a single table joined to a bunch of small tables. There's no way to
write that in SQL due to the limitations of the language but a bunch of
subqueries expresses precisely the same concept (albeit with another set of
language limitations which luckily don't impact this particular application).
--
greg
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
swapping
at which point it would be counter productive.
If increasing work_mem doesn't allow a hash aggregate or at least an in-memory
sort to handle it then putting the pgsql_tmp directory on a separate spindle
might help if you have any available.
--
greg
---(e
vely are getting
something closer to 1. Try 2 or 1.5 or so.
Note that the sequential scan has to scan the entire table. The index scan has
to scan the entire table *and* the entire index, and in a pretty random order.
If the table didn't fit entirely in RAM it would end up reading the
ts are taken; your report suggests you were
> spending 30% of the time in GetSnapshotData, which is a lot higher than
> I've ever seen in a profile.
Perhaps it reduced the amount of i/o concurrent vacuums were doing?
--
greg
---(end of broadcast)-
ously like a DNS problem.
--
greg
---(end of broadcast)---
TIP 6: explain analyze is your friend
equently in an OLTP setting it pegs the
cpu at 100%.
--
greg
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
es), which
means the drive is being cooked and will likely wear out quickly. But
that won't slow it down, and you'd get much scarier messages out of smartd
if the drives had a real problem. You should improve cooling in this case
if you want to drives to have a healthy li
er, haven't needed or wanted to reboot since then:
megaraid cmm: 2.20.2.6 (Release Date: Mon Mar 7 00:01:03 EST 2005)
megaraid: 2.20.4.6-rh2 (Release Date: Wed Jun 28 12:27:22 EST 2006)
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
even worse
under SW RAID than what you get from a single disk, because you may have
to wait for multiple discs to spin to the correct position and write data
out before you can consider the transaction complete.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
nes
like bonnie output (there are certainly two sides with valid points in
that debate), to make them more compatible with flow-impaired clients, you
can't expect that mail composition software is sophisticated enough to
allow doing that for one section while still wrapping the rest of the te
ts
you're seeing, expecially when combined with a 20% greater raw CPU clock.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.post
subsystems will
shift which optimizations are useful and which have minimal impact even if
the processor is basically the same.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 6: explain
rovement) for the pgbench 1.45 that comes with
current Postgres 8.1 versions.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I/O as the main driver of performance.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 6: explain analyze is your friend
fferent as soon as the number of
transactions increases. With little or no actual disk writes, you should
expect results to be ranked by CPU speed.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
eful on current gen multi-processor/core systems.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
#x27;ve noticed the same thing and have been meaning to figure out what the
cause is. It's just doing a select in there; it's not even in a begin/end
block.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)
into the mix people test. That one may
stack usefully with -O2, but probably not with -O3 (3 includes
optimizations that increase code size).
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 5:
y is.
Trying to do "INSERT INTO Messages(path, msgid) SELECT (path, msgid)
FROM tmpMessages" took a really long time before psql died with an
out-of-memory error.
Do you have the exact text of the error? I suspect you're falling victim
to the default parameters being far too l
imeout.it_value.tv_usec = PGSTAT_STAT_INTERVAL % 1000;
Change it to match the current line in the CVS tree for 8.3:
write_timeout.it_value.tv_usec = (PGSTAT_STAT_INTERVAL % 1000) * 1000;
That's all it took to resolve things for me.
--
* Greg Smith [EMAIL PROTECTED] http://www.
not sure how that factor may have
skewed this particular bit of data.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
elps ferret out when this happens.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PRO
at fact that today's
consumer processors produce massively more heat than those of even a few
years ago has contributed to drive manufacturers moving their specs
upwards as well.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
-
ess" in the long run as the documentation points
out but for the short term and within a transaction it seems to work
well.
Thoughts?
Greg
--
Greg Spiegelberg
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
614.318.4314, office
614.431.8388, fax
ISOdx Product Development Manager
Cranel, Inc.
CTID
implementation.
I would welcome any feedback.
TIA,
Greg
cranel=# \d sid2.data_id_table
Table "sid2.data_id_table"
Column| Type | Modifiers
-+-+---
point_id| bigint |
dtype_id| bigint |
segment_id | bigint
Craig,
I'm not using a TEMP TABLE in this DELETE however I have tried an
ANALYZE prior to the DELETE but it hardly makes a dent in the time.
Please look at the other follow-up email I just sent for full details.
Greg
-Original Message-
From: Craig A. James [mailto:[EMAIL PROT
h, but I doubt
that's a problem for you if you're so brazen as to turn off fsync.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 6: explain analyze is your friend
ow
magically solve these problems.
If the key is a integer, it's always possible to figure out a trivial map
that renumbers the entire database programmatically in order to merge two
sets of data.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
given all
available information at the time. The funny thing about unexpected
changes to a business model is that you never expect them.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 9: In ver
abase. Try increasing that to 30, restart the server, and
rebuild the index to see how much the 1GB case speeds up. If it's
significantly faster (it should be), try the 5GB one again.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
-
On Mon, 23 Apr 2007, Scott Marlowe wrote:
I honestly kinda wondered if the original post came out of a time warp,
like some mail relay somewhere held onto it for 4 years or something.
That wouldn't be out of the question if this system is also his mail
server.
--
* Greg Smith [
iently before you get to the point where the database I/O is being
measured usefully at all via pgbench.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner w
nce isn't as big as it used to be.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
ot data in it, and preferably after it's been running under load for a
while, and make your recommendations based on all that information.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 7: Y
esult
3) Unfair comparison of PostgreSQL with robust WAL vs. MySQL+MyISAM on
write-heavy worksloads
These are real issues, which of course stack on top of things like
outdated opinions from older PG releases with performance issues resolved
in the last few years.
--
* Greg Smith [EMAIL
ce it would remove that as something
separate that needed to be built.
To argue against myself for a second, it may very well be the case that
writing the simpler tool is the only way to get a useful prototype for
building the more complicated one; very easy to get bogged down in feature
creep
sting data. You would thing there would be an organized project
addressing this need around to keep everyone from reinventing that wheel,
but I'm not aware of one.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of
expect it to grow to?", now that's something people can work with.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
ely inappropriate for any environment I work in, because
there really is no thought of security whatsoever in the whole thing.
What I'm still thinking about is whether it's possible to fix that issue
while still keeping the essential simplicity that makes Munin so friendly.
--
that
simple can give dramatically less useful results for predicting PostgreSQL
performance than what you can find out running a real query.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 3:
, but I did miss the gigantic and easy to
install Debian software repository.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
ll/upgrade just by playing with the packages that are
different between the two. So someone who installs CentOS now could swap
to RHEL very quickly in a pinch if they have enough cojones to do the
required package substitutions.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltim
isk, there is no way to break the RPM barrier
without hardware support. The fact that he misunderstands such a
fundamental point makes me wonder what other gigantic mistakes might be
buried in his analysis.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
1 - 100 of 1492 matches
Mail list logo
