[PERFORM] Postgres using more memory than it should
Hi. I have a problem on one of our production servers. A fairly complicated query is running, and the backend process is using 30 GB of RAM. The machine only has 32GB, and is understandably swapping like crazy. My colleague is creating swap files as quickly as it can use them up. The work_mem setting on this machine is 1000MB, running Postgres 8.3.0. Here is an excerpt from top: top - 15:54:17 up 57 days, 6:49, 3 users, load average: 20.17, 21.29, 16.31 Tasks: 250 total, 2 running, 248 sleeping, 0 stopped, 0 zombie Cpu(s): 3.1%us, 2.5%sy, 0.0%ni, 15.2%id, 78.7%wa, 0.0%hi, 0.5%si, 0.0%st Mem: 32961364k total, 32898588k used,62776k free,22440k buffers Swap: 8096344k total, 8096344k used,0k free, 6056472k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 27192 postgres 18 0 30.6g 22g 1984 R 31 71.7 32:20.09 postgres: flymine production-release-15.0 192.168.128.84(33736) INSERT 650 root 10 -5 000 S5 0.0 13:56.10 [kswapd2] 5513 postgres 15 0 130m 19m 364 S4 0.1 1067:04 postgres: stats collector process 957 root 10 -5 000 D1 0.0 1:39.13 [md2_raid1] 649 root 10 -5 000 D1 0.0 14:14.95 [kswapd1] 28599 root 15 0 000 D1 0.0 0:01.25 [pdflush] 648 root 10 -5 000 S0 0.0 15:10.68 [kswapd0] 2585 root 10 -5 000 D0 0.0 67:15.89 [kjournald] The query that is being run is an INSERT INTO table SELECT a fairly complex query. Any ideas why this is going so badly, and what I can do to solve it? Matthew -- First law of computing: Anything can go wro sig: Segmentation fault. core dumped. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres using more memory than it should
In response to Matthew Wakeling [EMAIL PROTECTED]: Hi. I have a problem on one of our production servers. A fairly complicated query is running, and the backend process is using 30 GB of RAM. The machine only has 32GB, and is understandably swapping like crazy. My colleague is creating swap files as quickly as it can use them up. The work_mem setting on this machine is 1000MB, running Postgres 8.3.0. If your query it dealing with a lot of data, it could easily use 1G per sort operation. If there are a lot of sorts (more than 32) you'll end up with this problem. 1G is probably too much memory to allocate for work_mem. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres using more memory than it should
Hi. I have a problem on one of our production servers. A fairly complicated query is running, and the backend process is using 30 GB of RAM. The machine only has 32GB, and is understandably swapping like crazy. My colleague is creating swap files as quickly as it can use them up. The work_mem setting on this machine is 1000MB, running Postgres 8.3.0. Are you aware that this is a per-session / per-sort settings? That means, if you have 10 sessions, each of them running query with 2 sort steps in the plan, it may occupy up to 20 GB of RAM (if both sorts use the whole 1GB of RAM). regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres using more memory than it should
On Wed, 3 Dec 2008, [EMAIL PROTECTED] wrote: Hi. I have a problem on one of our production servers. A fairly complicated query is running, and the backend process is using 30 GB of RAM. The machine only has 32GB, and is understandably swapping like crazy. My colleague is creating swap files as quickly as it can use them up. The work_mem setting on this machine is 1000MB, running Postgres 8.3.0. Are you aware that this is a per-session / per-sort settings? That means, if you have 10 sessions, each of them running query with 2 sort steps in the plan, it may occupy up to 20 GB of RAM (if both sorts use the whole 1GB of RAM). Quite aware, thanks. Having sent the process a SIGINT and inspected the logs, I now have a query to explain. Looking at it, there is one single sort, and ten hash operations, which would equate to 10GB, not 30GB. What is more worrying is that now that the query has been stopped, the backend process is still hanging onto the RAM. Matthew -- Failure is not an option. It comes bundled with your Microsoft product. -- Ferenc Mantfeld -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres using more memory than it should
On Wed, Dec 3, 2008 at 9:34 AM, Matthew Wakeling [EMAIL PROTECTED] wrote: On Wed, 3 Dec 2008, [EMAIL PROTECTED] wrote: Hi. I have a problem on one of our production servers. A fairly complicated query is running, and the backend process is using 30 GB of RAM. The machine only has 32GB, and is understandably swapping like crazy. My colleague is creating swap files as quickly as it can use them up. The work_mem setting on this machine is 1000MB, running Postgres 8.3.0. Are you aware that this is a per-session / per-sort settings? That means, if you have 10 sessions, each of them running query with 2 sort steps in the plan, it may occupy up to 20 GB of RAM (if both sorts use the whole 1GB of RAM). Quite aware, thanks. Having sent the process a SIGINT and inspected the logs, I now have a query to explain. Looking at it, there is one single sort, and ten hash operations, which would equate to 10GB, not 30GB. What is more worrying is that now that the query has been stopped, the backend process is still hanging onto the RAM. What's your setting for share_buffers, as that's likely what the backend is holding onto. Also, you should REALLY update to 8.3.5 as there are some nasty bugs fixed from 8.3.0 you don't want to run into. Who knows, you might be being bitten by one right now. Unlike other bits of software floating around, pgsql updates are bug fix / security fix only, with no major code changes allowed, since those go into the next release which is usually ~1 year later anyway. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres using more memory than it should
On Wed, 3 Dec 2008, Scott Marlowe wrote: Having sent the process a SIGINT and inspected the logs, I now have a query to explain. Looking at it, there is one single sort, and ten hash operations, which would equate to 10GB, not 30GB. What is more worrying is that now that the query has been stopped, the backend process is still hanging onto the RAM. What's your setting for share_buffers, as that's likely what the backend is holding onto. Shared buffers are set at 500MB, which is what all the other backends are holding onto. It's just the one backend that is using 30GB. At the moment, it is being swapped out, but the system seems responsive. We'll restart the whole lot some time in the middle of the night when noone minds. Also, you should REALLY update to 8.3.5 as there are some nasty bugs fixed from 8.3.0 you don't want to run into. Who knows, you might be being bitten by one right now. Unlike other bits of software floating around, pgsql updates are bug fix / security fix only, with no major code changes allowed, since those go into the next release which is usually ~1 year later anyway. It's possible, although I didn't see any relevant memory leaks in the release notes. This is one of the only machines we have that has not been upgraded, and it is on our schedule. Because it is running a slightly old version of RedHat Fedora, upgrading involves more horribleness than our sysadmin is willing to do on the fly with the server up. Matthew -- The email of the species is more deadly than the mail. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres using more memory than it should
On Wed, Dec 03, 2008 at 04:01:48PM +, Matthew Wakeling wrote: The work_mem setting on this machine is 1000MB, running Postgres 8.3.0. Check bug report from 2008-11-28, by Grzegorz Jaskiewicz: query failed, not enough memory on 8.3.5 http://archives.postgresql.org/pgsql-bugs/2008-11/msg00180.php depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres using more memory than it should
On Wed, Dec 3, 2008 at 9:59 AM, Matthew Wakeling [EMAIL PROTECTED] wrote: On Wed, 3 Dec 2008, Scott Marlowe wrote: Also, you should REALLY update to 8.3.5 as there are some nasty bugs fixed from 8.3.0 you don't want to run into. Who knows, you might be being bitten by one right now. Unlike other bits of software floating around, pgsql updates are bug fix / security fix only, with no major code changes allowed, since those go into the next release which is usually ~1 year later anyway. It's possible, although I didn't see any relevant memory leaks in the release notes. This is one of the only machines we have that has not been upgraded, and it is on our schedule. Because it is running a slightly old version of RedHat Fedora, upgrading involves more horribleness than our sysadmin is willing to do on the fly with the server up. That makes absolutely no sense. If it's an in house built rpm, you just create a new one with the same .spec file, if it was built from source it's a simple ./configure --youroptionshere ;make;make install. You need a new sysadmin. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] How to profile an SQL script?
Hi. I have a longish collection of SQL statements stored in a file that I run periodically via cron. Running this script takes a bit too long, even for a cron job, and I would like to streamline it. Is there a way to tell Postgres to print out, after each SQL statement is executed, how long it took to execute? Thanks! Kynn
Re: [PERFORM] How to profile an SQL script?
Kynn Jones [EMAIL PROTECTED] schrieb: Hi. I have a longish collection of SQL statements stored in a file that I run periodically via cron. Running this script takes a bit too long, even for a cron job, and I would like to streamline it. Is there a way to tell Postgres to print out, after each SQL statement is executed, how long it took to execute? Do you run this with psql? You can switch on timing-output, with \timing. It displays after each statement the run-time for this statement. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to profile an SQL script?
Andreas Kretschmer wrote: Kynn Jones [EMAIL PROTECTED] schrieb: Hi. I have a longish collection of SQL statements stored in a file that I run periodically via cron. Running this script takes a bit too long, even for a cron job, and I would like to streamline it. Is there a way to tell Postgres to print out, after each SQL statement is executed, how long it took to execute? Do you run this with psql? You can switch on timing-output, with \timing. It displays after each statement the run-time for this statement. See also log_duration and log_min_duration settings. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to profile an SQL script?
Andreas, Heikki: Thanks! Kynn
