Re: [PERFORM] Postgress memory leak with JBoss3.2.6 and large DB
Hi Guys, We found the issue regarding our memory leak. It was the query. It seams were using functions with fetch cursors on large data sets and the cursors were not getting closed properly. Hence the memory was building up. So I guess this was an application error. In fact we bought the Query memory from 1.4 GB to 2 MB. Thanks for all the help guys. Because by reading all your comments I was able to understand a lot about Postgres memory settings. K.Indika Maligaspe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Kirkwood Sent: Monday, September 04, 2006 9:23 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Postgress memory leak with JBoss3.2.6 and large DB Indika Maligaspe wrote: > Hey guys, > > We are running a Linux 2.4 enterprise edition box with 6GB of RAM, > **Postgres 8.0.3**. (snippage) > You might want to consider upgrading to 8.0.8 (see below), and seeing if the problem still persists. > As you can see the postmaster users are taking way over the memory that > should be taken. > > > If any of you can give us some pointers we would really appreciate that > and thanks in advance. > I notice that there are a number of fixes for memory leaks since 8.0.3 - 8.0.4 and 8.0.8 is where I see 'em specifically (reading release notes for 8.0.8). So you may be experiencing an issue that is fixed in the current 8.0 releases! I recommend upgrading to 8.0.8. You didn't say what your HW was, but if you are on a 32-bit platform, then a 2.4 kernel when you have >2G ram may leak noticeable amounts of memory itself... Cheers Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] database bloat,non removovable rows, slow query etc... [RESOLVED]
Gregory Stark <[EMAIL PROTECTED]> writes: > I must be misunderstanding Tom's comment then. > What I'm referring to is lazy_vacuum_rel() calls vacuum_set_xid_limits with > the relisshared flag of the relation. vacuum_set_xid_limits passes that to > GetOldestXmin as the allDbs parameter. GetOldestXmin ignores transactions not > connected to the same database unless allDbs is true. The problem is the indirect effect of other backends' xmin values, which are computed across all live backends. In the current structure, it's hard to see how to fix this except by making each backend compute and advertise both a global and database-local xmin. This seems a bit ugly. Also, someone asked recently whether we could avoid counting prepared xacts when figuring vacuum cutoffs, which seems a fair question --- but again, how to do that without doubling the number of advertised xmin values yet again? I'm starting to feel that we've reached the limits of this system of accounting for live XIDs, but I have no idea what the next step might look like... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] database bloat,non removovable rows, slow query etc... [RESOLVED]
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: > > Tom Lane <[EMAIL PROTECTED]> writes: > > > > > Matteo Sgalaberni <[EMAIL PROTECTED]> writes: > > > > Good to know this...but why this behaviour? it'is lovely...:) > > > > > > Open transactions are tracked across the whole cluster. This is > > > necessary when vacuuming shared catalogs. In principle we could > > > track per-database xmin values as well, but the distributed overhead > > > that'd be added to *every* GetSnapshotData call is a bit worrisome. > > > > Don't we do that now in CVS (ie, in 8.2)? > > No, we don't. I must be misunderstanding Tom's comment then. What I'm referring to is lazy_vacuum_rel() calls vacuum_set_xid_limits with the relisshared flag of the relation. vacuum_set_xid_limits passes that to GetOldestXmin as the allDbs parameter. GetOldestXmin ignores transactions not connected to the same database unless allDbs is true. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] database bloat,non removovable rows, slow query etc... [RESOLVED]
Gregory Stark wrote: > Tom Lane <[EMAIL PROTECTED]> writes: > > > Matteo Sgalaberni <[EMAIL PROTECTED]> writes: > > > Good to know this...but why this behaviour? it'is lovely...:) > > > > Open transactions are tracked across the whole cluster. This is > > necessary when vacuuming shared catalogs. In principle we could > > track per-database xmin values as well, but the distributed overhead > > that'd be added to *every* GetSnapshotData call is a bit worrisome. > > Don't we do that now in CVS (ie, in 8.2)? No, we don't. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] database bloat,non removovable rows, slow query etc... [RESOLVED]
Tom Lane <[EMAIL PROTECTED]> writes: > Matteo Sgalaberni <[EMAIL PROTECTED]> writes: > > Good to know this...but why this behaviour? it'is lovely...:) > > Open transactions are tracked across the whole cluster. This is > necessary when vacuuming shared catalogs. In principle we could > track per-database xmin values as well, but the distributed overhead > that'd be added to *every* GetSnapshotData call is a bit worrisome. Don't we do that now in CVS (ie, in 8.2)? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] 64bit vs 32bit build on amd64
Hi. My config: gentoo linux "2005.1" on amd64x2 in 64-bit mode, kernel 2.6.16.12 glibc 3.3.5(NPTL), gcc 3.4.3. I had not used portage for building. I built two versions of postgres from sources: postgresql-8.1.4 native(64bit) and 32-bit with CFLAGS=... -m32, and "LD = /usr/x86_64-pc-linux-gnu/bin/ld -melf_i386" in src/Makefile.global. 32-bit build runs much faster than 64 apparently. What benchmark utility should I run to provide more concrete info (numbers)? What could be the reason of that difference in performance? Regards, Roman. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] how to partition disks
hubert depesz lubaczewski writes: On 6/14/06, Sven Geisler raid 10 is of course not questionable. but are you sure that it will work faster than for example: 2 discs (raid 1) for xlog 6 discs (raid 10) for tables 6 discs (raid 10) for indices? Caching up on the performance list. Although this may not help the original poster.. wanted to share a recent experience related to allocation of disks on a raid. We just got a server with 16 disks. We condfigured 12 to 1 raid controller and a second raid with 4. Both using raid 10. RAID 1 10 x 7,200rpm disks 2 hot spares RAID 2 4 x 10,000 rpm disk One of the things I always do with new machines is to run bonnie++ and get some numbers. I expected the second raid to have better numbers than the first because the disks were 10K drives (all SATA). To my surprise the larger raid had better numbers. So I figure the number of spindles on a single RAID does make a big difference. To that regard splitting 16 disks into 3 sets may help with data needing to be read/written to be in separate raids, but may degrade performance by reducing the number of spindles on each of the raids. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?
On 4-Sep-06, at 8:07 AM, Guillaume Cottenceau wrote: Dave Cramer writes: Guillaume 1G is really not a significant amount of memory these days, Yeah though we have 2G or 4G of RAM in our servers (and not only postgres running on it). That said 6-10% of available memory should be given to an 8.0 or older version of postgresql Newer versions work better around 25% I'm not sure what you mean by mechanically removed from effective_cache I mean that when you allocate more memory to applications, the consequence is less memory the OS will be able to use for disk cache. effective cache is really a representation of shared buffers plus OS cache Are you sure the shared buffers should be counted in? As I understand the documentation, they should not (as shared buffers is allocated memory for the OS, not part of "kernel's disk cache"): Yes, I am sure this should be counted, however effective_cache is not actually allocating anything so it doesn't have to be exact, but it has to be in the correct order of magnitude Sets the planner's assumption about the effective size of the disk cache (that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files). This is measured in disk pages, which are normally 8192 bytes each. The default is 1000. -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http:// mobilefriends.ch/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?
"Merlin Moncure" writes: > On 01 Sep 2006 19:00:52 +0200, Guillaume Cottenceau <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I've been looking at the results from the pg_statio* tables, to > > view the impact of increasing the shared buffers to increase > > performance. > > > > I think 'shared buffers' is one of the most overrated settings from a > performance standpoint. however you must ensure there is enough for > things the server does besides caching. It used to be a bigger deal "Beside caching".. It's unfornatunate that the documentation on pg.org is very vague about the actual use(s) of the shared buffers :/ -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?
Dave Cramer writes: > Guillaume > > 1G is really not a significant amount of memory these days, Yeah though we have 2G or 4G of RAM in our servers (and not only postgres running on it). > That said 6-10% of available memory should be given to an 8.0 or > older version of postgresql > > Newer versions work better around 25% > > I'm not sure what you mean by mechanically removed from effective_cache I mean that when you allocate more memory to applications, the consequence is less memory the OS will be able to use for disk cache. > effective cache is really a representation of shared buffers plus OS > cache Are you sure the shared buffers should be counted in? As I understand the documentation, they should not (as shared buffers is allocated memory for the OS, not part of "kernel's disk cache"): Sets the planner's assumption about the effective size of the disk cache (that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files). This is measured in disk pages, which are normally 8192 bytes each. The default is 1000. -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [BUGS] Hanging queries on Windows 2003 SP1
> Hi, > > We are seeing hanging queries on Windows 2003 Server SP1 with dual > CPU, looks like one of the process is blocked. In a lot of cases, > the whole DB is blocked if this process is holding important locks. > > Looks like this issue was discussed in the following thread a few > month ago, but didn't seem to have a solution mention. I would liek > to know if there is a patch for this already? > > http://archives.postgresql.org/pgsql-performance/2006- > 03/msg00129.php > There have been some fairly extensive changes in the semaphore code for 8.2. Any chance you can try the cvs snapshot version and see if the problem exists there as well? //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] database bloat,non removovable rows, slow query etc...
Hi, Matteo, Matteo Sgalaberni wrote: > A my collegue JDBC application that stay in "idle intransaction" 24h/24h Just a little note: For most applications, this can be fixed updating the JDBC driver. Old versions had the behaviour of auto-opening a new backend transaction on commit/rollback, whereas new versions delay that until the first statement in the new transaction is sent. This won't fix applications that do a select and then sit idle for days before committing/rolling back, however. Those should be fixed or use autocommit mode. > Good to know this...but why this behaviour? it'is lovely...:) > > Tom , can you explain why?... It is because the transaction IDs are global per cluster. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
