Thread: Reason for PG being seemingly I/O bound?
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
Harshad <harshad.rj@gmail.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
On Mon, Sep 14, 2009 at 12:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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)
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.
Harshad <harshad.rj@gmail.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