Lists: | pgsql-novice |
---|
From: | Harshad <harshad(dot)rj(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Reason for PG being seemingly I/O bound? |
Date: | 2009-09-13 17:00:18 |
Message-ID: | h8j892$sdg$1@ger.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Hi,
I am trying to figure out why a very simple query turns out to be I/O bound. (I have spent more than 2 days
learning and tweaking everything from "shared_buffer" to "vm.swappiness", but not making any progress.)
The postgresql.conf is set to all defaults.
The table is called 'users' which has an INTEGER id with a UNIQUE constraint.
There are only about 1000 rows in the table.
The query and the plan is:
EXPLAIN
SELECT null
FROM users
WHERE id=14601448;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using "userId" on users (cost=0.00..8.27 rows=1 width=0)
Index Cond: (id = 14601448)
I have put this query in simple.sql, and running it in a tight loop in the shell thusly:
while [ true ] ; do psql -d twinkle -U postgres -f simple.sql; done > /dev/null
(that's zsh syntax; in bash it would be while [[ true ]] ...)
The CPU utilisation of this is hardly 5%.
I have tried this on my PC as well as the production server, with several combinations of configuration settings
(including the defaults, shutting off autovacumm, shutting off synchronised_commit, shutting off fsync, etc).
What am I missing?
thanks for your help,
Harshad
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Harshad <harshad(dot)rj(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Reason for PG being seemingly I/O bound? |
Date: | 2009-09-13 19:15:27 |
Message-ID: | 8970.1252869327@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Harshad <harshad(dot)rj(at)gmail(dot)com> writes:
> I am trying to figure out why a very simple query turns out to be I/O
> bound.
It's hard to see how that case could be I/O bound, unless your machine
is seriously starved for memory. Did you watch "vmstat 1" or something
similar to confirm that a lot of I/O is really happening?
> I have put this query in simple.sql, and running it in a tight loop in the shell thusly:
> while [ true ] ; do psql -d twinkle -U postgres -f simple.sql; done > /dev/null
FWIW, the overhead of the above is just enormous --- starting up a new
database connection takes way more time than issuing one simple query.
However I still don't see how it would be I/O bound; the kernel
certainly ought to have everything needed in disk cache after a couple
of cycles. On my machine a similar test immediately pins the CPU
with about half user, half system time.
What platform are you using, exactly?
regards, tom lane
From: | Harshad RJ <harshad(dot)rj(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Reason for PG being seemingly I/O bound? |
Date: | 2009-09-14 04:34:22 |
Message-ID: | 5f2b35a60909132134q2380717dna6043ce6f81d39a2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
On Mon, Sep 14, 2009 at 12:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Harshad <harshad(dot)rj(at)gmail(dot)com> writes:
> Did you watch "vmstat 1" or something
> similar to confirm that a lot of I/O is really happening?
>
No, I hadn't confirmed; I was just guessing, because I thought the CPU
utilisation was low. It turns out I was wrong in measuring the CPU
utilisation (forgot about system utilisation); and vmstat confirms that
there isn't any block i/o happening.
(PS. That's an incredibly useful command. Thanks! I was using 'grep' on
/proc/vmstat and it was cumbersome)
>
> However I still don't see how it would be I/O bound; the kernel
> certainly ought to have everything needed in disk cache after a couple
> of cycles. On my machine a similar test immediately pins the CPU
> with about half user, half system time.
>
>
I hadn't noted down the system utilisation (sorry). My net CPU utilisation
(user + system) is about 50%, but it's a dual core system, so it is not all
that bad.
The CPU utilisation on my production server is lower, but I will
test/research further before asking questions here.
thanks & sorry for the false alarm,
--
Harshad RJ