Hi folks
I had a couple of semi-newbie questions about this, which I couldn't find
obvious answers to in the archives ... we are using Postgres 8.3, and the
behaviour is the same across Windows and Linux.
I am working with an app which, among other things stores XML files (average
about 50KB in
The autovac may have done most of the work before you killed it ...
I'm new to Postgres, but from limited subjective experience, it seems
it's a lot faster to vaccum ranges of blocks that are were recently
vacuumed (at minimum, a good chunk of table will have been brought
into buffer cache by both
Itching to jump in here :-)
There are a lot of things to trade off when choosing storage for a
database: performance for different parts of the workload,
reliability, performance in degraded mode (when a disk dies), backup
methodologies, etc. ... the mistake many people make is to overlook
the
Hi Eddy
Perhaps a slightly naive suggestion have you considered
converting the query to a small stored procedure ('function' in
Postgres speak)? You can pull the location values, and then iterate
over a query like this:
select userid from users where location=:x
which is more-or-less
My reply about server failure was shwoing what could go wrong at the server
level assuming a first-class, properly run data center, with fully redundant
power, including a server with dual power supplies on separate cords fed by
separate UPS'es etc.
Unfortunately, *correctly* configured A/B
multiple round trips.
Doing it as a stored procedure would be nicer but I'd have to think a little
more about how to refactor the java code around the query to make this
happen. Thanks for the suggestion.
Eddy
On Mon, Nov 16, 2009 at 9:44 AM, Dave Crooke dcro...@gmail.com wrote:
Hi Eddy
The problem with RAID-5 or RAID-6 is not the normal speed operation, it's
the degraded performance when there is a drive failure. This includes
read-only scenarios. A DB server getting any kind of real use will
effectively appear to be down to client apps if it loses a drive from that
RAID set.
The fact that the delays are clustered at (3 + 0.2 n) seconds, rather than a
distributed range, strongly indicates a timeout and not (directly) a
resource issue.
3 seconds is too fast for a timeout on almost any DNS operation, unless it
has been modified, so I'd suspect it's the TCP layer, e.g.
Oops, I meant to mention this too virtually all GigE and/or server
class NICs do TCP checksum offload.
Dimitri - it's unlikely that you have a hardware issue on the NIC, it's more
likely to be a cable problem or network congestion. What you want to look
for in the tcpdump capture is things
I'll bite
1. In general, RAID-10 is the only suitable RAID configuration for a
database. The decision making comes in how many drives, and splitting stuff
up into LUNs (like putting pg_xlog on its own LUN).
2. None of the above - you're asking the wrong question really. PostgreSQL
is open
I'd second this a database is doing all kinds of clever things to
ensure ACID consistency on every byte that gets written to it.
If you don't need that level of consistency for your 8MB blobs, write them
to plain files named with some kind of id, and put the id in the database
instead of the
This is the second time I've heard that PG shared buffer on Windows doesn't
matter ... I'd like to understand the reasoning behind that claim, and why
it differs from other DB servers.
though that's much less important for Pg than for most other things, as
Pg uses a
Hi Rama
I'm actually looking at going in the other direction
I have an app using PG where we have a single table where we just added a
lot of data, and I'm ending up with many millions of rows, and I'm finding
that the single table schema simply doesn't scale.
In PG, the table partitioning
On Wed, Feb 10, 2010 at 5:30 PM, Jon Lewison jlewis...@gmail.com wrote:
Just a nit, but Oracle implements MVCC. 90% of the databases out there do.
Sorry, I spoke imprecisely. What I meant was the difference in how the rows
are stored internally in Oracle, the main tablespace contains
Actually, in a way it does No space left on device or similar ;-)
Cheers
Dave
P.S. for those not familiar with Oracle, ORA-01555 translates to your query
/ transaction is kinda old and I've forgotten the data, so I'm just going to
throw an error at you now. If you're reading, your SELECT
I do think it's valid to prevent idiot customers from installing drives that
use too much power or run too hot, or desktop drives that don't support
fast-fail reads, thus driving up Dell's support load, but it sounds like
this is more of a lock-in attempt.
This is kind of a dumb move on their
Hi folks
I have an application which collects performance stats at time intervals, to
which I am retro-fitting a table partitioning scheme in order to improve
scalability.
The original data is keyed by a 3-ary tuple of strings to keep the row
size down, in the new data model I'm actually
Thanks Joe.
1. In my case, I'm erring on the side of not using the limited partitioning
support in PG 8.3, which we're using because I'm generating new tables
all the time, I need to dynamically generate the DML anyway, and it's
actually less code to just do my own calculation on the
It's always possible to rebuild into a consistent configuration by assigning
a precedence order; for parity RAID, the data drives take precedence over
parity drives, and for RAID-1 sets it assigns an arbitrary master.
You *should* never lose a whole stripe ... for example, RAID-5 updates do
read
This is a generic SQL issue and not PG specific, but I'd like to get
an opinion from this list.
Consider the following data:
# \d bar
Table public.bar
Column | Type | Modifiers
+-+---
city | character
BY city, temp desc
Or am I misunderstanding the issue?
Garrett Murphy
-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Dave Crooke
Sent: Wednesday, February 24, 2010 2:31 PM
To: pgsql-performance
Subject
. The *ratio* in cost between these two plans increases
in proportion to log(n) of the table size ... at 5.5m rows its
livable, at 500m it's probably not :-!
Cheers
Dave
On Wed, Feb 24, 2010 at 5:12 PM, Richard Huxton d...@archonet.com wrote:
On 24/02/10 22:47, Dave Crooke wrote:
I'd imagine it would
Seconded these days even a single 5400rpm SATA drive can muster almost
100MB/sec on a sequential read.
The benefit of 15K rpm drives is seen when you have a lot of small, random
accesses from a working set that is too big to cache the extra
rotational speed translates to an average
.
Thanks everyone for the insights
Cheers
Dave
On Tue, Mar 9, 2010 at 6:46 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Wed, Feb 24, 2010 at 4:31 PM, Dave Crooke dcro...@gmail.com wrote:
This is a generic SQL issue and not PG specific, but I'd like to get
an opinion from this list
Hi there
This list is for performance tuning questions related to PostgreSQL ... your
question is a general SQL syntax issue. Also, it's not quite clear from your
message exactly what you are trying to do - it's better to post example
table schemas.
At a guess, I think you might want:
select 1,
Hi there
I'm not an expert on PG's toast system, but a couple of thoughts inline
below.
Cheers
Dave
On Sat, Mar 13, 2010 at 3:17 PM, fka...@googlemail.com
fka...@googlemail.com wrote:
Hi all,
my posting on 2010-01-14 about the performance when writing
bytea to disk caused a longer
As a fellow PG newbie, some thoughts / ideas
1. What is the prupose of the dump (backup, migration, ETL, etc.)? Why
plain? Unless you have a need to load this into a different brand of
database at short notice, I'd use native format.
2. If you goal is indeed to get the data into another DB,
There seems to be a wide range of opinion on this I am new to PG and
grew up on Oracle, where more SGA is always a good thing ... I know people
who run Oracle on 2TB Superdome's with titanic SGA sizes to keep the whole
DB in RAM. I'd be using a 40GB+ Oracle SGA on that box of yours.
A lot of
I agree with Tom, any reordering attempt is at best second guessing the
filesystem and underlying storage.
However, having the ability to control the extent size would be a worthwhile
improvement for systems that walk and chew gum (write to lots of tables)
concurrently.
I'm thinking of Oracle's
Greg - with Oracle, I always do fixed 2GB dbf's for poartability, and
preallocate the whole file in advance. However, the situation is a bit
different in that Oracle will put blocks from multiple tables and indexes in
a DBF if you don't tell it differently.
Tom - I'm not sure what Oracle does,
I've also observed the same behaviour on a very large table (200GB data,
170GB for 2 indexes)
I have a table which has 6 small columns, let's call them (a, b, c, d, e, f)
and about 1 billion rows. There is an index on (a, b, c, d) - not my idea,
Hibernate requires primary keys for every
If you are really so desparate to save a couple of GB that you are resorting
to -Z9 then I'd suggest using bzip2 instead.
bzip is designed for things like installer images where there will be
massive amounts of downloads, so it uses a ton of cpu during compression,
but usually less than -Z9 and
Note however that Oracle offeres full transactionality and does in place row
updates. There is more than one way to do it.
Cheers
Dave
On Mar 21, 2010 5:43 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Sat, Mar 20, 2010 at 11:47 PM, Andy Colson a...@squeakycode.net wrote:
Don't underestimate
wrote:
On Sun, Mar 21, 2010 at 9:14 PM, Dave Crooke dcro...@gmail.com wrote:
Note however that Oracle offeres full transactionality and does in place
row
updates. There is more than one way to do it.
There's no free lunch.
MVCC : VACUUM
Oracle : Rollback Segments
MyISAM
This is why pre-allocation is a good idea if you have the space
Tom, what about a really simple command in a forthcoming release of PG that
would just preformat a 1GB file at a time? This is what I've always done
scripted with Oracle (ALTER TABLESPACE foo ADD DATAFILE ) rather than
What about InnoDB?
On Tue, Mar 23, 2010 at 4:38 PM, Greg Smith g...@2ndquadrant.com wrote:
Tom Lane wrote:
So has anyone looked at porting MythTV to PG?
Periodically someone hacks together something that works, last big effort
I'm aware of was in 2006, and then it bit rots away. I'm
MyISAM is SQLLite with some threading ;-)
On Tue, Mar 23, 2010 at 6:30 PM, Scott Marlowe scott.marl...@gmail.comwrote:
On Tue, Mar 23, 2010 at 5:07 PM, Dave Crooke dcro...@gmail.com wrote:
What about InnoDB?
Depends on what parts of mysql they otherwise use. There are plenty
of features
What Scott said ... seconded, all of it.
I'm running one 500GB database on a 64-bit, 8GB VMware virtual machine, with
2 vcores, PG 8.3.9 with shared_buffers set to 2GB, and it works great.
However, it's a modest workload, most of the database is archival for data
mining, and the working set for
For a card level RAID controller, I am a big fan of the LSI , which is
available in a PCIe riser form factor for blade / 1U servers, and comes with
0.5GB of battery backed cache. Full Linux support including mainline kernel
drivers and command line config tools. Was using these with SAS
Hi foilks
I am using PG 8.3 from Java. I am considering a performance tweak which will
involve holding about 150 java.sql.PreparedStatment objects open against a
single PGSQL connection. Is this safe?
I know that MySQL does not support prepared statements *per se*, and so
their implementation of
Mine is a single record INSERT, so no issues with plans :-) Little Java ETL
job.
Is there any setting I'd need to tweak assuming I'm using 150-200 of these
at once?
Cheers
Dave
On Wed, Apr 14, 2010 at 6:10 PM, Craig Ringer
cr...@postnewspapers.com.auwrote:
On 15/04/10 04:49, Dave Crooke wrote
, Apr 15, 2010 at 2:42 PM, Dave Crooke dcro...@gmail.com wrote:
Hey folks
I am trying to do a full table scan on a large table from Java, using a
straightforward select * from foo. I've run into these problems:
1. By default, the PG JDBC driver attempts to suck the entire result set
into RAM
When a connection is used for both reading and writing, a commit() also
destroys any open cursors. Simple workaround - use two connections.
See full discussion on JDBC list.
Cheers
Dave
On Thu, Apr 15, 2010 at 3:01 PM, Dave Crooke dcro...@gmail.com wrote:
I have followed the instructions
, at 1:01 PM, Dave Crooke wrote:
On Thu, Apr 15, 2010 at 2:42 PM, Dave Crooke dcro...@gmail.com wrote:
Hey folks
I am trying to do a full table scan on a large table from Java, using a
straightforward select * from foo. I've run into these problems:
1. By default, the PG JDBC driver
, Apr 19, 2010 at 6:28 PM, Dave Crooke dcro...@gmail.com wrote:
Scott - I tried to post a SOLVED followup to the JDBC list but it was
rejected :-!
I now have the opposite problem of getting rid of the cursor :-)
ResultSet.close() does not work. I am trying to do a DROP TABLE from the
other
Hey folks
I am trying to do a full table scan on a large table from Java, using a
straightforward select * from foo. I've run into these problems:
1. By default, the PG JDBC driver attempts to suck the entire result set
into RAM, resulting in *java.lang.OutOfMemoryError* ... this is not cool, in
of the acme of great FOSS is to make it easy to use for
newbies and thus attract a larger user base, but that is just my $0.02
worth.
Cheers
Dave
On Tue, Apr 20, 2010 at 9:28 AM, Kevin Grittner kevin.gritt...@wicourts.gov
wrote:
Dave Crooke dcro...@gmail.com wrote:
I'd consider the fact
is in the minority, standards nothwithstanding, and I
feel badly about that, because PG rocks!
Cheers
Dave
On Tue, Apr 20, 2010 at 11:32 AM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Dave Crooke dcro...@gmail.com wrote:
AFAICT from the Java end, ResultSet.close() is supposed to be
final
I digest this down to this is the best that can be achieved on a connection
that's single threaded
I think the big difference with Oracle is this:
i. in Oracle, a SELECT does not have to be a transaction, in the sense that
PG's SELECT does ... but in Oracle, a SELECT can fail mid-stream if you
If, like me, you came from the Oracle world, you may be tempted to throw a
ton of RAM at this. Don't. PG does not like it.
On Fri, May 28, 2010 at 4:11 PM, Scott Marlowe scott.marl...@gmail.comwrote:
On Mon, May 24, 2010 at 12:25 PM, Merlin Moncure mmonc...@gmail.com
wrote:
*) shared_buffers
Never say never with computer geeks
http://www.youtube.com/watch?v=mJyAA0oPAwE
On Fri, Jun 11, 2010 at 7:44 AM, Kenneth Marshall k...@rice.edu wrote:
Hi Anj,
That is an indication that your system was less correctly
modeled with a random_page_cost=2 which means that the system
will
It's a standard (indeed, required) best practice of concurrent database
programming across any brand of database to ensure that multi-row
transactions always acquire the locks they use in a predictable order based
on row identities, e.g. for the classic banking debit-credit pair, doing
something
Tom
I always prefer to choose apps based on business needs, then the OS based on
the needs for the app.
Cynically, I often feel that the best answer to we have a policy that says
we're only allowed to use operating system x is to ignore the policy
the kind of people ignorant enough to be
Hi there
1. PG 8.1.9 is ancient ... you should upgrade.
2. The database gross size on disk is not affected by VACUUM ANALYZE ... all
this does is return space used by deleted row-versions to PG for re-use. The
only way to reduce it and thus return disk space to the OS is to do a VACUUM
FULL, or
Of course, no backup strategy is complete without testing a full restore
onto bare hardware :-)
On Tue, Jun 22, 2010 at 9:29 AM, Karl Denninger k...@denninger.net wrote:
Justin Graf wrote:
On 6/22/2010 4:31 AM, Grzegorz Jaśkiewicz wrote:
Would moving WAL dir to separate disk help
I haven't jumped in yet on this thread, but here goes
If you're really looking for query performance, then any database which is
designed with reliability and ACID consistency in mind is going to
inherently have some mis-fit features.
Some other ideas to consider, depending on your query
With Oracle, I've found an anti-union (MINUS in Oracle, EXCEPT in PGSQL) to
be often a bit better than an anti-join, which is in turn faster than NOT
IN. Depends of course on row distribution and index layouts, and a bunch of
other details.
Depending on what you're returning, it can pay to make
Definitely switch to RAID-10 it's not merely that it's a fair bit
faster on normal operations (less seek contention), it's **WAY** faster than
any parity based RAID (RAID-2 through RAID-6) in degraded mode when you lose
a disk and have to rebuild it. This is something many people don't test
I presume there is more usage of this view than just those 3 queries
(otherwise, for a start there would be no need for d, e, f in the view
definition)
Why not just rewrite these 3 queries to go directly off the main table? Or,
create a different view without the sort_by in its definition?
Or,
Our Java application manages its own schema. Some of this is from Hibernate,
but some is hand-crafted JDBC.
By way of an upgrade path, we have a few places where we have added
additional indexes to optimize performance, and so at startup time the
application issues CREATE INDEX ... statements for
Thanks folks, that makes sense. We're now being more precise with our DDL
:-)
Cheers
Dave
On Thu, Oct 7, 2010 at 3:40 PM, Robert Haas robertmh...@gmail.com wrote:
On Mon, Sep 27, 2010 at 3:27 PM, Gurjeet Singh singh.gurj...@gmail.com
wrote:
On Mon, Sep 27, 2010 at 8:50 PM, Dave Crooke dcro
Hi Tom
I suspect I may be missing something here, but I think it's a pretty
universal truism that cache lines are aligned to power-of-2 memory
addresses, so it would suffice to ensure during setup that the lower order n
bits of the object address are all zeros for each critical object; if the
There is a plugin called pgstattuple which can be quite informative
however, it actually does a full scan of the table / index files, which may
be a bit invasive depending on your environment and load.
http://www.postgresql.org/docs/current/static/pgstattuple.html
It's in the contrib (at
There is a process in Oracle which essentially allows you to do the
equivalent of a CLUSTER in Postgres, but without locking the table, and so
updates can continue throughout the process. It requires a bit of manual
setup and fiddling (at least in Oracle 10g) this would probably scratch
a lot
You will get the same behaviour from any database product where the query as
written requires type coercion - the coercion has to go in the direction of
the wider type. I have seen the exact same scenario with Oracle, and I
view it as a problem with the way the query is written, not with the
For any database, anywhere, the answer is pretty much always RAID-10.
The only time you would do anything else is for odd special cases.
Cheers
Dave
On Sun, Feb 13, 2011 at 2:12 PM, sergey sergey.on@gmail.com wrote:
Hello,
I got a disk array appliance of 8 disks 1T each (UltraStor
Hi foks
This is an old chestnut which I've found a number of online threads for, and
never seen a clever answer to. It seems a common enough idiom that there
might be some slicker way to do it, so I thought I might inquire with this
august group if such a clever answer exists
Consider the
Thanks to all I had a tickling feeling at the back of my mind that
there was a neater answer here. For the record, times (all from in-memory
cached data, averaged over a bunch of runs):
Dependent subquery = 117.9 seconds
Join to temp table = 2.7 sec
DISTINCT ON = 2.7 sec
So the DISTINCT ON
)
a, b, time_stamp,value
from data
order by a, b, time_stamp desc;
Not sure if this is considered a parser bug or not, but it feels slightly
odd not to get an error.
PG 8.4.7 installed from Ubuntu 10.04's 64-bit build.
Cheers
Dave
On Thu, Feb 24, 2011 at 5:38 PM, Dave Crooke dcro...@gmail.com
.
My next tweak will be to cache the latest table in the Java layer ;-)
Cheers
Dave
On Fri, Feb 25, 2011 at 10:50 AM, Dave Johansen davejohan...@gmail.comwrote:
On Thu, Feb 24, 2011 at 4:38 PM, Dave Crooke dcro...@gmail.com wrote:
Thanks to all I had a tickling feeling at the back of my
Depending on your needs, you might consider putting the data into a columnar
text search engine like Lucene, having it return the integer id's which can
then be used for row lookups in PG.
On Thu, Sep 22, 2011 at 11:40 AM, Jonathan Bartlett
jonathan.l.bartl...@gmail.com wrote:
I am working on
Hi James
I'm guessing the problem is that the combination of using a view and the way
the view is defined with an in-line temporary table is too complex for the
planner to introspect into, transform and figure out the equivalent direct
query, and so it's creating that entire temporary table
Claudio is on point, I'll be even more pointed
If pkey_id truly is a primary key in the database sense of the term, and
thus unique, then IIUC there is no circumstance in which your composite
index would ever even get used ... all it's doing is slowing down writes :-)
If the query is
The most common reason for this (not specific to PG) is that the function
is getting compiled without the substituted constants, and the query plan
is generic, whereas with specific values it is able to use column
statistics to pick a more efficient one.
On Nov 1, 2011 8:16 PM, Sabin Coanda
Hi folks
This could be a sheer volume issue, but I though I would ask the wisdom of
this forum as to next investigative steps.
We use PostgreSQL 8.4.4 which is bundled with our application as a VMware
virtual appliance. The bulk of the app's database activity is recording
performance data
, 21:16, Dave Crooke wrote:
Hi folks
This could be a sheer volume issue, but I though I would ask the wisdom
of
this forum as to next investigative steps.
We use PostgreSQL 8.4.4 which is bundled with our application as a VMware
virtual appliance. The bulk of the app's
Just curious ... has anyone tried using a ram disk as the PG primary and
DRBD as the means to make it persistent?
On Mar 1, 2012 11:35 AM, Scott Marlowe scott.marl...@gmail.com wrote:
On Thu, Mar 1, 2012 at 9:57 AM, Jeff Janes jeff.ja...@gmail.com wrote:
On Tue, Feb 28, 2012 at 3:46 PM, Stefan
This may just be a typo, but if you really did create write (dirty) block
device cache by writing the pg_dump file somewhere, then that is what it's
supposed to do ;) Linux is more aggressive about write cache and will allow
more of it to build up than e.g. HP-UX which will start to throttle
On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire klaussfre...@gmail.comwrote:
You perform 8 roundtrips minimum per event, so that's 375us per query.
It doesn't look like much. That's probably Nagle and task switching
time, I don't think you can get it much lower than that, without
issuing
On the larger, cellular Itanium systems with multiple motherboards (rx6600
to Superdome) Oracle has done a lot of tuning with the HP-UX kernel calls
to optimize for NUMA issues. Will be interesting to see what they bring to
Linux.
On Jul 17, 2012 9:01 PM, Scott Marlowe scott.marl...@gmail.com
Asif:
1. 6GB is pretty small once you work through the issues, adding RAM
will probably be a good investment, depending on your time-working set
curve.
A quick rule of thumb is this:
- if your cache hit ratio is significantly larger than (cache size / db
size) then there is locality of
81 matches
Mail list logo
