Making sorts happen in memory instead of on disk

Making sorts happen in memory instead of on disk

If you have disk-based sorts occurring frequently, you can find the size of the largest of the frequent queries and then set this as the standard threshold for sorts. The parameter for this in/var/lib/pgsql/data/postgresql.conf is 'work_mem'. For example, if you had frequent disk-spaced sorts using up to 64MB, you would set the following in /var/lib/pgsql/data/postgresql.conf:

work_mem = 64MB

Be very careful, however, because this setting gets used by each query in each thread, so if you set this too high you could rapidly run out of RAM and cause the system to start swapping, which would be disastrous. The best thing to do is increase this bit by bit until as many as possible of your frequent queries are sorting in RAM but without running out of available memory. To see how much RAM is actually available on your system, use the command 'free'. As Linux tends to use up the majority of available RAM for its disk cache, the number you actually care about is the 'free' column in the 'buffers/cache' row - this is how much RAM is really available. For example, here we can see that there are just over 11 GB of free RAM available:

# free
             total       used       free     shared    buffers     cached
Mem:      12300988   12013536     287452          0     277788   10801424
-/+ buffers/cache:     934324   11366664
Swap:      4192956        240    4192716

Once you've dealt with sorts for frequent queries, you might find there are still the occasional disk-based sorts happening for a small number of particularly intensive queries. For those, you can increase the sort memory threshold on a per-query basis and then put it back to the standard setting once the query is finished. To do this, prepend your query with:

SET work_mem = '500MB';

changing '500MB' to whatever size is appropriate for you, then append the query with:

RESET work_mem;

to return it to the standard threshold. Again, be careful that you don't use up all the system's available RAM because then the dreaded swapping will occur.

Once you've done all this, as many sorts as possible will be happening in memory instead of on disk, and your PostgreSQL installation should be performing considerably better as a result of this tuning.

출처 : http://blog.mattbrock.co.uk/postgresql-tuning-ensuring-that-as-many-sorts-as-possible-are-done-in-memory-and-not-on-disk/