Re: Postgre Eating Up Too Much RAM

Поиск
Список
Период
Сортировка
От Aaron Bono
Тема Re: Postgre Eating Up Too Much RAM
Дата
Msg-id CAHfMse1UX1G6zfCTRw3+MBEbdLYJ=xDtCFtmoEr6m5bSgr0E+g@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Postgre Eating Up Too Much RAM  ("Kevin Grittner" <kgrittn@mail.com>)
Список pgsql-admin
Дерево обсуждения
I replied to this a few days ago but forgot to include the group.  It appears that increasing our server swap space has fixed our problems.  I will keep my fingers crossed.

 
> (there are currently a little over 200 active connections to the
> database):

How many cores do you have on the system? What sort of storage
systeme?

Intel Dual Xeon E5606 2133MHz
2 CPU's with 4 Cores each
32GB RAM
Hard Drive: 1.6 TB RAID10
 
What, exactly, are the symptoms of the problem? Are there
200 active connections when the problem occurs? By "active", do you
mean that there is a user connected or that they are actually running
something?

When the server goes unresponsive I am not sure what the number of connections are.  I will do more diagnostic reporting but I suspect the number of connections may be spiking for some reason and / or the usage of the BLOBs in the DB are at the heart of the problem.
 

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

> max_connections = 1000

If you want to handle a large number of clients concurrently, this is
probably the wrong way to go about it. You will probably get better
performance with a connection pool.

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

We already use connection pooling.  We are in the process of putting limits on the max open connections and also changing how those connections are used to reduce the number of open connections from any particular application instance.
 
> shared_buffers = 256MB

Depending on your workload, a Linux machine with 32GB RAM should
probably have this set somewhere between 1GB and 8GB.

I will try increasing the shared_buffers.  Thanks.

A few days ago I increased the swap on the machine to 34 GB (it was 2 GB and I added 32 more).  The server now appears to be stable.  Either this change has been enough to keep things humming along well or whatever the app is doing to cause issues just hasn't occurred in the last few days.  I suspect this change is what has stabilized things.
 
> vacuum_cost_delay = 20ms

Making VACUUM less aggressive usually backfires and causes
unacceptable performance, although that might not happen for days or
weeks after you make the configuration change.

Our databases are mostly heavy reads with not a lot of writes.  We almost never do hard deletes.  That is why I put the vacuum at this level.
 

By the way, the software is called PostgreSQL. It is often shortened
to Postgres, but "Postgre" is just wrong.

Yep, my typo.

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

Предыдущее
От: Lukasz Brodziak
Дата:
Сообщение: Re: [bug] I have installed pgAdminIII 1.16 on win7,rename table or function or sequence ,pgadmin crash
Следующее
От: Prabir Maity
Дата:
Сообщение: PostgreSQL server: received invalid response to SSL negotiation