Thread: Re: Hanging queries on dual CPU windows

> > > >  I dunno
> > > >
> > > > > if you've got anything gdb-equivalent under Windows,
> but that's
> > > > > the first thing I'd be interested in ...
> > > >
> > > > Here ya go:
> > > >
> > > > http://www.devisser-siderius.com/stack1.jpg
> > > > http://www.devisser-siderius.com/stack2.jpg
> > > > http://www.devisser-siderius.com/stack3.jpg
> > > >
> > > > There are three threads in the process. I guess thread 1
> > > > (stack1.jpg) is the most interesting.
> > > >
> > > > I also noted that cranking up concurrency in my app
> reproduces the
> > > > problem in about 4 minutes ;-)
> >
> > Just reproduced again.
> >
> > > Actually, stack2 looks very interesting. Does it "stay stuck" in
> > > pg_queue_signal? That's really not supposed to happen.
> >
> > Yes it does.
>
> An update on that: There is actually *two* processes in this
> state, both hanging in pg_queue_signal. I've looked at the
> source of that, and the obvious candidate for hanging is
> EnterCriticalSection. I also found this:
>
> http://blogs.msdn.com/larryosterman/archive/2005/03/02/383685.aspx
>
> where they say:
>
> "
> In addition, for Windows 2003, SP1, the EnterCriticalSection
> API has a subtle change that's intended tor resolve many of
> the lock convoy issues.  Before
> Win2003 SP1, if 10 threads were blocked on
> EnterCriticalSection and all 10 threads had the same
> priority, then EnterCriticalSection would service those
> threads in a FIFO (first -in, first-out) basis.  Starting in
> Windows 2003 SP1, the EnterCriticalSection will wake up a
> random thread from the waiting threads.  If all the threads
> are doing the same thing (like a thread pool) this won't make
> much of a difference, but if the different threads are doing
> different work (like the critical section protecting a widely
> accessed object), this will go a long way towards removing
> lock convoy semantics.
> "
>
> Could it be they broke it when they did that????

In theory, yes, but it still seems a bit far fetched :-(

If you have the env to rebuild, can you try changing the order of the lines:
    ResetEvent(pgwin32_signal_event);
    LeaveCriticalSection(&pg_signal_crit_sec);

in backend/port/win32/signal.c


And if not, can you also try disabling the stats collector and see if that makes a difference. (Could be a
workaround..)


//Magnus

On Friday 10 March 2006 10:11, Magnus Hagander wrote:
> > Could it be they broke it when they did that????
>
> In theory, yes, but it still seems a bit far fetched :-(

Well, I rolled back SP1 and am running my test again. Looking much better,
hasn't locked up in 45mins now, whereas before it would lock up within 5mins.

So I think they broke something.

jan

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

Hello.

Recently I've discovered an interesting thing (Postgres version 8.1.3):

example table:

CREATE TABLE test (
 id INT,
 name TEXT,
 comment TEXT,
 phone TEXT,
 visible BOOLEAN
);

then,
CREATE INDEX i1 ON test(phone);
CREATE INDEX i2 ON test(phone, visible);
CREATE INDEX i3 ON test(phone, visible) WHERE visible;

then insert lot's of data
and try to execute query like:

SELECT * FROM test WHERE phone='12345' AND visible;

uses index i1, and filters all visible fields.
When I drop index i1, postgres starts to use index i2
and the query began to work much more faster.

When I drop index i2, postgres uses index i3 which is faster than i2 ofcourse.

I've noticed that planner estimated all queries for all three cases with the same cost.
So, is it a planner bad estimate or what?

--
Evgeny Gridasov
Software Engineer
I-Free, Russia

Evgeny Gridasov <eugrid@fpm.kubsu.ru> writes:
> Recently I've discovered an interesting thing (Postgres version 8.1.3):

Have you ANALYZEd the table since loading it?  What fraction of the rows
have visible = true?

            regards, tom lane

Tom,

ofcourse I've analyzed it.
visible is true for about 0.3% of all rows.
testing table contains about 300,000-500,000 rows.

On Fri, 10 Mar 2006 12:09:19 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Evgeny Gridasov <eugrid@fpm.kubsu.ru> writes:
> > Recently I've discovered an interesting thing (Postgres version 8.1.3):
>
> Have you ANALYZEd the table since loading it?  What fraction of the rows
> have visible = true?

--
Evgeny Gridasov
Software Engineer
I-Free, Russia

Evgeny Gridasov <eugrid@fpm.kubsu.ru> writes:
> ofcourse I've analyzed it.
> visible is true for about 0.3% of all rows.

Well, I get an indexscan on i3 ... there isn't going to be any
strong reason for the planner to prefer i2 over i1, given that
the phone column is probably near-unique and the i2 index will be
bigger than i1.  I don't see why it wouldn't like i3 though.  Could
we see the EXPLAIN ANALYZE results with and without i3?

regression=# CREATE TABLE test (phone TEXT, visible BOOLEAN);
CREATE TABLE
regression=# insert into test select (z/2)::text,(z%1000)<=3 from generate_series(1,300000) z;
INSERT 0 300000
regression=# CREATE INDEX i1 ON test(phone);
CREATE INDEX
regression=# CREATE INDEX i2 ON test(phone, visible);
CREATE INDEX
regression=# CREATE INDEX i3 ON test(phone, visible) WHERE visible;
CREATE INDEX
regression=# analyze test;
ANALYZE
regression=# explain SELECT * FROM test WHERE phone='12345' AND visible;
                           QUERY PLAN
----------------------------------------------------------------
 Index Scan using i3 on test  (cost=0.00..5.82 rows=1 width=10)
   Index Cond: ((phone = '12345'::text) AND (visible = true))
(2 rows)

regression=# drop index i3;
DROP INDEX
regression=# explain SELECT * FROM test WHERE phone='12345' AND visible;
                           QUERY PLAN
----------------------------------------------------------------
 Index Scan using i2 on test  (cost=0.00..5.82 rows=1 width=10)
   Index Cond: ((phone = '12345'::text) AND (visible = true))
   Filter: visible
(3 rows)

regression=#

            regards, tom lane