Re: Query Performance and IOWait

Andrew,

What version of Redhat are you running? We have found running Enterprise
Update 3 kernel kills our Dell boxes with IOWait, both NFS and local disk
traffic. Update 2 kernel does not seem to have the issue, and we are in the
process of trying Update 4 beta to see if it is better.

Woody

iGLASS Networks
www.iglass.net

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Andrew Janian
Sent: Thursday, November 18, 2004 9:02 AM
To: Richard_D_Levine(at)raytheon(dot)com
Cc: pgsql-performance(at)postgresql(dot)org; pgsql-performance-owner(at)postgresql(dot)org
Subject: Re: [PERFORM] Query Performance and IOWait

Actually, unfortunately, that won't work. The subquery gets a list of
message IDs and then the outer query gets the responses to those messages.

Also, I dumped this data and imported it all to ms sql server and then ran
it there. The query ran in 2s.

Andrew

-----Original Message-----
From: Richard_D_Levine(at)raytheon(dot)com
[mailto:Richard_D_Levine(at)raytheon(dot)com]
Sent: Thursday, November 18, 2004 7:57 AM
To: Andrew Janian
Cc: pgsql-performance(at)postgresql(dot)org;
pgsql-performance-owner(at)postgresql(dot)org
Subject: Re: [PERFORM] Query Performance and IOWait

Andrew,

It seems that you could combine the subquery's WHERE clause with the main
query's to produce a simpler query, i.e. one without a subquery.

Rick

"Andrew Janian"

<ajanian(at)scottrade(dot)com> To:
<pgsql-performance(at)postgresql(dot)org>

Sent by: cc:

pgsql-performance-owner(at)pos Subject: [PERFORM]
Query Performance and IOWait
tgresql.org

11/18/2004 08:42 AM

Hello All,

I have a setup with a Dell Poweredge 2650 with Red Hat and Postgres 7.4.5
with a database with about 27GB of data. The table in question has about
35 million rows.

I am running the following query:

SELECT *
FROM mb_fix_message
WHERE msg_client_order_id IN (
SELECT msg_client_order_id
FROM mb_fix_message
WHERE msg_log_time >= '2004-06-01'
AND msg_log_time < '2004-06-01 13:30:00.000'
AND msg_message_type IN ('D','G')
AND mb_ord_type = '1'
)
AND msg_log_time > '2004-06-01'
AND msg_log_time < '2004-06-01 23:59:59.999'
AND msg_message_type = '8'
AND (mb_raw_text LIKE '%39=1%' OR mb_raw_text LIKE '%39=2%');

with the following plan:

QUERY PLAN
Nested Loop IN Join (cost=0.00..34047.29 rows=1 width=526)
-> Index Scan using mfi_log_time on mb_fix_message (cost=0.00..22231.31
rows=2539 width=526)
Index Cond: ((msg_log_time > '2004-06-01 00:00:00'::timestamp without
time zone) AND (msg_log_time < '2004-06-01 23:59:59.999'::timestamp without
time zone))
Filter: (((msg_message_type)::text = '8'::text) AND
(((mb_raw_text)::text ~~ '%39=1%'::text) OR ((mb_raw_text)::text ~~
'%39=2%'::text)))
-> Index Scan using mfi_client_ordid on mb_fix_message
(cost=0.00..445.56 rows=1 width=18)
Index Cond: (("outer".msg_client_order_id)::text =
(mb_fix_message.msg_client_order_id)::text)
Filter: ((msg_log_time >= '2004-06-01 00:00:00'::timestamp without
time zone) AND (msg_log_time < '2004-06-01 13:30:00'::timestamp without time
zone) AND ((msg_message_type)::text = 'D'::text) OR
((msg_message_type)::text = 'G'::text)) AND ((mb_ord_type)::text =
'1'::text))

While running, this query produces 100% iowait usage on its processor and
takes a ungodly amount of time (about an hour).

The postgres settings are as follows:

shared_buffers = 32768 # min 16, at least max_connections*2, 8KB
each
sort_mem = 262144 # min 64, size in KB

And the /etc/sysctl.conf has:
kernel.shmall = 274235392
kernel.shmmax = 274235392

The system has 4GB of RAM.

I am pretty sure of these settings, but only from my reading of the docs and
others' recommendations online.

Thanks,

Andrew Janian
OMS Development
Scottrade Financial Services
(314) 965-1555 x 1513
Cell: (314) 369-2083

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org