FAQ
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

Search Discussions

  • Mladen Gogala at Jun 23, 2010 at 12:49 pm

    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 Fuentes at Jun 23, 2010 at 4:27 pm
    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 at 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
    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
  • Mladen Gogala at Jun 23, 2010 at 6:03 pm
    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 run at 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
    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
  • Deborah Fuentes at Jun 23, 2010 at 11:03 pm
    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 Performance lists?

    -----Original Message-----
    From: 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 run at 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
    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
  • Mladen Gogala at Jun 24, 2010 at 1:10 am

    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 Performance lists?
    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
  • Devrim Gündüz at Jun 24, 2010 at 7:13 am

    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
  • Bruce Momjian at Jun 24, 2010 at 3:13 pm
    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. +

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 22, '10 at 8:40p
activeJun 24, '10 at 3:13p
posts8
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase