[postgis-users] Performance and memory problems

[Andrea Aime]

strk wrote:

 > andrea.aime wrote:
 >
 >>strk wrote:
 >>
 >>
 >>>What C++ compiler do you have (name/version) ?
 >>>
 >>
 >>Libraries are:
 >>
 >>gcc 3.2.2 (Mandrake Linux 9.1 3.2.2-3mdk)
 >>glibc version 2.3.1
 >>libstc++5-3.2.2
 >
 >
 > You can try disabling the memory cache feature of libstdc++ by
 > exporting GLIBCPP_FORCE_NEW=1 before starting the postgresql
 > postmaster. This might save some memory.

Does not work :-(

 > I've had problems with 3.2.2 also try to upgrading to 3.3.2.

Updating the compiler is a bit out of my reach, I don't have enough
time to fiddle with gcc removal and installation from sources.
I will try at home where I have a mdk 9.2 which uses 3.3.1.

 > Ask on the pgsql-hackers mailing list about postmaster
 > stack trace (and please let me know).
 >

Will do. I've also noted something strange: before maxing out memory
vmstat reports that the cpu is used almost 100% by system tasks, not
user ones... look at this "vmstat 1" output:

(A): here I started the query
(B): here it starts spinning like mad in the system area
(C): boom

0  0      0  69464  35592 117008    0    0     0   120  117   101  0  2 98  0
  3  0      0  65148  35592 117700    0    0     0     0  103    90 58  8 34  0 (A)
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
  1  0      0  64672  35592 117836    0    0     0     0  101    61 69 31  0  0
  2  0      0  66832  35592 117972    0    0     0     0  101    59 50 50  0  0
  1  0      0  66960  35592 118092    0    0     0     0  101    62 63 37  0  0
  2  0      0  66020  35592 118228    0    0     0   200  128    75 61 39  0  0
  1  0      0  65032  35592 118308    0    0     0     0  102    63 55 45  0  0
  1  0      0  66292  35592 118444    0    0     0     0  101    68 54 46  0  0
  1  0      0  65444  35592 118564    0    0     0     0  101    72 72 28  0  0
  1  0      0  66444  35596 118672    0    0     4     0  102    73 46 52  2  0
  1  0      0  65144  35596 118880    0    0     0   180  127    88 54 46  0  0
  1  0      0  66176  35596 119048    0    0     0     0  102    68 59 41  0  0
  1  0      0  64264  35596 119320    0    0     0     0  102    74 77 23  0  0
  1  0      0  64076  35596 119432    0    0     0     0  102    74 50 50  0  0
  1  0      0  62732  35596 119536    0    0     0     0  101    79 84 16  0  0
  2  0      0  62596  35596 119536    0    0     0   228  119    83 89 11  0  0
  1  0      0  62532  35596 119536    0    0     0     0  101    75 10 90  0  0
  1  0      0  62520  35596 119536    0    0     0     0  101    77  5 95  0  0 (B)
  1  0      0  61328  35596 119536    0    0     0     0  101    80  5 95  0  0
  1  0      0  61496  35596 119536    0    0     0     0  101    76  4 96  0  0
  1  0      0  62508  35596 119536    0    0     0    24  105    82 10 90  0  0
  1  0      0  60296  35596 119536    0    0     0     0  101    71  5 95  0  0
  1  0      0  60772  35596 119536    0    0     0     0  101    66  3 97  0  0
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
  1  0      0  60812  35596 119536    0    0     0     0  101    70  6 94  0  0
  1  0      0  61604  35596 119536    0    0     0     0  101    70  2 98  0  0
  2  0      0  65208  35596 119744    0    0     0    28  106    79 21 79  0  0
  1  0      0  64224  35596 119864    0    0     0     0  101    68 91  9  0  0
  1  0      0  64548  35596 120012    0    0     0     0  101    66 71 29  0  0
  0  0      0  69716  35596 117268    0    0    16  1548  172   123 15 27 58  0 (C)
  0  0      0  69716  35596 117268    0    0     0     0  101    64  0  0 100  0
  0  0      0  70140  35596 117268    0    0     0   136  123   116  0  1 99  0

I wonder why it is starting to behave like this at B since the postmaster process
never uses more than 20 MB on the RSS column of the "top" output...

Andrea