e 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 t
.
Do you intend to run 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_ and maybe some
inheritance to ease querying multiple simulations.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr P
gresql.org/wiki/PgBouncer
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://w
Craig Ringer 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 in-core pooling system.
Greg Smith 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 that?
Wh
Tom Lane 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 them.
On this t
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
t
Tom Lane 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 much churn in t
Hi,
Josh Berkus 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 setting, allowing
"Pierre C" 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 busy whil
Balkrishna Sharma 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 database.
See abo
Greg Smith 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 artifacts in some stan
Corin 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 12gb ram running
Greg Smith 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 now with every too
Tom Lane writes:
> "Davor J." 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 the point of partitio
Bob Dusek 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 application is
Jesper Krogh 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. New upco
Scott Marlowe 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 one!
--
dim
--
Sent
Hi,
Ben Brehmer 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 GCC gcc (G
Brian Karlak 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 pgsql-performance mai
Cédric Villemain 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 otherwise, when choosing
tes
Tom Lane 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 play nicely within
astro77 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 'fdc3da1f-060f-4c34
Hi,
Heikki Linnakangas 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.
>
> Then you would benefit f
Merlin Moncure 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 functions and
inline t
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 a écrit :
Hi,
Everyone says "load test using your app" - out of interest how does
everyone do that at the database level?
I've trie
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 for
Also consider on update triggers that you could want to run anyway
--
dim
Le 25 juin 2009 à 07:45, Craig Ringer 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 there some reason it would
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 exa
Hi,
Shaul Dar 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 and some trigger
"Markus Wanner" 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 :)
--
dim
--
Sent v
Віталій Тимчишин writes:
> I'd prefer ALTER VIEW SET ANALYZE=true; or CREATE/DROP ANALYZE ;
> Also it should be possible to change statistics target for analyzed
> columns.
Yeah, my idea was ALTER VIEW ENABLE ANALYZE; but that's an easy
point to solve if the idea proves helpful.
> Such a stat
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 can
"Kenneth Cox" writes:
> On Tue, 02 Jun 2009 05:26:41 -0400, Dimitri Fontaine
> 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://arch
Hi, "Peter Sheats" 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 settings in the conf
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 avai
Hi,
Greg Smith 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 deal with.
A fre
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. Post
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,
Dimitri writes:
>>> So, why I don't use prepare here: let's say I'm testing the worst
>>> stress case :-) Imagine you have thousands of such kind of queries -
>>> you cannot prepare all of them! :-)
>>
>> Thousands? Surely there'll be a dozen or three of most common queries,
>> to which yo
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" ]
}
th
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, althoug
Hi,
Le 26 mars 09 à 15:30, Matthew Wakeling a écrit :
Now, it happens that there is an algorithm for calculating overlaps
which is really quick. It involves iterating through the table in
order of the start variable and keeping a list of ranges which
"haven't ended yet". When you read the n
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
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 fas
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
-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 w
-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 v
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 a
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.
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 hardwa
Hi,
Le mardi 26 février 2008, Dimitri Fontaine a écrit :
> 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
As people here have asked for the new features im
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 Can
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 l
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 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
>
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 st
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 compare
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 paral
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évri
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 s
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?
http://pgfoundry.org/docman/view.php
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
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 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
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 i
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 eff
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
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,
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 w
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
(o
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
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/
http://pgfouine.projects.postgresql.org/reports/sample_
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)---
T
nsider 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
gs, so that I can 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/
pgp
[3]: http://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
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
r having a distributed processing
ready EA 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 he
e results, for disk io though you'll have to use some other tool(s)
while tests are running.
Regards,
--
Dimitri Fontaine
http://www.dalibo.com/
pgpfp2HJPIJH9.pgp
Description: PGP signature
inheritance?
I'm not sure 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
_exclusion to on in your postgresql.conf.
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
82 matches
Mail list logo
