Debugging shared memory issues on CentOS

Поиск
Список
Период
Сортировка
От Mack Talcott
Тема Debugging shared memory issues on CentOS
Дата
Msg-id CAPZbZCDrS11_YAExC_tHfv7U=1Phzbqi3=kTkp6Kd7CM4M0RBg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Debugging shared memory issues on CentOS  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Дерево обсуждения
I am trying to debug some shared memory issues with Postgres 9.3.1 and CentOS release 6.3 (Final).  I have a database machine that probably has some misconfigured shared memory settings.  It's getting into 2+ GB of swap.  Restarting postgres frees all of the memory, but after a few hours of normal usage it will go back into swap.  During light usage, postgres will *very* slowly release some memory, but not all.  Using top, I can see that many of the postgres connections are using shared memory:

```
top - 09:38:16 up 1 day, 21:21,  3 users,  load average: 0.40, 0.54, 0.45
Tasks: 253 total,   2 running, 251 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.7%us,  0.2%sy,  0.0%ni, 97.8%id,  1.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   6998260k total,  6849048k used,   149212k free,      248k buffers
Swap: 440478516k total,  1981912k used, 438496604k free,  1541356k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3534 postgres  20   0 2330m 1.4g 1.1g S  0.0 20.4   1:06.99 postgres: deploy mtalcott 10.222.154.172(53495) idle
 9143 postgres  20   0 2221m 1.1g 983m S  0.0 16.9   0:14.75 postgres: deploy mtalcott 10.222.154.167(35811) idle
 6026 postgres  20   0 2341m 1.1g 864m S  0.0 16.4   0:46.56 postgres: deploy mtalcott 10.222.154.167(37110) idle
18538 postgres  20   0 2327m 1.1g 865m S  0.0 16.1   2:06.59 postgres: deploy mtalcott 10.222.154.172(47796) idle
 1575 postgres  20   0 2358m 1.1g 858m S  0.0 15.9   1:41.76 postgres: deploy mtalcott 10.222.154.172(52560) idle
```

There are about 29 total idle connections.  `sudo ipcs -m` only shows:
```
  ------ Shared Memory Segments --------
  key        shmid      owner      perms      bytes      nattch     status
  0x0052e2c1 163840     postgres   600        48         21
```

Surprisingly, it only shows it using 48 bytes.  Any ideas why that would be?

My shared memory settings are:
kernel.shmmax = 8589934592  # 8 GB
kernel.shmall = 2097152     # * 4096 = 8 GB  
kernel.shmmni = 4096

Do I need to set lower shared memory limits?  In the past, I've run into issues using pg_dump and executing larger transactions with lower values.  If I can monitor the shared memory segment I can better understand when postgres is allocating and releasing..

В списке pgsql-performance по дате отправления:

Предыдущее
От: jacket41142
Дата:
Сообщение: select count(distinct ...) is slower than select distinct in about 5x
Следующее
От: Krzysztof Olszewski
Дата:
Сообщение: Problem with slow query with WHERE conditions with OR clause on primary keys