Обсуждение: who is best for support?

On Tue, 2005-02-15 at 14:12, Joel Fradkin wrote:
> Hi,
>
>
>
> I am trying to get postgres working on a Dell and am finidng my SQL
> queries run incrediably slow.
>
> I have tried to adjust cache and buffers, etc with no luck.
>
> Any advice on who would be best for help either it’s a config issue or
> a query issue etc.
>
>
>
> I been trying for a few weeks to get up to speed and diagnose, but I
> am running out of time.

Is this a single thread running slow, or poor performance under heavy
parallel access?

What drive controller do you have?  The stock adaptec RAID controllers
are pretty pokey (the 3I ones)  But the megaraid add on (4C) seem pretty
fast.

Is the load transactional or analytical?

Generally throwing more drives at the problem in a large RAID array.

Having lots of ram (and letting the kernel manage most of it) is a good
idea.

Have you posted your postgresql.conf to the list?

Lastly, you might want to take this to the performance list, as that's
where most of the people who know about performance tuning hang out.

On Tue, 15 Feb 2005, Joel Fradkin wrote:

> Hi,
>
>
>
> I am trying to get postgres working on a Dell and am finidng my SQL queries
> run incrediably slow.
>
> I have tried to adjust cache and buffers, etc with no luck.
>
> Any advice on who would be best for help either it's a config issue or a
> query issue etc.

Us? :)  Can you provide some more details?  Hardware configuration, type
of query, operating system?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

I think Joel may be asking for a paid, dedicated consultant.  Could be
wrong, but I know you folks are out there.  --Rick



                   
                      "Marc G. Fournier"
                   
                      <scrappy@postgresql.o        To:       Joel Fradkin <jfradkin@wazagua.com>
                   
                      rg>                          cc:       pgsql-admin@postgresql.org
                   
                      Sent by:                     Subject:  Re: [ADMIN] who is best for support?
                   
                      pgsql-admin-owner@pos
                   
                      tgresql.org
                   

                   

                   
                      02/15/2005 03:36 PM
                   

                   

                   




On Tue, 15 Feb 2005, Joel Fradkin wrote:

> Hi,
>
>
>
> I am trying to get postgres working on a Dell and am finidng my SQL
queries
> run incrediably slow.
>
> I have tried to adjust cache and buffers, etc with no luck.
>
> Any advice on who would be best for help either it's a config issue or a
> query issue etc.

Us? :)  Can you provide some more details?  Hardware configuration, type
of query, operating system?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Us? :)  Can you provide some more details?

Hardware configuration,
Dell 6560 4 processor, 8 gig memory, I think I have Postgres on two raids
separate from the OS (pgsql is a raid 10 4 10kdrive array) and /wal is 2 15k
drives). I am running postgres 8 and am using the conf in
/var/lib/pgslq/data to point the data at /pgsql and a link to point the /wal
I set up inside the /pgsql/data folder to /wal.

type of query,

I have cut out most of the query but even the first join makes it go to 27
seconds. In MSSQL with the first two left outers it takes 3 seconds.

