Return-Path: Delivered-To: apmail-incubator-connectors-user-archive@minotaur.apache.org Received: (qmail 71418 invoked from network); 17 Nov 2010 01:15:37 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 17 Nov 2010 01:15:37 -0000 Received: (qmail 97291 invoked by uid 500); 17 Nov 2010 01:16:09 -0000 Delivered-To: apmail-incubator-connectors-user-archive@incubator.apache.org Received: (qmail 97213 invoked by uid 500); 17 Nov 2010 01:16:09 -0000 Mailing-List: contact connectors-user-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: connectors-user@incubator.apache.org Delivered-To: mailing list connectors-user@incubator.apache.org Received: (qmail 97199 invoked by uid 99); 17 Nov 2010 01:16:08 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 17 Nov 2010 01:16:08 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [199.85.212.11] (HELO jdz1.dailyrazor.com) (199.85.212.11) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 17 Nov 2010 01:16:01 +0000 Received: from 72-48-50-27.dyn.grandenetworks.net ([72.48.50.27]:62418 helo=FileServer) by jdz1.dailyrazor.com with esmtpa (Exim 4.69) (envelope-from ) id 1PIWcc-0001xw-Sg for connectors-user@incubator.apache.org; Tue, 16 Nov 2010 19:15:39 -0600 From: "Farzad Valad" To: References: <47af57cfac59e9ddb55a3ac9ca0fc6ad.squirrel@www.farzad.net> <007301cb8538$40ca65a0$c25f30e0$@net> <002901cb859a$ee51ff00$caf5fd00$@net> <2391931c3ae7390283eee4301418b03e.squirrel@www.farzad.net> In-Reply-To: Subject: RE: Performance Problems Continue Date: Tue, 16 Nov 2010 19:15:34 -0600 Message-ID: <003c01cb85f4$f002f070$d008d150$@net> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcuF48idqYeIIACNQyefsvSoa99rBwAER+1g Content-Language: en-us X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - jdz1.dailyrazor.com X-AntiAbuse: Original Domain - incubator.apache.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - farzad.net X-Virus-Checked: Checked by ClamAV on apache.org Sounds perfect! -----Original Message----- From: Karl Wright [mailto:daddywri@gmail.com]=20 Sent: Tuesday, November 16, 2010 5:12 PM To: connectors-user@incubator.apache.org Subject: Re: Performance Problems Continue Turns out my memory is incorrect - the postgresql version that I've been using is 8.4.4, not 8.3.9. So there are a lot of possibilities here. What I'm going to try is to install the latest 8.4.5 on a different system, and do a large crawl. Then I'll see if the plan is OK. If it is OK, then I'll ask you to upgrade to that version as well, and see how you do. Does this sound reasonable? Karl On Tue, Nov 16, 2010 at 4:23 PM, Karl Wright wrote: > I'm concerned that if I install postgresql 8.3.12, and do not > reproduce your problem, we'll have learned nothing. > I'll look around to see if I have the 8.3.9 installer still around... > > Karl > > > On Tue, Nov 16, 2010 at 2:37 PM, =A0 wrote: >> Can't find the windows binaries on the web. =A0I found this link, but = it >> keeps saying file not found regardless of the mirror server I use. >> >> http://wwwmaster.postgresql.org/download/mirrors-ftp/binary/v8.3.9/win32/= pos tgresql-8.3.9-1.zip >> >> Seems like the fastest path would be for you to go up to .12, = especially >> that is the version anyone can get their hands on. =A0Do you have the >> windows binaries or installer? >> >> Thanks,Farzad. >> >> >> Hi Farzad, >> The index it should be using but isn't is: >> >> public =A0 =A0 | jobqueue =A0| i1289847375558 | =A0 =A0 =A0 =A0 =A0 = =A0| CREATE UNIQUE >> INDEX i1289847375558 ON jobqueue USING btree (dochash, jobid) >> >> Both the dochash value and the jobid value are specified for the >> jobqueue table in that query, so there's no reason it should not use >> this index. =A0It's either failed to consider it at all, or it has >> concluded that it's more efficient to scan the entire jobqueue table >> rather than using the index for the lookup. >> >> FWIW, I did run some explains on similar queries on my test system >> this morning before I left for work, and the plans for those involved >> something called an "index semi-join" which apparently means it scans >> both the jobqueue index and the carrydown index at the same time. = =A0If >> I recall correctly, my version of PostgreSQL was 8.3.9. =A0Since = yours >> is 8.3.12 it could well be that something was broken in PostgreSQL >> between releases. =A0If you have time, you might try to see if an >> earlier version of postgresql exhibits the same behavior for you. = =A0If >> that works properly, the next step would be to create a ticket = against >> PostgreSQL itself - or maybe there already is one. >> >> I expect that any of the many PostgreSQL books out there would have >> enough information in it to bring you up to speed. =A0The skill set = for >> optimizing queries is also portable to other databases such as = Oracle, >> if you care. >> >> Karl >> >> >> On Tue, Nov 16, 2010 at 12:03 PM, =A0 wrote: >>> Found a query statement to pull the index data. =A0Is this what you = are >>> looking for? =A0On a separate note, I'm a bit over my head in terms = of >>> understanding, any recommendation of a site/sites or book to read to >>> catch >>> up with needed db concepts? >>> >>> dbname=3D> select * from pg_indexes where tablename=3D'jobqueue'; >>> =A0schemaname | tablename | =A0 indexname =A0 =A0| tablespace | >>> =A0 =A0 =A0 =A0 =A0 =A0 =A0indexdef >>> ------------+-----------+----------------+------------+------------------= --- ---- >>> ------------------------------------------------------------- >>> =A0public =A0 =A0 | jobqueue =A0| jobqueue_pkey =A0| =A0 =A0 =A0 =A0 = =A0 =A0| CREATE UNIQUE >>> INDEX jobq >>> ueue_pkey ON jobqueue USING btree (id) >>> =A0public =A0 =A0 | jobqueue =A0| i1289847375565 | =A0 =A0 =A0 =A0 = =A0 =A0| CREATE INDEX >>> i1289847375 >>> 565 ON jobqueue USING btree (jobid, status) >>> =A0public =A0 =A0 | jobqueue =A0| i1289847375564 | =A0 =A0 =A0 =A0 = =A0 =A0| CREATE INDEX >>> i1289847375 >>> 564 ON jobqueue USING btree (jobid, isseed) >>> =A0public =A0 =A0 | jobqueue =A0| i1289847375563 | =A0 =A0 =A0 =A0 = =A0 =A0| CREATE INDEX >>> i1289847375 >>> 563 ON jobqueue USING btree (jobid, dochash, status) >>> =A0public =A0 =A0 | jobqueue =A0| i1289847375562 | =A0 =A0 =A0 =A0 = =A0 =A0| CREATE INDEX >>> i1289847375 >>> 562 ON jobqueue USING btree (status) >>> =A0public =A0 =A0 | jobqueue =A0| i1289847375561 | =A0 =A0 =A0 =A0 = =A0 =A0| CREATE INDEX >>> i1289847375 >>> 561 ON jobqueue USING btree (checkaction, checktime, status) >>> =A0public =A0 =A0 | jobqueue =A0| i1289847375560 | =A0 =A0 =A0 =A0 = =A0 =A0| CREATE INDEX >>> i1289847375 >>> 560 ON jobqueue USING btree (priorityset, status) >>> =A0public =A0 =A0 | jobqueue =A0| i1289847375559 | =A0 =A0 =A0 =A0 = =A0 =A0| CREATE INDEX >>> i1289847375 >>> 559 ON jobqueue USING btree (docpriority) >>> =A0public =A0 =A0 | jobqueue =A0| i1289847375558 | =A0 =A0 =A0 =A0 = =A0 =A0| CREATE UNIQUE >>> INDEX i128 >>> 9847375558 ON jobqueue USING btree (dochash, jobid) >>> (9 rows) >>> >>> dbname=3D> select * from pg_indexes where tablename=3D'carrydown'; >>> =A0schemaname | tablename | =A0 indexname =A0 =A0| tablespace | >>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 indexdef >>> >>> ------------+-----------+----------------+------------+------------------= --- ---- >>> -------------------------------------------------------------------------= --- ---- >>> ---------------- >>> =A0public =A0 =A0 | carrydown | i1289847375547 | =A0 =A0 =A0 =A0 =A0 = =A0| CREATE INDEX >>> i1289847375 >>> 547 ON carrydown USING btree (jobid, parentidhash) >>> =A0public =A0 =A0 | carrydown | i1289847375546 | =A0 =A0 =A0 =A0 =A0 = =A0| CREATE INDEX >>> i1289847375 >>> 546 ON carrydown USING btree (jobid, childidhash, dataname) >>> =A0public =A0 =A0 | carrydown | i1289847375545 | =A0 =A0 =A0 =A0 =A0 = =A0| CREATE INDEX >>> i1289847375 >>> 545 ON carrydown USING btree (jobid, childidhash, isnew) >>> =A0public =A0 =A0 | carrydown | i1289847375544 | =A0 =A0 =A0 =A0 =A0 = =A0| CREATE UNIQUE >>> INDEX i128 >>> 9847375544 ON carrydown USING btree (jobid, parentidhash, = childidhash, >>> dataname, >>> =A0datavaluehash) >>> (4 rows) >>> >>> dbname=3D> >>> >>> Farzad. >>> >>> >>> psql usually displays the indexes for a table when you dt the table. >>> These are not appearing in your postgresql version. =A0But it's = clear >>> they are there from the plan. >>> >>> The jobqueue table is being sequentially scanned *despite* the >>> probable fact that there's a reasonable, usable index on it that it >>> should be using. =A0In short, this looks like a PostgreSQL bug to = me. >>> >>> I'm away from my test machine right now, but I'd love to see the >>> indexes on the jobqueue table that you have. =A0There may be a new >>> command in psql that my version of PostgreSQL doesn't have that = prints >>> the indexes for a table, if they took it out of dt. >>> >>> If the proper index is there, then all we can do is try various = forms >>> of the query to see if we can dodge the PostgreSQL bug. >>> >>> Karl >>> >>> >>> >>> On Tue, Nov 16, 2010 at 9:31 AM, Farzad Valad = wrote: >>>> Here is the output: >>>> >>>> >>>> C:\Program Files (x86)\PostgreSQL\8.3\bin>psql -U manifoldcf dbname >>>> Password for user manifoldcf: >>>> Welcome to psql 8.3.12, the PostgreSQL interactive terminal. >>>> >>>> Type: =A0\copyright for distribution terms >>>> =A0 =A0 =A0 \h for help with SQL commands >>>> =A0 =A0 =A0 \? for help with psql commands >>>> =A0 =A0 =A0 \g or terminate with semicolon to execute query >>>> =A0 =A0 =A0 \q to quit >>>> >>>> Warning: Console code page (437) differs from Windows code page = (1252) >>>> =A0 =A0 =A0 =A0 8-bit characters might not work correctly. See psql = reference >>>> =A0 =A0 =A0 =A0 page "Notes for Windows users" for details. >>>> >>>> dbname=3D> \dt jobqueue; >>>> =A0 =A0 =A0 =A0 =A0 List of relations >>>> =A0Schema | =A0 Name =A0 | Type =A0| =A0 Owner >>>> --------+----------+-------+------------ >>>> =A0public | jobqueue | table | manifoldcf >>>> (1 row) >>>> >>>> dbname=3D> \dt carrydown; >>>> =A0 =A0 =A0 =A0 =A0 =A0List of relations >>>> =A0Schema | =A0 Name =A0 =A0| Type =A0| =A0 Owner >>>> --------+-----------+-------+------------ >>>> =A0public | carrydown | table | manifoldcf >>>> (1 row) >>>> >>>> dbname=3D> select count(*) from carrydown; >>>> =A0count >>>> ------- >>>> =A0 =A0 0 >>>> (1 row) >>>> >>>> dbname=3D> select distinct jobid from carrydown; >>>> =A0jobid >>>> ------- >>>> (0 rows) >>>> >>>> dbname=3D> explain SELECT t0.id,t0.dochash,t0.docid FROM jobqueue = t0 >>>> WHERE >>>> EXISTS( >>>> SELECT 'x' FROM carrydown t1 WHERE parentidhash IN >>>> dbname(> ('B0C2E57717919821A7C32D9F92F9F297155B727F') AND >>>> t1.childidhash=3Dt0.doch >>>> ash AND t0.jobid=3D1289847478234 AND >>>> dbname(> t1.jobid=3D1289847478234 AND t1.isnew=3D'B'); >>>> >>>> =A0 =A0 QUERY PLAN >>>> >>>> -------------------------------------------------------------------------= --- >>>> ---- >>>> -------------------------------------------------------------------------= --- >>>> ---- >>>> -------------------- >>>> =A0Seq Scan on jobqueue t0 =A0(cost=3D0.00..2000133.07 = rows=3D118994 width=3D158) >>>> =A0 Filter: (subplan) >>>> =A0 SubPlan >>>> =A0 =A0 -> =A0Result =A0(cost=3D0.00..8.28 rows=3D1 width=3D0) >>>> =A0 =A0 =A0 =A0 =A0 One-Time Filter: ($1 =3D 1289847478234::bigint) >>>> =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using i1289847375544 on = carrydown t1 >>>> (cost=3D0.00..8.28 >>>> =A0rows=3D1 width=3D0) >>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((jobid =3D = 1289847478234::bigint) AND >>>> ((parentidhash >>>> )::text =3D 'B0C2E57717919821A7C32D9F92F9F297155B727F'::text) AND >>>> ((childidhash):: >>>> text =3D ($0)::text)) >>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (isnew =3D 'B'::bpchar) >>>> (8 rows) >>>> >>>> dbname=3D> analyze carrydown; >>>> ANALYZE >>>> dbname=3D> analyze jobqueue; >>>> ANALYZE >>>> dbname=3D> explain SELECT t0.id,t0.dochash,t0.docid FROM jobqueue = t0 >>>> WHERE >>>> dbname-> EXISTS(SELECT 'x' FROM carrydown t1 WHERE parentidhash IN >>>> dbname(> ('B0C2E57717919821A7C32D9F92F9F297155B727F') AND >>>> dbname(> t1.childidhash=3Dt0.dochash AND t0.jobid=3D1289847478234 = AND >>>> dbname(> t1.jobid=3D1289847478234 AND t1.isnew=3D'B'); >>>> >>>> =A0 =A0 QUERY PLAN >>>> >>>> -------------------------------------------------------------------------= --- >>>> ---- >>>> -------------------------------------------------------------------------= --- >>>> ---- >>>> -------------------- >>>> =A0Seq Scan on jobqueue t0 =A0(cost=3D0.00..2091946.86 = rows=3D124532 width=3D158) >>>> =A0 Filter: (subplan) >>>> =A0 SubPlan >>>> =A0 =A0 -> =A0Result =A0(cost=3D0.00..8.28 rows=3D1 width=3D0) >>>> =A0 =A0 =A0 =A0 =A0 One-Time Filter: ($1 =3D 1289847478234::bigint) >>>> =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using i1289847375544 on = carrydown t1 >>>> (cost=3D0.00..8.28 >>>> =A0rows=3D1 width=3D0) >>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((jobid =3D = 1289847478234::bigint) AND >>>> ((parentidhash >>>> )::text =3D 'B0C2E57717919821A7C32D9F92F9F297155B727F'::text) AND >>>> ((childidhash):: >>>> text =3D ($0)::text)) >>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (isnew =3D 'B'::bpchar) >>>> (8 rows) >>>> >>>> dbname=3D> >>>> >>>> -----Original Message----- >>>> From: Karl Wright [mailto:daddywri@gmail.com] >>>> Sent: Tuesday, November 16, 2010 4:21 AM >>>> To: connectors-user@incubator.apache.org >>>> Subject: Re: Performance Problems Continue >>>> >>>> As I suspected, the plan it is generating is crap: >>>> >>>> Found a query that took more than a minute (62494 ms): [SELECT >>>> t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' >>>> FROM carrydown t1 WHERE parentidhash IN (?) AND >>>> t1.childidhash=3Dt0.dochash AND t0.jobid=3D? AND t1.jobid=3D? AND >>>> t1.isnew=3D?)] >>>> =A0Parameter 0: 'B0C2E57717919821A7C32D9F92F9F297155B727F' >>>> =A0Parameter 1: '1289847478234' >>>> =A0Parameter 2: '1289847478234' >>>> =A0Parameter 3: 'B' >>>> =A0Plan: Seq Scan on jobqueue t0 =A0(cost=3D0.00..1332448.02 = rows=3D79465 >>>> width=3D152) >>>> =A0Plan: =A0 Filter: (subplan) >>>> =A0Plan: =A0 SubPlan >>>> =A0Plan: =A0 =A0 -> =A0Result =A0(cost=3D0.00..8.28 rows=3D1 = width=3D0) >>>> =A0Plan: =A0 =A0 =A0 =A0 =A0 One-Time Filter: ($1 =3D = 1289847478234::bigint) >>>> =A0Plan: =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using i1289847375544 = on carrydown t1 >>>> (cost=3D0.00..8.28 rows=3D1 width=3D0) >>>> =A0Plan: =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((jobid =3D = 1289847478234::bigint) >>>> AND ((parentidhash)::text =3D >>>> 'B0C2E57717919821A7C32D9F92F9F297155B727F'::text) AND >>>> ((childidhash)::text =3D ($0)::text)) >>>> =A0Plan: =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (isnew =3D = 'B'::bpchar) >>>> >>>> >>>> >>>> ... which means that it is doing a sequential scan on the jobqueue >>>> table (large) and an index scan for each row on the carrydown table >>>> (which should be empty), rather than the other way round. =A0There = is a >>>> carrydown index that is should be using instead, which it is >>>> completely ignoring, which is all the more weird. >>>> >>>> Can you do the following: >>>> >>>> (1) Under the postgresql bin directory, run psql -U manifoldcf = dbname >>>> (2) If the defaults are in place, password should be = "local_pg_passwd" >>>> (3) Run the following commands, and cut/paste the answers for me: >>>> >>>> \dt jobqueue; >>>> \dt carrydown; >>>> select count(*) from carrydown; >>>> select distinct jobid from carrydown; >>>> explain SELECT t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE >>>> EXISTS(SELECT 'x' FROM carrydown t1 WHERE parentidhash IN >>>> ('B0C2E57717919821A7C32D9F92F9F297155B727F') AND >>>> t1.childidhash=3Dt0.dochash AND t0.jobid=3D1289847478234 AND >>>> t1.jobid=3D1289847478234 AND t1.isnew=3D'B'); >>>> >>>> Once you have done that, please do the following: >>>> >>>> analyze carrydown; >>>> analyze jobqueue; >>>> >>>> Then, try the explain again. and send me that output too: >>>> >>>> explain SELECT t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE >>>> EXISTS(SELECT 'x' FROM carrydown t1 WHERE parentidhash IN >>>> ('B0C2E57717919821A7C32D9F92F9F297155B727F') AND >>>> t1.childidhash=3Dt0.dochash AND t0.jobid=3D1289847478234 AND >>>> t1.jobid=3D1289847478234 AND t1.isnew=3D'B'); >>>> >>>> >>>> I've tried the same commands on my system, but they show reasonable >>>> plans. =A0PostgreSQL, like all databases, develops its plans based = on >>>> statistics it keeps for the tables in question. =A0The analyze = command >>>> tells it to update those statistics, which in theory should make >>>> PostgreSQL immediately thereafter use a correct plan. =A0However, = there >>>> may be great sensitivity to incorrect statistics for some sorts of >>>> query, depending on the conditions, so a small number of = accumulated >>>> changes can cause it to start to make huge errors. =A0I am trying = to >>>> determine if that's what is happening, and what those conditions = are >>>> in your case. >>>> >>>> Thanks, >>>> Karl >>>> >>>> >>>> On Mon, Nov 15, 2010 at 9:44 PM, Farzad Valad = wrote: >>>>> 1. Using 8.3.12, gave up on 9.x >>>>> 2. Yes, I do. =A0Attached the log file for this crawl >>>>> 3. Using Null Output and File System input, not using RSS >>>>> >>>> >>>> >>> >>> >>> >>> >> >> >> >> >