Re: [GENERAL] Debugging infrequent pegged out CPU usage
On Mon, 3 Nov 2008, Jason Long wrote: For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. Take a look at pg_stat_activity when this happens to see what's going on. Also, try running top -c to see what is going on (the -c displays extra information for the postgresql processes) and save a snapshot of what you see. I normally put a quick script on the server to collect everything I want before even thinking of a restart when I get into this sort of common and ugly situation. Here's a sample; run this next time and you'll be way ahead of where you are now at figuring out what's going wrong: #!/bin/bash date crash psql -c select * from pg_stat_activity crash top -c -b -n 1 crash vmstat 1 5 crash iostat 1 5 crash psql -c select * from pg_stat_activity crash date crash Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value? You should at a minimum set log_min_duration_statement to figure out what the statements taking so long are. At the root of this problem there's probably some bad queries. I'd bet you start seeing an increase of those reported in the logs in advance of when the server becomes completely unresponsive. While there are relatively live few users the data is extremely important and the users will not wait for me to see what is wrong. They demand immediate resolution and the best I can do is reboot. Well, if you don't have any idea how to fix the problem, that's reasonable I guess (although stopping just the postgresql process with pg_ctl is probably all that's really needed, a full reboot is just prolonging the downtime). Once you've gotten some ideas for what to look at, like the little script above, you have to make the users wait until you're done running that before giving into pressure to fix something. Otherwise you'll never solve the problem. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debugging infrequent pegged out CPU usage
Greg Smith wrote: On Mon, 3 Nov 2008, Jason Long wrote: For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. Take a look at pg_stat_activity when this happens to see what's going on. Also, try running top -c to see what is going on (the -c displays extra information for the postgresql processes) and save a snapshot of what you see. I normally put a quick script on the server to collect everything I want before even thinking of a restart when I get into this sort of common and ugly situation. Here's a sample; run this next time and you'll be way ahead of where you are now at figuring out what's going wrong: #!/bin/bash date crash psql -c select * from pg_stat_activity crash top -c -b -n 1 crash vmstat 1 5 crash iostat 1 5 crash psql -c select * from pg_stat_activity crash date crash *Thanks for your advice. This is one of the most helpful mailing lists ever. :)* Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value? You should at a minimum set log_min_duration_statement to figure out what the statements taking so long are. At the root of this problem there's probably some bad queries. I'd bet you start seeing an increase of those reported in the logs in advance of when the server becomes completely unresponsive. While there are relatively live few users the data is extremely important and the users will not wait for me to see what is wrong. They demand immediate resolution and the best I can do is reboot. Well, if you don't have any idea how to fix the problem, that's reasonable I guess (although stopping just the postgresql process with pg_ctl is probably all that's really needed, a full reboot is just prolonging the downtime). Once you've gotten some ideas for what to look at, like the little script above, you have to make the users wait until you're done running that before giving into pressure to fix something. Otherwise you'll never solve the problem. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
[GENERAL] Debugging infrequent pegged out CPU usage
I am running PostgreSQL 8.3.4 on Centos 5.2 with a single Xeon 5472, 1600 MHz, 12 MB cache, 3.0 GHz quad core, and 4 GB RAM. My database is only about 50 MB and there are only about 20 users. For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. After reboot all is well for another week or so, but this brings the system to a grinding halt. * What is the best way to debug this? Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value?* I would greatly appreciate any advice on debugging this problem. While there are relatively live few users the data is extremely important and the users will not wait for me to see what is wrong. They demand immediate resolution and the best I can do is reboot. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
Re: [GENERAL] Debugging infrequent pegged out CPU usage
On Monday 03 November 2008, Jason Long [EMAIL PROTECTED] wrote: I would greatly appreciate any advice on debugging this problem. While there are relatively live few users the data is extremely important and the users will not wait for me to see what is wrong. They demand immediate resolution and the best I can do is reboot. At the risk of being called an ass, I would say your organization needs to hire someone capable of diagnosing the problem. -- Alan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debugging infrequent pegged out CPU usage
Jason Long [EMAIL PROTECTED] writes: For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. Well, you need to find out why. Turning on query logging (see log_statement) would probably be a good first step --- it'd help you determine if there's a specific query or queries causing it. Also, have you tried pg_ctl stop -m fast instead of a reboot? (If that doesn't work, it'd also be interesting to see if -m immediate does work.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debugging infrequent pegged out CPU usage
On Mon, Nov 3, 2008 at 11:30 AM, Jason Long [EMAIL PROTECTED] wrote: I am running PostgreSQL 8.3.4 on Centos 5.2 with a single Xeon 5472, 1600 MHz, 12 MB cache, 3.0 GHz quad core, and 4 GB RAM. My database is only about 50 MB and there are only about 20 users. For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. After reboot all is well for another week or so, but this brings the system to a grinding halt. What is the best way to debug this? Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value? Best way I've found it to keep track of the server over a period of time. nagios and mrtg are your friends here. You can use some more primitive methods, like ps ax|grep postgres|wc -l to see how many postgres backends are running. You need to figure out exactly what's happening to the machine before it dies, but as its approaching that point. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debugging infrequent pegged out CPU usage
On Mon, Nov 3, 2008 at 12:25 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Mon, Nov 3, 2008 at 11:30 AM, Jason Long [EMAIL PROTECTED] wrote: I am running PostgreSQL 8.3.4 on Centos 5.2 with a single Xeon 5472, 1600 MHz, 12 MB cache, 3.0 GHz quad core, and 4 GB RAM. My database is only about 50 MB and there are only about 20 users. For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. After reboot all is well for another week or so, but this brings the system to a grinding halt. What is the best way to debug this? Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value? Best way I've found it to keep track of the server over a period of time. nagios and mrtg are your friends here. You can use some more primitive methods, like ps ax|grep postgres|wc -l to see how many postgres backends are running. You need to figure out exactly what's happening to the machine before it dies, but as its approaching that point. Also, use the built in pg_xxx tables / views that show you what the server is doing. use vmstat, iostat, top and other tools to keep track. If you're on Windows, ignore all that and ask someone else cause I don't know enough about troubleshooting windows systems to be a lot of help there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debugging infrequent pegged out CPU usage
Scott Marlowe wrote: On Mon, Nov 3, 2008 at 12:25 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Mon, Nov 3, 2008 at 11:30 AM, Jason Long [EMAIL PROTECTED] wrote: I am running PostgreSQL 8.3.4 on Centos 5.2 with a single Xeon 5472, 1600 MHz, 12 MB cache, 3.0 GHz quad core, and 4 GB RAM. My database is only about 50 MB and there are only about 20 users. For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. After reboot all is well for another week or so, but this brings the system to a grinding halt. What is the best way to debug this? Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value? How about preventing this lockup by limiting CPU resources to Postgres or giving up if a query takes too long? I am barely able to log in let alone poke around. Best way I've found it to keep track of the server over a period of time. nagios and mrtg are your friends here. Thank you for the advice. I will investigate these options. You can use some more primitive methods, like ps ax|grep postgres|wc -l to see how many postgres backends are running. You need to figure out exactly what's happening to the machine before it dies, but as its approaching that point. Also, use the built in pg_xxx tables / views that show you what the server is doing. use vmstat, iostat, top and other tools to keep track. If you're on Windows, ignore all that and ask someone else cause I don't know enough about troubleshooting windows systems to be a lot of help there.
Re: [GENERAL] Debugging infrequent pegged out CPU usage
Jason Long wrote: Scott Marlowe wrote: On Mon, Nov 3, 2008 at 12:25 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Mon, Nov 3, 2008 at 11:30 AM, Jason Long [EMAIL PROTECTED] wrote: I am running PostgreSQL 8.3.4 on Centos 5.2 with a single Xeon 5472, 1600 MHz, 12 MB cache, 3.0 GHz quad core, and 4 GB RAM. My database is only about 50 MB and there are only about 20 users. For some reason Postgres is pegging my CPU and I can barely log on to reboot the machine. After reboot all is well for another week or so, but this brings the system to a grinding halt. What is the best way to debug this? Can I limit Postgres to a certain number of cores or set the timeout on the queries to a lower value? How about preventing this lockup by limiting CPU resources to Postgres or giving up if a query takes too long? I am barely able to log in let alone poke around. You can't limit cpu usage but you can set timeouts. See statement_timeout here: http://www.postgresql.org/docs/8.3/interactive/runtime-config.html Set up your logging. Even if you do have to reboot, you can at least go back through the logs to find out what happened just before the reboot. -- Postgresql php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
