2

I've set swappiness on my DB server to zero, but during a large sort operation the swap space used grew even as the cache was still using a lot of memory, and then cache grew but swap was left alone. This makes no sense to me. Here's free -m as of now:

             total       used       free     shared    buffers     cached
Mem:         16000      15979         20          0         24      10520
-/+ buffers/cache:       5434      10565
Swap:         6111       5478        633

This is happening in the middle of a large sort in PostgreSQL. The issue is that the swapped out memory will probably be called upon as soon as the sort is over (it's a big UPDATE on a table I put into tmpfs), resulting in a huge slowdown as it will be random access.

ehsanul
  • 427
  • 1
  • 8
  • 19

1 Answers1

3

How much space are you using in your tmpfs?

Bear in mind that tmpfs is backed by cache, and shows up in that statistic. So, the normal advice of "ignore cached and use the +/- line" doesn't necessarily apply.

In fact, tmpfs is swappable, so it's very possible that that's what's getting swapped out as your real memory pressure increases.

mattdm
  • 6,550
  • 1
  • 25
  • 48
  • Oh, that explains it, thanks! I had a nagging feeling that was it. However, the memory amount used didn't add up in my head, so I discarded that theory. I guess I'll have to figure out why it's taking so much memory for the sort when I set PostgreSQL work_mem to stay within limits of RAM - `tmpfs`.. – ehsanul Jan 26 '11 at 21:43
  • Oh wait a minute.. If tmpfs is swapped out, it shouldn't still show up in the cache right? That part doesn't make sense to me still. – ehsanul Jan 26 '11 at 21:45
  • 4
    Well, if PostgreSQL needs more memory for the sorting than work_mem allows, it spills to disk, which is RAM in your case. You lose either way. So you see that using tmpfs with PostgreSQL might not make much sense. – Peter Eisentraut Jan 26 '11 at 22:29
  • I hadn't realized it would use the tmpfs tablespace for external merge sorts (assumed it would use pg_default tablespace), but that makes a lot of sense. – ehsanul Jan 27 '11 at 07:18