Skip site navigation (1) Skip section navigation (2)

how the hdd read speed is related to the query execution speed.

From: sathiya psql <sathiya(dot)psql(at)gmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: how the hdd read speed is related to the query execution speed.
Date: 2009-02-21 07:14:38
Message-ID: f966c2ee0902202314x40adc95mb4ab6d5a13dbef0c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

I have a 8 GB database, and 2 GB table. In a query i use the 2 GB table and
several other tables where it takes around 90 minutes for execution.

In different places, it takes drastically different time. Say everywhere i
have the same,
OS - Debian.
Primary memory - 3 GB
PostgreSQL configuration.

But in one machine it takes 3.5 minutes, and in other machine 90 minutes
which confuses me much. So i did a test of hard disk read speed, in the
machine where it takes 90 minutes resulted in the following.,

dd if=/var/lib/postgresql/8.1/main/base/16385/17283 of=/dev/null bs=1M
count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 8.32928 seconds, 129 MB/s

Unfortunately, i cannot execute this same on that 3.5 minute execution
machine.

But i had the previous write speed test, output which is
dd if=/dev/zero of=/tmp/test bs=1M count=1024
1073741824 bytes (1.1 GB) copied, 2.37701 seconds, 452 MB/s

and the same write speed test in the 90 minutes machine is
dd if=/dev/zero of=/tmp/test bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 19.5375 seconds, 55.0 MB/s

So i assume that there should be a 9 times faster execution. Because 55 MB
write per second, and 450 MB write per second.
But am i doing some thing silly here. Or what i can do better confirm the
problem ??

can some body give me ideas on what to do for confirming what is the issue
for consuming much time for the query execution ?

Responses

pgsql-performance by date

Next:From: Andreas KretschmerDate: 2009-02-21 08:36:04
Subject: Re: how the hdd read speed is related to the queryexecution speed.
Previous:From: decibelDate: 2009-02-21 03:22:11
Subject: Re: Partial index usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group