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 refe
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" wrote:
> On Tue, Jul 17,
On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire wrote:
>
> 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 less queries (ie: usi
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
proces
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" wrote:
> On Thu, Mar 1, 2012 at 9:57 AM, Jeff Janes wrote:
> > On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller
> wrote:
> >> 2012/2/28 Claudi
dra wrote:
> On 24 Leden 2012, 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
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 p
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" wrot
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 sufficie
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 every
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 o
the the
business logic will be appropriately fooled.
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 wrote:
> On Thu, Feb 24, 2011 at 4:38 PM, Dave Crooke wrote:
>
>> Thanks to all
on (a,b)
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
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 m
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 fo
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 wrote:
> Hello,
>
> I got a disk array appliance of 8 disks 1T each (UltraStor RS8IP4). It will
> be used
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
data
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
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 lea
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
mall
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 wrote:
> On Mon, Sep 27, 2010 at 3:27 PM, Gurjeet Singh
> wrote:
> > On Mon, Sep 27, 2010 at 8:50 PM, Dave Crooke wrote:
> >>
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 fo
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, if
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 fo
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 su
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 mix
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 wrote:
> Justin Graf wrote:
>
> On 6/22/2010 4:31 AM, Grzegorz Jaśkiewicz wrote:
>
>
> Would moving WAL dir to separate disk help potentially ?
>
>
>
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 t
t 7:41 PM, Tom Wilcox wrote:
> Hi Dave,
>
> I am definitely able to switch OS if it will get the most out of Postgres.
> So it is definitely a case of choosing the OS on the needs if the app
> providing it is well justified.
>
> Currently, we are running Ubuntu Server 64-bit
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 t
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 li
Never say never with computer geeks
http://www.youtube.com/watch?v=mJyAA0oPAwE
On Fri, Jun 11, 2010 at 7:44 AM, Kenneth Marshall 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 assume that
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 wrote:
> On Mon, May 24, 2010 at 12:25 PM, Merlin Moncure
> wrote:
> > *) shared_buffers is one of the _least_ important perfo
"Because it's policy" is rarely a good design decision :-) Lose the FK
constraints, and make up for them with integrity checking queries.
I just did a major refactor and shard on our PG schema and the performance
improvement was dramatic ... a big plus for PG, if it is e.g. time-series
data is to
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
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 wrote:
>
> > AFAICT from the Java end, ResultSet.close() is supposed t
f 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 wrote:
> Dave Crooke wrote:
>
> > I'd consider the fact that ResultSet.close(
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, i
ve
On Mon, Apr 19, 2010 at 6:28 PM, Dave Crooke 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 fr
PM, Dave Crooke wrote:
> > On Thu, Apr 15, 2010 at 2:42 PM, Dave Crooke 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:
> >
> >
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 wrote:
> I have followed the instructions below to no av
e
On Thu, Apr 15, 2010 at 2:42 PM, Dave Crooke 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 attemp
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
wrote:
> On 15/04/10 04:49, Dave Crooke wrote:
>
>
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
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 expande
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
MyISAM is SQLLite with some threading ;-)
On Tue, Mar 23, 2010 at 6:30 PM, Scott Marlowe wrote:
> On Tue, Mar 23, 2010 at 5:07 PM, Dave Crooke wrote:
> > What about InnoDB?
>
> Depends on what parts of mysql they otherwise use. There are plenty
> of features that won't w
What about InnoDB?
On Tue, Mar 23, 2010 at 4:38 PM, Greg Smith 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 sure we'd get
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
relyin
> wrote:
>
> On Sun, Mar 21, 2010 at 9:14 PM, Dave Crooke 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
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" wrote:
On Sat, Mar 20, 2010 at 11:47 PM, Andy Colson wrote:
> Don't underestimate my...
for non trivial selects (myis
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 ma
One more from me
If you think that the pipe to GZIP may be causing pg_dump to stall, try
putting something like buffer(1) in the pipeline ... it doesn't generally
come with Linux, but you can download source or create your own very easily
... all it needs to do is asynchronously poll stdin an
K.I.S.S. here . the best way to do one of these in most DB's is
typically an outer join and test for null:
select f1.* from friends f1
left outer join friends f2 on (f1.user_id=f2.ref_id and
f1.ref_id=f2.user_id)
where f2.id is null;
On Fri, Mar 19, 2010 at 7:26 AM, Corin wrote:
> Hi
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 table
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, but
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 A
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
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,
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
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,
they had to go across
10-20 tables.
Thanks everyone for the insights
Cheers
Dave
On Tue, Mar 9, 2010 at 6:46 AM, Merlin Moncure wrote:
> On Wed, Feb 24, 2010 at 4:31 PM, Dave Crooke wrote:
> > This is a generic SQL issue and not PG specific, but I'd like to get
> > an opin
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 reduc
the whole row. 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 wrote:
> On 24/02/10 22:47, Dave Crooke wrote:
>>
ECT DISTINCT ON (city)
> * FROM bar
> ORDER 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 O
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 varying
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
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 applicat
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 sto
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 part
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
On Wed, Feb 10, 2010 at 5:30 PM, Jon Lewison 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 only the
newest v
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
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 one-process-per-connec
15, 2010 at 10:10 AM, Tony McC wrote:
> On Thu, 14 Jan 2010 16:35:53 -0600
> Dave Crooke wrote:
>
> > For any given database engine, regardless of the marketing and support
> > stance, there is only one true "primary" enterprise OS platform that
> > most big
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
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
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 li
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. pe
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.
Ba
tiple 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 wrote:
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 p
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 guaran
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 sub
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
Thanks folks for the quick replies.
1. There is one transaction, connected from the JVM, that is showing
"IDLE in transaction" this appears to be a leftover from
Hibernate looking at the schema metadata. It's Apache Jackrabbit, not
our own code:
hyper9test_1_6=# select c.relname, l.* from pg
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 s
87 matches
Mail list logo
