Thread: Memory usage per postmaster process
Hello, Recently I had some problem with tsearch2 in postgres 8.3. Application was using JDBC connection pool and it was possible that each connection from the pool used tsearch2(with dictionary) in some query. This resulted in almost every postmaster process using >0.5g memory. Solution of this particular problem was simple: create separate small pool for tsearch2 related queries. Unfortunately some other queries make postmaster processes grow and while it does not make much problems in production environment, it makes difficult to set up dev instance, because application and postgres take more RAM than I currently have. 4GB memory in swap kills everything even though I have it in software raid0 with 6 disks. I run postgres and application in same LXC instance. Now I could play around with application code even more, but it is old and ugly, so instead I am looking at solution in postgresql.conf, which I don't fully understand. Postgres upgrade could also be some solution, but was there any significant improvement in system resources management in 8.X? Upgrade to 9.X might be too big effort at this time. I want to avoid problems with compatiblity, because it will take a lot of time to track them in application code which is very low quality. Is there any way to limit total memory usage by postgres and keep maximum connections limit? Postgresql.conf settings are default for 8.3.23. I need to have 100 connections in pool. Thanks! -- Regards, Grzegorz
On 11/2/2013 11:03 AM, Grzegorz Tańczyk wrote: > > Is there any way to limit total memory usage by postgres and keep > maximum connections limit? Postgresql.conf settings are default for > 8.3.23. I need to have 100 connections in pool. the size of your connection pool shouldn't be much more than 2-3 times the CPU core count on the server for optimal throughput... 100 queries running at once will grind ANY non-monster server to a standstill -- john r pierce 37N 122W somewhere on the middle of the left coast
On 11/02/2013 07:47 PM, John R Pierce wrote: > On 11/2/2013 11:03 AM, Grzegorz Tańczyk wrote: >> >> Is there any way to limit total memory usage by postgres and keep >> maximum connections limit? Postgresql.conf settings are default for >> 8.3.23. I need to have 100 connections in pool. > > the size of your connection pool shouldn't be much more than 2-3 times > the CPU core count on the server for optimal throughput... 100 queries > running at once will grind ANY non-monster server to a standstill In fact thats what happened when tsearch2 problem occured even though there was only few queries running at once. Group of idle connections was using resources and that's the thing I don't understand. Did tsearch2 dictionary caching implementation improve after 8.3 on this matter? Making small connection pool will help, however how small should it be? 1 connection max, 0 connections minimum? Connections will get closed after they are released by application code, but still there will be some group of postmaster processes and how can I be sure if none of them will get 1gb of system memory? I can't have any control over this (other than grepping ps output and manually pg_cancel_backend them once they grow too much). Thanks! -- Regards, Grzegorz
=?UTF-8?B?R3J6ZWdvcnogVGHFhGN6eWs=?= <goliatus@polzone.pl> writes:
> Did tsearch2 dictionary caching implementation improve after 8.3 on this
> matter?
Well, there was this:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_1_BR [3e5f9412d] 2010-10-06 19:31:05 -0400
Reduce the memory requirement for large ispell dictionaries.
This patch eliminates per-chunk palloc overhead for most small allocations
needed in the representation of an ispell dictionary. This saves close to
a factor of 2 on the current Czech ispell data. While it doesn't cover
every last small allocation in the ispell code, we are at the point of
diminishing returns, because about 95% of the allocations are covered
already.
Pavel Stehule, rather heavily revised by Tom
If you're not using ispell, it's not relevant, and I'm not sure whether
the savings were significant for anything but Czech.
regards, tom lane
On 11/02/2013 08:47 PM, Tom Lane wrote:
I checked this patch:Author: Tom Lane <tgl@sss.pgh.pa.us> Branch: master Release: REL9_1_BR [3e5f9412d] 2010-10-06 19:31:05 -0400 Reduce the memory requirement for large ispell dictionaries.
http://www.postgresql.org/message-id/AANLkTi=4fUi1zoFMpZ==Yf14RJDv_G1xgAkVQMDyEtBk@mail.gmail.com
I can't find it here:
http://doxygen.postgresql.org/spell_8c_source.html
I also don't see those changes in 9.3.1 source. Status in commitfest list is " Committed". I can't see hold_memory anywhere.
I am using ispell.If you're not using ispell, it's not relevant, and I'm not sure whether the savings were significant for anything but Czech.
Thanks
--
Regards,
Grzegorz
=?UTF-8?B?R3J6ZWdvcnogVGHFhGN6eWs=?= <goliatus@polzone.pl> writes: > On 11/02/2013 08:47 PM, Tom Lane wrote: >> Author: Tom Lane<tgl@sss.pgh.pa.us> >> Branch: master Release: REL9_1_BR [3e5f9412d] 2010-10-06 19:31:05 -0400 >> >> Reduce the memory requirement for large ispell dictionaries. > I checked this patch: > http://www.postgresql.org/message-id/AANLkTi=4fUi1zoFMpZ==Yf14RJDv_G1xgAkVQMDyEtBk@mail.gmail.com > I can't find it here: > http://doxygen.postgresql.org/spell_8c_source.html > I also don't see those changes in 9.3.1 source. Status in commitfest > list is " Committed". I can't see hold_memory anywhere. If you read the rest of the discussion of the patch, you'd find out that what got committed was not all that much like Pavel's original. But it has the same effect. regards, tom lane