.
Please read following documentation material :
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
Regards,
--
Dimitri Fontaine
http://www.dalibo.com/
pgpTgCewok9P3.pgp
Description: PGP signature
the planner benefits from constraint_exclusion without selecting
the empty parent table (instead of your own union based view).
--
Dimitri Fontaine
http://www.dalibo.com/
pgpf4COGRyPRY.pgp
Description: PGP signature
some other tool(s)
while tests are running.
Regards,
--
Dimitri Fontaine
http://www.dalibo.com/
pgpfp2HJPIJH9.pgp
Description: PGP signature
in some future), cheaper and accurate?
After all, the discussion, as far as I understand it, is about having a
accurate measure of duration of events, knowing when they occurred in the day
does not seem to be the point.
My 2¢, hoping this could be somehow helpfull,
--
Dimitri Fontaine
http
://pgfouine.projects.postgresql.org/tsung.html
http://tsung.erlang-projects.org/
http://debian.dalibo.org/unstable/
This latter link also contains a .tar.gz archive of tsung-ploter in case
you're not running a debian system. Dependencies are python and matplotlib.
Regards,
--
Dimitri Fontaine
http://www.dalibo.com
://tsung.erlang-projects.org/
[4]: http://debian.dalibo.org/unstable/tsung-ploter_0.1-1.tar.gz
Regards,
--
Dimitri Fontaine
http://www.dalibo.com/
pgpHLPZaAGz2d.pgp
Description: PGP signature
fairly compare.
[...]
Has anybody done anything similar to this? Anything better? Did it
end up being a waste of time, or was it helpful?
Please have a look at this:
http://pgfouine.projects.postgresql.org/tsung.html
--
Dimitri Fontaine
http://www.dalibo.com/
pgpyo3gUCBBqO.pgp
Description: PGP
Tsung and pgfouine softwares.
http://tsung.erlang-projects.org/
http://pgfouine.projects.postgresql.org/tsung.html
Regards,
--
Dimitri Fontaine
---(end of broadcast)---
TIP 6: explain analyze is your friend
Le jeudi 07 juin 2007, Kurt Overberg a écrit :
Is there a primer somewhere on how to read EXPLAIN output?
Those Robert Treat slides are a great reading:
http://www.postgresql.org/communityfiles/13.sxi
Regards,
--
dim
---(end of broadcast)---
Le jeudi 14 juin 2007, Sabin Coanda a écrit :
I'd like to understand completely the report generated by VACUUM VERBOSE.
Please tell me where is it documented ?
Try the pgfouine reporting tool :
http://pgfouine.projects.postgresql.org/
Hi,
Le mercredi 18 juillet 2007, Jonah H. Harris a écrit :
On 7/18/07, Benjamin Arai [EMAIL PROTECTED] wrote:
But I want to parrallelize searches if possible to reduce
the perofrmance loss of having multiple tables.
PostgreSQL does not support parallel query. Parallel query on top of
Hi,
Le Friday 21 September 2007 01:04:01 Decibel!, vous avez écrit :
I'm finding this rather interesting report from top on a Debian box...
I've read from people in other free software development groups that top/ps
memory usage outputs are not useful not trustable after all. A more usable
Hi,
Le jeudi 11 octobre 2007, Kevin Kempter a écrit :
I'm preparing to create a test suite of very complex queries that can be
profiled in terms of load and performance. The ultimate goal is to define a
load/performance profile during a run of the old application code base and
then again with
Hi,
Le lundi 29 octobre 2007, Tom Lane a écrit :
Is there any chance you can apply the one-line
patch shown here:
http://archives.postgresql.org/pgsql-committers/2007-10/msg00374.php
If rebuilding packages is not to your taste, possibly a down-rev to
8.2.4 would be the easiest solution.
Hi List,
Le mardi 06 novembre 2007, Tore Halset a écrit :
1) Dell 2900 (5U)
8 * 146 GB SAS 15Krpm 3,5
8GB ram
Perc 5/i. battery backup. 256MB ram.
2 * 4 Xeon 2,66GHz
In fact you can add 2 hot-plug disks on this setup, connected to the
frontpane. We've bought this very same model with 10 15
Le mardi 06 novembre 2007, Tore Halset a écrit :
Interesting. Do you have any benchmarking numbers? Did you test with
software raid 10 as well?
Just some basic pg_restore figures, which only make sense (for me anyway) when
compared to restoring same data on other machines, and to show the
Le Thursday 08 November 2007 19:22:48 Scott Marlowe, vous avez écrit :
On Nov 8, 2007 10:43 AM, Vivek Khera [EMAIL PROTECTED] wrote:
On Nov 6, 2007, at 1:10 PM, Greg Smith wrote:
elsewhere. But once you have enough disks in an array to spread all
the load over that itself may improve
Le jeudi 20 décembre 2007, Decibel! a écrit :
A work-around others have used is to have the trigger just insert
into a 'staging' table and then periodically take the records from
that table and summarize them somewhere else.
And you can even use the PgQ skytools implementation to easily have
Hi,
Le lundi 04 février 2008, Jignesh K. Shah a écrit :
Single stream loader of PostgreSQL takes hours to load data. (Single
stream load... wasting all the extra cores out there)
I wanted to work on this at the pgloader level, so CVS version of pgloader is
now able to load data in parallel,
Le mardi 05 février 2008, Simon Riggs a écrit :
It runs a stream of COPY statements, so only first would be optimized
with the empty table optimization.
The number of rows per COPY statement is configurable, so provided you have an
estimation of the volume to import (wc -l), you could tweak
Le mardi 05 février 2008, Simon Riggs a écrit :
I'll look at COPY FROM internals to make this faster. I'm looking at
this now to refresh my memory; I already had some plans on the shelf.
Maybe stealing some ideas from pg_bulkload could somewhat help here?
Le mercredi 06 février 2008, Greg Smith a écrit :
pgloader is a great tool for a lot of things, particularly if there's any
chance that some of your rows will get rejected. But the way things pass
through the Python/psycopg layer made it uncompetative (more than 50%
slowdown) against the
Hi,
I've been thinking about this topic some more, and as I don't know when I'll
be able to go and implement it I'd want to publish the ideas here. This way
I'll be able to find them again :)
Le mardi 05 février 2008, Dimitri Fontaine a écrit :
Le mardi 05 février 2008, Simon Riggs a écrit
Le mercredi 06 février 2008, Simon Riggs a écrit :
For me, it would be good to see a --parallel=n parameter that would
allow pg_loader to distribute rows in round-robin manner to n
different concurrent COPY statements. i.e. a non-routing version.
What happen when you want at most N parallel
Le mercredi 06 février 2008, Greg Smith a écrit :
COPY. If you're loading a TB, if you're smart it's going onto the server
itself if it all possible and loading directly from there. Would probably
get a closer comparision against psql \copy, but recognize you're always
going to be compared
Le mercredi 06 février 2008, Greg Smith a écrit :
If I'm loading a TB file, odds are good I can split that into 4 or more
vertical pieces (say rows 1-25%, 25-50%, 50-75%, 75-100%), start 4 loaders
at once, and get way more than 1 disk worth of throughput reading.
pgloader already supports
Le Wednesday 06 February 2008 18:37:41 Dimitri Fontaine, vous avez écrit :
Le mercredi 06 février 2008, Greg Smith a écrit :
If I'm loading a TB file, odds are good I can split that into 4 or more
vertical pieces (say rows 1-25%, 25-50%, 50-75%, 75-100%), start 4
loaders at once, and get
Le Wednesday 06 February 2008 18:49:56 Luke Lonergan, vous avez écrit :
Improvements are welcome, but to compete in the industry, loading will need
to speed up by a factor of 100.
Oh, I meant to compete with internal COPY command instead of \copy one, not
with the competition. AIUI competing
Le jeudi 07 février 2008, Greg Smith a écrit :
Le mercredi 06 février 2008, Dimitri Fontaine a écrit :
In other cases, a logical line is a physical line, so we start after first
newline met from given lseek start position, and continue reading after the
last lseek position until a newline
Hi,
You may remember some thread about data loading performances and
multi-threading support in pgloader:
http://archives.postgresql.org/pgsql-performance/2008-02/msg00081.php
The pgloader code to handle this is now ready to get tested, a more structured
project could talk about a Release
Hi,
Le dimanche 27 avril 2008, Greg Smith a écrit :
than SQL*PLUS. Then on the PostgreSQL side, you could run multiple COPY
sessions importing at once to read this data all back in, because COPY
will bottleneck at the CPU level before the disks will if you've got
reasonable storage hardware.
Hi,
Le samedi 28 juin 2008, Moritz Onken a écrit :
select count(*)
from result
where exists
(select * from item where item.url LIKE result.url || '%' limit 1);
which basically returns the number of items which exist in table
result and match a URL in table item by its prefix.
It
Hi,
Le mercredi 10 septembre 2008, Ryan Hansen a écrit :
One thing I'm experiencing some trouble with is running a COPY of a
large file (20+ million records) into a table in a reasonable amount of
time. Currently it's taking about 12 hours to complete on a 64 bit
server with 3 GB memory
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Le 10 sept. 08 à 19:16, Bill Moran a écrit :
There's a program called pgloader which supposedly is faster than
copy.
I've not used it so I can't say definitively how much faster it is.
In fact pgloader is using COPY under the hood, and doing so
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
Le 9 oct. 08 à 21:30, Tom Lane a écrit :
There's not a lot you can do about that at the moment. 8.4 will have
the ability to inline functions returning sets, if they're SQL-
language
and consist of just a single SELECT, but existing releases
Hi,
Le vendredi 26 décembre 2008, Tom Lane a écrit :
Yeah, if he's willing to use COPY BINARY directly. AFAIR there is not
an option to get pg_dump to use it.
Would it be possible to consider such an additional switch to pg_dump?
Of course the DBA has to know when to use it safely, but if
Hi,
On Wednesday 04 March 2009 02:37:42 Scott Marlowe wrote:
If some oddball query really needs a lot of work_mem,
and benchmarks show something larger work_mem helps, consider raising
the work_mem setting for that one query to something under 1G (way
under 1G) That makes it noticeably
On Thursday 12 March 2009 14:38:56 Frank Joerdens wrote:
I just put the patched .deb on staging and we'll give it a whirl there
for basic sanity checking - we currently have no way to even
approximate the load that we have on live for testing.
Is it a capacity problem or a tool suite problem?
On Monday 06 April 2009 14:35:30 Andrew Sullivan wrote:
*SkyTools/Londiste* - Don't know anything special about it.
I've been quite impressed by the usability. It's not quite as
flexible as Slony, but it has the same theory of operation. The
documentation is not as voluminous, although
Hi,
Ok I need to answer some more :)
Le 8 avr. 09 à 20:20, Jeff a écrit :
To add a table with a pk you edit slon_tools.conf and add something
along the lines of:
someset = {
set_id = 5,
table_id = 5,
pkeyedtables = [ tacos, burritos, gorditas ]
}
then you just run
Hi,
Le 12 mai 09 à 18:32, Robert Haas a écrit :
implement this same logic internally? IOW, when a client disconnects,
instead of having the backend exit immediately, have it perform the
equivalent of DISCARD ALL and then stick around for a minute or two
and, if a new connection request arrives
Hi,
Le 13 mai 09 à 18:42, Scott Carey a écrit :
will not help, as each client is *not* disconnecting/reconnecting
during the test, as well PG is keeping well even 256 users. And TPS
limit is reached already on 64 users, don't think pooler will help
here.
Actually, it might help a little.
Hi,
Greg Smith gsm...@gregsmith.com writes:
I keep falling into situations where it would be nice to host a server
somewhere else. Virtual host solutions and the mysterious cloud are no good
for the ones I run into though, as disk performance is important for all the
applications I have to
Hi,
Le 27 mai 09 à 19:57, Alan McKay a écrit :
I have done some googling and found a few things on the matter. But
am looking for some suggestions from the experts out there.
Got any good pointers for reading material to help me get up to speed
on PostgreSQL clustering? What options are
Hi, Peter Sheats pshe...@pbpost.com writes: I’m about to
set up a large instance on Amazon EC2 to be our DB server.
Before we switch to using it in production I would like to
simulate some load on it so that I know what it can handle and so
that I can make sure I have the optimal
Kenneth Cox kens...@gmail.com writes:
On Tue, 02 Jun 2009 05:26:41 -0400, Dimitri Fontaine
dfonta...@hi-media.com wrote:
I'd recommand having a look at tsung which will be able to replay a
typical application scenario with as many concurrent users as you want
to: http
Hi,
Le 6 juin 09 à 10:50, Simon Riggs a écrit :
On Wed, 2009-06-03 at 21:21 -0400, Robert Haas wrote:
But, we're not always real clever about selectivity. Sometimes you
have to fake the planner out, as discussed here.
[...]
Fortunately, these kinds of problems are fairly rare, but they
Віталій Тимчишин tiv...@gmail.com writes:
I'd prefer ALTER VIEW name SET ANALYZE=true; or CREATE/DROP ANALYZE SQL;
Also it should be possible to change statistics target for analyzed
columns.
Yeah, my idea was ALTER VIEW name ENABLE ANALYZE; but that's an easy
point to solve if the idea
Markus Wanner mar...@bluegap.ch writes:
If anybody has ever tried their systems, I'd like to hear back. I wish such
an offering would exist for Europe (guess that's just a matter of time).
http://www.niftyname.org/
http://lost-oasis.fr/
It seems to be coming very soon, in France :)
--
Hi,
Shaul Dar shaul...@gmail.com writes:
1. A staging server, which receives new data and updates the DB
2. Two web servers that have copies of the DB (essentially read-only)
and answer user queries (with load balancer)
[...]
Suggestions?
I'd consider WAL Shipping for the staging server
Hi,
Le 24 juin 09 à 18:29, Alvaro Herrera a écrit :
Oleg Bartunov wrote:
On Wed, 24 Jun 2009, Chris St Denis wrote:
Is tsvector_update_trigger() smart enough to not bother updating a
tsvector if the text in that column has not changed?
no, you should do check yourself. There are several
Also consider on update triggers that you could want to run anyway
--
dim
Le 25 juin 2009 à 07:45, Craig Ringer cr...@postnewspapers.com.au a
écrit :
On Wed, 2009-06-24 at 21:03 -0700, Chris St Denis wrote:
This sounds like something that should just be on by default, not a
trigger. Is
Hi,
Le 16 juil. 09 à 11:52, Andres Freund a écrit :
If I interpret those findings correcty the execution is approx. as
fast as DB2,
only DB2 is doing automated plan caching while pg is not.
If it _really_ is necessary that this is that fast, you can prepare
the query
like I showed.
A
Try tsung, dig the archives for a pg specific howto. Tsung is open
source and supports multiple protocols.
Regards,
--
dim
Le 31 juil. 2009 à 08:50, Chris dmag...@gmail.com a écrit :
Hi,
Everyone says load test using your app - out of interest how does
everyone do that at the database
Merlin Moncure mmonc...@gmail.com writes:
like joining the result to another table...the planner can see
'through' the view, etc. in a function, the result is fetched first
and materialized without looking at the rest of the query.
I though the planner would see through SQL language
Hi,
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
Joshua Rubin wrote:
We hardcode the parts of the where clause so that the prepared plan
will not vary among the possible partitions of the table. The only
values that are bound would not affect the planner's choice of table.
astro77 astro_co...@yahoo.com writes:
Kevin Grittner wrote:
I would try to minimize how many XML values it had to read, parse, and
search. The best approach that comes to mind would be to use tsearch2
techniques (with a GIN or GiST index on the tsvector) to identify
which rows contain
Tom Lane t...@sss.pgh.pa.us writes:
It's worth your time to learn how to do this on whatever system you
prefer to use. Then, if you're ever in a situation where you really
need patch XYZ right now, you can easily add that patch to the package
sources and rebuild a custom version that will
Cédric Villemain cedric.villem...@dalibo.com writes:
If you want the latest and greatest, then you can use Debian testing.
testing and sid are usually the same with a 15 days delay.
And receive no out-of-band security updates, so you keep the holes for
3 days when lucky, and 10 to 15 days
Brian Karlak zen...@metaweb.com writes:
I have a simple queuing application written on top of postgres which I'm
trying to squeeze some more performance out of.
Have you tried to write a custom PGQ consumer yet?
http://wiki.postgresql.org/wiki/PGQ_Tutorial
Regards,
--
dim
--
Sent via
Hi,
Ben Brehmer benbreh...@gmail.com writes:
By Loading data I am implying: psql -U postgres -d somedatabase -f
sql_file.sql. The sql_file.sql contains table creates and insert
statements. There are no
indexes present nor created during the load.
OS: x86_64-unknown-linux-gnu, compiled by
Scott Marlowe scott.marl...@gmail.com writes:
That's a lot of work to get to COPY.
Well, yes. I though about it this way only after having read that OP is
uneasy with producing another format from his source data, and
considering it's a one-shot operation.
Ah, tradeoffs, how to find the right
Jesper Krogh jes...@krogh.cc writes:
I have a message queue table, that contains in the order of 1-10m
messages. It is implemented using TheSchwartz:
http://search.cpan.org/~bradfitz/TheSchwartz-1.07/lib/TheSchwartz.pm
One way to approach queueing efficiently with PostgreSQL is to rely on
PGQ.
Bob Dusek redu...@gmail.com writes:
So, pgBouncer is pretty good. It doesn't appear to be as good as
limiting TCON and using pconnect, but since we can't limit TCON in a
production environment, we may not have a choice.
You can still use pconnect() with pgbouncer, in transaction mode, if
your
Tom Lane t...@sss.pgh.pa.us writes:
Davor J. dav...@live.com writes:
Now, if one takes a subquery for 1, the optimizer evaluates it first
(let's say to 1), but then searches for it (sequentially) in every
partition, which, for large partitions, can be very time-consuming and goes
beyond
Greg Smith g...@2ndquadrant.com writes:
I'm not sure how to make progress on similar ideas about
tuning closer to the filesystem level without having something automated
that takes over the actual benchmark running and data recording steps; it's
just way too time consuming to do those right
Corin wakath...@gmail.com writes:
I'm running quite a large social community website (250k users, 16gb
database). We are currently preparing a complete relaunch and thinking about
switching from mysql 5.1.37 innodb to postgresql 8.4.2. The database server
is a dual dualcore operton 2216 with
Greg Smith g...@2ndquadrant.com writes:
However, that doesn't actually solve any of the problems I was talking about
though, which is why I'm not even talking about that part. We need the glue
to pull out software releases, run whatever testing tool is appropriate, and
then save the run
Balkrishna Sharma b...@hotmail.com writes:
I will have a web application having postgres 8.4+ as backend. At any given
time, there will be max of 1000 parallel web-users interacting with the
database (read/write)
I wish to do performance testing of 1000 simultaneous read/write to
the
Pierre C li...@peufeu.com writes:
The same is true of a web server : 1000 active php interpreters (each eating
several megabytes or more) are not ideal for performance !
For php, I like lighttpd with php-fastcgi : the webserver proxies requests
to a small pool of php processes, which are only
Hi,
Josh Berkus j...@agliodbs.com writes:
a) Eliminate WAL logging entirely
b) Eliminate checkpointing
c) Turn off the background writer
d) Have PostgreSQL refuse to restart after a crash and instead call an
exteral script (for reprovisioning)
Well I guess I'd prefer a per-transaction
Tom Lane t...@sss.pgh.pa.us writes:
The problem with a system-wide no-WAL setting is it means you can't
trust the system catalogs after a crash. Which means you are forced to
use initdb to recover from any crash, in return for not a lot of savings
(for typical usages where there's not really
t...@exquisiteimages.com writes:
I am wondering how I should architect this in PostgreSQL. Should I follow
a similar strategy and have a separate database for each client and one
database that contains the global data?
As others said already, there's more problems to foresee doing so that
Tom Lane t...@sss.pgh.pa.us writes:
I agree with the comments to the effect that this is really a packaging
and documentation problem. There is no need for us to re-invent the
existing solutions, but there is a need for making sure that they are
readily available and people know when to use
Greg Smith g...@2ndquadrant.com writes:
Craig James wrote:
By using current and encouraging people to link to that, we could
quickly change the Google pagerank so that a search for Postgres would
turn up the most-recent version of documentation.
How do you propose to encourage people to do
Craig Ringer cr...@postnewspapers.com.au writes:
9.0 has application_name to let apps identify themselves. Perhaps a
pooled_client_ip, to be set by a pooler rather than the app, could be
added to address this problem in a way that can be used by all poolers
new and existing, not just any new
http://preprepare.projects.postgresql.org/README.html
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
queries across multiple simulations at once? If
yes, you want to avoid multi databases. Other than that, I'd go with a
naming convention like samples_simulation id and maybe some
inheritance to ease querying multiple simulations.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL
how closely it is related, but have you tried preprepare?
https://github.com/dimitri/preprepare
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes
79 matches
Mail list logo