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?
Best Answer
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
(orEXPLAIN 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...