[EMAIL PROTECTED] wrote:
And since it's basically impossible to know the selectivity of this kind
of where condition, I doubt the planner would ever realistically want to
choose that plan anyway because of its poor worst-case behavior.
What is a real life example where an intelligent and resear
Hi *,
for caching large autogenerated XML files, I have created a bytea table
in my database so that the cached files can be used by multiple servers.
There are about 500 rows and 10-20 Updates per minute on the table. The
files stored in the bytea are anything from 10kB to 10MB. My PostgreSQL
I set up pg to replace a plain gdbm database for my application. But
even running to the same machine, via a unix socket
* the pg database ran 100 times slower
Across the net it was
* about 500 to 1000 times slower than local gdbm
with no cpu use to speak of.
I'd heard that networked
Hi all,
I have a doubt/problem about how PostgreSQL handles multiple DDBB
instances running on a same server and how I should design the
architecture of an application.
I have an application that works with multiple customers. Thinking in
scalability we are thinking in applying the follo
Recently I've wrote few pgSql procedures that generates invoices and
store it in postgres table. Small test has shown that there is
performance problem. I've thought that string operation in pgsql are not
perfect but it has appeared that 90% of time program waste on very
simple update.
Below i
Bastian Voigt wrote:
OK, I'll give that a try. What about pg_autovacuum then? Is it a problem
when two processes try to vacuum the same table in parallel? Or do I
need to deactivate autovacuum altogether?
I was about to say that you can tune pg_autovacuum, but I just checked
your original pos
Peter T. Breuer wrote:
I set up pg to replace a plain gdbm database for my application. But
even running to the same machine, via a unix socket
* the pg database ran 100 times slower
For what operations? Bulk reads? 19-way joins?
Across the net it was
* about 500 to 1000 times slower
Richard Huxton wrote:
I was about to say that you can tune pg_autovacuum, but I just checked
your original post and you're running 8.0.x - not sure about that one.
The system catalog pg_autovacuum which allows finetuning autovacuum at
table level was introduced in 8.1 :-(
You'll have to chec
Bastian Voigt wrote:
Hi *,
for caching large autogenerated XML files, I have created a bytea table
in my database so that the cached files can be used by multiple servers.
There are about 500 rows and 10-20 Updates per minute on the table. The
files stored in the bytea are anything from 10kB t
Bastian Voigt wrote:
Richard Huxton wrote:
I was about to say that you can tune pg_autovacuum, but I just checked
your original post and you're running 8.0.x - not sure about that one.
The system catalog pg_autovacuum which allows finetuning autovacuum at
table level was introduced in 8.1 :-(
No, this did not help. The vacuum process is still running far too long
and makes everything slow. It is even worse than before, cause now the
system is slow almost all the time while when vacuuming only every 3
hours it is only slow once every three hours.
I now did the following. Well,
On Fri, May 25, 2007 at 10:50:58AM +0200, Peter T. Breuer wrote:
> I set up pg to replace a plain gdbm database for my application.
Postgres and gdbm are completely different. You want to rethink your queries
so each does more work, instead of running a zillion of them over the network.
/* Steina
you should first cluster the table on primary key.
The table is probably already bloated from the 3 hr delay it had before.
First
CLUSTER "primary key index name" ON group_fin_account_tst;
Then
vacuum it every 3 minutes.
NB! clustering takes an access exclusive lock on table
Kristo
On 25.05.2007
"Also sprach Kenneth Marshall:"
> > Surprise, ... I got a speed up of hundreds of times. The same application
> > that crawled under my original rgdbm implementation and under PG now
> > maxed out the network bandwidth at close to a full 10Mb/s and 1200
> > pkts/s, at 10% CPU on my 700MHz client,
Bastian Voigt wrote:
No, this did not help. The vacuum process is still running far too long
and makes everything slow. It is even worse than before, cause now the
system is slow almost all the time while when vacuuming only every 3
hours it is only slow once every three hours.
Could you
"Also sprach Richard Huxton:"
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> Peter T. Breuer wrote:
> > I set up pg to replace a plain gdbm database for my application. But
> > even running to the same machine, via a unix socket
> >
> >* the pg database ran 100 times slower
>
> F
Peter T. Breuer wrote:
The only operations being done are simple "find the row with this key",
or "update the row with this key". That's all. The queries are not an
issue (though why the PG thread choose to max out cpu when it gets the
chance to do so through a unix socket, I don't know).
The
Bastian Voigt wrote:
> No, this did not help. The vacuum process is still running far too long
> and makes everything slow. It is even worse than before, cause now the
> system is slow almost all the time while when vacuuming only every 3
> hours it is only slow once every three hours.
>
>
"Also sprach Richard Huxton:"
> I'm not sure you really want a full RDBMS. If you only have a single
> connection and are making basic key-lookup queries then 90% of
> PostgreSQL's code is just getting in your way. Sounds to me like gdbm
Yep - I could happily tell it not to try and compile a sp
Bastian Voigt <[EMAIL PROTECTED]> writes:
> Now my big big problem is that the database gets really really slow
> during these 20 minutes and after the vacuum process is running for a
> short time, many transactions show state "UPDATE waiting" in the process
> list. In my Java application server
Richard Huxton wrote:
Could you check the output of vacuum verbose on that table and see how
much work it's doing? I'd have thought the actual bytea data would be
TOASTed away to a separate table for storage, leaving the vacuum with
very little work to do.
I'm quite new to postgres (actually I
Peter T. Breuer escribió:
> I really think it would be worthwhile getting some developer to tell me
> where the network send is done in PG.
See src/backend/libpq/pqcomm.c (particularly internal_flush()).
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreS
Kristo Kaiv wrote:
you should first cluster the table on primary key.
The table is probably already bloated from the 3 hr delay it had before.
First
CLUSTER "primary key index name" ON group_fin_account_tst;
Then
vacuum it every 3 minutes.
NB! clustering takes an access exclusive lock on table
K
"Peter T. Breuer" <[EMAIL PROTECTED]> writes:
> S ... I took a look at my implementation of remote gdbm, and did
> a very little work to aggregate outgoing transmissions together into
> lumps.
We do that already --- for a simple query/response such as you are
describing, each query cycle will
Peter T. Breuer wrote:
"Also sprach Richard Huxton:"
I'm not sure you really want a full RDBMS. If you only have a single
connection and are making basic key-lookup queries then 90% of
PostgreSQL's code is just getting in your way. Sounds to me like gdbm
Yep - I could happily tell it not to
Michal Szymanski <[EMAIL PROTECTED]> writes:
> CREATE OR REPLACE FUNCTION test()
> RETURNS void AS
> $BODY$
> DECLARE
> BEGIN
> FOR v_i IN 1..4000 LOOP
> UPDATE group_fin_account_tst SET
> credit = v_i
> WHERE group_fin_account_tst_id = 1; -- for real procedure I
>
Arnau <[EMAIL PROTECTED]> writes:
>I have an application that works with multiple customers. Thinking in
> scalability we are thinking in applying the following approaches:
>- Create a separate database instance for each customer.
>- We think that customer's DB will be quite small, ab
On Fri, May 25, 2007 at 09:13:25AM +0100, Richard Huxton wrote:
> [EMAIL PROTECTED] wrote:
> >>And since it's basically impossible to know the selectivity of this kind
> >>of where condition, I doubt the planner would ever realistically want to
> >>choose that plan anyway because of its poor worst-
"Also sprach Alvaro Herrera:"
> > I really think it would be worthwhile getting some developer to tell me
> > where the network send is done in PG.
>
> See src/backend/libpq/pqcomm.c (particularly internal_flush()).
Yes. Thanks. That looks like it. It calls secure_write continually
until the buff
"Peter T. Breuer" <[EMAIL PROTECTED]> writes:
> And definitely all those could be grouped if there are several to do.
Except that in the situation you're describing, there's only a hundred
or two bytes of response to each query, which means that only one send()
will occur anyway. (The flush call
TOASTed means storage outside of the main table. But AFAIK, only rows bigger 2K
are considered for toasting.
Andreas
-- Ursprüngl. Mitteil. --
Betreff:Re: My quick and dirty "solution" (Re: [PERFORM] Performance
Problem with Vacuum of bytea table (PG 8.0.13))
Von:Bastian Voigt <[EMA
"Also sprach Tom Lane:"
> "Peter T. Breuer" <[EMAIL PROTECTED]> writes:
> > And definitely all those could be grouped if there are several to do.
>
> Except that in the situation you're describing, there's only a hundred
> or two bytes of response to each query, which means that only one send()
>
[EMAIL PROTECTED] wrote:
On Fri, May 25, 2007 at 09:13:25AM +0100, Richard Huxton wrote:
[EMAIL PROTECTED] wrote:
And since it's basically impossible to know the selectivity of this kind
of where condition, I doubt the planner would ever realistically want to
choose that plan anyway because of
"Peter T. Breuer" <[EMAIL PROTECTED]> writes:
> "Also sprach Tom Lane:"
>> Except that in the situation you're describing, there's only a hundred
>> or two bytes of response to each query, which means that only one send()
>> will occur anyway. (The flush call comes only when we are done
>> respond
OK - any application that allows user-built queries: foo>
Want another? Any application that has a "search by name" box - users
can (and do) put one letter in and hit enter.
Unfortunately you don't always have control over the selectivity of
queries issued.
-*- HOW TO MAKE A
"Also sprach Tom Lane:"
> > It may still be useful. The kernel won't necessarily send data as you
> > push it down to the network protocols and driver. The driver may decide
> > to wait for more data to accumulate,
>
> No, because we set TCP_NODELAY. Once we've flushed a message to the
That just
On Fri, May 25, 2007 at 04:35:22PM +0100, Richard Huxton wrote:
> >I notice you did not provide a real life example as requested. :-)
> OK - any application that allows user-built queries: foo>
> Want another? Any application that has a "search by name" box - users
> can (and do) put one letter
OK, I'll give that a try. What about pg_autovacuum then? Is it a problem
when two processes try to vacuum the same table in parallel? Or do I
need to deactivate autovacuum altogether?
Try vacuuming every 3 minutes and see what happens.
(Sorry Richard, forgot to reply to the list!)
--
Bastia
PFC wrote:
OK - any application that allows user-built queries: foo>
Want another? Any application that has a "search by name" box - users
can (and do) put one letter in and hit enter.
Unfortunately you don't always have control over the selectivity of
queries issued.
-*- HOW TO MA
Hi Tom,
Arnau <[EMAIL PROTECTED]> writes:
I have an application that works with multiple customers. Thinking in
scalability we are thinking in applying the following approaches:
- Create a separate database instance for each customer.
- We think that customer's DB will be quite smal
[EMAIL PROTECTED] wrote:
I am speaking of contains, as contains is the one that was said to
require a seqscan. I am questioning why it requires a seqscan. The
claim was made that with MVCC, the index is insufficient to check
for visibility and that the table would need to be accessed anyways,
th
[EMAIL PROTECTED] wrote:
On Fri, May 25, 2007 at 04:35:22PM +0100, Richard Huxton wrote:
I notice you did not provide a real life example as requested. :-)
OK - any application that allows user-built queries: foo>
Want another? Any application that has a "search by name" box - users
can (and
PFC wrote:
None of which address the question of what plan PG should produce for:
SELECT * FROM bigtable WHERE foo LIKE 's%'
Ah, this one already uses the btree since the '%' is at the end.
My point is that a search like this will yield too many results to
be useful to the user anyway,
None of which address the question of what plan PG should produce for:
SELECT * FROM bigtable WHERE foo LIKE 's%'
Ah, this one already uses the btree since the '%' is at the end.
My point is that a search like this will yield too many results to be
useful to the user anyway, so optim
"Richard Huxton" <[EMAIL PROTECTED]> writes:
> Now you and I can look at a substring and probably make a good guess how
> common
> it is (assuming we know the targets are British surnames or Japanese towns).
> PG
> needs one number - or rather, it picks one number for each length of
> search-str
Arnau <[EMAIL PROTECTED]> writes:
>> Can you instead run things with one postmaster per machine and one
>> database per customer within that instance? From a performance
>> perspective this is likely to work much better.
>What I meant is just have only one postmaster per server and a lot of
Gregory Stark wrote:
"Richard Huxton" <[EMAIL PROTECTED]> writes:
Now you and I can look at a substring and probably make a good guess how common
it is (assuming we know the targets are British surnames or Japanese towns). PG
needs one number - or rather, it picks one number for each length of
Tom Lane wrote:
Arnau <[EMAIL PROTECTED]> writes:
Can you instead run things with one postmaster per machine and one
database per customer within that instance? From a performance
perspective this is likely to work much better.
What I meant is just have only one postmaster per server and a
Arnau <[EMAIL PROTECTED]> writes:
>The point I'm worried is performance. Do you think the performance
> would be better executing exactly the same queries only adding an extra
> column to all the tables e.g. customer_id, than open a connection to the
> only one customers DB and execute the q
Greetings,
We have two servers running pgsql -- an older server running 8.2.3,
and a newer (far superior) one running 8.2.4. One of our reporting
queries is running painfully slowly on 8.2.4, but it executes in a
reasonable timeframe on 8.2.3. Below, I've included a contrived,
stripped
Lo!
referrer_paths seems to have totally wrong stats. try full analyze on
it.
how many records in total do you have in referrer_paths on 8.2.4 server?
might be just a problem of usage pattern change from old system to
new (1 row vs. 121399 rows) ?
does not seem to be just a plan problem as t
"Also sprach Kenneth Marshall:"
> improvement from coalescing the packets. Good luck in your investigations.
While I am recompiling stuff, just some stats.
Typical network traffic analysis during the PG runs:
Total Packets Processed 493,499
Unicast 100.0% 493,417
Broadca
Dave Pirotte <[EMAIL PROTECTED]> writes:
> We have two servers running pgsql -- an older server running 8.2.3,
> and a newer (far superior) one running 8.2.4. One of our reporting
> queries is running painfully slowly on 8.2.4, but it executes in a
> reasonable timeframe on 8.2.3.
Are you s
On Fri, May 25, 2007 at 03:56:35PM -0400, Tom Lane wrote:
> I looked through the CVS logs and didn't find any planner changes
> between 8.2.3 and 8.2.4 that seem likely to affect your query, so
> I'm thinking it must be a statistical discrepancy.
It looks like the estimated cost is lower for 8.2.4
"Steinar H. Gunderson" <[EMAIL PROTECTED]> writes:
> It looks like the estimated cost is lower for 8.2.4 -- could it be that the
> fact that he's giving it more memory lead to the planner picking a plan that
> happens to be worse?
Offhand I don't think so. More work_mem might make a hash join loo
Thanks for the quick responses. :-) The data is almost identical,
between the two servers: 8.2.3 has 882198 records, 8.2.4 has 893121.
For background, I pg_dump'ed the data into the 8.2.4 server
yesterday, and analyzed with the stats target of 250, then reanalyzed
with target 10. So, t
I have a busy postgresql server running running on a raid1 of 2 15k rpm
scsi drives.
I have been running into the problem of maxed out IO bandwidth. I would
like to convert my raid1 into a raid10 but that would require a full
rebuild which is more downtime than I want so I am looking into other
al
"Also sprach Richard Huxton:"
> > scheme each time, for example! (how that?). I could presumably also
> > help it by preloading the commands I will run and sending over the
> > params only with a "do a no. 17 now!".
>
> PREPARE/EXECUTE (or the equivalent libpq functions).
Yes, thank you. It see
Tom Lane wrote:
Michal Szymanski <[EMAIL PROTECTED]> writes:
CREATE OR REPLACE FUNCTION test()
RETURNS void AS
$BODY$
DECLARE
BEGIN
FOR v_i IN 1..4000 LOOP
UPDATE group_fin_account_tst SET
credit = v_i
WHERE group_fin_account_tst_id = 1; -- for real procedure
Michal Szymanski wrote:
> Tom Lane wrote:
> >(For the record, the reason you see nonlinear degradation is the
> >accumulation of tentatively-dead versions of the row, each of which has
> >to be rechecked by each later update.)
> >
> There is another strange thing. We have two versions of our
[EMAIL PROTECTED] writes:
> The best one I have come up with is moving the xlog/wal (can someone
> confirm whether these are the same thing?) to another physical drive.
Yeah, two names for same thing.
> I also think it may be beneficial to move some indexes to another drive as
> well (same one as
[EMAIL PROTECTED] writes:
> The best one I have come up with is moving the xlog/wal (can someone
> confirm whether these are the same thing?) to another physical drive.
Yeah, two names for same thing.
> I also think it may be beneficial to move some indexes to another drive as
> well (same one as
"Peter T. Breuer" <[EMAIL PROTECTED]> writes:
> But can I prepare a DECLARE x BINARY CURSOR FOR SELECT ... statement?
> The manual seems to say no.
No, you just prepare the SELECT. At the protocol level, DECLARE CURSOR
is a tad useless. You can still fetch the data in binary if you want...
<[EMAIL PROTECTED]> writes:
> Some questions on this:
> 1. Can the database survive loss/corruption of the xlog and indexes in a
> recoverable way? To save money (and because I won't need the throughput as
> much), I am thinking on making this index/wal/xlog drive a single cheap
> sata drive (or m
We're thinking of building some new servers. We bought some a while back that
have ECC (error correcting) RAM, which is absurdly expensive compared to the
same amount of non-ECC RAM. Does anyone have any real-life data about the
error rate of non-ECC RAM, and whether it matters or not? In my
On Fri, May 25, 2007 at 18:45:15 -0700,
Craig James <[EMAIL PROTECTED]> wrote:
> We're thinking of building some new servers. We bought some a while back
> that have ECC (error correcting) RAM, which is absurdly expensive compared
> to the same amount of non-ECC RAM. Does anyone have any real
On Fri, 25 May 2007, Bruno Wolff III wrote:
Wikipedia suggests a rule of thumb of one error per month per gigabyte,
though suggests error rates vary widely. They reference a paper that
should provide you with more background.
The paper I would recommend is
http://www.tezzaron.com/about/paper
Greg Smith <[EMAIL PROTECTED]> writes:
> The paper I would recommend is
> http://www.tezzaron.com/about/papers/soft_errors_1_1_secure.pdf
> which is a summary of many other people's papers, and quite informative.
> I know I had no idea before reading it how much error rates go up with
> increasin
68 matches
Mail list logo
