pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedwho is best for support?
7+ messages / 5 participants
[nested] [flat]
* who is best for support?
@ 2005-02-15 20:12 Joel Fradkin <jfradkin@wazagua.com>
2005-02-15 20:24 ` Re: who is best for support? Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-15 20:36 ` Re: who is best for support? Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 2 replies; 7+ messages in thread
From: Joel Fradkin @ 2005-02-15 20:12 UTC (permalink / raw)
To: pgsql-admin
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
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: who is best for support?
2005-02-15 20:12 who is best for support? Joel Fradkin <jfradkin@wazagua.com>
@ 2005-02-15 20:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2005-02-15 20:24 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: who is best for support?
2005-02-15 20:12 who is best for support? Joel Fradkin <jfradkin@wazagua.com>
@ 2005-02-15 20:36 ` Marc G. Fournier <scrappy@postgresql.org>
2005-02-15 21:21 ` Re: who is best for support? Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 1 reply; 7+ messages in thread
From: Marc G. Fournier @ 2005-02-15 20:36 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: who is best for support?
2005-02-15 20:12 who is best for support? Joel Fradkin <jfradkin@wazagua.com>
2005-02-15 20:36 ` Re: who is best for support? Marc G. Fournier <scrappy@postgresql.org>
@ 2005-02-15 21:21 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-15 21:30 ` Re: who is best for support? Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 7+ messages in thread
From: Joel Fradkin @ 2005-02-15 21:21 UTC (permalink / raw)
To: 'Marc G. Fournier' <scrappy@postgresql.org>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: who is best for support?
2005-02-15 20:12 who is best for support? Joel Fradkin <jfradkin@wazagua.com>
2005-02-15 20:36 ` Re: who is best for support? Marc G. Fournier <scrappy@postgresql.org>
2005-02-15 21:21 ` Re: who is best for support? Joel Fradkin <jfradkin@wazagua.com>
@ 2005-02-15 21:30 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2005-02-15 21:30 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: 'Marc G. Fournier' <scrappy@postgresql.org>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: who is best for support?
@ 2005-02-15 20:59 Richard_D_Levine@raytheon.com
0 siblings, 0 replies; 7+ messages in thread
From: Richard_D_Levine@raytheon.com @ 2005-02-15 20:59 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: Joel Fradkin <jfradkin@wazagua.com>; pgsql-admin; pgsql-admin-owner@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: who is best for support?
@ 2005-02-16 08:23 John Jensen <JRJ@ft.fo>
0 siblings, 0 replies; 7+ messages in thread
From: John Jensen @ 2005-02-16 08:23 UTC (permalink / raw)
To: pgsql-admin; jfradkin@wazagua.com
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-02-16 08:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-15 20:12 who is best for support? Joel Fradkin <jfradkin@wazagua.com>
2005-02-15 20:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-15 20:36 ` Marc G. Fournier <scrappy@postgresql.org>
2005-02-15 21:21 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-15 21:30 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-15 20:59 Re: who is best for support? Richard_D_Levine@raytheon.com
2005-02-16 08:23 Re: who is best for support? John Jensen <JRJ@ft.fo>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox