iotop shows kswapd0 at 99.99% with 0% DISK READ and DISK WRITE

Question

I have a heavy postgres query. It takes over ten minutes to run. I'd like to upgrade my hardware to get it run faster. I think more RAM will help, but my motherboard is full so I'll need a whole new motherboard. I don't want to invest unless I know I'll get better results. Here's what I see in iotop:

Total DISK READ:      46.81 M/s | Total DISK WRITE:       0.00 B/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                               
   27 be/4 root        0.00 B/s    0.00 B/s  0.00 % 99.99 % [kswapd0]
 2514 be/4 postgres   46.81 M/s    2.45 M/s  0.00 % 18.36 % postgres: postgres db1 127.0.0.1(55328) SELECT
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]
    5 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/u:0]
    6 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/0]
    7 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [watchdog/0]
    8 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/1]
    9 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/1:0]
   10 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/1]

Notice how the kswapd0 is hitting the hard drive the most (99.99% IO). However, the DISK READ and DISK WRITE and SWAPIN of kswap0 are all zero. What is kswap0 doing? Is it really hitting my hard drive? Would adding more RAM to this system help at all?

Solution

You are approaching this from the wrong angle. Only after optimizing your SQL should you consider throwing more RAM/CPU/Disk (I/O bandwidth) at the problem -- and addressing the problem as you've identified it.

First ask Postgres to EXPLAIN (or EXPLAIN ANALYZE) how it is performing the query.
Optimize the ever-loving kittens out of that, and then if you still have performance problems investigate further to determine where the bottleneck is (sonassi gave you some good suggestions on stuff you should look at in addition to iotop).

If you are running your web stack and DB on the same server, now is a good time to split them up as well...