explain analyze
SELECT /*(tblaudit.name::text || ' '::text) || tblaudit.version::text AS
audit, tblaudit.active, "substring"((tblsections.section::text || ' '::text)
|| tblsections.sectionname::text, 1, 105) AS section, tblauditstatus.value
AS status, COALESCE(tblaudittypes.value, 'monthly'::character varying) AS
audittype, tblresponseheader.clientnum, tblresponseheader.auditid,
tblresponseheader.responseid, tblresponseheader.division,
tblresponseheader.auditnum, tblresponseheader.customauditnum,
tblresponseheader.dateaudittaken, tblresponseheader.createdby,
to_char(tblresponseheader.dateaudittaken, 'yyyy'::text) AS "year",
to_char(tblresponseheader.dateaudittaken, 'q'::text) AS quarter,
to_char(tblresponseheader.dateaudittaken, 'MM'::text) AS "month",
to_char(tblresponseheader.dateaudittaken, 'D'::text) AS weekday,
to_char(tblresponseheader.dateaudittaken, 'WW'::text) AS week,
to_char(tblresponseheader.dateaudittaken, 'HH24:MI'::text) AS "time",
tbluser.completename AS keyedby, tblresponseheader.datecompleted,
tblresponseheader.dateauditkeyed, tblresponseheader.datekeyingcomplete,
tblresponseheader.pointsavailable AS auditpointsavailable,
tblresponseheader.pointsscored AS auditpointsscored,
        CASE tblresponseheader.pointsavailable
            WHEN 0 THEN 'na'::text
            ELSE COALESCE(to_char(tblresponseheader.pointsscored /
tblresponseheader.pointsavailable * 100::numeric, '99999999'::text),
'na'::text)
        END AS myaudittotalscore,*/ tblresponsesection.sectionid /*,
tblresponsesection.pointsavailable AS sectionpointsavailable,
tblresponsesection.pointsscored AS sectionpointsscored,
tblresponsesection.comments AS sectioncomments, tblresponse_line.questionid,
COALESCE(tblresponse_line.pointsscored, 0::numeric) AS pointsscored,
COALESCE(tblresponse_line.pointsavailable, 0::numeric) AS
questionpointsavailable, tbllocation.locationnum, tbllocation.name AS store,
tblregion.regionnum, tblregion.regionname AS region,
tbldistrict.districtnum, tbldistrict.districtname AS district,
tblresponse_line.y_n, tblresponse_line.answerid,
COALESCE("substring"(tblresponse_line.text_response, 1, 4048), 'na'::text)
AS text_response, tblanswers.answer, tblanswers.answertext,
tblanswers.answerdisplay, "substring"(tblquestions.question::text, 1, 105)
AS question, "substring"(tblquestions.questiondisplay::text, 1, 105) AS
questiondisplay, "substring"(tblquestions.qdescr::text, 1, 105) AS qdescr,
"substring"(tblquestions.qtext::text, 1, 105) AS qtext, tblquestions.qtext
AS qtextlong
*/
   FROM tblresponsesection
 /*  JOIN tblresponse_line ON tblresponsesection.sectionid =
tblresponse_line.sectionid AND tblresponsesection.responseid =
tblresponse_line.responseid AND tblresponsesection.clientnum =
tblresponse_line.clientnum
   JOIN tblresponseheader ON tblresponsesection.responseid =
tblresponseheader.responseid AND tblresponsesection.clientnum =
tblresponseheader.clientnum
   JOIN tbllocation ON tblresponseheader.locationid = tbllocation.locationid
AND tbllocation.clientnum = tblresponseheader.clientnum
   JOIN tbluser ON tblresponseheader.userid = tbluser.userid AND
tbluser.clientnum::bpchar = tblresponseheader.clientnum
   JOIN tbldistrict ON tbllocation.districtid = tbldistrict.districtid AND
tbldistrict.clientnum::bpchar = tblresponseheader.clientnum
   JOIN tblregion ON tbllocation.regionid = tblregion.regionid AND
tblregion.clientnum::bpchar = tblresponseheader.clientnum
   LEFT JOIN tblquestions ON tblresponse_line.clientnum =
tblquestions.clientnum AND tblresponse_line.sectionid =
tblquestions.sectionid AND tblresponse_line.questionid =
tblquestions.questionid AND tblresponseheader.auditid = tblquestions.auditid
   LEFT JOIN tblanswers ON tblresponseheader.auditid = tblanswers.auditid
AND tblresponse_line.clientnum = tblanswers.clientnum AND
tblresponse_line.sectionid = tblanswers.sectionid AND
tblresponse_line.questionid = tblanswers.questionid AND
tblresponse_line.answerid = tblanswers.answerid
   JOIN tblaudit ON tblresponseheader.auditid = tblaudit.auditid AND
tblresponse_line.clientnum = tblaudit.clientnum AND
tblaudit.defaultauditscoretype = 'p'::bpchar
   LEFT JOIN tblsections ON tblresponseheader.auditid = tblsections.auditid
AND tblresponse_line.clientnum = tblsections.clientnum AND
tblresponsesection.sectionid = tblsections.sectionid
   LEFT JOIN tblaudittypes ON tblresponseheader.audittypeid =
tblaudittypes.id AND 1 = tblaudittypes.presentationid AND
tblresponseheader.clientnum = tblaudittypes.clientnum::bpchar
   LEFT JOIN tblauditstatus ON tblresponseheader.statusid =
tblauditstatus.id AND 1 = tblauditstatus.presentationid
  WHERE tblresponseheader.isdeleted = false;*/
