Re: [PERFORM] DB is slow until DB is reloaded

On Mon, 2010-01-04 at 20:02 -0800, Craig James wrote: +Madison Kelly wrote: You are right, autovacuum is not running after all. From your comment, I am wondering if you'd recommend I turn it on or not? If so, given that I doubt I will upgrade any time soon, how would I enable it? I

Re: [PERFORM] forced sequential scan when condition has current_user

-Mensaje original- De: Keresztury Balázs hi, just a small question: is it normal that PostgreSQL 8.4.1 always uses sequential scanning on any table when there is a condition having the constant current_user? Of course there is a btree index set on that table, but the DBMS

Re: [PERFORM] pg_connect takes 3.0 seconds

On 1/4/2010 8:12 PM, Dmitri Girski wrote: Hi everybody, I am running a PostgreSQL server 8.3.5 with a pretty much standard config. The web application server which runs Apache 1.3/PHP2.9 has an intermittent problem: pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045.

Re: [PERFORM] query looping?

On Mon, Jan 4, 2010 at 5:24 PM, Brian Cox brian@ca.com wrote: On 01/04/2010 04:53 PM, Robert Haas [robertmh...@gmail.com] wrote: PREPARE foo AS the query, with the $x entries still in there EXPLAIN EXECUTE foo(the values); Thanks for the response. Results below. Brian cemdb= prepare

Re: [PERFORM] query looping?

SELECT SUM(1) FROM ts_stats_transetgroup_user_weekly b WHERE ts_interval_start_time [value] AND ts_interval_start_time [value]; ...and similarly for the bitmap index scan. cemdb= SELECT SUM(1) FROM ts_stats_transetgroup_user_weekly b WHERE ts_interval_start_time = '2009-12-28' AND

Re: [PERFORM] pg_connect takes 3.0 seconds

Delays that are almost exactly 3 seconds over a network are almost always some sort of network configuration issue. Inside a datacenter, mis-configured load balancers or routers can cause low level network issues that result in intermittent network delays of exactly 3 seconds (a loop in a routing

Re: [PERFORM] DB is slow until DB is reloaded

CLUSTER also does *nothing at all* to a table unless you have chosen an index to CLUSTER on. Its not as simple as switching from VACUUM or VACUUM FULL to CLUSTER. Does CLUSTER also REINDEX? I seem to recall reducing the size of my indexes by REINDEXing after a CLUSTER, but it was a while ago

Re: [PERFORM] DB is slow until DB is reloaded

Scott Carey wrote: CLUSTER also does *nothing at all* to a table unless you have chosen an index to CLUSTER on. Its not as simple as switching from VACUUM or VACUUM FULL to CLUSTER. Does CLUSTER also REINDEX? I seem to recall reducing the size of my indexes by REINDEXing after a

Re: [PERFORM] query looping?

On Tue, Jan 5, 2010 at 4:33 PM, Brian Cox brian@ca.com wrote: comparing this to the 1st explain foo output shows some minor differences in row estimates -- but nothing, I assume, that could explain the huge time difference. Of course, the 1st plan may not (and probably? wasn't) the plan

Re: [PERFORM] pg_connect takes 3.0 seconds

Hi Andy, I tried 2 connections strings: - server name (DB1), which is listed in all machines hosts files. - ip address. There is no difference in both methods, still I have 5-7 pg_connects which last around 3 seconds. Cheers, Dmitri. On Wed, Jan 6, 2010 at 2:03 AM, Andy Colson

Re: [PERFORM] pg_connect takes 3.0 seconds

Hi Scott, Thank you pointers, I've spoken to the network guy, he will help to monitor connections on the firewall. On the other hand, if I use ip addresses this should not attract any possible issues with DNS, right? Thanks! Dmitri. On Wed, Jan 6, 2010 at 9:32 AM, Scott Carey

Re: [PERFORM] pg_connect takes 3.0 seconds

Thank you for reply , Andy! I tried both cases: server name which is listed in hosts file and ip address ( 192.168.2.2) - no difference so far. Cheers, Dmitri. On Wed, Jan 6, 2010 at 2:03 AM, Andy Colson a...@squeakycode.net wrote: On 1/4/2010 8:12 PM, Dmitri Girski wrote: Hi everybody, I

Re: [PERFORM] pg_connect takes 3.0 seconds

Hi Tom, The timing is around 3.0 seconds Time=3.0037 Time=3.4038 Time=3.0038 Time=3.004 Time=3.2037 Time=3.0039 Time=3.0034 Time=3.0034 Time=3.2039 Time=3.0044 Time=3.8044 Time=3.2034 I don't think that it could relate to DNS problem as I tried 2 methods which does not use name resolution (

Re: [PERFORM] pg_connect takes 3.0 seconds

Hi Greg, Thank you for idea, reading about checkpints tuning was very useful. I had a checkpoints logging turned on. I studied a couple of days logs and I there is no clear dependency on checkpoint write. Sometimes it is within a vicinity of 3 seconds CONNECT, sometimes well off it. Also the

Re: [PERFORM] pg_connect takes 3.0 seconds

On Tue, Jan 5, 2010 at 8:49 PM, Dmitri Girski mite...@gmail.com wrote: Hi Tom, The timing is around 3.0 seconds Time=3.0037 Time=3.4038 Time=3.0038 Time=3.004 Time=3.2037 Time=3.0039 Time=3.0034 Time=3.0034 Time=3.2039 Time=3.0044 Time=3.8044 Time=3.2034 I don't think that it could

Re: [PERFORM] pg_connect takes 3.0 seconds

Dmitri Girski wrote: Hi Andy, I tried 2 connections strings: - server name (DB1), which is listed in all machines hosts files. - ip address. There is no difference in both methods, still I have 5-7 pg_connects which last around 3 seconds. Don't rule out reverse DNS issues (such as a