Обсуждение: Postgres 7.2.1 Really Spinning the CPU
Just got my new server up and running with PostgreSQL 7.2.1... I am running Jive Forums against it. I used pg_dump to extract the data from the old server, and used 'psql database < file.sql' to get the data into the new database. Now I have it up and running and PostgreSQL is using up to 40% of the CPU... My site is not heavily trafficked, so this seems out of place. Is it possible that Jive's indexes were not re-created properly? The old server was also 7.2.1. I have bumped up shared memory: [root@vader init.d]# cat /proc/sys/kernel/shmmax 128000000 and in my postgresql.conf I have increased my shared buffers to 3000. Any ideas how to figure out what is going on? Thanks, Hunter
Hunter- Have you done a vacuum analyze since the data reload? The stats are different enough that the planner gets all confused if you don't analyze right away. Regards, -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Hunter Hillegas > Sent: Tuesday, June 04, 2002 9:28 PM > To: PostgreSQL > Subject: [GENERAL] Postgres 7.2.1 Really Spinning the CPU > > > Just got my new server up and running with PostgreSQL 7.2.1... I > am running > Jive Forums against it. > > I used pg_dump to extract the data from the old server, and used 'psql > database < file.sql' to get the data into the new database. > > Now I have it up and running and PostgreSQL is using up to 40% of > the CPU... > My site is not heavily trafficked, so this seems out of place. > > Is it possible that Jive's indexes were not re-created properly? The old > server was also 7.2.1. > > I have bumped up shared memory: > > [root@vader init.d]# cat /proc/sys/kernel/shmmax > 128000000 > > and in my postgresql.conf I have increased my shared buffers to 3000. > > Any ideas how to figure out what is going on? > > Thanks, > Hunter > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
I didn't. Now I have and it seems to have cleared it up. > From: "Nick Fankhauser" <nickf@ontko.com> > Reply-To: <nickf@ontko.com> > Date: Tue, 4 Jun 2002 21:51:06 -0500 > To: "Hunter Hillegas" <lists@lastonepicked.com>, "PostgreSQL" > <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] Postgres 7.2.1 Really Spinning the CPU > > Hunter- > > Have you done a vacuum analyze since the data reload? The stats are > different enough that the planner gets all confused if you don't analyze > right away. > > Regards, > > -Nick > > -------------------------------------------------------------------------- > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 > Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > > > >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org >> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Hunter Hillegas >> Sent: Tuesday, June 04, 2002 9:28 PM >> To: PostgreSQL >> Subject: [GENERAL] Postgres 7.2.1 Really Spinning the CPU >> >> >> Just got my new server up and running with PostgreSQL 7.2.1... I >> am running >> Jive Forums against it. >> >> I used pg_dump to extract the data from the old server, and used 'psql >> database < file.sql' to get the data into the new database. >> >> Now I have it up and running and PostgreSQL is using up to 40% of >> the CPU... >> My site is not heavily trafficked, so this seems out of place. >> >> Is it possible that Jive's indexes were not re-created properly? The old >> server was also 7.2.1. >> >> I have bumped up shared memory: >> >> [root@vader init.d]# cat /proc/sys/kernel/shmmax >> 128000000 >> >> and in my postgresql.conf I have increased my shared buffers to 3000. >> >> Any ideas how to figure out what is going on? >> >> Thanks, >> Hunter >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Actually, I may have spoken too soon... It's now back to really spinning the CPU... Any other ideas? > From: Hunter Hillegas <lists@lastonepicked.com> > Date: Tue, 04 Jun 2002 20:03:29 -0700 > To: <nickf@ontko.com>, PostgreSQL <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] Postgres 7.2.1 Really Spinning the CPU > > I didn't. Now I have and it seems to have cleared it up. > > >> From: "Nick Fankhauser" <nickf@ontko.com> >> Reply-To: <nickf@ontko.com> >> Date: Tue, 4 Jun 2002 21:51:06 -0500 >> To: "Hunter Hillegas" <lists@lastonepicked.com>, "PostgreSQL" >> <pgsql-general@postgresql.org> >> Subject: Re: [GENERAL] Postgres 7.2.1 Really Spinning the CPU >> >> Hunter- >> >> Have you done a vacuum analyze since the data reload? The stats are >> different enough that the planner gets all confused if you don't analyze >> right away. >> >> Regards, >> >> -Nick >> >> -------------------------------------------------------------------------- >> Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 >> Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ >> >> >> >>> -----Original Message----- >>> From: pgsql-general-owner@postgresql.org >>> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Hunter Hillegas >>> Sent: Tuesday, June 04, 2002 9:28 PM >>> To: PostgreSQL >>> Subject: [GENERAL] Postgres 7.2.1 Really Spinning the CPU >>> >>> >>> Just got my new server up and running with PostgreSQL 7.2.1... I >>> am running >>> Jive Forums against it. >>> >>> I used pg_dump to extract the data from the old server, and used 'psql >>> database < file.sql' to get the data into the new database. >>> >>> Now I have it up and running and PostgreSQL is using up to 40% of >>> the CPU... >>> My site is not heavily trafficked, so this seems out of place. >>> >>> Is it possible that Jive's indexes were not re-created properly? The old >>> server was also 7.2.1. >>> >>> I have bumped up shared memory: >>> >>> [root@vader init.d]# cat /proc/sys/kernel/shmmax >>> 128000000 >>> >>> and in my postgresql.conf I have increased my shared buffers to 3000. >>> >>> Any ideas how to figure out what is going on? >>> >>> Thanks, >>> Hunter >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 4: Don't 'kill -9' the postmaster >>> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Tue, 04 Jun 2002 20:20:30 -0700 "Hunter Hillegas" <lists@lastonepicked.com> wrote: > Actually, I may have spoken too soon... It's now back to really spinning the > CPU... > > Any other ideas? Reading the docs would be a good place to start. Questions like "It goes slow, what's wrong?" don't give us much to go on. The query optimizer in 7.2 uses a much richer set of statistics to aid in choosing the correct query plan. This should lead to better choices in most situation, but it's conceivable that you've run into a problem (or at least, a situation in which 7.2 does things differently than 7.1). I'd suggest finding out which query or queries are taking a while to execute. Then use EXPLAIN ANALYZE on the query to determine its query plan (if possible, compare that with the plan chosen by 7.1). If you can't solve the problem yourself, post the output of EXPLAIN ANALYZE, as well as any relevant parts of the schema to the list. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
That was my only general suggestion based on the general info that you had just done an upgrade, So like Neil, I'd say that now we need to be getting into particulars. Because the planner is very different it is not completely surprising that you see a change in behavior, but you'll need to track it down by looking at the specific queries that cause it. One other "general" thought- The main thing that the planner does, when it is working well is to reduce the I/O involved in obtaining the results. In a busy system, this would tend to be reflected in slightly higher CPU usage, since the CPU doesn't sit idle waiting for data as much as much. Have you checked the performance in wall-clock-time to see if you're still getting results in a reasonable (or shorter) length of time? You may find that the "CPU spinning" is a good thing. If not, it's time to get specific. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Hunter Hillegas > Sent: Tuesday, June 04, 2002 10:21 PM > To: Hunter Hillegas; nickf@ontko.com; PostgreSQL > Subject: Re: [GENERAL] Postgres 7.2.1 Really Spinning the CPU > > > Actually, I may have spoken too soon... It's now back to really > spinning the > CPU... > > Any other ideas? > > > > > From: Hunter Hillegas <lists@lastonepicked.com> > > Date: Tue, 04 Jun 2002 20:03:29 -0700 > > To: <nickf@ontko.com>, PostgreSQL <pgsql-general@postgresql.org> > > Subject: Re: [GENERAL] Postgres 7.2.1 Really Spinning the CPU > > > > I didn't. Now I have and it seems to have cleared it up. > > > > > >> From: "Nick Fankhauser" <nickf@ontko.com> > >> Reply-To: <nickf@ontko.com> > >> Date: Tue, 4 Jun 2002 21:51:06 -0500 > >> To: "Hunter Hillegas" <lists@lastonepicked.com>, "PostgreSQL" > >> <pgsql-general@postgresql.org> > >> Subject: Re: [GENERAL] Postgres 7.2.1 Really Spinning the CPU > >> > >> Hunter- > >> > >> Have you done a vacuum analyze since the data reload? The stats are > >> different enough that the planner gets all confused if you > don't analyze > >> right away. > >> > >> Regards, > >> > >> -Nick > >> > >> > -------------------------------------------------------------------------- > >> Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax > 1.765.962.9788 > >> Ray Ontko & Co. Software Consulting Services > http://www.ontko.com/ > >> > >> > >> > >>> -----Original Message----- > >>> From: pgsql-general-owner@postgresql.org > >>> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of > Hunter Hillegas > >>> Sent: Tuesday, June 04, 2002 9:28 PM > >>> To: PostgreSQL > >>> Subject: [GENERAL] Postgres 7.2.1 Really Spinning the CPU > >>> > >>> > >>> Just got my new server up and running with PostgreSQL 7.2.1... I > >>> am running > >>> Jive Forums against it. > >>> > >>> I used pg_dump to extract the data from the old server, and used 'psql > >>> database < file.sql' to get the data into the new database. > >>> > >>> Now I have it up and running and PostgreSQL is using up to 40% of > >>> the CPU... > >>> My site is not heavily trafficked, so this seems out of place. > >>> > >>> Is it possible that Jive's indexes were not re-created > properly? The old > >>> server was also 7.2.1. > >>> > >>> I have bumped up shared memory: > >>> > >>> [root@vader init.d]# cat /proc/sys/kernel/shmmax > >>> 128000000 > >>> > >>> and in my postgresql.conf I have increased my shared buffers to 3000. > >>> > >>> Any ideas how to figure out what is going on? > >>> > >>> Thanks, > >>> Hunter > >>> > >>> > >>> ---------------------------(end of > broadcast)--------------------------- > >>> TIP 4: Don't 'kill -9' the postmaster > >>> > >> > >> > >> ---------------------------(end of > broadcast)--------------------------- > >> TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >