Thread: CPU usage when building a schema
Hello,
I’m new to Postgres so I apologize in advance for the simple question….
Why is it when I’m building an empty schema does Postgres consume all the CPU resources? My experience is with Informix and I’ve never seen anything like this.
Thanks!
Deb
Deborah Fuentes wrote: > > Hello, > > > > I’m new to Postgres so I apologize in advance for the simple question…. > > > > Why is it when I’m building an empty schema does Postgres consume all > the CPU resources? My experience is with Informix and I’ve never seen > anything like this. > > > > Thanks! > > Deb > What kind of a machine are you using? What OS, version and architecture? What Postgres version? If Linux, did you do "strace" to see what is going on? You can even compile PgSQL with the "-g" flag and use gprof to find out where the time is spent. Furthermore, what do you mean by "building an empty schema"? Are we talking about "create schema" command or something else? I tried creating an empty schema and it wasn't even visible on CPU: [mgogala@medo ~]$ psql Timing is on. psql (8.4.4) Type "help" for help. mgogala=# create schema test; CREATE SCHEMA Time: 192.082 ms mgogala=# drop schema test; DROP SCHEMA Time: 59.451 ms mgogala=# Simultaneously, in another window I did this: [mgogala@medo ~]$ sar -u 3 15 Linux 2.6.18-194.3.1.el5.centos.plusPAE (medo) 06/23/2010 08:45:43 AM CPU %user %nice %system %iowait %steal %idle 08:45:46 AM all 3.00 0.00 0.83 1.50 0.00 94.68 08:45:49 AM all 2.17 0.00 0.67 0.00 0.00 97.16 08:45:52 AM all 2.16 0.00 1.00 0.00 0.00 96.84 08:45:55 AM all 2.50 0.00 1.50 3.00 0.00 93.01 08:45:58 AM all 2.34 0.00 0.33 0.00 0.00 97.33 08:46:01 AM all 0.67 0.00 0.50 1.00 0.00 97.83 [mgogala@medo ~]$ It didn't even make a ripple. My Postgres is 8.4.4, Linux is CentOS 5.5: mgogala@medo ~]$ uname -a Linux medo 2.6.18-194.3.1.el5.centos.plusPAE #1 SMP Wed May 19 10:00:02 EDT 2010 i686 athlon i386 GNU/Linux [mgogala@medo ~]$ What versions of OS and DB do you have? Do you have an OS or are you running Windows? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
Mladen, Thank you for your reply. I apologize for not including the system specifics in my initial post. We are running Postgres 8.3.7 on a Sun M5000 with 2 x quad core CPUs (16 threads) running Solaris 10. When I run an SQL to create new tables and indexes is when Postgres consumes all CPU and impacts other users on the server. I've attached the sar data at the time of the run- here's a snip-it below. Thanks! Deb **************************************************** Here, note the run queue, the left column. That is the number of processes waiting to run. 97 processes waiting to run atany time with only eight CPU cores looks very busy. root@core2 # sar -q 5 500 SunOS core2 5.10 Generic_142900-11 sun4u 06/17/2010 12:01:50 runq-sz %runocc swpq-sz %swpocc 12:01:55 1.8 80 0.0 0 12:02:00 1.0 20 0.0 0 12:02:05 1.0 20 0.0 0 12:02:10 0.0 0 0.0 0 12:02:15 0.0 0 0.0 0 12:02:21 3.3 50 0.0 0 12:02:26 1.0 20 0.0 0 12:02:31 1.0 60 0.0 0 12:02:36 1.0 20 0.0 0 12:02:42 27.0 50 0.0 0 12:02:49 32.8 83 0.0 0 12:02:55 76.0 100 0.0 0 12:03:01 66.1 100 0.0 0 12:03:07 43.8 100 0.0 0 12:03:13 52.0 100 0.0 0 12:03:19 91.2 100 0.0 0 12:03:26 97.8 83 0.0 0 12:03:33 63.7 100 0.0 0 12:03:39 67.4 100 0.0 0 12:03:47 41.5 100 0.0 0 12:03:53 82.0 83 0.0 0 12:03:59 88.7 100 0.0 0 12:04:06 87.7 50 0.0 0 12:04:12 41.3 100 0.0 0 12:04:17 94.3 50 0.0 0 12:04:22 1.0 20 0.0 0 12:04:27 3.3 60 0.0 0 12:04:32 1.0 20 0.0 0 12:04:38 0.0 0 0.0 0 -----Original Message----- From: Mladen Gogala [mailto:mladen.gogala@vmsinfo.com] Sent: Wednesday, June 23, 2010 5:50 AM To: Deborah Fuentes Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] CPU usage when building a schema Deborah Fuentes wrote: > > Hello, > > > > I'm new to Postgres so I apologize in advance for the simple question.... > > > > Why is it when I'm building an empty schema does Postgres consume all > the CPU resources? My experience is with Informix and I've never seen > anything like this. > > > > Thanks! > > Deb > What kind of a machine are you using? What OS, version and architecture? What Postgres version? If Linux, did you do "strace" to see what is going on? You can even compile PgSQL with the "-g" flag and use gprof to find out where the time is spent. Furthermore, what do you mean by "building an empty schema"? Are we talking about "create schema" command or something else? I tried creating an empty schema and it wasn't even visible on CPU: [mgogala@medo ~]$ psql Timing is on. psql (8.4.4) Type "help" for help. mgogala=# create schema test; CREATE SCHEMA Time: 192.082 ms mgogala=# drop schema test; DROP SCHEMA Time: 59.451 ms mgogala=# Simultaneously, in another window I did this: [mgogala@medo ~]$ sar -u 3 15 Linux 2.6.18-194.3.1.el5.centos.plusPAE (medo) 06/23/2010 08:45:43 AM CPU %user %nice %system %iowait %steal %idle 08:45:46 AM all 3.00 0.00 0.83 1.50 0.00 94.68 08:45:49 AM all 2.17 0.00 0.67 0.00 0.00 97.16 08:45:52 AM all 2.16 0.00 1.00 0.00 0.00 96.84 08:45:55 AM all 2.50 0.00 1.50 3.00 0.00 93.01 08:45:58 AM all 2.34 0.00 0.33 0.00 0.00 97.33 08:46:01 AM all 0.67 0.00 0.50 1.00 0.00 97.83 [mgogala@medo ~]$ It didn't even make a ripple. My Postgres is 8.4.4, Linux is CentOS 5.5: mgogala@medo ~]$ uname -a Linux medo 2.6.18-194.3.1.el5.centos.plusPAE #1 SMP Wed May 19 10:00:02 EDT 2010 i686 athlon i386 GNU/Linux [mgogala@medo ~]$ What versions of OS and DB do you have? Do you have an OS or are you running Windows? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
Deborah, that is very strange. I would understand a CPU bound runaway process but having 97 processes being ready to run simultaneously, thus inundating the run queue seems like a bug. Tom Lane works for EnterpriseDB, maybe he would know what happens there. I don't have any Solaris available to test my hypothesis. This definitely sounds like a platform specific bug. Deborah Fuentes wrote: > Mladen, > > Thank you for your reply. I apologize for not including the system specifics in my initial post. > > We are running Postgres 8.3.7 on a Sun M5000 with 2 x quad core CPUs (16 threads) running Solaris 10. > > When I run an SQL to create new tables and indexes is when Postgres consumes all CPU and impacts other users on the server. > > I've attached the sar data at the time of the run- here's a snip-it below. > > Thanks! > Deb > > **************************************************** > > Here, note the run queue, the left column. That is the number of processes waiting to run. 97 processes waiting to runat any time with only eight CPU cores looks very busy. > > root@core2 # sar -q 5 500 > > SunOS core2 5.10 Generic_142900-11 sun4u 06/17/2010 > > 12:01:50 runq-sz %runocc swpq-sz %swpocc > 12:01:55 1.8 80 0.0 0 > 12:02:00 1.0 20 0.0 0 > 12:02:05 1.0 20 0.0 0 > 12:02:10 0.0 0 0.0 0 > 12:02:15 0.0 0 0.0 0 > 12:02:21 3.3 50 0.0 0 > 12:02:26 1.0 20 0.0 0 > 12:02:31 1.0 60 0.0 0 > 12:02:36 1.0 20 0.0 0 > 12:02:42 27.0 50 0.0 0 > 12:02:49 32.8 83 0.0 0 > 12:02:55 76.0 100 0.0 0 > 12:03:01 66.1 100 0.0 0 > 12:03:07 43.8 100 0.0 0 > 12:03:13 52.0 100 0.0 0 > 12:03:19 91.2 100 0.0 0 > 12:03:26 97.8 83 0.0 0 > 12:03:33 63.7 100 0.0 0 > 12:03:39 67.4 100 0.0 0 > 12:03:47 41.5 100 0.0 0 > 12:03:53 82.0 83 0.0 0 > 12:03:59 88.7 100 0.0 0 > 12:04:06 87.7 50 0.0 0 > 12:04:12 41.3 100 0.0 0 > 12:04:17 94.3 50 0.0 0 > 12:04:22 1.0 20 0.0 0 > 12:04:27 3.3 60 0.0 0 > 12:04:32 1.0 20 0.0 0 > 12:04:38 0.0 0 0.0 0 > > -----Original Message----- > From: Mladen Gogala [mailto:mladen.gogala@vmsinfo.com] > Sent: Wednesday, June 23, 2010 5:50 AM > To: Deborah Fuentes > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] CPU usage when building a schema > > Deborah Fuentes wrote: > >> Hello, >> >> >> >> I'm new to Postgres so I apologize in advance for the simple question.... >> >> >> >> Why is it when I'm building an empty schema does Postgres consume all >> the CPU resources? My experience is with Informix and I've never seen >> anything like this. >> >> >> >> Thanks! >> >> Deb >> >> > What kind of a machine are you using? What OS, version and architecture? > What Postgres version? If Linux, did you do "strace" to see what is > going on? You can even compile PgSQL with the "-g" flag and use gprof > to find out where the time is spent. Furthermore, what do you mean by > "building an empty schema"? Are we talking about "create schema" command > or something else? I tried creating an empty schema and it wasn't even > visible on CPU: > [mgogala@medo ~]$ psql > Timing is on. > psql (8.4.4) > Type "help" for help. > > mgogala=# create schema test; > CREATE SCHEMA > Time: 192.082 ms > mgogala=# drop schema test; > DROP SCHEMA > Time: 59.451 ms > mgogala=# > > Simultaneously, in another window I did this: > [mgogala@medo ~]$ sar -u 3 15 > Linux 2.6.18-194.3.1.el5.centos.plusPAE (medo) 06/23/2010 > > 08:45:43 AM CPU %user %nice %system %iowait > %steal %idle > 08:45:46 AM all 3.00 0.00 0.83 1.50 > 0.00 94.68 > 08:45:49 AM all 2.17 0.00 0.67 0.00 > 0.00 97.16 > 08:45:52 AM all 2.16 0.00 1.00 0.00 > 0.00 96.84 > 08:45:55 AM all 2.50 0.00 1.50 3.00 > 0.00 93.01 > 08:45:58 AM all 2.34 0.00 0.33 0.00 > 0.00 97.33 > 08:46:01 AM all 0.67 0.00 0.50 1.00 > 0.00 97.83 > > [mgogala@medo ~]$ > > It didn't even make a ripple. My Postgres is 8.4.4, Linux is CentOS 5.5: > mgogala@medo ~]$ uname -a > Linux medo 2.6.18-194.3.1.el5.centos.plusPAE #1 SMP Wed May 19 10:00:02 > EDT 2010 i686 athlon i386 GNU/Linux > [mgogala@medo ~]$ > What versions of OS and DB do you have? Do you have an OS or are you > running Windows? > > > > -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
Thank you for the feedback. I'll send Tom Lane an email. Do you think it would also be worth posting to the Admin or Performancelists? -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Mladen Gogala Sent: Wednesday, June 23, 2010 11:03 AM To: Deborah Fuentes Cc: Mladen Gogala; pgsql-novice@postgresql.org; John Beckner Subject: Re: [NOVICE] CPU usage when building a schema Deborah, that is very strange. I would understand a CPU bound runaway process but having 97 processes being ready to run simultaneously, thus inundating the run queue seems like a bug. Tom Lane works for EnterpriseDB, maybe he would know what happens there. I don't have any Solaris available to test my hypothesis. This definitely sounds like a platform specific bug. Deborah Fuentes wrote: > Mladen, > > Thank you for your reply. I apologize for not including the system specifics in my initial post. > > We are running Postgres 8.3.7 on a Sun M5000 with 2 x quad core CPUs (16 threads) running Solaris 10. > > When I run an SQL to create new tables and indexes is when Postgres consumes all CPU and impacts other users on the server. > > I've attached the sar data at the time of the run- here's a snip-it below. > > Thanks! > Deb > > **************************************************** > > Here, note the run queue, the left column. That is the number of processes waiting to run. 97 processes waiting to runat any time with only eight CPU cores looks very busy. > > root@core2 # sar -q 5 500 > > SunOS core2 5.10 Generic_142900-11 sun4u 06/17/2010 > > 12:01:50 runq-sz %runocc swpq-sz %swpocc > 12:01:55 1.8 80 0.0 0 > 12:02:00 1.0 20 0.0 0 > 12:02:05 1.0 20 0.0 0 > 12:02:10 0.0 0 0.0 0 > 12:02:15 0.0 0 0.0 0 > 12:02:21 3.3 50 0.0 0 > 12:02:26 1.0 20 0.0 0 > 12:02:31 1.0 60 0.0 0 > 12:02:36 1.0 20 0.0 0 > 12:02:42 27.0 50 0.0 0 > 12:02:49 32.8 83 0.0 0 > 12:02:55 76.0 100 0.0 0 > 12:03:01 66.1 100 0.0 0 > 12:03:07 43.8 100 0.0 0 > 12:03:13 52.0 100 0.0 0 > 12:03:19 91.2 100 0.0 0 > 12:03:26 97.8 83 0.0 0 > 12:03:33 63.7 100 0.0 0 > 12:03:39 67.4 100 0.0 0 > 12:03:47 41.5 100 0.0 0 > 12:03:53 82.0 83 0.0 0 > 12:03:59 88.7 100 0.0 0 > 12:04:06 87.7 50 0.0 0 > 12:04:12 41.3 100 0.0 0 > 12:04:17 94.3 50 0.0 0 > 12:04:22 1.0 20 0.0 0 > 12:04:27 3.3 60 0.0 0 > 12:04:32 1.0 20 0.0 0 > 12:04:38 0.0 0 0.0 0 > > -----Original Message----- > From: Mladen Gogala [mailto:mladen.gogala@vmsinfo.com] > Sent: Wednesday, June 23, 2010 5:50 AM > To: Deborah Fuentes > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] CPU usage when building a schema > > Deborah Fuentes wrote: > >> Hello, >> >> >> >> I'm new to Postgres so I apologize in advance for the simple question.... >> >> >> >> Why is it when I'm building an empty schema does Postgres consume all >> the CPU resources? My experience is with Informix and I've never seen >> anything like this. >> >> >> >> Thanks! >> >> Deb >> >> > What kind of a machine are you using? What OS, version and architecture? > What Postgres version? If Linux, did you do "strace" to see what is > going on? You can even compile PgSQL with the "-g" flag and use gprof > to find out where the time is spent. Furthermore, what do you mean by > "building an empty schema"? Are we talking about "create schema" command > or something else? I tried creating an empty schema and it wasn't even > visible on CPU: > [mgogala@medo ~]$ psql > Timing is on. > psql (8.4.4) > Type "help" for help. > > mgogala=# create schema test; > CREATE SCHEMA > Time: 192.082 ms > mgogala=# drop schema test; > DROP SCHEMA > Time: 59.451 ms > mgogala=# > > Simultaneously, in another window I did this: > [mgogala@medo ~]$ sar -u 3 15 > Linux 2.6.18-194.3.1.el5.centos.plusPAE (medo) 06/23/2010 > > 08:45:43 AM CPU %user %nice %system %iowait > %steal %idle > 08:45:46 AM all 3.00 0.00 0.83 1.50 > 0.00 94.68 > 08:45:49 AM all 2.17 0.00 0.67 0.00 > 0.00 97.16 > 08:45:52 AM all 2.16 0.00 1.00 0.00 > 0.00 96.84 > 08:45:55 AM all 2.50 0.00 1.50 3.00 > 0.00 93.01 > 08:45:58 AM all 2.34 0.00 0.33 0.00 > 0.00 97.33 > 08:46:01 AM all 0.67 0.00 0.50 1.00 > 0.00 97.83 > > [mgogala@medo ~]$ > > It didn't even make a ripple. My Postgres is 8.4.4, Linux is CentOS 5.5: > mgogala@medo ~]$ uname -a > Linux medo 2.6.18-194.3.1.el5.centos.plusPAE #1 SMP Wed May 19 10:00:02 > EDT 2010 i686 athlon i386 GNU/Linux > [mgogala@medo ~]$ > What versions of OS and DB do you have? Do you have an OS or are you > running Windows? > > > > -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Deborah Fuentes wrote: > Thank you for the feedback. I'll send Tom Lane an email. Do you think it would also be worth posting to the Admin or Performancelists? > > Please, publish any possible interesting development on this list. This sounds very interesting. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
On Wed, 2010-06-23 at 14:03 -0400, Mladen Gogala wrote: > Tom Lane works for EnterpriseDB No, he is working for Red Hat. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
Devrim G�ND�Z wrote: -- Start of PGP signed section. > On Wed, 2010-06-23 at 14:03 -0400, Mladen Gogala wrote: > > Tom Lane works for EnterpriseDB > > No, he is working for Red Hat. Also, sending bug reports to individual developers is discouraged because it does not scale. Posting to the right list is encouraged and someone will follow up on that. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +