I have this patch, if you're interested.
LWLock Instrumentation Patch
- counts locks and waits in shared and exclusive mode
- for selected locks, measures wait and hold times
- for selected locks, displays a histogram of wait and hold times
- information is printed at backend exit
OK, that makes sense. I thought you had hacked either XLogInsert or the
heap WAL replay code so that you could just accumulate tuples in the
rdata
chain and then submit them all under the cover of a single WALInsertLock.
Ah, no, I did not do that.
This would be difficult to do : rdata
Yes, I did not consider that to be a problem because I did not think it
would be used on indexed tables. I figured that the gain from doing bulk
inserts into the table would be so diluted by the still-bottle-necked
index maintenance that it was OK not to use this optimization for
indexed
Does that heuristic change the timings much? If not, it seems like it
would
better to keep it simple and always do the same thing, like log the
tuples
(if it is done under one WALInsertLock, which I am assuming it is..)
It is the logging of whole pages that makes it faster.
If you fill a
A little bit of a reply to Jeff's email about WALInsertLock.
This patch instruments LWLocks, it is controlled with the following
#define's in lwlock.c :
LWLOCK_STATS
LWLOCK_TIMING_STATS
It is an upgrade of current lwlocks stats.
When active, at backend exit, it will display stats as shown
I've done a little experiment with bulk inserts.
= heap_bulk_insert()
Behaves like heap_insert except it takes an array of tuples (HeapTuple
*tups, int ntups).
- Grabs a page (same as heap_insert)
- While holding exclusive lock, inserts as many tuples as it can on the
page.
-
Have you looked at the total execution time with and without the
LWLOCK_TIMING_STATS?
It didn't show any significant overhead on the little COPY test I made.
On selects, it probably does (just like EXPLAIN ANALYZE), but I didn't
test.
It is not meant to be always active, it's a #define,
Replying to myself...
Jeff suggested to build pages in local memory and insert them later in the
table. This is what's used in CLUSTER for instance, I believe.
It has some drawbacks though :
- To insert the tuples in indexes, the tuples need tids, but if you build
the page in local
I was thinking something like:
COPY tablename [ ( column [, ...] ) ] FROM { 'filename' | STDIN }
[WITH] [option [, ...]]
Where:
option := ColId [Sconst] | FORCE NOT NULL (column [,...])
I don't see any reasonable way to sandwhich the FORCE NOT NULL syntax
into a keyword/value notation.
I was looking in the int*in() functions and found some interesting limit
behaviours :
* Here, the most negative value is sometimes accepted, sometimes not :
test= SELECT -2147483648::INTEGER;
ERREUR: entier en dehors des limites
test= SELECT '-2147483648'::INTEGER;
int4
-
I've been idly thinking about binary COPY and recent performance efforts
spent on it. The main problem I have with binary output is that I never
know when it'll be any useful (except very same hardware and PostgreSQL
setup)... useful meaning I get to read it back into some database.
I posted
AFAIK you need to run app with LD_LIBRARY_PATH=/usr/lib/debug,
otherwise the debug packages won't be used.
I had stupidly put the LD_LIBRARY_PATH on make rather than on postgres,
ahem.
OK, it works, thanks.
I'm very carefully benchmarking everything everytime I make a
I'm doing some more exploration with oprofile...
I've got the glibc-debug package installed (on kubuntu), but oprofile
doesn't seem to know about it. I wonder what part of glibc gets 60% of the
run time... do I have to set a magic option in the postgres config ?
samples %image
Primary motivation of the attached patch is to support handling bytea
conversion allowing for embedded nulls, which in turn allows for
supporting the marshal module.
Secondary motivation is slightly improved performance for conversion
routines of basic datatypes that have simple mappings
But when I see a big red button, I just press it to see what happens.
Ugly hacks are useful to know how fast the thing can go ; then the
interesting part is to reimplement it cleanly, trying to reach the
same performance...
Right -- now that you've shown a 6x speedup increase, it is clear
In the previous mails I made a mistake, writing MTuples/s instead of
MDatums/s, sorry about that. It is the number of rows x columns. The
title was wrong, but the data was right.
I've been doing some tests on COPY FROM ... BINARY.
- inlines in various pg_get* etc
- a faster buffer handling
Replying to myself...
I've been examining the code path for COPY FROM too, and I think it is
possible to get the same kind of speedups on COPY FROM that the patch in
the previous message did for COPY TO, that is to say perhaps 2-3x faster
in BINARY mode and 10-20% faster in TEXT mode (these
For future reference, and since this keeps appearing every few months:
The
license of LZO is not acceptable for inclusion or use with PostgreSQL.
You
need to find a different library if you want to pursue this further.
Yes, I know about the license... I used LZO for tests, but since my
If you do as much damage to the I/O function API as the other patch
did, it will probably be rejected.
You mean, as the COPY patch in my previous message, or as another patch
?
(I just search the archives and found one about CopyReadLine, but that's
probably not what you are
We don't touch datatype APIs
lightly, because it affects too much code.
regards, tom lane
I definitely agree with that.
Actually, let me clarify:
When I modified the datatype API, I was feeling uneasy, like I shouldn't
really touch this.
But when I see
Well, here is the patch. I've included a README, which I paste here.
If someone wants to play with it (after the CommitFest...) feel free to
do so.
While it was an interesting thing to try, I don't think it has enough
potential to justify more effort...
* How to test
- apply
Incidentally, we billed pg_dump as hot backup at some point.
mysql calls mysqlhotcopy a script that locks and flushes all tables,
then makes a copy of the database directory (all queries being locked out
while this is in progress, of course).
--
Sent via pgsql-hackers mailing list
This is a spinoff of the current work on compression...
I've discovered that linux doesn't apply readahead to sparse files.
So I added a little readahead in seq scans.
Then I realized this might also be beneficial for the standard Postgres.
On my RAID1 it shows some pretty drastic effects.
The
First, a few things that I forgot to mention in the previous message :
I like the idea too, but I think there are some major problems to
solve. In particular I think we need a better solution to blocks
growing than sparse files.
Sparse files allow something great : to test this concept
On Thu, Aug 6, 2009 at 4:03 PM, Greg Starkgsst...@mit.edu wrote:
I like the idea too, but I think there are some major problems to
solve. In particular I think we need a better solution to blocks
growing than sparse files.
How much benefit does this approach have over using TOAST compression
Also, I'm puzzled why it would the space increase would proportional
to the amount of data and be more than 300 bytes. There's no reason it
wouldn't be a small fixed amount. The ideal is you set aside one bit
-- if the bit is set the rest is compressed and has to save at least
one bit. If the
For reference what I'm picturing is this:
When a table is compressed it's marked read-only which bars any new
tuples from being inserted or existing tuples being deleted. Then it's
frozen and any pages which contain tuples wich can't be frozen are
waited on until they can be. When it's finished
Not strictly related to compression, but I've noticed something really
strange...
pg 8.4 (vanilla) is doing it, and my compressed version is doing it too.
tablespace is a RAID5 of 3 drives, xlog in on a RAID1 of 2 drives,
but it does it too if I put the tablespace and data on the same volume.
On Fri, 07 Aug 2009 15:42:35 +0200, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Pierre Frédéric Caillaudli...@peufeu.com wrote:
tablespace is a RAID5 of 3 drives, xlog in on a RAID1 of 2 drives,
but it does it too if I put the tablespace and data on the same
volume.
it starts out
29 matches
Mail list logo