where tblresponsesection.clientnum = 'SAKS'

operating system?
Fedora core 3 at the moment, but next week I will get the Redhat AS4 when it
is available (we bought it but wanted the newer Kernel).

Thanks so much for taking a look.
Be happy to furnish conf files etc.
I am not opposed to learning enough to help myself, but also do not mind
getting some hired help, or a combination of both. I got a call from Command
prompt any one have experience with them? They are asking 150.00 an hour and
this may take a bit of time to get it all happy. I am sure I can get some $
budgeted if need be, but I don't want to end up with what I did last time I
called in consulting (was both Microsoft and Dell, when I switched from NT4
to Win2000 my app died in a use of 4 gigs memory in 20 minutes; took me two
weeks to plug all the holes and I had to reload NT 4 in the between time. We
spent like 900.00 on Microsoft and Dell just to be told I had to fix the
application).
----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

On Tue, 2005-02-15 at 15:21, Joel Fradkin wrote:
> Us? :)  Can you provide some more details?
>
> Hardware configuration,
> Dell 6560 4 processor, 8 gig memory, I think I have Postgres on two raids

four processors, or two hyperthreaded cpus?  My experience has been that
hyperthreading usually doesn't help, and often hurts performance.  This
may have improved with later model 2.6 kernels.

> separate from the OS (pgsql is a raid 10 4 10kdrive array) and /wal is 2 15k
> drives). I am running postgres 8 and am using the conf in
> /var/lib/pgslq/data to point the data at /pgsql and a link to point the /wal
> I set up inside the /pgsql/data folder to /wal.

Which RAID Controller are you using?  Does it have battery backed cache?

> I have cut out most of the query but even the first join makes it go to 27
> seconds. In MSSQL with the first two left outers it takes 3 seconds.

Are the columns you are joining on the same type?  Are they integers
(the fastest) or numerics (slow) or text (also slow)?

Is there a reason you're joining on so many different fields for each
table?  If there is a good model of FK relations, that isn't usually
necessary.

IT looks kinda like there's some redundancy in the join on clauses by
how may of them are from all over the place in there.

Hi Joel,
I'm running on a more or less similar hw config and my speed is pretty
ok.
My base is currently weighing in at 50Gig.

You may be having problems with the query itself. Aside from index'es
how you construct the query can have a dramatic impact on execution
time.

I can highly recommend the book "SQL Tuning" by Dan Tow.
It's database independant so I think this guy is relly on to the core
of the
problem. He's got a web site on (I shit you not):
http://www.singingsql.com/

Have you looked into the I/O rates (iostat) on your disk devices. One
of them
might be broken/slow.

Good luck

John

>>> "Joel Fradkin" <jfradkin@wazagua.com> 02/15/05 8:12 pm >>>
Hi,

I am trying to get postgres working on a Dell and am finidng my SQL
queries
run incrediably slow.

I have tried to adjust cache and buffers, etc with no luck.

Any advice on who would be best for help either it's a config issue or
a
query issue etc.

I been trying for a few weeks to get up to speed and diagnose, but I
am
running out of time.

Joel Fradkin