Hello all,
I'm now working on performance tuning for PostgreSQL application.
I know shared_buffers and sort_mem have huge impacts for the performance.
If a disk sort (called tape sort in the code) is occured, we need to
increase sort_mem value. Then I found it is difficult to get a
reasonable value for sort_mem.
So I've implemented new five functions. These functions can give some
hints to estimate the sort_mem value.
- pg_stat_get_heap_all_sorts()- pg_stat_get_heap_tape_sorts()- pg_stat_get_index_all_sorts()-
pg_stat_get_index_tape_sorts()-pg_stat_get_max_sort_size()
Using these functions, we can create a new system view about sort memory
condition and statistics as below.
------------------------------------------------------------------
snaga=# select pg_stat_get_heap_all_sorts() as heap_all, pg_stat_get_heap_tape_sorts() as heap_tape,
pg_stat_get_index_all_sorts() as index_all, pg_stat_get_index_tape_sorts() as index_tape,
pg_stat_get_max_sort_size() as max_sort_size;heap_all | heap_tape | index_all | index_tape | max_sort_size
----------+-----------+-----------+------------+--------------- 2 | 1 | 0 | 0 |
110203384
(1 row)
snaga=#
------------------------------------------------------------------
And my patch reports sort memory condition to the log.
------------------------------------------------------------------
> NOTICE: tuplesort is attempting to use physical device.
> NOTICE: Max used size of the sort memory (213109 kB)
------------------------------------------------------------------
I'm ready to post this patch.
Is this useful? Any comments?
--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/
